--- title: Ch2 - The Relational Model of Databases date: 2024-03-08 excerpt: Relational model, relation tables, primary keys, foreign keys, compound keys, entity integrity, and referential integrity. tags: [DataBase] cover: https://assets.vluv.space/cover/DataBase/DBRelationModel.webp updated: 2026-07-08 21:17:39 lang: en i18n: cn: /Ch2-DbRelationModel translation: 2 --- ## 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: $\sigma_{p}(R) = \{t | t \in R \, \text{and} \, p(t) \, \text{is true}\}$ $\sigma_F(table)$ selects rows from table according to the condition F Equivalent to `{sql} SELECT * FROM table WHERE F`; for example $\sigma_{age<20>}(Student)$ ### Project Project: $\pi_{A}(R) = \{t[A] | t \in R\}$ Selects certain attribute columns from R to form a new relation $\pi_{(Sname,Sgender)}(Student)$, equivalent to `{sql} SELECT Sname,Sgender FROM Student` ### Join Join: $R \bowtie S = \{t | t \in R \, \text{and} \, t \in S\}$ 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) $R \bowtie_{c} S = \sigma_c(R \times S)$ - Equi join $R \bowtie_{R.B=S.B} S = \sigma_{R.B=S.B}(R \times S)$ 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 to `{sql} SELECT * 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 `{sql} 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: $R \cap S = \{t | t \in R \, \text{and} \, t \in S\}$ Union: $R \cup S = \{t | t \in R \, \text{or} \, t \in S\}$ Difference: $R - S = \{t | t \in R \, \text{and} \, t \notin S\}$ Division: $R ÷ S$, the inverse of the Cartesian product