Data Relationships

Imagine you are asked to create an application that allows a company to rent cars to potential customers. You may start by creating a list of cars that would be made available to customers. The list may include the type (make, model, and year) of the car and other pieces of information such as options (CD, AC, DVD, etc) that would be of interest to some of the customers. Here is an example of such a list:

When a new customer comes to the store to rent a car, the company clerk would need some of the customer's personal information to keep track of who has the car. The information about the customer may include her name, address, driver's license number, etc. The list of information about the customer may appear as follows:

Once this information is collected, the clerk can present the available cars and their rental rates to the customer so she can select the type of car she wants. The clerk would also be interested to know how long the customer intends to keep the car. After using the car, the customer would bring it back to the store so the car can be made available to other customers.

When creating this application, you may be tempted to enter information about the car in the same list that includes the customer's information:

The problem here is that, when a different customer comes to rent the same car, the clerk would have to enter the same pieces of information. Experience shows that when the same information is manually entered over and over again, it could be different from one list to another as human beings are capable of making mistakes. The solution is to create separate lists: one list for the customers, another list for the cars, etc. When a customer comes to rent a car, the clerk can select the customer's information from one list, select the information about the car from another list, and then process a rental order. This technique tremendously reduces the likelihood of making mistakes because information about each item, whether a customer or a car, is created only once and then made available to other lists that would need that information: this is the foundation of relational databases.

A relational database is an application in which information flows from one list to another. The SQL allows you to create tables and link them so they can exchange information among themselves.

1. Open the Command Prompt and login
2. To create a new database, execute the following statement:
    
   CREATE DATABASE WorldStats1;
   
   (BCR stands for Bethesda Car Rental)
3. To select the new database, execute the following statement:
    
   USE WorldStats1;

The transactions among various objects of a database should make sure information of one object is accessible to another object. The objects that hold information, as we have mentioned already, are the tables.

To manage the flow of information from one table (A) to another table (B), the table that holds the information, A, must make it available to other tables, such as B. There are two issues that must be dealt with:

1. Each record that a table (A) holds should/must be unique among all the other records of the same table (A). For example, if a clerk creates a list of cars on a table, you should make sure that, even when two cars are the exact same type (a car rental company can purchase two to four of the same car at the same time), each car must be uniquely identified so that, when one of them has been rented, the company should know with certainty what car is out and what car is available.
2. A table (A) that holds information must make it available to other tables (such as B). Two tables must not serve the same purpose. For example, you should not have two lists of cars that hold information about the cars that can be rented (you can have different lists of cars; for example, one list may contain the cars that have just been purchased but are not yet registered to be rented, and another list that contains the cars made available for renting but the cars that are available to be rented should be in only one list: this reduces confusion).

To solve the first problem of uniquely identifying records inside of a table, in Lesson 7, we saw that you could create one column or a group of columns used as the primary key. In a relational database, which is the case for most of the databases you will be creating using SQL, each table should have at least one primary key.

To create a primary key column using SQL, as we did in Lesson 7, on the right side of the column definition, type PRIMARY KEY (remember, SQL is case-insensitive). Here is an example:

You don't have to specify the primary key at the same time you are creating the column. Before the end of the statement that creates the table, you can enter name of the column and specify PRIMARY KEY. To do this, on a separate line, type PRIMARY KEY() and, in the parentheses, enter the name of the column that will serve as the primary key. Here is an example:

CREATE TABLE Students (
StudentID INTEGER NOT NULL,
FirstName varchar(20),
LastName varchar(20),
HomePhone varchar(16),
PRIMARY KEY(StudentID));

Practical Learning: Creating a Tables With a Primary Key

• To create a table that has a primary key, execute the following statement:
   

  CREATE TABLE Continents ( ContinentID integer auto_increment not null, ContinentName varchar(60), ContinentArea varchar(40), ContinentPopulation varchar(40), Primary Key(ContinentID));

Foreign Keys

In our introduction to relationships, we illustrated a table that could be used to enter a customer's information and the car he wants to rent:

We also mentioned that it was not effective to put these two categories of information, namely the customer and the car's, in the same list. The reason is that the same customer may come at different times to rent different types of cars and the same car is regularly rented by different customers. To reduce the likelihood of mistakes, you should separate these two categories, each in its own list:

This time, if you keep these two lists separate, when it is time to rent a car to a customer, you can use another list that allows the clerk to select the name of the customer, followed by the car she wants to rent:

To make this scenario work, there must be a column in the Rental Order list that represents the customers: this would be the Customer column in our illustration. The column that belongs to the Rental Order list but is used to represent the customers is called a foreign key. This column behaves like an ambassador who is not a citizen of the country where he works but instead represents his native country.

Because a foreign key is used to represent a table other than the one where it resides, the table that the foreign key represents must have a primary key that would insure the uniqueness of records in the original table. The table that holds the necessary values and that has the primary key can be referred to as the parent table. In the above illustration, the Customers table is the parent. The table that holds the foreign key is referred to as the child table, which is the case for the Rental Orders list of our illustration.

To create a foreign key, you can start by adding the necessary column in the table that will need or use it. There are rules and suggestions you should or must follow. As a suggestion, the name of the column used as the foreign key should be the same as the primary key of the table it represents. As a rule, the data type of the primary key of the parent table must be the same as the data type of the foreign key.

To create a foreign key, when creating the table, before the closing comma of the name of a column (if the column is not the last in the table) or the closing parenthesis of the table (if the column is the last), you can type REFERENCES followed by the name of the parent table with parentheses. In the parentheses of the name of the parent table, enter the name of the primary key column. Here is an example:

mysql> CREATE TABLE Departments (
    -> DepartmentID INTEGER AUTO_INCREMENT NOT NULL,
    -> Department varchar(50),
    -> PRIMARY KEY(DepartmentID));
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE Employees (
    -> EmployeeID INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    -> FirstName varchar(20),
    -> MI char(1),
    -> LastName varchar(20),
    -> HourlySalary decimal(6,2),
    -> DateHired DATE,
    -> DepartmentID INTEGER REFERENCES Departments(DepartmentID));
Query OK, 0 rows affected (0.41 sec)

mysql>

When you create a table like this, the interpreter would know that, in the Employees table, the DepartmentID column is a foreign key. Still, if you want to indicate that the column will be used as a foreign key, you can formally specify it. To do this, on one line, first create the column by specifying its name, its data type, and other options related to a column. Then, on another line, type

FOREIGN KEY() REFERENCES ParentTable(ParentPrimaryKey)

In the parentheses of the FOREIGN KEY expression, enter the name of the column that was created and that would serve as the foreign key.

Practical Learning: Creating a Foreign Key

• To create a table with a foreign key, execute the following statement:
   

  CREATE TABLE Countries ( CountryID integer auto_increment not null, DepartmentID integer not null, CountryName varchar(60), CountryArea varchar(40), CountryPopulation varchar(40), CountryCode char(2), PRIMARY KEY(CountryID), FOREIGN KEY(DepartmentID) REFERENCES Continents(ContinentID));