Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
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
00:17
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,
00:28
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,
00:46
and we're going to tie that to a location table that describes that zip code.
00:51
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.
01:03
So If you do not have a count, go ahead and create one
01:07
and you'll be able to use the free tier to complete this assignment or to follow along.
01:15
Click New project that's gonna take us into the diagram area.
01:19
If you wanted to save your diagram, you just click the skim a button click save.
01:25
But we're gonna start our new tables. So we need that employees table.
01:29
Let's go ahead and give it a title. Title of employees.
01:33
Quick save.
01:34
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
01:42
manager
01:45
now. Some people would be tempted to call this employee. I d
01:48
Don't do that. The table's already called employees and calling it employees again. Down here is a bit redundant.
01:56
So if you're thinking about that, just calling i D. And the full name will be employees. Got I D,
02:02
which sounds fine
02:05
and looks like
02:07
so no size, no default. Value is the primary key.
02:12
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.
02:23
Quick save. Then add a field. Recall this one first name
02:28
gonna make that of our char
02:30
make it a size of 30.
02:31
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,
02:46
Bring in another field called Last name
02:50
Bar chart 30. Allow Knowles no auto increment. So these settings are all good.
02:57
Then we're going to add a another field that we see a lot SSN
03:04
manager
03:05
and that empty
03:07
We're not going to loan knows it's required.
03:12
Yeah,
03:14
and then we're gonna add zip code field,
03:16
which is an image, er
03:19
I'm not going to allow knows
03:22
we're not gonna auto increment for sure, because it needs to be an actual zip code.
03:25
I don't go and we're done with the employees table, so let's
03:30
you can right click on the diagram clique table,
03:34
and we're gonna title this one location.
03:38
We're gonna add a field. We're gonna call it the I, uh the Actually, we call this
03:44
zip code.
03:49
We're going to, um,
03:51
make this an imager.
03:53
Make it the primary key.
03:54
We're gonna turn off auto increment because it needs to be a real zip code.
03:59
It needs to be unique.
04:05
Well, the primary key will make it unique, so we don't need to check that box.
04:09
So look, look safe.
04:12
We're gonna add another field. We're gonna call it city name.
04:15
This one needs to be a bar chart again.
04:18
Size of 30
04:20
allowed Knowles on that one, so it can be empty,
04:27
and then we're gonna add another one called state code.
04:30
Make that a bar chart of TOOTY, too.
04:32
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.
04:44
So,
04:46
with just having the zip code, I can find out what city that is.
04:49
Um, with the state code, I will need a code to figure out what state it is in.
04:55
So that was my thinking when I applied that logic there. Of course, that can vary by business rules,
05:00
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,
05:10
not adding a field. We're editing the ZIP code field.
05:14
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.
05:25
So now in our diagram, we can see
05:27
it's clearly indicated that we have two tables. And when those tables reference represents the column
05:34
and the other table creating that relationship,
05:38
let's go ahead and export that code.
05:40
So we'll click the export option like click Sequel.
05:45
Make sure that great script has selected generates sequel.
05:47
We're gonna download that file
05:50
good and save that file.
05:51
Let's go to our download section.
05:56
That's it.
05:57
View that no pad
05:59
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?
06:16
And the answer is no. You could not cause the tables would not exist yet when you ran it. These happen in sequential order,
06:24
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
06:41
a lot of developers will not put in the constraints.
06:44
And what happens when you do that is that that developer will remember that
06:48
that constraint in their head and they will follow it by a convention like that developed will make sure that the ZIP code exists
06:58
when he adds it to the employee e table. Now
07:01
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
07:17
in that scenario that they were working in.
07:19
For example,
07:21
without this constraint,
07:24
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.
07:35
However,
07:38
when we had this constraint,
07:39
we're saying that this if someone adds a zip code here
07:43
and this table it must exist in the location table
07:50
now is is, of course possible that someone could put a zip code in the location table that does not exist.
07:56
Well, then, then that's, Ah less likely mistake
07:59
to occur because somebody would have to make mistake of adding that zip code here and then accidentally using that zip code here.
08:07
So let's go ahead and try. And two, Let's go ahead and run this code.
08:11
I'm just gonna copy and paste because it's a small code base.
08:16
I'm gonna left click on my data basis, right click
08:18
and click create new database. I'm just gonna call an example
08:22
it okay,
08:24
I'm going to right click This and click set is active.
08:28
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.
08:41
And if there's some mistake going on here? Ah, the script may not work correctly,
08:46
So I've pasted this script in there, and we put the cursor at the top.
08:50
I'm gonna go ahead and execute the sequel script,
08:54
and it executed just fine.
08:56
I'm gonna look at our tables. Let's go and view the diagram.
09:03
Okay,
09:05
there we go. We have the employees table. Oh, education table. And we can see the relationship between them just fine.
09:11
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.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor