RECENT ASSIGNMENT

CIS2002 SEMESTER 1, 2015
Assignment 2 specification
Description Marks out of Mode Wtg(%) Due date
ASSIGNMENT 2 100.00 External 25.00 29 May 2015
ASSIGNMENT 2 100.00 On Campus 18.00* 29 May 2015
*On-campus students take practical tests and the weightings for assignments are different due to this.
IMPORTANT INFORMATION
You must submit the assignment electronically by the due date via the Moodle link on the study desk. Instructions will be provided on the course study desk.
If you are unable to complete the whole assignment, submit what you have done rather than nothing.
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 acceptable if you submit neat hand-drawn ERD’s. Preferably 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. There are large number of online drawing tools for modeling that you might want to use.
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) (30 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
5. SQL CREATE TABLE uses an appropriate relation, is syntactically correct and meets the business rules.
6. USQ methodology used throughout.
A medical practice needs to develop a patient appointment system so that the reception staff can easily check doctor schedule, allocate consultation times and manage medical tests for patients. You are required to develop a data model which will allow following information and activities to be recorded.
The system needs to store doctor’s name, address, qualification, contact number and date of joining the practice. A doctor is categorised as either a specialist or general practitioner. For a specialist the system also stores the area of speciality. Both categories of doctors provide consultation to patients. For a patient the system stores patient identifier, name, address, date of birth, gender and medical history. Medical history is stored as a single field of text in the current design.
When a consultation is setup between a patient and a doctor, we record the consultation date, start time, duration and fees. A consultation may result in a referral to a specialist. A referral is stored in the system with the following details: referral date, reason for referral, the specialist being referred and the consultation that led to the referral.
A consultation may also result in requests for one or more tests that are conducted by the practice. It is mandatory to have a consultation by a doctor before a test can be conducted. There are a number of tests such as blood tests and fitness tests that can be administered by the staff employed by the practice. For every test we store test identifier, cost, consultation identifier that requested the test, test date, test result date and a follow up flag in case the client requires further urgent consultation or further tests.
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. (15 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. (20 Marks)
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. (5 Marks)

SECTION B (Normalisation) (40 marks)
MARKING CRITERIA
0. All entitles listed and correctly
1. Relations – no missing relations, appropriate names, no redundant relations.
2. All primary keys present and correctly notated.
3. All foreign keys present and correctly notated.
4. All attributes present.
5. All repeating groups resolved.
6. Derived attributes indicated in brackets.
7. All 2NF and transitive dependencies resolved.
8. All relations correctly notated using USQ methodology.
Examine the sales order for a veterinarian service bellow. Normalise the table design. This is a dual purpose from in that we record the animal details as well as the sale of goods.
a) Using the form as a guide create a list of entities and correctly identify the repeating groups of entities. (10 marks)
b) 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.
(30 marks)

SECTION C (SQL) (30 marks)
MARKING CRITERIA
1. Five marks awarded for each correct SQL statement.
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.
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.

For each question, five marks will be awarded for the SQL.
Write SQL queries to solve the following specifications.
For your submission provide the queries and the output result of running the query.
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 solution.
1) Display the book title and the number of books sold where the profit from the book is more the 70%. The resulting list should display highest quantity of books title sold first in the list. Profit for a book is calculated as (retail – cost) / cost.
2) Display the publisher name, category and average price for books which belong to the either a category of ‘COMPUTER’ or “CHILDREN’ and where the average retail cost for the category is more than 50 dollars. Rename the calculated field ‘AVERAGE_CAT_PRICE’ and format with a leading ‘$’ symbol and rounded to two decimal places.
3) Display the categories and the count of number of books in those categories where the category has the most books then the minimum number of books for all the categories. Sort the resulting set in category order ascending.
4) Display book title, ISBN number and cost for all books which belong to the category with the least number of books in the category. Format the cost field as dollars ‘$’ with two decimal places. Rename the new formatted cost field to ‘COST’ (complex query)
5) Display all the categories and the total amount of profit for each category. Rename the calculated field as ‘CATEGORY_PROFIT’. Category profit is calculated as number of books sold times profit (retail – cost) in that category. Round the profit calculation to the nearest full value. Order the result set in the descending order of highest calculated profit.
6) Display category, book title and retail price for all the books where the retail price of the book is less than the maximum cost of all the books. Order the result set by the book category.