Relational Data Model

The relational data model defines relations between data, where keys can be used to connect and relate different pieces of data to provide more data & information to the user.

Relation §

A relation (also called a table) is defined with a schema (which is like a blueprint), and then an instance is created using the schema.

Slight ambiguity

Technically, a relation is defined as a set of tuples/rows. By the definition of a set, every relation is unordered, and more specifically, unique. However, in most cases a table is treated as a multiset (i.e. a set which allows duplicates). So they aren’t actually the same thing, but are mostly treated as if they are.

Schema §

• Identifies the relation with a name
• Defines every attribute/field in the relation with an identifier and a data type
• Is a blueprint, defines metadata for the relation. Does not contain actual data.

$$\text{Employee(ssn, name, age)}$$

Instance §

• A table, with rows/records and columns/fields
• The number of rows is the cardinality of the relation instance.
• The number of columns is the degree (or arity) of the instance

Instance example:

ssn	name	age
53666	Jones	18
53688	Smith	18
56347	Smith	19
45645	Alice	21
The table above has cardinality 4 (rows) and degree 3 (columns)

Keys §

Keys are specific attributes/columns/fields that can be used to associate tuples/records/rows in different tables. They are unique identifiers.

Superkey §

A set of fields is can be considered a superkey if, for two records, they cannot have the same value for every field in the set. In other words, a superkey is uniquely defined in a relation.

ssn	name	age
53666	Jones	18
53688	Smith	18
56347	Smith	19
45645	Alice	21

The set $\{\text{name, age}\}$ is a superkey, because no entries in the relation instance have the both the same age and the same name.

Key §

A set of fields is a key if it is a superkey and minimal, i.e. the smallest subset of fields that is a superkey.

From the employee instance above, even if $\{\text{ssn, name}\}$ is a superkey, $\{\text{ssn}\}$ by itself is also a superkey (since no records have the same ssn ) and hence it is a key.

Primary Key §

Out of all possible keys in a relation, one is chosen as a primary key, which can be used by other relations (tables) to refer to our original relation.

Every relation must have a primary key.

A primary key is:

• Unique
• Minimal
• Not Null

Note that a primary key, although it is minimal, does not necessarily need to only contain one attribute. In that case, the primary key is considered composite.

Candidate Key §

Any keys that are not primary keys are known as candidate keys.

A candidate key is still unique and minimal, but is not used to define the relation.

If a candidate key is composite, that is, it is made up of multiple attributes, then those attributes are called prime attributes.

Foreign Key §

An entity that references a key of another entity has that key saved as a foreign key.

For example, if a Book entity references a Page entity, it can store the primary key PageID as a foreign key

Integrity Constraint (IC) §

Any condition that must be true for any instance of a database.

• Defined in the database schema
• Checked whenever records/tuples are added to the instance

A legal instance is one that satisfies all it’s specified ICs. A DBMS needs to block illegal instances.

From ER Diagram §

• Every entity set is a relation
  • An entity is a tuple/record
• ER Attributes are now relational attributes
  • Multi-valued Attributes & Composite Attributes become unpacked/flattened

ER Relationships §

• Every ER relationship is also a relation in this data model.

• In translating a many-to-many relationship set to a relation, attributes of a new relation must include:

  • Keys for each participating entity set (as foreign keys). This set of attributes forms a superkey of the relation.
  • All descriptive attributes.

$$\text{Borrower(UID,Name, Age)}$$

$$\text{Book(BookID, Title, Author)}$$

$$\text{Borrows((UID, BookId), BorrowDate)}$$

For Key Constraints, we replace the creation of a Borrows instance, and instead directly add all relationship data to the ‘one’ entity/relation.

The primary key of the ‘many’ entity becomes the foreign key of the ‘one’ entity $\text{Book(BookId, emphUID, Title, Author, BorrowDate)}$

For Participation Constraints, if the participation is total, we ensure the foreign key is NOT NULL (it cannot be left empty). This is done in the

For Weak Entities and their identifying relationships, we make a foreign key of the parent entity, and add it to the primary key set:

$$\text{Child(ChildID,emphPersonID, Other Attributes)}$$