Relational Databases

Video Activity
Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or

Already have an account? Sign In »

Time
15 hours 43 minutes
Difficulty
Advanced
CEU/CPE
16
Video Transcription
00:00
>> Now our next section is on Relational Databases.
00:00
I think this is one of
00:00
the more testable sections in
00:00
the software development security domain,
00:00
but they don't get particularly deep.
00:00
They don't expect anybody to be a database administrator.
00:00
But what they do expect you is to have
00:00
a basic understanding of what relational databases are,
00:00
what makes them relational,
00:00
and how the data is stored and retrieved.
00:00
Let's start out by just talking about
00:00
some of the key elements of this database.
00:00
Again, it's more at the level of somebody that
00:00
would use access as opposed to being a SQL developer.
00:00
All right, so what is a relational database?
00:00
Well, the basic unit of
00:00
a relational database is the table.
00:00
Your tables are made up of rows and
00:00
columns just like Excel, for instance.
00:00
Where the rows meet the columns as
00:00
your data and your information is stored in those,
00:00
those are referred to as cells,
00:00
and your information is stored in the cells.
00:00
Now we want to make sure that I can
00:00
not store data but also retrieve it.
00:00
It's funny, I have here reference
00:00
the database engine from Oracle and Sybase.
00:00
I can't believe, it's so dated,
00:00
that's gone for a little bit.
00:00
But let's focus in on what makes a database.
00:00
Let's say I work for Netflix,
00:00
and we have a list of customers,
00:00
we have a list of movies.
00:00
I can tell you my age because I factored in my table,
00:00
I call them videos.
00:00
I'm still back in the days of VHS,
00:00
but just humor me.
00:00
Also, we have customers,
00:00
we have videos,
00:00
and then what I want to know is what customer ordered,
00:00
what video or movie, when.
00:00
We start out with a table,
00:00
and the table is for customers.
00:00
One of the rules of relational databases
00:00
is that each table should be unique and specific.
00:00
For instance, when I have a customer table,
00:00
all the fields in that table
00:00
specifically describe the customer.
00:00
I don't have any information about what movie they
00:00
ordered or when they came in the store,
00:00
this table is just about customers.
00:00
Customer name and address,
00:00
phone number, and we could go on and on.
00:00
We could collect a lot of information about our customer.
00:00
Another rule though in relational databases,
00:00
is each table needs one field that will
00:00
uniquely identify each record from all the others.
00:00
I need one field in my customer table that will
00:00
identify every single customer from any other customer.
00:00
Now name can't work because
00:00
we might have multiple John Smiths,
00:00
phone number can't work
00:00
because we might have two people in
00:00
the same family using the same phone as customers.
00:00
It would almost feel like
00:00
Social Security number would work,
00:00
but not all my customers
00:00
will have a Social Security number.
00:00
That's another rule of
00:00
database design is that
00:00
the primary key field can't be null.
00:00
I need an entry there.
00:00
What we finally decide to do
00:00
this bottom field customer ID,
00:00
that's just the easy way to address
00:00
unique information for each customer
00:00
given my customer ID field,
00:00
where every customer has a unique ID.
00:00
That way when I type out customer 57,
00:00
that will only bring up that one specific customer.
00:00
Now, each one of these values, name,
00:00
address, phone number,
00:00
these are usually arranged in columns.
00:00
If you think of an Excel spreadsheet where
00:00
your column 1 is name,
00:00
and column 2 is address,
00:00
and column 3 is phone number,
00:00
those are often referred to as fields.
00:00
The name we want to use here is attributes.
00:00
Your fields, we're going to refer to those as attributes.
00:00
Now, all the attributes about
00:00
a single entity comes together to be a record.
00:00
Kelly, hand-to-hand at 101 Walker way,
00:00
555 1212, all of that information is a record.
00:00
All of those attributes that describe one entity.
00:00
Instead of calling it a record,
00:00
and we know that those are usually arranged in rows,
00:00
we're going to refer to
00:00
the information in the rows as being a tuple.
00:00
That's the phrase we want to use.
00:00
All right, now, I just
00:00
want to catch us up on a couple of
00:00
definitions down below.
00:00
When I talked about there needing to be a field that
00:00
uniquely identify each record as unique,
00:00
that field is referred to as the primary key.
00:00
In our illustration, the primary key is of course,
00:00
the customer ID for the customer table.
00:00
Now, we said Social Security number might be used
00:00
except the chances are we'll have
00:00
a customer without a Social Security number.
00:00
It's entity integrity that says that
00:00
primary field key can't be null.
00:00
We need a valid value
00:00
in our primary key field for every customer.
00:00
We also said every attribute in
00:00
a table should describe
00:00
that table or the primary key for that table.
00:00
For instance, name, address, phone number,
00:00
all of that specifically describes customer 1, 2, 3.
00:00
Customer 1, 2, 3, 4 has the same descriptors.
00:00
That's called normalization.
00:00
When you design databases,
00:00
there are different levels of normalization,
00:00
but we keep it at the upper level for this exam.
00:00
Normalization means each field
00:00
in your table describes the primary key.
00:00
There are also techniques for
00:00
removing duplicates in your table as well,
00:00
that's all part of normalization.
00:00
Now we talked about attributes,
00:00
and we talked about tuples.
00:00
Now, I want to mention the next thing we need to do
00:00
is create a table for our videos or movies.
00:00
In that table, I might have fields,
00:00
title, starring, genre.
00:00
Remember, normalization says just to describe the videos.
00:00
My primary key in
00:00
the videos table is going to be a video ID number.
00:00
I can make sure that that is unique.
00:00
The problem with relational databases
00:00
is normally that they
00:00
only want to pull information from a single table.
00:00
To gather information from across tables,
00:00
we try to find a link,
00:00
a way to relate those tables.
00:00
What I have, this as one of the things
00:00
with student access is we would
00:00
build a third table that acted like a bridge.
00:00
I have the orders table.
00:00
In that table I might have order date and order ID.
00:00
What I do to create the link is I put the video ID for
00:00
the video table and the customer ID from
00:00
the customer table into the orders table.
00:00
Anytime the primary key from
00:00
one table appears in a secondary table,
00:00
we refer to it as a foreign key.
00:00
In the orders table, video ID and
00:00
customer ID are foreign keys.
00:00
I can create my link based on those keys.
00:00
Now, if I were to ask you how many times customer 1,
00:00
2, 3 should appear in the customer table?
00:00
There should only be one customer 1, 2, 3.
00:00
But how many times can customer 1, 2,
00:00
3 appear in the orders table?
00:00
Many times, customer 1, 2,
00:00
3 can have a lot of orders.
00:00
That's what's referred to as a one-to-many relationship.
00:00
That describes the cardinality of the database,
00:00
the number of rows in a relation.
00:00
In the customer table it will appear once,
00:00
many times in its secondary table.
00:00
You can have a one-to-one relationship,
00:00
you could have a many-to-many.
00:00
Most common is one-to-many.
00:00
Now if the database was arranged a little differently,
00:00
almost like flipped on its side,
00:00
then we would talk about the degree,
00:00
which would be the number of columns in a relation.
00:00
Then everything I have in
00:00
my illustration, the attributes,
00:00
the tuples, how they're related,
00:00
what the primary keys,
00:00
with the foreign keys,
00:00
the cardinality of the database.
00:00
All of that is part of the database's schema.
00:00
The schema defines the database,
00:00
number of tables, fields within the tables,
00:00
key fields, information, any keys that are there.
00:00
All of that information
00:00
comes under the heading of the schema.
00:00
If you want to modify the structure of the database,
00:00
you're modifying the schema.
00:00
In this video, we covered
00:00
the schema and
00:00
the various elements of relational databases.
00:00
I would know every one of those terms that we
00:00
used from schema all the way
00:00
down to attributes and tuples.
00:00
They tend to ask a couple of questions here and there.
00:00
Just about database design and
00:00
basic relationship ideas like primary and foreign key.
00:00
Make sure you go back and review that in your solid.
00:00
Again, nothing deep on databases and those of
00:00
you that are database designers
00:00
may go wow, that was simple.
00:00
Good, I hope so,
00:00
because we just want to stay
00:00
>> at that level for the exam.
Up Next