0:00
Hey. Good day, everyone. Welcome back.
0:06
Today we will be addressing the second topic in our course on database design and management.
0:15
And this topic is going to be the relational model. We have several objectives for this topic.
0:21
First, we're going to try to learn about the conceptual foundations of the relational model,
0:27
and try to understand the differences between a relation
0:34
and a regular table. I will also be introducing you to some of the terminology associated with the relational model.
0:42
And a major part of today's lecture is going to be to try to familiarize you
0:47
with the concept of keys. There are many different types of keys in database modeling,
0:54
and we will explore these different types of keys today. Further, we will learn how one of these types of keys,
1:01
specifically a foreign key, is used to implement relationships between tables
1:09
within the relational model. Toward the end of the lecture today, we will move into a discussion of something
1:17
known as normalization, the normalization process.
1:22
And we will talk about normalization in the context of dependencies.
1:28
And we will introduce the process for normalizing our relations.
1:35
So let's begin. First, I'd like to talk today about something known as an entity.
1:41
In the database world we can consider an entity to be something that is important.
1:46
And we want to store information about this entity in the database. Conceptually an entity should represent a single theme,
1:55
or a single business concept. For example, an employee might be an entity.
2:02
A department might be an entity. A project might be an entity.
2:08
Each of these is a single business concept, about which we might want to track information
2:15
on several different attributes. Using the employee example, we may
2:21
want to track the employee's name, the department in which the employee works, the employee's telephone
2:29
number, and so forth. We are going to be learning much more about entities when we explore our fourth topic, which will address
2:37
the entity relationship model. Next I'd like to talk about a concept known as a relation.
2:44
A relation is a table. If you recall, last time we said that a table is essentially
2:53
a two dimensional grid, which contains both rows and columns.
3:00
A relation is a specific type of table. That is, in order for a table to qualify as a relation,
3:09
the table must have certain characteristics. Let's see what these characteristics are.
3:16
As you can see on your screen, several conditions must be met in order for a relation to qualify as a table.
3:23
First, the rows in the table must contain information
3:29
about instances of an entity. For example, if we are working with an employee relation,
3:38
then each row in the table would represent a single employee.
3:45
Columns in a relation are used to represent the attributes of the entity.
3:52
Using our employee relation as an example, we may have several columns, such as an Employee
4:00
ID, or an employee Name, or an employee telephone number.
4:05
The values stored in the column are all going to be in this example, employee ID numbers, employee
4:13
names, or employee telephone numbers. Within the table we have cells.
4:20
A cell can be thought of as the intersection of a row and a column.
4:25
In order for a table to qualify as a relation, the cells in the table can only hold a single value.
4:34
That is, we are not allowed to store more than one value in the same cell. Further, all of the values within a specific column
4:44
must be of the same data type. For example, if we create an Employee ID column,
4:49
we would specify its data type. We may, for example, use an integer data
4:55
type for the employee ID. This rule says that all of the values which
5:02
appear in that column then must be integers. Further, every column must have a unique name.
5:09
This is necessary so that the database management system
5:14
are able to determine which column it is to which we are referring when we are making requests
5:20
of the database. We could not, for example, create two columns in a table that were both called Employee ID.
5:29
It is possible to have multiple tables in a database. And we may have an Employee ID in one table
5:37
and an Employee ID in another table. But we cannot have two columns within the same table that have
5:44
the same name, such as Employee ID. Another characteristic of an relation
5:50
is that no two rows within the table can be identical.
5:56
This s not mean that we are not allowed to have identical values within a column
6:02
as we move from row to row. But rather this is saying that if we consider the row in its
6:08
entirety, if we take all of the values in the row together, they cannot be identical to any other row in the table.
6:17
And then the final two characteristics of a relation are that the order of the rows, and the order of the columns
6:25
cannot matter. Let's take a look at a sample relation.
6:31
As we see, here we have a table which contains three columns, EmployeeNumber, FirstName,
6:38
and LastName. The table also contains four rows.
6:44
Each row in this table represents a single employee. And you will note that this table meets
6:51
all of the characteristics that we discussed on the previous slide. So here we see a table with three columns, EmployeeNumber,
7:01
Phone, and LastName. But this table has several problems with it,
7:07
that mean it cannot qualify as a relation. The first problem is if we look at the values stored
7:16
in the Phone column for employee number 100. Here we have more than one value stored in this cell.
7:24
In a relation we are not allowed to store more than one value for an attribute within a given cell.
7:33
Another problem with this table that disqualifies it from being a relation, is that two of the rows are identical.
7:41
We see that we have two rows, which have employee number 100-- a phone number of 215-7789,
7:51
and a last name of Cadley. Remember that when all of the values for a row
7:57
are considered together, there must be something that is unique about the row.
8:02
No two rows can be identical to each other. If they are, the table does not qualify as a relation.
8:10
What we can take from this example is an interesting point. And that is, all relations are tables,
8:18
but not all tables are relations. So that's an important point that you can remember.
8:25
Let's further expand our database vocabulary a little bit by looking at some synonyms.
8:31
Depending upon which textbook you read, or which lecture you listen to, or which database manager
8:38
you are speaking with, you may find that different people use different words
8:45
for the same concept. This is quite common in the database world. Examples here are table, relation, and file.
8:56
All generally refer to the same concept in the context of the database world.
9:02
Note that in the modern era people will rarely refer to a database table as a file.
9:08
Other synonyms include row, record, and tuple.
9:13
The horizontal data structure within a table might alternately be referred to by one of these words,
9:20
depending upon with whom you're speaking. Finally, the vertical data structure within a table
9:26
might alternately be referred to as a column, a field, or an attribute.
9:31
These all refer to the same concept, within the broader context of the database world.
9:38
Now we're going to move on to a new topic. And this is a critically important topic for understanding the relational model.
9:46
And that is the notion of keys. The general idea here is that a key
9:52
is a column within a relation whose values are used to identify a row.
10:01
Now keys come in many different varieties. Some keys are unique and some keys are non-unique.
10:10
If a key is unique, what that means is that the values within the key column
10:16
will all be unique within the entire table. For example, if we have an employee table, which
10:23
contains an employee ID, no two employees would be allowed to have the same ID.
10:30
That would be a unique key. On the other hand, we have non-unique keys.
10:36
And in non-unique keys the idea is that the value of the column
10:42
may be the same for more than one row within the table. Non-unique keys are generally used
10:48
to categorize the rows within a table into groups.
10:53
For example, if I have an employee table, one of the attributes might be Department ID.
11:00
So let's say that Department ID number two is the accounting department.
11:06
In this case, the Department ID is a non-unique key within the employee table, and more than one employee
11:15
may have a value of two for the Department ID attribute.
11:22
In the coming slides we will take a look at many different types of database keys.
11:27
But before I begin this exploration of keys, I wanted to present what I call Dan's Typology of Database
11:34
Keys. I hope this will help you to understand the relationships among the different types of keys
11:40
that we will discuss today. So you can see that all database keys can be subdivided into two major groups, unique or non-unique.
11:49
Among the non-unique keys we will discuss something called a foreign key.
11:54
And among unique keys, you can see that there are several different types of unique keys, candidate
12:01
keys, composite keys, primary keys, and surrogate keys.
12:06
We will begin by examining a composite key. Remember that a composite key is a unique key,
12:13
according to Dan's Topology of Database Keys. And a composite key is called a composite key
12:21
because it is composed of two or more columns. So that is, we combine the values of two or more columns
12:30
together in order to get uniqueness. This is sometimes a challenging concept for people
12:38
to understand initially. So I think the best way for us to explore the composite key
12:45
is through an example. Let's imagine that we want to take a flight from Orange
12:52
County to Washington, DC. So we are going to purchase a ticket for this flight,
12:59
and our flight will have a specific number. For example, we might be on the flight number 34.
13:07
Within the airline industry a given airline might operate Flight 34 once every day.
13:14
And what that means to the airline is it's a flight from Orange County to Washington, DC.
13:21
So Flight 34 is a flight from Orange County to Washington, DC, and we make that flight once every day.
13:30
Because Flight 34 occurs every day, we cannot use that value.
13:36
We cannot use Flight 34 to uniquely identify a specific
13:42
flight, that is a flight with a real airplane, where a specific group of people get on board,
13:50
and fly from Orange County to Washington, DC. In order to get to uniqueness, we
13:56
would need to combine that flight number with another piece of information.
14:01
And one piece of information that we could use would be the date. Let's look at this table that's currently shown on your screen.
14:09
As you can see, we have United Airlines Flight 36. And it exists in more than one location
14:17
in the table, which means that the value UA 36 is not unique.
14:23
However, if we combine UA 36 with the date
14:29
upon which that flight took place, those two values together are unique within the table.
14:36
And this is true for all of the flights that are listed within this table. If we examine the flight number column,
14:44
we can see that it's possible to have more than one flight with the same flight number.
14:50
And if we examine the date column, we can see that it's possible to have more than one flight
14:57
on the same date. But the combination of a flight number and a date is unique.
15:04
We could therefore combine those two values together, and use them as a composite key.
15:12
Next, I would like to talk about a candidate key. A candidate key is called a candidate key,
15:19
because it has the potential to become something that we call a primary key.
15:25
So just like in a presidential election a candidate has the potential to become the president.
15:32
In the database world, a candidate key has the potential to become the primary key.
15:38
And if you remember Dan's Topology of Database Keys, a candidate key was the second of our four
15:44
types of unique keys. Since a candidate key has the potential to become a primary key, it's important for us
15:51
to understand what a primary key is. As it's currently shown on your slide,
15:58
a primary key is chosen to be the main identifier
16:03
for a relation. That is to say, the primary key is a unique key,
16:09
and if we know the value of the primary key,
16:14
then we will always be able to locate a specific single row
16:19
within the table. For example, if I have an employee relation, which
16:25
contains a primary key column that is named Employee ID,
16:30
and I know that the employee ID in which I am interested is employee ID number two, I should
16:38
be able to uniquely identify a specific employee-- just one
16:45
employee-- within that table. Let's see an example. Here we have a table with three columns, Employee Number,
16:53
FirstName, and LastName. In this table we are using EmployeeNumber
16:58
as the primary key. That means that if I know a specific employee number,
17:06
I should be able to locate a single specific employee within the table.
17:12
For example, if I know that the employee number in which I am interested is employee number 107,
17:20
there should only be one employee number 107 within this entire table.
17:26
And I can then use that value to identify the employee-- in this case, Shir Anavi.
17:33
The next type of key that I would like to discuss is called a surrogate key. Now a surrogate key is a unique key.
17:40
It is typically a numeric value, like an integer. And it is intentionally added as a new column to a relation
17:51
by the database designer for the purpose of serving as the primary key.
17:56
Surrogate keys are often used when we do not have a column within the table that would naturally
18:04
serve as a unique identifier, that is, as a primary key.
18:09
On the previous example, we saw an employee table that contained an EmployeeNumber.
18:16
In that example, the EmployeeNumber really had no meaning.
18:21
The numbers were 100, 101, 102, and so forth.
18:27
Those numbers had no meaning outside of the database. In that case, we might consider EmployeeNumber
18:34
to be a surrogate key. One of the common uses of surrogate keys
18:40
is to avoid having to use a composite primary key in a table.
18:46
Let's see an example. Here we see our airline flight table again.
18:52
We have the Flight Number column and the Date column. And we said in a previous example
18:57
that if we combine those two values together, we can get uniqueness.
19:03
But in this case, we've added an additional column, which I've called Flight ID.
19:09
And it's just another way of ensuring that each row is unique.
19:14
I can uniquely identify each row simply by knowing the Flight ID.
19:20
Our next topic is to discuss relationships between tables.
19:25
And here we will introduce a concept known as a foreign key. In the business world it is very common for relationships
19:35
to exist among business objects, or business concepts. For example, employees can work in departments.
19:44
In most businesses, each employee will work in just one department. But each department may contain many different employees.
19:54
As another example, each project within a company may be assigned a project manager.
20:00
But each project manager may simultaneously manage many different projects.
20:08
So these business concepts, these business themes, are related to each other.
20:13
In the database world we establish relationships between the tables in our database
20:21
by using matched pairs of values. To establish these relationships,
20:28
we need to implement something called a foreign key. Put simply, a foreign key is a primary key
20:37
from one table, which is added or placed into another table for the purpose of linking the records
20:44
in those tables together. The key value is called a foreign key
20:50
in the table that receives the new column. So we create a new column in a table
20:56
that is the primary key from another table. This new column is called the foreign key
21:02
in the table which receives that column. Here we see an example.
21:07
On the left we have a project table, which is just represented here by its attributes.
21:14
So each project contains a Project ID, a Project Name, and a Manager ID.
21:21
On the right, we have a Manager table, which contains two columns.
21:26
They are the Manager ID and the Manager Name. In this case, Manager ID within the Manager table
21:35
is the primary key, and we have placed Manager ID into the Project table to serve as a foreign key.
21:43
In this way, if we know the Manager ID for a specific project, we can easily
21:50
determine the name of the manager by using that ID to look inside the Manager table.
21:59
In this example, we have two tables, one named Department and one named Employee.
22:07
Here we have added Department ID, which was the primary key in the Department table
22:15
into the Employee table, where it will serve as a foreign key.
22:20
In this way, if we know the Department ID for a specific employee, we can follow that relationship
22:28
over to the Department table, and use that information to determine the name of the department
22:35
in which the employee works. One of the very useful capabilities that we gain by using these primary key,
22:42
foreign key relationships is the ability to enforce something called referential integrity.
22:49
Now put simply, referential integrity means that each value of a foreign key
22:55
must match a value of an existing primary key. And the database management system,
23:02
in enforcing referential integrity, helps us to preserve the quality of the data in our database.
23:11
For example, here we have a project table, shown toward the top, and a customer table,
23:17
shown toward the bottom. If I want to add a new project to the project
23:23
table-- in this case, it's going to be project number 113-- and I propose to link this new project to customer ID number
23:33
five, what referential integrity means is that the database will
23:38
look in the customer table, and will check to see whether a customer with customer
23:45
ID number five actually exists. If customer ID number five does not actually
23:52
exist in the customer table, then the database will not allow me to add the new project.
23:59
Note here, that customer ID in the project table is acting as a foreign key, whereas customer
24:08
ID in the customer table at the bottom is the primary key. Also note, that in the project table
24:15
at the top, more than one row can have the same customer ID.
24:22
In this case, we have two rows which have customer ID number two.
24:28
This simply means that these two projects, DB upgrade,
24:34
and new email server, are both associated with our second customer, whose name in this case is Priya.
24:42
Customer ID is a foreign key, but as we can see, because more than one row can
24:50
have the same value for the customer ID, this means that foreign keys are non-unique keys.
24:59
If you recall Dan's Topology of Database Keys, remember that we subdivided all database keys into two groups,
25:09
unique and non-unique. And on the right side of your screen we can see that foreign keys are non-unique.
25:18
Hopefully, based upon the previous example, you can understand why.
25:25
The next topic that I would like to discuss is that of null values.
25:32
In the database world a null value simply means that no data exists within a particular cell.
25:40
That is, you can think of this as an empty cell in the table.
25:45
Note that a null value is different from a zero, a space
25:50
character, an empty string, or a tab character, or any other character.
25:57
A null value is empty. It's nothingness. Null values in the database world are sometimes beneficial,
26:04
but they can also cause us problems. The reason why is that null values can be ambiguous.
26:12
A null value can potentially mean many different things. For example, a null value might indicate
26:19
that the proper value for a given row has not yet been determined. Or a null value might indicate that the proper value is simply
26:29
not known or is missing. If you recall when we were discussing our previous topic,
26:36
we described some of the problems with lists. And we saw that when we use a list approach for storing data,
26:44
the potential exists for us to add new rows to the list, which contain many different null values,
26:53
many different empty cells. One of the great advantages of the relational model
26:58
is that it helps us to design data structures in such a way that we can minimize, or in many cases,
27:06
entirely eliminate empty cells within our tables. To achieve this, we need to go through a process called
27:14
normalization, which I will discuss a little bit later. First however, I would like to talk about something
27:22
called a functional dependency. Now this is a very technical term for a very simple concept.
27:29
A functional dependency is simply a relationship between the different attributes
27:36
within a table. And it says that the value of one attribute
27:41
can be used to find the values of other attributes.
27:46
As an example, if we know the price of one delicious Girl Scout cookie, and we know that a box of Girl Scout cookies
27:53
contains 12 cookies in total, then we can use that information, the price of a single cookie,
28:01
and the quantity within a box, to determine the price of a box of cookies.
28:06
That is to say, the cookie price and the quantity determine the box price.
28:13
The box price is functionally dependent upon the cookie price, and the number of cookies in the box.
28:20
In this case, the attributes on the left side of our screen,
28:26
that is cookie price and quantity, are called determinants, because we
28:32
can use those values to determine the values of other attributes within the table.
28:40
Next, let's review some interesting characteristics of the relational model. We learned earlier about candidate keys.
28:49
If an attribute within a relation is a candidate key, by definition
28:54
it must functionally determine all of the other non-key attributes in the row.
29:02
Recalling that a candidate key will eventually be selected and promoted to the status of a primary key,
29:11
then by extension a primary key must also functionally
29:16
determine all of the other non-key attributes in the row. Let's look at some examples.
29:23
Let's say that we know an EmployeeID. If an EmployeeID is a candidate key, or a primary key,
29:33
by definition we should be able to use that EmployeeID to find the values of the other attributes
29:40
in the row that are associated with that employee. In this case, we have two additional attributes
29:48
in our relation named employee last name and employee phone.
29:53
And we can see that if we know the EmployeeID, we should be able to find these other attributes
30:00
values within the table. Similarly, if I have a project table and I know the ProjectID,
30:08
the ProjectID is a determinant. And I can use the ProjectID to find
30:14
the values of other attributes associated with a given project-- in this case, project name or the start date
30:22
for the project. Now we can introduce the concept of data normalization.
30:30
Although I will describe the normalization process here,
30:35
we will come back to this topic again in a future lecture. Data normalization then is a process
30:43
that a database designer goes through in order to determine if a relation is
30:51
what we call well formed. A well formed relation is one which
30:58
is not susceptible to the three types of anomalies
31:03
that we described in our previous lecture. If you recall we described three different types of anomalies.
31:10
They were deletion anomalies, update anomalies,
31:16
and insertion anomalies. A well formed relation is not susceptible to any
31:23
of these types of anomalies. So another way of thinking about the data normalization process
31:30
is that we are attempting to create relations in which we
31:36
can insert new data, delete existing data, or modify existing data without creating
31:43
one of these anomalies. There are two major design principles that are associated with the normalization process.
31:52
First, as a general rule, we need to remember that in order
31:58
for a relation to be considered well formed every determinant within the relation
32:05
must also be a candidate key. That is every determinant in the relation
32:11
must also be a candidate for promotion to the status of primary key.
32:18
Our second principal is that if we encounter a relation that
32:23
is not well formed, we're going to need to break that relation apart into two or more
32:30
smaller relations, with the goal of making those smaller relations well formed.
32:37
I'll now give you a very important tip that will help you to learn to design well formed relations.
32:45
And that is simply this, as a general rule a well formed relation will not encompass more
32:52
than a single business concept. If you have a relation that contains non key attributes
33:02
for more than one business concept, then it is almost certainly not a well formed relation.
33:11
And we will need to break that relation into smaller relations in order to successfully complete the normalization
33:20
process. Let's look at a few examples. In the first case we have a StudentID,
33:27
which is a determinant. And if we know the StudentID, then we might also know the name of the student,
33:35
the dorm in which that student lives, and the cost of living in that dorm. However, if the cost of living in the dorm
33:44
can be determined by the name of the dorm, then we're going to need to break this relation apart
33:52
into two relations. In this case, we would have a student relation,
33:57
which contains StudentID as a primary key, StudentName,
34:02
and DormName as a foreign key. And then we would have a dorm table,
34:09
which contains DormName as the primary key, and DormCost as a non-key attribute.
34:17
Here's another example. In this case, assume that we need to record meetings between an attorney
34:25
and one of the attorney's clients. To describe the meeting we would need
34:30
to know the attorney's ID and the client's ID. That is, if we know the AttorneyID and the ClientID,
34:39
then we can determine the name of the client, the date upon which the meeting will take place,
34:46
and the duration of the meeting. However, if a ClientID can be separately used
34:54
to determine the client name, then the client name should be removed from our original relation,
35:03
and placed into its own relation using ClientID as the primary key.
35:09
In this case, the result would be one table, which contains AttorneyID, ClientID,
35:17
MeetingDate, and Duration. And within this table AttorneyID and ClientID
35:24
together determine the MeetingDate and the Duration. And then we have a second table which
35:32
contains two attributes, ClientID and ClientName.
35:37
Within this table, if we know the ClientID, we can easily determine the name of the client.
35:43
ClientID in this table serves as a primary key, which links the clients in the table back to the meeting
35:52
table. Within the meeting table ClientID serves as a foreign key link.
35:59
What we see on your screen right now is a series of steps through which a data designer must
36:07
move in order to ensure that a relation is well formed.
36:13
Our objective for this class is going to be to normalize our data tables until the point where
36:19
they are in something called third normal form. In order to arrive at third normal form
36:27
we will need to get our tables into first normal form,
36:33
and then into second normal form, eventually arriving at third normal form.
36:39
It is important to note that there are higher normal forms, which have been defined-- fourth normal form, fifth normal form,
36:47
six normal form, and so forth. However, for the purposes of this class,
36:54
and for the purposes of the vast majority of business data needs, third normal form
37:01
is more than sufficient. Let's begin then by defining first normal form.
37:07
One easy way to think about first normal form is that a relation is in first normal form
37:14
if it does not contain any multivalued attributes.
37:19
Another way of saying that is that every attribute value is atomic.
37:25
We are not storing more than one value in each cell of the table. An extremely important point to remember
37:33
is that all relations are, by definition, in first normal form.
37:40
At the beginning of this lecture, we talked about the characteristics of a relation.
37:46
And we said that all relations are tables, but not all tables are relations.
37:53
If a table meets the definition of a relation, that table is also by definition in first normal form.
38:03
Here we see an example of a table that is not in first normal form.
38:09
Again, by definition, that means that this table is not a relation.
38:15
How can we tell? Well, the table contains multivalued attributes.
38:20
That is, for the same order within this table, we have multiple values stored in our Product ID, Product
38:30
Description, Product Finish, Unit Price, and Ordered
38:36
Quantity columns. Because we have more than one value in each cell,
38:42
this cannot be a relation. And it is therefore not in first normal form.
38:48
Here's an example of the same table, which is in first normal form, and by definition is also a relation.
38:57
So what we can see here is that each cell within the table
39:03
contains one value, a single value. That is, values within the table are atomic.
39:13
Next I'd like to talk about second normal form. A critical point to remember here
39:19
is that in order to qualify as being in second normal form,
39:25
a table must first meet all of the criteria of first normal form, and then it must meet
39:32
and additional criterion. Specifically, beyond all of the requirements
39:37
of first normal form, every non-key attribute within the table must be fully functional dependent
39:46
upon the entire primary key. This means that the values of the primary key
39:54
must be able to fully functionally determine the values of all of the other non-key attributes
40:03
within the table. Another way of saying this is that we cannot have any partial
40:08
dependencies among the attributes within the table. Here we see an example illustrated in something
40:15
called a dependency diagram. Looking at this relation, we can see that the relation
40:21
has a composite primary key. The attributes which comprise the composite primary key
40:28
are Order_ID and Product_ID. We can identify these because they are underlined.
40:35
Now what this means is that the combination of an Order_ID value and a Product_ID value should
40:44	be able to uniquely identify every row within this table.
40:50	However, we see that we have partial dependencies within the table. On the right side of the relation, as an example,
40:58	we see that several non-key attributes-- namely Product-Description, Product_Finish, and Unit_Price
41:08	are dependent upon Product_ID. That is, those values are not dependent
41:15	upon the entire primary key. If I know a product ID, then I also
41:21	know the Product_Description, the Product_Finish, and the Unit_Price.
41:27	I do not need to know a Product_Id and an Order_ID
41:32	in order to know the Product_Description, finish, and price.
41:38	This is called a partial dependency. Although this relation is in first normal form,
41:44	it cannot be in second normal form until we remove these partial dependencies.
41:51	In general, the strategy that we use for moving from a lower normal form to a higher normal form
41:59	is to break the relation apart into smaller relations.
42:05	So if I want to go from a first normal form relation, which
42:10	is shown here, to a second normal form relation, I will need to break this relation apart
42:17	into smaller relations. On this slide, we see that we have
42:23	taken our original relation, and broken it apart into three separate relations.
42:29	At the top, we have a relation called ORDER_LINE, followed by a relation called PRODUCT,
42:37	followed by a relation called CUSTOMER_ORDER. By breaking our original relation into these three
42:44	relations we have removed all of the partial dependencies.
42:49	That is, if I look at the non-key attributes in any
42:54	of these three relations, I will find that all of those non key attributes
43:00	are fully functionally dependent upon the entire primary key.
43:05	For example, looking at the PRODUCT relation, I can see that the primary key is Product_ID.
43:12	If I know a Product_ID, then I also know the Product_Description, the Product_Finish,
43:19	and the Unit_Price. Although we have removed the partial dependencies,
43:25	we still have a problem with our relational design. Namely, within our customer order table
43:33	we have something called a transitive dependency. In order for our relational design
43:39	to be entirely in third normal form, we are going to have to eliminate
43:44	this transitive dependency. In order for a relation to be in third normal form then,
43:51	it must meet all of the criteria of second normal form, plus it cannot have any transitive dependencies.
44:01	A transitive dependency is a functional dependency
44:06	on an attribute, which is not the primary key.
44:11	We call these dependencies transitive because the primary key will determine
44:19	one non-key attribute, and that non-key attribute, in turn,
44:25	will be a determinant for one or more additional non-key
44:30	attributes. As is always the case in the normalization process,
44:36	the solution is to take our relation, which contains a transitive dependency,
44:43	and break it apart into smaller relations. In this case, we want to remove the non-key determinant,
44:53	and the attributes that depend upon that non-key determinant,
44:59	and put them in a separate table. What we've done is we've taken our previous customer order
45:07	relation, and we've broken it apart into two separate relations, one of which
45:13	is named ORDER and the other of which is named CUSTOMER. Both of these relations are now in third normal form.
45:22	That is, they contain no partial dependencies. They contain no transitive dependencies.
45:30	Note that we have retained Customer_ID in the order table
45:37
as a foreign key link, which points back to the CUSTOMER table.
45:43
Thus from our original relation, we have created four separate relations in order
45:49
to achieve a relational model, which is entirely in third normal form.
45:56
To many people who are new to relational modeling this whole process of normalization
46:03
can seem quite confusing and quite challenging. I promise that with a little bit of practice
46:11
it will become second nature to you. The most important point to remember with respect
46:17	to designing relations that are in third normal form, is simply this-- each relation should contain attributes that
46:28	are related to one and only one business concept or business
46:33	theme. If you can remember that single rule, then you will be able to easily create relations
46:42	that are in third normal.
46:49	Well my friends, thus ends our lecture on the relational model.
46:56	Come back soon, and we will explore our next topic. Until then, have a great day.