E Product ProductID: int Name: string Barcode string ManufacturerID : int E Consumer Email: string sold in sells (E) FoodProduct // Inherits ProductID, Name, Barcode, ManufacturerID from Product Expiry Date date E NonFoodProduct // Inherits ProductID, Name, Barcode, ManufacturerID from Product WarrantyPeriod: string E Manufacturer ManufacturerID : int Name: string Address string PhoneNumber: string Email: string E Store StoreID: int Name: string Address string PhoneNumber: string Email: string StoreLocation: string E Ingredient IngredientID : int Item: string Source: string E NutritionalDetail NutritionalDetailID : int Item: string Amount: string

Write the SQL code to create the relational schemas from this data base system : 

Entities and Attributes

1. Product

• Attributes:
  • ProductID (PK): Unique identifier for each product.
  • Name: Name of the product.
  • Barcode: Barcode for the product.
  • ManufacturerID (FK): Foreign key referencing the Manufacturer.
  • Category: Dietary category of the product (e.g., vegan, vegetarian).
• Assumption: Each product has a unique identifier and is associated with a manufacturer and dietary category.

2. Manufacturer

• Attributes:
  • ManufacturerID (PK): Unique identifier for each manufacturer.
  • Name: Name of the manufacturer.
  • Address: Address of the manufacturer.
  • PhoneNumber: Contact number.
  • Email: Contact email.
• Assumption: Manufacturers produce multiple products.

3. Store

• Attributes:
  • StoreID (PK): Unique identifier for each store.
  • Name: Name of the store.
  • Address: Address of the store.
  • PhoneNumber: Contact number.
  • Email: Contact email.
  • StoreLocation: Location of the store.
• Assumption: Stores sell multiple products.

4. Ingredient

• Attributes:
  • IngredientID (PK): Unique identifier for each ingredient.
  • Item: Name of the ingredient.
  • Source: Source of the ingredient.
• Assumption: Each product consists of multiple ingredients.

5. NutritionalDetail

• Attributes:
  • NutritionalDetailID (PK): Unique identifier for each nutritional detail.
  • Item: Nutritional item (e.g., calories, protein).
  • Amount: Amount of the nutritional item.
• Assumption: Each product has multiple nutritional details.

6. Consumer

• Attributes:
  • Email (PK): Email of the consumer.
• Assumption: Consumers can suggest products, and only their email is stored.

Relationships

1. Product-Manufacturer: A product is manufactured by one manufacturer, and a manufacturer can produce many products.

• Type: One-to-Many
• Explanation: The ManufacturerID in the Product entity references the Manufacturer entity.

2. Product-Store: A product can be sold in many stores, and a store can sell many products.

• Type: Many-to-Many
• Explanation: This relationship is represented using an associative entity (e.g., ProductStore), which connects ProductID and StoreID.

3. Product-Ingredient: A product can have many ingredients, and an ingredient can be part of many products.

• Type: Many-to-Many
• Explanation: This relationship is represented using an associative entity (e.g., ProductIngredient), which connects ProductID and IngredientID.

4. Product-NutritionalDetail: A product can have many nutritional details, and each nutritional detail is associated with one product.

• Type: One-to-Many
• Explanation: The NutritionalDetailID in the Product entity references the NutritionalDetail entity.

5. Consumer-Product: Consumers can request to add products.

• Type: Optional One-to-Many
• Explanation: No direct relationship is stored in the database, but consumers can suggest products, and only their email is recorded if they request it.

Subtype/Supertype Hierarchy (EER Specific)

• Supertype Product: General entity for all products.
• Subtypes FoodProduct and NonFoodProduct: Specific types of products inheriting from Product.
  • FoodProduct Attributes:
    • ExpiryDate: Expiry date of the food product.
  • NonFoodProduct Attributes:
    • WarrantyPeriod: Warranty period of the non-food product.