Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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:
- Create a Database order Information, or any suitable name.
- Create all relevant tables as required. Choose appropriate data types for each attribute. Indicate the primary key and foreign key in each table created.
- 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:
- there are at least 3 restaurants and each restaurant have at least serve 2 foods
- each customer can makes one or more order and the order can be have more than one foods
- make sure you create the relationships between tables
- Write SQL query and its result based on the records added earlier for the following tasks: (one query per question)
- List number of food based on food menu type
- List all food sold by each restaurant with food menu type Western (you may also use the available food menu type inserted earlier)
- List all customer details who lives in Kubang Kerian (you may also use the available customer address inserted earlier)
- 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)
- 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.
- 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
- Display full detail of customer, total number of orders made and the overall total order amount made by the customer
- Modify the ORDERLIST table by adding in new column entry for additional request made by customer
- Create ONE form to handle the Order-Entry. The form must be able to enter and display data.
Note:
- You are free to use any DBMS tool (using DDL SQL or GUI)
- 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.
- 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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started