Question 1: ER Model (30 marks)

You must use only the following notation:

Use of Crows feet, UML, IE notation or any other form is not permitted and will incur a penalty for this question.

A tour company, "Total Escapes" wants to implement a simple database to keep records on some aspects of its operation. They describe key elements of the requirements in the following dot points. Use this information to understand their requirements and draw an ER Diagram.

• Total Escapes has a wide variety of tours on offer to its customers. Each tour can be uniquely identified by a route ID. A tour also has associated with it a name, an estimated duration and a description of the tour itself.

• Customer details are to be recorded in the database, with a unique customer ID generated for each customer. Additional details include a name, date of birth and an email address.

• A Tour run is defined as one instance of a tour, i.e. a tour has a number of tour runs per day. For example, a ‘Melbourne City Tour' runs twice every day, 8:30am and 2:30pm. A number is allocated to each tour run for the day on which it is run. The time the tour is run is also recorded.

e.g.

Tour name  Tour date Tour Number     Tour time

Melbourne   City Tour    1/1/2018 1      8:30 a.m.

Melbourne   City Tour    1/1/2018 2      2:30 p.m.

• A customer can book tour runs. Each booking is given a special Booking ID. The date, price paid and status(paid/unpaid) for the booking is recorded.

• Each tour run requires the usage of exactly one vehicle. Each vehicle can be identified by it registration plates. Other attributes such as vehicle type, capacity and transmission are also recorded.

• A Tour guide also attends a tour run. The tour guide must have a unique certificate number to be recorded in the system. Their name, mobile number and address are also required.

• The driver responsible for operating the vehicle on a tour run can be identified by their D/L number. A name and mobile phone number are also recorded in the database for each driver.

• Some tour guides train other tour guides to manage tours themselves. Each trainee has one supervisor while a supervisor can guide many trainees.

According to the given description, construct an Entity Relationship (ER) diagram for the database, and make assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any concepts in the description that cannot be expressed in the ER diagram.

Do not create any new attributes not already provided in this business description.

Question 2: The Relational Model (15 marks)

2.1 Convert the following ER diagram for a real estate system into a relational schema.

For each relation in your relational database schema, you should:

• Underline a primary key for each relation

• Denote any foreign keys with asterisks(*) in your relations

Yes No

relationName(attribute1, attribute2*)

Relations not expressed in the approved representation are not permitted and will incur a penalty for this question.

Question 3: Relational Design (20 marks)

3.1. The following relation holds data about a car mechanics database:

Mechanics(customerID, customerName, mechanicID, mechanicName, partID, costOfRepair, VIN, make, model)

• A customer has a unique ID and associated with that ID is the customer's name.

• Each mechanic is identified by a unique ID and also has a name recorded in the system.

• A customer's vehicle is identified by its unique Vehicle Identification Number(VIN). A customer can own many vehicles but each vehicle has one registered owner.

• Each vehicle has details such as make and model recorded in the database.

• The estimate for repair costs is calculated based on the vehicle identifier and the part that needs replacing.

3.1.1. List the functional dependencies for the car mechanics relation.

3.1.2. Work out what the Primary Key of the car mechanics relation is and prove it using closure notation.

3.1.3. Using the functional dependencies you have created in 3.1.1., show which normal form the car mechanics relation is in.

3.1.4. Decompose the car mechanics relation into separate relations so that each can be considered as being in BCNF. Show the final schema in full with all primary keys and foreign keys marked appropriately.

Question 4: Short answer (10 marks)

4.1 When should we use ternary relationships / binary relationships?

Consider a medical clinic DBMS. There are 3 entities ‘Doctor', ‘Patient' and ‘Consultation' in the

ER diagram and the system allows for the following actions:

• A doctor can conduct many consultations.

• A patient can have many consultations.

• Each consultation must have exactly one doctor and exactly one patient.

According to the above description, decide which ER diagram below best describes the relationship and would be the most practical to implement. Explain your reasoning.

Responses should be limited to no more than 2 or 3 sentences but this is not a strict requirement. The use of images/diagrams to aid in your explanation is also acceptable.

Question 5: Advanced SQL (25 marks)

The "Big Bank" banking organisation has been upgraded and is shown below:

Customer(ID, firstName, lastName, address, email, phone, dateOfBirth)

Transaction(code, type, dateExecuted, amount)

Account(accNumber, balance, type)

Branch(BSB, phone, streetAddress, town)

Atm(AtmNumber, atmType)

Employee(employeeID, firstName, lastName, address, phone, TFN, dateOfBirth)

performs(code*, accNumber*, ID*)

has(ID*, accNumber*)

registered(accNumber*, BSB*)

locatedAt(AtmNumber*, streetAddress, BSB*)

worksAt(employeeID*, BSB*, occupation)

• Primary keys are underlined and foreign keys are denoted with asterisks (*).

• A customer can have more than one account and an account can be jointly owned by more than one customer.

• An employee can only work at one branch.
• All accounts must be registered with a branch.

Write one SQL query for each question below to extract information from the database. Each question is worth 5 marks. Do not supply the output of the query. Only the SQL query is required for each question.

Hardcoding of identifiers not given in the question is not permitted and will incur a penalty for the question. Only use the information provided in each question.

5.1. Which couple works at the same bank? (hint: They live at the same address and share the same last name)

There should be no repeated pairs of results for your query.

5.2. Using IN or NOT IN operator compose a query that can determine which branches do not have any ATMs.

5.3. Find all low-net-worth customers who have a total balance of less than $5,000 from all their accounts combined total. List their first and last name and the balance for each account they own. The output should be in ascending order of last name of customers.

5.4. Use a set operator to find new customers, i.e. those customers who have performed no more than two transactions thus far. List the customer ID of these customers.

5.5. Find all customers whose accounts are all held in only one branch. List customer ID and email address of these customers.