1 00:00:01,129 --> 00:00:03,600 i'm ok taxes 2013 shoot or ooze 2 00:00:03,600 --> 00:00:06,890 this video I'm gonna be talking you through the concept of normalization 3 00:00:06,889 --> 00:00:10,399 so it's gonna play long followed the link below the video to do them a sec to 4 00:00:10,400 --> 00:00:13,820 the website and our taxes 2013 DVD 03 5 00:00:13,820 --> 00:00:17,350 and should be hooking that up that is 6 00:00:17,350 --> 00:00:21,210 actually only one table in the solar if you know that fast 7 00:00:21,210 --> 00:00:24,880 her help downloading this particular database 8 00:00:24,879 --> 00:00:28,820 I'll understand at now the tape we've got is a table 9 00:00:28,820 --> 00:00:32,179 that lists songs and if you're a member firm the last video 10 00:00:32,179 --> 00:00:35,799 will discuss in think data types in table in general 11 00:00:35,799 --> 00:00:39,649 the foot of the table when access is the list data I'm 12 00:00:39,649 --> 00:00:44,100 no sweetie I think we were doing shopping this and this video 13 00:00:44,100 --> 00:00:48,039 i'm looking at songs so we got a book a table songs 14 00:00:48,039 --> 00:00:51,719 and in it we list some socks funny enough I'm 15 00:00:51,719 --> 00:00:56,050 I'm but the songs listed here we also listed above X information about the 16 00:00:56,049 --> 00:00:57,729 songs listed in the album 17 00:00:57,729 --> 00:01:01,229 the songs from where listing the artist the 18 00:01:01,229 --> 00:01:05,250 made a song I so typical Cardiff 19 00:01:05,250 --> 00:01:08,799 list something you'd probably expect to see made up in Excel 20 00:01:08,799 --> 00:01:12,939 I'm what information we need everything's great right 21 00:01:12,938 --> 00:01:16,709 was the problem well actually everything is in great we do have a problem 22 00:01:16,709 --> 00:01:19,750 and for all we've got is the of 23 00:01:19,750 --> 00:01:22,780 when we look at the songs we can see that every single song 24 00:01:22,780 --> 00:01:26,849 is individual different I was his way to expect to see a list 25 00:01:26,849 --> 00:01:30,289 I'm tu amigo with the albums in the office 26 00:01:30,290 --> 00:01:34,390 we've got a problem in that we're repeating data to repeating 27 00:01:34,390 --> 00:01:37,599 the album Let It Be twice ripping sergeant pepper's Lonely Hearts Club 28 00:01:37,599 --> 00:01:38,559 Band twice 29 00:01:38,560 --> 00:01:42,649 mccartney's repeat twice in Lennon's repeated twice and 30 00:01:42,649 --> 00:01:47,000 with databases I wanted to main things you have to try and achieve 31 00:01:47,000 --> 00:01:50,250 data modeling is to take a 32 00:01:50,250 --> 00:01:55,259 with data and try and get it so you absolutely minimize down repeating as 33 00:01:55,259 --> 00:01:55,978 data 34 00:01:55,978 --> 00:02:00,390 I'm no necessarily eliminated without me nice 35 00:02:00,390 --> 00:02:04,728 the certainly minimize it down I'm and in this case 36 00:02:04,728 --> 00:02:09,439 we haven't done that because we've got a mode we do have repeating data 37 00:02:09,439 --> 00:02:13,009 in this single table so we would consider this table to be 38 00:02:13,009 --> 00:02:16,199 a normalized for not normalize 39 00:02:16,199 --> 00:02:19,219 I don't know with the correct expression is 40 00:02:19,219 --> 00:02:22,318 I'm the normalized maybe who knows 41 00:02:22,318 --> 00:02:25,479 I'm sign on those up in the comment section 42 00:02:25,479 --> 00:02:29,250 Arctic so what we do and what was actually the problem we've repeating 43 00:02:29,250 --> 00:02:30,400 data anyway I was the 44 00:02:30,400 --> 00:02:34,550 the big fuss about well the couple have reasons but 45 00:02:34,550 --> 00:02:37,939 for one if you're repeating date your skin 46 00:02:37,939 --> 00:02:41,780 every time somebody enters a record here they have the right now 47 00:02:41,780 --> 00:02:45,159 them now imagine I am they want it at 48 00:02:45,159 --> 00:02:48,889 the long and too long 49 00:02:48,889 --> 00:02:52,238 load long and Lorraine 50 00:02:52,239 --> 00:02:55,590 being Road right from I'm 51 00:02:55,590 --> 00:02:59,620 let it be and then they did something I do 52 00:02:59,620 --> 00:03:03,110 while I made a spelling site I miss bell 53 00:03:03,110 --> 00:03:06,300 had to be remitted notice well if you're 54 00:03:06,300 --> 00:03:11,209 assayed filled up your entire tables songs and you at that and I thousand 55 00:03:11,209 --> 00:03:13,408 songs in there you want to run a query in C 56 00:03:13,408 --> 00:03:17,649 all the songs let it be it wouldn't return 57 00:03:17,650 --> 00:03:21,569 dis Rizzo assuming you spell let it be crack in the query 58 00:03:21,568 --> 00:03:25,139 I'm overturned as it stands only these two 59 00:03:25,139 --> 00:03:29,268 oversee anything else this was spelled correctly so that's one problem we've 60 00:03:29,269 --> 00:03:33,030 repeating data is its you're already bringing in the possibility that the 61 00:03:33,030 --> 00:03:34,158 date is inaccurate 62 00:03:34,158 --> 00:03:38,399 and another issue is few years ago 63 00:03:38,400 --> 00:03:42,689 the beaters all the people who own the Beatles 64 00:03:42,689 --> 00:03:45,769 the rights to the Beatles released let it be naked 65 00:03:45,769 --> 00:03:48,819 which was that I think to the 66 00:03:48,818 --> 00:03:52,369 I will let it be but with all of the installation instrumentation stripped 67 00:03:52,370 --> 00:03:52,780 out 68 00:03:52,780 --> 00:03:56,959 so if I decided to that was the preferred our modern really won't let it 69 00:03:56,959 --> 00:03:57,789 be more 70 00:03:57,789 --> 00:04:01,949 because I'd like more information instrumentation unpronounceable week 71 00:04:01,949 --> 00:04:05,530 instru instrumentation in the first place 72 00:04:05,530 --> 00:04:10,650 why would have to do is I would have to go for every single 73 00:04:10,650 --> 00:04:16,500 the record and any record has let it be left updated to lead to be naked 74 00:04:16,500 --> 00:04:19,629 and again I mean 75 00:04:19,629 --> 00:04:23,699 you could miss a record I makes the process longer 76 00:04:23,699 --> 00:04:28,830 so there these days a couple problems are described as repeating data 77 00:04:28,829 --> 00:04:33,019 I at the moment we've got six records here not the end of the world 78 00:04:33,019 --> 00:04:36,829 when you start getting today is sets they've got thousands tens of thousands 79 00:04:36,829 --> 00:04:38,849 hundreds thousands millions a records 80 00:04:38,850 --> 00:04:42,790 this stuff really does become an issue for thank you for me 81 00:04:42,790 --> 00:04:46,069 you wanna normalize your database so how do we do that 82 00:04:46,069 --> 00:04:49,310 well let's go back to the definition of a table 83 00:04:49,310 --> 00:04:53,089 a table is I'm something the 84 00:04:53,089 --> 00:04:58,049 stores lists as data so what we gonna do they simply is we're gonna create 85 00:04:58,050 --> 00:05:01,189 a list of albums and we're gonna cry list of artists 86 00:05:01,189 --> 00:05:06,779 so this close table songs 87 00:05:06,779 --> 00:05:10,250 on ask.com craving go table design 88 00:05:10,250 --> 00:05:13,740 and we were say 89 00:05:13,740 --> 00:05:16,780 album ID 90 00:05:16,779 --> 00:05:19,978 port a number so it's gonna cry be out on table now 91 00:05:19,978 --> 00:05:23,180 kitchen and give you a primary key and now 92 00:05:23,180 --> 00:05:27,769 firm name and short text 93 00:05:27,769 --> 00:05:33,529 and save that call it table albums 94 00:05:33,529 --> 00:05:38,569 and go thanks you lysette a couple of albums we had 95 00:05:38,569 --> 00:05:41,639 I'm sergeant Antequera 96 00:05:41,639 --> 00:05:46,300 I'm given the short time scotty Beaufort 97 00:05:46,300 --> 00:05:50,870 other papers and we use a hat let it be 98 00:05:50,870 --> 00:05:54,410 I okay so we've actually met now made a list that are open so that's great 99 00:05:54,410 --> 00:05:57,810 let's save that close to now let's cray 100 00:05:57,810 --> 00:06:01,240 another table banquet artists 101 00:06:01,240 --> 00:06:04,449 artist it. 102 00:06:04,449 --> 00:06:07,879 court Mon auto number and 103 00:06:07,879 --> 00:06:11,728 marxist name and short text again 104 00:06:11,728 --> 00:06:15,649 I'll make sure this artist I these a primary K save it 105 00:06:15,649 --> 00:06:21,500 table artists sure you get the idea 106 00:06:21,500 --> 00:06:25,500 press OK and drop in today tissue 107 00:06:25,500 --> 00:06:29,209 and this way one second when I saw a mouse out 108 00:06:29,209 --> 00:06:32,339 okay right and a stop smart to sin eso 109 00:06:32,339 --> 00:06:36,698 linen work cockney 110 00:06:36,699 --> 00:06:42,350 and I was on starving 111 00:06:42,350 --> 00:06:46,889 are so we got now three lists the data we've got a list of the songs 112 00:06:46,889 --> 00:06:50,418 lists are artists analysts the problems the question is how do we 113 00:06:50,418 --> 00:06:53,579 incorporate this so they all make sense because 114 00:06:53,579 --> 00:06:56,939 now we really are chips que de Cuba I was here 115 00:06:56,939 --> 00:07:01,259 I'm grabs here but it is what we do close or not because up not 116 00:07:01,259 --> 00:07:04,990 and we drop zones into the design view am we 117 00:07:04,990 --> 00:07:09,259 to Lee album and artist 118 00:07:09,259 --> 00:07:12,598 we say album ninth state 119 00:07:12,598 --> 00:07:16,259 all of I am being text we're gonna 120 00:07:16,259 --> 00:07:22,729 change helped a number and we're going to decipher artist 121 00:07:22,728 --> 00:07:29,728 number what we should actually do this to us 122 00:07:31,589 --> 00:07:36,459 because the value in a store in Table songs here 123 00:07:36,459 --> 00:07:42,560 is the album ID which is this number here 124 00:07:42,560 --> 00:07:45,910 i Mees FK which means foreign key I'm 125 00:07:45,910 --> 00:07:49,370 you need to worry about it too much just yet but just rest assured that Rove the 126 00:07:49,370 --> 00:07:50,750 store the name 127 00:07:50,750 --> 00:07:54,709 we're just gonna store the number so for example so depends on caucus 128 00:07:54,709 --> 00:07:59,228 band is number one to open number one there 129 00:07:59,228 --> 00:08:02,740 will help my friend number one number one and then across the universe is that 130 00:08:02,740 --> 00:08:03,300 it be 131 00:08:03,300 --> 00:08:08,680 just 22 and two so the idea is to bust or in a number 132 00:08:08,680 --> 00:08:13,410 I'm this will always be the number for sergeant Pepper's 133 00:08:13,410 --> 00:08:16,889 and this number matches up here so 134 00:08:16,889 --> 00:08:22,629 photo change wants is for example a baby photos changes to let it be naked 135 00:08:22,629 --> 00:08:26,430 not do this gonna be late show you what I mean this is actually 136 00:08:26,430 --> 00:08:29,889 nothing in Table songs changes to just relax it to number two 137 00:08:29,889 --> 00:08:33,839 just kinda locker a code lookup saying that you have to look up 138 00:08:33,839 --> 00:08:37,450 album number two in the album section to work out 139 00:08:37,450 --> 00:08:41,009 the album for across the University gotta album look at number two and you 140 00:08:41,009 --> 00:08:44,000 get let it be naked in st. I did so you're 141 00:08:44,000 --> 00:08:47,769 basically get the best of both worlds which is you are able to relate the data 142 00:08:47,769 --> 00:08:50,789 and the table but you're also able to 143 00:08:50,789 --> 00:08:55,360 have sector entity separate list data first albums in essence a look artists 144 00:08:55,360 --> 00:08:57,759 are you letter mccarty star 145 00:08:57,759 --> 00:09:02,250 so sorry for blowing up close by Anthony McCartney Stella McCartney 146 00:09:02,250 --> 00:09:05,580 help from my friends the star 147 00:09:05,580 --> 00:09:10,110 mccartney my show across the universe 148 00:09:10,110 --> 00:09:13,490 I don't remember our sugar for London 149 00:09:13,490 --> 00:09:18,778 let it be was McCartney and those so it must be learned and 150 00:09:18,778 --> 00:09:22,899 must've been done and as well across the universe 151 00:09:22,899 --> 00:09:28,080 are running without if it's wrong sorry state govern in the comment section 152 00:09:28,080 --> 00:09:31,379 okay sucking this is how 153 00:09:31,379 --> 00:09:35,980 computer store data we've broken down table songs were store in the 154 00:09:35,980 --> 00:09:39,500 I'm I D keys from the other table so that we are 155 00:09:39,500 --> 00:09:43,820 have consistent data and this is the kind of setup we should used one other 156 00:09:43,820 --> 00:09:45,220 thing you need to do 157 00:09:45,220 --> 00:09:48,700 this got a database tools good relationships and now 158 00:09:48,700 --> 00:09:53,310 right click Show table top three tables and now it gets going to relate the 159 00:09:53,309 --> 00:09:54,479 stables 160 00:09:54,480 --> 00:09:59,519 by went I'm sums in the middle clicking on table album from Table I haven't been 161 00:09:59,519 --> 00:10:02,110 tracking over the album ID FK 162 00:10:02,110 --> 00:10:07,149 force referential integrity create an artist 163 00:10:07,149 --> 00:10:10,740 artist so this means what we've done now 164 00:10:10,740 --> 00:10:14,120 is right now said table songs 165 00:10:14,120 --> 00:10:18,470 few credit record and was gonna krecko told ABC day 166 00:10:18,470 --> 00:10:21,889 and you can add an album there's no 167 00:10:21,889 --> 00:10:26,028 album number three so if I try and had our number three here 168 00:10:26,028 --> 00:10:29,480 and the so-so have a table of this there's no number four 169 00:10:29,480 --> 00:10:33,278 and for example are trying at number four artist 170 00:10:33,278 --> 00:10:36,919 you can at Pechanga record because a related records required 171 00:10:36,919 --> 00:10:40,819 so we've done is we've actually restricted depth restricted to the only 172 00:10:40,820 --> 00:10:42,019 values that can be 173 00:10:42,019 --> 00:10:46,360 and today in the album column 174 00:10:46,360 --> 00:10:50,360 our values that already exist a which makes perfect sense 175 00:10:50,360 --> 00:10:54,480 and the only valdes it can be entered in the of this column %uh values already 176 00:10:54,480 --> 00:10:55,339 existed 177 00:10:55,339 --> 00:10:59,519 the number for doesn't exist so what good would it be in a stable anyway 178 00:10:59,519 --> 00:11:03,179 and that's bad idea that's the idea of for 179 00:11:03,179 --> 00:11:07,469 around enforcing referential integrity which is what I'm trying to say 180 00:11:07,470 --> 00:11:11,320 so that's normalization I'm gonna do while most being 181 00:11:11,320 --> 00:11:14,339 which isn't related to normalization but 182 00:11:14,339 --> 00:11:18,790 just for the sake of the next couple videos grover the nice 183 00:11:18,789 --> 00:11:21,860 were discussing state have time to look at the number one 184 00:11:21,860 --> 00:11:25,079 I would like to actually change this I'm 185 00:11:25,078 --> 00:11:28,099 so that I can see the actual album 186 00:11:28,100 --> 00:11:31,629 so I'm going to use a lookup field I'm gonna give you disclaim I wouldn't 187 00:11:31,629 --> 00:11:32,700 normally the 188 00:11:32,700 --> 00:11:36,870 use lookup field soweto's making web database but 189 00:11:36,870 --> 00:11:40,649 just because if the next couple videos would be much more useful for us to see 190 00:11:40,649 --> 00:11:45,519 sergeant Pepper's than number one so we're gonna drop it into the sign the 191 00:11:45,519 --> 00:11:49,089 go to overall I D lookup 192 00:11:49,089 --> 00:11:53,720 spray is comic books persist thought they were query 193 00:11:53,720 --> 00:11:57,250 and Rosso us 194 00:11:57,250 --> 00:12:01,289 mean click on a on the arrow and we're gonna choose 195 00:12:01,289 --> 00:12:06,039 table albums 196 00:12:06,039 --> 00:12:09,379 up that yeah K a column count 197 00:12:09,379 --> 00:12:12,519 to and corn whips 0 198 00:12:12,519 --> 00:12:15,919 and four 199 00:12:15,919 --> 00:12:20,979 okay now free good day she you too 200 00:12:20,980 --> 00:12:26,519 now we actually see sergeant Pepper's me the same thing with artists 201 00:12:26,519 --> 00:12:30,230 clicking here we'll change it to calm my books 202 00:12:30,230 --> 00:12:33,610 and a 203 00:12:33,610 --> 00:12:37,919 she's artists column cap to 204 00:12:37,919 --> 00:12:41,129 0 and full yeah update 205 00:12:41,129 --> 00:12:44,240 even though has nothing to be up for it Co 206 00:12:44,240 --> 00:12:48,709 got into that issue to now we've essentially actually recreate 207 00:12:48,708 --> 00:12:53,219 what we had originally which was the 208 00:12:53,220 --> 00:12:56,410 original table we've had that we've been actually done so much more now we've 209 00:12:56,409 --> 00:12:57,078 divided 210 00:12:57,078 --> 00:13:00,250 the entities down with no call list the songs 211 00:13:00,250 --> 00:13:04,480 listed I was on a list of artists just to prove my original point if you open 212 00:13:04,480 --> 00:13:06,370 up they will artists 213 00:13:06,370 --> 00:13:10,440 not RT side table albums look at let it be naked I'm 214 00:13:10,440 --> 00:13:14,430 had to be naked for a change this back to just let it be 215 00:13:14,429 --> 00:13:18,479 on change in one record in Table albums 216 00:13:18,480 --> 00:13:21,528 when I got a table some this changed every record here 217 00:13:21,528 --> 00:13:24,669 and that's because it is although we can see 218 00:13:24,669 --> 00:13:28,309 the name let it be there's a hidden column which is the 219 00:13:28,309 --> 00:13:31,539 ID to about this album ID which relates to 220 00:13:31,539 --> 00:13:34,588 number two so using like sold 221 00:13:34,589 --> 00:13:37,779 concept to the lookup another table 222 00:13:37,778 --> 00:13:42,860 I'm and the yeah that's how normalization works breaking 223 00:13:42,860 --> 00:13:46,149 a table down into this is data 224 00:13:46,149 --> 00:13:50,559 relating them and then you can use things like we resolve this case lookup 225 00:13:50,559 --> 00:13:51,899 fields to 226 00:13:51,899 --> 00:13:55,049 us to reconstitute the data into information and 227 00:13:55,049 --> 00:13:59,240 you've achieved your objectives okay say thanks what's misleading and hope CNNX 228 00:13:59,240 --> 00:13:59,539 video