CSE2DBF/CSE4DBF 2013
Assignment 2 (20%)
Due date: Assignment 2 – 9.30am Tuesday, June 4th 2013
AIMS AND OBJECTIVES:
Assignment 2
? To perform queries on a relational database system using SQL;
? To demonstrate an advanced knowledge of stored procedures, stored functions and triggers.
This is an individual assignment. You are not permitted to work as a group when writing this assignment.
Copying, Plagiarism: Plagiarism is the submission of somebody else’s work in a manner that gives the impression that the work is your own. The Department of Computer Science and Computer Engineering treats plagiarism very seriously. When it is detected, penalties are strictly imposed.
No extensions will be given: Penalties are applied to late assignments (5% of total assignment mark given is deducted per day, accepted up to 5 days after the due date only). If there are circumstances that prevent the assignment being submitted on time, an application for special consideration may be made. See the departmental Student Handbook for details. Note that delays caused by computer downtime cannot be accepted as a valid reason for a late submission without penalty. Students must plan their work to allow for both scheduled and unscheduled downtime.
SUBMISSION GUIDELINES:
Task 1 should be saved to a file named task1.txt using the SPOOL command.
Task 2 should be saved to a file named task2.txt using the SPOOL command. Task 3 should be saved to a file named task3.txt using the SPOOL command.
Note an example of using the SPOOL command is given in the lab book. In the SPOOL file, you need to provide the query/procedure/function/trigger execution and the sample output. For the trigger, you need to show a sample test that demonstrates the successful execution of the trigger.
All the tasks above are to be submitted in hard-copy format to the relevant submission box on the first floor of the Beth Gleeson Building by 9.30am Tuesday, June 4th, 2013. The same tasks above are also required to be submitted online via the Assignment Submission on the LMS by 9.30am Tuesday, June 4th, 2013.
Implement the following tasks using ORACLE SQL*Plus.
Task 1 [50 marks]
Download the file eatWellSchema.sql from the LMS site and run it on ORACLE SQL*Plus. This file contains all the create and insert statements you will need for this assignment.
NOTE: YOU DO NOT NEED TO INSERT MORE DATA INTO THE TABLES.
The list of tables available for this assignment is the following (the EER diagram for these tables is attached as an appendix):
NormalClient(clientNo, clientName, streetAddress, suburb, postcode, phoneNo, faxNo, contactName, joiningDate, isRegular)
School(clientNo, clientName, streetAddress, suburb, postcode, phoneNo, faxNo,
contactName, joiningDate, salesTaxExNo)
PlatterOrder(orderNo, deliveryDate, deliveryTime, invoiced, isBulk, platterQuantity, discountPercent, clientNo)
SchoolOrder(orderNo, deliveryDate, deliveryTime, invoiced, taxExPercent, clientNo)
SandwichCombo(comboNo, comboName, comboPrice)
Item(name, type, retailCost, stockLevel, unit)
Supplier(supplierNo, supplierName, address, contactNo)
StoreOrder(orderNo, orderDate)
PlatterItem(orderNo, name, quantity)
SchoolCombo(orderNo, comboNo, quantity)
ComboItem(comboNo, name)
SupplierItem(supplierNo, name,currentPrice)
StoreOrderItem(supplierNo, orderNo, name, quantity, cost)
NOTE: PK is printed bold and underlined, FK is printed italic, PK that is also FK is printed bold, italic, and underlined.
Using the tables, provide the SQL for the following queries.
a. Display the name and address of the normal client who has most often used the services of ‘Eat Well’ food store.
b. For each school, display the total number of orders that have been placed so far.
c. Display the list of normal client names, and the number of ordered platters, for all orders that have been placed in April 2013.
d. Display the total projected income (based on orders) of ‘Eat Well’ for April 2013 from both normal clients and schools. Do not include taxes for schools. (Note: the price of each platter is $25)
e. Display the supplier’s name and address who has supplied the highest number of food items to the store.
f. Display the suburb where the majority of client orders (both normal and school) have come from.
g. Display the list of required food items and their retail cost, for all the orders that have been placed within the last one month.
h. Display the average number of orders that come from schools each month.
i. Display the name of the sandwich combo that has been ordered the most.
j. Display the platter ingredient that has been included most of the time in normal client’s orders.
[5 marks each – 50%]
Task 2 [30 marks]
Provide the implementation of the following stored procedures and function:
a. A Stored Procedure that displays the names and contact numbers of all clients in the
database that havent had an order delivered by ‘Eat Well’ for a month or more.
b. A Stored Function that takes an order number and an indicator as to whether the order is for a school or normal client as input and displays the total cost for that order. (Including taxes or discounts, if applicable.) You also need to show the SQL statements to display the total cost of every client order in the database.
c. A Stored Procedure that takes a date as input and displays two groups of information, the first one is the total number and total price amount of normal client orders for that day, and the second one is the total number and total price amount of school orders for that day.
[10 marks each – 30%]
Task 3 [20 marks]
Provide the implementation of the following triggers:
a. A Trigger that is activated if the combination of clients name and address already exists in the database.
? NOTE: you will need to create 2 triggers, one for each table.
b. A Trigger which automatically stores in a separate table called ‘ItemsToOrderList’ the item name, retail Cost, and the new stock level, each time the stock level of an item is reduced by more than 50%. (Note: You need to create ‘ItemsToOrderList’ table before implementing this trigger.)
[10 marks each – 20%]
SUBMISSION CHECKLIST:
? The relevant SQL queries for the ‘Eat Well Food Store’ Database System; ? The required stored procedures, stored function and triggers.

Appendix 1 EER Diagram