i'm ok taxes 2013 shoot or ooze
this video I'm gonna be talking you through the concept of normalization
so it's gonna play long followed the link below the video to do them a sec to
the website and our taxes 2013 DVD 03
and should be hooking that up that is
actually only one table in the solar if you know that fast
her help downloading this particular database
I'll understand at now the tape we've got is a table
that lists songs and if you're a member firm the last video
will discuss in think data types in table in general
the foot of the table when access is the list data I'm
no sweetie I think we were doing shopping this and this video
i'm looking at songs so we got a book a table songs
and in it we list some socks funny enough I'm
I'm but the songs listed here we also listed above X information about the
songs listed in the album
the songs from where listing the artist the
made a song I so typical Cardiff
list something you'd probably expect to see made up in Excel
I'm what information we need everything's great right
was the problem well actually everything is in great we do have a problem
and for all we've got is the of
when we look at the songs we can see that every single song
is individual different I was his way to expect to see a list
I'm tu amigo with the albums in the office
we've got a problem in that we're repeating data to repeating
the album Let It Be twice ripping sergeant pepper's Lonely Hearts Club
Band twice
mccartney's repeat twice in Lennon's repeated twice and
with databases I wanted to main things you have to try and achieve
data modeling is to take a
with data and try and get it so you absolutely minimize down repeating as
data
I'm no necessarily eliminated without me nice
the certainly minimize it down I'm and in this case
we haven't done that because we've got a mode we do have repeating data
in this single table so we would consider this table to be
a normalized for not normalize
I don't know with the correct expression is
I'm the normalized maybe who knows
I'm sign on those up in the comment section
Arctic so what we do and what was actually the problem we've repeating
data anyway I was the
the big fuss about well the couple have reasons but
for one if you're repeating date your skin
every time somebody enters a record here they have the right now
them now imagine I am they want it at
the long and too long
load long and Lorraine
being Road right from I'm
let it be and then they did something I do
while I made a spelling site I miss bell
had to be remitted notice well if you're
assayed filled up your entire tables songs and you at that and I thousand
songs in there you want to run a query in C
all the songs let it be it wouldn't return
dis Rizzo assuming you spell let it be crack in the query
I'm overturned as it stands only these two
oversee anything else this was spelled correctly so that's one problem we've
repeating data is its you're already bringing in the possibility that the
date is inaccurate
and another issue is few years ago
the beaters all the people who own the Beatles
the rights to the Beatles released let it be naked
which was that I think to the
I will let it be but with all of the installation instrumentation stripped
out
so if I decided to that was the preferred our modern really won't let it
be more
because I'd like more information instrumentation unpronounceable week
instru instrumentation in the first place
why would have to do is I would have to go for every single
the record and any record has let it be left updated to lead to be naked
and again I mean
you could miss a record I makes the process longer
so there these days a couple problems are described as repeating data
I at the moment we've got six records here not the end of the world
when you start getting today is sets they've got thousands tens of thousands
hundreds thousands millions a records
this stuff really does become an issue for thank you for me
you wanna normalize your database so how do we do that
well let's go back to the definition of a table
a table is I'm something the
stores lists as data so what we gonna do they simply is we're gonna create
a list of albums and we're gonna cry list of artists
so this close table songs
on ask.com craving go table design
and we were say
album ID
port a number so it's gonna cry be out on table now
kitchen and give you a primary key and now
firm name and short text
and save that call it table albums
and go thanks you lysette a couple of albums we had
I'm sergeant Antequera
I'm given the short time scotty Beaufort
other papers and we use a hat let it be
I okay so we've actually met now made a list that are open so that's great
let's save that close to now let's cray
another table banquet artists
artist it.
court Mon auto number and
marxist name and short text again
I'll make sure this artist I these a primary K save it
table artists sure you get the idea
press OK and drop in today tissue
and this way one second when I saw a mouse out
okay right and a stop smart to sin eso
linen work cockney
and I was on starving
are so we got now three lists the data we've got a list of the songs
lists are artists analysts the problems the question is how do we
incorporate this so they all make sense because
now we really are chips que de Cuba I was here
I'm grabs here but it is what we do close or not because up not
and we drop zones into the design view am we
to Lee album and artist
we say album ninth state
all of I am being text we're gonna
change helped a number and we're going to decipher artist
number what we should actually do this to us
because the value in a store in Table songs here
is the album ID which is this number here
i Mees FK which means foreign key I'm
you need to worry about it too much just yet but just rest assured that Rove the
store the name
we're just gonna store the number so for example so depends on caucus
band is number one to open number one there
will help my friend number one number one and then across the universe is that
it be
just 22 and two so the idea is to bust or in a number
I'm this will always be the number for sergeant Pepper's
and this number matches up here so
photo change wants is for example a baby photos changes to let it be naked
not do this gonna be late show you what I mean this is actually
nothing in Table songs changes to just relax it to number two
just kinda locker a code lookup saying that you have to look up
album number two in the album section to work out
the album for across the University gotta album look at number two and you
get let it be naked in st. I did so you're
basically get the best of both worlds which is you are able to relate the data
and the table but you're also able to
have sector entity separate list data first albums in essence a look artists
are you letter mccarty star
so sorry for blowing up close by Anthony McCartney Stella McCartney
help from my friends the star
mccartney my show across the universe
I don't remember our sugar for London
let it be was McCartney and those so it must be learned and
must've been done and as well across the universe
are running without if it's wrong sorry state govern in the comment section
okay sucking this is how
computer store data we've broken down table songs were store in the
I'm I D keys from the other table so that we are
have consistent data and this is the kind of setup we should used one other
thing you need to do
this got a database tools good relationships and now
right click Show table top three tables and now it gets going to relate the
stables
by went I'm sums in the middle clicking on table album from Table I haven't been
tracking over the album ID FK
force referential integrity create an artist
artist so this means what we've done now
is right now said table songs
few credit record and was gonna krecko told ABC day
and you can add an album there's no
album number three so if I try and had our number three here
and the so-so have a table of this there's no number four
and for example are trying at number four artist
you can at Pechanga record because a related records required
so we've done is we've actually restricted depth restricted to the only
values that can be
and today in the album column
our values that already exist a which makes perfect sense
and the only valdes it can be entered in the of this column %uh values already
existed
the number for doesn't exist so what good would it be in a stable anyway
and that's bad idea that's the idea of for
around enforcing referential integrity which is what I'm trying to say
so that's normalization I'm gonna do while most being
which isn't related to normalization but
just for the sake of the next couple videos grover the nice
were discussing state have time to look at the number one
I would like to actually change this I'm
so that I can see the actual album
so I'm going to use a lookup field I'm gonna give you disclaim I wouldn't
normally the
use lookup field soweto's making web database but
just because if the next couple videos would be much more useful for us to see
sergeant Pepper's than number one so we're gonna drop it into the sign the
go to overall I D lookup
spray is comic books persist thought they were query
and Rosso us
mean click on a on the arrow and we're gonna choose
table albums
up that yeah K a column count
to and corn whips 0
and four
okay now free good day she you too
now we actually see sergeant Pepper's me the same thing with artists
clicking here we'll change it to calm my books
and a
she's artists column cap to
0 and full yeah update
even though has nothing to be up for it Co
got into that issue to now we've essentially actually recreate
what we had originally which was the
original table we've had that we've been actually done so much more now we've
divided
the entities down with no call list the songs
listed I was on a list of artists just to prove my original point if you open
up they will artists
not RT side table albums look at let it be naked I'm
had to be naked for a change this back to just let it be
on change in one record in Table albums
when I got a table some this changed every record here
and that's because it is although we can see
the name let it be there's a hidden column which is the
ID to about this album ID which relates to
number two so using like sold
concept to the lookup another table
I'm and the yeah that's how normalization works breaking
a table down into this is data
relating them and then you can use things like we resolve this case lookup
fields to
us to reconstitute the data into information and
you've achieved your objectives okay say thanks what's misleading and hope CNNX
video