Question 2 Analyze the business rules of a Wall Coverings Company carefully and use it to answer the given questions. The company has three stores that sell wallpaper and accessories. Customers place orders through a branch. The owner would like to track the following about customers: Name, Address, City, State, Zip Code, Telephone, Date of Birth, and Primary Language. A customer may place many orders. A customer does not always have to order through the same branch all the time. Customers may have one or more accounts, although they may also have no accounts. The following information needs to be recorded about accounts: Balance, Last payment date, Last payment amount and Type. A branch may have many customers. The following information about each branch needs to be recorded: Branch Number, Location (Street, City, State, Post Code), and Square Footage (area). A branch may sell all items or may only sell certain items. Orders are composed of one or more items. The following information about each order needs to be recorded: Order Date and Credit Authorization Status. Items may be EXAMINER: GEORGE KODJO ANNI Page 1 of 4 sold by one or more branches. The company wishes to record the following about each item: Description, Color, Size, Pattem, and Type. An item can be composed of multiple items; for example, a dining room wallcovering set (item id 20) may consist of wallpaper (item id 22) and borders (item id 23). The company employs 56 employees and would like to track the following information about employees: Name, Address (Street, City. State and Postcode). Telephone, Date of Hire, Title, Salary, Skill, and Age. Each employee works in one and only one branch Each employee may have one or more dependents. The company wishes to record the name of the dependent as well as the age and relationship. (a) Based upon the provided information, draw an Entity-Relationship (ER) Diagram using the Crow's foot model symbols and include all attributes. 18 marks] (b) In the ER diagram: (1) Identify the main relationship types (connectivity) between the entities. (1) Determine the multiplicity constraints (cardinality) for each relationship [4 marks] Determine primary key and foreign key attributes for cach entity. (4 marks (d) Resolve all many-to-many relationships into one-to-many relationships. (4 marks] Using SQL statements, create a table for the EMPLOYEE entity. Specify all constraints, if any. (5 marks] Question 2 Analyze the business rules of a Wall Coverings Company carefully and use it to answer the given questions. The company has three stores that sell wallpaper and accessories. Customers place orders through a branch. The owner would like to track the following about customers: Name, Address, City, State, Zip Code, Telephone, Date of Birth, and Primary Language. A customer may place many orders. A customer does not always have to order through the same branch all the time. Customers may have one or more accounts, although they may also have no accounts. The following information needs to be recorded about accounts: Balance, Last payment date, Last payment amount and Type. A branch may have many customers. The following information about each branch needs to be recorded: Branch Number, Location (Street, City, State, Post Code), and Square Footage (area). A branch may sell all items or may only sell certain items. Orders are composed of one or more items. The following information about each order needs to be recorded: Order Date and Credit Authorization Status. Items may be EXAMINER: GEORGE KODJO ANNI Page 1 of 4 sold by one or more branches. The company wishes to record the following about each item: Description, Color, Size, Pattem, and Type. An item can be composed of multiple items; for example, a dining room wallcovering set (item id 20) may consist of wallpaper (item id 22) and borders (item id 23). The company employs 56 employees and would like to track the following information about employees: Name, Address (Street, City. State and Postcode). Telephone, Date of Hire, Title, Salary, Skill, and Age. Each employee works in one and only one branch Each employee may have one or more dependents. The company wishes to record the name of the dependent as well as the age and relationship. (a) Based upon the provided information, draw an Entity-Relationship (ER) Diagram using the Crow's foot model symbols and include all attributes. 18 marks] (b) In the ER diagram: (1) Identify the main relationship types (connectivity) between the entities. (1) Determine the multiplicity constraints (cardinality) for each relationship [4 marks] Determine primary key and foreign key attributes for cach entity. (4 marks (d) Resolve all many-to-many relationships into one-to-many relationships. (4 marks] Using SQL statements, create a table for the EMPLOYEE entity. Specify all constraints, if any. (5 marks]
