1 / 201

Temple University – CIS Dept. CIS616– Principles of Database Systems

Temple University – CIS Dept. CIS616– Principles of Database Systems. V. Megalooikonomou Relational Model (based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU). Overview. history concepts Formal query languages relational algebra rel. tuple calculus

lars-kerr
Download Presentation

Temple University – CIS Dept. CIS616– Principles of Database 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. Temple University – CIS Dept.CIS616– Principles of Database Systems V. Megalooikonomou Relational Model (based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU)

  2. Overview • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus

  3. History • before: records, pointers, sets etc • introduced by E.F. Codd (1923-2003) in 1970 • revolutionary!!! • first systems: 1977-8 (System R; Ingres) • Turing award in 1981

  4. Concepts • Database: a set of relations (= tables) • rows: tuples • columns: attributes (or keys) • superkey, candidate key, primary key

  5. Example Database:

  6. Example: cont’d Database: k-th attribute (Dk domain) rel. schema (attr+domains) tuple

  7. Example: cont’d rel. schema (attr+domains) instance

  8. Example: cont’d • Di: the domain of the I-th attribute (eg., char(10) • Formally: an instance is a subset of (D1 x D2 x …x Dn) rel. schema (attr+domains) instance

  9. Example: cont’d • superkey (eg., ‘ssn , name’): determines record • cand. key (eg., ‘ssn’, or ‘st#’): minimal superkey (no subset of it is a superkey) • primary key: one of the cand. keys

  10. Another example • Example: if Customer-name = {Jones, Smith, Curry, Lindsay}Customer-street = {Main, North, Park}Customer-city = {Harrison, Rye, Pittsfield} Then r = { (Jones, Main, Harrison), (Smith, North, Rye), (Curry, North, Rye), (Lindsay, Park, Pittsfield)} is a relation over Customer-name x Customer-street x Customer-city

  11. Relations, tuples • Relation Schema: • A1, A2, …, Anare attributes • R = (A1, A2, …, An ) is a relation schema E.g. Customer-schema = (customer-name, customer-street, customer-city) • r(R) is a relation on the relation schema R E.g. customer (Customer-schema) • Relations are unordered • Order of tuples is irrelevant (tuples may be stored in an arbitrary order)

  12. Database • A database consists of multiple relations • Information about an enterprise is broken up into parts, with each relation storing one part of the informationE.g.: account : stores information about accountsdepositor : stores information about which customer owns which account customer : stores information about customers • Storing all information as a single relation such as bank(account-number, balance, customer-name, ..)results in • repetition of information (e.g. two customers own an account) • the need for null values (e.g. represent a customer without an account) • Normalization theory (discuss later) deals with how to design relational schemas

  13. Overview • history • concepts • Formal query languages • relational algebra • rel. tuple calculus • rel. domain calculus

  14. Formal query languages • How do we collect information? • Eg., find ssn’s of people in cis331 • (recall: everything is a set!) • One solution: Relational algebra, i.e., set operators (procedural language) • Q1: Which operators?? • Q2: What is a minimal set of operators?

  15. Relational operators • . • . • . • set union U • set difference ‘-’

  16. Example: • Q: find all students (part or full time) • A: PT-STUDENT union FT-STUDENT

  17. Observations: • two tables are ‘union compatible’ if they have the same attributes (i.e., same arity: number of attributes and same ‘domains’) • Q: how about intersection ? U

  18. Observations: • A: redundant: • STUDENT intersection STAFF = STUDENT - (STUDENT - STAFF) STAFF STUDENT

  19. Relational operators • . • . • . • set union • set difference ‘-’ U

  20. Other operators? • E.g., find all students on ‘Main street’ • A: ‘selection’

  21. Other operators? • Notice: selection (and rest of operators) expect tables, and produce tables • --> can be cascaded!! • For selection, in general:

  22. Selection - examples • Find all ‘Smiths’ on ‘Forbes Ave’ ‘condition’ can be any boolean combination of ‘=‘, ‘>’, ‘>=‘, ...

  23. Relational operators • selection • . • . • set union • set difference R - S R U S

  24. Relational operators • selection picks rows - how about columns? • A: ‘projection’ - eg.: finds all the ‘ssn’ - removing duplicates

  25. Relational operators Cascading: ‘find ssn of students on ‘forbes ave’

  26. Relational operators • selection • projection • . • set union • set difference R - S R U S

  27. Relational operators Are we done yet? Q: Give a query we can not answer yet!

  28. TAKES SSN c-id grade 123 cis331 A 234 cis331 B Relational operators A: any query across two or more tables, eg., ‘find names of students in cis351’ Q: what extra operator do we need?? A: surprisingly, the cartesian product is enough!

  29. Cartesian product • E.g., dog-breeding: MALE x FEMALE • gives all possible couples = x

  30. TAKES SSN c-id grade 123 cis331 A 234 cis331 B so what? • Eg., how do we find names of students taking cis351?

  31. Ssn Name Address ssn cid grade 123 smith main str 123 cis331 A 234 jones forbes ave 123 cis331 A 123 smith main str 234 cis331 B 234 jones forbes ave 234 cis331 B Cartesian product • A:

  32. Cartesian product Ssn Name Address ssn cid grade 123 smith main str 123 cis331 A 234 jones forbes ave 123 cis331 A 123 smith main str 234 cis331 B 234 jones forbes ave 234 cis331 B

  33. Ssn Name Address ssn cid grade 123 smith main str 123 cis331 A 234 jones forbes ave 123 cis331 A 123 smith main str 234 cis331 B 234 jones forbes ave 234 cis331 B

  34. FUNDAMENTALRelational operators • selection • projection • cartesian product MALE x FEMALE • set union • set difference R - S R U S

  35. Relational ops • Surprisingly, they are enough, to help us answer almost any query we want!! • derived operators, for convenience • set intersection • join (theta join, equi-join, natural join) • ‘rename’ operator • division

  36. Joins • Equijoin:

  37. Cartesian product • A: Ssn Name Address ssn cid grade 123 smith main str 123 cis331 A 234 jones forbes ave 123 cis331 A 123 smith main str 234 cis331 B 234 jones forbes ave 234 cis331 B

  38. Joins • Equijoin: • theta-joins: generalization of equi-join - any condition

  39. Joins • very popular: natural join: RS • like equi-join, but it drops duplicate columns: STUDENT(ssn, name, address) TAKES(ssn, cid, grade)

  40. Joins • nat. join has 5 attributes Ssn Name Address ssn cid grade 123 smith main str 123 cis331 A 234 jones forbes ave 123 cis331 A 123 smith main str 234 cis331 B 234 jones forbes ave 234 cis331 B equi-join: 6

  41. Natural Joins - nit-picking • if no attributes in common between R, S: • nat. join -> cartesian product:

  42. Overview - rel. algebra • fundamental operators • derived operators • joins etc • rename • division • examples

  43. rename op. • Q: why? • A: • Shorthand (BEFORE can be a relational algebra expression) • self-joins; … • for example, find the grand-parents of ‘Tom’, given PC(parent-id, child-id)

  44. rename op. • PC(parent-id, child-id)

  45. rename op. • first, WRONG attempt: • (why? how many columns?) • Second WRONG attempt:

  46. rename op. • we clearly need two different names for the same table - hence, the ‘rename’ op.

  47. Overview - rel. algebra • fundamental operators • derived operators • joins etc • rename • division • examples

  48. Division • Rarely used, but powerful. • Suited for queries that include the phrase “for all” • Example: find suspicious suppliers, i.e., suppliers that supplied all the parts in A_BOMB

  49. Division

  50. Division • Observations: ~reverse of cartesian product • It can be derived from the 5 fundamental operators (!!) • How?

More Related