Mis 301 Introduction To Business Information Systems Homewor
Mis 301 Introduction To Business Information Systemshomework 4 Da
Read all questions and instructions carefully. On-the-Vine Vineyard, Inc., is a small, family-owned winery in Sonoma Valley, California, specializing in limited production of premium wine. The company employs over 12 full-time employees, with records including their ID, name, position, social security number, address, phone number, and emergency contacts. The winery owns several vineyards, each managed by a single employee, and each vineyard has a unique ID, name, address, and size. Wines produced by the winery are identified by a unique ID, name, vintage year, category, vineyard, price, and alcohol percentage, with responsibility assigned to a specific employee. Customers purchase wine online; each customer has a unique ID, name, address, phone, and date of birth. Orders are placed by customers, with each order having a unique number, order date, shipping date, and status; customers can order multiple bottles per order.
Draw an Entity-Relationship Diagram (ERD) that includes all necessary entities, attributes, and relationships based on this description. Specify relationship names and types (1:1, 1:M, or M:N). Relationships should be named with verbs; entities need identifiers; include all attributes. Ensure no entities or relationships are missing or redundant, and avoid missing or incorrect relationship types.
Paper For Above instruction
The Entity-Relationship Diagram (ERD) for On-the-Vine Vineyard's database must comprehensively capture the organizational data concerning employees, vineyards, wines, customers, and orders. The primary entities identified from the business description include Employee, Vineyard, Wine, Customer, and Order. Each of these entities has specific attributes, and their interrelations define how data is interconnected within the system.
Entities and Attributes
Employee: The Employee entity uniquely identifies each employee via an Employee_ID attribute. Additional attributes include Name, Position, Social_Security_Number, Address, Phone_Number, and Emergency_Contact. These details are essential for employee management and communication.
Vineyard: The Vineyard entity is characterized by Vineyard_ID, Name, Address, and Size. Each vineyard is managed by a single employee (a one-to-one relationship), illustrating a direct management link.
Wine: The Wine entity's primary key is Wine_ID, with attributes including Name, Vintage_Year, Category, Price, and Alcohol_Percentage. Each wine is produced at a specific vineyard, establishing a relationship between Wine and Vineyard, indicating the provenance of each wine.
Customer: Identified by Customer_ID, with attributes First_Name, Last_Name, Address, Phone, and Date_of_Birth. Customers are consumers who purchase wines online.
Order: Each order has Order_Number, Order_Date, Shipping_Date, and Shipping_Status as attributes. An order can contain multiple bottles of wine, corresponding to a many-to-many relationship between Orders and Wines, with an associative entity (Order_Details) to manage quantities and specifics.
Relationships and Relationship Types
- Manages: Connects Employee to Vineyard (Employee 1:1 Vineyard). Each vineyard is managed by exactly one employee, while an employee manages at most one vineyard.
- Produces: Links Vineyard to Wine (Vineyard 1:M Wine). Each vineyard produces multiple wines, but each wine is produced at a single vineyard.
- Responsible_For: Connects Employee to Wine (Employee 1:M Wine). The winemaker (employee) responsible for making specific wines.
- Places: Between Customer and Order (Customer 1:M Order). Customers place multiple orders over time.
- Contains: Between Order and Wine (Order M:N Wine). Each order can include multiple wines, and each wine can be part of multiple orders; this relation is managed via an associative entity to record quantities.
ERD Description and Notes
The ERD visually captures these entities and relationships, with clear relation names (manage, produce, responsible for, place, contain) and accurate relationship types. The model ensures data integrity, avoids redundant entities or attributes, and captures the business rules efficiently. For example, the explicit management of vineyards by employees, wines tied to the vineyards and winemakers, and the detailed order structure reflecting customer purchase behavior are all essential for operational and analytical purposes.
Conclusion
This ERD provides a robust framework for managing On-the-Vine Vineyard’s core business data, facilitating effective data storage, retrieval, and analysis, thereby supporting the winery's operational and strategic activities.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
- Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.