Write the SQL code to create the relational schemas from this data base system : Entities and Attributes1. ProductAttributes: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. ManufacturerAttributes: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. StoreAttributes: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. IngredientAttributes: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. NutritionalDetailAttributes: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. ConsumerAttributes:Email (PK): Email of the consumer.Assumption: Consumers can suggest products, and only their email is stored.Relationships1. Product-Manufacturer: A product is manufactured by one manufacturer, and a manufacturer can produce many products.Type: One-to-ManyExplanation: 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-ManyExplanation: 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-ManyExplanation: 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-ManyExplanation: The NutritionalDetailID in the Product entity references the NutritionalDetail entity.5. Consumer-Product: Consumers can request to add products.Type: Optional One-to-ManyExplanation: 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. E ProductProductID: intName: stringBarcode stringManufacturerID : intE ConsumerEmail: stringsold insells(E) FoodProduct// Inherits ProductID, Name, Barcode, ManufacturerID from ProductExpiry Date dateE NonFoodProduct// Inherits ProductID, Name, Barcode, ManufacturerID from ProductWarrantyPeriod: stringE ManufacturerManufacturerID : intName: stringAddress stringPhoneNumber: stringEmail: stringE StoreStoreID: intName: stringAddress stringPhoneNumber: stringEmail: stringStoreLocation: stringE IngredientIngredientID : intItem: stringSource: stringE NutritionalDetailNutritionalDetailID : intItem: stringAmount: string

The following SQL code builds relational schemas for the specified database system using the…

Answered: E Product ProductID: int Name: string…

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

Related questions

Question

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

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.

Expert Solution

Step by step

Solved in 2 steps