Week 12 Flashcards
Characteristics of a Relation
All the cells in a relation must hold a single value.
No repeating groups – no more than one attribute from the same physical and logical
domain (ex. author1, author2, etc.)
All values for an attribute (column) must be of the same logical and physical domain.
Tuples (rows) must be unique (must have a primary key).
Unique name for each attribute within a relation.
Order of tuples and attributes is unimportant.
Relational Algebra
Consists of variables – representing relations.
Operators – represent the actions we can take with these relations.
The output must meet the characteristics of a relation.
Operators
Operations to remove: selection(which tuples), projection(which columns)
Set based: union, intersection, difference
join two tuples: product joins
Projection
The projection of a relation produces a new relation with selected columns from the
original relation
Limits the attributes that will appear in the resulting relation to those that are specified
Can be used to rearrange the order of columns in the resulting relation
Duplicate tuples (rows) from the result set are removed
Notation variations:
RELATION[attr1, attr2, attr4]
Selection
Can be used to specify condition(s) that must be met in order for a tuple to appear in the
result set
Condition can include: =, <, >, ≤, ≥, Negation: ¬, OR: ∨, AND: ∧
Notation variations:
RELATION WHERE condition_to_be_met
Example: EMP WHERE dept# = 3
Distinct Keyword
When attempting a relational algebra using SQL, the DBMS may not produce a relation as
duplicate rows can be in the result set.
Can use the DISTINCT key word as part of SELECT clause of SELECT statement to remove
duplicate rows from a result set.
Not needed for every SELECT statement written!
Only those where you are asked to produce a relational algebra operation using SQL.
Format: SELECT DISTINCT ..
Union
The union of two relations produces a resulting relation with the all of the tuples from
each relation.
Relations must be Union Compatible.
Remember, the resulting relation is ALWAYS a relation and must meet the characterics of a relation
(i.e. duplicate tuples are removed)
Notation variations:
§ A ∪ B
§ A + B
§ A UNION B
Union Compatible
Each relation has the same number of attributes
The attributes must be in the same order in the two relations, which means that
corresponding attributes must come from the same physical and logical domains
Example:
§ RIT_STUDENT(univID, firstName, lastName)
§ MCC_STUDENT(stuID, fn, ln)
Knowledge required to determine logical domains
§ EMP(emp#, name, salary)
§ DEPT(dept#, name, income)
Does EMP UNION DEPT make sense?
If performing in a DBMS it would be allowed because the physical domains match. However, the resulting relation is of little value because a DBMS can’t determine logical domains.
Commutative
order of the relations will not impact the resulting relation, not all relation algebra operations are commutative
but unions are
Difference
Result is all of the tuples from the first relation that do not appear in the second relation.
Relations must be union compatible
Is NOT cummutative
Order matters: R – S ¹ S - R
Notation:
A – B
A DIFFERENCE B
Subqueries
You can have many nested subqueries
The subquery should be in parentheses
The subquery can be part of the WHERE clause of the parent query
The datatype being returned by the subquery must match the field referenced in the
WHERE clause
Any projection of attributes to the user can only include attributes from the outermost
query
Intersection
The intersection of two relations is a relation that contains only the tuples that appear in
both relations.
Relations must be union compatible.
Is commutative
A INTERSECT B = B INTERSECT A
Notation variations:
§ A ∩ B
§ A INTERSECT B
Product
The set of tuples formed by combining each tuple in one relation with each of the tuples
in another relation.
Also called:
§ Cartesian product
§ Cross product
Notation variations:
§ A x B
§ A PRODUCT B
§ By itself, not very useful, but is used as part of other operators (joins)
Joins
Includes a combination of a relational algebra product and selection
§ Types of Relational Algebra Joins:
§ Equijoin
§ Natural join
§ Also includes a projection
§ Outer join (also known as a directional join)
§ Left outer join
§ Right outer join
§ Full outer join
Theoretical Relational Algebra JOIN Types
EQUIJOIN and NATURAL JOIN selects tuples where the “ON” attribute(s) has non-null values in both relations (matches only)
LEFT OUTER JOIN selects tuples where the “ON” attribute(s) has non-null values in the first relation (left side)
RIGHT OUTER JOIN selects tuples where the “ON” attribute(s) has non-null values in the second relation (right side)
FULL OUTER JOIN selects tuples where the “ON” attribute has
non-null values in either relation
