Relational Model and Keys
Data is stored in relations (tables) made of tuples (rows) and attributes (columns).
Terms
| Term | Table equivalent |
|---|---|
| Relation | Table |
| Tuple | Row |
| Attribute | Column |
| Domain | Allowed values of a column |
| Degree | Number of columns |
| Cardinality | Number of rows |
Keys
| Key | Definition |
|---|---|
| Super key | Any set of attributes that uniquely identifies a row |
| Candidate key | A minimal super key |
| Primary key | The candidate key chosen to identify rows; not NULL |
| Alternate key | Candidate keys not chosen as primary |
| Foreign key | Attribute(s) referencing a primary key of another table |
| Composite key | A key made of more than one attribute |
keys.sql
CREATE TABLE department (
dept_id INT PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
Integrity constraints
- Domain: type and
CHECKconstraints. - Entity: the primary key is unique and not
NULL. - Referential: a foreign key must match an existing primary key (or be
NULL).
Relational algebra (core operators)
| Operator | Symbol | Meaning |
|---|---|---|
| Selection | σ | Filter rows |
| Projection | π | Choose columns |
| Union / Difference | ∪ / − | Set operations |
| Cartesian product | × | All pairs |
| Join | ⋈ | Combine related rows |
| Rename | ρ | Rename a relation or attribute |
π name (σ salary > 50000 (employee)) is SELECT name FROM employee WHERE salary > 50000;.
Loading Comments...