Question 1 (ER/EER Diagram & Transformation)

Question 1 (ER/EER Diagram & Transformation) – 40 marks
Signum Libri (SL) is a Melbourne-based publishing company and you are hired to design its database. This publisher publishes large number of books. For each book, the book ID, title, number of pages and release date are required. In addition, the genre of the book is also stored, in which each book can belong to more than one genre.
For selected title, SL wants to have few excerpts stored in the database. Each excerpt of a book comes from different chapter of the book.
For some title, SL publishes the book in two formats, namely printed book and e-book, while for some other titles only printed book is available. If the book is printed, the information of the number of printed copies is required as well as printed copy price. For e-book, the information of the price is also maintained. In addition, for e-book, SL wants to keep track on the platforms in which the e-book can be viewed and downloaded. For each platform, information about platform name and description is required.
The publisher also wants to store information about the writer of the book. For each writer, a unique writer identifier as well as writer name, address and country of origin is stored. Each book can be written by a group of writer and each writer can write many books published by SL. At this stage, SL does not keep track information of writers who did not write a book for SL.
Each writer is represented by one agent and for this reason SL also needs information about the agents, which is identified by a unique identifier. The name, address and contact number of the agents is also required.
Each SL book has one editor. Each editor edits at least one book, but can edit many books. The information about editors is also required in the database. This includes the unique identifier of the editor, the name, contact detail and information whether she or he is available to edit in any foreign language. Each editor can mentor one or more other editors, but does not have to mentor any. Each mentor can have at most one mentor editor, but does not have to have any.
a) Develop an EER model for the above description. The EER should contain necessary information such as entities, attributes, primary keys, relationships, cardinalities, and participation. Note that you may need to make up some of the attributes for your model. Any assumption should also be stated.
[25 marks]
b) Perform the transformation of the EER model into the Relational tables.
[15 marks]
Page 2 of 7
Question 2 (Normalisation) – 30 marks
a) Given a table below, answer the following questions:
EMPLOYEE-PROJECT
EmployeeNo
Project#
Hours
EmployeeName
ProjectName
ProjectLocation
123456789
1
32.5
John Smith
Major Training
Hobart
123456789
2
7.5
John Smith
Automation
Adelaide
666884444
3
40.0
Ramesh Narayan
Installation
Melbourne
453453453
1
20.0
Joyce English
Major Training
Hobart
453453453
2
20.0
Joyce English
Automation
Adelaide
333445555
2
10.0
Franklin Wong
Automation
Adelaide
333445555
3
10.0
Franklin Wong
Installation
Melbourne
333445555
10
10.0
Franklin Wong
Computerization
Melbourne
333445555
20
10.0
Franklin Wong
Reorganization
Albury
999887777
30
30.0
Alicia Zelaya
Newbenefits
Perth
(i) What is the highest level of normal form for the table?
(ii) Describe the insertion, update and delete anomalies that can happen on the table.
[10 marks]
b) Nip and Tuck Construction Company maintains timesheet for their employees using a sample form shown below. Using normalization technique, identify the database tables for this form. All tables must be at least in BCNF.
Nip and Tuck Construction Company: Time Sheet
Employee No: 124298 Name: Smithers, Thomas Title (Mr, Mrs, Mss, Ms): Mr
Department: Electrics Telephone Extension: 5890
Bank Account No: 12312-45683-58
Date
Job No
Operation
Time
Start
Time
Finish
Time (Hrs)
Worked
01/08/12
3421
Cabling
8.00
12.00
4
02/08/12
3421
Cabling
8.00
12.00
4
02/08/12
3421
Cabling
12.30
16.30
4
03/08/12
2310
Faulty Wiring
8.00
10.00
2
03/08/12
3421
Insulation
10.30
12.30
2
03/08/12
4812
Faulty Wiring
13.00
16.30
3.5
Total Hours Worked:
19.5
SICK LEAVE:
FROM: 04/08/12
TO: 05/08/12
Comments: Medical certificate seen
[20 marks]
Page 3 of 7
Question 3 (Relational Algebra) – 20 marks
BUILDING APARTMENT
BuildingID BNoofFloor BManagerID BuildingID AptNo ANoOfBedrooms
B1 5 M12 B1 41 1
B2 6 M23 B1 21 1
B3 4 M23 B2 11 2
B4 4 M34 B2 31 2
B5 3 M34 B3 11 2
B4 11 2
INSPECTOR INSPECTING
InsID InsName InsID BuildingID DateLast DateNext
I11 Jane I11 B1 15-MAY-2015 14-MAY-2016
I22 Niko I11 B2 17-FEB-2016 17-MAY-2016
I33 Mick I22 B2 17-FEB-2016 17-MAY-2016
I44 Rudy I22 B3 11-JAN-2016 11-JAN-2017
I33 B3 12-JAN-2016 12-JAN-2017
I33 B4 11-JAN-2016 11-JAN-2017
MANAGER
ManagerID MFName MLName MBDate MSalary MBonus
M12 Boris Grant 30-JUN-1980 60000
M23 Austin Lee 30-OCT-1975 50000 5000
M34 George Sherman 11-JAN-1976 52000 2000
Given the set of relations/tables, answer these following questions. (NOTE:
(1) all PKs are underlined and all FKs are written in italics; (2) BManagerID
is a foreign key pointing to MANAGER table)
a) Write the following query in relational algebra expression: display
building id of all buildings that have never been inspected.
[6 marks]
b) Write the following query in relational algebra expression: display
apartment number and the manager name of the building where the
apartments are located. The apartments listed are only those located in
building with 4 level or more.
[7 marks]
c) List the outcome of the relational algebra queries below:
 BuildingID (BUILDING) BuildingID=BuildingID (INSPECTING)
U
 BuildingID (BUILDING) BManagerID=ManagerID (MSalary > 50000) (MANAGER)
[7 marks]
Page 4 of 7
Question 4 (SQL) – 45 marks
The following show the tables and sample data of Bundoora Clinic. PATIENT, DOCTOR and NATUROPATH tables store the list of patients, doctors and naturopaths respectively. PATIENT_DOCTOR table stores the information of the patients and their referring doctors. VISIT table stores information of patient’s visit to the clinic including the medication prescribed to the patient during that visit. CONDITION table lists possible condition that patients can have. Finally, PATIENT_CONDITION table identifies what conditions that each patient has.
NOTE: Primary Key (PK) is underlined, Foreign Key (FK) is printed in italic, PK that is also FK is underlined and printed in italic.
PATIENT
NATUROPATH
PatientID
PatientName
Age
NaturoNo
NaturoNo
NaturoName
NaturoContact
P1
Jake Long
25
NP1
NP1
Glenn Murphy
98562562
P2
Drew Green
35
NP2
NP2
Helena Bond
98659620
P3
Amy Chan
10
NP3
Daniel Rowe
95369485
P4
Lisa Moon
60
NP1
NP4
Adam Auburn
94793459
P5
Henry Tran
55
NP3
NP5
Hasan Sulistyo
95568840
DOCTOR
PATIENT_DOCTOR
DoctorNo
DoctorName
Specialty
DoctorContact
PatientNo
DoctorNo
D1
Ramon Dunn
98698544
P1
D1
D2
Jill Ilic
Oncologist
98695214
P1
D2
D3
Dean Hanson
Cardiologist
90248965
P2
D3
D4
Kunal Reddy
Pediatrician
88963562
P2
D4
D5
Hans Bolte
Perinatologist
98012563
P3
D3
VISIT
VisitNo
VisitDate
AmountPaid
Medication
PatientID
V1
15-Apr-2016
95
Chinese Herbs
P4
V2
16-Apr-2016
75
Anti-Depressant
P1
V3
30-Apr-2016
120
Supplements
P2
V4
13-May-2016
125
P2
V5
15-May-2016
265
Antihistamine
P4
CONDITION
PATIENT_CONDITION
ConditionCode
ConditionName
PatientID
ConditionCode
Notes
C1
Allergy
P1
C1
Antihistamine
C2
Artificial Implants
P2
C1
Chinese Herbs
C3
Hospitalized
P3
C3
5 Years Ago
C4
Under Medical Care
P4
C4
Since Birth
Page 5 of 7
Provide the SQL statements for questions (a) to (e)
(a) Create a view that can be used to list the doctor name and the number of patients referred by the doctor in the database.
[5 marks]
(b) Display the details of all naturopath in the database along with his/her patient(s) (if any). Patient name and age should be displayed. The display should be ordered by naturopath name in ascending order and then the patients should be ordered from the older patients to the younger ones.
[10 marks]
(c) Display the detail of the doctors who have the greatest number of patients in the database.
[10 marks]
(d) Display the names and contact numbers of doctors and naturopaths who have at least one patient in the database.
[10 marks]
(e) Display the top 5 most expensive patient’s visits made in 2016.
[10 marks]
Page 6 of 7
Question 5 (Stored Procedures/Functions &Triggers) – 45 marks
The following show the tables and sample data of Bundoora Clinic. PATIENT, DOCTOR and NATUROPATH tables store the list of patients, doctors and naturopaths respectively. PATIENT_DOCTOR table stores the information of the patients and their referring doctors. VISIT table stores information of patient’s visit to the clinic including the medication prescribed to the patient during that visit. CONDITION table lists possible condition that patients can have. Finally PATIENT_CONDITION table identifies what conditions that each patient has.
NOTE: Primary Key (PK) is underlined, Foreign Key (FK) is printed in italic, PK that is also FK is underlined and printed in italic.
PATIENT
NATUROPATH
PatientID
PatientName
Age
NaturoNo
NaturoNo
NaturoName
NaturoContact
P1
Jake Long
25
NP1
NP1
Glenn Murphy
98562562
P2
Drew Green
35
NP2
NP2
Helena Bond
98659620
P3
Amy Chan
10
NP3
Daniel Rowe
95369485
P4
Lisa Moon
60
NP1
NP4
Adam Auburn
94793459
P5
Henry Tran
55
NP3
NP5
Hasan Sulistyo
95568840
DOCTOR
PATIENT_DOCTOR
DoctorNo
DoctorName
Specialty
DoctorContact
PatientNo
DoctorNo
D1
Ramon Dunn
98698544
P1
D1
D2
Jill Ilic
Oncologist
98695214
P1
D2
D3
Dean Hanson
Cardiologist
90248965
P2
D3
D4
Kunal Reddy
Pediatrician
88963562
P2
D4
D5
Hans Bolte
Perinatologist
98012563
P3
D3
VISIT
VisitNo
VisitDate
AmountPaid
Medication
PatientID
V1
15-Apr-2016
95
Chinese Herbs
P4
V2
16-Apr-2016
75
Anti-Depressant
P1
V3
30-Apr-2016
120
Supplements
P2
V4
13-May-2016
125
P2
V5
15-May-2016
265
Antihistamine
P4
CONDITION
PATIENTCONDITION
ConditionCode
ConditionName
PatientID
ConditionCode
Notes
C1
Allergy
P1
C1
Antihistamine
C2
Artificial Implants
P2
C1
Chinese Herbs
C3
Hospitalized
P3
C3
5 Years Ago
C4
Under Medical Care
P4
C4
Since Birth
Page 7 of 7
a. Write a stored procedure that receives a date as input and displays as the output the list of patient names who visited the clinic on that day.
[15 marks]
b. Write a stored function that uses a patient’s id as input and calculates the total amount paid to date by that patient. In addition, also need to show an SQL statement to display the total amount paid by all of the patients in the database.
[15 marks]
c. A trigger that is activated whenever a new medication given to a patient matches any one of the patient’s allergy list.
[15 marks]
Order Now

Calculate a fair price for your paper

Such a cheap price for your free time and healthy sleep

1650 words
-
-
Place an order within a couple of minutes.
Get guaranteed assistance and 100% confidentiality.
Total price: $78
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?