This Lecture • The Relational Model • Relational data structures Relations and Relational Algebra • Relational algebra • Union, Intersection and Difference • Product of Relations Database Systems • Projection, Selection • Further reading Michael Pound • Database Systems, Connolly & Begg, 4.2 and 5.1 www.cs.nott.ac.uk/~mpp/G51DBS • The Manga Guide to Databases, Chapter 2 mpp@cs.nott.ac.uk The Relational Model Relational Data Structure • Introduced by E.F. Codd in his paper “A Relational Model of Data for Large Shared • Data is stored in Relation relations (tables) Databanks”, 1970 • Data takes the form of • The foundation for most (but not all) modern John 23 tuples (rows) Mary 20 database systems Tuples Mark 18 • The order of tuples is Jane 21 not important • There must not be duplicate tuples Relations Relations • We will use tables to represent relations • In general, each column has a domain , a set from which all possible values for that column can come • This is an example relation between people • For example, each value in the first column below and email addresses: comes from the set of first names Anne aaa@cs.nott.ac.uk Anne aaa@cs.nott.ac.uk Bob bbb@cs.nott.ac.uk Bob bbb@cs.nott.ac.uk Chris ccc@cs.nott.ac.uk Chris ccc@cs.nott.ac.uk 1
Relations Terminology • A mathematical relation is a set of tuples: sequences of • Degree of a relation : how long each tuple is, or values. Each tuple represents a row in the table: how many columns the table has • In the first example (name, email), the degree of the Anne aaa@cs.nott.ac.uk 0115 911 1111 relation is 2 Bob bbb@cs.nott.ac.uk 0115 922 2222 • In the second example (name, email, phone) the Chris ccc@cs.nott.ac.uk 0115 933 3333 degree of the relation is 3 • Degrees of 2, 3, ... are often called Binary, Ternary, etc. • {<Anne, aaa@cs.nott.ac.uk, 01159111111>, • Cardinality of a relation : how many different <Bob, bbb@cs.nott.ac.uk, 01159222222>, tuples there are, or how many rows a table has <Chris, ccc@cs.nott.ac.uk, 01159333333>} Mathematical Definition Data Manipulation • The mathematical definition of a relation R of • Data is represented as relations degree n, where values come from domains A 1 , • Manipulation of this data (through updates and ..., A n : queries) corresponds to operations on relations R A 1 x A 2 x … x A n • Relational algebra describes those operations. (a relation is a subset of the Cartesian These take relations as arguments, and produce product of domains) new relations Cartesian product: • Relational algebra contains two types of A 1 x A 2 x … x A n = operators. Common, set-theoretic operators and {<a 1 , a 2 , …, a n >: a 1 A 1 , a 2 A 2 , …, a n A n } those specific to relations Union Union-compatible Relations • Standard set-theoretic definition of union: • Two relations R and S are union- A B = {x: x A or x B} compatible if: • For example, {a,b,c} {a,d,e} = {a,b,c,d,e} • They have the same number of columns • For relations, we require the results to be in • Corresponding columns have the same the form of another relation. domains • In order to take a union of relations R and S, R and S must have the same number of columns and corresponding columns must have the same domains 2
Union-compatible Example Not union-compatible Example Same number of columns and matching domains Different numbers of columns Anne 1970 Tom 1980 Anne 1970 NG7 Tom 1980 Bob 1971 Sam 1985 Bob 1971 NG16 Sam 1985 Chris 1972 Steve 1986 Chris 1972 NG21 Steve 1986 Not union-compatible Example Unions of Relations • Let R and S be two union-compatible relations. The Union R S is a relation Corresponding columns have different domains containing all tuples from both relations: R S = {x: x R or x S} Anne NG7 Tom 1980 Bob NG16 Sam 1985 • Note that union is a partial operation on Chris NG21 Steve 1986 relations. That is, it is only defined for some (compatible) relations • This is similar in principle to division of numbers. Division by zero is undefined Union Example Difference of Relations • Let R and S be two union-compatible relations. The difference R - S is a relation R S R S containing all tuples from R that are not in S: Cheese 1.34 Cream 2.00 Cheese 1.34 R - S = {x: x R and x S} Milk 0.80 Soap 1.00 Milk 0.80 • This is also a partial operation on relations Bread 0.60 Bread 0.60 Eggs 1.20 Eggs 1.20 Soap 1.00 Soap 1.00 Cream 2.00 3
Difference Example Intersection of Relations • Let R and S be two union-compatible relations. The intersection R S is a relation R S R - S containing all tuples that are in both R and S: Cheese 1.34 Cream 2.00 Cheese 1.34 R S = {x: x R and x S} Milk 0.80 Soap 1.00 Milk 0.80 • This is also a partial operation on relations Bread 0.60 Bread 0.60 Eggs 1.20 Eggs 1.20 Soap 1.00 Intersection Example Cartesian Product • Cartesian product is a total operation on relations. R S R S • Can be applied to relations of any relative size Cheese 1.34 Cream 2.00 Soap 1.00 • Set-theoretic definition of product: Milk 0.80 Soap 1.00 R x S = {<x, y>: x R, y S} Bread 0.60 Eggs 1.20 • For example, if <Cheese, 1.34> R and <Soap, Soap 1.00 1.00> S then <<Cheese,1.34>,<Soap,1.00>> R x S Extended Cartesian Product of Extended Cartesian Product Relations • Extended Cartesian product flattens the result • Let R be a relation with column domains into a single tuple. For example: {A 1 ,...,A n } and S a relation with column domains {B 1 ,...,B m }. Their extended Cartesian <Cheese, 1.34, Soap, 1.00> product R x S is a relation: • This is more useful for relational databases R x S = {<c 1 , ..., c n , c n+1 , ..., c n+m >: • For the rest of this course, “product” will <c 1 , ..., c n > R, <c n+1 , ..., c n+m > S} mean extended Cartesian product 4
Product Example Projection • Sometimes using all columns in a relation is R S R x S Cheese 1.34 Cream 2.00 Cheese 1.34 Cream 2.00 unnecessary • Let R be a relation with n columns, and X be a set Milk 0.80 Soap 1.00 Milk 0.80 Cream 2.00 of column identifiers. The projection of R on X is a Bread 0.60 Bread 0.60 Cream 2.00 new relation X (R) that only has columns in X Eggs 1.20 Eggs 1.20 Cream 2.00 • For example, 1,2 (R) is a table that contains only Soap 1.00 Soap 1.00 Cream 2.00 the 1 st and 2 nd columns of R Cheese 1.34 Soap 1.00 • We can use numbers or names to index columns Milk 0.80 Soap 1.00 (naming columns will be discussed in the next Bread 0.60 Soap 1.00 lecture) Eggs 1.20 Soap 1.00 Soap 1.00 Soap 1.00 Projection Example Selection R • Sometimes we want to select tuples based on 1 2 3 one or more criteria Anne aaa@cs.nott.ac.uk 0115 911 1111 • Let R be a relation with n columns, and α is a Bob bbb@cs.nott.ac.uk 0115 922 2222 property of tuples Chris ccc@cs.nott.ac.uk 0115 933 3333 • Selection from R subject to condition α is 1,3 (R) defined as: Anne 0115 911 1111 α (R) = {<a 1 ,…,a n > R: α (a 1 ,…,a n )} Bob 0115 922 2222 Chris 0115 933 3333 Comparison Properties Meaningful Comparisons • Comparisons between values can only take place • We assume that properties are written using where it makes sense to compare them {and, or, not} and expressions of the form • We can always perform an equivalence test between col(i) col(j), where i, j are column numbers, two values in the same domain or col(i) v, where v is a value from domain A i • In some cases you can compare values from different • is a comparator which makes sense when domains, e.g. if both are strings • For example, 1975 < 1987 is a meaningful applied to values from columns i and j. Often comparison, “Anne” = 1981 is not these will be = , , , , , • We can only use a comparison in a selection if its result is true or false, never undefined 5
Selection Example Selection Example • col(3) < 2002 and col(2) = Nolan (R) • col(3) < 2002 and col(2) = Nolan (R) R R Insomnia Nolan 2002 Insomnia Nolan 2002 Magnolia Anderson 1999 Magnolia Anderson 1999 Insomnia Skjoldbjaerg 1997 Insomnia Skjoldbjaerg 1997 Memento Nolan 2000 Memento Nolan 2000 Gattaca Niccol 1997 Gattaca Niccol 1997 Selection Example Other Operations • col(3) < 2002 and col(2) = Nolan (R) • Not all SQL queries can be translated into relational algebra operations defined in this lecture R • Extended relational algebra includes counting, Memento Nolan 2000 joins and other additional operations This Lecture in Exams Next Lecture • The Relational Model What is the result of the following operation • Relational data structures 1,3 ( col(2) = col(4) (R x S)), where R and S are: • Relational data integrity R S • Further reading Anne 111111 Chris 111111 • Database Systems, Connolly & Begg, Chapter 4.2 Bob 222222 Dan 222222 • The Manga Guide to Databases, Chapter 2 6
Recommend
More recommend
