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
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
Chapter4: More Object Concepts
Section: Chapter Questions
Problem 2RQ
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
- 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.
- FoodProduct Attributes:
Transcribed Image Text: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
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps
Recommended textbooks for you
EBK JAVA PROGRAMMING
Computer Science
ISBN:
9781305480537
Author:
FARRELL
Publisher:
CENGAGE LEARNING - CONSIGNMENT
EBK JAVA PROGRAMMING
Computer Science
ISBN:
9781337671385
Author:
FARRELL
Publisher:
CENGAGE LEARNING - CONSIGNMENT
EBK JAVA PROGRAMMING
Computer Science
ISBN:
9781305480537
Author:
FARRELL
Publisher:
CENGAGE LEARNING - CONSIGNMENT
EBK JAVA PROGRAMMING
Computer Science
ISBN:
9781337671385
Author:
FARRELL
Publisher:
CENGAGE LEARNING - CONSIGNMENT