# The Relational Model

Chapter 3

## Outline

### Concepts

Table, Row, Column Header, Column Type :: Relation, Tuple, Attribute, Domain

domain
specialized data type, set of atomic values (indivisible to relational model)
relation schema
`R(A₁,A₂,…,An)`, relation name and list of attributes (each of which has a name and a domain)
attribute
the name of a role played by a domain in that relation
relation (relation state)
`r(R)`, a set of tuples. Each tuple is an ordered list of values, corresponding with the domain of their attribute, and representing a fact. A relation state is a subset of the Cartesian product of the domains defining the relation schema.
characteristics of relations
• a relation is a set – tuples are not in any order, and have no duplicates
• flat relational model – values are atomic, not structures or lists
• NULL (ω) – “information missing”, “not applicable”; ambiguous semantics, not a member of any domain
• semantics of a relation – each tuple is an assertion of a true fact; sometimes confusing that this could be about an entity or a relationship; the schema can be interpreted as a predicate, each tuple is a list of values for which the predicate is true.

#### Notation

• `Q,R,S`: relation names
• `R(A₁…)`: relation schema (intension of the relation)
• `q,r,s`: relation states (extension of the relation)
• `t,u,v`: tuples
• a relation name by itself refers to the current relation state, not the relation schema
• dot notation can qualify an attribute name (like `Q.a` versus `R.a`) but all attributes in a schema must be uniquely named
• `t=<v₁,v₂,…vn>`: a tuple with its component values
• `t[Ai], t.Ai, t[i]`: all ways of referring to the ith value of the tuple t, which corresponds with the attribute Ai
• `t[Au,Aw,…,Az]` (and similar): a subtuple formed from the values corresponding with the listed attributes

### Constraints and schemas

Inherent model-based constraints or implicit constraints – already discussed. Schema-based constraints or explicit constrants – expressed by the schema, written in the DDL, enforced by the DBMS. Application-based, semantic constraints, or business rules – other than the above; typically enforced by the application.

domain constraints
within a tuple the value of each attribute must be an element from the domain of that attribute
key constraints
a subset of attributes called a `superkey (SK)`, that specify uniqueness on subtuples of their relation (for any distinct tuples `t₁` and `t₂` that are members of a relation `r(R)`, `t₁[SK]≠t₂[SK]`). A `key (K)` is a minimal superkey. In notation, the `primary key` is usually underlined. The remaining `candidate keys` are not and are called ```unique keys```.

null constraints
applied to attributes, specifying that their values must be NOT NULL
relational database schema
A set of relational schemas and a set of integrity constraints. Also, a relational database state is a set of relation states (that satisfy the integrity constraints).
entity integrity constraint
no primary key value can be `NULL`
foreign key
a set of attributes from one relation schema that have the same domains as, and refer to, the primary key of a second relation schema
referential integrity constraint
One relation schema `R₁`has a foreign key `FK` referencing a second relation schema `R₂` with primary key `PK`, and if for a tuple `t₁ ∈ r₁` that `t₁[FK] ≠ ω`, `t₂[PK] ∈ r₂`. A foreign key must match its primary key unless it's `NULL`.

other constraints
semantic integrity constraints; enforced by applications, or by the DBMS if it supports a constraint specification language with features like `CREATE TRIGGER` or `CREATE ASSERTION`.

retrieval
A function taking a relational algebra expression, returning a result relation
insert
Adds a new tuple to a relation. Can potentially violate any constraint.
delete
Remove a tuple from a relation. Can violate referential integrity.
update
Modify a tuple in a relation. Depending on what's modified, can violate whatever the previous two can.
Transactions

```relational constraints, domain constraints, a lot of definitions to take in.
superkey? (versus primary key, candidate key)
foreign key
atomic attributes
“it seems very difficult to maintain referential integrity” you said it

* are key values in a database used like key values in a hash table?
as an associative array, yes. as an actual hash table, also yes

examples of modifications SQL and RDBMSs do not implement the relational model! (they approximate it)
```

## Questions

Which is a plausible candidate key for the `Course` relation?
1. `<Credit_hours, Department>`
2. `<Course_name, Course_number>`
3. `<Department>`

### Constraints

Assume the database is in a consistent state. Which of these modifications will definitely violate a referential integrity constraint?
1. `INSERT INTO Project VALUES ('Take over world', NULL, 'Everywhere', 5);`
2. ```INSERT INTO Works_on VALUES ('078-05-1120', 14, 30); INSERT INTO Works_on VALUES ('078-05-1120', 14, 18);```
3. ```DELETE FROM Project WHERE Pnumber = 14; INSERT INTO Works_on VALUES ('078-05-1120', 14, 30);```

← Main Page