TASK 1: Data Definition [20 + 5 = 25 mks] For this task you are required to complete the following: 1.1 Add to your solutions script the CREATE TABLE and CONSTRAINT definitions which are missing from...

TASK 1: Data Definition [20 + 5 = 25 mks]
For this task you are required to complete the following:
1.1 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.
1.2 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 1.1 above.


TASK 2: Data Manipulation [12 + 8 + 5 = 25 mks]
Run the script MonashFood-insert.sql to add some initial data into the tables you created in task 1.
Data manipulation tasks:
2.1 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:


2.2 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
2.3 Provide the DROP SEQUENCE statements for the sequence objects you have created
in Q2.2 above


TASK 3: Database Insert/Updates [5 + 5 + (5 + 5 + 5) = 25 mks]
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.
3.1 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.
3.2 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
3.3 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.