Advertisement
Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

Design a Sample MySQL Database

In this exercise, you design a sample MySQL database to contain customer order information. Create the following list of information that you want to store for each customer:

  • Name

  • Address

  • Phone number

  • Fax number

  • E-mail address

In addition, you need to collect information about which products the customers order. For each order, you need to collect the following information:

  • Date the order is placed

  • Product information for each item in the order

    In this example, the product is T-shirts. Therefore, you need the following information for each item:

    • Number that identifies the specific product (such as a catalog number)

    • Size

    • Price

    • Color

You design the Customer database with this information in mind:

  1. Name your database.

    The database for the order information is named CustomerOrderInformation.

  2. Identify the objects.

    The information list is

    • Customer name

    • Customer address

    • Customer phone number

    • Customer fax number

    • Customer e-mail address

    • Order date

    • Number that identifies the specific product (such as a catalog number)

    • Size

    • Color

    • Price

    The first five information items pertain to customers, so one object is Customer. The order date information pertains to the total order, so another object is CustomerOrder. The remaining four pieces of information pertain to each individual item in the order, so the remaining object is OrderItem.

  3. Define and name a table for each object.

    The CustomerOrderInformation database needs the following tables:

    • Customer

    • CustomerOrder

    • OrderItem

  4. Identify the attributes for each object.

    Look at the information list in detail:

    • Customer ID: One attribute (a unique ID for each customer).

    • Customer name: Two attributes (first name and last name).

    • Customer address: Four attributes (street address, city, state, and ZIP code).

    • Customer phone number: One attribute.

    • Customer fax number: One attribute.

    • Customer e-mail address: One attribute.

    • Order number: One attribute (a unique ID for each order).

    • Order date: One attribute.

    • Number that identifies the specific product (such as a catalog number): One attribute.

    • Size: One attribute.

    • Color: One attribute.

    • Price: One attribute.

  5. Define and name the columns.

    The Customer table has one row for each customer. The columns for the Customer table are

    • customerID

    • firstName

    • lastName

    • street

    • city

    • state

    • zip

    • email

    • phone

    The CustomerOrder table has one row for each order with the following columns:

    • CustomerID: This column links this table to the Customer table. This value is unique in the Customer table, but it’s not unique in this table.

    • orderID

    • *orderDate

    The OrderItem table has one row for each item in an order that includes the following columns:

    • catalogID

    • orderID: This column links this table to the CustomerOrder table. This value is unique in the CustomerOrder table, but it’s not unique in this table.

    • size

    • color

    • price

  6. Identify the primary key.

    The primary key for the Customer table is customerID. Therefore, customerID must be unique. The primary key for the CustomerOrder table is orderID. The primary key for the OrderItem table is orderID and catalogID together.

  7. Define the defaults.

    No defaults are defined for any table.

  8. Identify columns with required data.

    The following columns should never be allowed to be empty:

    • customerID

    • orderID

    • catalogID

    These columns are the primary-key columns. Never allow a row without these values in the tables.

  9. Decide on the data type for storing each attribute.

    • Numeric: CustomerID and orderID are numeric data types.

    • Date: OrderDate is a date data type.

    • Character: All remaining fields are character data types.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win an iPad Mini. Enter to win now!