Assignment
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 (SQL)
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 B (Data Modelling)
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.