# CS代写 Relational Model – cscodehelp代写

Relational Model

Relational Algebra

“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

applied independently to each individual tuple t in R
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
( < cond2> (R)) ≡  AND < cond2> (R)

Is SELECT Commutative?
( < condition2> (R)) =  ( < condition1> (R))

Cascading of the SELECT operation may be applied in any order:
( ( (R))) =  ( ( ( R)))

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
Nested application of PROJECT operations
( (R))
Attributes in contains the attributes in

Is PROJECT Commutative?
( (R)) ≠  ( (R))

Nesting Different Operators
List birthdates of employees in departments 3, 5, 7.

Dno  3 OR Dno  5 OR Dno  7 (EMPLOYEE)
 Bdate (EMPLOYEE)

 Bdate (Dno  3 OR Dno  5 OR Dno  7 (EMPLOYEE))

RENAME Operation –  (rho)
Rename attributes in intermediate results
In-line expression:

Sequence of operations

RENAME Operation
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

 RESULT (F,M,L,S,B,A,SX,SAL,SU,DNO)(EMPLOYEE)

Relational Algebra
Set Theory

UNION, INTERSECTION, and MINUS
Merge the elements of two sets in various ways
Binary operations
Relations must have the same type of tuples

Relational Algebra Operations
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

UNION operation – 
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)

Type Compatibility
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.

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.
DEP5_EMPS  sDno=5 (EMPLOYEE)
RESULT1  p Ssn(DEP5_EMPS)
RESULT2(SSN)  pSuper_ssn(DEP5_EMPS)
RESULT  RESULT1  RESULT2

INTERSECTION operation- 
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”

DIFFERENCE operator –
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”

UNION, INTERSECT, and DIFFERENCE

STUDENT  INSTRUCTOR
STUDENT  INSTRUCTOR
STUDENT – INSTRUCTOR
INSTRUCTOR – STUDENT

UNION, INTERSECT, and DIFFERENCE
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

Join information between 2 or more tables
Binary operators

Cross Product – X

The CARTESIAN operation – X
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?

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 
 SSN=ESSN(EMP_DEPENDENTS)

 FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPS)

The JOIN Operation
Denoted by
Combine related tuples from two relations
General join condition of the form AND AND…AND

Variations of JOIN
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

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
A simplification of an Equijoin joins join attributes with the same name
Q  R(A,B,C,D) * S(C,D,E)

Resulting relation Q(A, B, C, D, E)

List information about each department including its location
Combine DEPARTMENT with DEPT_LOCATIONS

DEPT_LOCS  DEPARTMENT * DEPT_LOCATIONS

Use EQUIJOIN
 Fname, Lname (EMPLOYEE Dno  DNnumber ( Dlocation  ‘ ’(DEPT_LOCATIONS)))
Use NATURAL Join
 Fname, Lname (EMPLOYEE * (Dno, Dlocation) ( Dlocation  ‘ ’(DEPT_LOCATIONS)))

Find the full names of employees who work in

THETA JOIN Operation
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

Joins – Binary Operations
Name Notation Notes
Cross-Product R X S Combines all tuples and attributes of R, S
Useful when followed by selection
Join R S Only match related tuples
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.Ai = S.Bj AND
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

RelaX – Relational Algebra Calculator

/docProps/thumbnail.jpeg

Posted in Uncategorized