ICT285 Databases TMA 2017 Assignment 2 | Design & Implementation & SQL

Case Study

Re-read the description of the FastFoods4U 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:

  1. 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.
  2. Bill needs to know whether his meals are being delivered on time as per his advertising so the database will need to be able to provide information on requested and actual delivery times/dates.
  3. Bill is pleased with the way FF4U is developing and wants to make some further additions to the concept. He has decided to offer a service whereby customers will be able to see the carbon footprint of the food they order, and so be able to choose accordingly. Bill is going to call this service ‘Earth First’. Earth First dishes will provide a carbon cost for the dish and an overall EF rating from green (best), through orange to red (worst). Not all restaurants will be able to provide the information so only some dishes will be available on the Earth First menu. However, Bill expects that Earth First dishes will come from any of the existing kinds of food he offers (vegetarian, Halal certified, noodle house, etc). When the order/ receipt is printed for the customer, the carbon cost (in kg) and Earth First rating will be included along with the other delivery information.

Part 1: Revised ERD and schema

  1. Create and submit the ERD for this database that you are going to use as the basis of your implementation.
  2. 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.
  3. 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:
  1. 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’
  2. 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.
  3. 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 tables for FF4U in Oracle SQLPlus on arion.murdoch.edu.au. Note the following:
  1. 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.
  2. All entity and referential integrity constraints should be created and appropriately named.
  3. All columns (attributes) should be of an appropriate data type/size and be set as required or not as appropriate.
  4. All domain constraints should be implemented.
  5. 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 same sample data in your Word document. If you use a screen dump, it MUST be a size that is readable without zooming.
  6. 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 and you will not get any marks for it.
  7. Please state whether you have used your V account or H account.

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. Note that some of these have changed from Assignment 1 in line with Bill’s new requirements.
  • ViewA All the details of a single order for a particular customer. This is what the driver needs to pick up the dishes from the restaurant and to confirm with the customer on delivery.
  • ViewB All the ‘Earth First’ green rating, organic certified dishes that are available in a particular suburb.
  • ViewC The details of all the orders for a particular restaurant on a particular date.
  • ViewD A list of all the Italian 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 FastFoods4U commenced).
  • ViewH An Earth First ‘booklet’ which lists all the Earth First dishes available from a particular restaurant, giving their names, descriptions, course type, EF rating, carbon cost, 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, listed from the most orders to the least. The view should work for any current month.
  • 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.

Get Project Solution Now

Comments

Post a Comment