Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome to less than 1.8 the many too many relationship explored and the previous lesson we stole the database that was required to display the relationship. And then the lesson before that. And 1.6, I believe we described the relationship in detail, using some
00:16
suitable database tables represented in spreadsheets. Now let's go ahead and open D beaver and take a look at the data base we installed in the previous lesson.
00:30
I'm gonna go ahead and bring up the schematic by searching for it again.
00:34
So let's see. It was my sequel
00:38
sample database employees.
00:43
So it's the top hit here at the dev dot my simple dot com domain,
00:49
and we're gonna look at the employee structure.
00:53
I'm go ahead and take a quick snip of that
00:57
so that I can draw on it a little bit.
01:03
So if we look at the structure here, this is a many too many relationship facilitated by this table in the center here,
01:12
cause we if we were to break this apart, we would see that this relationship
01:18
that isn't many too many between the departments
01:21
and employees
01:23
is actually a composition of a one, too many
01:27
two from the departments to the department underscore employees table
01:34
on the left side. In this picture
01:37
and on the right side,
01:38
the other side of that composition is of one too many from the employees to the department. Underscore
01:45
EMP table and together that creates the many too many relationship,
01:49
and this table in the center is often called
01:53
a pivot table or a lookup table.
01:56
So let's go ahead and bring that up in our database of open D beaver.
02:01
Expand the connection we established on a previous lesson.
02:05
Expand the databases,
02:07
expand the employees database, expand the tables
02:13
and then click on the department. Underscore EMP table right click that
02:19
and click view diagram.
02:22
When you view a diagram in a one in a mini too many relationships,
02:27
you usually want to view the diagram of the table in the center because that will give you the entire picture.
02:34
If we had viewed the diagram of the department's, we would not have gotten this this part of the schematic that we're interested in
02:43
so we could see the employees connects here to the department employees
02:47
table and back next to the department stable, just like
02:52
we saw on the schematic.
02:54
So let's go ahead and connect that up and start writing the sequel that will carry that out.
03:00
Before we do that.
03:02
Click on your employees database right click it
03:07
and set it as your active database so that the intelligence works correctly.
03:12
Then click Sequel editor, new sequel editor.
03:16
So I'm gonna start with my selection. Now what am I going to select again? I don't know. So I'm just gonna jump down to where I'm going to select from Enlist my tables.
03:28
I know I need the employees table.
03:31
I need the in fact, let's give that an alias that makes it shorter. And
03:38
and I know I need the ah
03:42
department
03:44
underscore EMP table And let's call that
03:46
D E.
03:50
And I also know I need the department's table,
03:53
and we'll just call that
03:55
the E p T.
03:59
Now, without any relationship constraints, we would have a Cartesian product. We don't want that.
04:04
Let's go ahead and add in the constraints where emp dot
04:11
ah hem number
04:14
equals d e
04:17
dot m number.
04:19
So we've connected the employees table
04:24
to the department empty table
04:27
through the employee number.
04:30
So let's add in the
04:32
remaining connection.
04:33
So where D e dot
04:36
department number
04:39
equals
04:40
the e p t
04:42
dot
04:44
department number?
04:45
So we've added in the relationship of the pivot table in the center to the apartment number
04:51
to the I'm sorry to the department table. So let's go ahead and select the Fields were interested in.
04:59
So let's go m dot
05:01
first name
05:03
on Let's do, um, D p t dot
05:08
department name
05:11
and let's go ahead and put in the, ah, the numbers for each
05:15
object. So imp
05:16
that number.
05:20
And in between here, let's put in the department that
05:27
apartment number.
05:30
Let's go ahead and select out right now.
05:32
No
05:34
notification.
05:35
Get rid of that.
05:38
Run that,
05:40
and we could see a bunch of results that we're getting back.
05:44
But is anybody in multiple departments? Well, it's really difficult to tell here, and there's tons of results and it be bad practice to try and look at all those results to try and make a determination on the data structure. So let's
05:59
let's do it the right way. Let's add a count variable
06:01
after a group,
06:03
because if anybody is in multiple departments
06:06
after we group them, they should show up with a count greater than one.
06:13
So we're going to group by
06:16
and we're gonna go ahead and get rid of the department part of it,
06:20
because the department would be unique, which would cause the group by statement to fail by making every return result a one.
06:30
So when we get rid of that and we grew by
06:33
employee number and the play first name, if anyone occurs more than once, we will see that reflected in account.
06:41
So let's go ahead and put the count variable there
06:46
and see if we get
06:46
a anyone greater than one.
06:54
All right, so we got some twos.
06:56
Let's go ahead and bring out this.
07:02
I'm gonna start His name's a little easier. So I think it's pronounced Omar.
07:10
We're say, end
07:13
that
07:14
EMP number
07:15
equals 129.
07:19
Remember, the Kama is not really in the data.
07:23
The beaver is just showing that to you so that it's easier to read.
07:27
You have to remember to exclude those
07:30
because if you put a common the number, it will not work.
07:31
So we're gonna go ahead and get rid of our count and get rid of our grouping.
07:38
And now we're gonna bring back our previous
07:41
elements of the department number in the department name,
07:47
and we're gonna go ahead and run this
07:53
and we can see that Omar, who is employee 129
07:58
is within two departments
08:01
production and quality management.
08:05
And this, again, is that many too many relationship because we know there's more employees than Omar
08:11
and production.
08:13
So this department belongs to many employees,
08:16
and this employee belongs to many departments. And that is our many too many relationships
08:22
queried out.
08:26
Well, I hope you enjoy this lesson. I hope you get that query working, and I will see you in the next lesson. 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