Wally Los Gatos, owner of Wallys 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 specifications:
Customers place orders through a branch. Wally 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 (Address, City, State, Zip Code), and Square Footage. 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 sold by one or more branches. We wish to record the following about each item: Description, Color, Size, Pattern, and Type. An item can be composed of multiple items;
for example, a dining room wall covering set (item 20) may consist of wallpaper (item 22) and borders (item 23). Wally employs 56 employees. He would like to track the following information about employees: Name, Address (Street, City, State, Zip Code), 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. We wish to record the name of the dependent as well as the age and relationship. Employees can have one or more skills.