0:00	Good day, everyone. This is Dr. Soper here. And today, we will be exploring the fifth topic
0:07	in our series of database lectures, this one being entitled, Database Design.
0:13	Although there are many objectives associated
0:19	with today's topic, broadly speaking, our goals are to further develop our skills such
0:25	that we are able to transition away from our conceptual data models to a point where we feel comfortable implementing
0:35	complex data structures in a real-world database. To this end, we will review the normalization process
0:44	in this lecture. And we will also explore the concept of denormalization
0:49	and learn when denormalization is an appropriate strategy in database design.
0:56	We will also learn some new information that will be useful in helping us implement 1 to 1, 1 to many,
1:05	and many to many binary relationships in a database. And toward the end of the lecture,
1:10	we will also gain insights into how to implement these 1:1, 1 to many, and many to many relationships, when those relationships are
1:19	recursive. What's more, we will further expand upon our knowledge of the structured query language
1:26
today by learning the strategies and approaches that
1:31
can be used in order to work with these recursive relationships using SQL.
1:37
To begin, I'd like to talk about the process of transitioning
1:44
from relations, to entities, and ultimately
1:49
into designs for cables that we can actually implement in a real-world database.
1:57
We begin with simple relations. And we learn the parenthetical method
2:02
of describing these relations, an example of which is shown on your screen.
2:08
So here, we have an item relation. And we can see that this relation contains five attributes.
2:15
The first attribute, itemId, is underlined, indicating that it is a primary key.
2:21
And then we have four non-key attributes. We've already learned how to take
2:26
this initial, simple conceptual model of a relation
2:32
and translate that into an entity consistent with the set of symbols that are used in entity relationship modeling.
2:40
Our item relation, then, can be easily transformed into an entity.
2:46
And we see an example of such an entity here. Although modeling the entity in this way can be very useful,
2:55
we still need to make additional decisions, and specify additional details, before we can actually
3:02
implement this entity as a table in a real-world database.
3:08
As an example of these missing details, we might consider that we need to specify data types for each
3:15
of the attributes in the table. This information will ultimately be required before we can implement our entities as tables
3:24
in the database. Transitioning from a data model into a database,
3:30
however, involves several more steps than just identifying the data types for our attributes.
3:39
So, perhaps it would be useful if we were to explore this process in a slightly more formal way.
3:47
To ensure that we are able to successfully implement our data model, the first step that we would like to complete
3:55
is to create a table for each of the entities appearing in the data model.
4:01
As we go through the process of implementing these tables, there are several key tasks that we
4:07
will need to complete in order to ensure that the resulting implementation of our database
4:14
is satisfactory. One of the most critical of these tasks is to specify a primary key.
4:22
We must always remember to specify a primary key for each of the tables in our database.
4:29
After adding our attributes or columns to the table, we need to specify several properties for each column
4:38
so that the database engine will be able to treat the column properly
4:43
once the database is deployed. Among these properties are, first, the data type,
4:50
is the attribute-- an integer, a float, a varchar, a date-time,
4:56
or some other data type. The null status of the attribute-- that
5:02
is, are null values allowed? Default values, if any, for the attribute.
5:10
That is to say, if a specific value for the attribute is not supplied, is there a value
5:17
that we would want the database to fill in automatically for us? And, of course, we may also impose certain constraints
5:26
on the column so as to preserve the integrity of the data contained therein.
5:33
Examples of such constraints might include a range constraint in which the values appearing
5:39
in a column must fall between a minimum and maximum value.
5:45
You may also specify a set of acceptable values, such
5:51
that each value appearing in the column must exist within the predefined set, and so forth.
5:59
Finally, after completing all of these design tasks, we may want to re-evaluate the structure
6:09
of the table in light of the normalization needs of the database.
6:15
If, for example, our performance needs require that we denormalize the table,
6:21
that action of denormalization, which we will discuss and describe later today,
6:26
might require us to alter the structure of the table somewhat.
6:33
Before we discuss denormalization, however, a brief review of normalization
6:39
and the normalization process may be useful. If you recall from an earlier lecture,
6:46
we said that data tables which are not properly normalized
6:52
may be susceptible to problems which we called modification
6:57
anomalies. And there are three different types of these anomalies.
7:03
First are insertion problems in which a problem arises when we attempt to add new data into a table.
7:13
Next, we have update anomalies in which a problem arises when we attempt to modify the data in a table.
7:24
And finally, we have deletion anomalies in which a problem arises when we attempt to delete existing
7:32
data from within a table. Tables which are properly normalized
7:38
will help us to avoid these three types of problems.
7:46
As a quick review, let's see some examples of these three different types of modification anomalies.
7:54
Here, we see a list of data in which we are recording information about customers and the cars
8:01
that they own. To begin, let's consider a deletion anomaly.
8:09
Let's say, for example, that Leonard is no longer my customer.
8:15
So I want to delete Leonard from the database. By deleting the row of data in the list for Leonard,
8:22
however, I have now lost all record that a type of car, known as a 2010 Nissan Sentra,
8:33
even exists. So by deleting Leonard, I have inadvertently deleted
8:39
other information from the list that I may have found useful,
8:44
or I may have needed to preserve. This is a deletion anomaly.
8:51
Next, let's consider an insertion anomaly. Say that we want to add a new customer to our table.
8:58
Perhaps this customer's name is Sheldon. But Sheldon doesn't drive.
9:04
So we add information about Sheldon into the table. But the result is that we now have many missing
9:12
values in our list. This is wasted space.
9:17
And we might consider this to be an insertion anomaly.
9:23
Later on, perhaps, our new customer, Sheldon, decides to learn to drive.
9:29
And let's say that he purchases a 2003 Volkswagen Golf.
9:36
Now, a 2003 Volkswagen Golf already exists in our list.
9:42
So as we are adding this information into the list for our customer, Sheldon,
9:48
assume that we make a spelling mistake. Perhaps we misspell Volkswagen.
9:55
Again, this is an insertion anomaly that would not have occurred in a properly-normalized database
10:05
design. Finally, we can consider an update anomaly.
10:11
Let's say, for example, that we want to change the car ID for a 2003 Volkswagen Golf
10:20
from 102 to 100.
10:27
If I make that change for my customer, Penny, I have now introduced an update anomaly into the list
10:35
because my customer, Sheldon, has the same type of vehicle.
10:41
And therefore, in order to maintain the accuracy of the data in the list,
10:46
I would need to update the car ID not only for Penny, but also
10:52
for Sheldon. And this is an example of an update anomaly because we've introduced a problem into our table of data
11:02
as the result of an update operation.
11:09
If you recall, the approach that we take to avoid these modification anomalies
11:14
is to break tables apart into smaller and smaller tables
11:21
until they are no longer susceptible to these anomalies.
11:27
This is a process which we can call normalization.
11:34
In our previous example, we had a large list of data that contained information
11:41
about two separate business concepts, or business themes.
11:47
That is, our list of data contained information about customers and cars in the same list.
11:56
By breaking that list apart into two separate tables, say, a customer table and a car table,
12:05
we now have a data design that allows us to add, update,
12:12
or delete data without introducing any anomalies
12:17
into the database. Next, let's briefly review some of the definitions associated
12:24
with the normalization process. First is a functional dependency.
12:31
Now, a functional dependency refers to a relationship among attributes
12:36
within a relation, such that the value of one attribute
12:42
can be used to find or identify the value of another attribute
12:48
or set of attributes. That is, if the value of one attribute can be determined by the value of another,
12:56
then that first attribute is said to be functionally dependent upon the second attribute.
13:04
Further, if an attribute can be used to find the values of other attributes within a table,
13:13
then that attribute is known as a determinant. That is, the value of the attribute
13:20
can be used to determine the values of other attributes
13:26
within the table. Finally, recall the concept of a candidate key.
13:31
And a candidate key is simply an attribute whose values
13:36
can be used to determine, or to find, the value of every other non-key attribute within a row.
13:45
And these candidate keys can come in a few different flavors, one being a simple candidate
13:51
key which consists of the value of just a single attribute.
13:57
And the other being a composite candidate key whose uniqueness comes by combining the values of two
14:07
or more attributes. Recall, also, that many different types of normal forms
14:14
have been defined. We began by exploring the concept of a relation.
14:20
And we said that every relation is, by definition, in first normal form.
14:27
That is, if a table meets the definition of a relation, then that table is also in First Normal Form.
14:36
If we take our table in first normal form, and we remove any partial dependencies,
14:43
then the table meets the definition of second normal form.
14:49
If we further remove any transitive dependencies, then our table will meet the definition
14:55
of Third Normal Form. If we reach a point in our normalization process
15:01
where every determinant within the table is also a candidate key, then the table
15:08
will meet the definition of something called Boyce-Codd Normal Form.
15:13
And as you can see, beyond Boyce-Codd Normal Form, there is Fourth Normal Form, Fifth Normal Form, DK Normal
15:22
Form, and Sixth Normal Form. So many additional normal forms have been identified.
15:30
For our purposes, a relation is considered normalized
15:35
when every determinant is a candidate key. And again, technically, this is known
15:43
as Boyce-Codd Normal Form. Boyce-Codd Normal Form is a slightly more stringent version
15:51
of Third Normal Form, and it is sometimes called 3.5 Normal Form for that reason.
16:00
A database design which has been normalized such
16:05
that it is in Third Normal Form or above is generally not susceptible to the types
16:12
of modification anomalies that we described earlier. And, at this level of normalization,
16:18
a database design is also almost always sufficient
16:24
to handle any business data problem. Next, let's consider a normalization example.
16:34
Assume, for this example, that we want to create a database table which
16:40
will allow us to keep track of information relating to our customers, and also information
16:47
relating to each of those customers' accountants. So we create a customer table that contains a customer ID.
16:56
That will be our primary key. And we also track the customer's name, address, city, state, zip
17:04
code, and the accountant ID for the customer, along with that accountant's name and telephone number.
17:12
In its current form, this customer table is not normalized because all of the attributes in the customer
17:21
table cannot be determined by the customer ID.
17:28
Specifically, consider that if we know a zip code, or a postal code, then, by definition,
17:37
we also know the city and state. That is to say, if I know a zip code,
17:43
I can determine the city and state to which that zip code applies.
17:51
Furthermore, in this table, we also see that, if we know the accountant ID,
17:57
we can use that information to determine the name of the accountant and the accountant's phone number.
18:04
To normalize this database design, then, we will need to break the table apart
18:10
into a series of smaller tables until we reach a point where every non-key attribute is fully
18:19
functionally determined by the primary key.
18:24
Our normalized solution, then, contains three separate tables,
18:30
a customer table, which contains the customer ID as a primary key, the name and address of the customer,
18:38
and the zip code and accountant IDs associated
18:43
with the customer as foreign key links back to their parent tables.
18:49
We then have a zip code table where zip is the primary key. And we have city and state as attributes of the zip code
18:58
table. And then we have an accountant table where accountant ID
19:03
serves as the primary key. And we have two non-key attributes, accountant name
19:08
and accountant phone. We have thus taken our original customer table
19:14
and broken it into three smaller tables which, when considered together, provide us
19:22
with a normalized data solution. Now that we've reviewed the normalization process,
19:30
next I would like to talk about a new topic. And that is the concept known as denormalization.
19:39
As the name suggests, denormalization is the opposite of the normalization process.
19:46
That is, in the denormalization process, we have identified a business need, or a business
19:54
justification, for why two or more tables within our database
20:00
design should be combined together into a single table.
20:07
Common reasons for wanting to denormalize a set of tables
20:13
include simplicity of design and speed of querying.
20:20
The trade-off, however, is that, by using a denormalized design,
20:27
we are introducing the possibility of modification anomalies into our database.
20:34
Consider, for example, the set of tables shown here.
20:42
In this example, we have combined the zip code table
20:48
and the customer table from our normalized solution into a single table, which here, we are just calling Customer.
20:58
Specifically, what we have done is we have taken the city and state attributes that
21:04
used to exist in the zip code table, and we have added them into the customer table, along with zip
21:13
code, such that the entire customer address can now
21:18
be read simply by examining the rows of the customer table.
21:23
From a performance perspective, this may be desirable because it allows
21:30
us to extract a customer's address from the table without having to perform a join operation with the zip code
21:39
table. In the relational database world, joint operations are computationally and temporally
21:48
expensive processes. It takes the database engine a substantial amount
21:54
of computational cycles and time in order to join two tables together.
22:01
And that extra time, that extra effort, slows down the query performance.
22:09
By denormalizing the design, and combining the zip code table and the customer tables together,
22:17
we can expect an increase in query performance if our objective is to extract customer addresses.
22:27
What's more, we have the additional benefit of simplicity.
22:32
Our database design now contains two tables instead of three,
22:38
which makes it conceptually easier to understand, perhaps easier to maintain, certainly
22:45
easier from the perspective of writing SQL commands.
22:51
There is, however, a cost to this denormalization process.
22:56
And the cost is our denormalized design
23:02
is now open to the possibility of modification anomalies.
23:08
In this case, because we are storing the zip code and the city and the state for each customer
23:16
in the customer table, this means, one, we will have a great deal of repetitive data in the customer
23:25
table. For example, if we have 100,000 customers who
23:31
live in Los Angeles, California, that extra text, Los Angeles,
23:37
California, will appear redundantly in many, many, many rows within our customer table.
23:45
What's more, we now have a much greater probability of data entry problems existing in our customer table.
23:54
One customer, for example, might enter their city and state as Los Angeles, California, where they
24:02
spell the state California. Another customer might use the two-character abbreviation, CA,
24:10
for California. Whereas another customer might provide the city and state
24:16
entirely as upper case, while another customer
24:22
might provide the same information entirely as lower case. These types of anomalies in the data
24:29
would not be present if we had retained our original, normalized design.
24:36
But because we made the decision to denormalize those tables, we are now exposed to the possibility
24:44
that these anomalous data values might exist.
24:49
In summary, then, you should denormalize your data design only when there is a defensible business reason for doing so.
24:59
And remember that, by denormalizing your database design, your database will now be exposed
25:05
to the possibility of having problems that it might not otherwise have.
25:11
There are some preventative measures that can be taken to help avoid those problems,
25:20
such as using database constraints, or implementing validity checks in software.
25:28
However, each of these strategies represents additional overhead tasks.
25:34
And in the end, there may not truly be any gains in simplicity from using such an approach.
25:42
A major component of relational database design, of course,
25:49
is creating relationships between tables. And to that end, I'd like to provide
25:55
some additional useful information about how to implement one to one, one to many,
26:02
and many to many relationships in a real-world database.
26:07
First, let's consider a one to one relationship. Now, the question here is, in order
26:14
to establish a relationship between two tables,
26:19
we need to use a link between a primary key and a foreign key.
26:25
But in a one to one relationship, which table should contain the foreign key?
26:31
Here are a few guidelines that you can use in order to decide what to do.
26:37
First, if both sides of the relationship are optional-- that is, if the minimum cardinality
26:44
on both sides of the relationship is 0, then it doesn't really matter which table
26:50
receives the foreign in a one-to-one binary relationship. You can place the foreign key in whichever
26:57
table makes most sense to you, conceptually, as a human being.
27:03
If, however, only one side of the relationship is optional, that is to say, if one side of the relationship
27:11
has a minimum cardinality of zero, while the other side of the relationship has a minimum cardinality of one,
27:19
then the best strategy is usually to put the foreign key in the table that
27:25
resides on the optional side of the relationship.
27:31
Let's look at some examples. First, consider the relationship between an employee
27:36
and a locker. This is a one-to-one relationship such that an employee might be assigned one locker,
27:45
and each locker might be assigned to a single employee. In this case, the minimum cardinality
27:53
in this one-to-one relationship is zero on both sides-- that is, the relationship is optional
28:00
in both directions-- and therefore, we can place the foreign key in whichever table we prefer.
28:08
In the first example, we've placed the foreign key in the locker table such that a locker has an employee,
28:17
whereas in the second example, we've placed the foreign key in the employee table
28:24
such that an employee has a locker. So whichever of those two notions
28:30
conceptually makes most sense to you as the data designer should guide your decision as to where the foreign key should
28:38
be placed. If it makes more sense to think about a locker having
28:44
an employee, then place the foreign key in the locker table. If it makes more sense to you to think about an employee having
28:52
a locker, then place the foreign key in the employee table.
28:58
If, however, we have a one-to-one binary relationship, where the minimum cardinality on one side of the relationship
29:07
is zero, while the minimum cardinality on the other side of the relationship is one, then the foreign key
29:14
should always go at the optional side of the relationship. Let's consider this example where we have a hospital
29:23
patient and a hospital bed. According to this data design, a hospital patient
29:31
is always assigned to exactly one bed in the hospital.
29:37
But each bed in the hospital might not be assigned to a patient.
29:43
If a bed is assigned to a patient, then, of course, it is assigned to a maximum of one patient.
29:50
But the possibility exists that we have empty beds that are not assigned to any patients.
29:58
In this case, we want the foreign key to go on the optional side of the relationship.
30:04
That is we would put the bed ID as a foreign key in the Patient
30:10
table. And we would say a patient is assigned a bed.
30:16
SQL queries for one-to-one binary relationships are quite straightforward and depend only upon our decision
30:25
as to the placement of the foreign key. In this first example, we placed the locker ID as a foreign key
30:35
in the employee table. And therefore, we need to do the join on equality between locker IDs.
30:45
Whereas, in the second example, we made the decision to place employee ID as a foreign key
30:51
in the locker table. And we therefore need to do the join by looking for matching values of employee IDs between the two
31:00
tables. Next, let's consider a one to many relationship.
31:08
Just as with a one-to-one binary relationship, in a one to many binary relationship,
31:15
we establish the relationship between the two tables by creating a link between a primary and foreign key.
31:24
In a one to many relationship, however, it is very easy to identify the table that
31:30
should receive the foreign key. All you need to remember is that the foreign key
31:36
belongs at the many side of the relationship. And in these types of one to many binary relationships,
31:44
we typically refer to the one side of the relationship as the parent table,
31:51
while we refer to the many side of the relationship is the child table.
31:58
Here, for example, we see a one to many binary relationship between a team table and a player table such
32:08
that a team has 0 to many players, while a player belongs to one and only one team.
32:18
In this case, the player table resides at the many end of the relationship.
32:25
And we therefore know that the foreign key belongs in that table.
32:30
Team ID, which is currently serving as the primary key of the team table,
32:36
is thus placed into the player table as a foreign key.
32:43
Just as with one-to-one binary relationships, writing SQL queries to join two tables, which
32:51
are involved in a one to many binary relationship, is quite simple.
32:56
In this example, we are performing the join in the WHERE clause. But we could just as easily perform
33:03
the join in the FROM clause by using the JOIN keyword.
33:09
Finally, we can consider many to many binary relationships.
33:16
And although, conceptually, we as human beings are easily able to understand the notion of a many
33:22
to many relationship, in order to actually implement such relationships in a real-world database,
33:29
we need to create an intersection table that will sit between our two parent tables.
33:37
The typical strategy for creating these intersection tables is to use a composite primary key in the intersection
33:47
table, which is comprised of the primary keys from each of the parent tables.
33:54
It is, of course, also possible to use a surrogate key as the primary key in an intersection table.
34:03
However, doing so has some implications. We will take a look at those implications shortly.
34:12
First, however, consider this conceptual example of a relationship between a student and a class.
34:21
So, conceptually speaking, a student can take many classes.
34:26
And a class can contain many students. This is a many to many relationship.
34:32
We cannot, unfortunately, implement this type of relationship directly in a real-world database.
34:41
Instead, we need to create an intersection table that
34:46
will serve as a look-up table between the student and class
34:52
tables. In this case, we've created an intersection table named Student_Class.
34:58
And we can see that it has a composite primary key composed
35:04
of the primary keys of the two tables to which it is attached.
35:11
Note here that each component of the composite primary key
35:17
is individually serving as a foreign key link back to its parent table.
35:24
Thus, the attributes involved in the composite primary key are simultaneously serving two purposes.
35:34
First, they are a part of the primary key for our intersection table.
35:40
And second, when considered individually, they are serving as foreign key links
35:47
back to their parent tables. From the perspective of the structured query language,
35:54
to write a query, which would assemble a list of students and all of the classes that each student has taken,
36:02
we would thus need to join three tables together. We would need to join our student table and class
36:09
table together. But in order to do that, we need to use the student-class table
36:15
as a part of the joint operation. And in this case, in order to accomplish this task,
36:21
we need to match student IDs in the student table to student IDs in the student class table,
36:28
while also matching the class number in the student class
36:34
table to the class number in the class table. In this way, we've linked all three tables together in order
36:42
to produce our query results. Next, let's consider a type of intersection table known
36:52
as an associative table, or an associative entity. An associated entity is simply an intersection table
37:00
which contains additional non-key attributes. And we call this an association relationship.
37:11
Revisiting our student class example, we see here that we've added a new attribute to the student
37:19
class table, named Grade, which provides us with additional information about the relationship
37:26
between the student and the class. That is, for each intersection between a specific student
37:32
and a specific class, we want the ability to record a grade which indicates the student's
37:39
performance in that class. This sort of a design is called an association relationship.
37:45
Next, as promised, I would like to talk briefly
37:51
about the implications of using a surrogate key in conjunction
37:56
with an associative entity. When an associative entity uses a composite primary key that
38:05
is made up of the primary keys of the parent tables to which it is attached, then each possible pair
38:15
of matched values that appear within the associative entity
38:21
can appear a maximum of one time.
38:26
By contrast, if we decide to use a surrogate key, then, in that case, the primary keys of the parent tables
38:37
are serving just as foreign keys in the associative entity.
38:44
And the implication, then, is that each possible matched
38:50
pair of values between the two parent tables can appear many times in the associative entity
38:59
because those foreign keys are no longer considered part of the primary key.
39:06
In this first example, we have a situation where we would like to allow our customers to provide ratings
39:14
for the products that we sell. This is a many to many relationship such that,
39:19
conceptually speaking, a customer should be able to provide ratings for many products.
39:25
And each product should be able to be rated by many different customers.
39:32
In our first example, we see that we are using a composite primary key
39:39
as the primary key for our associative entity rating.
39:45
Because of this, each possible combination of customer ID and product ID can occur only one time
39:55
in the rating table. What this means from a practical perspective, in this example,
40:01
is that each customer would be allowed to rate each product only one time,
40:08
according to this data design. By contrast, if we consider our second example,
40:15
we see that we are using a surrogate key as the primary key in the rating table, named Rating ID.
40:24
And our customer ID and product ID attributes
40:29
are simply serving as foreign key links back to their respective parent tables.
40:35
That is, they are no longer part of the primary key in this design. They are just simple foreign keys.
40:43
In the second design, because we are using a surrogate key, the combination of values between customer and product
40:52
ID, each unique combination could potentially occur in the rating table many times.
40:59
So customer number 1 might rate product number 7 many different times, the practical implication
41:07
being that, in this sort of a design, each customer would be allowed to rate each product
41:13
potentially many times. So there are important implications
41:19
to be considered when making the decision as to whether a surrogate key should be used in place
41:27
of a composite primary key in one of these associative entities.
41:32
Our final topic for this lecture is
41:37
to consider how to properly implement recursive relationships in our database designs.
41:46
If you recall, we said that a recursive relationship is a relationship in which a table is connected to
41:56
or related to itself. To actually implement these recursive relationships
42:01
in a real-world database, however, we need to consider a few points.
42:07
First, with respect to one-to-one recursive relationships, and one to many recursive relationships,
42:15
the process of implementing the recursive relationship is quite straightforward.
42:21
We simply need to use a foreign key link within the table. And that will allow us to create the recursive relationship.
42:31
If, however, we have a many to many recursive relationship, we will need to create an intersection table in order
42:38
to actually implement the recursive many to many relationship in a real-world database.
42:44
So on the coming slides, we will consider these three
42:52
conceptual recursive relationships. In the first example, we have a sponsorship relationship
42:59
which is a one to one recursive relationship. In this case, each person in the table
43:07
is sponsored by 0 to 1 other people. That is, they can be sponsored by, at most, one other person.
43:16
In our second example, we have a one to many recursive relationship. And this is a referral relationship.
43:24
That is, each customer might refer many other customers
43:29
to us, but each customer is referred to by a maximum of one other customer.
43:37
So this is a one to many recursive relationship. And finally, we have a many to many recursive
43:44
relationship in which a doctor might treat many other doctors,
43:53
while each doctor might be treated by many other doctors
43:58
as well. This is conceptually a many to many recursive relationship.
44:05
Let's see how we actually implement these recursive relationships in a real-world database.
44:12
To begin, consider our one-to-one recursive relationship. To implement this type of relationship,
44:20
we just add a foreign key into the same table, in this case, a person table, which
44:27
will track the sponsorship relationship between people
44:32
in the table. Just as when we were discussing where
44:38
to place the foreign key in a one to one binary relationship, when we have a one-to-one recursive
44:46
relationship, we also have a choice. In this case, depending upon the approach
44:53
which makes the most conceptual sense to us as a human being,
44:58
we can implement the sponsorship relationship, either by creating a foreign key which records the name of the person
45:08
that each person in the database has sponsored.
45:14
But we can also record exactly the same information by storing instead the person who was
45:22
the sponsor for each person. Regardless of which strategy we take
45:29
to implement the one to one recursive relationship, we will see that the approach which
45:36
is used to run SQL queries related to the recursive relationship is quite interesting.
45:45
Broadly speaking, the strategy involves joining the table to itself in order
45:52
to extract the information that we want. So we see in these examples here,
45:58
we are referencing the same table twice each time,
46:05
giving the table a different alias. And this approach allows us to join the table to itself.
46:15
Next, let's consider a one to many recursive relationship. In this case, again, the strategy
46:21
is to place a foreign key inside the same table that
46:28
will track the one to many recursive relationship.
46:33
In our example, we have a referral relationship. And as an example, we would like to use this one
46:41
to many recursive relationship to generate a list of customers who have been referred
46:49
by a specific chosen customer. So we might, for example, want to know
46:55
which customers were referred by customer number 100.
47:01
And this example, we would see that customer number 200 and customer number 400 were referred to
47:09
by customer number 100. So referred by, in this example, is a foreign key link
47:17
which enables the one to many recursive relationship within the table.
47:24
Notice that the strategy for writing SQL queries for this type of recursive relationship
47:32
is the same as the strategy that we used for a one-to-one recursive relationship.
47:38
That is, we simply query the same table twice, giving each instance of the table
47:46
a different name-- here, A and B-- and this allows
47:52
us to join the table to itself. Finally, we have the problem of how
47:59
to implement a many to many recursive relationship. In our example, we said that we want
48:07
each doctor to be able to treat or provide care for many other doctors, while each doctor can also
48:16
be treated by, or receive care from, many other doctors.
48:21
So conceptually, this is a many to many recursive relationship on our doctor table.
48:29
In order to actually implement these many to many recursive relationships, we will need
48:35
to use an intersection table which will function as a look-up table that allows us to determine,
48:43
in this case, which doctors are being treated by which other doctors.
48:51
The structured query language statements that will allow us to join these two tables
48:57
together look a bit odd. But hopefully, they make logical sense to you, just as
49:05
with our one-to-one and one to many recursive relationships, we must first select data twice from the same table.
49:16
And we do this by providing each instance of the table a unique name, in this case, A and B.
49:25
And then we also must select data from our intersection table. With all of our tables defined, we then
49:33
simply perform the joint operation, as we ordinarily would. And we can, using this approach, generate
49:40
a list of doctors and patients who, in this case,
49:47
conceptually are also doctors.
49:53
Well, my friends, thus ends our further exploration of database design.
50:01
I hope that you learned something interesting today. And until next time, have a great day.
50:09