Problem Description

The ER diagram shown in the following picture
mostly follows the description of data in the previous assignment, with following modifications to make future application development easier:

• When a member places an order, the member specify the expected order delivery date instead of use the Monday of the week to represent the week they expect the deliver. Realistically, the members would choose from a list of food delivery dates for their orders instead of entering any date they wish.
• The same producer can supply the same food item multiple times as long as on different suply dates. ER diagram can't handily capture this requirement. I tried to use an discriminator (supplyDate) on the relationship set Supply to capture this valid duplications.

Your Tasks:

First, translate the ER diagram to its equivalent relational conceptual schema.
Clearly show the relations, the attributes, the primary keys and the foreign keys if there are any in the shcema. Note that the names of the relations and attributes don't need to be the same as the names used in the ER diagram. You can choose your own names as long as they are meaningful, easy to understand and not ambiguous.

Then, realize the relational conceptual schema with its equivalent physical schema.
Write the SQL statements that can actually create these relations/tables in Oracle database system. Choose reasonable data types for the attributes/columns, and specify at least all the primary key and foreign key constraints.

How to Submit

Make your answer precise and concise. Make your documentation easily understandable.

You can submit your assignment solution using one of the following two ways:

• Submit a hard copy (in or outside the lecture/lab time) before the due date/time.
• Submit electronically. The submit script currently accepts .txt, .pdf and/or .md files.
  Login to any of the cub machines in the lab, in the directory that holds your solution files, submit your solution file(s) using the following commands:
  ~liuh/bin/submit 370 A2 .