REQUIREMENT

A Food Delivery System allows customer to place food order online and will be delivered by the restaurant via dispatch services. The schema of the database is provided below:

[Primary key is underlined, foreign key is dotted underlined]

Take note:

• Food type refers to the food main ingredient such as chicken.
• Food menu type refers to the style of cooking such as western.

Based on the relational schema, you are required to produce the following:

1. Create a Database order Information, or any suitable name.
2. Create all relevant tables as required. Choose appropriate data types for each attribute. Indicate the primary key and foreign key in each table created.
3. Produce sample records for each table that observes the Referential Integrity Constraint. FIVE records per table are enough. The data inserted must satisfy the following constraint:
   1. there are at least 3 restaurants and each restaurant have at least serve 2 foods
   2. each customer can makes one or more order and the order can be have more than one foods
   3. make sure you create the relationships between tables
4. Write SQL query and its result based on the records added earlier for the following tasks: (one query per question)

1. List number of food based on food menu type
2. List all food sold by each restaurant with food menu type Western (you may also use the available food menu type inserted earlier)
3. List all customer details who lives in Kubang Kerian (you may also use the available customer address inserted earlier)
4. Produce a Total-Purchase information by getting the overall total purchase for food menu type Thai (you may also use the available food menu type inserted earlier)
5. Update expiry hour for all food sold by restaurant name "KAYUONE" (you may also use the available restaurant name inserted earlier) with additional 2 hours. You are required to show before and after update records.
6. Display full customer name, food name, food type, food menu type, order date and order price for all order made on the last 30 days
7. Display full detail of customer, total number of orders made and the overall total order amount made by the customer
8. Modify the ORDERLIST table by adding in new column entry for additional request made by customer

5. Create ONE form to handle the Order-Entry. The form must be able to enter and display data.

Note:

1. You are free to use any DBMS tool (using DDL SQL or GUI)
2. Or you can also use Oracle Application Express (Oracle APEX) at https://apex.oracle.com/i/index.html. If you do not have an account with Oracle Application Express, do register yourself first to get a free workspace.
3. For every table, form and SQL query (query and result table) created for the above questions, capture the screen and paste it in a MS-Word, as a proof that you are using any DBMS or Oracle APEX.

CUSTOMER CustID, CustName, CustAddress, CustPhone, CustEmail RESTAURANT RestID, RestName, RestAddress, RestPhone, RestEmail, RestPIC FOOD FoodID, FoodName, FoodType, FoodMenu Type, FoodExpiry Hour, Food Price, RestID ORDERLIST Order_ID, Order Date, OrderQty, Order Amount, FoodID, CustID [Primary key is underlined, foreign key is dotted underlined] CUSTOMER CustID, CustName, CustAddress, CustPhone, CustEmail RESTAURANT RestID, RestName, RestAddress, RestPhone, RestEmail, RestPIC FOOD FoodID, FoodName, FoodType, FoodMenu Type, FoodExpiry Hour, Food Price, RestID ORDERLIST Order_ID, Order Date, OrderQty, Order Amount, FoodID, CustID [Primary key is underlined, foreign key is dotted underlined]