1 / 78

Relations

Relations. Tuples. Given a collection of types T i (i=1,2,….n), a tuple value on those types is a set of ordered triples of the form <Ai, Ti, vi> where Ai is attribute name Ti is type name vi is a value of type Ti and. Tuples. The value n is the degree or arity of t

bryga
Télécharger la présentation

Relations

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. Relations www.techstudent.co.cc

  2. Tuples • Given a collection of types Ti (i=1,2,….n), a tuple value on those types is a set of ordered triples of the form <Ai, Ti, vi> where Ai is attribute name Ti is type name vi is a value of type Ti and www.techstudent.co.cc

  3. Tuples • The value n is the degree or arity of t • ordered triple <Ai, Ti,vi> is a component of t • The ordered pair <Ai, Ti> is an attribute of t and identified by attribute name Ai • The complete set of attributes is the heading of t • The tuple type of t is determined by the heading of t; heading and tuple type both have the same attributes and the same degree as t does www.techstudent.co.cc

  4. Tuples • Tuple type name is TUPLE { A1 T1, A2 T2,…. An Tn} • Example Degree : Heading Type www.techstudent.co.cc

  5. Tuples • Properties of tuples • Each tuple contains exactly one value for each of its attributes • There is no left-to-right ordering to the components of a tuple. • Every subset of a tuple is a tuple • A tuple of degree n is said to be n-ary • Tuple of degree 0 is called nullary www.techstudent.co.cc

  6. Tuples • The TUPLE type generator: • General Form TUPLE { < attribute comma list> } Where each <attribute> consists of an <attribute name> followed by a <type name> www.techstudent.co.cc

  7. Tuples • Example VAR ADDR TUPLE { STREET CHAR, CITY CHAR, STATE CHAR, ZIP CHAR } Tuple Selector operator TUPLE { STREET `14 Park View`, CITY `TVM`, STATE ‘Kerala`, ZIP `695 002’} www.techstudent.co.cc

  8. Tuples • Operators: • All operators of relational algebra • Candidate keys • Foreign keys • Functional and other dependencies • Tuple equality • Tuple type inference • WRAP and UNWRAP www.techstudent.co.cc

  9. Tuples • Tuple equality • Tuples t1 and t2 are equal if and only if they have the same set of attributes A1, A2, …An and for all i (i=1,2,..n) the value of v1 of Ai is equal to the value of v2 of Ai in t2 • t1 and t2 are duplicates of each other if and only if they are equal www.techstudent.co.cc

  10. Tuples • Tuple projection • ADDR { CITY, ZIP } • WRAP and UNWRAP • Let • TT1 = TUPLE {NAME NAME, ADDR TUPLE { STREET CHAR, CITY CHAR, STATE CHAR, ZIP CHAR} } • TT2 = TUPLE {NAME NAME, ADDR TUPLE { STREET CHAR, CITY CHAR, STATE CHAR, ZIP CHAR} } www.techstudent.co.cc

  11. Tuples • Let • TT1 = TUPLE {NAME NAME, ADDR TUPLE { STREET CHAR, CITY CHAR, STATE CHAR, ZIP CHAR} } • TT1 = TUPLE {NAME NAME, STREET CHAR, CITY CHAR, STATE CHAR, ZIP CHAR} • Let NADDR1 and NADDR2 be tuple variables of types TT1 and TT2 www.techstudent.co.cc

  12. Tuples • NADDR1 := NADDR2 WRAP { STREET, CITY, STATE, ZIP } AS ADDR; • NADDR1 UNWRAP ADDR • NADDR2 := NADDR1 UNWRAP ADDR; www.techstudent.co.cc

  13. Relation types • Definition A relation value r consists of a heading and a body where : The heading of r is a tuple heading. relation r has the same attributes and the same degree as that heading has the body of r is a set of tuples, all having the same heading; cardinality of that set is said to be the cardinality of r. www.techstudent.co.cc

  14. Relation types • Relation type of r is determined by the heading of r • Relation type name is RELATION { A1 T1, A2 T2,…An Tn } • General form of relation selector is RELATION [<heading>] {tuple exp comalist>} <heading> is comalist of <attribute>s enclosed in braces www.techstudent.co.cc

  15. Relation values • Relations are normalized • There is no left-to-right ordering to atttributes • There is no top-to-bottom ordering to the tuples • There are no duplicate tuples www.techstudent.co.cc

  16. Relation values www.techstudent.co.cc

  17. www.techstudent.co.cc

  18. Relation values • Relations with no attributes • A relation can have an empty set of attributes or in other words no attribute at all • Such a relation can have atmost one tuple 0-tuple • Thus there are two relations of degree 0 • One which contains one tuple (TABLE_DEE) • That contains no tuples at all (TABLE_DUM) • DEE means TRUE and DUM means false www.techstudent.co.cc

  19. Relation values • Relation selector RELATION { } { TUPLE { } } RELATION { } { } www.techstudent.co.cc

  20. Relation values • Operators on relations • Relational comparison <relation exp> <relation comp op> <relation exp> Relation comp op = equals ≠ not equals  subset of  proper subset of  superset of  proper superset of www.techstudent.co.cc

  21. Relation values • To find if a relation is equal to an empty relation • IS_EMPTY ( < relation exp> ) • To find whether a given tuple is found in a relation r or not • t  r www.techstudent.co.cc

  22. Relation variables • Base Relvar Definition VAR <relvar name> BASE <relation type> <candidate key def list> [ <foreign key def list> ]; <relation type> takes the form RELATION { <attribute comalist> } www.techstudent.co.cc

  23. Relation variables • Example VAR SUPPLIERS BASE RELATION { SUP_NUMBER SUPPLIER_NUMBER, SUP_NAME NAME, STATUS INTEGER, CITY CHAR } PRIMARY KEY { SUP_NUMBER} ; www.techstudent.co.cc

  24. Relation variables • Explanation • The base relation has the type RELATION { SUP_NUMBER SUPPLIER_NUMBER, SUP_NAME NAME, STATUS INTEGER, CITY CHAR} • The terms heading, body, attribute, tuple, degree applies to relvars also • All possible values of any given relvar are of the same relation type and hence have the same heading www.techstudent.co.cc

  25. Relational Algebra www.techstudent.co.cc

  26. Introduction • Original eight operators: • Union • Intersection • Difference • Cartesian product • Select • Project • Join • divide www.techstudent.co.cc

  27. Closure • Relational closure property • The output from any relational operation is another relation • To achieve closure property, all relations should have proper attribute names in order to use those attributes in the subsequent operations www.techstudent.co.cc

  28. Closure • RENAME operator • To rename attributes within a specified relation • Eg: SUPPLIERS RENAME CITY AS SCITY Gives the same heading and body as the relation that is the current value of SUPPLIERS except that the city attribute is named SCITY instead of city Multiple renaming PARTS RENAME (PART_NAME AS PN, WEIGHT AS WT www.techstudent.co.cc

  29. Relational Operators • Union • Special type of mathematical union in which both the relations should be of the same type • This is referred to as union compatibility • Given two relations a and b of the same type, the union of those two relations a union b, is a relation of the same type, with the body consisting of all tuples t such that t appears in a or b or both www.techstudent.co.cc

  30. Relational Operators • Intersect • Given two relations a and b of the same type, the intersection of those two relations a INTERSECT b, is a relation of the same type, with the body consisting of all tuples t such that t appears in both a and b www.techstudent.co.cc

  31. Relational Operators • Difference • Given two relations a and b of the same type, the difference of those two relations a MINUS b, is a relation of the same type, with the body consisting of all tuples t such that t appears in a and not b • A MINUS B is not the same as B MINUS A www.techstudent.co.cc

  32. Relational Operators A B www.techstudent.co.cc

  33. Relational Operators • Product • The cartesian product of two relations a and b, a TIMES b, where a and b have no common attribute names, to be a relation with a heading that is the union of the headings of a and b and with a body consisting of the set of all tuples t such that t is the union of a tuple appearing in a and a tuple appearing in b www.techstudent.co.cc

  34. Relational Operators • Given the tuples {A1 a1, A2 a2,….Am am} And {B1 b1, B2 b2, …Bn bn} The union of the two is the single tuple {A1 a1, A2 a2,….Am am,B1 b1, B2 b2, …Bn bn } www.techstudent.co.cc

  35. Relational Operators • Select • Used to select a subset of tuples in a relation that satisfy a selection condition • Denoted by σ<selection condition>(<relation name>) σ – SELECT operator Selection Condition – boolean expression specified on relation attributes using the comparison operators { =, <, <=, >, >=, } www.techstudent.co.cc

  36. Relational Operators(Select) • <selection operation> is applied to each tuple t in relation R specified by <relation name> • If condition is satisfied,tuple t is SELECTED • Boolean operators AND, OR, NOT can be used to connect the conditions www.techstudent.co.cc

  37. Relational Operators(Select) • Is applied on a single relation • Degree of relation resulting from SELECT is the same as the original relation • Fraction of tuples selected is called as selectivityof the condition • Is commutative www.techstudent.co.cc

  38. Relational Operators(Project) • Selects certain columns from the relation • General Form π<attribute list>(<relation name>) π – project operator • Degree is equal to the number of attributes in <attribute list> www.techstudent.co.cc

  39. Relational Operators(Project) • Πx, y,….z(A) is a relation with • A heading derived from the heading of a by removing all attributes not mentioned in the set {X, Y, …., Z } • A body consisting of all tuples{X x,Y y,…, Z z} such that a tuple appears in a with X value x, Y value y, … and Z value z. www.techstudent.co.cc

  40. Relational Operators(Project) • PROJECT implicitly removes any duplicate tuples • Whenever there are two identical tuples, only one is kept in the result.This is called duplicate elimination • Commutativity does not hold on PROJECT www.techstudent.co.cc

  41. Relational Operators(Join) • Denoted by • Combine related tuples from two relations • General form R <join condition>S The resultant relation Q has one tuple for each combination of tuples whenever the combination satisfies the join condition www.techstudent.co.cc

  42. Relational Operators(Join) • common join – joins with equality condition. • A join where only the = comparison is used is called Equi Join • Equi join always have one or more pairs of attributes that have identical values. • To get rid of the second attribute, natural join is used www.techstudent.co.cc

  43. Relational Operator(Divide) • Let relations a and b have attributes X1, X2, …..Xm and Y1,Y2,….Yn Division of a by b a divideby b is a relation with heading {X} and body consisting of all tuples {X x} appearing in a such that a tuple {X x, Y y} appears in c for all tuples {Y y} appearing in b. www.techstudent.co.cc

  44. Integrity www.techstudent.co.cc

  45. Integrity • An integrity constraint is a boolean expression that is associated with some database and is required to evaluate at all times to TRUE. • Constraints should be formally declared to the DBMS and DBMS enforces these constraints www.techstudent.co.cc

  46. Internal Vs External Predicates • Internal predicates: what the data means to the system • External predicates: What the data means to the user • A given internal predicate is the system’s approximation to the corresponding external predicate www.techstudent.co.cc

  47. Internal Vs External Predicates • External predicate for a given relvar is basically what the relvar means to the user • The EMPLOYEE with the specified employee number(EMPNO) has the specified name(ENAME) , is working for the department with the specified number (DNO), and gets a salary specified(SALARY) • S is a tuple of the form (EMPNO, ENAME, DNO, SALARY) www.techstudent.co.cc

  48. A given tuple appears in a given relvar at a given time if and only if that tuple makes that relvar’s external predicate evaluate to TRUE at that time. www.techstudent.co.cc

  49. Keys • Candidate Key • Primary Key and Alternate Key • Foreign Key www.techstudent.co.cc

  50. Candidate Key • The set of all attributes of R having the uniqueness property • Let K be a set of attributes of relvar R. Then K is a candidate key for R if and only if it has both of the following properties • Uniqueness: No legal value of R ever contains two distinct tuples with the same value for K • Irreducibility: No proper subset of K has the uniqueness property www.techstudent.co.cc

More Related