7 hours 36 minutes
hi and welcome the module to Pleasant 1.4, the 1 to 1 relationship in more detail or continued.
I'm at a my sequel Demo database download site. This will be affiliated with the lesson as well for download, but you can also go to my sequel tutorial dot or slash my sequel,
Dash sample Dash database dot as PX to download as well.
So we're going to work with a skimmer that displays the 1 to 1 relationship.
And this is the schematic right here for the database.
So you'll see here between order details and orders
that and products that there is a 1 to 1 and only one relationship. Now what is the 1 to 1 and only relationship?
Well, if you think back to the employees related to the state that they were in, you could have had
a John Kate, a Bob that all lived in Arizona. They had a 1 to 1 relationship. They all lived in that one state,
now 1 to 1, and only one relationship would be like if we said, Well, no, you can't have three employees in one state. They all have to be in different states
now, in the context of living in the States, that doesn't make too much sense. But where that does make sense is online orders.
For example, if you place an order online, do some real Taylor. You'll get an order number from that retailer. That order number is yours and only yours.
Other customers won't have that order number. And that's a good thing. Because if you had a complaint about an order order got mixed up and customers were sharing order numbers,
it would be mass confusion, and that would be no good for any store.
So we're gonna take a look at this data base. We're gonna download it and the neat thing about these demo databases, and there's a lot of them out there. You can find him by just searching my sequel demo database, and they usually give you a nice kima. Their pre built their good representation of what you find out there in the wild and, uh, there
built following the ah, you know, custom design patterns and standards
that unfortunately are missing from the wild sometimes.
So if we scroll up here
when we click the download, my simple sample database
and click save
We're gonna Then
Little Earl Bouncing Mints. It's done.
Go to your downloads folder,
right click that
click Extract All
open the folder going and double Click that because we affiliated dot sequel to de Beaver. It should start D Beaver with that script open,
that doesn't take too long. Shouldn't take too long. Okay, we are good to go
If we look do this file that we opened, we'll see that it's a great database.
used classic models, and then it's gonna create those tables. In addition to creating those tables, it's gonna assert all this nifty fake data forced the query against and makes sense off
and maybe transform if we're filling. Ah, like having a good time.
So if you just opened up
de beber and then have it open, it's possible that this could have said none.
If it does, make sure you give this a click. You'll get this window. Select a connection, Do you? Ah, establishing the previous lesson and just click Select.
And once that's done place, you're cursed at the top of the script
and click the Execute sequel script button
and you'll see it run through and run. Everything
all right? That was quick. We're done.
So the next band, the data basis
left, clicked on the databases. Then right? Click,
There's our classic models. Now click on that and right click is well and click set as active. That'll make sure that Thea
the ah, intelligence works correctly where it predicts and shows you what your options are when you're coding
we're gonna set that is active. Let's take a quick look at the tables.
Okay, Let's see if that matches the schematic. We were looking at those table names.
Make sure ah,
nothing weird is going on.
Okay, we take a look at this, will see that we got the orders. Order details, products. That's what we're interested in now, when you're right, enquiries. You want to focus on what you're trying to get out of it.
Every now and then, I see an individual who tries to connect every tables and make one super query that they can just select from. And what that usually results in is miss misunderstood relationships and connections that then skew the results in weird ways. So good
practice, especially when he knew, is just connected tables that you're interested in getting data out off for that report.
So we're interested in the products that order details and orders. The order details sits right in the middle,
So let's give that one a click. Left click, Right. Click view diagram.
All right, so we see those three same tables, we see the orders, the order details, the products dee beavers being kind to us and saying, Hey,
this is the keys on these tables or details. Has two keys. Orders has one key
products has one keep.
So let's get the quivering click. Sequel Editor,
new sequel editor.
And we're gonna type in select Star Star means everything. Select everything. Give me everything from
All right. We're gonna start order. We're gonna work our way up.
Let's go in and run that and see what happens. Cursor at the top.
Pretty sure this fails. Let's try.
Nope, that worked. Okay, I know I've seen that feel before. Maybe they updated in a new version.
Okay, so that's the data we're getting out right now. That's from one table. That's good. But we need two tables. Let's go ahead and connect. Actually, only three But let's let's work our way up a proper way to format. This is comma because you're listing making a list just like the anglers English language. We use commas,
order details. That's what we want.
So if we run this,
we'll get a bunch of columns,
which is no good for us right now. So let's be more specific. Let's do orders.
This is where then tell us since comes in very helpful.
So just see. Ah, let's see, we want the order number
on. Then let's grab something from order details
I'll stay up Coach.
Well, we need to make a relationship between the tables. Right now, there's no relationship established between the tables, and we'll get wrong results. Usually
there are a few use cases for not establishing relationship, but that's not the case here. We're gonna take
for our first constraint
and with the orders dot order number
dot order number
that's going to run this. We're selecting the order number.
The product code
from orders and order details tables two tables
where the order number on one table is equal to the order number on another table. Let's give that a run.
so if we look at this, will see that 10 123
had four products associated with that order number. So we know we're pretty confident. Anyway, when this customer placed this order, he ordered four things.
Now, let's go ahead and filter out the other order numbers by making an extra constraint.
We're gonna say where and
cause once you've used the key word where
and then and to continue the constraints. So we're going to say and orders dot order number.
Oh, that's my orders, right? That's very important.
Got order number
10. 123 No comments here.
De Beber is kind of Ah, this is one of the things I'm not too fond of. There's probably a way to turn it off, but in the database there is no comma in the data.
And if we put a common there,
they won't work.
We'll get rid of that comet will work.
There we go. So there's those four. We've narrowed it down to justice order number. Let's find out what those products are. And to do that, we need to add one more table. The product stable. So comma
There we go.
Now. We haven't connected
any relationship. What happens if without a relationship,
we run the quarry? Well, we're gonna see bad results. Let me show you what I'm talking about.
Boom. Okay, so what is going on here? Why do we get all these results? Fact that I can tell you right now are incorrect.
Well, what's going on here is because we haven't defined our relationship.
My sequel saying, Hey, you've added this table. You having to find a relationship? So you know what? I'm gonna D'oh!
I'm going to give you a record back for every product that exists in the products table because
you haven't stated explicitly what you're looking for.
And that's wrong. In most cases,
we may see a case where we want to do that, but that's not the case here. So we need to add that relationship to fix this. And I like to keep relationships to the better.
I'm sorry. I like to keep the relationships together
and the constraints at the bottom. So what we're gonna do is we're gonna go above the and orders dot Order number equals
10 1 23 and we're gonna
add the other relationship that the table has. So we're gonna
hit, enter right there, type the word. And again
we're gonna say
detail. Spell it right,
doc. Product code
That product heard.
Okay, so now we've established a relationship. Let's look at that skim again.
We could see that the product code is a key over here. We see the product code is a key. Over here, you can see we have a 1 to 1 and only one relationship again.
And when we run this query, we should see this clean up quite nicely. So let's give that a run.
And sure enough, it did it drop back down to the four records that we had before. But we don't have the product name. Let's bring that into the
into the decree. So it's a comma
There's our product name. Let's give that a click
and let's play that.
There we go. Okay, those are cars,
so this guy must have a fair amount of money because it would appear that within within order 10 123
this customer purchased four products and those four products were different types of cars. Anyways, this lesson starting to get a little bit long. We're at about 13 minutes.
I hope you enjoy this lesson and I will see you in the next lesson. Thank you.
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