Lecture 5- Relational Algebra Flashcards
What are the three variants of relational query language?
- Relational algebra
- Relational calculus
- SQL
What is algebra?
The study of operations in an abstract level on some domains (as a language, study of syntax and semantics of expressions)
What are two parts of relational algebra?
- Domain= set of all relations
- Expressions= referred to as queries
Operations on one table vs two tables?
- Operations on one table= pick some rows, pick some columns
- Operations on two tables= join
What is the domain, basic operations and derived operators?
- Domain = set of relations
- Basic operations = select, project, union, set difference, cartesian product, renaming
- Derived operators = set intersection, division, join
Describe the role of algebra inside of a DBMS
What is a select operator?
Select rows that satisfy the condition
This is an example of what?
Select operator
This is an example of what?
Select operator
What are the operators for select condition?
What are some simple selection conditions?
- < attribute> operator < constant>
- < attribute> operator < attribute>
- < condition> AND < condition>
- < condition> OR < condition>
- NOT < condition>
What is a project operator?
Project on (or pick) a subset of columns
This is an example of what?
Project operator
This is an example of what?
Project operator
What are some set operator operations?
Two relations are union compatible if?
- Both have the same number of columns
- Names of attributes with the same name in both relations have the same domain
Union compatible relations can be combined using?
- Union
- Intersection
- Set difference
This is an example of what?
Set operator
What is the cartesian product?
R x S is the set of all concatenated tuples < x,y>, where x is a tuple in R and y is a tuple in S
For cartesian products, relations ________
Don’t have to be union compatible
R x S can be ____ and expensive to compute
Huge
How to compute the cartesian product?
What is renaming?
- Expr returns a relation
- Rename the resulting relations to x with the first column in the result relation to A1, the second to A2, etc
This is an example of what?
Renaming
This is an example of what?
Renaming
What is a common usage of renaming?
- To clean up the result
- To prepare the result for the next operation
What is join (derived operator)?
A (general or theta) join of R and S is the expression
Join definition is equivalent to what?
This is an example of what?
Join (derived operator)
This is an example of what?
Join (derived operator)
What is equijoin?
- A special case of condition join where the condition contains only equalities
- Result schema similar to cross product, but only one copy of fields for which equality is specified
- Equijoin= join condition is a conjunction of equalities
This is an example of what?
Equijoin
What is a problem and solution to join?
- Problem= if R and S have attributes with the same then the Cartesian product is not well defined
- Solution 1= rename attributes prior to forming the product and use new names in join-condition
- Solution 2= common attribute names are qualified with relation names in the result of the join
What is natural join?
- Special case of equijoin
- Join condition equates all and only those attributes with the same name (condition doesn’t have to be explicitly stated)
- Duplicate columns eliminated from the result
This is an example of what?
Natural join
What is division?
Finds tuples in one relation r that match all tuples in another relation s (can be expressed in terms of projection, set difference and cross product)
This is an example of what?
Division (derived operator)
This is an example of what?
Division (derived operator)
