Time
1 hour 57 minutes
Difficulty
Intermediate
CEU/CPE
4

Video Description

In this final section of Module 08, we examine relational databases. As touched on in the previous section, this database model is based on tables which are comprised of rows and columns. In turn, tables in the relational model are related to one another via columns that they have in common. This allows for pulling information from many combined tables. We discuss tables and liken them to a storage shelf where like items are grouped. An example is a customer table that holds information associated with customer accounts. The principle of normalization is then discussed. This process assures that there are no duplicates in the table. We also introduce the primary key which uniquely identifies every record in a table. It's noted that good database design utilizes the principle of normalization along with entity integrity which dictates that the primary can never be null (empty). We conclude with a discussion of how a key from one table is linked to one in another table. This is the feature that puts the "relational" in the relational model. The principles of a one-to-many relationship, cardinality, degree, tuples, and database schema and design conclude the video.

Video Transcription

00:04
all right, so let's go ahead and place our focus on relation all databases. And as we've said, many of the major database products out there are using relational databases. So the basic unit is the table, and then we create multiple tables and relate those tables
00:24
in order to pull information across
00:26
many tables as opposed to one flat table. Hopefully, this Mikel better sense in a minute. All right, so let's talk about a relational database and its tables. So a table is kind of like a storage shelf in a warehouse. You're gonna keep those like items together.
00:43
So what I've got here is I've got an illustration or a snapshot of a customer table.
00:49
So this is a little bit dated oven example. I've actually been using the same example since I taught access 95. So
00:58
I've been using this example for a while, and it's about a video store. And yes, I do realise video stores are rapidly becoming obsolete. But let me tell you this. I went to Fairbanks, Alaska, last year,
01:11
and I got off. The plane was driving to my my hotel, and by golly, there was a blockbuster video
01:19
in Fairbanks, Alaska. And let me tell you, it was hopping. So until the last video store has been removed from this planet, I'm going to continue my example. All right, So I'm the owner of the video store. I want to keep track of who my customers are. I want to keep track of what videos I have.
01:38
And then ultimately, I want to find out what customer
01:42
ordered. What video? All right, so the first thing I'm gonna do is create a customer table and then list all the fields on the track about my customers. Now, there's something called, uh, entity integrity.
01:57
Uh, that, uh I'm sorry, not entity integrity. It's called
02:04
normalization. Just totally drive like they're It's called normalization. What normalization? Part of what it means is we're gonna make sure we don't have any duplicates. But we're also going to make sure that every field in the table describes what's called the primary key.
02:23
And the primary key is the one field in that table that is gonna uniquely identify
02:29
every record from every other record.
02:31
All right, so first piece, what's our primary key? You can see the Wilkie drawn beside customer I d.
02:38
Well, why couldn't we use Social Security number as a primary key or credit card?
02:44
Well, because all of our customers may not have credit card numbers. All of our customers may not have Social Security numbers, and it's called entity integrity. That says your primary key field can't be no. Okay, so a couple of definitions there, let me hit him again.
03:00
Your primary key is your unique Identify her for every record in your tape.
03:06
Entity integrity says the primary key can't be no worse for, uh, empty. And then normalisation says we're gonna keep the principles of good database design. We're gonna get rid of duplicates. And we're also going to make sure that
03:23
every field describes the primary key,
03:27
so you'll notice in my table. There's nothing about what videos thes folks ordered what data which employees helped him. It's strictly about the customers. I will tell you that if you get a little deeper into database design, which we will not do their different levels of normalization. So this is definitely simplification,
03:45
but this is good for what we need.
03:46
All right, So I have my customer table. I've selected my primary key. Now I have my videotape, and once again I use video I d. As a primary key. And I've got the various fields and, you know, we could get much more elaborate with these fields as we wanted to.
04:01
But the problem with the relational databases a relational databases just looking at one table at a time. So if I want to find out John Smith's address, I'm good. That's from the customer table
04:13
or what? The rating is a Star Wars. That's fine. I'm in the video table, but I want to do. But what I want to do is I want to find out what movie John Smith has ordered.
04:25
So, in that instance, what I'm gonna do is I'm gonna bring in 1/3 table,
04:30
and in this third table, I'm going to have it. I'm gonna call it Borders and I'll have an order I d. Because that table needs a primary key.
04:41
Maybe order date, But notice. I'm bringing in the primary key from each of the other two tables, custom writing and video D i. D.
04:50
Because their primary keys in other tables there now refer to us foreign keys in the orders table and basically when I link with my relational databases I link based on keys, so I like one key to another
05:06
now, as you can see, once I've created my mind linking, I can pull any information from any of the table so I can find out what movie John Smith ordered on What date? Okay, so that's that's kind of an overview gives you the idea of relational database also gives you some important terms,
05:26
right? Mean a primary key. We know
05:29
foreign key. We know ideas about linking. Let me also mention
05:36
if you'll look and you look a notice the links between the tables, you see the symbol for one,
05:44
and this is the symbol for infinity that indicates a one too many relationships.
05:50
And this describes the databases card analogy.
05:55
Carnality is the number of rows in a relation
06:00
the number of rows in a relation. So the idea is the customer. I d. 123 should appear one time in the customer table. The customer, 123 can order many videos, so that's a one too many relationship, and that's described as the databases card in ality. If the database were arranged a little differently,
06:19
you would look at the number of columns in a relation,
06:25
and that would be the degree
06:27
it's a number of rows in a relation is card in ality. Number of columns in a relation is degree now. Also, mention each field in my tables. Customer I D. Because we're name Social Security number. These air usually arranged in columns in the database. You know, if you think a name,
06:46
date, city, state, zip code, whatever those would be arranged, usually in columns. These air referred to his attributes in a relation all database and then the collection of attributes of have a single entity that's referred to or that's usually arranged in a row. We frequently call it a record,
07:05
but I think what they'll use on the testes they'll use the term
07:09
to pull.
07:10
So the collection of attributes about a single instance
07:14
that's a two people.
07:15
All right, so lots of definitions. And I've gotten those definitions for you on the next page, just ah, list of those things that you need to know, and you may want to go back and kind of fill in the gaps on make sure, maybe re watch the last couple of slides because a lot of definitions
07:34
and I think I'm the exam. Some of the database questions you're going to see
07:38
are gonna be just based on definitions. So I would be solid with everything here. And by the way, when we go back and we look at the data base, this is part of the database schema. It indicates which tables, What field? What type of carnality. So the overall structure and design of a database
07:57
isn't schema.

Up Next

Software Development Security

Domain 8 covers understanding, applying, and enforcing software security

Instructed By

Instructor Profile Image
Kelly Handerhan
Senior Instructor