2.12 ERD One to Many Relationships

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
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3
Video Transcription
00:00
All right. Welcome back to model to databases. This is lesson to skim design. Sub lesson 2.4 The one too many relationship. We're gonna implement a one too many relationship
00:10
following the previous lesson of a 1 to 1 relationship that we indicated between the employees and the location table.
00:18
So let's go ahead and get started.
00:21
We're gonna right click on the diagramming area and select table.
00:26
I'm to go ahead and drag this table out. Select the pencil and paper. I'm gonna give it a name of states
00:32
Click save.
00:34
I'm gonna add a field. We're gonna call it state code.
00:39
It's a bard shar.
00:43
It's a size of two,
00:45
and it's a primary key. I'm gonna be sure to de select the auto increment because that will cause this software to generate code that will not run.
00:54
I'm a click save.
00:56
So what makes sure that there's only one record
01:00
or one state in the state code are in the state table? So
01:03
what stops me from entering the state code ese twice?
01:08
Well, it's the fact that we made the state code a primary key when we indicated that this is a primary key We also indicated that it would be unique
01:18
and that it would be indexed
01:19
and that
01:21
those together will make sure
01:23
that
01:26
we don't get multiple entries with the same state code.
01:30
And that makes sure that there's only one record over here.
01:34
So we're gonna go ahead and add another field.
01:38
We're gonna call this one name, so it'll be like the full state name. We're gonna give that a size of 30.
01:44
We're gonna allow Knowles so they don't have to fill it in.
01:49
Well, add another field. We're gonna call this the capital.
01:53
It's gonna be an inner jer.
01:57
Oh, not a float and manager.
02:00
Get rid of the size.
02:01
We're gonna allow Knowles again.
02:05
I'm gonna save,
02:06
and now we're gonna go back to the location to table. We're gonna click the pencil next to the state code. We're going to select the foreign key
02:14
check box, and we're going to select a reference table of States,
02:17
and it's gonna correctly indicate that the reference field is state code. So when we select foreign key here were indicating that this field is a primary key in another table. So
02:31
a foreign key again indicates that that column is a primary key and another table,
02:38
and that
02:38
this
02:39
whatever is entered into this column must exist in that table.
02:44
If it does not in a programmer tries to insert data into the location table. For example, for a state that does not exist, the database will throw in air That will cause the programmer to have to evaluate what he's doing
02:58
and what possibly went wrong with his logic when trying to add a state that didn't exist.
03:07
So on the state's code, we're gonna create a reference from capital to zip code. So let's go ahead and select capital.
03:15
And we are going to say that it is a foreign key, which again means that it's a primary key in another table. So I'm gonna select location
03:23
and zip code will be the reference field.
03:27
So if somebody adds a state and indicates a capital,
03:30
that location must exist in this location table,
03:38
so we can have multiple ZIP codes that have the same state code,
03:42
which creates a one too many between the states and location code. So let's go ahead and explored that code and see if we can't run it,
03:50
so I'm gonna select the my sequel. Create statement, generate the sequel.
03:53
Download the file, Save the file.
03:58
And again, we're dealing with a pretty small file for right now. So I'm just gonna go ahead and open it,
04:03
and I'm going to get de Beaver open as well.
04:11
And that shouldn't take too long at all. We can see the code that's going to run.
04:15
You see the three tables that are gonna get generated.
04:18
I wanna see the constraints that are added
04:21
that make sure the data exists
04:24
in a pattern that we
04:26
we, uh, indicated.
04:29
Well, this seems to be taking a second. Usually doesn't take that long.
04:36
Let's see them get a bar
04:40
loading workbench. Okay, there we go. And we are open.
04:44
So would expand my connection.
04:46
Gonna close this.
04:48
I will worry about updating later. When expand databases,
04:53
I'm going to select my example. I'm gonna go ahead and delete that because I'm just gonna rerun the code to recreate it.
05:00
Someone, uh, right. Click databases
05:02
click, create new database and type in example. Again,
05:08
I'm gonna right click. Example. Set is active
05:12
sequel editor, new sequel editor,
05:15
and I'm going to just copy and paste this code in
05:19
gonna put my curse at the top
05:20
and I'm gonna go ahead and run it.
05:25
It looks like it all ran just fine. I'm gonna go ahead and take a look at the zip code
05:30
or the location.
05:33
I'm gonna view the diagram
05:38
and we can see that there are two references between the location and the state's table, just as we expected. So this completes this lesson? I hope you enjoyed it. I hope you got that working in the next lesson. We're going to discuss the many too many relationship and how to diagram and implement that.
Up Next