Entity-Relationship Model/1 — Transcript

Full transcript

1. 0:07so
2. 0:09[Music]
3. 0:16welcome to module 13 of database
4. 0:19management systems
5. 0:21in this module and the next two
6. 0:23we will discuss about entity
7. 0:26relationship model
8. 0:28so far we have
9. 0:31had a good look into the sql language
10. 0:33the query language
11. 0:35and
12. 0:36its formal basis in terms of relational
13. 0:39algebra and calculate
14. 0:42in this module we will and try to
15. 0:44understand the design process for
16. 0:46database systems
17. 0:48because so far whatever we have done
18. 0:51we have assumed that the schema is known
19. 0:54to us
20. 0:55that some instance is given to us and
21. 0:58then we have tried to
22. 1:01extract different query information
23. 1:04from the relation but now we will look
24. 1:07into how do we
25. 1:10model the real world and actually get
26. 1:12into the design process
27. 1:15so after an overview of the design
28. 1:17process
29. 1:18we would
30. 1:19study
31. 1:20entity relationship model
32. 1:23which is used to represent the real
33. 1:26world whatever exist in the real world
34. 1:28that will have to be represented
35. 1:31for our use and
36. 1:34final
37. 1:36representation in terms of different
38. 1:38relations
39. 1:41the design process at an abstract level
40. 1:43the initial
41. 1:44phase of database design
42. 1:46certainly has to characterize what data
43. 1:50is required to be maintained
44. 1:52for an enterprise
45. 1:54so
46. 1:55whether i am doing if i am doing an
47. 1:57university database naturally we will
48. 1:59need to identify that what are the data
49. 2:02needs the students need to be described
50. 2:04the
51. 2:05instructors need to be described the
52. 2:07courses sections time slots grades
53. 2:10examinations etcetera but if i am trying
54. 2:13to deal with a world which is ah say
55. 2:17railway reservation then i will need to
56. 2:20deal with the stations trains
57. 2:23dates births
58. 2:25the different classes of coach that the
59. 2:28train has and so on so the initial phase
60. 2:30is to characterize the data requirement
61. 2:34next the designer has to choose a data
62. 2:37model
63. 2:38because
64. 2:39unless we can
65. 2:41we cannot
66. 2:43deal with
67. 2:44a natural language or english kind of
68. 2:46description
69. 2:48and
70. 2:49work towards getting a particular schema
71. 2:53so we will need to use a data model
72. 2:56and
73. 2:57apply the concepts of the data model
74. 2:59that we choose
75. 3:01and translate the requirements into what
76. 3:03is known as a conceptual schema of the
77. 3:06database
78. 3:07which is not a not a very concrete one
79. 3:09but a conceptual one this is what
80. 3:11grossly what i want to do
81. 3:14and a fully developed conceptual schema
82. 3:17will indicate
83. 3:18my functional requirements
84. 3:21in terms of what usually is called
85. 3:24a specification of functional
86. 3:27requirements
87. 3:28system requirements
88. 3:31it will specify
89. 3:33what kind of users
90. 3:35will be involved what kinds of
91. 3:37operations transactions will be
92. 3:38performed and so on
93. 3:41now once we have that
94. 3:43kind of a conceptual model that abstract
95. 3:47that is a conceptual more abstract data
96. 3:49model
97. 3:50will go to the next phase of the design
98. 3:54which is finding out
99. 3:56what is the more concrete design through
100. 4:00a process of logical design
101. 4:03in the process of logical design we will
102. 4:05first decide on the database schema
103. 4:09we need to decide on what is a good
104. 4:12schema so there are
105. 4:15principles to say that
106. 4:17what is good and what is not so good
107. 4:21we need to make business decisions to
108. 4:23find out which attributes we record in
109. 4:26the database
110. 4:27we need to make computer science
111. 4:30decision as to how the
112. 4:33relational
113. 4:34schemas will be interrelated between
114. 4:36themselves how the attributes will be
115. 4:39distributed
116. 4:42and at a last phase
117. 4:44we need to also decide on the physical
118. 4:47design
119. 4:48which will tell us
120. 4:50what is the physical layout of the data
121. 4:52so conceptual design
122. 4:55refined into logical design
123. 4:58finalized with physical design is our
124. 5:01gross process of design
125. 5:04now in this
126. 5:06for the conceptual design
127. 5:08we primarily follow
128. 5:11a model called entity relationship model
129. 5:15that tries to identify the collection of
130. 5:18entities and relationships
131. 5:21an entity is nothing but its is is an
132. 5:25object is a thing
133. 5:27that is distinguishable from
134. 5:30other objects so if i say
135. 5:32that
136. 5:34student is an entity then a student is
137. 5:36distinguishable from
138. 5:38another entity course
139. 5:42both of them are distinguishable from a
140. 5:44third entity instructor and so on
141. 5:48so every entity for the purpose of
142. 5:50distinction is described by a set of
143. 5:53attributes or properties
144. 5:56and these
145. 5:57entities
146. 5:59will
147. 6:00have relations between them for example
148. 6:03you can say that a course
149. 6:04will be attended by students
150. 6:08students will be advised by instructors
151. 6:12so this
152. 6:13attended by advised by these are
153. 6:16relationships or association
154. 6:18between several entities
155. 6:21and the model which represents
156. 6:24initially diagrammatically and then
157. 6:27in textual form
158. 6:29this kind of relationship is known as
159. 6:31the
160. 6:32entity relationship model or the entity
161. 6:34relationship diagram
162. 6:37we will then use it to get a relational
163. 6:41set of relational schema
164. 6:43which subsequently we normalize
165. 6:46the normalization is nothing but
166. 6:48refinement of the design
167. 6:50which improves
168. 6:52a design to make it better in terms of
169. 6:56correctness in terms of ease of
170. 6:58manipulation
171. 7:00performance and so on
172. 7:02so
173. 7:03it basically
174. 7:07removes bad designs
175. 7:09from the database
176. 7:10and
177. 7:11converts them into good designs we will
178. 7:13talk about this normalization theory
179. 7:16later in the course
180. 7:18right now we are interested only in the
181. 7:21entity relationship model which will be
182. 7:23used for conceptual design
183. 7:26and then
184. 7:27will give us the basis for the logical
185. 7:30design in terms of the schemas
186. 7:33so let us
187. 7:36take a deeper look into the entity
188. 7:38relationship model
189. 7:40and entity relationship model as i said
190. 7:42is developed to facilitate the database
191. 7:45design
192. 7:47get the overall logical structure it is
193. 7:50useful in mapping
194. 7:52the meaning and interactions of the real
195. 7:54world in terms of certain
196. 7:56diagrammatic schemas
197. 7:59and it employs three basic concepts
198. 8:02entities or entity sets we talked about
199. 8:04entities
200. 8:06all entities that share the same set of
201. 8:09properties like
202. 8:10if student is an entity
203. 8:12then the collection of student is an
204. 8:14entity set a instructor is an entity
205. 8:17collection of
206. 8:18instructors is an entity set so all
207. 8:21entities in an entity set will share the
208. 8:23same set of attributes
209. 8:26we will have relationship sets which
210. 8:28define relationship between multiple
211. 8:31entity sets
212. 8:33and certainly in the process will use
213. 8:34make use of attributes these are the
214. 8:36three key components of an er model
215. 8:42it also has a er diagram as we will show
216. 8:46soon
217. 8:48so as already defined entity is an
218. 8:50object that exists and is
219. 8:52distinguishable from other objects
220. 8:55entity set is a set of entities of the
221. 8:57same type that share the same properties
222. 9:00and an entities is represented by the
223. 9:03set of attributes or properties that
224. 9:05describe it
225. 9:07so when we say instructor for example if
226. 9:10we say here these are my attributes
227. 9:14you have already
228. 9:15learned this in terms of studying sql so
229. 9:18it has there has five attributes and
230. 9:21these five attributes together
231. 9:23or the values of these five attributes
232. 9:25for a particular instructor
233. 9:27defines my entity set instructor
234. 9:30collection of these attributes define my
235. 9:32entity set courses so these are my
236. 9:34different entity sets
237. 9:36that
238. 9:37exist that can be defined
239. 9:42so a subset of attributes
240. 9:45in the entity set
241. 9:47forms a key
242. 9:49called the primary key
243. 9:52which can uniquely identify every entity
244. 9:54in that entity set we have already been
245. 9:57familiar with this concept of primary
246. 9:59key
247. 10:00the same concept continues
248. 10:03so these are examples of ah entity sets
249. 10:06instructor with two attributes and
250. 10:08student with two attributes as well
251. 10:12a relationship is an association
252. 10:15among
253. 10:17two or more entities
254. 10:20so
255. 10:21here we have an entity
256. 10:24here shown as a student this is a
257. 10:26student entity
258. 10:28identified by the student id which is a
259. 10:32primary key in the student entity set
260. 10:36we have an instance of an instructor
261. 10:38entity
262. 10:39identified by the
263. 10:41id
264. 10:42of the instructor einstein
265. 10:45which identifies any instructor uniquely
266. 10:50and then
267. 10:51advisor is a relationship set
268. 10:55which relates these two
269. 10:57so what i we want to mean is
270. 11:01if i say advisor
271. 11:03relates
272. 11:05four four five five three to
273. 11:07two to two to two
274. 11:10what i want to mean is
275. 11:13peltier the student peltier is advised
276. 11:16by the instructor einstein
277. 11:19so
278. 11:20whenever we relate
279. 11:21two or more entity sets like this
280. 11:24we get relationships so a relationship
281. 11:28is a mathematical relation among
282. 11:31more than two or more entities
283. 11:33each taken from the entity set so you
284. 11:36can see that it can have components e
285. 11:38one e two
286. 11:39e n n entity sets and
287. 11:43each entity e one should belong to
288. 11:45entity set capital e one
289. 11:47e two should belong to entity set
290. 11:49capital e two and so on and is called a
291. 11:52relationship we have already seen the
292. 11:54advisor relationship as above
293. 11:58so here what we show is a relationship
294. 12:02advisor by these arrows
295. 12:04ah these lines so what he is showing is
296. 12:07this connection between these two show
297. 12:10that
298. 12:11this student is advised by this
299. 12:13instructor
300. 12:15whereas you can see so crick advises
301. 12:18tanaka whereas shankar and zhang
302. 12:21both are advised by cuts
303. 12:23so
304. 12:24this
305. 12:25group of associations between
306. 12:29instructor and student is a gives me the
307. 12:32relationship advisor as to who advises
308. 12:35whom
309. 12:38a relationship also
310. 12:40like the entity sets the relationship
311. 12:42also can have some additional attribute
312. 12:44for example
313. 12:46when i say that crick advises tanaka i
314. 12:49may associate an attribute date type
315. 12:52attributes at third may 2008
316. 12:55to mean
317. 12:56that when did this
318. 12:59process of quick advising tanaka started
319. 13:02we can it can be some other attribute
320. 13:04also so all that i am trying to
321. 13:06highlight is
322. 13:08attributes can be assigned to
323. 13:10relationships as well
324. 13:14now
325. 13:15how will a relationship span out
326. 13:19we have said that a relationship must
327. 13:20involve two
328. 13:22entity sets so primarily relationships
329. 13:25are binary it involves two
330. 13:27and most ah relationships in most
331. 13:30databases are binary in nature
332. 13:33but it could be that there are we will
333. 13:36see later that there are possibilities
334. 13:39of having relationships which are
335. 13:41ah
336. 13:42more than binary ternary and higher
337. 13:45so
338. 13:46here are examples students work on
339. 13:48research projects under the guidance of
340. 13:51an instructor
341. 13:53so here we have as you can see students
342. 13:57research projects and instructors so
343. 13:59there are three entity sets so if i want
344. 14:01to maintain a relationship of say
345. 14:04project guidance between them then that
346. 14:06turns out to be a ternary relationship
347. 14:09we will talk about this more later
348. 14:14there are constraints in terms of the
349. 14:17cardinality of the relationship
350. 14:20the cardinality basically talks of that
351. 14:23when we have
352. 14:24when i have
353. 14:29a relation entity set e one
354. 14:31and identity set e two
355. 14:34so there are different entities
356. 14:36in them
357. 14:37and i have
358. 14:39different
359. 14:40associations between them
360. 14:42then the question is
361. 14:45how many of
362. 14:46the entity of one entity set is related
363. 14:49to how many of the entities of the other
364. 14:51entity set
365. 14:54and
366. 14:54certain types of cardinality measures
367. 14:57are very important to track
368. 15:00and
369. 15:00we say it is whether it is one to one
370. 15:02one to many many to one or many too many
371. 15:06so here are the examples or or the
372. 15:09schematics so in the first one in the
373. 15:11diagram a
374. 15:12you see that every entity from the
375. 15:14entity set a relates to exactly one
376. 15:17entity in the entity set b or you can
377. 15:19say at most one entity in the entity set
378. 15:21b
379. 15:23similarly every entity in entity set b
380. 15:25relates to exactly one entity in
381. 15:28entities at a or at most one entity in
382. 15:30entities at a
383. 15:31if this holds then we say this
384. 15:33relationship is one to one
385. 15:36whereas in diagram b you see that a 1
386. 15:39relates to b 1 as well as b 2 a 2
387. 15:41relates to b 3 as well as b 4. so one
388. 15:44entity in a relates to more than 1
389. 15:47entity may relate to more than 1 entity
390. 15:49in b but
391. 15:51if you look from b side
392. 15:53every entity in b is related to at most
393. 15:56one entity in a
394. 15:58then we say from a to b it is one to
395. 16:00many
396. 16:02now naturally since i can put the
397. 16:04relations in any order
398. 16:06ah as we have one too many if you look
399. 16:09in the other direction it becomes many
400. 16:11to one so many to one is from a to b
401. 16:14many to one is where more than one
402. 16:16entity in set a may relate to one entity
403. 16:18inside b but all entities in set b
404. 16:21relates to at most one entity inside a
405. 16:24and when there is no restriction at all
406. 16:26that is any number of entities in set a
407. 16:28may relate to any number of entities in
408. 16:30set b
409. 16:32and
410. 16:32any number of entities in set b may
411. 16:35relate to any number of entities in set
412. 16:36a we say it is a many to many relation
413. 16:39so we have one too many one to one we
414. 16:42have one too many and many to one and we
415. 16:44have many too many and it often helps in
416. 16:46the design to be able to characterize
417. 16:48which type of relationship we do have
418. 16:52coming to the attributes we can note
419. 16:54that attributes are of different types
420. 16:56one is they could be simple or composite
421. 16:58a simple attribute is just
422. 17:00one single domain value like a salary
423. 17:03number like an id like a name string and
424. 17:06so on
425. 17:06whereas a composite attribute
426. 17:09may comprise of multiple
427. 17:11parts
428. 17:13so
429. 17:15consider this this is a composite
430. 17:16attribute so name is an attribute if i
431. 17:19think of
432. 17:20then it has different parts it has a
433. 17:22first name middle name last name if i
434. 17:25think of address
435. 17:26it has so many different parts then
436. 17:28street itself has so many different
437. 17:29parts
438. 17:30so whenever an attribute is
439. 17:33comprise
440. 17:34some
441. 17:36more of the components when it is not a
442. 17:39simple value then it is called a
443. 17:41composite attribute
444. 17:43we will see how to handle that
445. 17:46then some attributes may be single
446. 17:48valued for example a person has a has
447. 17:50one name let us say
448. 17:52but has one address
449. 17:55but may have two or more phone numbers
450. 17:58the attributes which can take more than
451. 18:00one value is known to be multi valued
452. 18:02attribute
453. 18:04so we also need to specify whether
454. 18:06certain
455. 18:08specify in the design whether certain
456. 18:10attributes are single valued or multiple
457. 18:12valued multi valued of course single
458. 18:15valued attributes are easy to deal with
459. 18:16if it is multivalued we need to do some
460. 18:18design changes
461. 18:21certain attributes can be derived
462. 18:23for example age
463. 18:25now i cannot keep the age of some a
464. 18:28person in the database because with
465. 18:30every day the age changes
466. 18:32so what will typically keep is the date
467. 18:34of birth and the age is computed
468. 18:37on the day when the particular query is
469. 18:41made to find out what the edge is
470. 18:43so it is called a derived attribute and
471. 18:45each one of them will have corresponding
472. 18:47set of domains
473. 18:51some attributes in the design may turn
474. 18:53out to be redundant also consider this
475. 18:56you have already seen this this is an
476. 18:58instructor
477. 18:59which has a department name along with
478. 19:02the different attributes and certainly i
479. 19:04have a department table
480. 19:07so which department relation which gives
481. 19:09the details of the department now
482. 19:12since every instructor belongs to a
483. 19:14department so naturally
484. 19:17we might want to have a
485. 19:22relation ins
486. 19:24department
487. 19:26which could give
488. 19:27the instructor and
489. 19:29his or her department name
490. 19:32so if we maintain that
491. 19:34then
492. 19:35this becomes
493. 19:36a redundant attribute
494. 19:38this is not required
495. 19:40because it that information is already
496. 19:42there in this relation
497. 19:45so
498. 19:46in several cases there is a question of
499. 19:49whether
500. 19:50we maintain some information in terms of
501. 19:53a relation
502. 19:54or
503. 19:55we can
504. 19:57make that
505. 19:59directly include that directly in the
506. 20:02entity set
507. 20:04and get rid of that relation so if i
508. 20:06have
509. 20:07the ins depth relation
510. 20:09and then the attribute department name
511. 20:11appears on both these sets
512. 20:14instead as well as on the
513. 20:17instructor and there is duplication
514. 20:20replication of the data which we would
515. 20:21want to avoid
516. 20:24but we will see the different cases when
517. 20:27which style of design whether we would
518. 20:29be better to maintain the department
519. 20:31name as a part of the instructor
520. 20:35relation or it would be better not to
521. 20:37have it there and have a separate
522. 20:39relation which maps instructor id
523. 20:42against the department name
524. 20:46finally comes a concept of
525. 20:48weak entity sets you need to understand
526. 20:50this a little bit consider the
527. 20:53university database example
528. 20:56so we have courses
529. 20:58we have students
530. 21:01we have ah
531. 21:03instructors
532. 21:05and we have section
533. 21:07a section is
534. 21:09if a course is large
535. 21:11then
536. 21:12it needs to be taught in multiple
537. 21:15sections
538. 21:17so for the same course at the same
539. 21:20semester in the same year i may have
540. 21:23different sections
541. 21:24in which the students are divided and
542. 21:26naturally there could be multiple
543. 21:28instructors
544. 21:30each teaching
545. 21:32one section of that course and students
546. 21:34will be distributed
547. 21:36on the sections not on the course
548. 21:39now consider this section entity if you
549. 21:41look into this then
550. 21:43this is how
551. 21:44what we we maintained we did a course id
552. 21:48semester year and section id
553. 21:51but
554. 21:52if you look into specifically
555. 21:54and if you want to now no you know that
556. 21:58there is a section and there is a course
557. 22:00so you may want to
558. 22:04relate these two
559. 22:10section with the course
560. 22:13and
561. 22:14set up an entity between them
562. 22:17so what will it relate
563. 22:18it will relate
564. 22:20the course id of the course with all of
565. 22:23these
566. 22:24but the course id is already there as a
567. 22:26part of the section
568. 22:28right
569. 22:30so you would say that well it is not
570. 22:32required to have the course id
571. 22:35ah since it already has that and it
572. 22:40identifies it
573. 22:42so
574. 22:44we can can we remove this
575. 22:47course id
576. 22:49from here
577. 22:52well if we remove the course id now we
578. 22:54have a different problem
579. 22:56if you remove the course id
580. 22:58then you have section id semester and
581. 23:00year but this does not uniquely
582. 23:03represent the tuples of this relation
583. 23:07because
584. 23:08there could be two section is
585. 23:11in the same semester in the same year
586. 23:14for two different courses how do you
587. 23:16distinguish them
588. 23:19so
589. 23:22you get into a situation where
590. 23:25the
591. 23:26course
592. 23:27the section
593. 23:31gets
594. 23:32identified
595. 23:38uniquely
596. 23:40provided
597. 23:42either you know
598. 23:43the relationship between the
599. 23:45section
600. 23:47and the course
601. 23:49in terms of the sec course relationship
602. 23:52or you include the primary key
603. 23:56of course
604. 23:57into
605. 23:59the
606. 24:01relation section which we did in the
607. 24:03design
608. 24:06and this is not a coincidence this is
609. 24:08something which happens regularly
610. 24:10and
611. 24:11is
612. 24:13is a characteristics
613. 24:15that
614. 24:16specify the existence of weak entity
615. 24:20sets
616. 24:22so
617. 24:24the weak entity set
618. 24:26is one
619. 24:28whose existence depends on another
620. 24:30entity set so if i just say section
621. 24:33having section id year and semester then
622. 24:36it is not uniquely specified until
623. 24:41i have a relation
624. 24:42section course which relates the section
625. 24:46to the particular course id
626. 24:49when such relationships are used to
627. 24:52identify entities of a particular entity
628. 24:55set
629. 24:56then
630. 24:58the unique side the core side which is
631. 25:01unique
632. 25:02is known as the identifying entity
633. 25:05and the other attributes
634. 25:08in this case section id year
635. 25:10semester are known as the discriminators
636. 25:14so we have a relationship
637. 25:17between a weak entity set
638. 25:26which is section
639. 25:29we have a strong entity set which is
640. 25:31course
641. 25:32why is it strong because course is
642. 25:34identified by course id itself
643. 25:37section is not
644. 25:39unless
645. 25:41you have a
646. 25:42sec course
647. 25:44kind of
648. 25:46relationship
649. 25:48set between the course and the section
650. 25:50which specifies that well
651. 25:52for this course this is the section this
652. 25:54is the here this is the semester
653. 25:58so
654. 25:59this is the identifying entity through
655. 26:01which
656. 26:02the entities of this set
657. 26:05gets
658. 26:06specified
659. 26:08and whenever that situation happens
660. 26:11then
661. 26:12we say that we have a weak entity set
662. 26:17so weak entity sets naturally cannot
663. 26:19happen by themselves
664. 26:21they are existence dependent on
665. 26:24identifying entity set
666. 26:27and the identifying entity set
667. 26:30owns the weak entity set so the courses
668. 26:32in that way own
669. 26:34the section
670. 26:36and
671. 26:37the identifying relationship between
672. 26:40them
673. 26:40is
674. 26:41necessary to uniquely identify every
675. 26:45entity
676. 26:46of this weak entity set or section in
677. 26:49our case
678. 26:50so this notion is ah very important for
679. 26:54the design as we will see
680. 26:57that the relational schema that we
681. 26:59eventually created in this case
682. 27:01from the entity set section
683. 27:04we did include course id
684. 27:08as a part of the primary key
685. 27:12not using
686. 27:13the
687. 27:15sec course kind of relationship and we
688. 27:17will show
689. 27:18how this design style for
690. 27:21dealing with entity weak entity sets
691. 27:24influences the different database
692. 27:27designs
693. 27:28so weak entity sets are critical notions
694. 27:30that you need to be
695. 27:32aware of need to be confident of
696. 27:37so in summary
697. 27:38we have introduced the design process
698. 27:40for database systems i will just quickly
699. 27:43recap
700. 27:44the first stage is identifying the data
701. 27:46items which is leading to the conceptual
702. 27:49design
703. 27:51which will primarily do in terms of the
704. 27:53entity relationship model identifying
705. 27:55the entities the entity
706. 27:58sets
707. 27:59the attributes that
708. 28:01define the entity set describe the
709. 28:03entity set
710. 28:04the subset of attributes forming primary
711. 28:07key that uniquely specifies every entity
712. 28:09set
713. 28:10every entity in the entity set
714. 28:13and the relationships typically binary
715. 28:16may be non-binary also
716. 28:18relationships that hold between the
717. 28:20different entity sets
718. 28:22so this is the conceptual design that
719. 28:25will lead to
720. 28:26more detailed logical design of
721. 28:29how the relationship should be organized
722. 28:31what is the cardinality of that what
723. 28:34kind of attributes do i have whether it
724. 28:36is simple whether it is composite
725. 28:38whether
726. 28:40certain attributes are
727. 28:42derived or not so all those ah different
728. 28:46aspects will have to be detailed out
729. 28:48and
730. 28:49we need to identify what are the weak
731. 28:51entity sets and what are the strong
732. 28:53entity sets what are the identifying
733. 28:56entities
734. 28:57and
735. 28:59with that we could complete the logical
736. 29:02design
737. 29:03and then we will need to make it
738. 29:06in terms of a express it in terms of a
739. 29:08relational schema
740. 29:10so
741. 29:11in this module we have just taken a look
742. 29:13in the first part the entity
743. 29:14relationship model
744. 29:16and the
745. 29:17very basic of how the conceptual design
746. 29:20will go forward
747. 29:22in the model we have seen all the
748. 29:24different primitives required to
749. 29:26represent the reality represent what
750. 29:29holds in the real world