00:00:00
00:00:03
Welcome, everyone.
00:00:04
This is Dr. Soper
And today we
00:00:06
will be going through our first
lecture for our database class.
00:00:11
I need to operate
under the assumption
00:00:13
that not everyone in the
class has had experience
00:00:16
with databases in the past.
00:00:19
And so this first lecture is
intended to bring everyone up
00:00:23
to the same basic
level of understanding
00:00:26
with respect to several
important database concepts.
00:00:30
We have several
different objectives,
00:00:33
which we will seek to
achieve in this lecture.
00:00:36
These are listed on
the screen right now.
00:00:39
First, we want to
try to understand
00:00:43
why storing data in a
list is not necessarily
00:00:46
a very good idea.
00:00:48
It can cause many different
types of problems.
00:00:51
Second, we want to see if we
can gain some insight into why
00:00:55
an organization might
want to use a database.
00:00:58
Third, we will see how the
notion of related tables
00:01:03
of data, which forms a core
part of the relational database
00:01:07
model, provides a
basis for eliminating
00:01:11
many of the problems which are
associated with storing data
00:01:14
in a list.
00:01:16
We'll also explore the various
components and elements
00:01:19
that comprise a database
or a database system.
00:01:23
And we will learn about
the purpose of something
00:01:27
that we call a DBMS, a
database management system.
00:01:31
And along the way,
we're going to explore
00:01:34
some of the functions
that a robust database
00:01:38
application can provide to us.
00:01:41
What, then, is the
purpose of a database?
00:01:43
Well, it's important to remember
that a database does not
00:01:46
have just a single purpose.
00:01:49
Instead, there are
several key advantages
00:01:52
that databases provide.
00:01:54
First, a database provides a
repository for storing data.
00:01:59
That's kind of
implicit in the name.
00:02:01
Database implies that we
have a place to store data.
00:02:05
However, what might
not be so obvious
00:02:08
is that databases provide
an organizational structure
00:02:13
for data.
00:02:14
That is, we don't just
have a place to store data,
00:02:17
but the database also provides
an organized structure
00:02:21
into which those
data can be placed.
00:02:24
Finally, a database
provides us with a mechanism
00:02:30
for interacting with our data.
00:02:32
Now, interacting with
data can generally
00:02:36
be described in four
different operations.
00:02:41
Here they're listed as
querying, creating, modifying,
00:02:46
and deleting data.
00:02:47
But there's another more
interesting acronym, which
00:02:50
may help you to remember this.
00:02:52
And that acronym
is CRUD, C-R-U-D.
00:02:57
This stands for create,
read, update, and delete.
00:03:02
These are the four
basic operations
00:03:04
that we can use when
interacting with data.
00:03:09
A key point to remember here
is that, in business, there
00:03:14
are many natural, hierarchical
relationships among data.
00:03:21
For example, a customer
can place many orders.
00:03:26
Another way of saying that
is many different orders
00:03:29
can be associated with
the same customer.
00:03:32
Or another example
is a department
00:03:35
can have many
different employees,
00:03:38
but a given employee might
work in one, and only
00:03:43
one, department.
00:03:44
So these are hierarchical
relationships among the data.
00:03:49
And a relational database
allows us to model and represent
00:03:53
these relationships.
00:03:55
Let's take a look
at a list of data.
00:03:59
What I mean by a list here is
a simple, two-dimensional table
00:04:05
of data.
00:04:06
And in this table,
we store information
00:04:09
that is important to
us for some reason.
00:04:11
An example might be, say that
we have many different projects
00:04:16
in our company, and we want to
keep track of who the project
00:04:21
manager is for each project.
00:04:25
Therefore, for each
project, we may
00:04:27
track the project manager's
name, their ID number,
00:04:32
and maybe their phone
extension within our company.
00:04:37
Now, ordinarily this wouldn't
be such a big problem.
00:04:40
But imagine if the same person,
the same project manager,
00:04:44
is simultaneously managing
multiple projects.
00:04:49
In that case, using
a simple list,
00:04:52
the project
manager's information
00:04:55
would necessarily appear
on the list multiple times.
00:04:59
Now, what is the
problem with this?
00:05:03
Well, there isn't really
any major problem,
00:05:06
aside from the fact
that it's redundant.
00:05:08
Another way of saying that
is we are using more space
00:05:12
than is necessary to record
which project manager is
00:05:17
associated with which projects.
00:05:20
Another problem with
storing data in a list
00:05:24
is that the list of data may
contain more than one concept
00:05:30
or more than one theme.
00:05:33
I want you to remember this
idea of a business concept,
00:05:36
because we will see it again
and again throughout our course.
00:05:41
A business theme
or concept refers
00:05:44
to a number of different
attributes or properties
00:05:49
that are all naturally related
to one type of business entity.
00:05:56
An example might be an employee.
00:06:00
An employee is a
business concept.
00:06:03
Employees have
different attributes
00:06:06
that we may want to
track for each employee
00:06:09
in our organization.
00:06:11
For example, we may want
to track the employee's ID
00:06:14
number, the employee's
name, the employee's salary.
00:06:20
Other examples of business
concepts or business themes
00:06:23
include things like departments,
products, customers, orders,
00:06:31
locations, and so forth.
00:06:34
Returning to lists,
a major problem
00:06:37
is with these multiple themes.
00:06:40
That is, for each
row of the list,
00:06:43
we might be recording
information about more than one
00:06:46
of these business concepts.
00:06:49
As an example, consider our
list of project managers.
00:06:52
If we also wanted to include
project information-- that
00:06:57
is, perhaps, a project
name, a project
00:07:01
ID, any additional information
about the project--
00:07:05
we might store that in the
same row along with the project
00:07:09
manager.
00:07:11
Aside from redundancy,
as I mentioned earlier,
00:07:14
the problem here
is not necessarily
00:07:17
how we are storing
this information,
00:07:19
but what might happen
to the information
00:07:23
if it is stored in this way.
00:07:25
When we store
information in a list,
00:07:28
we introduce the
possibility of something
00:07:32
called data anomalies.
00:07:34
And specifically, there are
three types of these anomalies.
00:07:40
Here they're listed as a
deletion problem, an update
00:07:43
problem, and an
insertion problem.
00:07:46
Later in our course,
we will refer
00:07:47
to these as deletion
anomalies, update anomalies,
00:07:51
and insertion anomalies.
00:07:54
So the structure of a list
and its associated problems
00:07:58
of redundancy and
multiple themes
00:08:01
can produce a situation in which
these modification problems
00:08:06
potentially could occur.
00:08:08
So let's take a look at some
of these modification issues.
00:08:13
Here we have a list of data,
which contains information
00:08:16
on students.
00:08:18
So we have a student's
last name, first name,
00:08:21
their email address.
00:08:23
And then we see who that
student's advisor is.
00:08:27
So the last name
of the student's
00:08:29
advisor, the email of
the student's advisor,
00:08:32
and the department in
which they work, along
00:08:35
with some additional
information.
00:08:38
Let's take a look at how some
of these modification anomalies
00:08:42
might emerge.
00:08:43
In the first case,
imagine that we want
00:08:45
to change a student's advisor.
00:08:48
And for this
example, we're going
00:08:49
to change Advisor Baker
to Advisor [? Tiang. ?]
00:08:54
When we do this, not
only are we going
00:08:56
to have to update the last name
of the advisor in the list,
00:09:00
but to maintain the overall
quality of the data, a concept
00:09:04
that we call data
integrity, we are
00:09:07
going to have to also update
the advisor's email address.
00:09:12
Note, in this case,
that we do not
00:09:14
need to update the department or
the administrator's last name,
00:09:19
because those are the same for
advisors [? Tiang ?] and Baker.
00:09:24
However, if, for some
reason, we wanted
00:09:28
to change the student's advisor
from, say, Baker to Valdez,
00:09:34
well, now not only
do we need to update
00:09:37
the last name and
the email address,
00:09:40
but we also need to
update the department
00:09:43
and the admin last name
attributes as well.
00:09:47
Just to do something as simple
as changing the student's
00:09:50
advisor with this
list approach requires
00:09:54
that four separate pieces
of information be updated.
00:09:58
So this is what we might call
an update anomaly or an update
00:10:03
problem.
00:10:05
Next, let's look at
a deletion problem.
00:10:08
Imagine that our student,
listed here as Chip Marino,
00:10:14
decides to drop out
of the university.
00:10:18
So we need to remove Chip
from our list of students.
00:10:22
Now, look what happens if
we delete this row of data.
00:10:26
When the row of data
is deleted, not only
00:10:30
do we lose the
student's information,
00:10:33
but we've also lost
information about the advisor
00:10:38
and the department in
which that advisor works.
00:10:42
That is, you will notice that
Advisor [? Tran ?] is not
00:10:45
currently assigned to
advise any other students.
00:10:49
So when we delete
this row of data,
00:10:51
we may entirely
lose the knowledge
00:10:54
that Advisor
[? Tran ?] even exists.
00:10:57
And that can be a problem.
00:10:59
In this case, this is called a
deletion problem or a deletion
00:11:04
anomaly.
00:11:06
Finally, let's look at
an insertion anomaly.
00:11:09
Let's say that at
our university,
00:11:13
we decide that we want
to add a new department.
00:11:18
So we create a
biology department.
00:11:21
Well, this is fine.
00:11:22
We add information to our list.
00:11:24
We now have a biology department
and an administrator's
00:11:29
last name.
00:11:31
However, we now have all of
this missing data in our table.
00:11:35
There are empty cells.
00:11:37
There's no information
for a student.
00:11:40
There's no information
for an advisor.
00:11:42
All we have here is
information for the department.
00:11:46
This means that
data are missing,
00:11:49
and we're not efficiently
utilizing our available storage
00:11:53
space.
00:11:55
These are some of the
problems with these lists.
00:11:57
Now, remember,
earlier I mentioned
00:12:00
that in the business world
there are complex relationships
00:12:05
among different types of data.
00:12:07
For example, a department
may have many employees
00:12:14
who work in that department.
00:12:16
Or a product may be assembled
from many different components.
00:12:22
Or a customer might place
many different orders
00:12:26
with our company.
00:12:28
So there are these
natural complexities
00:12:31
that arise in business.
00:12:34
And relational databases, as
we will see, not only solve
00:12:39
the problems that are
associated with lists,
00:12:43
but also allow us to model
these natural relationships
00:12:47
among business data.
00:12:49
Now, I've been using this
term relational database.
00:12:52
So it would probably
be a good idea for us
00:12:55
to consider what a relational
database actually is.
00:12:59
On a very basic level,
relational databases
00:13:03
store data in a table.
00:13:07
Now, a table is simply
a two-dimensional grid
00:13:11
of data that contains
columns, and it contains rows.
00:13:18
The convention in the
relational database world
00:13:22
is that the columns represent
different attributes
00:13:27
of an entity.
00:13:29
And each row in the
table represents
00:13:33
an instance of the entity.
00:13:36
So for example, if I
have an employee table
00:13:39
with an employee ID, an employee
name, and an employee telephone
00:13:45
number, we would list
those three attributes--
00:13:49
ID, name, and telephone number--
as columns in the table.
00:13:55
And then each row in the
table would represent
00:13:58
an individual employee.
00:13:59
00:14:02
Again, we said that there are
these natural relationships
00:14:06
among different
business concepts
00:14:08
out there in the business world.
00:14:11
In a relational database, data
for each of these concepts
00:14:15
is stored in its own table.
00:14:18
So I may have an employee table.
00:14:21
I may have a department table.
00:14:23
And then I can
create a relationship
00:14:27
between those tables,
which will allow
00:14:29
me to figure out which employees
work in which departments.
00:14:35
So a good way of
thinking about this
00:14:38
is instead of storing all of
the information in one big list,
00:14:42
we break the list apart
into separate pieces
00:14:47
according to which
information is
00:14:50
associated with which business
theme or business concept.
00:14:56
Therefore, all of
the information
00:14:59
associated with
an employee might
00:15:01
go into an employee table.
00:15:03
And all of the information
associated with a department
00:15:06
might go into a
department table.
00:15:09
Although, inside a
relational database,
00:15:14
information about different
business concepts or business
00:15:17
themes is stored
in separate tables,
00:15:21
it may be necessary-- say, for
reporting purposes-- for us
00:15:27
to reassemble all of the data
from these different tables
00:15:32
back into a list.
00:15:35
Now, we can accomplish this in
the relational database world
00:15:40
by performing something
called a join operation.
00:15:44
That is, we join
the tables together.
00:15:47
Now, a very, very important
concept for you to understand
00:15:52
is that in the relational
database world,
00:15:56
we link the records
in different tables
00:15:59
together using matched
pairs of values.
00:16:06
These matched pairs
of values allow
00:16:10
us to associate a
row in one table
00:16:14
with one or more rows
in another table.
00:16:19
For example, if we
have a project table
00:16:22
and we have a
customer table, and we
00:16:25
use an attribute
called a customer ID
00:16:31
to uniquely identify each
customer, then what we can do
00:16:36
is we can place a customer ID
attribute in the project table.
00:16:43
And we can then use
the ID of the customer
00:16:47
in each table to link
related rows together.
00:16:52
Now, of course, you may
be listening to this,
00:16:55
and you may think,
why do we want
00:16:56
to do all of this extra work?
00:16:59
And it is true that relational
databases are more complicated
00:17:04
than storing data in a list.
00:17:08
However, as we will see,
relational databases
00:17:12
have many, many
advantages over a list.
00:17:16
Among these, first,
a relational database
00:17:20
minimizes data redundancy.
00:17:23
In the examples
we saw earlier, we
00:17:25
saw that there exists
with lists the possibility
00:17:30
to have all sorts
of redundant data.
00:17:33
Relational databases
eliminate this.
00:17:36
Relational databases
also allow us
00:17:38
to model the natural
relationships that
00:17:41
exist among business
concepts in the real world.
00:17:45
And there are several
other advantages as well,
00:17:47
including providing
a solid basis
00:17:49
from which to do things
like generate reports
00:17:53
or build user interface forms.
00:17:57
I mentioned a few
minutes ago that there
00:18:00
are four basic ways of
interacting with data-- create,
00:18:08
read, update, and delete.
00:18:11
In the relational
database world,
00:18:14
we have access to something
called the Structured Query
00:18:17
Language, often called SQL,
or SEQUEL, if you like.
00:18:23
And this is the
primary tool that we
00:18:27
can use for
performing these four
00:18:29
basic operations on our data.
00:18:32
In a few weeks, we will begin
learning the Structured Query
00:18:35
Language.
00:18:37
This is an extremely valuable
skill for you to develop.
00:18:42
If you are able to learn the
Structured Query Language well,
00:18:46
then you will be able
to sit down and work
00:18:49
with virtually any modern
database with a very, very
00:18:55
short learning curve.
00:18:57
It doesn't matter if you need
to work with a SQL Server
00:19:00
database, or an Access
database, or an Oracle
00:19:04
database, or a DB2, or a MySQL
database, or even a database
00:19:10
for, say, a mobile device.
00:19:13
Most databases, including all
of those that I mentioned,
00:19:17
support the Structured
Query Language.
00:19:20
So if you can learn the
Structured Query Language,
00:19:23
you will be very well
positioned to work
00:19:25
with almost any database.
00:19:28
Even though we're still
early in our course,
00:19:31
I want to give you an example
of the Structured Query Language
00:19:34
now so that you can see
that it's really not
00:19:38
that difficult to understand.
00:19:41
This is certainly not a full
scale computer programming
00:19:45
language.
00:19:46
The Structured Query Language
was designed from the ground up
00:19:50
to be simple to use and to
be simple to understand.
00:19:53
00:19:56
So what we have
here is an example
00:19:58
where we have three tables.
00:20:01
There is a customer
table, and a course table,
00:20:07
and an enrollment table.
00:20:10
So let's say that
these are art courses.
00:20:15
We have customers who have
signed up to take different art
00:20:20
courses to see, perhaps,
if they can learn to paint
00:20:24
or they can learn to sculpt.
00:20:26
Now, although we start
with three tables,
00:20:28
if we need to combine
all of the information
00:20:32
together in order to
restore the original list
00:20:36
structure of the
data, we can do that
00:20:40
by using something called
a SQL SELECT statement.
00:20:45
Here we see an example
of such a statement.
00:20:48
I know this looks complicated.
00:20:50
But hopefully, you are able
to read this even right now
00:20:56
at the beginning of
our course and get
00:20:58
a good idea for what this
statement is supposed to do.
00:21:03
So let's take a look.
00:21:05
We are selecting the customer's
last name, the customer's
00:21:09
first name, the customer's phone
number, and the date of the art
00:21:15
course, the amount paid out
of the enrollment table,
00:21:21
the course itself,
and the total course
00:21:24
fee from our three
different tables--
00:21:27
customer, enrollment,
and course.
00:21:30
We are then linking the related
records in each table together
00:21:34
using these matched
pairs of values
00:21:37
that I mentioned earlier.
00:21:39
In the first case, we are
linking the customer table
00:21:42
to the enrollment table using
matched pairs of customer
00:21:47
numbers.
00:21:49
And in the second case, we're
linking the course table
00:21:53
to the enrollment table
using matched pairs of course
00:21:57
numbers.
00:21:58
What this will do
for us is restore
00:22:02
the original list of data.
00:22:05
So you can see our list.
00:22:06
It contains the customer's
last name and first name,
00:22:09
customer's phone number,
the course date, the amount
00:22:14
that the customer
has paid so far,
00:22:17
the course they are taking,
and the total course fee.
00:22:21
Now, the SQL statement that
we saw was a SELECT statement.
00:22:27
And that is a statement
which just pulls data
00:22:31
from the database.
00:22:33
There are many
other things that we
00:22:35
can do with SQL, like deleting
data, inserting new data,
00:22:40
updating data in the
database, and so forth.
00:22:44
And we will learn how to do
all of those various tasks
00:22:48
when we study the Structured
Query Language in more
00:22:52
depth in a few weeks.
00:22:55
Now I would like to
turn our attention
00:22:57
to some conceptual information.
00:23:02
Let's focus on a
database system.
00:23:05
It's important to remember
that a database system is not
00:23:09
just the database itself.
00:23:13
Rather, it exists in a
broader hierarchy, which
00:23:17
includes users, application
software programs which
00:23:22
rely on the database, something
called a database management
00:23:27
system, a DBMS, and then, of
course, the database itself.
00:23:33
The broader picture
of a database system
00:23:36
includes these four components.
00:23:39
Here we see the relationships
between the four components
00:23:43
of a database system.
00:23:46
On the far left, we see users.
00:23:50
Now, users are
people, just like you
00:23:53
or me, who might need to
use the data in a database.
00:23:57
But a user doesn't necessarily
have to be a human being.
00:24:02
It is possible, for example,
for other software programs
00:24:07
to be users of our
database as well.
00:24:11
Now, you will see
that users interact
00:24:14
with database applications.
00:24:17
These are the programs
that rely on the database.
00:24:20
So if we are a human user,
we might, for example,
00:24:24
use a website, which
interacts with a database.
00:24:29
That website could be considered
a database application.
00:24:33
You will notice that
the database application
00:24:37
talks to the DBMS.
00:24:40
The DBMS, the database
management system,
00:24:45
acts as a gatekeeper.
00:24:48
All of the information flowing
in or out of the database
00:24:53
must travel through the DBMS.
00:24:57
It is a critical mechanism
for maintaining the quality
00:25:02
of the data in the database.
00:25:04
We do not allow our users
or our database applications
00:25:09
to directly access the
data in the database.
00:25:13
To work with the data,
to access those data,
00:25:17
database applications must go
through the database management
00:25:22
system.
00:25:24
And then, finally, far over on
the right side of our screen,
00:25:28
we see the database itself.
00:25:31
And this is where we
actually store the raw data
00:25:34
in their separate tables.
00:25:38
So what do the users of
a database system do?
00:25:41
Well, they do many
different things.
00:25:43
You and I are users
of database systems
00:25:45
on almost a daily basis,
even if we don't realize it.
00:25:50
So we can look up our checking
account balances online.
00:25:55
We can produce reports.
00:25:58
We can seek information
on Wikipedia.
00:26:02
All of these are examples
of us as users interacting
00:26:06
with database applications.
00:26:10
A few interesting things
to note about the database
00:26:12
itself is that it is a
self-describing collection
00:26:18
of related records.
00:26:21
What this means is that
the database does not just
00:26:24
contain the data, but it
also contains a definition
00:26:28
of its own structure.
00:26:30
Databases also contain metadata.
00:26:34
The simplest way to
understand metadata
00:26:36
is that metadata are data
which describe other data.
00:26:42
And in the context of
the database world,
00:26:46
metadata are data which
describe the structure
00:26:49
of the data in the database.
00:26:52
So for example, I may say that
an employee table contains
00:26:57
an attribute called Employee
ID, and that ID value is
00:27:03
an integer.
00:27:05
That knowledge that the
Employee ID value is an integer
00:27:10
is metadata.
00:27:12
And of course, databases
allow us to establish
00:27:16
relationships between tables.
00:27:19
So that means the
tables within a database
00:27:22
are related to each other
according to the relationships
00:27:26
that we establish.
00:27:28
So it's important to remember
that a database does not just
00:27:31
contain the data that
we have put into it.
00:27:35
A database also
contains metadata,
00:27:38
which describe our data.
00:27:41
It contains, or
potentially can contain,
00:27:46
what we might refer
to as overhead data.
00:27:49
These are tables
of data that are
00:27:52
used to improve
performance or track
00:27:57
the status of the database
or how users are interacting
00:28:00
with the database.
00:28:02
And one of the major types
of these overhead data
00:28:05
are called indexes.
00:28:08
Later in our course, we will
examine database indexes.
00:28:12
And of course,
databases can also
00:28:15
contain application metadata.
00:28:19
So these are data
values that are
00:28:21
used by the applications
which rely upon the database.
00:28:27
An application, for example,
might store user preferences
00:28:32
in the database.
00:28:34
Now, to reiterate, a DBMS, or
a database management system,
00:28:39
serves as a gatekeeper
or an intermediary
00:28:43
between the database
and the applications
00:28:47
which rely upon that database.
00:28:50
The purpose of the DBMS
is to control and manage
00:28:54
the operations of the database.
00:28:57
The DBMS is a special kind
of software program, which
00:29:02
is used to not only
create databases,
00:29:06
but also to process and
administer those databases.
00:29:10
So a DBMS is a type of
application program.
00:29:15
But it is not the database.
00:29:18
The DBMS interacts
with the database,
00:29:21
but it itself is
not the database.
00:29:26
What, then, can a database
management system do for us?
00:29:30
Well, the answer is it can
do many different things,
00:29:33
as we see here on your screen.
00:29:35
We can use the DBMS to
create a new database.
00:29:39
And then, within
that database, we
00:29:41
can create tables and establish
relationships between tables.
00:29:48
We can read, write, edit, and
delete data using the DBMS.
00:29:56
We can establish rules
which regulate and control
00:29:59
the data in the database.
00:30:02
The DBMS provides an additional
layer of security for us.
00:30:06
And it also provides
mechanisms through which
00:30:11
we can backup our data and
recover our data if anything
00:30:17
ever goes wrong.
00:30:18
So the DBMS is a very
critical piece of software.
00:30:23
Now, businesses often operate
with many different types
00:30:27
of business rules
or constraints.
00:30:31
And one of the great things
about working with databases
00:30:34
is that the DBMS can
establish and enforce
00:30:39
many different types
of constraints.
00:30:42
One of the most useful
types of constraint
00:30:45
that a DBMS can enforce is
called a referential integrity
00:30:49
constraint.
00:30:51
The purpose of a referential
integrity constraint
00:30:54
is to ensure that values that
we are entering in one table
00:31:00
have matching values
in another table.
00:31:04
Now, that's a very
abstract way of trying
00:31:07
to convey a simple concept.
00:31:10
As an example, imagine
that we have a customer
00:31:12
table and a project table.
00:31:16
And we are trying
to enter information
00:31:19
about a new project
in the project table.
00:31:22
And we specify that
the customer ID
00:31:27
that is associated with this
project is customer number 5.
00:31:32
Referential integrity means
that the database will actually
00:31:37
check in the
customer table to see
00:31:40
if customer number 5 exists.
00:31:44
If customer number
5 does not exist,
00:31:47
then the database will not
allow us to add that customer
00:31:52
ID to our project table.
00:31:55
And in this way,
I hope you can see
00:31:58
it helps to maintain
the quality of the data
00:32:01
in the database, what
we call data integrity.
00:32:04
00:32:06
Now, the fourth component
of our database system
00:32:09
were database applications.
00:32:12
And database
applications are simply
00:32:14
those software programs
that need to use or work
00:32:20
with data in the database.
00:32:23
So these might be
data driven websites.
00:32:26
They might be mobile apps.
00:32:28
They might be
desktop applications
00:32:30
or productivity software.
00:32:34
Just remember that these
applications are not
00:32:37
allowed to talk to or use
the database directly.
00:32:43
But rather, they must
go through the DBMS
00:32:47
to work with or use those data.
00:32:49
Finally, for this lecture, I
want to just talk about some
00:32:55
of the different types
of database systems
00:32:57
that are available.
00:32:59
First, we can consider something
called a personal database
00:33:03
system.
00:33:04
These are very small databases.
00:33:08
They are typically used by
a single user at a time.
00:33:13
They are not complicated.
00:33:15
They are simple in design.
00:33:17
And they're not really
suitable for supporting
00:33:20
medium or large businesses.
00:33:22
They might be used by very
small businesses just out
00:33:26
of convenience.
00:33:27
But as those companies grow,
they will, at some point,
00:33:31
certainly need to choose a
more robust database solution.
00:33:37
An example of a
personal database system
00:33:39
might be Microsoft Access.
00:33:43
Convenient, easy to use,
but not very powerful.
00:33:48
One of the interesting
characteristics
00:33:50
of personal database
systems is that they often
00:33:54
contain the capability
to build a database
00:33:58
application, or at least
interfaces to the database
00:34:03
within the DBMS.
00:34:06
So in Microsoft
Access, for example,
00:34:09
I can create data
entry forms or generate
00:34:14
reports for my Microsoft
Access database.
00:34:18
That is, it kind of combines
the database application
00:34:23
and the database management
system into a single entity,
00:34:27
conceptually speaking.
00:34:29
On the other hand, we have
a class of database systems
00:34:33
that are called Enterprise-Level
database systems.
00:34:36
These are databases that are
used by medium and large size
00:34:40
companies to support
their operations.
00:34:43
And it is
Enterprise-Level databases
00:34:46
that we will learn to use and
interact with in this class.
00:34:51
Compared to personal
database systems,
00:34:54
Enterprise-Level
databases are designed
00:34:57
to support many different
users at the same time.
00:35:01
And this could be thousands
or tens of thousands of users
00:35:07
all at the same time.
00:35:09
Enterprise-Level
databases also serve data
00:35:12
to more than one application.
00:35:16
For example, if we are
a retailer like Target
00:35:22
or Walmart, we may
have a database
00:35:27
that provides data
to our website
00:35:30
and also provides data
to the dashboards that
00:35:35
are used by our managers.
00:35:38
Enterprise-Level
databases are commonly
00:35:41
spread across multiple
physical computing devices.
00:35:47
So many different computers.
00:35:50
So a single database
might be running
00:35:52
on many physical servers.
00:35:56
Enterprise-Level
databases can also
00:35:59
be geographically distributed.
00:36:02
So I might have
part of my database
00:36:05
in my corporate
offices in Los Angeles,
00:36:08
another part in Beijing, another
part in Berlin in Germany.
00:36:15
And Enterprise-Level
database management systems
00:36:18
support more than one database.
00:36:21
So within the same
database management system,
00:36:25
we might, for example, have
our operational database,
00:36:29
which allows us to
run our business
00:36:32
and keep track of
transactions in real time.
00:36:36
And we might also create a
data warehouse or data marts.
00:36:42
And they can all be managed
by the same Enterprise-Level
00:36:45
database management system.
00:36:47
These are large databases.
00:36:49
As an example, consider
an ERP system like SAP.
00:36:56
A typical ERP
implementation will
00:36:59
have thousands of
tables, all related
00:37:03
to each other in some way.
00:37:06
An Enterprise-Level
database management system
00:37:09
can handle this level of
complexity with relative ease.
00:37:14
So here we see a
graphical example
00:37:16
of these Enterprise-Level
database systems.
00:37:20
The DBMS, again, serves
as an intermediary
00:37:24
or a gatekeeper
between the databases
00:37:27
and all of the various
database applications
00:37:31
that want to rely on the
data in those databases.
00:37:34
And they can be
mobile applications,
00:37:38
applications that are
written in Java, or C#,
00:37:41
or even web applications.
00:37:43
It might be ASP.NET or PHP apps.
00:37:50
Just as examples of some
commercial DBMS products,
00:37:54
again, I mentioned that personal
or desktop DBMS products
00:37:57
might include Microsoft Access.
00:38:01
Whereas,
Enterprise-Level products
00:38:04
include SQL Server,
which is what
00:38:07
we will be using in this
class, Oracle, MySQL, and DB2.
00:38:15
MySQL, if you're interested,
is a completely free and open
00:38:20
source database.
00:38:24
Well, my friends, thus
ends our first lecture
00:38:28
for our database class.
00:38:30
I hope you learned something.
00:38:32
And come back soon, and
we'll begin our exploration
00:38:37
of our next topic.
00:38:39
Until then, have a great day.
