ICT285:Database Implementation and Views for FineFoods4U Case Study

• This assignment is worth 20% of your final mark for the unit.
• This assignment consists of 100 marks. Marks are allocated as described in the assignment. Late submissions will be penalised at the rate of 10 marks per day late or part thereof after the due date. Assignments will not be accepted more than 7 days after the submission date as assignment return will have commenced.
• If you have questions about the assignment, you can ask your tutor, or the unit coordinator, or post a question on the LMS discussion board (preferred). Please check the discussion board before asking to make sure that your question has not already been answered.
• The University treats plagiarism, collusion, theft of other student’s work and other forms of dishonesty in assessment seriously. This is an INDIVIDUAL assignment. Any instances of dishonesty in this assessment will be immediately forwarded for investigation.
• Marks may be deducted based on the overall organisation and presentation of the document – up to 5 marks (Standard Font Size and Colour i.e., Arial/12, double line spacing, page number, separate sections, aligning paragraphs. Title page and table of contents. Good technical style, avoiding excessive jargon. Correct use of equations, footers, and headers).
• The University takes academic integrity very seriously. Instances of academic misconduct include submitting work that has been produced by someone else or either in part or whole using Artificial Intelligence tools. More information about academic integrity is contained within the Murdoch Academic Passport (MAP) unit here.

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 (15 marks)

• 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 based on your feedback from Assignment 1 and/or because 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 (20 marks)

• a) For each table: a definition of each column (attribute), consisting of the column name, a 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 (25 marks)

• 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.
• 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 supports the transactions and views listed below.
• 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 all or part of your assignment.

Part 4: Views (40 marks)

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, descriptions 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 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.

Note that where the requirement is for “a particular” suburb/month/restaurant/etc, assume for this assignment the view definition includes a specific value such as ‘Murdoch’ or ‘September’ (although it should work for all relevant values).

Marking Criteria

• The marker will view your documentation and then match your documentation to your implementation. This means for example that tables, columns and constraints should be named in your database as they are in your documentation. Relationships defined in your ERD should be defined in your database using foreign keys.
• The marker will view the sample data in your tables.
• The marker will execute each of the views created for Part 4 above.
• AGAIN, please ensure that you GRANT the appropriate privileges on all relevant objects (tables and views) to the user MARKERTL. If you do not do this, the marker will not be able to mark part of your assignment (and you may be awarded 0 for this section).