Database Lesson #2 of 8 – The Relational Model

Database Lesson #2 of 8 – The Relational Model

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

Danny Hutson

100 thoughts on “Database Lesson #2 of 8 – The Relational Model

  1. I've learned a lot from this lesson as well as L1 and willing to continue to learn until the end of this course. In the meantime, thanks to Dr Soper for this impressive free course of his.

  2. @ 39:00 minutes when multivalued attributes were removed and made 3 column then the ORDER ID was duplicated. so its no more a primary key. please help me to understand that.

  3. +Dr. Daniel Soper 

    In your last example, instead of having the following relations (primary keys are in double quotes):

    ("OrderID", "ProductID", Order_Quantity),
    ("ProductID", … , Unit_Price),
    ("OrderID", Order_Date, CustomerID), and
    ("CustomerID", Customer_Name, Customer_Address) .

    Can you have this relation instead?

    ("OrderID", "CustomerID", "ProductID", Order_Quantity),
    ("OrderID", Order_Date),
    ("ProductID", … , Unit_Price) and
    ("CustomerID", … , Customer_Address) .

  4. Awesome, just awesome the way you explain it, so it can be understood and you give everyday examples.I've looked at video after video and finally found yours. I was struggling now I'm starting to get. And this is just the 2nd video. THANK YOU

  5. 00:19 Topic Objectives
    01:37 Entity: represents a single theme or concept with which we want to track its attributes. e.g. department, employee, or project.
    02:43 Relation: a relation is a type of table. Relation table characteristics: rows contain instances of a table, each column is an attribute, each column are of the same data type, each column has a unique name, cells of the table hold only a single value, no two rows can be identical, the order of the rows and column do not matter.
    08:28 DB terminology synonyms: table = relation, row = record, column = field = attribute
    09:40 A key is a column(s) of a relation whose values are used to identify a row
    11:45 Topology of DB keys
    12:09 Composite key: unique key, composed of two or more columns.
    15:12 Candidate key: unique key, has potential to become primary key.
    15:50 Primary key: unique key, main key for identifying a specific row in a table.
    17:35 Surrogate key: unique key, added to a relation to act as the primary key.
    19:20 Relationships between tables
    19:30 Foreign key: non-unique, a foreign key is a primary key from one table placed into another table so that the tables can be linked
    22:48 Referential integrity ensures foreign keys refer to actual entities in another table
    25:30 Null value
    30:32 Data normalization: process of analyzing a relation to ensure that it is well formed (i.e. not susceptible to anomalies)
    37:02 First normal form: table contains no multivalued attributes. Every attribute value is atomic. Thus, all relations are in first normal form.
    39:13 Second normal form: 1NF + every non-key attribute is fully functionally dependent on the entire primary key. i.e. we can't have partial dependencies among attributes of the table.
    43:32 Third normal form: 2NF + no transitive dependencies

  6. This IS the best video I've ever watched for a college class. You are explaining the whole chapter about relational models in depth without skipping over any information. I WISH you were my teacher…. Thank you for saving another student.

  7. this is a good video I have to say – but about 38mins in – you say the table is in 1NF however the customer address column isn't atomic. you could have a county column and a state column

  8. I turn it up to 2 times speed to get through this faster. I think he intentionally talks slowly so that you can process the information.

  9. I dont understand why "order_date" does not go with the [order_line table]. Instead you have it in the [customer_order table]. Could you explain?

  10. it is considered to be the best approach for the beginners. i found some personal issue while hearing out this. He was quite slow in delivering most often i went for yawning and lied for long sleep.. :p anyways Dr. praised for ua t ur best delivered approach……

  11. Dr. Soper, while the information is valuable. You tend to have the "Bueller, Bueller" personality going on. As an instructor, you need to inject more Energy into your online lecture. This information is so valuable to me, but your putting me to sleep!
    Though when I speed it up 1.5, It clicks.
    I think you purposely spoke this way, I would just speak a bit faster.
    Kudos though sir, thank you for your time.

  12. You should write university textbooks. The current material out there there is vague and doesn't explain the terms in real terms…. You are a brilliant lecturer Doctor D.

  13. Hi Dr. D. I am struggling to understand this concept.. To be a well formed relation, every determinant must be a candidate key. My understanding is that every functionally dependent attribute, must have a primary key. But the line says candidate key. A candidate key is a key which is a candidate for becoming a primary key. What am I missing here?

  14. I read two textbooks and couldn't learn this stuff. You are helping to make online school doable and in the process saving me thousands of dollars and hours of time. I thank you.

  15. Simply superb. Highly appreciated. Full respect. I have learned a lot. Can't thank you enough Dr. Daniel Soper! My best regards.

  16. Well instead of creating his own lectures my teacher has lead me here to be subjected to quite possibly the slowest speaking person I've ever heard. Great info, absolutely painful delivery.

  17. 34:00 what if one student can get assigned only a unique Dorm Name? This part and ambiguity was really difficult to understand me, as I try to grasp the information logically and formally, not intuitively.

  18. Brilliant. Very brilliant. Thank you Dr. Soper. I am currently using this in my class. Sorry that I spelled your name wrong in my first comment.

  19. These first two video parts summed up around 8 hours of lectures in school, and I learned even better from these two videos

  20. The best Instructor and the best Basics of Database tutorial, that I’ve seen so far on the social media.
    We really appreciate it.
    Thank you Sir Dr. Soper


Leave a Reply

Your email address will not be published. Required fields are marked *