Ch2 - the Relational Model of Databases
Relation and Basic Concepts
An entity is the abstract name, in the conceptual model, for a real-world object that carries data characteristics.
A relation is a two-dimensional table with relational properties, used to store entity data. A relation is often called a relation table.
- Each row of the table stores one instance of the entity (a record)
- Each column of the table holds one attribute of the entity
- A cell can store only a single value
- No duplicate rows or columns are allowed
- Rows and columns may appear in any order
In a relation, the attribute columns that uniquely identify a tuple are called a key; all other columns are non-key columns.
- A compound key uses multiple columns together to uniquely identify a tuple. For example, (student ID, course ID) serves as the compound key of a course-enrollment table.
- A candidate key: a relation may have several columns suitable as a key; each of them is called a candidate key. For example, both (student ID) and (national ID number) can be candidate keys of a student table.
- A primary key is the most representative candidate key of a relation table; each table can define only one primary key.
- A surrogate key uses a number sequence auto-generated by the DBMS as the table’s primary key. It can replace a compound primary key to achieve higher-performance data access.
Primary key notation in a relation schema: RELATION_NAME(primary key attribute, attribute 1, attribute 2, …)
What a primary key does:
- Uniquely identifies each tuple of the relation table
- Links with the foreign keys of related tables to connect relation tables
- Database files organize the table’s data storage by primary key values
- The database uses the primary key index for fast data retrieval
Relation Model Principles
The relational model is a data model that stores data entities and the relationships between them in two-dimensional table structures.
Set operations include select, project, join, intersection, union, and difference.
Relational operations specific to tuples include row insertion (Insert), modification (Update), and deletion (Delete).
Integrity Constraint
What integrity constraints do
- Eliminate duplicate tuples in relation tables
- Keep data consistent across related tables
- Enforce business data rules
Integrity constraints in the relational model
- Entity integrity: guarantees that every tuple in a relation is identifiable and unique. Primary key columns in a relation table allow neither null values nor duplicates
- Referential integrity: a foreign key column’s value must match a primary key value in another relation table, or be null
- User-defined integrity: constraints defined by the user, such as an attribute column’s value range, value type, or uniqueness
| Constraint | Description | Keyword |
|---|---|---|
| Not-null constraint | The field’s data cannot be null | NOT NULL |
| Unique constraint | Guarantees that all values in the field are unique, with no duplicates | UNIQUE |
| Primary key constraint | The primary key uniquely identifies a row; it must be non-null and unique | PRIMARY KEY |
| Default constraint | If no value is given for the field when saving data, the default value is used | DEFAULT |
| Check constraint (since 8.0.1) | Guarantees that the field’s value satisfies a given condition | CHECK |
| Foreign key constraint | Connects the data of two tables, guaranteeing data consistency and integrity | FOREIGN KEY |
Relation Model Data Operation
Select
Select:
selects rows from table according to the condition F
Equivalent to SELECT * FROM table WHERE F; for example
Project
Project:
Selects certain attribute columns from R to form a new relation
, equivalent to SELECT Sname,Sgender FROM Student
Join
Join:
Selects tuples from the Cartesian product of two relations whose attributes satisfy a given condition
The join operation takes, from the extended Cartesian product R×S of R and S, the tuples where the values of attribute group A (from relation R) and attribute group B (from relation S) satisfy
the comparison condition.
- Conditional join (cross join)
- Equi join
A special case of the conditional join. From the extended Cartesian product of R and S, it selects the tuples where the values of attributes A and B are equal, equivalent toSELECT * FROM R, S WHERE R.A = S.B - Natural join
A natural join is a special case of the equi join: it joins on all attribute columns shared by the two tables, and keeps only one copy of each shared column after joining
SELECT * FROM R NATURAL JOIN S - Outer join
The outer join is a variant of the natural join: a natural join returns rows only when the shared attribute values match, while an outer join also keeps a row from one side that has no matching row on the other, filling the gaps with NULL. It comes in left outer, right outer, and full outer forms
Others
Intersection:
Union:
Difference:
Division: , the inverse of the Cartesian product