Chapter 4 The Relational Database Model
Textbook p116-121 Study Unit: 4
In this chapter, you will learn:
• What is meant by relational algebra? • How to manipulate database tables using relational set operators • How the DBMS supports the key relational operators: select, project and join. • The different types of joins. • How to write queries using relational algebra expressions.
• Relational algebra and relational calculus are the mathematical basis for ‘relational databases’. • Relational algebra……. – Defines theoretical way of manipulating table contents using relational operators – Use of relational algebra operators on existing tables (relations) produces new relations
• Codd defined eight relational operators:
Relational Algebra Operators
• SELECT • PROJECT
• DIFFERENCE • DIVIDE • PRODUCT
Re-visiting Set Theory
• Yields values for all rows found in a table • Can be used to list either all row values or it can yield only those row values that match a specified criterion • Yields a horizontal subset of a table
• The SELECT operator denoted by σθ , is formally defined as: σθ (R) or σ (RELATION)
• where σθ (R) is the set of specified tuples of the relation R and θ is the predicate (or criterion) to extract the required tuples.
– Yields all values for selected attributes – Yields a vertical subset of a table
• The PROJECT operator, denoted by ∏, is formally defined as: ∏ a1…an (R) or ∏ (Relation)
• where the projection of the relation R, denoted by ∏ a1…an (R) is the set of specified attributes a1…an of the relation R.
– Combines all rows from two tables, excluding duplicate rows – Tables must have the same attribute characteristics
– When two or more tables share the same number of columns, i.e. have the same degree, and when they share the same (or compatible) domains, they are said to be union-compatible.
• The UNION operator, denoted by ∪, is formally defined as: The union of relations R1(a1, a2,.... ,an) and R2(b1, b2,...., bn) denoted R1 ∪ R2 with degree n, is the relation R3(c1, c2,.... ,cn) where for each i (i =1,2..n), ai and bi must have compatible domains.
– Yields only the rows that appear in both tables
– the tables must be union-compatible to give valid results.
• The INTERSECT operator is formally defined as:
The intersect of relations R1(a1, a2,.... ,an) and R2(b1, b2,...., bn) denoted R1 ∩ R2 with degree n, is the relation R3(c1, c2,.... ,cn) that includes only those tuples of R1 that also appear in R2 where for each i (i =1,2..n), ai and bi must have compatible domains.
– Yields all rows in one table not found in the other table — that is, it subtracts one table from the other – The DIFFERENCE operator also requires that the two relations must be union-compatible.
• The DIFFERENCE operator is formally defined as: • The difference of relations R1(a1, a2,.... ,am) and R2(b1, b2,...., bm) denoted R1 - R2 with degree m, is the relation R3(c1, c2,.... ,cm) that includes all tuples that are in R1 but not in R2 where for each i (i =1,2..m), ai and bi must have compatible domains.
• Cartesian product
• usually written as R1 X R2
• Yields all possible pairs of rows from two tables
• Also known as the product.