COMP643 Advanced Database Management

Assignment 1

IMPORTANT

PART 1 – ER DIAGRAM (100 POINTS)

South Island Scenic Tours (SIST) is a company in Christchurch that provides guided tours to groups of

visitors to Christchurch area. Over the years, the number of tourists coming to Christchurch has grown and SIST has found it difficult to manage various information related to the tours. The

company’s operations areas follows:

•    SIST offers many different tours. For each tour, the tour name, the description, approximate  length (in hours) and fee charged is needed. A tour can have many different qualified guides. Guides are identified by an employee ID. Additional information for guides include guide’s

name, home address and date of hire. Guides take a test to be qualified to lead specific

tours. As the company enforces that each tour must be led by a qualified guide, it is

important to know which guides are qualified to lead which tours and the date that they

completed the qualification test for each tour. A guide may be qualified to lead many

different tours as long as the guide has completed the qualification test for those tours. New guides (who have not completed any qualification test) are not qualified to lead any tours.

New tours mayor may not have any qualified guides.

•    Every tour is designed to visit at least three locations. For each location, a name,a type and official description are kept. All locations are visited by at least one tour. The order in which the tour visits each location should be tracked as well.

•    When a tour is actually given, this is referred to as an “outing”. SIST schedules outings well in advance so they can be advertised and so employees can understand their upcoming work

schedules. A tour can have many scheduled outings. Each outing is for a single tour and is

scheduled for a particular date and time. All outings must be associated with a tour. All tours at SIST are guided tours, so a qualified guide must be assigned to each outing. Each outing has one and only one qualified guide.

•    Tourists pay to join a scheduled outing. For each tourist, the name and telephone number    are recorded. A tourist may sign up to join many different outings. Each outing can have at   least one or up to 30 tourists. Information is kept only on tourists who have signed up for at least one outing.

The owner, Laura Kendall has approached you to design the database for the web application that   they are developing to help run the business. Based on the information provided, create the Crow’s Foot notation ERD to support SIST’s business operation.

Marking Information

PART 2 – CREATING DATABASE, TABLES WITH INTEGRITY CONSTRAINTS (50 POINTS)

The ERD for Prescription Management System is shown in Figure 1. Drugs are sold in pharmacies.

Each pharmacy has a unique identification. Every pharmacy sells one or more drugs, but some pharmacies do not sell every drug. Drug sales must be recorded by prescription, which are kept as a record by the pharmacy. A prescription clearly identifies the drug, doctor and patient as well as the date it is filled. Doctors prescribes drugs for patients. A doctor can prescribe one or more drugs for a patient and a patient can get one or more prescriptions. However, a prescription is written by only one doctor.

Figure 1: ERD for Prescription Management System

1.    Write an SQL script. to create a database and the accompanying tables. Ensure that all the integrity constraints are defined.

2.    Write another SQL script. to populate the database that you have created in Part 1. Create 10 rows for each table.

Marking Information

PART 3 – SQL QUERIES (50 POINTS)

Use the ERD for the SaleCo Online Order system shown in Figure 2 to answer the following queries. You can download the SQL script (“SaleCoDBMySQL.sql”) to create the database in your local machine.

Figure 2: ERD for the SaleCo Online Order System

1.    Display the total number of invoices. (2 points)

2.    Display the number of customers with a balance of more than $500. (3 points)

3.    Display all purchases made by the customers. The list should include customer code, invoice

number, invoice date, product description, line units and line price. Sort the results by customer code, invoice number and product description. (3 points)

4.    Display a list of all customer purchases. The list should include customer code, invoice number, product description, units bought, unit price and subtotal for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying line_units by line_price. Sort the

output by customer code, invoice number and product description. (4 points)

5.    Display the customer code, balance and total purchases for each customer. Total purchase is

calculated by summing the line subtotals for each customer. Sort the result by customer code. (3 points)

6.    Display the customer code, balance, total purchases and number of individual product purchases made by each customer. For example, if the customer’s invoice is based on three products, one per line_number, then you count the three product purchases. Sort the result by customer code. (4 points)

7.    Display the customer code, customer balance, total of all purchases, the number of purchases,

and the average purchase amount made by each customer. Sort the results by customer code. (5 points)

8.    Display the invoice number and total purchase per invoice. The total purchase is the sum of the product purchases in Line table that corresponds to the invoice. Sort the results by invoice number. (3 points)

9.    Display customer code, invoice number and invoice totals. Sort the results by customer code and then by invoice number. Note that there may be more than one invoice per customer.  (3 points)

10. Display the customer code, number of invoices and the total purchases by the customer. Sort the results by customer order. (5 points)

11. Display the total number of invoices, the invoice total for all the invoices, the smallest of the

invoice amounts, the largest of the invoice amounts and the average of the invoice amounts. (5 points)

12. Display the customer code and the customer balance for all customers who appear in the Invoice table.  Sort the results by customer code. (2 points)

13. Display the minimum balance, maximum balance and average balance for those customers who made purchases. (3 points)

14. Display the total balance, minimum balance, maximum balance and average balance for all customers. (2 points)

15.  Display the customer code and customer balance for all customers who did not make any purchases (3 points)

PART 4 – NORMALISATION (50 POINTS)

CoverServ supplies part-time/temporary staff to hotels throughout the South Island. Table 1 shows the time spent by agency staff working at two hotels. The EMP_ID is unique for employee.

Table 1: Sample data of time spent by agency staff working at two hotels

Based on the information provided in Table 1:

1.    Provide examples of insertion, deletion and modification anomalies. (10 points)

2.    Normalise the table to 3NF.  Show your work.  State any assumptions you make about the data shown in Table 1.  (30 points)

3.    Draw the Crow’s Foot ERD of the normalised relations. (10 points)