Database Design and Data Types

1. For this exercise, find paper and pen. You will design a database on paper. This database is a simple catalog that will be used to keep track of product, customers, and their orders.
2. The database should have the following characteristics:

– A way to store information about many individual customers, including a unique identifier, last name, first name, email address, street address, city, state, zip code, day phone, evening phone, and fax.

– A way to store information about many products including a unique product identifying number, a product name, a product description, product manufacturer, price, and a picture of the product.

– A way to store information about an order including a unique order number, a unique customer identifier, how many items were ordered, the total cost, what shipping method was used, the cost of shipping, tax, the date the order was placed, the date the order was shipped.
3. Draw a diagram of each of these tables, with each category of information named.
4. Normalize, using primary key and foreign key relationships. Use one-to-many relationships where possible.
5. Determine the data type (number, text string, money, date, etc…) of each category of information. Write it next to the category name.
6. Is any critical information missing from the information schema described? Discuss with the class. Hint– a lot is missing!
7. What tools might you use to formalize and improve upon this manual modeling process? Discuss with the class.

1. Return to the design you created in the last exercise. Open your text to page 57. You may need to redraw your design for this exercise.
2. Give proper field names to each item in your design. Tables should use a plural name. Field names should be descriptive, use singular alphanumeric character names, lowercase, with no spaces (use the underscore). Primary and foreign keys columns should end with “_id” (no quotes). Names should be unique except for keys.
3. Determine the data type and length (if applicable) for each category of information in your database, based on the table on page 57.