View by Category

Loading...

PPT – Relational Algebra and Relational Calculus PowerPoint presentation | free to view

The Adobe Flash plugin is needed to view this content

About This Presentation

Write a Comment

User Comments (0)

Transcript and Presenter's Notes

Lecture 11

- Relational Algebra and Relational Calculus

Generations of Computer Languages

- What is the difference between 1GL, 2GL, 3GL, 4GL

and 5GLs? - 1GL Machine Language
- 2GL Assembly Language
- 3GL Procedural Languages
- high-level programming languages, such as C, C,

and Java - We must specify how things need to be done
- 4GL Non-procedural (declarative) Languages
- Most 4GLs are used to access databases
- We specify only what we want and not how to get

it - 5GL Natural languages
- English-like languages used to specify

requirements - E.g. FIND ALL RECORDS WHERE NAME IS "SMITH"
- Still have not matured enough

Relational Languages

- Data model includes a set of operations to

manipulate and access data in sthe database - Two formal languages for the relational model
- Relational Algebra
- Relational Calculus
- Relation Algebra
- Provides a formal foundation for relational model

operations - Used as a basis for implementing and optimizing

queries in RDBMSs - Some of concepts are now part of the SQL
- Standard query language for RDBMSs
- Query writing is based on relational calculus

Relational Algebra

- Relational algebra is a procedural language
- Operations divided into two groups
- Operations from mathematical set theory
- Union, Intersection, Set Difference, and

Cartesian Product - Additional operations developed for relational

databases - Select, Project, Join, divide, aggregate

functions, etc - The result of a retrieval is a new relation,

which may have been formed from one or more

relations - A sequence of relational algebra operations forms

a relational algebra expression - Result will also be a relation that represents

the result of a database query (or retrieval

request)

(No Transcript)

Unary Relational Operations

- SELECT Operation
- Used to select a subset of the tuples from a

relation that satisfy a selection condition - It is a filter that keeps only those tuples that

satisfy a qualifying condition those satisfying

the condition are selected while others are

discarded - In general, the select operation is denoted by ?

ltselection conditiongt(R) - symbol ? (sigma) is used to denote the select

operator - selection condition is a Boolean expression

specified on the attributes of - ltattribute namegtltcomparison opgtltconstant valuegt
- ltattribute namegtltcomparison opgtlt attribute name gt
- Combined by AND, OR and NOT
- To select the EMPLOYEE tuples whose department

number is four - ?DNO 4 (EMPLOYEE)
- select the EMPLOYEE tuples whose salary is

greater than 30,000 - ? SALARY gt 30,000 (EMPLOYEE)

Unary Relational Operations

- SELECT Operation Properties
- The SELECT operation ? ltselection conditiongt(R)

produces a relation S that has the same schema as

R - Degree of S is the same as that of R
- Cardinality of S is less than or equal to that of

R - The SELECT operation ? is commutative
- ? ltcondition1gt(?lt condition2gt(R)) ?

ltcondition2gt (?ltcondition1gt (R)) - ?ltcondition1gt(?ltcondition2gt(?ltcondition3gt(R))?ltco

ndition2(?lt condition3gt (?ltcondition1gt( R))) - A cascaded SELECT operation may be replaced by a

single selection with a conjunction of all the

conditions - ?ltcondition1gt(? ltcondition2gt(? ltcondition3gt(R))

? ltcondition1gtANDlt condition2gt ANDltcondition3gt(R))

)

Unary Relational Operations

- PROJECT Operation
- Selects certain columns from the table and

discards the other columns - The PROJECT creates a vertical partitioning one

with the needed columns (attributes) containing

results of the operation and other containing the

discarded columns - The general form is ?ltattribute_listgt(R) where ?

(pi) is the symbol used to represent the project

operation and ltattribute_listgt is the desired

list of attributes from the attributes of

relation R - To list each employees first and last name and

salary, the following is used - ??LNAME, FNAME,SALARY(EMPLOYEE)

Unary Relational Operations

- Worry about ICs in result?
- Key
- Removes any duplicate tuples so the result of the

project operation is a set of tuples and hence a

valid relation - Otherwise, the result is NOT a relation but a

multiset or a bag - PROJECT Operation Properties
- The number of tuples in the result of projection

? ltlistgt (R) is always less or equal to the

number of tuples in R - When is it guaranteed to be equal?
- If the list of attributes includes a key of R
- ? ltlist1gt (? ltlist2gt (R) ) ? ltlist1gt (R) as

long as ltlist1gt contains the attributes in

ltlist2gt - Otherwise we have an error

Show the full name and salary of every employee.

Show the gender and salary of every employee.

Select employees in dep. 4 with salaries

exceeding 25000 or in dep. 5 with salaries

exceeding 30000

Select the gender and salary of all employees in

dep. 4 with salaries exceeding 25000 or in dep. 5

with salaries exceeding 30000

(No Transcript)

Unary Relational Operations

- We may want to apply several relational algebra

operations one after the other - a single relational algebra expression by nesting

the operations, - apply one operation at a time and create

intermediate result relations. - Must give names to the relations that hold the

intermediate results - To retrieve the first name, last name, and salary

of all employees who work in department 5, we

must apply select and project operations. - We can write a single relational algebra

expression as follows - ?FNAME, LNAME, SALARY(? DNO5(EMPLOYEE))
- OR We can explicitly show the sequence of

operations, giving a name to each intermediate

relation - TEMP ? ? DNO5(EMPLOYEE)
- R ? ? FNAME, LNAME, SALARY (TEMP)

(No Transcript)

Unary Relational Operations

- Rename Operation
- The rename operator is ? (rho)
- The general Rename operation can be expressed by

any of the following forms - ?S(B1, B2, , Bn)(R) is a renamed relation S

based on R with column names B1, B1,..Bn - ?S(R) is a renamed relation S based on R
- does not specify column names
- Columns names dont change
- ?(B1, B2, , Bn )(R) is a renamed relation with

column names B1, B1, ..Bn which does not

specify a new relation name - does not specify a relation name
- Relation name not changed
- ?DEPT(DEPT_NAME, DEPT_NUMBER, MGRSSN,MGRSTARTDATE)

(DEPARTMENT)

Relational Algebra Operations From Set Theory

- UNION Operation
- Binary Operation
- The result of this operation, denoted by 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 by default
- To retrieve the social security numbers of all

employees who either work in department 5 or

directly supervise an employee who works in

department 5, we can use the union operation as

follows - DEP5_EMPS? ?DNO5(EMPLOYEE)
- RESULT1??SSN(DEP5_EMPS)
- RESULT2?? SUPERSSN(DEP5_EMPS)
- RESULT?RESULT1 ? RESULT2
- The union operation produces the tuples that are

in either RESULT1 or RESULT2 or both - R?S has all attributes of R
- The two operands must be type compatible (or

union compatible)

Relational Algebra Operations From Set Theory

- Type Compatibility
- The operand relations R1(A1, A2, ..., An) and

R2(B1, B2, ..., Bn) must have the same number of

attributes, and the domains of corresponding

attributes must be the same - That is, dom(Ai)dom(Bi) for i1, 2, ..., n
- The resulting relation for R1?R2,R1 ? R2, or

R1-R2 has the same attribute names as the first

operand relation R1 (by convention) - Compatibility must also exist for intersection

and difference - Commutative and associative
- R ? S S ? R
- R ? (S ? T) (R ? S) ? T

Relational Algebra Operations From Set Theory

- UNION

STUDENT?INSTRUCTOR

Relational Algebra Operations From Set Theory

- INTERSECTION OPERATION
- The result of this operation, denoted by R ? S,

is a relation that includes all tuples that are

in both R and S. - The two operands must be "type compatible
- Commutative and associative R ? S S ? R and (R

? S) ? T R ? (S ? T)

Relational Algebra Operations From Set Theory

- Set Difference (or MINUS) Operation
- The result of this operation, denoted by R - S,

is a relation that includes all tuples in R but

not in S - The two operands must be "type compatible
- Neither commutative and associative
- R - S ? S R
- (R - S) - T ? R (S T)

(No Transcript)

Operations From Set Theorys

- CARTESIAN (or cross product) Operation
- Combine tuples from two relations in a

combinatorial fashion - R(A1, A2, . . ., An) x S(B1, B2, . . ., Bm) is a

relation Q with - degree n m attributes Q(A1, A2, . . ., An, B1,

B2, . . ., Bm) - if R has nR tuples (cardinality of R denoted as

R nR ), and S has nS tuples, then R x S

will have nR nS tuples - The two operands do NOT have to be "type

compatible - Find the cross product between female employees

(Fname, LName and SSN) and dependents - FEMALE_EMPS ? ? SEXF(EMPLOYEE)
- EMPNAMES ? ? FNAME, LNAME,SSN (FEMALE_EMPS)
- EMP_DEPENDENTS ? EMPNAMES x DEPENDENT

To get employees dependants, SSNs must match

The rest are spurious

Binary Relational Operations

- JOIN Operation
- The CARTESIAN PRODUCT operation is rarely used on

its own because of - Spurious tuples
- Efficiency
- Usually followed by a SELECT operation to get

actual tuples - The sequence of cartesian product followed by

select is used quite commonly to identify and

select related tuples from two relations, a

special operation, called JOIN - Very important for any relational database with

more than one relation - Allows us to process relationships among

relations - Makes the Pk,Fk combinations effective (like a

physical link) - The general form of a join operation on two

relations R(A1, A2, . . ., An) and S(B1, B2, . .

., Bm) is - R ltjoin conditiongtS where R and S can be any

relations

Binary Relational Operations

- Retrieve the manager of each department
- we need to combine each DEPARTMENT tuple with the

EMPLOYEE tuple whose SSN value matches the MGRSSN

value in the department tuple - We do this by using the join operation
- DEPT_MGR ? DEPARTMENT MGRSSNSSN

EMPLOYEE

Binary Relational Operations

- Get the locations of every department
- DEPT_LOCS ? DEPARTMENT

DEPT_LOCATIONS DNUMBERDNUMBER

About PowerShow.com

PowerShow.com is a leading presentation/slideshow sharing website. Whether your application is business, how-to, education, medicine, school, church, sales, marketing, online training or just for fun, PowerShow.com is a great resource. And, best of all, most of its cool features are free and easy to use.

You can use PowerShow.com to find and download example online PowerPoint ppt presentations on just about any topic you can imagine so you can learn how to improve your own slides and presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

You can use PowerShow.com to find and download example online PowerPoint ppt presentations on just about any topic you can imagine so you can learn how to improve your own slides and presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

Recommended

«

/ »

Page of

«

/ »

disablevideo

Promoted Presentations

Related Presentations

Page of

Page of

CrystalGraphics Sales Tel: (800) 394-0700 x 1 or Send an email

Home About Us Terms and Conditions Privacy Policy Contact Us Send Us Feedback

Copyright 2015 CrystalGraphics, Inc. — All rights Reserved. PowerShow.com is a trademark of CrystalGraphics, Inc.

Copyright 2015 CrystalGraphics, Inc. — All rights Reserved. PowerShow.com is a trademark of CrystalGraphics, Inc.

The PowerPoint PPT presentation: "Relational Algebra and Relational Calculus" is the property of its rightful owner.

Do you have PowerPoint slides to share? If so, share your PPT presentation slides online with PowerShow.com. It's FREE!

Committed to assisting Csbsju University and other schools with their online training by sharing educational presentations for free