CIS 2002
SEMESTER 1, 2015
Assignment 1 specification
Description Marks out of Wtg(%) Due date
ASSIGNMENT 1 100.00 7% ONC / 10% EXT 27 April 2015
IMPORTANT INFORMATION
You must submit the assignment electronically by the due date via the link on the study desk. Instructions will be provided on the course study desk.
You must use the official USQ data modelling and normalization methodology. This methodology is based on Clive Finkelstein’s techniques (SR 2.1 and SR 2.2) and all the examples in the lectures, study book and the tutorials use this methodology.
If you do not use the USQ methodology, you will probably be awarded a mark of zero.
It is perfectly acceptable if you submit neat hand-drawn ERD’s. Alternatively, you might wish to use Word. If you use a CASE or drawing tool, you must adapt the drawing to conform to the USQ methodology.
SECTION A (APPLIED DATABASE THEORY) (20 marks)
Since its establishment in early 90s, Fresh Fast Foods has expanded its business from a single store in Queensland to over 40 stores and franchises across Australia. Integration of its business processes with IT has been one of the critical success factors for Fresh Fast Food. A transaction support database system was deployed early as a business strategy to provide up to date stock management and record point of sales information in all of its 40 stores.
Over the ten year period, Fresh Fast Foods’ the database system has accumulated huge volumes of data regarding customer demands, orders and inventory management. The new CIO of the company Samantha Jones wants to look at using the existing data collected over the years to support decision making for management in order to better understand its internal processes, customer demands and trends.
CIO Samantha thinks data warehouse is a good solution to meet the data mining objectives? As the IT project manager at Fresh Fast Foods, you are asked to provide a brief review of data warehouse concepts and its use in business. Building on that information and looking at the current information gathering process at Fresh Fast Foods what other activities or information gathering might you suggest that we adopt to make analyses of warehoused data a successful strategy?
Write a memorandum to Samantha Jones (CIO) and present your research findings. Your memorandum should be no longer than 500 words.
Memorandum Template
A template for the memorandum is included below. You may use this as a template.
Company Name Here
Memo
To: [Click here and type name]
From: [Click here and type name]
CC: [Click here and type name]
Date: 28-May-15
Re: [Click here and type subject]
How to Use This Memo Template
Select text you would like to replace, and type your memo. Use styles such as Heading 1-3 and Body Text in the Style control on the Formatting toolbar. To save changes to this template for future use, choose Save As from the File menu. In the Save As Type box, choose Document Template. Next time you want to use it, choose New from the File menu, and then double-click your template.
Selected Reading 1.3 is a good starting resource to study for this case.
You are expected to present solutions like an IT consultant’s report in a memo format. You are required to extensively research on the relevant topics and present concise and workable solution.
There is no requirement for formal in-text referencing in this question. However, it is recommended to put a list of references at the end of the memo showing the published materials that you researched while answering this question.
SECTION B (SQL) (40 marks)
For each question, three marks will be awarded for the SQL and one mark for the correct output.
The following E-R diagram represents a JustLee Books database. The script for the table creation is located on the Moodle website for the CIS2002 course under the assignment specifications. You will need to make sure that you rerun the script to reset the tables to their default state.

In this question, you will use the JustLee database. The JustLee database including appropriate data will be made available on the USQ Oracle server. You must use this data.
If you are using Oracle on your own computer and are unable to access the USQ server, email the course leader for a script file that will create the tables for you. Due to copyright issues, you will need to insert the data yourself but you will be provided with a template.
Full description of the JustLee databse is found in Appendix A in the textbook page 511. You may also get most of the details by using data dictionary on the oracle server.
Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT. A screen dump of the output is acceptable. Show as many rows as you can. A screen dump is usually done via the ALT + PRNT SCRN command sequence or by the use if the ‘Snipping Tool’ under windows to capture parts of the screen.
While the output helps to understand your solution, you should not be analyzing the output of the query in detail. As long as you are confident that your query corresponds to the question completely, your output may not be significant. You can have an output that says NO ROWS FOUND and it could be a perfectly valid output as long as your query fulfils the requirement. Explain why the query may not contain any output in this instance.
1. Display full details of all the attributes in the ORDERITEMS table.
2. Display the last name, first name, city and email address for all the customers in the CUSTOMERS table and sort all records by city name order ascending.
3. Display last name, first name of all the customers that have been referred to us by one of our existing customers who are identified by customer number 1003 or 1005 or 1010.
4. List the names of all the cities that have customers recorded in the database. Each city should only appear once and the result should be in city name order ascending.
5. Display the ISBN and title of books in the BOOKS table where the category contains the characters ‘COM’ anywhere in the field and where the book does not have a discount value recorded.
6. Display the book title, cost, retail and calculate the profit and name the new field profit for all the books where the profit from the sale of the book is more than 70% of the cost of the book.
7. Display the ISBN, book title, cost and retail price of all the books published during the month of March in any year. Order the list by the book title.
8. Display the book title, retail cost and cost of the book after subtracting any available discount for that book. Name the new field ‘RETAIL AFTER DISCOUNT’ and order by the new calculated retail price of the book in descending order.
9. Display the book title, retail cost and cost of the book after subtracting any available discount for that book for all the books that have a discount. Name the new field ‘RETAIL AFTER DISCOUNT’ and order by the new calculated retail price of the book in descending order.
10. List ISBN, title, retail, category of all the books that have the category of ‘COMPUTER’ or where the category contains the string ‘LIFE’ anywhere in the description and where price of the books is more than 30 dollars and where the published year is after 2004.

SECTION C (Data Modelling) (40 marks)
PART MARKING: We will only mark TWO of the four questions below but you must submit answers to all four. We will choose the questions we mark randomly and mark the same questions for all submissions. Model answers will be supply for all questions during the semester.
Construct data models for the following specifications. Include an ERD and a list of relations (entity list). Your relations must show all attributes, primary keys and foreign keys. You must use the USQ (Finkelstein) methodology as described in your Study Book, the lectures and the tutorials.
Question 1
A number of engineers employed by a builder company work in several construction projects. We store the project name, location, estimated duration and project manager name for a construction project. Each project has a number of engineers working on the project. For an engineer, we store name, address, engineering membership number and area of expertise. We also need to store the number of days each engineer spends on each project.
Question 2
A questionnaire is a research instrument consisting of a series of questions. We need to develop a model to store questionnaires and its underlying questions. A questionnaire records questionnaire name, primary contact name, start date and expected end date. A questionnaire may have many questions. Each question has a question number, question text and a category description for the question.
Question 3
A property rental manager needs to store details about the tenant and the owner of a property. Tenant and Owner details include an identification number, name and contact number. For tenant, we also need to store name and contact number of next of kin. Property is directly associated with the owner. One owner may own many properties but a property must belong to one owner. For a property, we store its address, property type and current rental rate.
Question 4
A truck driver may be assigned to many teams over time. A team comprises of a minimum of one driver but could have up to four. For driver, we store the name, license number and birth date. For team, we store the team identifier and base location. We also need to store the date a driver is assigned to a team, the date a driver leaves the team and the total kilometres the driver has driven within the team.
Prepare the following for all four questions:
1 An ER diagram: Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities but do not create entities that are not specifically covered by the specification. You must use the Finkelstein methodology as per the study book and tutorials.
2 A list of relations (entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned but do not create any other additional attributes.
MARKING CRITERIA
SECTION A
1. Solution addresses the business problem and provides a workable resolution to the problem.
2. Solution demonstrates appropriate reference to relevant sections of the selected readings, textbook and, if applicable, other reference material.
3. Solution addresses the assumptions of the problem appropriately.
4. Solution uses the correct template.
5. Solution is grammatically and structurally sound.
6. Presentation is neat and professional.
SECTION B
1. Three marks awarded for each correct SQL statement and one mark for the correct output.
2. Alternative approaches to the model answer will often be accepted unless they do not follow the rules / requirements set out in the specification, are poorly optimised or are poorly constructed (SQL) or utilise features of SQL not covered at this stage in the course.
3. Part marks may be awarded if an answer only has a small problem or an alternative solution is presented that works but is not ideally optimised / constructed.
SECTION C
1. Entities – no missing entities, appropriate names, no redundant entities, etc.
2. Cardinalities and optionalities all shown and correct.
3. Complete list of relations, showing all applicable attributes, primary keys and foreign keys.
4. Sophistication: well presented solution; good layout; innovative approach; correct
diagrams/notation; solution easy to read and understand; solution comprehensive