You are asked to design a relational database for a company that maintains a co-op style Food Sharing Program. The italic values are example data.
People are asked to sign up to be members of the program. One member, regardless it's an individual or a family, has an account in the program. Each account is assigned a unique account number (378021), has a name of a contact person (John Smith), an email address as contact information (John.Smith@shaw.ca), and an address as food package delivery address (123 Main Street, Nanaimo, BC). The date when the member signed up to this program (March 22, 2020) should also be captured and stored. The member account has a status to indicate whether the account is still active(active or inactive). The most recent activation date (September 5, 2024) should also be stored. Some of the most recent activation dates are the same as their accounts' sign up dates.
Some members are also food producers. These producers should provide the address of their farms (123 Cedar Road, Nanaimo, BC).
The Food Sharing Program has a list of acceptable food items. Each food item has a unique product code (4034), a name (broccoli), and standard unit (gram or quantity).
Food producers can contribute food items to the Food Sharing Program. The Program should capture and record who (378021) supplied which food item (4034), when (December 15, 2024) and in what quantity (20,000 grams).
The Food Sharing Program packs food to batches of packages. Each batch has a unique batch id barcode (M3379025), a packing time (January 2, 2025, 14:05:00) and a size type (one of small, median and large). All packages belonging to the same batch contain the same (amount of) food items. (note: "amount of" added on January 23 to make it clear that each package can have a variety of different food items.) The content of these packages should be recorded for future references. Each package has its unique package number (1) within the packages belonging to the same batch (M3379025).
Members can place orders for food delivery. To simplify the scenario, Each order is assigned with a unique order number (55017), placed by a member (represented by their account (378021)) for one week represented by the Monday's date in the corresponding week (January 6, 2025). The member specify the size of the package (one of small, median and large) they want for the order. When (January 3, 2025) the order is placed is also important to be captured and recorded.
A package (M3379025 - 1) will be delivered to the member's delivery address (123 Main Street, Nanaimo, BC) for the order (55017) placed by the member. Upon the member receiving the package delivery at a date/time (January 6, 2025, 8:07:00), the order is considered to be completed. The order completion information should be captured and stored for future reference.
This problem description may have ambiguities and may miss some information for the application domain knowledge. You are welcome to document your own interpretation and assumptions.
Draw an ER diagram to capture the data information described in the Problem Description.
Choose meaningful names for the entity sets, the attributes/properties, and the relationship sets. Identify appropriate constraints that should be held in the diagram (e.g., cardinality constraints on the relationship sets, the weak entity sets and aggregations if there are any, etc.). Indicate the identifiers and/or discriminators of the entity sets.
In your ER diagram, try to capture as much relevant information as possible. There may be properties or constraints that you cannot capture in your model. If you cannot model something exactly, it is preferable to be under-constrained rather than over-constrained. If there are constraints that you recognize but cannot capture in your ER model, document them clearly.