COMP643 Advanced Database Management Assignment 1: Prescription Management System
COMP643 Advanced Database Management Assignment 1
40%
Friday, 26th April 2024 @ 5:00pm
Work not received by the due time attracts an immediate penalty of up to 25% of the marks available. No work will be accepted after Sun 28th April 2024 5:00pm
via COMP643 on LEARN
Worth:
Due:
Late Penalty:
Submission:
IMPORTANT
The use of Artificial Intelligence (AI) tools, such as ChatGPT, to complete this assessment is prohibited. Assessment answers will be analysed for evidence of the use of AI and penalties may be administered.
The University policy on Academic Integrity can be found here. 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 are as 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 may or 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
1|Page
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
Identification of main entities 30
Identification of the relationship types 40
between the entities in including the
relationship types and cardinality
Each entity is named sensibly. Relationship types are labelled; cardinality of each relationship is shown.
|
Item |
Marks |
Criteria |
|
Identify the attributes for each entity including primary key and foreign key. |
30 |
All relevant attributes are identified for each; primary key is identified; foreign keys identified. |
2|Page
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
-
Write an SQL script to create a database and the accompanying tables. Ensure that all the integrity constraints are defined.
-
Write another SQL script to populate the database that you have created in Part 1. Create 10 rows for each table.
Marking Information
All entities identified in Part 1 are converted 10
to table.
All attributes have sensible types with 20
constraints.
All tables are populated with 10 rows of data 20
Database and tables are
created.
Data types are sensible, and
constraints are implemented.
Database is ready to use.
|
Item |
Marks |
Criteria |
3|Page
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
-
Display the total number of invoices. (2 points)
-
Display the number of customers with a balance of more than $500. (3 points)
-
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)
-
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)
-
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)
-
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)
-
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)
4|Page
-
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)
-
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)
-
Display the customer code, number of invoices and the total purchases by the customer. Sort the results by customer order. (5 points)
-
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)
-
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)
-
Display the minimum balance, maximum balance and average balance for those customers who made purchases. (3 points)
-
Display the total balance, minimum balance, maximum balance and average balance for all customers. (2 points)
-
Display the customer code and customer balance for all customers who did not make any purchases (3 points)
5|Page
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
|
Attribute Name |
Sample Value |
Sample Value |
Sample Value |
Sample Value |
EMP_ID 10001 CONTRACT_NO C1024 HOURS_PER_WEEK 16 EMP_NAME John Smith HOTEL_NO H25 HOTEL_LOCATION Christchurch
Based on the information provided in Table
-
Provide examples of insertion, deletion
-
Normalise the table to 3NF. Show your
10005 C1024 24 Diane Gray H25 Christchurch
11003 C1025 28 Sandy Wu H4 Queenstown
10001 C1025 16 John Smith H4 Queenstown
shown in Table 1. (30 points)
3. Draw the Crow’s Foot ERD of the normalised relations. (10 points)
1:
and modification anomalies. (10 points)
work. State any assumptions you make about the data
6|Page
