Semester Examinations
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 1 of 23
INSTRUCTIONS SHEET
1. Write your answers in the answer spaces
oxes provided for
all the questions in the examination paper.
2. This examination comprises four (4) parts, Part A, B, C and D.
3. Students should answer ALL questions in Parts A and B.
4. In Part C, students should answer five (5) of the seven (7)
questions. If more than five questions are answered, only the
first five will be marked.
5. In Part D, students should answer two (2) of the three (3)
questions. If all three questions are answered, only the first
two will be marked.
6. Write clearly, use headings or subheadings. Example: Part B
– Question 2.
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 2 of 23
PART A 10 MARKS
APPLICATION QUESTIONS
Students are to answer ALL questions from this part.
Questions 1 and 2 in this part relate to the ER model given below. The ER model
illustrates the entities and relationships for a large bank. Customers have accounts.
Accounts have transactions. Examine the ER model below and answer all the
questions that follow.
ER model
ACCOUNT
AccountID
AccountName
AccountType
Cu
entBalance
CUSTOMER
CustomerID
CustomerName
PostalAddress
(Street, City,
State, PostCode)
{Phone}
TRANSACTION
TransactionID
TimeStamp
TransactionType
Amount
d
SAVINGS
MonthlyFee
CREDIT CARD
CreditLimit
HOME LOAN
InterestRate
owns has
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 3 of 23
Question 1 2 Marks
Data Modelling
According to the ER model given above, answer either yes or no to the following
questions:
(a) Can an account be both a savings account and a credit card account?
(0.5 mark)
(b) Does every customer have an account? (0.5 mark)
(c) Is it possible for an account to have no transactions? (0.5 mark)
(d) Are there any other account types besides savings, credit card and home loan?
(0.5 mark)
Your answer:
Your answer:
Your answer:
Your answer:
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 4 of 23
Question 2 4 Marks
Converting ER models
Convert the ER model given at the start of 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 your relations. You should write your relations in either format shown
elow:
Student (StudentID, StudentName, DateOfBirth)
Enrolment (EnrolmentID, StudentID, DateOfEnrolment)
or:
Student (StudentID, StudentName, DateOfBirth)
Enrolment (EnrolmentID, StudentID, DateOfEnrolment)
Foreign key (StudentID) references Student
Question 2 continued over next page
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 5 of 23
Question 2 (continued)
Answer:
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 6 of 23
Question 3 4 Marks
Relational model and Normalization
The following table identifies the information that doctors in a hospital made
treatments for patients. You may assume that the combination of PatientID, DoctorID
and TreatDate uniquely identify each row/tuple in this relation
Treatment
PatientID PatientName DoctorID DoctorName TreatDesc TreatDate
P101 Phil London D001 Peter
Peterson
Take panadol XXXXXXXXXX
P101 Phil London D002 Tennis Bonn Brain surgery XXXXXXXXXX
P102 Shirley Wu D001 Peter
Peterson
Take
antibiotics
XXXXXXXXXX
P102 Shirley Wu D003 Amanda Lee Heart surgery XXXXXXXXXX
P103 Victor Smith D004 John Lee Physio-
therapy
XXXXXXXXXX
P104 Sam Sydney D002 Tennis Boon Blood-
transfusion
XXXXXXXXXX
(a) On the above table, if we want to delete a record related to the treatment of the
patient – Victor Smith, what kind of anomaly will happen and why? (1 mark)
Question 3 continued over next page
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 7 of 23
Question 3 (continued)
Answer:
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
(b) What is the highest normal form (e.g. 1NF, 2NF, 3NF etc) that the relation
Treatment satisfies? Explain your answer. (1 mark)
Answer:
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
(Question 3 continued over page)
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 8 of 23
(Question 3 continued)
(c) Decompose the relation Treatment into a set of relations which satisfy 3NF.
(2 marks)
Answer:
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 9 of 23
PART B 10 MARKS
STRUCTURED QUERY LANGUAGE QUESTIONS
Students are to answer ALL questions from this part.
Each question is worth two (2) marks (2 x 5 = 10 marks).
Formulate SQL queries to answer the following information requests. Use the
elations AGENT, PROPERTY, CLIENT, and VIEWING provided below. These
elations describe clients viewing properties that they intend to rent. These properties
are managed or looked after by specific agents in a property management company
as shown in the tables.
Note that:
• You are asked to provide a general solution to each request. If the database
contents change, each of your queries should continue to answer the
information requested co
ectly.
• Simple queries are prefe
ed; if your queries are unnecessarily complex you
may lose marks.
• Your queries should be able to generate the same data and column names as
shown in the result table for each request.
• You are not required to sort the results in any order unless requested.
• State any assumptions that you make to clarify your understanding of the
information request.
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 10 of 23
TABLES
AGENT
AAggeennttNNoo FFNNaammee LLNNaammee PPoossiittiioonn PPhhoonnee
100 Michael Wu Manager XXXXXXXXXX
120 Ann Beech Assistant XXXXXXXXXX
140 David Ford Supervisor XXXXXXXXXX
160 Mary Howe Supervisor XXXXXXXXXX
180 Susan Hall Manager XXXXXXXXXX
200 Julie Lee Assistant XXXXXXXXXX
Notes about the Agent table
• Each row describes an agent.
• The primary key is AgentNo.
PROPERTY
PP
ooppee
ttyyNNoo SStt
eeeett SSuu
uu
RReenntt AAggeennttNNoo
P14 16 Holhead Frenchville XXXXXXXXXX
P94 6 Argyll St North Rocky XXXXXXXXXX
P4 6 LawrenceSt South Rocky 335
P36 2 Manor Rd South Rocky XXXXXXXXXX
P21 18 Dale Rd Frenchville XXXXXXXXXX
P16 5 Novar Dr South Rocky XXXXXXXXXX
Notes about the Property table
• Each row describes a property under management by an agent.
• The primary key is PropertyNo.
• AgentNo is a foreign key reference to the Agent table; it indicates the ID of the
agent who is managing the property.
CLIENT
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 11 of 23
CClliieennttNNoo FFNNaammee LLNNaammee PPhhoonnee
C76 John Kay XXXXXXXXXX
C56 Aline Stuart XXXXXXXXXX
C74 Mike Ritchie XXXXXXXXXX
C62 Mary Tegear XXXXXXXXXX
Notes about the Client table
• Each row describes a client.
• The primary key is ClientNo.
VIEWING
CClliieennttNNoo PP
ooppee
ttyyNNoo VViieewwDDaattee CCoommmmeenntt
C56 P14 24-May-12 Too small
C76 P4 20-April-12 Too remote
C56 P4 26-May-12
C62 P14 14-May-12 No dining room
C56 P36 28-April-12
C74 P16 15-April-12 OK
Notes about the Viewing table
• Each row describes a viewing inspection that has been made by a client.
• The primary key is the combination of ClientNo, PropertyNo and ViewDate.
Note that the same client may have another look at the same property.
• PropertyNo is a foreign key reference to the Property table.
• ClientNo is a foreign key reference to the Client table.
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 12 of 23
Question 1
List details of the agents who have not been a
anged to manage any of properties.
(2 marks)
AgentID FName LName Position Phone
100 Michael Wu Supervisor XXXXXXXXXX
200 Julie Lee Assistant XXXXXXXXXX
Answer:
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
………………………………………………………………………….
Question 2
How many properties does each agent manage? Note: You will need to use the
count() function. (2 marks)
AgentID N
PropertyManaged
120 2
140 1
160 1
180 1
Question 2 continued over next page
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 13 of 23
Question 2 (continued)
Answer:
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
……………………………………………………………………
Question 3
List the details for those properties that have been viewed and have the comment
OK. (2 marks)
PropertyNo Street Subu
Rent AgentID
P16 5 Novar Dr South Rocky XXXXXXXXXX
Answer:
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 14 of 23
Question 4
Which property has the most expensive rent? Note: You will need to use the max()
function. (2 marks)
PropertyNo Street Subu
Rent AgentID
P14 16 Holhead Frenchville XXXXXXXXXX
Answer:
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
…………………………………………………………………………………………………
PAST EXAM
DATABASE DISIGN & DEVELOPMENT — COIT20247
Page 15 of 23
Question 5
List