Question

Requirements: Based on the following information, draw an E-R diagram and a set of relations in 3rd normal form. Please indicate any assumptions that you have made. Wally Los Gatos, owner of Wally’s Wonderful World of Wallcoverings, has hired you as a consultant to design a database management system for his chain of three stores that sell wallpaper and accessories. He would like to track sales, customers, and employees. After an initial meeting with Wally, you have developed a list of business rules and specification to begin the design of an E-R Model:

 Customers place orders through a branch

 Wally would like to track the following about customers: Name, Address, City, Province/State, Postal/Zip code, Telephone, Date of Birth, 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, Type

 A branch may have many customers  Orders are composed of one or more items

 The following information about each order needs to be recorded: Order Date, Credit Authorization Status

 Items may be sold by one or more branches

 We wish to record the following about each item: Description, Colour, Size, Pattern, Type

 An item can be composed of multiple items. For example, a dining room wall covering set may consist of wallpaper and borders.

 Wally employs fifty-six employees

 He would like to track the following information about employees: Name, Address (Street, City, Province/State, Postal/Zip Code), Telephone, Date of Hire, Title, Salary, Skill(s), Age

 Each employee works in one and only one branch

 Each employee may have one or more dependents. We wish to record the name of the dependent as well as the age and relationship

 Employees can have one or more skills.

Answer 1

E-R diagram