Exercise 1.01: Organizing data in a relational format

Suppose you are working for a company, ABC Corp. Your manager would like to develop a database that stores clients' contact information, as well as the orders a client has made. You have been asked to determine how to organize the data in a relational format. In addition, the company would like you to define the data types that are appropriate for each field. The following is a list of properties that are to be stored in the relational model:

• Customer Data:
  • Customer ID
  • Customer Name
  • Customer Address
  • Customer Phone Number
• Order Data:
  • Customer ID
  • Order ID
  • Order Price

Perform the following steps to create a relational database structure:

1. First, determine the data types that are appropriate for the data. The ID fields should be int data type, since IDs are typically numeric. For fields containing names, addresses, and phone numbers, a varchar data type is appropriate since it can store general text. Finally, a price can be defined as double, since it needs to be able to store decimal values.
2. Determine how many tables you should have. In this case, you have two sets of data, which means you should have two tables – CustomerData and OrderData.
3. Consider how tables are related to each other. Since a customer can have an order in the order data, you can conclude that customers and orders are related to one another.
4. Next, look at what columns are the same between the two sets of data. In this case, both tables contain the CustomerID column.

Finally, combine all the information. You have two tables, CustomerData and OrderData. You can relate them by using the column they share, which is CustomerID. The relational model would look like the following:

Figure 1.6 – The data for customers and orders organized in a relational format

With this, you now have a fully defined relational structure for your data. This structure with data types can be used to construct a proper relational database.

Now, you will delve into the architecture of MySQL in the following section.