# CS代写 Relational Model – cscodehelp代写

Relational Model

Relational Algebra

Copyright By cscodehelp代写 加微信 cscodehelp

“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

Nested application of PROJECT operations

Attributes in

Is PROJECT Commutative?

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)

Follow with /

The JOIN Operation

Denoted by

Combine related tuples from two relations

General join condition of the form

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

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

程序代写 CS代考 加微信: cscodehelp QQ: 2235208643 Email: kyit630461@163.com