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
Charles Darwin University Final Examination
Family Name
Given Name/s
Student Number
Teaching Period
Semester 1, 2019
HIT234 – Database Concepts
DURATION
Reading Time:
10 minutes
Writing Time:
180 minutes
INSTRUCTIONS TO CANDIDATES
Answer all the questions. If information appears to be missing from a question, make a reasonable assumption, state it and proceed.
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 double-sided notes permitted
No dictionaries are permitted
ADDITIONAL AUTHORISED MATERIALS
EXAMINATION MATERIALS TO BE SUPPLIED
No additional printed material is permitted
1 x 16 Page Book
Semester 1, 2019 FINAL EXAMINATION Page 1 of 8
HIT234 – 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, 2019 FINAL EXAMINATION Page 2 of 8
HIT234 – 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
For each of the below scenarios draw a separate E-R Diagram including attributes, cardinalities and identifiers when applicable.
a) DarwinCom Pty Ltd is made up of a number of departments that manage none or more projects. Each project is made up of none or more team members. Each team member belongs to one department and zero to one project. One of the team members supervise the other team members on the project.
b) A company has four departments. Each department has one manager. Each department employs staff. Each staff may work for one or more departments. A staff may be supervised by another staff at least.
c) A car insurance company whose customers own one or more cars each. Each car has associated with it zero to many number of recorded accidents.
d) A university registrar has the following entities: Courses (including course number, title, credits, syllabus, and prerequisites); Course offerings, (including course number, year, teaching period, instructors, timings and classroom); Students (including student-id, name, and program); and Instructors (including identification number, name, department, and title). The enrolment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modelled. An instructor could teach in only one course. Each course only runs in one session
e) Employees (identified by EMP-ID, SURNAME, FIRST-NAME, and Date-of Birth) make many orders (identified by ORDER-NO, ORDER-DATE, DES, WUOTED-PRICE) for Customers (Identified by CUST-ID). The orders are for one customer at a time but a customer might have many orders. The orders create many requests (Identified by REQUEST-NO, START-DATE, END-DATE). Those requests might result in few jobs (identified by JOB-NO, COST) and consequently will use different materials (identified by MATERIAL-ID, MATERIAL-DES). The requests are made to one section but a section has many requests.
(25 marks in total)
Semester 1, 2019 FINAL EXAMINATION Page 3 of 8
HIT234 – 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
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) Show which attributes are foreign keys and justify your decisions.
b) Draw an E-R diagram for these relations, using your answer in part a.
(15 marks in total)
c) Explain the assumptions you made about cardinalities in your answer to part b. Explain why it is said that the ER data model is more expressive or more semantically rich than the relational data model.
Semester 1, 2019 FINAL EXAMINATION Page 4 of 8
HIT234 – 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 3: Normalization
(25 marks in total)
a) Assume that at Darwin Furniture products are comprised of components, products are assigned to salespersons, and components are produced by vendors. Also assume that in the relation PRODUCT(Prodname, Salesperson, Compname, Vendor) Vendor is functionally dependent on Compname, and Compname is functionally dependent on Prodname. Eliminate the transitive dependency in this relation and form 3NF relations. (10 marks)
b) The dependency diagram in the Figure below indicates that authors are paid royalties for each book that they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book. (15 marks)
1. Based on the dependency diagram, create a database whose tables are at least in 2NF, showing the dependency diagram for each table.
2. Create a database whose tables are at least in 3NF, showing the dependency diagram for each table.
Semester 1, 2019 FINAL EXAMINATION Page 5 of 8
HIT234 – 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: SQL statements
The following is the relational schema of part of the database that HighTech Pty Ltd recently implemented:
DEPARTMENT (DeptNo, Name, Location)
EMPLOYEE (EmpNo, Name, Street, City, Postcode, HireDate, Salary, DeptNo) LEAVE (EmpNo, FromDate, ToDate)
EMPQUAL (EmpNo, QualID)
QUALIFICATION (QualID, Qualification, Institution)
Using the above schema, write SQL queries that will answer/process the following:
a) List all cities where an employee lives. Only show the city once.
b) What is the lowest salary paid for any employee that lives in the city of Darwin?
c) For each qualification, show the total number of employees that have that qualification. Show qualification id, qualification and the total number.
d) List any employees who are on annual leave on 01/05/2018. Note, date format is #01‐05‐ 2018#. Show employee number only.
e) What is the total salary cost incurred by HighTech for employees in the Marketing department?
f) List the employee(s) with the highest salary. Show employee number and name.
g) List the employee names and all of the institutions that they have studied at.
h) How many employees have no qualifications?
i) Delete all leave rows if the leave ended prior to the start of this year.
j) Increase the salary of all employees by $80.
(20 marks in total)
Semester 1, 2019 FINAL EXAMINATION Page 6 of 8
HIT234 – 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 5: Views and Granting
Using the tables in Appendix A
a) What are the advantages of using VIEW in SQL?
b) Using SQL statement Create a VIEW showing the details of all Customers who have returned an Automobile.
(15 marks in total)
FirstName
LastName
Suburb
Reg No
Make
Year
Date Taken
Date Returned
Mohamed
Griffin
Nightcliff
BRG446
Ford
2009
15/11/2011
Anna
Thanh
Coconut Grove
NMT667
Madza
2010
16/11/2011
Justin
Brownworth
Nakara
CCT899
Toyota
2009
17/11/2011
c) Create a query using the new view to find the customer in Coconut Grove or Bayview.
d) As an owner of this view GRANT permission to user s989232 to enable him/her full access rights.
FirstName
LastName
Suburb
Reg No
Make
Year
Date Taken
Date Returned
Anna
Thanh
Coconut Grove
NMT667
Mazda
2010
2/09/2011
3/09/2011
Semester 1, 2019 FINAL EXAMINATION Page 7 of 8
HIT234 – 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.
Automobile
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
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
Customer Automobile Table
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, 2019 FINAL EXAMINATION Page 8 of 8
HIT234 – 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.