7 hours 36 minutes
I welcome back to Marshall to databases. This is less than to skim a design. And within that lesson we're at sub less than 2.3, the 1 to 1 relationship and the previous lesson we discussed using E. R D database development software to expedite that process. In this lesson, we're going to continue that idea
and use that software. We discussed in the previous lesson to develop two tables that have the 1 to 1 relationship. Now, if we look at this skim of that we have used in the past,
we'll see that we have a one and only 1 to 1 relationship between orders and order, details and products and order details. Again, however, we're going to facilitate a new relationship that I've seen in some databases. We're going to create a column called Zip Code and Employees,
and we're going to tie that to a location table that describes that zip code.
Now with that said, Let's get started. We're gonna need to open up a browser, so you'll need Internet access and we're going to go to the D B designer dot net website and we're gonna go ahead in law again.
So If you do not have a count, go ahead and create one
and you'll be able to use the free tier to complete this assignment or to follow along.
Click New project that's gonna take us into the diagram area.
If you wanted to save your diagram, you just click the skim a button click save.
But we're gonna start our new tables. So we need that employees table.
Let's go ahead and give it a title. Title of employees.
We're gonna add a field. We're gonna call it the first name field. Oh, actually, we need I d feel first. So I d
now. Some people would be tempted to call this employee. I d
Don't do that. The table's already called employees and calling it employees again. Down here is a bit redundant.
So if you're thinking about that, just calling i D. And the full name will be employees. Got I D,
which sounds fine
and looks like
so no size, no default. Value is the primary key.
We're gonna make it auto increments, so it's gonna take care of itself. It's gonna take care of itself and increment up automatically whenever a new records inserted.
Quick save. Then add a field. Recall this one first name
gonna make that of our char
make it a size of 30.
We're gonna allow Knowles, which means it can be empty that I do not have to add it when a new record is at it. Turn off, auto increment. Make sure you get this because that will break the code that you may try to run in my sequel,
Bring in another field called Last name
Bar chart 30. Allow Knowles no auto increment. So these settings are all good.
Then we're going to add a another field that we see a lot SSN
and that empty
We're not going to loan knows it's required.
and then we're gonna add zip code field,
which is an image, er
I'm not going to allow knows
we're not gonna auto increment for sure, because it needs to be an actual zip code.
I don't go and we're done with the employees table, so let's
you can right click on the diagram clique table,
and we're gonna title this one location.
We're gonna add a field. We're gonna call it the I, uh the Actually, we call this
We're going to, um,
make this an imager.
Make it the primary key.
We're gonna turn off auto increment because it needs to be a real zip code.
It needs to be unique.
Well, the primary key will make it unique, so we don't need to check that box.
So look, look safe.
We're gonna add another field. We're gonna call it city name.
This one needs to be a bar chart again.
Size of 30
allowed Knowles on that one, so it can be empty,
and then we're gonna add another one called state code.
Make that a bar chart of TOOTY, too.
Can not be no. So it has to be filled in. Now. Why would I make the city name not required, but the state code required? Well, it's causes sip code is gonna pretty much identify the city.
with just having the zip code, I can find out what city that is.
Um, with the state code, I will need a code to figure out what state it is in.
So that was my thinking when I applied that logic there. Of course, that can vary by business rules,
so make sure you talk to your stakeholders and figure out what they need before making such decisions. In any case, let's add that relationship,
not adding a field. We're editing the ZIP code field.
You say that's a foreign key, which means it's a primary key and another table. And that primary key exists in the location table, with the reference field being the ZIP code update that.
So now in our diagram, we can see
it's clearly indicated that we have two tables. And when those tables reference represents the column
and the other table creating that relationship,
let's go ahead and export that code.
So we'll click the export option like click Sequel.
Make sure that great script has selected generates sequel.
We're gonna download that file
good and save that file.
Let's go to our download section.
View that no pad
and we can see that it's running the create state the great statement for employees, and they create statement for location and then creating the constraint at the bottom. Now, let's quickly look at this a little bit. Could we do this? Could we put this up here?
And the answer is no. You could not cause the tables would not exist yet when you ran it. These happen in sequential order,
so that means it creates the employees table. It creates the location table and then says the employees table is related to the location table via these fields, so those tables have to exist first. Now, the next question is, if you call in a previous lesson, I mentioned that
a lot of developers will not put in the constraints.
And what happens when you do that is that that developer will remember that
that constraint in their head and they will follow it by a convention like that developed will make sure that the ZIP code exists
when he adds it to the employee e table. Now
that's not a good pattern to follow, because as you work with more programmers, it becomes more likely that someone could make a mistake and violate some data rule that you might believe is obvious but was not obvious to that person
in that scenario that they were working in.
without this constraint,
you can create a zip code record in the employees table that does not exist. You could put like negative 20 in there, which is not a zip code. And the database would accept that.
when we had this constraint,
we're saying that this if someone adds a zip code here
and this table it must exist in the location table
now is is, of course possible that someone could put a zip code in the location table that does not exist.
Well, then, then that's, Ah less likely mistake
to occur because somebody would have to make mistake of adding that zip code here and then accidentally using that zip code here.
So let's go ahead and try. And two, Let's go ahead and run this code.
I'm just gonna copy and paste because it's a small code base.
I'm gonna left click on my data basis, right click
and click create new database. I'm just gonna call an example
I'm going to right click This and click set is active.
If you have issues with the active database, just right, click. A different one said it is active and this switch back because when you create a new sequel editor, it is pointing this editor at whatever you're active databases.
And if there's some mistake going on here? Ah, the script may not work correctly,
So I've pasted this script in there, and we put the cursor at the top.
I'm gonna go ahead and execute the sequel script,
and it executed just fine.
I'm gonna look at our tables. Let's go and view the diagram.
there we go. We have the employees table. Oh, education table. And we can see the relationship between them just fine.
So hopefully that worked well for you. I hope you enjoy this lesson. And the next lesson we're gonna go over the one too many relationship and create another set of tables that has that type of relationship. Um, I hope you enjoy this lesson. I hope you got it working, and I will see you there. Thanks. Bye.
Penetration Testing and Ethical Hacking
The Penetration Testing and Ethical Hacking course prepares students for certifications, like CEH. This course ...
7 CEU/CPE Hours Available
Certificate of Completion Offered
This free course introduces the student to intermediate concepts found in the implementation and application ...
10 CEU/CPE Hours Available
Certificate of Completion Offered