Database Design 36 - 1NF (First Normal Form of Database Normalization) — Transcript

Full transcript

1. 0:00hey what's up everybody this video will
2. 0:02be over the first normal form which is
3. 0:04the first step in database
4. 0:06normalization this normal form deals
5. 0:08with data being Atomic or atomicity all
6. 0:13right so let's begin let's first look at
7. 0:15three examples where there are problems
8. 0:18and then I'm going to explain a solution
9. 0:20if we take a look at this first example
10. 0:22we have a user ID first name last name
11. 0:25email and address I have some values in
12. 0:28here but those aren't really important
13. 0:30and in this example the main thing you
14. 0:32need to pay attention to is the address
15. 0:34because an address if you think about it
16. 0:36actually consists of a street address
17. 0:38State country and so forth so this
18. 0:42column is not Atomic we're asking for
19. 0:45multiple things within one column so
20. 0:48that is the problem in this
21. 0:51example the solution for this one would
22. 0:53be to break it up into multiple columns
23. 0:55you know a state and so forth that way
24. 0:58we don't have a bunch of things within
25. 0:59one column as an individual
26. 1:03value this example you can see we have
27. 1:06one row with a user ID first name last
28. 1:09name and email but if you look closely
29. 1:11we have two emails these emails were
30. 1:15entered together as one individual value
31. 1:18so you can think of maybe on a website
32. 1:20It'll ask you for your email imagine
33. 1:22putting one email in and then putting
34. 1:25your second email in in the same box and
35. 1:27clicking submit well it's entered into
36. 1:29the data database as a single value
37. 1:31maybe with the comma or something but
38. 1:33it's still entered in as the email for
39. 1:38this
40. 1:39user so two values within one which is
41. 1:42not Atomic in this example the actual
42. 1:46values given for the email are Atomic
43. 1:49because they're only one email but we
44. 1:52put two entries within our database so
45. 1:54now we have this in here twice this in
46. 1:57here twice and this in here twice and
47. 1:59the prim primary key should never be in
48. 2:01there twice the only time you can see
49. 2:03the primary key in there twice is if
50. 2:05it's a reference as a foreign key
51. 2:08because the foreign key can be put in
52. 2:09there multiple times but the primary key
53. 2:11of five should only be in there one time
54. 2:14so we have all that duplicate data now
55. 2:16what if this Caleb Curry goes and gets a
56. 2:18name change because that's such a dumb
57. 2:21name right no I'm just kidding because
58. 2:23that's that's my name and I actually
59. 2:25like that name but anyways if he gets a
60. 2:29name change he updates his information
61. 2:31what if uh only this one's updated so
62. 2:34now we have conflicting data and see
63. 2:35that's the problem with data Integrity
64. 2:37we need to make sure that doesn't happen
65. 2:40so in this example the
66. 2:43column is the problem in this example
67. 2:47the values for the row is the problem
68. 2:50and in this example the values for the
69. 2:53row is the problem so these basically
70. 2:56have the same problem how do we figure
71. 2:59out the solution
72. 3:00let's take over let's take a look over
73. 3:02here and as you can see there is no
74. 3:03solution so yeah guys thanks for
75. 3:05watching I'm just kidding let's let's do
76. 3:08this the best solution for this problem
77. 3:10is to have a user
78. 3:12table and I'm going to write the columns
79. 3:14that are within this user table so we
80. 3:16would have a user
81. 3:21ID then we are going to have a first
82. 3:23name and we're going to have a last name
83. 3:26if you wanted the address in there you
84. 3:27could have street address state country
85. 3:30up all that other good stuff then we're
86. 3:33also going to have an email table so the
87. 3:37solution is to break off the problem
88. 3:40column you can see in this example the
89. 3:43email is the thing causing the
90. 3:47problem the address that we had earlier
91. 3:50that's fixable if it's a problem in the
92. 3:52column we could just break that up into
93. 3:54multiple columns so we would have to
94. 3:55make a new table for that but since this
95. 3:57one even the the columns cor it's the
96. 4:00values within it that's causing the
97. 4:01problem we would need to create a new
98. 4:03table for that so that's why we have
99. 4:06this email table and now within here
100. 4:08we're going to have an email
101. 4:13ID and then we're going to have a
102. 4:19email which is going to be the actual
103. 4:21email
104. 4:22address which in this case would be
105. 4:25these and then we're going to have a
106. 4:28user ID
107. 4:31now this is going to be a foreign
108. 4:34key that
109. 4:36references this user
110. 4:40ID the primary key of the user
111. 4:44table now if we wanted to put two
112. 4:47individual emails we could have rows
113. 4:50within this email table let's let's
114. 4:52spread out the email table and see what
115. 4:53it might look like here's what the email
116. 4:56table might look like we would have an
117. 4:58email ID which is just a random
118. 4:59generated number for each email 6008 is
119. 5:03fine and then the email that's going to
120. 5:05be the actual address of the email so we
121. 5:08could have
122. 5:10this I'll try to make it just as bad as
123. 5:14handwriting perfect then the user ID of
124. 5:17who owns that which in this case would
125. 5:20have been five because five was the ID
126. 5:23that was used for this
127. 5:27user this will be our first row then we
128. 5:30could have another row and it'd be a new
129. 5:32email so we'd have a new email ID it'd
130. 5:35be a different email we would use this
131. 5:36one this
132. 5:39time and I'm going to make that
133. 5:41beautiful handwriting that you guys love
134. 5:44there we go and then the user ID for
135. 5:46that would also be five now this
136. 5:50repeating value is okay this this repeat
137. 5:53right here this is okay because this is
138. 5:56a forign
139. 5:58key and multiple emails are allowed in
140. 6:01this
141. 6:02situation this email ID this is not not
142. 6:06okay to repeat because each individual
143. 6:09number should be a separate email
144. 6:11because this is the primary key of the
145. 6:13email table now when we look back at the
146. 6:16user table we don't have any repeating
147. 6:19data because we're only going to have a
148. 6:21user ID such as five first name Caleb
149. 6:25and the last name Curry and there's no
150. 6:27need to make a repeating row because we
151. 6:29have have all the information we need
152. 6:31without having any problems if we change
153. 6:33the name we would just update this keep
154. 6:35the same ID change this to whatever we
155. 6:37name our name ourselves or whatever the
156. 6:40person names themselves so there you go
157. 6:42everybody that's first normal form and
158. 6:44I'll see you in the next video where we
159. 6:45will be discussing second normal form
160. 6:47peace out