COIT20247 Database Design and Development - Take Home Examination

PART A                                                                                                                    13 MARKS

DATA MODELLING QUESTIONS

Students are required to answer ALL questions from this part.

Question 1                                                                                                                    5 Marks

The ER model, provided below, contains errors and is incomplete.  You need to identify the errors/omissions, rectify them, and then redraw the diagram, so that the redrawn ER model illustrates the entities and relationships for a Strata unit model with the following requirements:

• a building can have many strata units
• each building must have its address recorded
• a strata unit is situated in one building only
• an owner can own many strata units
• an owner can have many contact numbers
• a strata unit can be owned by many owners
• each owner’s percentage owning of the strata unit must be recorded
• building, owner and strata unit are identified by their respective ids

Note: If you have any additional assumptions, mention them along with your redrawn diagram.

ER model (Incomplete):

Question 2                                                                                                                    5 Marks

Converting ER models

Convert your redrawn ER model for Question 1 in Part A into a set of relations that satisfy Third Normal Form (3NF).  You do not need to show your workings.  You do not need to justify that they are in 3NF at this stage.  You do not need to show sample data.  Just show/write the relations.  You can write your relations in either format shown below:

Student (StudentID, StudentName, DateOfBirth)

Enrolment (EnrolmentID, StudentID, DateOfEnrolment)

or:

Student (StudentID, StudentName, DateOfBirth)

Enrolment (EnrolmentID, StudentID, DateOfEnrolment) Foreign key (StudentID) references Student

Question 3                                                                                                                    3 Marks

Relational model and Normalisation

An incorrect relation NursingSkill has been shown below. The primary key of the relation is (NurseId, SkillId). Examine the relation and answer the questions that follow.

Note: A nurse can have many skills.  A nurse is identified by NurseId and a skill is identified by SkillId

NursingSkill

NurseId	Name	SkillId	SkillName
N001	John Smith	S1	Wound Management
N001	John Smith	S2	Blood sample collection
N002	Mary Smith	S1	Wound Management
N003	Rose Miller	S1	Wound Management

 

1. Explain the ‘insertion anomaly’ that exists in the above relation by providing an example.                                                                                                                                                (1 mark)
2. What is the highest normal form that NursingSkill relation satisfies and why?  (1 mark)
3. Normalise NursingSkill relation into a set of relations that satisfy 3NF using the format as follows:

Customer (CustomerID, CustomerName)

Order (OrderID, Amount, Date, CustomerID)

Foreign key (CustomerID) references Customer (1 mark)

PART B                                                                                                                    10 MARKS

STRUCTURED QUERY LANGUAGE QUESTIONS

Students are required to answer ALL questions from this part.

Each question is worth two marks (5 x 2 = 10 marks).

In this Part B, the relations/tables from your assignment2 are used and the relationship page has been provided for your reference.  Formulate SQL queries to answer the following information requests.

Tables

Invoice
InvoiceId	InvoiceDate	Amount	LeaseBookingId
1	1/04/2021	$1,140.00	1
2	2/04/2021	$900.00	2
3	3/04/2021	$1,200.00	3

LeaseBooking
LeaseBookingId	BookingDate	TheatreId	SurgeonId
1	1/04/2021	1	1
2	2/04/2021	1	2
3	3/04/2021	2	1

Payment
PaymentID	PaymentDate	Amount	InvoiceId
1	1/04/2021	$1,000.00	1
2	2/04/2021	$900.00	2
3	2/04/2021	$100.00	1
4	3/04/2021	$700.00	3

Person
ID	Name	Email	ContactNumber
1	John Lewis	[email protected]	93245000
2	Lisa Major	[email protected]	93245002
3	Mary Carpenter	[email protected]	93245003
4	Amy Stone	[email protected]	93245005
5	Daniel Busker	[email protected]	93245007
6	Panuwat Puri	[email protected]	93245009

StaffDuty
LeaseBookingId	SupportStaffId
1	3
1	4
2	3

SupportStaff
ID	Position	Wages
3	Nurse	$200.00
4	DermNurse	$240.00
5	PlasticNurse	$250.00

Surgeon
ID	Specialty
1	Cardiology
2	Dermatology
6	Opthomology

Theatre
ID	TheatreName	CostPerDay	RoomNumber
1	East	$700.00	202
2	West	$740.00	707
3	North	$680.00	705

Note:

• You need to provide a general solution to each request. If the database contents change, each of your queries should continue to provide the information requested
• Simple queries are preferred; if your queries are unnecessarily complex you may lose
• For the given sample data, your queries should be able to generate the same data and column names as shown in the result table for each
• You are not required to sort the results in any order unless
• State any assumptions that you make to clarify your understanding of the information request.

Question 1                                                                                                                   2 Marks

Which surgeon(s) have not booked any lease-booking? Display the results in descending order of the surgeon’s name.

SurgeonId	Name
6	Panuwat Puri

select s.id,p.Name from  Person p, Surgeon s  where p.id = s.id and ( select s.ID not in (select l.SurgeonId from LeaseBooking l order by l.SurgeonId DESC ));

Question 2                                                                                                                   2 Marks

Which surgeons have done more than one (1) lease-booking? Display the SurgeonId, surgeon name and number of bookings.

SurgeonId	Name	NumBookings
1	John Lewis	2

select s.id ,p.Name,count(l.SurgeonId) as NumBookings   from Surgeon s join LeaseBooking l

on s.id = l.SurgeonId  inner join Person p on p.id = s.Id  Group by l.SurgeonId HAVING COUNT(l.SurgeonId  ) > 1;

Question 3                                                                                                                   2 Marks

Display the details of theatres and number of lease-bookings that have the lowest number of lease-bookings.

TheatreId	TheatreName	NumBookings
2	West	1

select l.TheatreId, t.TheatreName, count(l.TheatreId) as NumBookings from Theatre t join LeaseBooking l on t.id = l.TheatreId Group by l.TheatreId Having Count(l.TheatreId) <=1

Question 4                                                                                                                   2 Marks

Display the cost per day for the theatres that have the name ending with ‘t’. Display the theatre name and cost per day in ascending order of the cost per day.

TheatreName	CostPerDay
East	$700.00
West	$740.00

select t.TheatreName, t.CostPerDay  from Theatre t where TheatreName Like "%t"

Question 5                                                                                                                   2 Marks

How many support staff are there in each of the listed position?  Display the position and number of support staff in descending order of the latter (number of support staff).

Position	NumStaff
PlasticNurse	1
Nurse	1
DermNurse	1

select s.Position, count(s.id) as NumStaff  from SupportStaff s Group by s.Position order By  s.Position DESC

PART C                                                                                                                    12 MARKS

SHORT-ANSWER THEORY QUESTIONS

Students are required to answer ALL questions from this part.

Each question is worth two marks (6 x 2 = 12 marks).

Question 1                                                                                                                   2 Marks

Briefly explain ‘Modification Anomaly’ and provide a suitable example.

Question 2                                                                                                                   2 Marks

Briefly explain ‘Deadlock’.

Question 3                                                                                                                   2 Marks

A data warehouse is said to be a subject-oriented, integrated, time-variant and non-updatable collection of data. Give one or two sentences describing each of these.

Question 4                                                                                                                   2 Marks

How does a distributed database differ from a decentralised database?

Question 5                                                                                                                   2 Marks

Briefly explain ‘Lost update’ and provide a suitable example.

Question 6                                                                                                                   2 Marks

List all (six) properties of ‘Relation’ and explain the requirement of single (atomic) value in the intersection of row and column through an example.

End of Questions