1 / 41

CS634 Information Systems

CS634 Information Systems. Dr Deepak B Phatak Subrao Nilekani Chair Professor Kanwal Rekhi Building, Department of CSE IIT Bombay Session 3, Relational Model. Session overview. Review of earlier sessions Introduction to relational model Group projects. Entity model. sroom. shostel.

maxime
Download Presentation

CS634 Information Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS634 Information Systems Dr Deepak B Phatak Subrao Nilekani Chair Professor Kanwal Rekhi Building, Department of CSE IIT Bombay Session 3, Relational Model

  2. Session overview • Review of earlier sessions • Introduction to relational model • Group projects CS634-Session 3 Relational Model

  3. Entity model sroom shostel scpi sname scourses sroll Student shobbies CS634-Session 3 Relational Model

  4. Table representation … sroll sname shostel … 78011012 Ajay Shah 02 … 08 73815034 Nandan Nilekani CS634-Session 3 Relational Model

  5. Course entity ccredits cname ccode cstudents Course cfaculty CS634-Session 3 Relational Model

  6. TABLE REPRESENTATION ccode cname ccredit … … CS634 Modern Info Systems 06 … CS101 Computer Programming 08 CS634-Session 3 Relational Model

  7. Student and course association 98014012 98014015 99057943 99057961 99101491 99112345 CS634 CS101 ME203 EE102 Student Set Course Set CS634-Session 3 Relational Model

  8. Relationship set sroll Student Registers For Course ccode CS634-Session 3 Relational Model

  9. Relationship set attribute(s) sroll Student Grade Registers For Course ccode CS634-Session 3 Relational Model

  10. Introduction to relational model • Developed by professor Codd • Part of system-R work at IBM - 1972 • Mathematical foundation for database modeling • Relational algebra and calculus • Structured Query Language (SQL) • Structured English-like … (SEQUEL) CS634-Session 3 Relational Model

  11. Relational model • A relation is a set of n-tuples • Rows in a table • Each relation has a schema • List of attributes • Similar to column headers in a table CS634-Session 3 Relational Model

  12. Sample relation schema • Student = (sroll, sname, sh, sr, scpi) • Course = (ccode, cname, ccredit) • Reg = (sroll, ccode, grade) CS634-Session 3 Relational Model

  13. Student relation 98014012 … 98014015 … 99057943 … 99057961 … 99101491 … 99112345 … Number of elements = no. of students - Each element is a 5-tuple Student Set Course Set CS634-Session 3 Relational Model

  14. Student relation as a Table … sroll sname sh … 78011012 Ajay Shah 02 … 08 73815034 Nandan Nilekani CS634-Session 3 Relational Model

  15. Relational algebra • A relation is an element • Operators are defined over relation(s) • A relational operation results in a new relation having • Different set of rows • Different schema (may be) • Or both CS634-Session 3 Relational Model

  16. Keys • Super key: A set of attributes that uniquely characterizes a specific n-tuple • Candidate key: minimal super key • Primary key: chosen candidate key CS634-Session 3 Relational Model

  17. Examples of keys Student (sroll, sname, sh, sr, scpi) • Superkeys: (unique entity identifier) Sroll; Sroll + sname; Sh + sr; Sroll + sh + sr; … • Candidate keys are reduced minimal superkeys Sroll; sh + sr (single occupancy assumption) • Primary key: sroll (practical choice) CS634-Session 3 Relational Model

  18. Sample relation sroll sname sh sr scpi 06011012 Vijay Ambre 02 218 8.49 7.94 06D15034 Parag Samarth 08 218 06012015 Arul Menezes 03 146 9.25 06012018 Ajay Shah 03 151 8.38 06012014 Rajan Tambe 08 317 6.85 CS634-Session 3 Relational Model

  19. Sample relation sroll sname sh sr scpi 99011012 Vijay Ambre 02 218 8.49 01D15034 Shaukat Ali 08 218 7.94 98012015 Arul Menezes 03 146 9.25 98012018 Ajay Shah 03 151 8.38 98012014 Rajan Tambe 08 317 7.25 98012019 Sanjay Mistry 07 169 6.85 CS634-Session 3 Relational Model

  20. Sample relation sroll sname sh sr scpi 99011012 Vijay Ambre 02 218 8.49 01D15034 Shaukat Ali 08 218 7.94 98012015 Arul Menezes 03 146 9.25 98012018 Ajay Shah 03 151 8.38 98012014 Rajan Tambe 08 317 7.25 98012019 Sanjay Mistry 07 169 6.85 CS634-Session 3 Relational Model

  21. Relations • If any value in a table (or a file) changes (add, delete, update), such a change can be made in the same table or file • However, a relational operation results in a new relation • Relational algebra operations are used to extract meaningful information from one or more relations • Can be directly mapped into SQL CS634-Session 3 Relational Model

  22. Relational algebra operators • Selection • Get rows from a relation • Projection • Get columns from a relation • Cross product • Combine rows from multiple relations • Join • Get meaningful information from the cross product CS634-Session 3 Relational Model

  23. Selection • Extracts n-tuples from a relation R, based on a predicate (condition) P • R1 = p (R) • R1: has the same schema as that of R • P: valid condition on R CS634-Session 3 Relational Model

  24. Selection • R1 = scpi > 9 AND sh = 3 (student) • The resulting relation will have one row • [98012015 arul menezes 03 146 9.25] • SQL queryfor this relational operation can be written as • Select * • From student • Where scpi > 9 • and sh = 3; • ‘*’ means select ALL attributes of the schema CS634-Session 3 Relational Model

  25. Projection • Projects only specified attributes • Given as a list • R1 =  list (R) • R1 has a new schema • Cardinality (no of rows) ? CS634-Session 3 Relational Model

  26. Projection • R1 =  cname, ccredit (course) cname ccredit Information Systems 6 Data Mining 6 Professional Ethics in IT 3 • SQL query can be written as • Select cname, ccredit • from Course; CS634-Session 3 Relational Model

  27. Projection- another example R1 =  sh (student) SQL query: Select sh • From student; • How many elements would R1 have? CS634-Session 3 Relational Model

  28. Projection example sroll sname sh sr scpi 99011012 Vijay Ambre 02 218 8.49 01D15034 Shaukat Ali 08 218 7.94 98012015 Arul menezes 03 146 9.25 98012018 Ajay Shah 03 151 8.38 98012014 Rajan Tambe 08 317 6.85 CS634-Session 3 Relational Model

  29. SQL implementation of projection Select distinct sh From student; Result: Sh 2 8 3 CS634-Session 3 Relational Model

  30. Expressions •  Sname (scpi >9 & sh = 8 (student)) • Select sname • From student • Where scpi > 9 • And sh = 8; CS634-Session 3 Relational Model

  31. CROSS PRODUCT • Operates on two relations • R = R1 x R2 • Concatenates each tuple of R1 with every tuple of R2 • If there are M and N elements in the two relations, the resultant relation has M x N elements • Schema of R is concatenation of the two schemas of R1 and R2 • R1.A1, R1.A2, … , R2.A1, …, etc.. CS634-Session 3 Relational Model

  32. Example tables sroll sname - - 89005012 Sunita Sarawagi - - 89007017 Kesav Nori - - 89004039 Muthukrishnan - - - - - - sroll ccode - 89005012 CS413 - 89005012 CS634 - 89007017 CS634 - 89007017 CS413 - 89004039 HS412 - - - - Student Reg CS634-Session 3 Relational Model

  33. Cross product Student X Reg (First 8 Rows) sroll sname --- --- Reg.sroll ccode - 89005012 Sunita Sarawagi - - 89005012 CS413 -89005012 Sunita Sarawagi - - 89005012 CS634 - 89005012 Sunita Sarawagi - - 89007017 CS634 - 89005012 Sunita Sarawagi - - 89007017 CS413 - 89005012 Sunita Sarawagi - - 89004039 HS412 - 89007017 Kesav Nori - - 89005012 CS413 - 89007017 Kesav Nori - - 89005012 CS634 - 89007017 Kesav Nori - - 89007017 CS634 - CS634-Session 3 Relational Model

  34. Cross product Student X Reg (First 8 Rows) sroll sname --- --- sroll ccode - 89005012 Sunita Sarawagi - - 89005012 CS413 -89005012 Sunita Sarawagi - - 89005012 CS634 - 89005012 Sunita Sarawagi - - 89007017 CS634 - 89005012 Sunita Sarawagi - - 89007017 CS413 - 89005012 Sunita Sarawagi - - 89004039 HS412 - 89007017 Kesav Nori - - 89005012 CS413 - 89007017 Kesav Nori - - 89005012 CS634 - 89007017 Kesav Nori - - 89007017 CS634- CS634-Session 3 Relational Model

  35. Join • Join is defined to get meaningful set of rows from a cross product • R = ρ(R1 X R2) • Selection predicate provides for meaningful information extraction • Two categories of join • Natural join • Theta join CS634-Session 3 Relational Model

  36. Natural join • Ensures equality of values of all common attributes • Result schema is union of two schemas • R = r1 |x| r2 R = student |X| reg R = student.Sroll =reg.Sroll(student X reg) CS634-Session 3 Relational Model

  37. Cross product Student X Reg (First 8 Rows) sroll sname --- --- sroll ccode 89005012 Sunita Sarawagi - - 89005012 CS413 89005012 Sunita Sarawagi - - 89005012 CS634 89005012 Sunita Sarawagi - - 89007017 CS634 89005012 Sunita Sarawagi - - 89007017 CS413 89005012 Sunita Sarawagi - - 89004039 HS412 89007017 Kesav Nori - - 89005012 CS413 89007017 Kesav Nori - - 89005012 CS634 89007017 Kesav Nori - - 89007017 CS634 CS634-Session 3 Relational Model

  38. SQL query for natural join Select * from student natural join reg; Select * from student, reg Where student.Sroll = reg.Sroll; CS634-Session 3 Relational Model

  39. Natural join schema Student |X| Reg sroll sname --- --- sroll ccode 89005012 Sunita Sarawagi - - 89005012 CS413 -89005012 Sunita Sarawagi - - 89005012 IT640 - 89007017 Kesav Nori - - 89007017 IT640 - 89007017 Kesav Nori - - 89007017 CS413 - 89004039 Muthukrishnan - - 89004039 HS412 - CS634-Session 3 Relational Model

  40. Theta join • R = R1 |x| R2 • Theta is any (meaningful) predicate • Usually has a “join” condition • R =  (R1 x R2) • R = student |x|  reg •  : student.sroll = reg.sroll and scpi > 9 and sh = 8 CS634-Session 3 Relational Model

  41. SQL query for  join Select * from student, reg Where Student.Sroll = reg.Sroll and scpi > 9 and sh = 8; CS634-Session 3 Relational Model

More Related