Part 1 Short answer questions [10 marks total]

1. How does a data dictionary can assist you in implementing your database? Describe two benefits of a data dictionary when implementing a database? Describe one problem that you may encounter if you don’t have a data dictionary of a database when implementing that database?

[4 marks]

2. Describe briefly how normalization will assist in Logical database design. What will be the problem/s of not applying normalization in logical database phase?

[3 marks]

4. Briefly discuss the benefits of having foreign keys and primary keys in a relational database. You may give an example to illustrate your answer.

[3 marks]

Part 2 ER Modelling [30 marks total]

The Canberra Driving School (CDS) was established recently. It has three branches in Canberra. CDS requires your team to design a database system to enable more smooth operation of the driving school. CDS database will record and store the data about each of its clients and instructors. The details stored about clients are: client number, first and last name, street number, street name, suburb, post-code, city, state and type of driving classes a client is enrolled in. Each client can enrol in up to three classes. CDS offers several types of driving classes. Driving classes are managed by administration staff under course development section of CDS. There exist several types of driving classes. These are namely Class C (Trucks), Class B (Mini Trucks) and Class A (normal passenger vehicles). There are different fees for different classes. The clients need to book for each class beforehand by using CDS reservation facilities using CDS website. Details of all reservations performed by customers for driving classes are stored. The reservation details stored are: reservation number, customer first and last name, customer address, class number, reservation date, reservation time, class type, class fee, and instructor name.

The timetable and availability of each instructor is also recorded in CDS database. When a client enrols at CDS he or she is assigned a client number and his/her details are recorded. Every client is provided with a client number as well as the rules of CDS including fees and charges. A client uses his/her client number to book a class. CDS has several staff members in each branch. For each staff member the following data is stored in the database system: staff first and last name, staff number, position, gender, date of birth, name of the section he/she works in, internal telephone number and branch number, and branch address. Each branch can have up to five staff members and five instructors and each branch can have up to five telephone numbers.

Each branch of CDS has up to five instructors assigned to it. For each instructor the following data is stored in the CDS database system: staff first and last name, instructor number, position, gender, date of birth, type of driving classes that an instructor can teach (i.e. Class A, B or C), internal telephone number and branch number. CDS consists of six sections. These sections are: instructor section, information section, course development section, assessment section, enrolment section and administration section.

The information about each section is stored in the CDS database. The information about each section includes the section name, location, telephone and fax number. A CDS client can browse the CDS website or call the CDS branches to find out the details of all classes available for each week. Printed copy of the class timetable is also available from CDS branches.

Timetable of weekly classes are stored in CDS database. Instructors belong to the instructor section. The instructor’s identity is established with their ID number. Instructor ID number is used to retrieve the details of the class that each instructor teach. The details of each class taken by a client are stored in CDS database. These details include: class number, Instructor number, Instructor first and last name, date and time, client first and last name.

If a client is enrolled in a class then the client should make a payment for his/her class before he/she can take that class. The amount of payment depends on the type of class. The payment can be made via the CDS branches. The payment details are: payment number, client number, client first and last name, client address, amount paid, payment date, payment type, class number.

For each payment a receipt is issued. The data about each receipt are: receipt number, client number, client first and last name, client address, amount paid, payment number, payment type, class number.

Client enrolment details are stored. The data about each class enrolment are: class number, enrolment number, class number, class type, class fee, enrolment date, class start date and time, class end date and time, instructor last name, branch number, client first and last name, client address, vehicle number.

CDS has several vehicles that are used by its instruction for driving classes. The details about each vehicle is stored. The data about each vehicle are: vehicle number, registration number, vehicle type, vehicle model, instructor name, branch number. Details of each vehicle used in each class is stored in CDS database. These details are: vehicle number, registration number, vehicle type, vehicle model, instructor last name, branch number, class number, class type, instructor number.

Each vehicle is serviced every six months by a local car dealer. Service details of all vehicles are stored. The data about each service performed on each vehicle are: vehicle number, registration number, vehicle type, service type, service number, service description, date of service, service fee.

A payment is made for each service for each vehicle. Service payments are made by CDS accounting section. Service payment details are: service payment number, vehicle number, vehicle registration number, amount paid, payment date and first name and last name of the staff who made the payment.

Instructors are required to sit for instructor examination every year to renew their instructor driving licence. Details of instructor licence examinations are recorded in CDS database. These details are: instructor first and last name, instructor number, driving examination date and the result of examination (pass or fail).

You are required to:

(a) Identify and list all entities, attributes and primary keys of the above system. ((Make sure that your data model is normalized to 3NF). You need to include with your data model the details of all assumptions that you have made for your designed database.

[20 Marks]

(c) Draw an E-R diagram of the above system. (Make sure that your E-R diagram is based on your data model that is normalized to 3NF).                                                                                                             [10 Marks]

Part 3 SQL [15 marks total]

Based on the entities, attributes and primary keys of your solution for Canberra Driving School (CDS) in Part 2 of this Take-Home Assessment paper, Write the following queries using SQL:

1. List details of all clients from Canberra that have enrolled in a driving class order by Customer Number.

[3 Marks]

2. How many clients from Canberra have enrolled in a class conducted by an instructor with Instructor Number = 12345?

[2 Marks]

3. List the number of male staff at each branch.

[3 Marks]

4. List the first and last name of all female instructors that have done an instructor examination to renew their instructor driving licence.

[3 Marks]

5. What is the average salary of male staff from Canberra?

[2 Marks]

6. List details of all vehicle that are serviced with a service fee of more than $500.

[2 Marks]

Part 4 Normalization [10 marks total]

The table below lists student/subject, department number and enrolment data. Each student may enrol in many subjects from various departments in a university. Subjects are offered by different departments.

(a) The data in the above table is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies that could occur on this table.

[5 marks]

(b) Design a database to store the data from the above table. Your designed database must be in third normal form (3NF). Draw the E-R diagram of you model in 3NF.

[5 marks]