ICT285 Databases Design and Implementation Assignment 01 Questions | Murdoch University

Assignment 2

Check the LMS for the Due Date

Some Important Points Worth Noting:

• This assignment is worth 20% of your final mark for the unit.
• This assignment is worth marks. Marks are allocated as described in the assignment. Late submissions will be penalised at the rate of 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, 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 students’ 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 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 work that has been partially or wholly produced using Artificial Intelligence tools. More information about academic integrity is contained within the Murdoch Academic Passport (MAP) unit: Murdoch Academic Passport.

Assignment Description:

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.

Marks Distribution:

• Part 1: Revised ERD and schema
• Part 2: Data dictionary
• Part 3: Implementation
• Part 4: Views 

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 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 

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” 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.

Part 3: Implementation

Implement the database in Oracle SQLPlus on rhea.ad.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.

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.

Important: SELECT, UPDATE, and DELETE permissions should be GRANTED on all database objects to the user MARKERTL.

Part 4: Views 

Create the following views in Oracle. Each view should be named ViewA, ViewB, etc. Include the CREATE VIEW statements used to create these views in your Word document.

View Descriptions:

View A: 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.
View B: All the vegetarian dishes that can be delivered to customers in a particular suburb in less than half an hour.
View C: The details of the orders for a particular restaurant on a particular date.
View D: A list of all the vegan restaurants and the names, descriptions, and prices of the dishes they offer.
View E: List of all drivers and the customers (if any) they delivered to on a particular date.
View F: List of drivers who are currently available (i.e., not out on a delivery) in a particular suburb.
View G: The total number of orders for each restaurant so far (i.e., since FineFoods4U commenced).
View H: The ‘booklet’ that 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.)
View I: The number of orders from each suburb in the previous month, in descending order.
View J: 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.

Notes:

• For requirements involving “a particular” suburb, month, restaurant, etc., assume the view definition includes specific values such as ‘Murdoch’ or ‘September. ’ However, the view should work for all relevant values.
• The marker will view your documentation and match it to your implementation. Ensure that table, column, and constraint names in your database match those in your documentation.
• The marker will execute the sample data and views you provide to verify functionality.

Important: Grant SELECT, UPDATE, and DELETE permissions on all relevant objects (tables and views) to the user MARKERTL. Failing to do so may result in lost marks.

Please ensure that your assignment follows these instructions for proper evaluation and grading.