Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome the model to databases. This is less than 1.3, the one the one relationship and the previous lesson. We discussed how database tables are similar to spread sheets in concept.
00:11
So
00:13
within that previous example,
00:15
I had a good sample of a 1 to 1 relationship.
00:18
So I opened up the two spreadsheets that
00:21
did a good job of showing that type of relationship. So we have the employee spreadsheet on the state's fresh sheet,
00:27
and if we take a look at the employees spreadsheet, we can see that an employee can have one state
00:32
and that one state will tie to something on the state table. For example, Kit Parker has a state idea of two,
00:40
and two is California,
00:42
while John Smith has a state idea of one and one is Arizona.
00:47
That's a good 1 to 1 relationship.
00:49
Now
00:50
there's a service I use, and unfortunately, it's not a free service, but it doesn't. Excellent job is allowing to diagram databases and then create that DOT database based on that diagram, and that's Verte Bello.
01:02
If you do have a student account, you can sign up for their free student account. Otherwise they have a free trial with a monthly service feet.
01:11
But within Verte Bellow, I have diagrammed these spreadsheets into two tables with that relationship.
01:19
So if I click on the employees table, we'll see the column. Names of I. D first name, last name State just like that occurred on the spreadsheet.
01:27
In addition to that, we'll see how those fields are defined with respect to data type.
01:33
For example, the I D is an ent or energy, which means it must be a number
01:38
first name and last name or both. Defined is Var char 30 which means it could be text up to 30 characters long.
01:47
And we can see that state is defined as an imager, which is a clue that there's probably a state table
01:53
if you didn't happen to have the diagram in front of you.
01:57
In addition, we could see the idea is also marked. Is the primary Key or P K for short?
02:02
Now again, if you click on the state table, we'll see the name of state.
02:07
We'll see the columns with their names of I D name code
02:10
with their types,
02:13
with idea being that of an ent or energy er and mark with P k or Primary key
02:17
name Having a bar chart 30 which again means text that could be 30 characters long
02:23
and a column of code, which is a bar chart, too, which means it's text that could be two characters long.
02:30
And this line in the center represents the relationship.
02:34
And if you click on that, we'll see that we have a carnality of 1 to 1. We could change that in. The diagram would change to reflect what we pick.
02:44
So that's what the one too many
02:46
relationship looks like. But we're gonna change it back to 1 to 1, because that's over working with. That's what we're talking about.
02:52
And again we could see that we define the primary
02:55
from the state
02:58
I D.
03:00
And the foreign is on the employees
03:05
state,
03:06
and it's mandatory,
03:07
which means that you can't insert data that violates this relationship.
03:12
So the nice thing about Verte Bello is we can actually click the sequel button.
03:17
We can click the generate button. After that
03:21
click, save or download,
03:23
we can click open.
03:27
Now we're gonna get rid of this bottom. This is noise. If you were using this, you won't see this. This is from something I was doing earlier.
03:37
So the nice thing about this is we can actually take this and copy it
03:40
into D Beaver.
03:43
But first
03:45
we need to open D beaver, and you should see the connection that you established in a previous lesson.
03:51
Expand the databases. We're gonna make a new database.
03:53
I'm gonna create a new database. We're just gonna give it a simple name like test, but you can call it whatever you like.
04:00
And we're going to right? Click that new database. We're gonna set it as the active database.
04:06
Then we're going to click the sequel editor
04:10
and Click New sequel editor.
04:12
We're gonna pace that code in there.
04:15
We're gonna move the cursor to the first statement because D Beaver will attempt to run
04:19
the query based on where your cursor is.
04:23
So we've taken that Verbillo code, which generates the diagram we just defined.
04:30
And to run that whole statement in De Beber, you need to select the button that says Execute sequel script.
04:36
Have you tried to click just the play button? It will only execute your top statement.
04:42
So let's go ahead and execute the entire sequel script,
04:46
and we're seeing that it did three Curries queries. Which makes sense because one,
04:51
two,
04:53
three
04:55
and we could see the If we take a quick look at the create statement, we'll see what it did was say that it created a table named employees.
05:01
We can see that there was, for example, an i. D.
05:05
There was an imager rent, and it's not allowed to be no, which means it has to be filled in.
05:12
Well, let's not worry about that in too much detail right now. Let's see what was created
05:17
on their way there. We have our two tables, employees and the state.
05:23
And if we clicked on employees and we clicked,
05:26
view Diagram
05:28
de Beaver will show us that relationship we just defined,
05:32
which is, Ah, very neat feature. So if you're working with my single database that has had the four keys to find, you can very quickly
05:41
look at the tables and see the type of relationships you are working with, so you can better query that database. Now. It is common,
05:48
more common than you'd probably like to think for
05:53
designers to not define the relationship
05:56
and what that means is they didn't run this.
05:59
They didn't. They didn't add the foreign key.
06:02
And then what they do is buy convention. They respect that relationship,
06:08
and the issue there is that it's not clearly defined what the relationship is. You kind of have to assume what it is and do a few checks to check your assumption. And also it can lead to some weird data entries that don't follow the relationship that someone might have actually left in the database as noise.
06:29
So this has been the 1 to 1 relationship, and the next tutorial will go over the one too many relationship.
06:34
And then after that, we'll go through a couple more relationship types, and then we'll tie it all together.
06:41
I hope you enjoy this lesson, and I hope to see you in the next lesson. Thank you for your time.

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