Database Design 7 - Data Integrity — Transcript

Full transcript

1. 0:00hey what's up everybody I'm on the
2. 0:01opposite side today which just feels
3. 0:03weird but anyways we're going to be
4. 0:05talking about data Integrity all right
5. 0:08so what is data Integrity we talked
6. 0:11about it a little bit in the last video
7. 0:13but this video is going to be going over
8. 0:14it in more depth data Integrity is just
9. 0:17having correct data in your database so
10. 0:20when you when we're databasing we don't
11. 0:23want repeating values we don't want
12. 0:26incorrect values and we don't want
13. 0:28broken relationships
14. 0:30between tables for example in the last
15. 0:33video I gave the example you have a
16. 0:35table here and this is the uh user table
17. 0:39this is weird writing like this and then
18. 0:42we have a table over here and that is
19. 0:44the sale table which is where you sell
20. 0:48products for example this is just an
21. 0:50example uh you can you can Implement
22. 0:53other examples but this is just to
23. 0:55illustrate my
24. 0:57point a sale is when a user buys
25. 1:02product that means a sale needs a user
26. 1:06as the buyer otherwise there's no sale
27. 1:09we can't have a sale if no one bought it
28. 1:12does that make sense that means the sale
29. 1:14is dependent on a user and we have a
30. 1:18relationship the user
31. 1:20buys stuff stuff is bought by a user
32. 1:25this is a known as a can you see this
33. 1:28this is known as a
34. 1:33[Music]
35. 1:34relationship now uh do keep in mind that
36. 1:38the term relational database does not
37. 1:40come from the word relationship it comes
38. 1:44from relations which is the
39. 1:47mathematical um connection of sets which
40. 1:51we talked about that in a earlier video
41. 1:54but just don't get that confused but
42. 1:56relational database does have
43. 1:57relationships between
44. 2:00tables so this if for some reason this
45. 2:03was broken and now we have a sale that
46. 2:06was bought by a user or no user so for
47. 2:11example let's say this sale was bought
48. 2:14by user with the ID of seven okay and
49. 2:18then we have a user of ID of seven so
50. 2:21there's a connection the sale was bought
51. 2:23by the user of ID of 7 which points back
52. 2:26to the user make sense that's data
53. 2:28Integrity but If this relationship is
54. 2:31broken and it says this sale is bought
55. 2:33by the user with the ID of seven and
56. 2:38then this person is removed from the
57. 2:41database well now this sale points to a
58. 2:44table or a user that doesn't exist and
59. 2:48uh that might be that might be practical
60. 2:50in some sense to say that sale was
61. 2:52bought by that user when he did exist
62. 2:55but for things that need to update
63. 2:57consistently we don't want that to
64. 2:59happen
65. 3:00so that's an example of an Integrity
66. 3:02issue so you want to keep that
67. 3:04relationship to say okay well we got rid
68. 3:06of the user we need to update the sales
69. 3:09to say well either the person is deleted
70. 3:12or also do something with the
71. 3:15sales so yeah sorry if that was a little
72. 3:22confusing okay so the three main types
73. 3:25of data Integrity first one is entity
74. 3:28integrity we talked about entities in
75. 3:31like one of the first few videos an
76. 3:32entity is anything we store data about
77. 3:35so we had the user to begin with this is
78. 3:38an
79. 3:40entity what it means by entity Integrity
80. 3:43is basically unique entities when we
81. 3:47have a user we often give them IDs this
82. 3:50is known as a key so an ID is used to
83. 3:56enforce uniqueness among the entities
84. 4:00like for example if we had a table for
85. 4:07users and we
86. 4:09store the the name of the
87. 4:15person and then we store their address
88. 4:19no let's just go their phone number yeah
89. 4:21sorry because when we talk about
90. 4:23addresses it's best to break it up into
91. 4:26like Street State and I don't want it to
92. 4:28go through all that so we could
93. 4:30potentially have a person with a
94. 4:33repeating name so let's just say his
95. 4:35name's Caleb and his phone number
96. 4:39is800 you
97. 4:42wish all right well if we had another
98. 4:46person if they both lived in the same
99. 4:48house for example and this was their
100. 4:50house phone well sometimes people name
101. 4:53their children the same name as them so
102. 4:55now we have two
103. 4:57people with the same phone
104. 5:01number and you look at this and you say
105. 5:04okay well either either we have two
106. 5:05people with the same phone number or we
107. 5:09have two rows talking about the same
108. 5:11person and we don't know because we
109. 5:13don't have proper data Integrity so a
110. 5:16way to solve this is to either add more
111. 5:20columns to where the row would be unique
112. 5:22such as a social security number or we
113. 5:25could add an
114. 5:27ID column
115. 5:30and this would be like six and seven so
116. 5:33now we're talking about two separate
117. 5:36entities this is talking about this guy
118. 5:39because his ID is seven this one is
119. 5:41talking about this guy because his ID is
120. 5:45six they're two separate entities that's
121. 5:49entity Integrity having uniqueness among
122. 5:52your entities we have uniqueness with
123. 5:56the rows and we also have only one user
124. 5:59table we're not going to have another
125. 6:00user table because that information
126. 6:03should go in the original user
127. 6:06table on to referential Integrity this
128. 6:09is the one we talked about earlier is
129. 6:11when we reference the ID of a table and
130. 6:15another
131. 6:17table if we had a uh a comments table on
132. 6:21the website right and we say who the
133. 6:24comment is by we have a user ID
134. 6:30which points back to this ID over here
135. 6:33that's because the comment has to be
136. 6:36posted by a user otherwise we have a
137. 6:41comment that didn't have someone who
138. 6:43posted it which doesn't make sense in
139. 6:46the Practical world so we always have to
140. 6:49have referential integrity which
141. 6:51basically says every time there's a
142. 6:53comment there needs to be a person who
143. 6:56posted that comment and that connection
144. 6:58needs to REM it needs to stay if for
145. 7:01some reason this is disconnected and
146. 7:03then this user is removed or this table
147. 7:06is changed well then this could change I
148. 7:09mean this stays the same and then
149. 7:11there's a disconnection to where this is
150. 7:14changed and this is old outdated data
151. 7:18where either the user no longer exists
152. 7:21and the comment still says he does or
153. 7:24something else like that that's
154. 7:27referential integrity and that'll make
155. 7:29more sense once we start designing and
156. 7:32stuff but
157. 7:34yeah uh domain
158. 7:37Integrity that that brings up a new word
159. 7:40domain domain is basically just the
160. 7:44acceptable values for a column for SE
161. 7:49it's what we are storing the range of
162. 7:52what we are storing within a database so
163. 7:55when we're talking about tables we have
164. 7:57columns so these these columns up here
165. 8:00let's say we have a phone
166. 8:03number well that means we should have a
167. 8:06certain number of digits uh we have the
168. 8:08area code and then the three digits and
169. 8:12then the four digits and that's at least
170. 8:15us it might change other places but that
171. 8:18is uh 10 digits so we know that the
172. 8:21phone number should be 10 digits and it
173. 8:24should
174. 8:24be numbers so if for some reason I put
175. 8:28the word
176. 8:29take in the phone number column well for
177. 8:33one this is going to cause an error if
178. 8:36we have domain Integrity because it's
179. 8:39not a 10-digit
180. 8:41number it's just a word that's not the
181. 8:44proper domain it's not a digit it's not
182. 8:48numbers and it's not 10 of them so we
183. 8:52know that's going to cause an error so
184. 8:54that's the basics of data
185. 8:57Integrity if when we we don't have data
186. 8:59Integrity we have errors when we have
187. 9:03data Integrity we do something to
188. 9:05correct those errors such as saying oh
189. 9:09these all need to be
190. 9:10numbers that's how we Implement data
191. 9:13Integrity by setting database rules and
192. 9:16how you do that with the database well
193. 9:18that kind of varies upon the relational
194. 9:21database management system that you use
195. 9:24some relational database Management
196. 9:26systems allow for General
197. 9:29rules saying okay this has to be a range
198. 9:33of this numbers some of them are more
199. 9:35vague where they where they uh only
200. 9:38allow you to implement data types data
201. 9:41types would be like integer 10
202. 9:44digits so when we talk about practically
203. 9:46implementing data Integrity different
204. 9:49relational database Management Systems
205. 9:51have different things you can do almost
206. 9:54every single relational database system
207. 9:57that I know of I'm pretty sure all of
208. 9:58them but who knows um they allow for
209. 10:02data
210. 10:04types so a data type it's pretty pretty
211. 10:09self-explanatory it's the type of
212. 10:15data so in database we classify things
213. 10:20as
214. 10:21generally
215. 10:22integer or text or date so we either
216. 10:27have numbers text text or dates that's
217. 10:31like three General categories which
218. 10:33we'll be talking about that more in a
219. 10:35future video but the data type can put
220. 10:37limits on what we're allowed to store
221. 10:40for example we can say okay we only
222. 10:43want a character
223. 10:47column with the maximum of 20
224. 10:53characters that means we can put a
225. 10:56string in the column that says
226. 11:00I
227. 11:02like pizza
228. 11:08yeah 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
229. 11:1316 17 that'll fit but if I wanted to
230. 11:16change this pizza to
231. 11:20lasagna well then I just added a couple
232. 11:22extra characters that might cut it off
233. 11:24I'm not sure 1 2 3 4 5 6 7 8 9 10 11 12
234. 11:2813 14 15 16 17 18
235. 11:3219 20 so that's the maximum we could put
236. 11:36in that data type that's an example of
237. 11:40implementing that data Integrity for the
238. 11:43uh
239. 11:44characters so this this will be for the
240. 11:47domain Integrity uh we can also have
241. 11:49referential integrity with what's known
242. 11:51as foreign key constraints which allows
243. 11:55us to connect tables because when we
244. 11:57draw tables we could say that they're
245. 11:59connected in some
246. 12:01way but how do we enforce that in a
247. 12:04database we use foreign key constraints
248. 12:07for example we can say if this is the um
249. 12:09the
250. 12:11parent let's say this is the
251. 12:15users and this is the
252. 12:20comments the user places a comment so
253. 12:24this is the parent this is the child
254. 12:26because the child can't exist without
255. 12:28the parent you can't have a comment
256. 12:30without a commenter well using foreign
257. 12:32key constraints we can say all right if
258. 12:35this user gets removed also remove all
259. 12:39of the comments by this user that's an
260. 12:42example of foreign key con strength as
261. 12:46for the non-re repeating data that is
262. 12:51done with just designing your database
263. 12:53in the best way so yeah I know that
264. 12:56video was kind of uh jump back and forth
265. 12:59from concept to concept and maybe a
266. 13:01little confusing but I'm sure you'll get
267. 13:03it as time goes on so yeah that's all I
268. 13:06have to say in this video thank you for
269. 13:07watching if you liked it please be sure
270. 13:09to subscribe and click like and share to
271. 13:13your friends on Facebook or Twitter or
272. 13:16Google Plus
273. 13:19or yeah that's all I know LinkedIn
274. 13:24yeah