SECTION A (Data Modelling)
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 set up 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:
1.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.
2.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.
3.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)
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.
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.
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.
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.