This assignment may require a significant amount of work but you should treat it as an exercise in examination preparation as well as an assignment. This assignment covers much of the same ground as the exam and will provide you with a strong indicator of your level of preparedness for the exam. Most of the questions are at examination standard although the data modelling exercise is somewhat more substantial than what would be expected in an examination setting.
SECTION A (Data Modelling)
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 located in the assignment section on Moodle) 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 marked down.
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.
Student Tests Database.
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.
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.
c) An Oracle SQL table create statement for the relation that you think is most critical in this system. This relation must have a primary key and at least one foreign key.
SECTION B (Normalisation)
a) Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. Show your working and entitles for 1NF, 2NF and 3NF. You must use the Finkelstein methodology as used in the study book and tutorials.
LIBRARY (library name, address, opening hours, ((book id, title, category, genre, published date, book entry date, ((author name, author contact)) )), total number of books, ((member id, member name, member contact, join date)), main librarian name, main librarian office, main librarian contact)
Notes:
1. Library name uniquely identifies a library.
2. A library has several books and the number of books can be calculated by this relationship.
3. "book entry date" is the date when the book is entered into the library system.
4. Each book has one or many authors.
5. There are also many members of a library and always one main librarian.
SECTION C (SQL)
Below is a reproduction of the ERD for the JustLee books database. It should help you navigate the tables in the database. The database script to create the tables is located on the Moodle site under the assignment specifications. If you have run a version of the script earlier in the semester please run it again to ensure that you are using the correct version of the tables. The ERD diagram may not necessarily have all the fields listed so you might have to refer to the description of the table by using the DESCRIBE command in Oracle
The questions in this section are challenging. Most require a number of tables and/or nested queries. When solving each 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 parts and once you understand the data and the results rewrite the query into a solution.
Each question below is worth 5 marks. For each question, provide the SQL queries to meet the question's specifications and the output result of running your query.
1) Display the book title, publisher name, cost and retail of the books for all books that do not have a discount value and have had no sales recorded for them. All money fields should be formatted as $999.99 and all fields should have meaningful names.
2) Display author last name, book title and category for all books where (1) the author's last name starts with a Z or contains a Q anywhere and (2) the book's title contains the word ‘ZMAY'. Order the output by author last name in ascending order.
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) Display the book title, publisher name, author first and last name for all the books that have been co-authored (that is where the book has been authored by 2 or more authors). Order the resulting set by the book title.
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 ‘TOTAL PROFIT'. Round the profit calculation to the nearest full value. Order the result set in the descending order of calculated profit.
6) Display category, book title and retail price for all the books where the retail price of the book is less or equal than the maximum cost of all the books and more than the average cost of all the books. Order the result set by the book category ascending and retail within the category descending order