Metropolis Toys
Metropolis Toys is an independent, family-owned manufacturer of wooden toys. The toys are designed by members of the Whittle family, which has owned the business for more than 125 years.
Once a design for a toy has been approved by the entire family, Shavings Whittle details the manufacturing process so the toys can be made by the company's staff. Toys are then manufactured and shipped directly from the company's single location.
Metropolis Toys sells its merchandise in two ways: through toy stores of all sizes and direct to customers from a catalog. Redwood Whittle, the patriarch of the family, is satisfied with the company's current manual system for handling inventory and orders. However, the younger generation (children Shavings, Chip, and Splinter) believe that the company could work more efficiently if they invested in a database.
With the help of matriarch Birch, the Whittle children have convinced their father to purchase a computer and a database management system. They also hire a database designer to design their database and create an application program for the company's staff to use.
The Whittle family comes up with the following list of major attributes that should be stored in the database:
|
product_number
|
Unique identifier given to each product
|
|
product_name
|
Name of product
|
|
product_description
|
Description of product
|
|
shipping_weight
|
Shipping weight of product
|
|
wholesale_price
|
Wholesale price of product
|
|
suggested_retail_price
|
Suggested retail price of product
|
|
number_in_warehouse
|
Amount of a specific product currently in the warehouse, ready to be shipped
|
|
customer_name
|
Name of a customer
|
|
customer_address
|
Address of the customer
|
|
customer_phone
|
Phone number of a customer
|
|
customer_type
|
Type of customer (store or individual)
|
|
order_date
|
Date on which an order is placed
|
|
quantity_ordered
|
Quantity of a product included on an order
|
|
line_cost
|
Cost of a single line item on an order (computed by multiplying the wholesale price by the quantity ordered)
|
|
order_cost
|
Total cost of an order (computed by summing the line costs)
|
|
order_complete
|
A Boolean indicating whether all products on an order have been shipped
|
|
shipment_date
|
Date on which a shipment is sent to a customer
|
|
quantity_shipped
|
Quantity of a product included on a shipment
|
|
line_weight
|
Weight of a single line item on a shipment (computed by multiplying the shippinjg weight by the quantity shipped)
|
|
total_weight
|
Total weight of a shipment (comnputer by summing the line weights)
|
|
quantity_not_shipped
|
Quantity of a specific product on an order that has not been shipped (initialized to the quantity ordered and decremented by a programn when itrems are shipped)
|
It is clear to the database designer that a few attributes will need to be added to provide unique keys and that some of the attributes specified by the Whittles will need to be split into several pieces to provide a good database design. You should therefore feel free to add/change attributes as necessary. Just be sure that you capture all the information specified by the Whittles.
1. Draw an ER diagram for your database scenario.
2. Design a set of 3NF tables for your database scenario.
3. Uncontrolled concurrent use of a database can lead to serious problems. What are these problems? Why do they occur?
4. Assume that you have been asked to look at the database design being used by an organization. When you see the tables, you realize that they are all in first normal form; none are higher. Explain to the organization's management the three types of problems you would expect to find in these relations.