write a relational schema for this er diagram. Use conversion algorithm to convert ER diagram to Relational Model

Submission for this phase includes: 

a. Revised ER diagram

b. All ER to relational model intermediatory conversion steps with final relational model (i.e. step 4)

Phase 3.2 Database Implementation (10 Marks)

In this phase, you need to consider the final relational model from phase 3.1 and the business rules used in Phase 2 to do final implementation of the database. You need to implement all the database tables with appropriate data types, constraints (e.g. primary keys, foreign keys, check constraints) and triggers. You need to also insert some dummy data in the database.

Based on the business rules following triggers are possible:

a) Adding a new user receipt/order cost in receipt table when order is delivered.

b) Not allowing deletion of primary payment option. 

c) updating the promotion code status to redeemed once order is delivered.

NOTE: You may have to assume certain numbers such as fees etc  during implementation of triggers. These assumptions should be clearly specified during your submission.

Submission for this phase includes:  A zip file containing all SQL files for creating tables with required constraints and trigger codes. Also, you need to include a one-page document for assumption you made during implementation.

Phase 3.3. Querying the database (5 Marks)

In this phase you need to insert some dummy/sample data in your database and implement atleast three PL/SQL procedures or functions that can extract data required to answer a query by the end user (i.e. restaurant, customer or delivery person). Such queries can be of your own choice. An example of query is that restaurant query menu items ordered previously by a customer or check delivery person details for an order. 

Submission for this phase includes: 

a. SQL file containing all Insert queries to add sample data

b. 3 PL/SQL codes

 

Transcribed Image Text:

belongs-to CATEGORIES AVAILABILITY RUberlD CategoryName MenultemName MenuID RUberlD Description Price Taxrate DietaryPref AvStatus imageavailable has-custom CUSTOMISATION available CustomisationID RUberlD Limit details DayofWeek consists MENUITEMS MENU has-coption RUberlD MenulD Menuname Description Menuhour selects MenuitemName MenulD CustName CustomisationID Cost selected CUSTOMOPTION ReceiptID MenuCost RECEIPT CartID UserID DeliveryFee ServiceFee OtherFees Discount include add-menu CART checkout FEEDBACK RUberlD REmail Password FeedbackID Rating Comments ForWhom ByWhom Mobile No Name Address numlocations ContractStatus RESTAURANT gets-feedback ORDERID DeliveryOption Payment Method DeliveryAddress PromoCode Speciallnstruction StatusID Estimated DelTime IsCancelled? OrderDate PickupAddress Estimated Pickup Time weekly-paid accepts ORDER givesdelvopt STATUS has Date ByWhom Comment R-PAYMENT RPaylD Pay Date Pay Uberfees creates StatusID StatusName PAYOPTION givespayopt POPTIONID Pay_details Type Expiry Date IsPrimary? givesAddress SCHEDULEDDELIVERY sends ID DeliveryDate DeliveryTime MAEKETING COMM add DELADDRESS AddID Address givespromo CommID MC_details save receives CUSTOMER UserID Password Name Language Mobile No Email insert PROMO call Duration Date PromoCode Status Discount details D-PAYMENT paid-weekly DELIVERYPERSON getsorder Action DPaylD PayDate Pay UberDID Name Email Phone Address IDProof_details IDProofType BackgroundCheck AccountStatus belongs-to CATEGORIES AVAILABILITY RUberlD CategoryName MenultemName MenuID RUberlD Description Price Taxrate DietaryPref AvStatus imageavailable has-custom CUSTOMISATION available CustomisationID RUberlD Limit details DayofWeek consists MENUITEMS MENU has-coption RUberlD MenulD Menuname Description Menuhour selects MenuitemName MenulD CustName CustomisationID Cost selected CUSTOMOPTION ReceiptID MenuCost RECEIPT CartID UserID DeliveryFee ServiceFee OtherFees Discount include add-menu CART checkout FEEDBACK RUberlD REmail Password FeedbackID Rating Comments ForWhom ByWhom Mobile No Name Address numlocations ContractStatus RESTAURANT gets-feedback ORDERID DeliveryOption Payment Method DeliveryAddress PromoCode Speciallnstruction StatusID Estimated DelTime IsCancelled? OrderDate PickupAddress Estimated Pickup Time weekly-paid accepts ORDER givesdelvopt STATUS has Date ByWhom Comment R-PAYMENT RPaylD Pay Date Pay Uberfees creates StatusID StatusName PAYOPTION givespayopt POPTIONID Pay_details Type Expiry Date IsPrimary? givesAddress SCHEDULEDDELIVERY sends ID DeliveryDate DeliveryTime MAEKETING COMM add DELADDRESS AddID Address givespromo CommID MC_details save receives CUSTOMER UserID Password Name Language Mobile No Email insert PROMO call Duration Date PromoCode Status Discount details D-PAYMENT paid-weekly DELIVERYPERSON getsorder Action DPaylD PayDate Pay UberDID Name Email Phone Address IDProof_details IDProofType BackgroundCheck AccountStatus

Answer rating: 100% (QA)

Based on the ER diagram provided we will follow an algorithm to convert the ER model into a relational model schema The process involves converting entities to tables relationships to foreign keys and ... View the full answer