DarwinCom Pty Ltd is made up of a number of departments that manage none or more projects

Charles Darwin University
Final Examination
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
Family Name
Given Name/s
Student Number
Teaching Period Semester 1, 2019
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.

THIS EXAMINATION IS PRINTED
DOUBLE-SIDED.

THIS PAGE HAS BEEN INTENTIONALLY
LEFT BLANK.
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.
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
(25 marks in total)
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.
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 2: Relationships
(15 marks in total)
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.
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 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 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 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 4: SQL statements
(20 marks in total)
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.
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.
Question 5: Views and Granting
(15 marks in total)
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.
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.
FirstName LastName Suburb Reg No Make Year Date Taken Date
Returned
Anna Thanh Coconut
Grove
NMT667 Mazda 2010 2/09/2011 3/09/2011
d) As an owner of this view GRANT permission to user s989232 to enable him/her full access
rights.
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.
Appendix A
Automobile
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