Introduction to Relational Databases — Transcript

Full transcript

1. 0:00foreign
2. 0:07welcome back we are now in the next
3. 0:10chapter where we are going to focus on
4. 0:12relational databases in this
5. 0:15presentation we are going to focus on
6. 0:17the introduction to relational databases
7. 0:19in chapter 1 of this lecture series we
8. 0:23have seen a lot of data models what is a
9. 0:26data model it is the underlying
10. 0:28structure of a database and actually
11. 0:30this data model is a conceptual tool for
12. 0:33describing the data the relationship
13. 0:35among the data the data semantics and
14. 0:38the data constraints if you are directly
15. 0:41watching this lecture I request you to
16. 0:43navigate to the first chapter of this
17. 0:45playlist and please do watch the lecture
18. 0:47title data models and we have seen the
19. 0:50various categories of data models the
20. 0:52relational model The Entity relationship
21. 0:55model the object-based data model and
22. 0:57the semi-structured data model in this
23. 1:00chapter we are going to exclusively
24. 1:01focus on the relational databases where
25. 1:04relational databases follow those
26. 1:06relational model if you are clear with
27. 1:08relational model then understanding
28. 1:10relational databases is easy so why
29. 1:13waiting let's see the basic structure of
30. 1:16relational databases
31. 1:18what is a database basically a database
32. 1:21is a collection of tables and we know
33. 1:24what is a table a table is actually a
34. 1:26data structure that is used to describe
35. 1:28the data in rows and columns are tables
36. 1:31going to store only data no tables
37. 1:35represent both data and the relationship
38. 1:37I will explain you suppose if you have a
39. 1:40table with four attributes obviously we
40. 1:42store all the data in the tables and
41. 1:45this table is actually described by rows
42. 1:47and columns let's take I am talking
43. 1:49about a student table and this student
44. 1:51table contains multiple attributes or
45. 1:54columns let's take only two columns roll
46. 1:56number and name say for example if roll
47. 1:59number is 101 or 101 and the name of the
48. 2:03person is Muhammad Khan so if You
49. 2:05observe here the relationship is the
50. 2:07roll number one out one is pertaining to
51. 2:09the student Muhammad Khan or in other
52. 2:12words Muhammad Khan is having the roll
53. 2:14number 101 or 101 so what does it mean
54. 2:18this means there exists a relationship
55. 2:21among the values that are stored in the
56. 2:23table in other words when you have a row
57. 2:25each value in a row is having
58. 2:28relationship among other values and not
59. 2:31only this tables can also be used to
60. 2:33maintain the relationship among other
61. 2:36tables say for example an advisor table
62. 2:39can be used to maintain the relationship
63. 2:41between a student table and an
64. 2:43instructor table no worries I will
65. 2:46explain that shortly and that's why I
66. 2:48have mentioned here as tables represent
67. 2:51both the data and the relationship among
68. 2:54the data present in the table now in
69. 2:56relational model are we going to refer
70. 2:58this as table no we are going to refer
71. 3:01this as relation then why the term we
72. 3:04are using for representing tables as
73. 3:06relation in relational model because
74. 3:09there is a close association between the
75. 3:11dbms concept the table and the
76. 3:14mathematical concept the relation and
77. 3:16that's why in relational model we use
78. 3:19the term relation instead of referring
79. 3:21it as tables any way we will talk about
80. 3:23this elaborately in the coming lectures
81. 3:25for now just understand a table in
82. 3:28relational model or a table in
83. 3:30relational databases are referred as
84. 3:32relation basically relations are tables
85. 3:35but the important thing to note here is
86. 3:37it has a very close association with the
87. 3:40mathematical concept called relations
88. 3:43anyway you will understand about this
89. 3:45when we see the concepts relational
90. 3:47algebra and relational calculus in the
91. 3:49coming lectures for now just understand
92. 3:51a relation in relational database
93. 3:54management system is basically a table
94. 3:56only but we can apply many mathematical
95. 3:59operations on relations when you compare
96. 4:02with the table then what about the rows
97. 4:04and columns a row in a relation is
98. 4:07referred as Tuple and a column in a
99. 4:09relation is referred as attribute in a
100. 4:12table we represent rows and columns in
101. 4:14relation we represent it as Tuple and
102. 4:17attributes remember a tuple is basically
103. 4:20a row in a relation and coming to
104. 4:23attribute this is actually the column in
105. 4:26a relational database management system
106. 4:27and we have already talked about
107. 4:29instances and schemas what is an
108. 4:32instance the data present in the
109. 4:34database at a particular moment we call
110. 4:36it as an instance in relational database
111. 4:38we call it as relation instance what we
112. 4:41mean by this this relation instead sense
113. 4:43refers to a specific instance of a
114. 4:45relation it means a specific set of rows
115. 4:48for example if you are referring to a
116. 4:50specific set of rows in a relation then
117. 4:52this is referred as relation instance
118. 4:55and coming to the next important Concept
119. 4:57in relational database which is the
120. 4:59domain what is a domain it is actually a
121. 5:03set of permitted values I'll give you an
122. 5:06example let's take we are talking about
123. 5:08a banking database and let's assume this
124. 5:11banking database is a relational
125. 5:12database let's also assume this bank has
126. 5:15five branches only five branches the
127. 5:18branches are in New York Washington San
128. 5:21Francisco Chicago and Austin or whenever
129. 5:24we see a value in the database against
130. 5:26the branch then only these five values
131. 5:30are permitted what are the values New
132. 5:32York Washington San Francisco Chicago
133. 5:34and Austin let's say someone is trying
134. 5:37to insert a row into the table with the
135. 5:40value Los Angeles obviously Los Angeles
136. 5:42is not a set of permitted values because
137. 5:44there is no branch called Los Angeles
138. 5:46for that particular Bank in that case
139. 5:49that particular value is not belonging
140. 5:51to a set of values right so that is what
141. 5:54we call as a domain basically a domain
142. 5:57is a set of parameter values and please
143. 6:00see here an important question when a
144. 6:03domain is said to be atomic the answer
145. 6:06to this question should be known by all
146. 6:08database administrators because this is
147. 6:10going to be playing a crucial role in
148. 6:12relational databases so let's understand
149. 6:14what do you mean by atomic a domain is
150. 6:17said to be Atomic if the elements in the
151. 6:20domain are considered to be indivisible
152. 6:23let's say we have a column in the table
153. 6:25as the phone number this phone number if
154. 6:28it is considered as a single value then
155. 6:31it is an indivisible unit and we can say
156. 6:33that this phone number is atomic in
157. 6:35nature instead if we consider phone
158. 6:37number has multiple units like country
159. 6:40code area code and the local number in
160. 6:42that case this phone number is referred
161. 6:44as non-atomic so please be noted that
162. 6:47when a domain is said to be Atomic in
163. 6:49nature when the elements of the domain
164. 6:52are considered to be indivisible so in
165. 6:55the domain example we have taken Branch
166. 6:57name right the branch name that we have
167. 6:59taken our New York City Washington San
168. 7:01Francisco Chicago and Austin all these
169. 7:04are indivisible in nature so we can
170. 7:06quote that the values present in the
171. 7:08branch name is atomic in nature because
172. 7:11it cannot be divided further and another
173. 7:14important thing to note about the
174. 7:16relational database is this null values
175. 7:19what is the null value remember this
176. 7:21null value is not a blank value or empty
177. 7:24spaces or no value or no characters null
178. 7:28values in dbms is a special value which
179. 7:31represents that the value is either
180. 7:33unknown or that does not exist I'll give
181. 7:36you an example let's take if a student
182. 7:38has no phone number in that case we
183. 7:41cannot give 0 as the phone number
184. 7:43because 0 is not an all value at the
185. 7:45same time it may be added in the future
186. 7:48so a student who is not having a phone
187. 7:50number in the database the phone number
188. 7:53column for that particular student it
189. 7:55should be a null value because it is
190. 7:58either unknown or does not exist I will
191. 8:01elaborate about the concept of null
192. 8:03values when the course progresses and
193. 8:05I'll ask you a question will the
194. 8:07relations are generally sorted in nature
195. 8:09or unsorted in nature
196. 8:11the relations are generally unsorted in
197. 8:14nature the reason for this I'll explain
198. 8:16you in the following slides let's take
199. 8:18an example relation the example we are
200. 8:21going to take here is the instructor
201. 8:23relation which contains four attributes
202. 8:25instructor ID instructor name Department
203. 8:28name and salary and can you see here we
204. 8:31have some rows here this is actually a
205. 8:34tuple in relational database 34586 the
206. 8:37instructor ID is pertaining to John who
207. 8:40is working for the Department biology
208. 8:42who is drawing a salary of 65 000. now
209. 8:46the instructor ID of John is 34586 and
210. 8:5034586 is actually John and that's why I
211. 8:53told you a row in a table or a tuple in
212. 8:56a relation represents the relationship
213. 8:59among a set of values and this is about
214. 9:02the instructor relation I'll show you
215. 9:05one more relation the student relation
216. 9:07in the student relation we have four
217. 9:09attributes student ID student name
218. 9:11student department and the total credits
219. 9:14earned by the student so far we have
220. 9:17seen two relations instructor relation
221. 9:19and student relation let's assume these
222. 9:21two relations are a part of university
223. 9:23database what is a database it's a
224. 9:25collection of table in this case we are
225. 9:27assuming student table or student
226. 9:29relation and instructor relation are
227. 9:31part of university database how the
228. 9:34relationship between the tables or
229. 9:35relations are established it is also
230. 9:38achieved using another relation I'll
231. 9:41show you that now in this case we can
232. 9:43see that the advisor is a relation that
233. 9:46contains exactly two columns the student
234. 9:48ID and the instructor ID the advisor for
235. 9:51the student who is having the student ID
236. 9:53103 is 25252 what do we mean by this it
237. 9:58means I'll go to the previous slide 103
238. 10:01is B and the instructor for the student
239. 10:04B is Alia
240. 10:0725252 that is what is mentioned here the
241. 10:11student with the student ID 103 is B and
242. 10:14the student base advisor is alya who is
243. 10:17having the instructor ID 25252 and this
244. 10:20is how the relationship among multiple
245. 10:22tables or relations are maintained in
246. 10:25the relational database management
247. 10:26system
248. 10:27before we sign out let's see a homework
249. 10:30question here is the course relation
250. 10:32this course relation has course ID the
251. 10:35title the department name and credits
252. 10:37and what I am asking you to do is just
253. 10:40try to figure out the relationship
254. 10:42between the student relation or the
255. 10:44instructor relation with this course
256. 10:46relation
257. 10:47so this will definitely help you how
258. 10:50these relationships are maintained in
259. 10:52the database also I am going to answer
260. 10:54you one more question whether the
261. 10:56relations are sorted or unsorted if you
262. 10:59see here I have just shown you an
263. 11:01example where the course ID is actually
264. 11:03sorted here because this course ID is
265. 11:06actually sorted alphabetically B biology
266. 11:09followed by computer science followed by
267. 11:11electrical engineering followed by
268. 11:13Finance followed by history followed by
269. 11:16music and then physics so this is
270. 11:18alphabetically sorted in real time
271. 11:21databases need not be sorted let's say I
272. 11:24am going to insert a new row to this
273. 11:26course relation let's say the name of
274. 11:28the subject is cryptography and network
275. 11:30security and obviously it will be a part
276. 11:33of the computer science department but
277. 11:36do you think that this new row will be
278. 11:37inserted between this and this no and
279. 11:41that's why I told you in real time the
280. 11:43relations are not sorted in the storage
281. 11:45only it may not be sorted but while
282. 11:48displaying you can sort and display
283. 11:51and that's it guys I hope you guys
284. 11:53enjoyed this presentation and thank you
285. 11:55for watching
286. 11:56[Music]
287. 11:57[Applause]
288. 11:59[Music]