COIT20247 Database Design and Development - Take Home Examination

Assistance on Data Modelling Questions

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