write a relational schema for this er diagram. Use conversion algorithm to convert ER diagram to Relational
Question:
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
Fundamentals Of Database Systems
ISBN: 9780133970777
7th Edition
Authors: Ramez Elmasri, Shamkant Navathe