代写数据库作业，实现DDL和DML语句。
Requirement
Monash Food operates a restaurant on the Clayton Campus overlooking the Monash
Common. They have asked that their current paper based recording system be
computerised.
The restaurant has a number of tables for which they would like to record the
table number, its seating capacity (how many diners/people can sit at the
table) and the table’s location within the restaurant.
When a diner (person) arrives at the restaurant they are escorted to their
table and assigned a seat number at the table to help waiting staff keep track
of the meals to be served. Each tables seat numbers start at one and increase
by one until they reach the tables seating capacity. Monash Food is not
interested in storing any personal details about a diner, nor in tracking a
diner to see if they return to the restaurant (each visit is treated as a new
diner). A table may have several different groups seated at it across the
evening as one set of diners finish their meal, leave the restaurant and a new
set of diners arrive. Monash Food needs to track what each diner orders and
how much they owe for what they have ordered.
Monash Food offer a wide range of food items. For each food item they wish to
record the name of the item, a description and the cost per standard serve. If
the food item is a Beverage they wish to record the alcohol level. If the food
item is an Entre they wish to record whether the item is a hot or cold entre.
If the food item is a Main they wish to note if the item is vegetarian and
also if it is gluten free. As well as the standard serve size some mains are
also available as a small serve, some also as a large serve and some are
available as standard, small and large serves. Monash Food wishes to record
the kilojoules and serve cost for the small and large mains. If the food item
is a Dessert they wish to record if the item is lactose free. For all
beverages, entres, mains and desserts they wish to record the kilojoule value
of the standard item.
Diners order food items, which are prepared by the kitchen staff and then
delivered to the diners at their table. Some diners who might be especially
hungry or big eaters may order more than one serve of a particular food item
(eg. two serves of chocolate mousse).
A full sized copy of the model is available as a PDF document from Moodle.
When a new diner is seated at the restaurant an entry is made into the DINER
table. The diner_seated attribute of DINER is set and they are assigned a
table and seat no at that table, the diner_completed is not known since they
have not completed their meal. A diner orders items from the menu which are
recorded in the table FS_DINER. Within this table fs_diner_no_serves indicates
the number of serves they have ordered and fs_diner_item_served is set to ‘O’
to indicate this is an order. When an item is served to a diner their payment
due is updated and fs_diner_item_served is set to ‘S’ to indicate the order
has been served. After the diner has completed their meal they pay their
payment due and the diner_completed attribute is set to signify the close of
this diner experience.
To simplify this task we are applying a rule that says diners can only order
any particular item of food only once - however they may order multiple serves
when they place this order.
You have been supplied with a schema file MonashFood-schema-start.sql (which
must not be altered in any way) which partially implements the Monash Food
model.
You have also been supplied with a document ass2-solution.sql - you should
rename this script by prepending your authcate username to the start of the
filename eg. abc123-ass2-solutions.sql. This script file will be referred to
as your solutions script. Within this script there are marked points where
each of your solutions must be added.
All of the work for assignment 2 will take place in this document so please
take great care to keep regular backups, including off your computer eg. on
Google Drive, so you do not lose work.
Before starting work on the task complete the header by adding your name etc,
in the solutions script.
In completing this assignment you are not permitted to manually look up a
value in the database, obtain its primary key (for example) and use that in
your answer. As an example you cannot look in the database and see that
‘Bruschetta’ is food_item_no 1 and use this in your work. You must use only
the values listed in this document for the particular task you are working on.
TASK 1: Data Definition
For this task you are required to complete the following:
- Add to your solutions script the CREATE TABLE and CONSTRAINT definitions which are missing from the MonashFood-schema-start.sql script. You MUST use the entity and attribute names shown in the data model above to name tables and attributes which you add.
- Add the full set of DROP TABLE statements to your solutions script. In completing this section you may only use DROP TABLE tablename - you are not permitted to add any other clauses such as cascade constraints
Before proceeding with Task 2 you must run the supplied MonashFood-schema-
start.sql (which must not be altered in any way) followed by the extra
definitions that you added in 1.1 above.
In a script you can run a section of the script by highlighting the lines you
wish to run and selecting the run button. If at any stage your tables are
corrupted during working on this assignment you simply need to run your drop
commands from 1.2 above and then rerun MonashFood-schema-start.sql and your
extra definitions that you added in above.
TASK 2: Data Manipulation
Run the script MonashFood-insert.sql to add some initial data into the tables
you created in task 1.
Data manipulation tasks:
Add to your database four DINER records and their associated FS_DINER records.
These four diners should all represent completed dining experiences (ie. they
have ordered, been served, paid the full amount outstanding and left the
restaurant) which occurred in May 2017. You may pick any range of dates/times
you wish for these diner records.
Each diner must have a minimum of two FS_DINER records, you may pick any food
items and number of serves you wish, however ensure you use a variety of items
and serves.
For diner_no’s you may assign primary keys that you choose provided the number
is below 10 (ie. the values must be in the range from 1 to 9). All four diners
should be assigned to table number 1, over a range of dates. Table 1 has a
seating capacity of 3 (assign an appropriate seat number from 1 .. 3). For
this question only, you may use the following food item details:
An Oracle sequence is to be implemented in the database for the subsequent
insertion of records into the database for the tables FOOD_ITEM and DINER.
Provide the CREATE SEQUENCE statements for the FOOD_ITEM and DINER tables.
The sequences will be used to generate new primary key values when adding new
tuples/rows to the database:
- a. The sequence for FOOD_ITEM should start at 11 and increment by 1
- b. The sequence for DINER should start at 10 and increment by 1
Provide the DROP SEQUENCE statements for the sequence objects you have created
in Q2.2 above
TASK 3: Database Insert/Updates
Sequences created in task 2 must be used to insert data into the database for
the task 3 questions. For these questions you may only use the data supplied
in this task.
Add food: Add a new DESSERT to the Monash food menu - you will need to
research some meaningful data to be able to add this item. DESSERT’s are
food_type ‘D’ and are only served in standard ‘ST’ serve sizes.
Increase Price: Monash food has decided to increase the price charged for all
standard serve (‘ST’) main food items (‘M’ food type) by 15%, make this change
in the database
Diner activity:
- a. A new diner has just arrived and been seated at Table 1 seat 3. Update the database to seat this diner.
- b. This new diner calls the waiter over and proceeds to order two ‘Bruschetta’ entrees. Entrees are only available in a standard ‘ST’ size. Add this data to the Monash Food System for this diner. The food item has not been served as yet, this is an order only
- c. Some time after this order has been recorded the ‘Bruschetta’ ordered in (b) is served to this diner - update the database to record this service.
TASK 4: Database Structure
After using the system for some time Monash Food has realised that it is not
making the optimal use of its diner information and so has decided that it
will offer all future diners an opportunity to provide their name, contact
mobile number and email address so that Monash Food can let them know about
specials and events which they run (diners are not required to provide this
information). Change the “live” database so that this information can be
recorded. For any structures you create you are not required to include column
comments.
After the start of each new financial year (July 01) Monash Food wish to
archive diner (DINER) and order (FS_DINER) information into two historical
data tables and remove all the current data in these two tables representing
the previous financial year. This archive will be carried out some time early
in the new finanical year, all completed diner records for the past financial
year must be archived.
This change should be carried out on your database after 4.1 has been
successfully completed.
DINER should be archived into DINER_HISTORY and FS_DINER into
FS_DINER_HISTORY. For the diner archive (DINER_HISTORY) the seat number and
table number attributes will not be stored in the history table. For the
FS_DINER table all attributes must be archived into the history table. The
relationship between the two history tables must be maintained. The history
tables may be created without column comments.
ALL foreign key relationships between these history tables and any already
existing tables in the database must also be maintained:
- FS_DINER_HISTORY must maintain a relationship to FOOD_SERVE, and
- DINER_HISTORY must maintain a relationship to the table/s you created in 4.1 above.
Submission Requirements
For this assignment there is only one file to submit. You are required to
submit your solutions script file to Moodle before the assignment due
date/time. If you need to make any comments your marker/tutor should be aware
of please place them at the head of your solutions script in the “Comments for
your marker:” section.
