Homework 4: Database Design - Total 50 Points Read All Quest
Homework 4 Database Design Total 50 Pointsread All Questions And
Read all questions and instructions carefully. On-the-Vine Vineyard, Inc., is a small, family-owned winery, one of California’s largest in Sonoma Valley, specializing in limited production of premium quality wine. The company employs over 12 full-time employees with various positions. Each employee has a unique ID, name, position, social security number, address, phone number, and emergency contact. On-the-Vine owns several vineyards, each managed by one employee and described by a unique number, name, address, and size. Each wine bottle has a unique ID, name, vintage year, category, vineyard, price, percent alcohol, and is associated with a winemaker (employee). Customers are identified by a unique ID, with personal details recorded. Orders made by customers include multiple wine bottles, with each order having a unique order number, order date, shipping date, and status. The task is to create an Entity-Relationship Diagram (ERD) illustrating all entities, attributes, and relationships, with relationships named and typed (1:1, 1:M, M:N) accurately. Relationships should have descriptive names and proper relationship types. The ERD must include all necessary entities, attributes, and relationships to comprehensively represent the business process.
Paper For Above instruction
The ERD for On-the-Vine Vineyard's database system must comprehensively encapsulate the core components of the business, including employees, vineyards, wines, customers, and orders. Each entity and its attributes depict critical data points, enabling effective management and retrieval of information pertinent to wines, personnel, client base, and sales transactions.
Entities and Their Attributes:
- Employee: EmployeeID (PK), Name, Position, SSN, Address, PhoneNumber, EmergencyContact
- Vineyard: VineyardID (PK), Name, Address, Size, ManagedBy (FK to EmployeeID)
- Wine: WineID (PK), Name, VintageYear, Category, Price, PercentAlcohol, VineyardID (FK), WinemakerID (FK to EmployeeID)
- Customer: CustomerID (PK), FirstName, LastName, Address, PhoneNumber, DOB
- Order: OrderID (PK), CustomerID (FK), OrderDate, ShippingDate, ShippingStatus
- OrderDetails: OrderID (FK), WineID (FK), Quantity, (PK composite of OrderID and WineID)
Relationships:
- An Employee manages exactly one Vineyard (1:1), with the relationship named "Manages."
- A Vineyard produces many Wines (1:M), relationship name: "Produces."
- Each Wine is made at one Vineyard (M:1).
- A Wine can be produced by one Winemaker (Employee), and an employee can oversee multiple wines (1:M), with relationship "Makes."
- Customers place many Orders (1:M), relationship "Places."
- Each Order contains multiple Wines, and each Wine can belong to multiple Orders (M:N), with the relationship "Contains."
- The OrderDetails entity resolves the M:N relationship, capturing specific quantities per wine per order.
This ERD ensures that all business aspects are modeled accurately, supporting efficient database implementation and management.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
- Database Systems: Design, Implementation, & Management (12th ed.). Cengage Learning.
- Journal of Database Management, 30(2), 65-78.
- Relational Database Design Clearly Explained. Morgan Kaufmann.
- International Journal of Computer Science and Information Security, 18(4), 123-130.
- Database Systems: The Complete Book. Pearson.
- Database System Concepts (7th ed.). McGraw-Hill Education.
- Database Systems (6th ed.). Pearson.
- An Introduction to Database Systems (8th ed.). Addison Wesley.