7 hours 36 minutes
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
following the previous lesson of a 1 to 1 relationship that we indicated between the employees and the location table.
So let's go ahead and get started.
We're gonna right click on the diagramming area and select table.
I'm to go ahead and drag this table out. Select the pencil and paper. I'm gonna give it a name of states
I'm gonna add a field. We're gonna call it state code.
It's a bard shar.
It's a size of two,
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.
I'm a click save.
So what makes sure that there's only one record
or one state in the state code are in the state table? So
what stops me from entering the state code ese twice?
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
and that it would be indexed
those together will make sure
we don't get multiple entries with the same state code.
And that makes sure that there's only one record over here.
So we're gonna go ahead and add another field.
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.
We're gonna allow Knowles so they don't have to fill it in.
Well, add another field. We're gonna call this the capital.
It's gonna be an inner jer.
Oh, not a float and manager.
Get rid of the size.
We're gonna allow Knowles again.
I'm gonna save,
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
check box, and we're going to select a reference table of States,
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
a foreign key again indicates that that column is a primary key and another table,
whatever is entered into this column must exist in that table.
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
and what possibly went wrong with his logic when trying to add a state that didn't exist.
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.
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
and zip code will be the reference field.
So if somebody adds a state and indicates a capital,
that location must exist in this location table,
so we can have multiple ZIP codes that have the same state code,
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,
so I'm gonna select the my sequel. Create statement, generate the sequel.
Download the file, Save the file.
And again, we're dealing with a pretty small file for right now. So I'm just gonna go ahead and open it,
and I'm going to get de Beaver open as well.
And that shouldn't take too long at all. We can see the code that's going to run.
You see the three tables that are gonna get generated.
I wanna see the constraints that are added
that make sure the data exists
in a pattern that we
we, uh, indicated.
Well, this seems to be taking a second. Usually doesn't take that long.
Let's see them get a bar
loading workbench. Okay, there we go. And we are open.
So would expand my connection.
Gonna close this.
I will worry about updating later. When expand databases,
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.
Someone, uh, right. Click databases
click, create new database and type in example. Again,
I'm gonna right click. Example. Set is active
sequel editor, new sequel editor,
and I'm going to just copy and paste this code in
gonna put my curse at the top
and I'm gonna go ahead and run it.
It looks like it all ran just fine. I'm gonna go ahead and take a look at the zip code
or the location.
I'm gonna view the diagram
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.