This assignment requires you to implement the database you designed to address the requirements of the FineFoods4U case study in Assignment 1. You will need to incorporate any changes you may need to make to your design as a result of the feedback on Assignment 1 both individually and as a class (e.g. on the online forum), and to address the additional/amended requirements listed in this document.
The assignment addresses the following learning outcomes for the unit:
Demonstrate practical skills in using SQL
Demonstrate practical skills in normalisation and convert a conceptual database design to a logical design in 3NF
Create a database from a given design using a DBMS and implement specified constraints using appropriate tools and approaches
Explain and implement security as it applies in the database environment.
Case study
Re-read the description of the FineFoods4U case in Assignment 1 if you need to refresh your memory.
Bill is pleased with your work so far and asked you to go on to implement your design. He has made a few clarifications and additions to the specifications that you should note:
- Bill's trial of the system has been very successful and he now wants to broaden his service into other suburbs as well. He has also recruited more drivers, with the expectation that there will be drivers available in each suburb. However, customers will still only be able to have orders delivered from restaurants in the same suburb.
- Bill needs to know whether his meals are being delivered on time, so the database will need to be able to provide information on requested and actual delivery times/dates.
Part 1: Revised ERD and schema
a) Create and submit the ERD for this database that you are going to use as the basis of your implementation.
b) Include a one or two paragraph explanation as to the changes you have made to the ERD on the basis of your feedback from Assignment 1 and/or as a result of having to support the transactions and views described in this assignment.
c) Show the relational schema in 3NF that will be the basis of your implemented design. Show your relations using the following convention:
RELATION_NAME (PrimaryKey, Attribute, Attribute, ... ForeignKey)
Part 2: Data dictionary
Create a data dictionary for your database. This should include:
a) For each table: a definition of each column (attribute), consisting of the column name, brief description of what it represents, its data type and size, domain (allowable values), any default value, whether it is required, and any constraints (primary key, foreign key). You can follow the examples in the textbook for the View Ridge Gallery tables, e.g. p299 tables labelled ‘Column characteristics'
b) For each of the columns that is a foreign key, give the appropriate referential integrity rules (i.e. the "on delete.... ; on update" etc actions that should apply when the corresponding primary key is altered). The appropriate action should be included whether or not there is a statement in Oracle to implement it.
c) Any business rules (enterprise constraints) that should apply to the database as a whole. Note that your data dictionary must be consistent with your ERD and schema.
Part 3: Implementation
Implement the database in Oracle SQLPlus on arion.murdoch.edu.au. Note the following:
a) All tables should be created as per your ERD and data dictionary; the marker will check your ERD against your tables. You do NOT need to include the SQL CREATE TABLE statements that you used to create the tables.
b) All entity and referential integrity constraints should be created and appropriately named.
c) All columns (attributes) should be of an appropriate data type/size and be set as required or not as appropriate.
d) All domain constraints should be implemented.
e) All tables should be populated with sample data that will allow the marker to test that your database fulfils the application requirements as specified and support the transactions and views listed below. Also provide the sample data in your Word document.
f) SELECT, UPDATE and DELETE permissions should be GRANTED on all database objects (particularly tables and views) to the user MARKERTL. This is most important. If you do not grant this permission, the marker will not be able to mark this part of your assignment.
Part 4: Views
Create VIEWS for the following in Oracle (views should be named as ViewA, ViewB etc). You should also provide the CREATE VIEW statements you used to create the views in your Word document.
ViewA All the details of an order for a particular customer. The driver needs this to pick up the dishes from the restaurant, and to confirm with the customer on delivery.
ViewB All the vegetarian dishes that can be delivered to customers in a particular suburb in less than half an hour.
ViewC The details of the orders for a particular restaurant on a particular date.
ViewD A list of all the vegan restaurants and the names, description and prices of the dishes they offer.
ViewE List of all drivers, and the customers (if any) they delivered to on a particular date.
ViewF List of drivers who are currently available (i.e. not out on a delivery) in a particular suburb.
ViewG The total number of orders for each restaurant so far (i.e. since FineFoods4U commenced).
ViewH The ‘booklet' which lists all the dishes available from a particular restaurant, with their names, descriptions, course type, prices and delivery time. (Note you do not have to create the booklet itself, just provide the information for it.)
ViewI The number of orders from each suburb in the previous month, in descending order.
ViewJ The number of orders in each suburb that were delivered later than requested in a particular month, and the average time by which they were late.
Attachment:- Assignment - Feedback.pdf