Week 12 Flashcards

1
Q

Characteristics of a Relation

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
1
Q

Relational Algebra

A

Consists of variables – representing relations.

Operators – represent the actions we can take with these relations.

The output must meet the characteristics of a relation.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Operators

A

Operations to remove: selection(which tuples), projection(which columns)

Set based: union, intersection, difference

join two tuples: product joins

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Projection

A

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]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Selection

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Distinct Keyword

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Union

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Union Compatible

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Commutative

A

order of the relations will not impact the resulting relation, not all relation algebra operations are commutative
but unions are

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Difference

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Subqueries

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Intersection

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Product

A

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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Joins

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Theoretical Relational Algebra JOIN Types

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Equijoin

A

In an equijoin, specified attributes are matched using an equality test. All attributes will
appear in the resulting relation.

12
Q

Natural Join

A

Similar to an equijoin.
* In a natural join, specified attributes are matched using an equality test.
Only one of the “joining” attributes appears in the final result

13
Q

OUTER Join

A

There are cases where you want to look at all tuples from one relation and any matching
tuples from the other relation

Outer joins allow you to do this
§ LEFT OUTER JOIN
§ RIGHT OUTER JOIN
§ FULL OUTER JOIN

Similar notation to INNER JOIN, except join type is changed:
EMP LEFT OUTER JOIN (EMP.dept# = DEPT.dept#) DEPT

14
Q

LEFT OUTER JOIN

A

Left Outer Join: Gets all tuples from the relation that appears on the left-hand side of the
join and any matching rows from the relation on the right side.
§ In case there are no matching tuples from the right side, NULL will appear for “right side”
attributes.

DEPT LEFT OUTER JOIN (DEPT.dept# = EMP.dept#) EMP

15
Q

Right OUTer Join

A

Right Outer Join: Gets all tuples from the relation on the right side of the statement and
any matching tuples from the relation on the left side.
¡ In case there are no matching tuples on the left side, attribute values for “left side” are
NULL.
¡ Example: STUDENT RIGHT OUTER JOIN (major = dept) DEPT

16
Q
A