WARNING
This material has been reproduced and communicated to you by or on behalf of Charles Darwin University in accordance with section 113P of the Copyright Act 1968 (Act).
The material in this communication may be subject to copyright under the Act.
Any further reproduction or communication of this material by you may be the subject of copyright protection under the Act.
Do not remove this notice
Family Name
Given Names
Student Number
Teaching Period
FINAL EXAMINATION
DURATION
Reading Time:
Writing Time:
BIS234 – Database Concepts
Semester 1, 2017
10 minutes 180 minutes
INSTRUCTIONS TO CANDIDATES
EXAM CONDITIONS
You may begin writing from the commencement of the examination session. The reading time indicated above is provided as a guide only.
This is a RESTRICTED OPEN BOOK examination
No calculators are permitted
One A4 sheet of handwritten single-sided notes permitted No dictionaries are permitted
No additional printed material is permitted 1 x 16 Page Book 1 x Scrap Paper
ADDITIONAL AUTHORISED MATERIALS
EXAMINATION MATERIALS TO BE SUPPLIED
Semester 1, 2017 FINAL EXAMINATION Page 1 of 8 BIS234 – Database Concepts
THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOT PERMITTED TO BE REMOVED FROM ANY EXAMINATION VENUE IN ANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTED DOUBLE-SIDED.
THIS EXAMINATION IS PRINTED DOUBLE-SIDED.
THIS PAGE HAS BEEN INTENTIONALLY LEFT BLANK.
Semester 1, 2017 FINAL EXAMINATION Page 2 of 8 BIS234 – Database Concepts
THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOT PERMITTED TO BE REMOVED FROM ANY EXAMINATION VENUE IN ANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTED DOUBLE-SIDED.
Problem Solving Questions
Total Number of Marks for this section: 100 Marks
This exam should be answered in the Answer Booklet provided. ALL Question MUST be answered.
Marks for each question are indicated
Question 1: ER-Diagrams (20 Marks)
For each of the below scenarios draw a separate E-R diagram including attributes, cardinalities and identifiers when applicable.
a) Customers identified by a CUST-NAME and have an ADDRESS buy Items from a store. Items are identified by an ITEM-NO and have a DESCRIPTION. The QTY-BOUGHT-OF-AN-ITEM of an item by a customer is recorded. Each customer buys a specific item. (5 points)
b) Projects are identified by a PROJECT-NO and have BUDGETS. Projects are managed by a department. Each department is identified by a DEPARTMENT-NAME and has a FLOOR-NO. Each department manages at least one project and a project can be managed by 2 departments (5 points)
c) An order with a unique ORDER-NO and ORDER-DATE can be made for any number of parts (identified by ITEM-NO and with a COLOR). QTY-ORDERED is the amount of each part ordered. Orders are made to three suppliers (each is identified by a SUPPLIER-NAME and has one ADDRESS). (5 points)
d) A fault occurs on one item of equipment. A log book contains FAULT-NO, FAULT-DATE and FAULT-DESCRIPTION. Each item of equipment has a unique EQUIP-NO and an EQUIP- DESCRIPTION and a TYPE. Each such item is located in one building, which has a unique BUILDING-NAME and one ADDRESS. (5 points)
Semester 1, 2017 FINAL EXAMINATION Page 3 of 8 BIS234 – Database Concepts
THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOT PERMITTED TO BE REMOVED FROM ANY EXAMINATION VENUE IN ANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTED DOUBLE-SIDED.
Question 2: Relationships – 20 marks
Consider the following 3NF relations about a sorority or fraternity: MEMBER(Member_ID, Name, Address, Dues_Owed) OFFICE(Office_Name, Officer_ID, Term_Start_Date, Budget) EXPENSE(Ledger_Number, Office_Name, Expense_Date, Amt_Owed) PAYMENT(Check_Number, Expense_Ledger_Number, Amt_Paid) RECEIPT(Member_ID, Receipt_Date, Dues_Received) COMMITTEE(Committee_ID, Officer_in_Charge) WORKERS(Committee_ID, Member_ID)
a) Foreign keys are not indicated in these relations. Decide which attributes are foreign keys and justify your decisions. (6 marks)
b) Draw an E-R diagram for these relations, using your answer in part a. (6 marks)
c) Explain the assumptions you made about cardinalities in your answer to part b. Explain why it
is said that the E-R data model is more expressive or more semantically rich than the relational data model. (8 marks)
Question 3: Vehicle Database SQL statements – 20 marks
Using the vehicle database in Appendix A, write SQL statements for the following queries. The expected answer is given in each case.
a) Find the cars that were used by Employee’s Department as services and sort by Model. (5 marks)
b) Find all employees whose car year is 2009. (5 marks)
c) Find the number of cars used by all employees who are not managers. (5 marks)
d) Find the employees who have taken a Toyota car. (5 marks)
Semester 1, 2017 FINAL EXAMINATION Page 4 of 8 BIS234 – Database Concepts
THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOT PERMITTED TO BE REMOVED FROM ANY EXAMINATION VENUE IN ANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTED DOUBLE-SIDED.
Question 4: E-R Diagrams – 15 marks
Draw an E-R diagram for the scenario below.
A restaurant chain has several store locations in a city (with a name and zipcode stored for each), and each is managed by one manager. Managers manage only one store. Each restaurant location has its own unique set of menus. Most have more than one menu (e.g., lunch and dinner menus). Each menu has many menu items, and items can appear on multiple menus, and with different prices on different menus.
Question 5: ERD and Relational Schema – 10 marks
Figure 1 shows an ER diagram for a university dining services organization that provides dining services to a university.
a) Transform the diagram to a set of relations and develop a relational schema (5 marks)
b) Find the functional dependencies in the relations and determine their normal forms (5 marks)
Figure 1 ER Diagram for a university dining services (ref: Modern Database Management, Hoffer et al)
Semester 1, 2017 FINAL EXAMINATION Page 5 of 8 BIS234 – Database Concepts
THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOT PERMITTED TO BE REMOVED FROM ANY EXAMINATION VENUE IN ANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTED DOUBLE-SIDED.
Question 6 Mapping – 15 marks SHIPPING MANIFEST
Shipment ID Origin: Destination:
00-0001 Boston Brazil
Shipment Date: 01 /10/2010 Expected Arrival: 01/14/2010 Captain: 002-15
Henry Moore
Shipment Total:
Item Number
Type
Description
Weight
Quantity
Total WEIGHT
3223
BM
Concrete Form
500
100
50,000
3297
BM
Steel Beam
87
2,000
174,000
224,000
Figure 2: (ref: Modern Database Management, Hoffer et al)
Based on figure 2 above:
a) Draw a relational schema and show the dependencies on it. (5 marks)
b) In what normal form is this relation? Prove your answer by showing how you reached the
solution (5 marks)
c) Decompose SHIPPING MANIFEST into a set of 3NF relations. Show all your work (5 marks)
Semester 1, 2017 FINAL EXAMINATION Page 6 of 8 BIS234 – Database Concepts
THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOT PERMITTED TO BE REMOVED FROM ANY EXAMINATION VENUE IN ANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTED DOUBLE-SIDED.
Vehicle
Employee Vehicle Table
Appendix A
Reg No
Make
Model
Year
Colour
BRG446
Ford
Meteor
2009
White
VRG655
Bmw
Coupe
2008
Blue
NMT667
Madza
Delivery Van
2010
Green
CCT899
Toyota
HiLuxe
2009
Red
FGR122
Mitsubishi
Magna
2007
Purple
Employee Table
Employee_No
FirstName
LastName
Department
118
Riley
Griffin
Business
123
Bryn
Underwood
Information Tech
156
Anna
Thanh
Administration
166
Justin
Brownworth
Business
120
Sarah
McDonald
Services
134
Tammy
Hubber
Manager
Employee_veh
Reg No
Employee_No
Date Taken
Date Returned
A12
BRG446
118
1/09/2011
3/09/2011
A13
NMT667
156
2/09/2011
3/09/2011
A14
CCT899
166
2/09/2011
4/09/2011
A15
FGR122
134
3/09/2011
5/09/2011
A16
BRG446
118
4/09/2011
10/09/2011
A17
NMT667
166
5/09/2011
10/09/2011
A18
NMT667
134
11/09/2011
15/09/2011
A19
FGR122
166
12/09/2011
15/11/2011
A20
NMT667
118
14/11/2011
15/11/2011
Semester 1, 2017
FINAL EXAMINATION BIS234 – Database Concepts
Page 7 of 8
THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOT PERMITTED TO BE REMOVED FROM ANY EXAMINATION VENUE IN ANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTED DOUBLE-SIDED.
Automobile
Customer Automobile Table
Appendix B
Reg No
Make
Model
Year
Colour
BRG446
Ford
Meteor
2009
White
VRG655
Bmw
Coupe
2008
Blue
NMT667
Madza
Delivery Van
2010
Green
CCT899
Toyota
HiLuxe
2009
Red
FGR122
Mitsubishi
Magna
2007
Purple
Customer Table
Employee_No
FirstName
LastName
Suburb
A118
Mohamed
Griffin
Nightcliff
A120
Ali
McDonald
Brinkin
A123
Albert
Underwood
Rapid Creek
A134
Sammy
Smith
Casuarina
A156
Anna
Thanh
Coconut Grove
A166
Justin
Brownworth
Nakara
Employee_veh
Reg No
Employee_No
Date Taken
Date Returned
B-12
BRG446
A118
1/09/2011
3/09/2011
B-13
NMT667
A156
2/09/2011
3/09/2011
B-14
CCT899
A166
2/09/2011
4/09/2011
B-15
FGR122
A134
3/09/2011
5/09/2011
B-16
BRG446
A118
4/09/2011
10/09/2011
B-17
NMT667
A166
5/09/2011
10/09/2011
B-18
NMT667
A134
11/09/2011
15/09/2011
B-19
FGR122
A166
12/09/2011
15/11/2011
B-20
NMT667
A118
14/11/2011
15/11/2011
B-12
BRG446
A118
15/11/2011
B-13
NMT667
A156
16/11/2011
B-14
CCT899
A166
17/11/2011
Semester 1, 2017 FINAL EXAMINATION Page 8 of 8 BIS234 – Database Concepts
THIS EXAMINATION PAPER AND SUPPLIED MATERIALS ARE NOT PERMITTED TO BE REMOVED FROM ANY EXAMINATION VENUE IN ANY CIRCUMSTANCE. THIS EXAMINATION IS PRINTED DOUBLE-SIDED.