Database, 3nf normalization, mapping, ER diagram and sql work

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

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.

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