Relational Model
Relational Algebra
Copyright By PowCoder代写 加微信 powcoder
“Relational” Mathematics
A mathematical basis is a great way to formally express the myriad of requests we may want to make
Operations from Algebra and Set Theory
The Relational Algebra and
Relational Calculus
Relational algebra
Basic set of operations for the relational model
Relational algebra expression
Sequence of relational algebra operations
Relational calculus
Higher-level declarative language for specifying relational queries
Relational Algebra
Unary Relational Operations
SELECT (symbol: (sigma))
PROJECT (symbol: (pi))
RENAME (symbol: (rho))
Relational Algebra Operations From Set Theory
UNION ( ), INTERSECTION ( ), DIFFERENCE (or MINUS, – )
CARTESIAN PRODUCT ( x )
Operations
Operations: have relations as inputs and outputs (i.e., produce new relations)
The result of an operation is a new relation, which may have been formed from one or more input relations
The algebra is “closed” (all objects in relational algebra are relations)
Expressions: A sequence of relational algebra operations
The result of a relational algebra expression is also a relation that represents the result of a database query (or retrieval request)
Unary Relational Operations
SELECT operation – (sigma)
Unary — Applied to a single relation
If condition evaluates to TRUE, tuple selected
Boolean conditions AND, OR, and NOT
SELECT Operation
Select the EMPLOYEE tuples whose department number is 4:
DNO = 4 (EMPLOYEE)
Select the EMPLOYEE tuples whose salary is greater than $30,000:
SALARY > 30,000 (EMPLOYEE)
(Dno 4 AND Salary > 25000)
OR (Dno 5 AND Salary > 30000)(EMPLOYEE)
SELECT operation
Nested application of SELECT operations
Is SELECT Commutative?
Cascading of the SELECT operation may be applied in any order:
PROJECT Operation – (pi)
Selects columns from table and discards the other columns:
Eliminates duplicates
Result of PROJECT operation is a set of distinct tuples
Project each employee’s first, last name, and salary:
LNAME, FNAME,SALARY(EMPLOYEE)
Lname, Fname, Salary (EMPLOYEE)
PROJECT operation Is PROJECT Commutative? Nesting Different Operators Dno 3 OR Dno 5 OR Dno 7 (EMPLOYEE) Bdate (Dno 3 OR Dno 5 OR Dno 7 (EMPLOYEE)) RENAME Operation – (rho) Sequence of operations RENAME Operation RESULT (F,M,L,S,B,A,SX,SAL,SU,DNO)(EMPLOYEE) Relational Algebra UNION, INTERSECTION, and MINUS Relational Algebra Operations UNION operation – Type Compatibility UNION Operation Example List the social security numbers of all employees who either work in Dept. 5 or directly supervise an employee who works in Dept. 5. INTERSECTION operation- DIFFERENCE operator – UNION, INTERSECT, and DIFFERENCE STUDENT INSTRUCTOR UNION, INTERSECT, and DIFFERENCE Join information between 2 or more tables Cross Product – X The CARTESIAN operation – X Database COMPANY Ex., Find all dependants of female employes Find all dependents of female employees FEMALE_EMPS SEX=’F’(EMPLOYEE) EMPNAMES FNAME, LNAME, SSN (FEMALE_EMPS) EMP_DEPENDENTS EMPNAMES x DEPENDENT contains every combination ACTUAL_DEPS FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPS) The JOIN Operation Variations of JOIN Retrieve the name of the manager of each department Combine each DEPARTMENT tuple with the EMPLOYEE tuple whose SSN value matches the MGRSSN value in the department tuple. DEPT_MGR DEPARTMENT MGRSSN = SSN EMPLOYEE NATURAL JOIN Operator Resulting relation Q(A, B, C, D, E) List information about each department including its location DEPT_LOCS DEPARTMENT * DEPT_LOCATIONS Use EQUIJOIN Find the full names of employees who work in THETA JOIN Operation Joins – Binary Operations R.Ai = S.Bj AND RelaX – Relational Algebra Calculator /docProps/thumbnail.jpeg 程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com
Nested application of PROJECT operations
Attributes in
List birthdates of employees in departments 3, 5, 7.
Bdate (EMPLOYEE)
Rename attributes in intermediate results
In-line expression:
S(R) only changes:
The relation name to S
(B1, B2, …, Bn )(R) only changes:
The column (attribute) names to B1, B1, …..Bn
S (B1, B2, …, Bn )(R) changes both:
The relation name to S, and
The column (attribute) names to B1, B1, …..Bn
Set Theory
Merge the elements of two sets in various ways
Binary operations
Relations must have the same type of tuples
from Set Theory
Includes all tuples in either R, or S, or in both R and S
INTERSECTION
Includes all tuples that are in both R and S
SET DIFFERENCE (or MINUS)
Includes all tuples in R but not in S
The result of R S, is a relation that includes all tuples that are either in R or in S or in both R and S
Duplicate tuples are eliminated
R and S must be “type compatible” (or UNION compatible)
R (A1, A2, …, An) and S(B1, B2, …, Bn) are type compatible if:
They have the same number of attributes
The domains of corresponding attributes are type compatible i.e., dom(Ai) = dom(Bi) for i = 1, 2, …, n.
DEP5_EMPS sDno=5 (EMPLOYEE)
RESULT1 p Ssn(DEP5_EMPS)
RESULT2(SSN) pSuper_ssn(DEP5_EMPS)
RESULT RESULT1 RESULT2
R S includes all tuples that are in both R and S
The attribute names in the result will be the same as the attribute names in R
The two operand relations R and S must be “type compatible”
Also called MINUS or EXCEPT
The result of R – S, is a relation that includes all tuples that are in R but not in S
The attribute names in the result will be the same as the attribute names in R
The two operand relations R and S must be “type compatible”
STUDENT INSTRUCTOR
STUDENT – INSTRUCTOR
INSTRUCTOR – STUDENT
Both union and intersection are commutative operations:
R S = S R, and R S = S R
Both union and intersection can be treated as n-ary operations
as both are associative operations
R (S T) = (R S) T
(R S) T = R (S T)
The minus operation is not commutative
R – S ≠ S – R
Binary operators
CROSS PRODUCT or CROSS JOIN
Combine tuples from two relations in a combinatorial fashion, i.e. an exhaustive pairing of tuples
Q = R(A1, A2, . . ., An) x S(B1, B2, . . ., Bm)
Relation Q has degree n + m attributes:
Q(A1, A2, . . ., An, B1, B2, . . ., Bm), in that order.
The two operands do NOT have to be “type compatible”
When is this operation useful?
SSN=ESSN(EMP_DEPENDENTS)
Follow with /
Denoted by
Combine related tuples from two relations
General join condition of the form
Only = comparison operator used
Always have one or more pairs of attributes that have identical values in every tuple
NATURAL JOIN
Denoted by *
Removes second (superfluous) attribute in an EQUIJOIN condition
A simplification of an Equijoin joins join attributes with the same name
Q R(A,B,C,D) * S(C,D,E)
Combine DEPARTMENT with DEPT_LOCATIONS
Fname, Lname (EMPLOYEE Dno DNnumber ( Dlocation ‘ ’(DEPT_LOCATIONS)))
Use NATURAL Join
Fname, Lname (EMPLOYEE * (Dno, Dlocation) ( Dlocation ‘ ’(DEPT_LOCATIONS)))
R S
can be any general boolean expression on the attributes of R and S
Each term of relates a tuple from R with a tuple from S using any {<, , >, , =, }
In practice, is constructed with just one or more equality conditions “AND”ed together:
R.Ai = S.Bj AND R.Ak = S.Bl AND R.Ap = S.Bq
Each attribute pair is called a join attribute
Name Notation Notes
Cross-Product R X S Combines all tuples and attributes of R, S
Useful when followed by selection
Join R
Theta-Join R S can be any general boolean expression on the attributes of R and S
Each term of relates a tuple from R with a tuple from S using any {<, , >, , =, }
Equi-Join R S
R.Ak = S.Bl AND
R.Ap = S.Bq Combines all attributes of R, S
Constructed with = as the only comparison operator
More equality conditions may be AND’d together
Natural Join R * S A simplification of an Equijoin
Combines all attributes of R, S
Joins attributes with the same name
Matching attributes are implicitly matched