INFS $4790/5790$

Data Modeling

Unlimited Furniture Outlet $($UFO$)$ has three stores throughout southern Florida. You have been hired to

create a database to help track sales, customers, and employees for all three stores. During your initial

meeting, you have gathered the following information.

Unlimited Furniture Outlet $($UFO$)$ has three stores, although the owner would like to expand in the near future. For

each store, the store number, location $($street$,$ city, state, and zip$),$ and total square footage needs to be recorded. The system

should support expansion by supporting the storage of information on new stores that are going to be opened before

specifying any inventory or hiring employees for that store.

Each store sells many different furniture products. Some products may be sold at multiple stores, but every product

is sold by at least one store. The quantity of each product at each store should be tracked by the system. For each product

that UFO sells, the following information should be kept in the system: product number, brand, description, and retail price.

Most products are individual items. However, it is possible that one product can be composed of several other products. For

example, a bedroom set $($product #$100)$ may consist of a specific bed $($product #$200),$ mattress $($product #$300),$ box spring

$($product #$301),$ and a specific chest of drawers $($product #$203).$ As stated, not all products are a part of another product;

however, if a product is part of another product, it is a part of only that one other product. For example, a specific bed

$($product #$200)$ may be sold individually, or it may be a part of only one bedroom set $($product #$100).$

A single customer can place many orders, and each customer must have placed at least one order to be in the system.

Each order must be for at least one product, but an order may be for many different products. Each order is associated with

one and only one customer. Further, a product can be ordered many times, although some products have never been ordered.

For each product in each order, the quantity of that product and the price charged per unit for that product must be recorded.

For each order, the order number, order date, and credit authorization status are recorded. An employee can complete several

different orders, but each order is completed by only one employee. Each order must be completed by an employee, but

some employees have not completed any orders. For each employee, the following information should be kept by the

system: name, date of hire, and title. Each employee must work at one and only one branch store; although, obviously, each

store can have many employees.

Information about customers includes customer name, telephone number $($area code and number within area code$),$

and date of birth. Customers may have one or more accounts with the company, but not all customers have an account. An

account is held by one and only one customer. For each account, the account number, balance, and account type $($personal or

commercial$)$ needs to be recorded. A given order may be charged to a customer$$s account, although some orders are not

charged to any account. Even if an order is not charged to an account, the system should still track which customer has

placed that order. A given order can be charged to only one account, no matter how many accounts the customer has. An

account can have many orders charged to it$,$ but some accounts will not have had any charges yet.