Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I will come back to module to databases. This is lesson to skim a design. And this is the last sub lesson in skim A design which is defining the many too many relationship using the diagramming software. So in this lesson, we will discuss so many too many relations relationship. And in the previous lessons we had looked at implementing a
00:20
1 to 1 and a one too many,
00:22
and this lesson, we're gonna pretend that employees can have multiple jobs and that a job can be assigned multiple employees.
00:33
So let's go ahead and get started with that. So I'm gonna right click on the software. The diagram software area. A select table.
00:41
We're going to drag this back down on the first table I'm going to create is the job table.
00:47
So I'm just gonna call it jobs.
00:50
Quick, save
00:52
one at a field
00:54
comes good. Call it. I d
00:59
I'm gonna make that an imager. Gonna say that's a primary key and in increments.
01:04
And then I'm gonna add another field. I'm gonna call it a job. Job name
01:12
Gonna make that of our char
01:15
in a size of 30
01:19
on. We're going to save. We're gonna turn off auto increment. That won't work for us.
01:26
And, uh,
01:27
next I'm gonna add a field, and we're just going to, ah,
01:33
call it building.
01:34
So the building that the job occurs in
01:41
and we don't leave that as 34 Bar char again.
01:46
So save
01:47
so and realistically on these tables, you probably have many more fields,
01:52
and we'll get into some of those fields further on in this Siri's. But for now, we're just gonna have a very simple job stable.
02:01
Now we need to add our pivot table.
02:05
Now, remember, the pivot table is a combination of
02:09
to one too many relationships that, when combined, create the many too many relationship.
02:16
So let's go then, right click again in the area, Select table,
02:23
and we're gonna title this one.
02:25
I am underscored jobs.
02:30
We're gonna save that.
02:31
We're gonna add a field.
02:34
Mmm.
02:35
We're going to call this field. The
02:38
employee I D or amp. Underscore idea
02:43
were indicate that that is an imager,
02:47
and we're going to leave that alone right now. And we're gonna add another field.
02:54
We call this one the job I d
02:57
job underscore. I d.
02:59
I can make it an energy er
03:01
and save that
03:05
and then on the impiety, we're going to
03:08
indicate that this is a foreign key
03:12
and it's on the employees table
03:15
with a reference field of I D, which is correct. So we're not going an upgrade. Update that,
03:21
And then we're gonna select the pencil for the job i d.
03:24
And we're going to say that this is a flooring key
03:29
and that is a
03:32
is the jobs table.
03:35
And again, the reference field. His idea. We'll update that.
03:39
And there we have it. We have our pivot table,
03:43
so we have the, uh,
03:45
employees
03:46
table being referenced by the amp. Underscored jobs
03:51
with an EMP i D. And a job idea.
03:53
So, for example, in this table, we could say that we had a job of one that was maybe, I don't know, programmer.
04:00
And in this employees table, we had an employee with the idea of
04:05
to, which was, say is me Maybe Kit Parker so and the pivot table. We might see the EMP i d. Of two with a job idea of one, and we would know that followed by following this relationship that that was Kim Parker and he works as a programmer.
04:20
And then again, we could have another entry in there where I mentioned again
04:26
with a different job, I d, which would then indicate that I had multiple jobs on then. Also, that programmer job could be assigned to multiple employees because it is common for companies to have,
04:39
you know, many programmers working for them.
04:42
And that completes this lesson. That was the many too many relationship. I hope you got that working. I hope you see how that works. And in the next lesson, three will be going over the basic administration off
04:54
databases, and I hope to see you there. Thank you.

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