SECTION A (Data Modelling) (40 marks)
MARKING CRITERIA
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
You are designing a database for multiple choice questions in an online test module of the student information systems for a campus. The online test module stores general profile information about all the students and records student responses and test results. All information regarding the tests including all the questions and options of the multiple choice questions are recorded in the module.
A test is recorded with a name, description, start date, end date and must belong to one of two types: General Test or Academic Test. These two types of test are recorded in an entity called TEST TYPE. For Academic Test, you also need to store the Field of Research (FoR) code.
A test contains a minimum of 10 questions. Each question can be listed in multiple tests as well. For a question, we store its identifier, the question text and the category for the question. A separate list of all category names are stored in an entity called CATEGORY. A question also has a minimum of four options where one of the options must be the correct answer. All the options for each question are stored with an identifier, option text and a flag to determine whether the option is the correct answer or not.
Student numbers and email addresses are imported from a legacy application and stored in the STUDENT entity. The imported student number is the identifier of the student entity and the email address is the username for login. Other details stored for a student include name, password, account joined date and account expiry date.
Information about each response is stored with reference to the particular test, specific question, selected option and the student who attempted the question. We also store the response date and response time for audit purposes.
Finally, results of every test for each student is stored and identified with a result reference number. The score and rank of the student for the test is also recorded for reporting purposes.
Prepare the following:
a) An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials. (25 Marks)
b) A list of relations (equivalent to Finkelstein 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. You must use the Finkelstein methodology as per the study book and tutorials. (15 Marks)
SECTION B (Normalisation) (30 marks)
MARKING CRITERIA
1. All entitles listed and correctly
2. Relations – no missing relations, appropriate names, no redundant relations.
3. All primary keys present and correctly notated.
4. All foreign keys present and correctly notated.
5. All attributes present.
6. All repeating groups resolved.
7. Derived attributes indicated in brackets.
8. All 2NF and transitive dependencies resolved.
9. All relations correctly notated using USQ methodology.
CAR PURCHASE CONTRACT (contract number, contract date, details of purchase, total purchase amount, car delivery date, customer number, customer name, customer address, customer email, car stock number, car registration number, make, model, colour, body type, VIN number, engine number, manufacture date, manufacturer code, manufacturer name, manufacturer contact details)
Notes:
1. One car purchase contract is a legally binding agreement between one customer and a car offered for sale.
2. A car is identified by the car stock number and not the car registration number.
3. A car is manufactured by a car manufacturer.
4. There are no repeating groups associated with a car purchase contract.
Convert the set of entities above as set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the above un-normalised relation. You must use the Finkelstein methodology as used in the study book and tutorials.

SECTION C (SQL) (30 marks)
MARKING CRITERIA
1. Four marks awarded for each correct SQL statement and one mark for the output.
2. Alternative approaches to the model answer could be accepted unless they do not follow the rules / requirements set out in the specification, are poorly optimised or are poorly constructed (SQL).
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.
4. There are 6 questions for the total of 30 marks.
For each question, four marks will be awarded for the SQL and one mark for the correct output.
The following E-R diagram represents the 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 re-run the script to reset the tables to their default state.

Write SQL queries to solve the following specifications.
The questions are challenging most requiring a number of tables or/and nested queries. When solving the question it is best not to try and write the solution as a single activity. Instead try and write a separate query to solve each of the part and once you understand the data and the results, rewrite the query into a single solution.

1) Display the full name (firstname space lastname) of customers and the number of times they have placed an order if they have placed more than one order. The resulting list should display full names of relevant customers with the column heading “Full name” and in ascending order.
2) Display the category and maximum retail price for books in each category. Include only those categories whose category name begins with C and the maximum retail price is over $50. Rename the calculated field ‘Maximum Retail Price’. Please ensure that the maximum retail price is formatted this way: ‘$99.99’
3) Using a subquery, display the book category and the average retail price in all categories where the average retail price is less than the highest average retail price of books for all the categories. Sort the resulting set in category order ascending.
4) Using a nested subquery, display book title and cost for all books which belong to the category of the least expensive book. Please ensure that the title of the book is displayed in uppercase and the cost is formatted this way: ‘$99.99’.
5) Using a three table join, display the book title, category, profit and the first and last names of book authors for all the books published before 1 January 2005. Profit is a calculated field which is calculated as (retail – cost). Rename the calculated field as ‘PROFIT’. Round the profit calculation to the nearest full value. Order the result set in the descending order of calculated profit.
6) Using a set operator, display the ISBN of all the books that have never been ordered.