COIT20247 Database Design and Development Assignment

Get Expert's Help on Structured Query Language Questions

PART A                                                                                                                    13 MARKS


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

NoteIf 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 (EnrolmentIDStudentID, DateOfEnrolment)


Student (StudentID, StudentName, DateOfBirth)

Enrolment (EnrolmentIDStudentID, 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


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


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.


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
LeaseBookingId BookingDate TheatreId SurgeonId
1 1/04/2021 1 1
2 2/04/2021 1 2
3 3/04/2021 2 1
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
ID Name Email ContactNumber
1 John Lewis 93245000
2 Lisa Major 93245002
3 Mary Carpenter 93245003
4 Amy Stone 93245005
5 Daniel Busker 93245007
6 Panuwat Puri 93245009
LeaseBookingId SupportStaffId
1 3
1 4
2 3
ID Position Wages
3 Nurse $200.00
4 DermNurse $240.00
5 PlasticNurse $250.00
ID Specialty
1 Cardiology
2 Dermatology
6 Opthomology
ID TheatreName CostPerDay RoomNumber
1 East $700.00 202
2 West $740.00 707
3 North $680.00 705


  • 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,p.Name from  Person p, Surgeon s  where = 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 ,p.Name,count(l.SurgeonId) as NumBookings   from Surgeon s join LeaseBooking l

on = l.SurgeonId  inner join Person p on = 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 = 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( as NumStaff  from SupportStaff s Group by s.Position order By  s.Position DESC

PART C                                                                                                                    12 MARKS


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 

Expert's Answer

Hire Expert Tutors

Get Professional Tutoring at Low Price in Australia


Tutoring Services


Orders Delivered


5 Star Rating


PhD Experts


Amazing Features

Plagiarism Free

Top Quality

Best Price

On-Time Delivery

100% Money Back

24 x 7 Support