What is the domain of an attribute?
The set of allowed values for that attribute.
What does "atomic" mean for attribute values?
Indivisible — attribute values are (normally) required to be atomic.
What is null?
A special value that is a member of every domain; it indicates that the value is "unknown". It causes complications in the definition of many operations.
Relation schema (with the example)
R = (A1, A2, …, An), where A1 … An are attributes — e.g., instructor = (ID, name, dept_name, salary).
Formally, what is a relation r?
A subset of D1 × D2 × … × Dn — a set of n-tuples (a1, …, an) where each ai ∈ Di.
Does the order of tuples matter in a relation?
No — relations are unordered; tuples may be stored in an arbitrary order.
Superkey
A set of attributes K ⊆ R whose values are sufficient to identify a unique tuple of each possible relation r(R) — e.g., {ID} and {ID, name} for instructor.
Candidate key
A minimal superkey — e.g., {ID} for instructor.
Primary key
One of the candidate keys, selected to be the primary key.
Foreign key constraint
A value in one relation (referencing) must appear in another (referenced) — e.g., dept_name in instructor references department.
The 6 basic operations of relational algebra
Select, project, union, set difference, Cartesian product, and rename.
The three pure query languages and their computing power
Relational algebra, tuple relational calculus, domain relational calculus — equivalent in computing power.
What are the input and the output of every relational query?
Input: a table (or set of tables). Output: a table; all data in the output appears in one of the input tables.
σ (selection)
Selects rows (tuples): returns the rows of the input relation that satisfy the predicate — e.g., σ salary >= 85000 (instructor).
Π (projection)
Selects columns (attributes): outputs the specified attributes from all rows and removes duplicate tuples — e.g., Π ID, salary (instructor).
How can intersection be written with set difference?
r ∩ s = r − (r − s).
r = (α,1), (α,2), (β,1); s = (α,2), (β,3). What are r ∪ s, r − s and r ∩ s?
r ∪ s = (α,1), (α,2), (β,1), (β,3); r − s = (α,1), (β,1); r ∩ s = (α,2).
Cartesian product r × s: how many rows?
Every row of r is paired with every row of s: rows(r) × rows(s) — e.g., 2 × 4 = 8 rows on slide 15.
ρ x (E)
Rename: returns the expression E under the name X, so a relation can be referred to by more than one name.
In r × s, both relations have attribute B. How are the two Bs named?
r.B and s.B.
Natural join r ⋈ s
A relation on schema R ∪ S: for each pair of tuples that have the same value on each attribute in R ∩ S, add one tuple combining them.
Write r ⋈ s using Π, σ and × (r on A,B,C,D; s on B,D,E).
Π A, r.B, C, r.D, E (σ r.B = s.B ∧ r.D = s.D (r × s)).