PROBLEM
The problem emphasized correct usage of the Crow's Foot notation and application of the diagram rules. This emphasis is consistence with the pedagogy of the chapter. The more challenging problems in Chapter 6 emphasize user requirements, diagram transformations, design documentation, and schema conversion. To develop a good understanding of data modeling, you should complete the problems in both chapters.
1) Draw an ERD containing the order and customer entity types connected by a 1-M relationship from customer to order. Choose an appropriate relationship name using your common knowledge of interactions between customers and orders. Define minimum cardinalities so that an order is optional for a customer and a customer is mandatory for an order. For the customer entity type, add attributes CustNo (primary key), CustFirstName, CustLastName, CustStreet, CustCity, CustState, CustZip, and CustBal (balance). For the order entity type, add attributes for the ordNo (primary key), OrdDate, OrdName, OrdStreet, OrdCity, OrdState, OrdZip. If you are using a data modeling tool that supports data type specification, choose appropriate data types for the attributes based on your common knowledge.
2) Extend the ERD from problem 1 with the Employee entity type and a 1-M relationship from Employee to order. Choose an appropriate relationship name using your common knowledge of interactions between employees and order. Define minimum cardinalities so that an employee is optional to an employee. For the Employee entity type, add attributes EmpNo (primary key), EmpFirstName, EmpLastName,EmpPhone,EmpEmail,EmpCommRate (commission rate), EmpDateName. If you are using a date modeling tool that supports data type specification, choose appropriate data types for the attributes based on your common knowledge.
3) Extend the ERD from problem 2 with a self -reference 1-M relationship 1-M relationship involving the Employee entity type. Choose an appropriate relationship name using your common knowledge of organizational relationships among employees. Define minimum cardinalities so that the relationship is optional in both directions.
4) Extend the ERD from problem 3 with the product entity type and an M- N relationship between product and order. Choose an appropriate relationship name using your common knowledge of connections between products and orders. Define minimum cardinalities so that an order is optional to a product, and a product is mandatory to an order. For the product entity type, add attributes ProdNo (primary Key), ProdName, ProdQOH, ProdPrice, and ProdNextShipDate. For the M- N relationship, add an attribute for the order quantity. If you are using a date modeling tool that supports data type specification, choose appreciation data types for the attribute data types for the attributes based on your common knowledge.
5) Revise the ERD from problem 4 by transforming the M-N relationship into an associative entity type and two identifying, 1-M relationship.