Module 2 part 1: The Relational Data Model

The relational model

Relations:

• Are a set of records
• Are similar to a table with columns and rows

Relations are not tables

• Every relation is a table
• Not every table is a relation!

Relations have specific properties, based on the mathematical set theory

Relational Components:

Component type	Example and description
Domain Types	A domain D is a set of atomic values. An atomic value is indivisible (as far as the relational data model is concerned) Examples: - Integers - Numbers and currency - Fixed or variable length character strings - Date, timestamp - Sub-range from a data type - e.g.,1 £ grade £ 7 - Enumerated data type - e.g. Gender in {‘Male’, ‘Female’, ‘Other’} - Australian telephone numbers - Format: the digits “61” followed by 9 digits 0 - 9 - Car registration numbers - Format: 6 characters (either alpha or digits but no ‘Q’s allowed)
Attributes	Each attribute $A$ is the name of a role played by some domain $D$ in the relation named $R$. Example: salary is an attribute name
Degree	The number of attributes in a relation $R$ is called the degree of $R$ Example - Exmployee relation of degree 4
Tuples	Each tuple t is an ordered list of n values: $t = <v_{1}, v_{2}, ... v_{n}>$ where each value $v_{i}(1\le i \le n)$ is an element of the corresponding domain of attribute A i or a special value called “NULL”

Domain attribute restrictions:

• Same attribute name does not necessarily imply same domain
• Different attribute name does not necessarily imply different domain

Relation Schema and Instance

Relation Schema

• Denoted by $R[A_{1}, A_{2}, ..., A_{n}]$, includes a relation name $R$ and list of attributes $A_{1}, A_{2}, ..., A_{n}$
• Integer n is termed “degree of the relation”
• A relation schema of degree 5
  • Employee [id, name, sex, salary, department]

Relation Instance

• A relation instance r of the relation schema $R$, denoted by $r(R)$, is a set of n-tuples $r = t_{1}, t_{2}, ..., t_{m}$

Relation Schema	Relation Instance

Primary Keys

• A key is a minimal set of attributes that uniquely identify tuples in a relation.
  • The term minimal does not mean the smallest set of attributes but instead a set of attributes without any redundant attributes.

A schema may have more than one key

• Each is called a candidate key
• A primary key is candidate key chosen as main key for relation, which would be underlined.

Foreign Keys

To preserve relationships, you may need to create a foreign key (FK). A foreign key is a primary key from one table placed into another table.

• This can be viewed graphically or textually.

Example

• Let $FK$ be a set of attributes in $R1$ and let $PK$ be the primary attributes in $R2$
• $FK$ in $R1$ is a foreign key referencing $PK$ in $R2$ if
  • $FK$ and $PK$ have the same domain, and
  • For any tuple $t_{1}$ in $R1$, either $t_{1} [FK]$ is null; or there exists a tuple $t_{2}$ in $R2$, such that $t_{1}[FK] = t_{2}[PK]$

THIS MEANS

• The attributes from the foreign key have the same domain as the primary key
• the values from the foriegn key exist in the primary key, or IT IS NULL

Integrity Constraints

Domain constraint

• A domain constraint violation occurs when an attribute’s value does not appear in the corresponding domain.

Key constraint

• A key constraint violation occurs when a tuple is inserted or modified such that it has the same key value as another tuple.

Entity Integrity Constraint

An entity integrity constraint violation occurs when a tuple is inserted or modified such that part of its primary key contains the value NULL.

For primary keys that consists of multiple attributes, no part of the primary key can be null.

Referential Integrity Constraint

A referential integrity constraint can be utilised to guarantee that a department with department number 2 exists before the “Grace Mills” tuple is stored.

Semantic Integrity Constraint

Semantic integrity constraints are generally defined by the business or organization during client consultation.

Semantic constraints can be used to enforce organisation policies such as:

• “The salary of an employee should not exceed the employee’s supervisor’s salary”

Constraints and Operations

Enforcement of integrity constraints ensures that the database remains consistent.

Changes to the database such as insert, modification and deletion must not violate integrity constraints (leave the database in an inconsistent state).

If a database update is submitted to the DBMS that would violate integrity, it must be rejected.

The Transaction Concept

A transaction is an executing program that includes some database operations, such as reading from the database, or applying insertions, deletions, or updates to the database. At the end of the transaction, it must leave the database in a valid or consistent state that satisfies all the constraints specified on the database schema.

Issues

• Constraint 1: Every department should have at least one employee.
• Constraint 2: Every employee must work for a department.

Problem: Cannot create a new department since it has no employees.

Solution: Use a transaction to insert information about a new department and its employee at the same time.