0:00	Good day, everyone. This is Dr. Soper here. And today we will be discussing the fourth topic in our series
0:10	of lectures on databases. And the topic today will be on data modeling and the entity
0:18	relationship model, or ER model for short.
0:23	We have many different objectives for our lecture today. Generally speaking, our goals are
0:30	to understand the reasons why data models are valuable,
0:37	and to gain both understanding and expertise in reading
0:44	and creating entity relationship, or ER models.
0:49	Along the way we will explore the various elements of entity relationship models.
0:56	And we will further discuss different types of relationships that can exist between entities,
1:03	including unary or recursive relationships, binary relationships, ternary relationships.
1:11	We will also learn about a concept known as a cardinality, which describes
1:18	the number of instances of one entity that are allowed to participate in a relationship
1:25	with another entity. And toward the end of our discussion, we will explore the differences between strong and weak
1:35	entities, and we will look at the different types of weak entity relationships, including both identifying
1:43	and non-identifying relationships. We will then conclude this lecture
1:50	with a discussion of supertype and subtype relationships,
1:55	and an examination of recursive relationships within the entity relationship model.
2:02	To begin, let's take a broad view and consider the three different stages of database development.
2:09	The first stage, prior to actually developing a database itself, is to perform what
2:15	we call requirements analysis. This is the process of gathering information
2:23	so that we can understand the data problem for which we
2:29	are attempting to design a solution. Once we have completed requirements gathering,
2:36	we can then move into the design phase. And the design phase is where we will create data models which
2:47	are graphical representations of the database solution
2:53	which we ultimately hope to implement. Finally, the third stage of database development
2:59	is actually implementing our database designs as a real-world functioning physical database that
3:08	can actually be used by an organization to solve one or more data problems.
3:14	Broadly speaking, the requirements analysis stage
3:20	is about gaining an understanding of the data problem for which we are trying to design a solution.
3:28	And for this purpose we have many different possible sources
3:33	of information. It can include interviews with users, existing forms, reports,
3:42	or queries. So these might be forms, reports, or queries that
3:48	are used with a current system. If the organization is going through a broader systems
3:55	design process, then use cases from within the unified modeling language may be available to us.
4:04	We may also consider business rules. And observation, where we simply observe the way
4:13	that various employees do their jobs in an effort to understand their data needs.
4:20	And of course, we may use JAD sessions. These are joint application development sessions
4:27	which are characterized by assembling all of the stakeholders in a project
4:34	together in a single room. So you may have systems designers, database designers,
4:41	users, executives, potential customers, et cetera.
4:47	And everyone in the room simply has a discussion about what they want the proposed new system to do,
4:55	and how they want that system to function. The sort of information gathered from these joint application
5:02	development sessions can then be very useful for developing a system in general, and specifically in our case,
5:11
for developing the database to support that system. After we have completed requirements analysis, what
5:20
we learn from that requirements analysis process then becomes our data model.
5:27
So we take everything that we learned through the requirements analysis stage and we process and distill all of that information
5:37
into a data model, where a data model is a graphical representation of the database system
5:46
that we ultimately hope to implement. The most popular type of data model
5:53
that is used in the database world today it's known as an entity relationship, or E-R data
6:00
model. These E-R models consist of three major graphical components.
6:08
They are entities, which we have spoken about previously as tables or relations, attributes,
6:17
for which several different types might exist, including identifiers or keys, and non-key attributes.
6:26
And our third and final component our relationships. These are relationships between entities.
6:35
Before we proceed, I need to speak briefly about the distinction between an entity class and an entity
6:44
instance. An entity class can be thought of as a structural description
6:53
for the individual occurrences of an entity. I know that description contains a lot of technical jargon,
7:01
so perhaps a better way of understanding this concept is to link it to other concepts which serve a similar purpose.
7:10
Examples might be a recipe, or blueprints
7:15
that are used by an architect.
7:20
In the first case, let's consider a recipe for baking a cake. If this is a generic recipe, we might
7:29
have some basic instructions-- mixing flour together with sugar and eggs and baking powder and put that in an oven.
7:41
We cook or bake our cake, and then we
7:47
add whatever sort of frosting we like. So the recipe gives us a basics framework or guideline
7:54
on how to produce a cake. But ultimately, the cake that is created is a unique individual item.
8:04
In our analogy, the recipe is equivalent to an entity class.
8:11
Well, the resulting specific individual real-world cake
8:16
is equivalent to an instance of the entity. Similarly, we might consider architectural blueprints
8:24
where we may have a generic set of blueprints that tell us how to design a basic house.
8:31
But then the homeowner who is building the house may be able to make some specific decisions,
8:38
such as what type of appliances they want in the kitchen, or what color they want the paint to be,
8:45
or what type of carpeting they want. All of those individual characteristics
8:51
make that resulting real-world world house a unique, specific item.
8:59
In our analogy, the architectural blueprints are similar to an entity class, while
9:05
the resulting individual real-world houses that are created as a result of following those blueprints
9:13
would be instances of the entity class.
9:18
So just remember that an entity instance is an occurrence
9:24
of an entity class. If we have an employee entity-- that is,
9:30
an employee entity class-- we may define attributes that we want to track for all employees,
9:39
such as an employee ID number, an employee name, an employee
9:45
department. When we add a new row of data to that table,
9:52
and we specify specific values for those attributes--
9:57
perhaps employee ID number one, named Dan who works in the marketing department--
10:07
we are describing a real-world entity. That new row of data is an instant of an employee.
10:14
And as far as the database is concerned, that row of data is an employee.
10:21
Here we see some additional examples. Our entity class, in this case, is an item.
10:28
And we can see that the item entity contains five attributes-- item number, description, cost, list price,
10:37
and quantity on hand. And then we see two instances of that entity.
10:43
So the instances of the entity represent an instantiation
10:49
of the entity class. This idea, if you've had some object-oriented programming,
10:56
is directly analogous to the concepts of a class
11:03
and an object in the object-oriented programming paradigm.
11:08
Next I would like to talk about attributes. An attribute is specific piece of information
11:17
that we would like to keep track of for each instance of an entity.
11:23
If we're talking about an employee, we may have attributes that we want
11:28
to track such as the employee's name, or their age, or the date
11:35
when they were hired, and so forth. If we're talking about a project entity,
11:41
we may have attributes that we want to track, such as a project name or the start
11:47
date of the project, or the type of project, or a description of the project.
11:54
The point here is that, as we fill in values for these individual attributes, what we are really
12:02
doing is defining a specific instance of the entity class.
12:08
So we are defining an employee, or we are redefining real-world project by providing values
12:15
for these attributes. Each attribute that is part of an entity
12:20
has a data type as well as several other properties. So we may define an employee ID number, for example,
12:28
as an integer. We may define an employee's name as a [INAUDIBLE],
12:35
with a maximum length of 50 characters. We can also specify additional properties for each attribute,
12:46
such as whether not the attribute is allowed to store null values, or perhaps we
12:53
want to provide a textual description of the attribute. So in addition to a data type, an attribute
13:01
can have other properties as well. Broadly speaking, we can consider two different types
13:08
of attributes. There are first, identifiers, which we will generally
13:14
referred to as keys. And then we have non-key attributes.
13:20
In the first case, identifiers are attributes whose values are used to identify an instance
13:30
of the entity class. Common examples of identifiers, or keys, might be a social security number, a student ID,
13:40
an employee ID, an email address, or even a department
13:45
ID. One of the important things to remember about keys
13:51
is that they can either be unique or non-unique.
13:58
If a key is unique, that means that each instance
14:04
of the entity-- or if you prefer to think about it as each row in the table--
14:12
will have a unique value for that attribute.
14:19
For example, our employees may have an employee number. And we would almost certainly want each employee
14:28
to have a unique employee number so that we can keep track of our employees individually
14:35
by using that number. By contrast, we can also have non-unique keys.
14:42
And non-unique keys are used in order to group instances of an entity class into categories.
14:53
An example might be a department ID.
14:59
More than one employee can have the same department ID.
15:04
And what that tells is that the employees which share the same department ID all belong to, or work
15:13
in, or are affiliated with, the same department. In that case, he would be non-unique,
15:21
because more than one instance of the employee class can have the same department ID.
15:28
Recall also that we have composite keys, and that a composite key gains its uniqueness
15:37
by combining the values of two or more attributes together.
15:44
The example that we used previously in this series of lectures was the flight number
15:52
and the date of a flight. So we may have, for example, United Airlines
15:58
flight 33 that takes place on the 17th of March, 2016.
16:07
There may be several other flights that take place on the 17th of March. And we may operate flight 33 every day.
16:17
But if we combine those values together, we can then use flight 33 on the 17th of March
16:25
to identify a specific real-world flight, with a real airplane, real passengers, and so forth.
16:33
The combination of those two values will be unique. Before we continue our exploration
16:41
of entity relationship models, I think at this point it would be a good idea
16:47
to provide examples of different ways in which entities might be represented in an entity
16:56
relationship diagram. The simplest way of representing an entity is just to use a rectangle which contains
17:04
the name of the entity. This is usually used for conceptual data modeling.
17:13
I may, for example, have an item entity. All I need to know is that item entity is related
17:21
to other entities in some way. I don't yet care about the various attributes that will
17:27
become part of the item entity. Another way of graphically depicting
17:34
these entities is to show the entity and just include its key attributes--
17:41
attributes which are serving as identifiers or keys for the entity.
17:46
We might, for example, have an item entity, and we may have defined an item ID as its primary key.
17:55
And we will list just that single key attribute, so that when we create relationships between the item
18:04
entity and other entities, we can see which attributes are involved in the relationship.
18:11
And that may be all of the information we need during a preliminary stage of data modeling.
18:18
Finally, we have a way of representing an entity in its entirety, which includes,
18:27
not only the name of the entity and its key attributes, but also all of the other non-key attributes
18:35
that together comprise the entity as well. Next, I would like to talk about relationships between entities.
18:43
If you recall from some of our earlier lectures,
18:50
we talked about how relationships naturally emerge among the various business
18:59
concepts for which we might want to track or record information.
19:05
A department, for example, can have many different employees.
19:10
Or a customer can place many different orders.
19:15
Or a product can have many different suppliers. These are all examples of relationships
19:22
among entities-- relationships among business concepts.
19:28
From the perspective of entity relationship modeling then, we can consider several different types
19:34
of relationships. And these types of relationships can be defined by what we call the degree of the relationship,
19:44
where the degree of the relationship refers to the number of entities that are participating
19:52
in the relationship. Common examples include a unary relationship.
19:59
This would be a degree one relationship, in which an entity is related to itself.
20:07
We would also call that a recursive relationship. We may also have a binary relationship, or a degree two
20:16
relationship, where one entity is related to another entity.
20:22
Binary relationships are by far the most common types of relationships in entity relationship modeling.
20:33
We may also have a ternary relationship, or a degree three relationship.
20:38
In a ternary relationship we would have three entities involved in the relationship.
20:45
So the degree then simply refers to the number of entities which participate in the relationship.
20:52
Let's see some examples. Here we see a conceptual drawing of a unary relationship.
21:00
Remember that a unary relationship is an entity that is related to itself.
21:07
In this case, we have a person entity. And the person entity has a relationship to itself
21:14
that we are calling marriage. What we can infer from this diagram then,
21:20
is that a person can be married to another person. The possibility exists, for instances of the person entity
21:31
to have this marriage relationship with each other. So a person can be married to another person.
21:38
The person entity is related to itself in a unary relationship.
21:44
Here we see an example of a binary relationship. In this case, we have two entities-- employee and parking
21:51
space. And these two entities are related to each other. So an employee may park in a parking space,
22:00
or a parking space may be assigned to an employee. This is a binary relationship, or a degree two relationship,
22:08
and these types of relationships are the most common in entity relationship modeling.
22:14
Here we see a conceptual ternary relationship. Recall that in a ternary relationship,
22:19
three different entities participate. In this case, we have a doctor entity, a patient entity,
22:28
and a drug entity. The intersection of these three entities
22:35
then is a prescription. So a doctor writes a prescription
22:44
which involves a patient and a specific drug. These three entities together conceptually
22:52
are involved in a ternary relationship.
22:57
Next, let's expand the set of tools that we have available for describing the relationships
23:04
between entities. We will begin by learning two simple symbols
23:10
for describing conceptual relationships between entities. The first of these symbols is a simple line,
23:17
and it is used to represent conceptual one
23:22
relationship between entities. The second of these symbols is known as a crow's foot,
23:30
because it is supposed to look like the foot of a bird.
23:37
And we use this symbol to represent the conceptual notion of many.
23:44
Let's see how these conceptual crow's foot relationship symbols might be implemented to describe relationships
23:55
between entities with a little more detail. To begin, we can consider a conceptual one-to-one binary
24:04
relationship. In a one-to-one relationship, an instance of one entity
24:11
is related to one instance of another entity. Here we see an example of a one-to-one relationship
24:19
between an employee entity and a locker entity.
24:25
So we might imagine that an employee is assigned to exactly one locker.
24:33
And a locker can be assigned to just one employee.
24:39
This is a one-to-one relationship. Next we can consider a one-to-many relationship.
24:45
I should note that one-to-many relationships are by far
24:52
the most common types of binary relationships in entity
24:57
relationship modeling. In a one-to-many relationship, a single instance of one entity
25:06
is conceptually related to many instances of another entity.
25:12
A simple example of this type of relationship would be the one-to-many relationship
25:17
which exists between the department and an employee entity. So an employee works in one department,
25:25
but a department can have many employees. This is a one-to-many relationship between entities.
25:32
Finally, we can have a conceptual many-to-many
25:38
relationship. In a many-to-many relationship, many instances of one entity
25:45
can be related to many instances of another entity. In this example we see a conceptual many-to-many
25:53
relationship between an item entity and a supplier entity.
25:58
What we can infer from this diagram is that a supplier can supply many items,
26:06
while a given item might be supplied by many different suppliers.
26:13
And extremely important point to remember is that although we
26:19
can think about these many-to-many relationships conceptually in this way, in a real-world implementation
26:29
of a database, we cannot directly implement a many-to-many relationship between two entities.
26:38
We must always have a third entity, or a third table which
26:44
will sit between them, which acts as a look-up table,
26:49
and provides us with the ability to actually link
26:54
many instances of one answer entity with many instances of another.
27:00
In this example, to actually implement the many-to-many relationship between our item and supplier
27:08
entities, we would need to create a third entity to sit between them, which we might label,
27:15
item supplier, which we could use to actually link
27:21
items and suppliers together. Now that we are able to better describe
27:28
the relationships between our entities by using the concepts of one and many,
27:35
we can talk about the notion of cardinalities, which allow us to be even more precise in describing
27:43
and defining the relationships between entities.
27:48
In entity relationship modeling, there are two different types of cardinalities.
27:54
The first is a maximum cardinality, and the second is a minimum cardinality.
27:59
The maximum and minimum cardinalities respectively
28:07
allow us to specify the maximum number of instances
28:12
of one entity that are allowed to participate in a relationship, and the minimum number of instances
28:21
of an entity that must participate in relationship. Maximum cardinalities are typically
28:29
defined as one, many, or some other specific positive fixed
28:37
number. By contrast, minimum cardinalities are typically
28:45
specified as zero or one, where zero would indicate
28:51
an optional relationship, and one as a minimum cardinality
28:56
would indicate a mandatory relationship. Remember that a minimum cardinality
29:03
refers to the minimum number of entity instances which must participate in the relationship.
29:13
The relationship might be optional, or it might be mandatory-- zero or one.
29:21
Here we see how we can specify minimum and maximum cardinalities using our crow's foot symbols.
29:29
To begin, consider a hash mark across the relationship line.
29:37
You will notice that this hash mark looks similar to the number one.
29:44
And that's an easy way of remembering what the hash mark represents.
29:49
It represents a cardinality of one, and it can be used to represent either a minimum or maximum
29:57
cardinality. In our first example, we have what we would call
30:03
a one-and-only-one relationship. The minimum cardinality and the maximum cardinality
30:10
are both specified by this hash mark across the relationship line.
30:16
Remember that the symbol which appears closest to an entity
30:25
is always the maximum cardinality, while the adjacent symbol is the minimum cardinality.
30:32
Now that we know the meaning of this hash mark
30:38
across the relationship line we can understand the notion of a one-to-many relationship.
30:46
In a one-to-many relationship we are saying many instances of one entity
30:52
may participate in a relationship with a single instance of another entity.
30:59
But at a bare minimum, at least one entity instance
31:05
must be involved in the relationship. Again, remember that the symbol which
31:11
appears closest to the entity is the maximum cardinality,
31:17
while the adjacent symbol is the minimum cardinality.
31:23
Next, let's consider the circle symbol. You will notice that this symbol closely
31:30
resembles the number zero. And you can use that as a way of remembering its meaning.
31:37
The circle symbol represents a minimum cardinality of zero.
31:46
Note that the maximum cardinality can never be zero.
31:53
If the maximum cardinality were ever zero, then a relationship could not actually
31:58
exist between two entities. You would be saying that no instances of one entity
32:06
are ever allowed to participate in the relationship. So the only time we will see this zero or circle symbol
32:15
is when it is being used to represent a minimum cardinality.
32:21
Now that we're familiar with our three different types of cardinality symbols-- that is,
32:28
the hash mark across the relationship line, the crow's foot symbol, and the circle,
32:36
we have everything that we need to describe the four different types of specific relationships
32:44
which might exist among entities in an entity relationship model.
32:50
They are one-and-only-one, or if you prefer exactly one,
32:57
a one-to-many, a zero-to-one relationship, in which the minimum cardinality is zero and the maximum
33:06
cardinality is one, or we may have a zero-to-many relationship in which the minimum cardinality is zero
33:15
and the maximum cardinality is many. Let's see some examples of these fully specified cardinalities
33:25
being used to describe relationships between entities.
33:30
Here we see a binary relationship between a customer entity and an order entity.
33:39
Note that relationships can be read in two different ways.
33:46
In this example, as we follow the relationship line from customer to order, we would read the relationship
33:54
as follows. A customer can place one-to-many orders.
34:03
That is one way in which the relationship can be read. But note that we can also read the relationship moving
34:10
in the opposite direction, and the direction from order to customer.
34:16
When we read the relationship in that way, we would say an order can be placed
34:22
by one and only one customer.
34:27
The diagrams that we have been seeing on the previous slides are what are known entity relationship diagrams.
34:36
They are graphical ways of describing the relationships
34:42
among the various entities that will eventually become a part of the database.
34:49
In these entities relationship diagrams, our entities, or our tables if you will,
34:57
are represented by rectangles. Inside of those rectangles we will
35:03
see the names of the entities, and potentially the attributes that together define the entity.
35:14
Entity relationship diagrams also depict relationships. And these are shown by using lines
35:21
that interconnect our entities. And as we saw on the previous slide,
35:30
the specific nature of those relationships is depicted using our cardinality symbols.
35:38
Specifically we use crow's foot symbols in order to provide specific details
35:46
about the nature of the relationship between two entities.
35:52
All of the examples that we've seen today can be thought of as what we would call HAS-A relationships.
35:59
And this term is used because an entity has a relationships
36:08
with some other entity. For example, an employee has a locker.
36:15
A department has an employee. There are other types of relationships between entities
36:22
which are called IS-A relationships, and we will discuss these a little later.
36:29
First, however, let's talk about strong and weak entities.
36:36
To begin, a weak entity is an entity whose instances cannot
36:42
exist in the database unless they are related to an instance
36:51
in another entity. By contrast, a strong entity is an entity whose instances
37:00
can exist independently. They do not rely on the existence
37:06
of instances of any other entity in order to be able to exist.
37:12
They can be added to the database independently.
37:17
Consider for example, the relationship between a customer
37:23
entity and an order entity. New customers can be added to the database
37:32
without any additional information being required to exist in any other entities.
37:41
However, we cannot add an order to the database unless that
37:48
order is associated an existing customer.
37:53
In this case, in this example, a customer would be a strong entity, whereas an order
38:01
would be a weak entity. Beyond the distinction between strong and weak entities,
38:11
we also need to note that there are two different types of weak entities.
38:18
These are ID-dependent weak entities and non-ID dependent weak entities.
38:28
To begin, an ID-dependent weak entity is a weak entity whose identifier--
38:37
whose uniqueness is partially linked to its parent entity.
38:45
Another way of saying that is, the ID-dependent weak entity
38:50
will have, as part of its primary key, the primary key
38:57
of its parent entity. In this way, the database will enforce the constraint that
39:06
instances of the ID-dependent weak entity cannot exist this
39:11
without being associated with an instance of its parent entity.
39:18
Here we see some examples of these ID-dependent weak entity
39:24
relationships. In the first case, we may have a parent entity, which
39:29
we will call building, and a child entity which we will call apartment.
39:36
In this model, an apartment cannot exist without belonging to a building.
39:41
And this constraint is enforced by making the building name a component of the primary key for apartment.
39:51
If the relationship between a strong entity and a weak entity
39:57
is ID-dependent, then we call that relationship
40:02
an identifying relationship. And this can be seen graphically in entity relationship models
40:11
by the use of a solid relationship line connecting the strong entity to the weak entity.
40:21
So just to reiterate, ID-dependent weak entity
40:26
relationships are known as identifying relationships.
40:33
These are represented using a solid line on the entity relationship diagram.
40:39
They are called identifying relationships because a part of the identifier of the weak entity
40:47
is derived from the parent. If, by contrast, the identifier of a weak entity
40:56
is not partially composed of the identifier of a strong entity,
41:03
then we call that a non-identifying relationship, and we can say that the weak entity is non-ID-dependent.
41:11
In our entity relationship diagrams,
41:18
these non-ID-dependent relationships are represented with a dashed relationship line.
41:27
And this dash relationship line is also used to model relationships between strong entities.
41:35
If the relationship between a strong entity and a weak entity
41:44
is a non-ID-dependent relationship,
41:49
then the primary key of the strong entity
41:54
will appear as a foreign key within the weak entity.
42:00
But it will not be a part of that weak entity's primary key.
42:07
Let's see some examples that will hopefully clarify the difference between ID-dependent and
42:14
non-ID-dependent relationship. In our first example, we see an ID-dependent weak entity
42:24
named vehicle. We can tell that this is an ID-dependent weak entity
42:29
because part its primary key is composed
42:34
of the primary key from a strong entity.
42:40
In this case, the strong entity is the auto model entity,
42:45
and its primary key is the combination of manufacture and model.
42:51
And in the vehicle entity, we can see that manufacturer and model are part of the primary key.
42:59
This is therefore an ID-dependent weak entity.
43:06
By contrast, we might use a different type of primary key for our vehicle entity,
43:14
such as a VIN or vehicle identification number.
43:19
And in that case, the primary key of the strong entity-- auto
43:24
model-- does not appear as part of the primary key
43:31
of the weak entity vehicle. Instead, the primary key of auto model
43:38
appears in the vehicle entity as a foreign key link back
43:45
to the strong entity. In this case, it would be a non-ID-dependent weak entity.
43:54
And again note that, ID-dependent relationships
44:00
are shown with a solid line, while non-ID-dependent relationships are shown with a dashed line.
44:08
Next I would like to talk about a different type
44:14
of relationship between entities, which we can call a supertype subtype relationship.
44:20
In a supertype subtype relationship,
44:27
the subtype is a special case, or a specific version
44:34
of the supertype. As an intuitive example, consider the generic concept
44:42
of a vehicle. That might be a supertype.
44:48
And then we might have specific subtypes of the vehicle class,
44:54
such as a car or a truck or a motorcycle, where there
45:02
may be unique attributes about a car or a truck or a motorcycle
45:09
that we want to track, in addition to a set of attributes
45:14
that we want to track for every vehicle, regardless of what specific type of vehicle it may be.
45:22
This is an example of a supertype subtype relationship. Broadly speaking, there are two different types
45:30
of supertype subtype relationships. The first is called an exclusive relationship,
45:38
and the other is called an inclusive relationship. If a supertype subtype relationship is exclusive,
45:47
what that means is that each instance of the supertype
45:53
can be related to, at most, one of the subtypes.
45:59
So a vehicle, for example, can be a car or a truck
46:05
or a motorcycle, but it cannot simultaneously be a truck and a motorcycle.
46:12
That would be an exclusive supertype subtype relationship.
46:18
By contrast, we might have inclusive supertype subtype
46:23
relationships. And in an inclusive supertype subtype relationship,
46:31
the supertype can be related to one or more subtypes.
46:38
As an example, consider a university where we might have a supertype that we call person,
46:48
and we might have a subtype which we call student,
46:54
and another subtype which we call employee.
47:00
In an inclusive supertype- subtype relationship,
47:05
a given person at our university could simultaneously be a student and an employee of the university.
47:16
Thus when a supertype can be related to one or more subtypes, we call that an inclusive
47:25
supertype subtype relationship. In these supertype subtype relationships,
47:31
we will always connect supertypes to their subtypes by using this same attribute.
47:40
Recall that earlier, we talked about HAS-A relationships.
47:46
So an employee HAS-A locker, or an employee
47:52
has a parking space. In supertype-subtype relationships,
48:02
if the subtype is a specific case of the supertype,
48:10
then we can call that an IS-A relationship.
48:16
Consider our previous vehicle example.
48:21
A car is a vehicle. A truck is a vehicle.
48:27
A motorcycle is a vehicle. Each of those three specific subtypes
48:35
has individual characteristics which make it unique,
48:41
but all three can be broadly classified as vehicles-- types of vehicles.
48:48
These are IS-A relationships. Remember that in these types of relationships
48:55
in entity relationship modeling, the subtype will inherit all of the attributes
49:02
or all of the properties of its supertype. Let's see some graphical examples,
49:08
which will hopefully make these concepts easy to understand.
49:15
In our first example, we see a supertype subtype relationship
49:20
where the supertype is named student. And we have two different subtypes-- two different types
49:28
of students. They are undergraduate students and graduate students.
49:34
This is an example of an exclusive supertype subtype relationship, because a student is allowed to be
49:43
an undergraduate student or a graduate student but it cannot
49:49
be both at the same time.
49:54
Note here, that we have an attribute in our supertype table named isGradStudent, for which we can
50:03
imagine that values might be true or false, which tells us
50:09
which of the relationship paths to follow in order to get further information about this specific student.
50:20
Also note that each of the subtypes-- undergraduate
50:25
and graduate, contains attributes that apply only to that specific type of student.
50:36
So a graduate student, for example, has an undergraduate GPA and a score on the GMAT exam,
50:45
whereas undergraduate students in this model do not have those attributes.
50:54
Remember that when the subtype is a specific type of the supertype-- in this case,
51:02
a graduate student is a student, an undergraduate student
51:07
is a student-- then this subtype inherits all of the attributes
51:14
of the supertype. Also note that we use student ID in order
51:23
to link the supertype to the subtypes.
51:29
In our second example, we have an inclusive supertype subtype
51:34
relationship, where our supertype is a student, and our subtypes are various student clubs.
51:43
So we may have a hiking club, a sailing club. Now this is an inclusive supertype subtype relationship,
51:52
because each instance of the supertype-- in this case each student-- can potentially be related
52:01
to more than one subtype. So a student might be a member of the hiking club
52:07
and the sailing club simultaneously. One last point to make on these supertype subtype
52:13
relationships-- note that we use this circular symbol
52:20
with a line under it to indicate that a relationship is a supertype subtype relationship.
52:27
If an X symbol appears inside the circle,
52:34
we know that is an exclusive supertype subtype relationship.
52:40
Whereas if no X appears inside the circle, we know that is an inclusive supertype subtype relationship.
52:51
As our last topic for this lecture, I would like to discuss recursive relationships.
52:59
If you recall earlier, we said that it's possible for an entity to be related to itself
53:07
in a unary relationship. And here we want to explore this concept
53:13
in a little more detail. Consider this employee entity.
53:21
The primary key of the employee entity is employee ID.
53:26
And we have another attribute in this entity, which is named manager ID.
53:32
And we see that there is a one-to-one relationship between employee ID and manager ID.
53:40
In this example, the important thing to remember is that a manager ID is actually an employee ID.
53:50
It's just being used with a different name here in order to allow us to map a specific type of relationship
54:01
between employees. These types of recursive relationships
54:06
can be very useful for tracking things like customer referrals.
54:15
For example, imagine that we run a dentist's office.
54:22
Each customer that we have-- each patient if you will-- might have been referred to us by another patient,
54:31
and we might want to keep track of that information. We can track that information in our customer table,
54:38
or our patient table, by using one of these recursive relationships.
54:45
Another great use for these recursive relationships is to implement hierarchies.
54:51
And that is what can be accomplished using the sort of design that we see here
54:56
with our employee entity. Let's see how this works in more detail.
55:03
Each of the rectangles that we see here represents a record or a row in our employee table.
55:10
At the top of our hierarchy, we have employee ID number one.
55:19
And for this employee ID, the value of manager ID is null.
55:25
And what this tells us is that employee ID number one has no manager.
55:31
So we might imagine that employee ID number one is the CEO of the company, for example.
55:37
Moving down to the next layer in the hierarchy,
55:43
we have employees two, three, and four.
55:50
For each of these employees we see that their manager ID is this one.
55:56
So this tells us that employees two, three, and four
56:02
report to employee number one. Or another way of saying that is, employee number one
56:09
is the manager, or the boss of employees two, three, and four.
56:16
Further down in the hierarchy we have employees five and six.
56:23
And employees five and six have a manager ID of three.
56:29
This shows us that employees five and six work for employee three, who in turn works for employee one.
56:40
By implementing this sort of recursive relationship,
56:46
I hope you can understand how we could easily track the hierarchical relationships among employees
56:55
and managers for an organization that has as many levels of management as we want,
57:01
and as many employees as we want. We could use this approach to track
57:07
employee-manager relationships for a small company, containing as few as five or six employees,
57:15
like the example we see here. But we could use exactly the same approach
57:20
to track all of the employee and employee-manager relationships
57:27
for a massive company, like an automobile manufacturer that
57:33
might have nine or 10 levels of management, and tens of thousands of employees.
57:40
The point to take away from this example is that we can implement a hierarchy of any arbitrary
57:49
level of complexity using just this simple data modeling approach.
57:59
Well, my friends, thus ends our brief exploration of data modeling and the entity relationship model.
58:08
I hope you learned something interesting. Until next time, have a great day.
58:15