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

We’ll write customized paper for you.
Get Upto 30% OFF on your Order!

Order Now Whatsapp Now

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

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

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.

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.

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.

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.

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).

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