An er diagram for the system

Assignment Help PL-SQL Programming

Reference no: EM13141562

A medical practice in Brisbane 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.

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)
Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. You must use the Finkelstein methodology as used in the study book and tutorials.

STUDENT (student number, name, address, email, ((course code, course name, description, grade, ((lecturer name, lecturer contact number, date assigned)) )), number of courses enrolled, student contact phone)

1. Each student is identified by a student number.
2. A student undertakes multiple courses and each course comprises multiple lecturers.
3. The attribute 'grade' is achieved by a student in a course.
4. The attribute 'date assigned' represents the date when the lecturer is assigned to the course.
SECTION C (SQL)
In this question, you will use the CAR HIRE database. The CAR HIRE database including
appropriate data will be made available on the USQ Oracle server. You may query any of these tables but may not make changes.

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 database for you.

The table descriptions appear below, including the column names and data types.

Write SQL queries to solve the following specifications. Provide the queries and the output.
1. Display full details of all customers where (a) customer name starts with 'S' or ends with 'CO' and (b) county has not been specified. Order the output by customer name in ascending order.

2. Using a set operator, find out all customer numbers who have not made any bookings.

3. Display the car registration, cost, model name, model description, car group name and rental charge per day for each car bought after 31 December 2011.

4. Display the average cost of cars for each car group where the average cost is greater than $50,000.

5. Display the total number of bookings made for the most expensive car.

6. Display the car registration and model name for all cars that are more than eighteen months old.

7. Display all the bookings made in the year 2012 and reserved by 'JANE B'.

8. Display the total amount due for the bookings made on the month of February in the year 2013. Exclude the amount that has not been paid.

Reference no: EM13141562

Questions Cloud

Calculate end-of-year balanced for the fund balance : Calculate end-of-year balanced for the Fund Balance and Budgetary Fund Balance accounts that would be reported on the Village’s balance sheet prepared as of June 30, 2010. Show all necessary work.

How many possible seating arrangements are there for group : Six employees who have formed a car pool. If only four of these employees can drive, how many possible seating arrangements are there for the group?

How many milliliters of 0.100 m naoh(aq) would be needed : How many milliliters of 0.100 M NaOH(aq) would be needed to completely neutralize 50.0 milliliters of 0.300 M HCl(aq)

Explain why accounting treatment is different in the fund : Explain why the accounting treatment is different in the general Fund and governmental activities general journals.

An er diagram for the system : 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.

What are halothane : what are halothane and how exactly does it work. When overdose with halothane what is the antidote that can be used and how does the it work?

Net amount required to retire a bond before maturity : The net amount required to retire a bond before maturity (assuming no call premium and constant interest rates) is the:

Give the solution to power factor : The solution to Power Factor, Two single-phase loads are connected in parallel across a 415 V, 50 Hz supply. One load is rated 130 kW at a power factor of 0.75

Find item selected for inspection is classified as defective : The company has evidence that its line produces %0.9 of noncorforming items. What is the probability that an item selected for inspection is classified as defective?

User Account

All Pages