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