Demonstrate practical skills in using sql

Assignment Help Database Management System
Reference no: EM131266261

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

Reference no: EM131266261

Questions Cloud

Problem regarding the crime classification systems : QUESTION 1: According to the readings, "Crime Classification Systems: NCVS, NIBRS, and UCR" authors Pazzani & Tita identify limitations in the use of NIBRS:
Discuss how to approach such a research issue : What are the major challenges faced in bringing data mining research to market? Illustrate one data mining research issue that, in your view, may have a strong impact on the market and on society. Discuss how to approach such a research issue.
How similar is the culture of canada to other countries : This assignment seeks to help you understand the answer to a broader concern and that is, "How similar or different is the culture of Canada to other countries?"
Compare and contract the production function : Design a study of a production function for a steel mill and another one for a call center. Which variables would you use, and what statistical method would you select for each function? In general, compare and contract the production function for..
Demonstrate practical skills in using sql : ICT285 Databases Demonstrate practical skills in using SQL - Demonstrate practical skills in normalisation and convert a conceptual database design to a logical design in 3NF
Maria choose to maximize profits : a) What price and output will Maria choose to maximize profits? b) Maria returns to college and asks her cousin Victor to manage her pizza business. Victor faces the same demand and cost conditions as Maria, but he decides to maximize revenue. Wha..
Why the researchers stated that our planet magnetic field : Demonstrate an instance of static charge such as seen with examples #32 and 33 shown on page 209 of your textbook. Next, post a picture of your example then explain the method of charging you used as well as the charges displayed in each object.
Describe the communication strategies you would use : Analyze two leadership models and corresponding communication skills that drive innovation. Describe the communication strategies you would use to introduce the proposed changes to your work team.
Describe the sovereignty movement in hawaii : Describe the sovereignty movement in Hawaii. What goals is the movement attempting to achieve? Apply a conflict sociological perspective to explain the movement and resistance to it.

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd