Relational Model Introduction

Introduction to the Relational Model

• E. F. Codd first proposed the relational model in his paper 'A relational model of data for large shared data banks' in 1970.
• Relational databases are founded on mathematical principles.
• Relational databases are made of a group of relations that hold data describing a business environment.

Terminology

• The relational model is based on the mathematical concept of a relation, and physically, this is represented as a table.
• A relation is a table with columns and rows.
• An attribute is a named column of a relation.
• A domain is the set of allowable values for one or more attributes.
• A tuple is a row of a relation.
• Degree refers to the number of attributes in a relation.
• Cardinality is the maximum number of entities in a relationship.
• Modality is the minimum number of entity occurrences in a relationship.

Mathematical Relations

• Relation means reviewing concepts from mathematics.
• A Cartesian product, D1 X D2, is a set of all ordered pairs where the first element is a member of D1 and the second is a member of D2.
• Any subset of a Cartesian product forms a relation
• It's possible to denote relation pairs with conditions.

Database Relations

• Relation schema is a named relation defined by a set of attributes and domain name pairs.
• Relational database schema includes a set of relation schemas, each with a distinct name.
• Example of an employee's relation schema: Employees (empid, empname, empemail, empaddress, deptid).
• Example of a department's relation schema: Departments (deptid, deptname, locid).
• Example of a locations' relation schema: Locations (locid, locname, locaddress).

Properties of Relations

• Relation names must be distinct within a relational schema.
• Each cell of a relation contains exactly one atomic value.
• Each attribute has a distinct name.
• All values in an attribute come from the same domain.
• Each tuple is distinct; no duplicate tuples exist.
• The order of tuples has no theoretical significance.
• The order of attributes has no significance.

Relational Keys

• In each relation there are no duplicate tuples.
• Relational keys are attributes used to identify each tuple uniquely.
• Super key: Attribute or set of attributes that uniquely identify a tuple in a relation.
• Candidate key: Minimal super key with no redundancy.
• Primary key: Candidate key chosen to uniquely identify tuples within the relation.
• Alternate Key: Candidate key not chosen as the primary key.
• Foreign Key: Attribute or group of attributes that serves as the primary key of one relation and appears in another.

Integrity Constraints

• Integrity constraints ensure data accuracy.
• Inaccurate data can lead to an unreliable database system and affect business operations.
• Null values represent values for an attribute that are unknown or not applicable.
• Two principal rules for the relational model: entity integrity and referential integrity.

Integrity Constraint Rules

• Entity Integrity: Primary key cannot be null, and it must be unique.
• Referential Integrity: Foreign key must match a primary key value in its home relation or be wholly null.

Actions for Referential Integrity

• For INSERT operations: Child table insert fails if no matching primary key exists in the parent table.
• For DELETE operations: Deletion fails if matching foreign key exists in a child table.
• For UPDATE operations: Update fails if a match foreign key exists in the child's table, also fails if there is no existing primary key in the parent's table.

ON DELETE and ON UPDATE Actions

• ON DELETE and ON UPDATE actions for foreign keys can be: NO ACTION, RESTRICT, SET NULL, SET DEFAULT, or CASCADE.
• If unspecified, the default action is NO ACTION.
• NO ACTION: No special action occurs when a parent key is modified or deleted.
• RESTRICT: Prohibits deleting (ON DELETE RESTRICT) or modifying (ON UPDATE RESTRICT) a parent key if child keys map to it.
• SET NULL: Sets child key columns to SQL NULL values when a parent key is deleted (ON DELETE SET NULL) or modified (ON UPDATE SET NULL).
• SET DEFAULT: Sets child key columns to a default value instead of NULL.
• CASCADE: Propagates delete or update operations on the parent key to each dependent child key.
• ON DELETE CASCADE: Each row in the child table associated with the deleted parent row is also deleted.
• ON UPDATE CASCADE: Values stored in each dependent child key are modified to match the new parent key values.
• General Constraints: Additional rules that have been specified by users or database administrators that define or constrain some aspect of the enterprise.

Views

• Views are virtual relations that do not exist in the database but are produced upon request.
• Contents of views are defined by queries on base relations.
• Views are dynamic; changes to base relations are immediately reflected.

Purpose of Views

• Views provide security by hiding parts of the database from certain users.
• Views let users access data in a customized way.
• Views simplify complex operations on base relations.

Updating Views

• All updates to a base relation should reflect in all views referencing it.
• If a view updates, the underlying base relation should mirror the change.
• Updates are only allowed if the query involves a single base relation & contains a candidate key of the base relation.
• Updates are not allowed if the query involves multiple base relations.
• Updates are not allowed if the query involves aggregation or grouping operations.

Classes of Views

• Views that are theoretically not updatable.
• Views that are theoretically updatable.
• Views that are partially updatable.