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 »
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
Instructed By
Similar Content
