Terse Notes — Fundamentals of Database Systems Part 5

The Relational Data Model and Relational Database Constraints

Photo by Arnold Antoo on Unsplash

Book Credit to Elmasri Ramez And Navathe Shamkant

Book: Fundamentals Of Database System, 7th Edition by Elmasri Ramez And Navathe Shamkant

Ch 5 — The Relational Data Model and Relational Database Constraints

Major types of constrains:

1. Inherent model-based constraints or implicit constraints — have to do with things inherent to the data model
2. Schema-based constraints or explicit constraints — these are constraints directly expressed in the schema of the data model. Specified in the DDL.
3. Application-based — these cannot be directly expressed in the schemas of the data model and are enforced by the application program or some other way.
4. Data Dependencies — used for testing the goodness of the design of the relational database and used in normalization process.

Schema-based Constraints:

1. Domain constraints
2. Key constraints
3. Entity integrity constraints
4. Referential integrity constraints

Operations:

Modification or update operations:

1. Insert — can violate all 4 constraints above
2. Delete — can only violate the referential integrity constraint
3. Update/modify

Review Questions

5.1. Definitions

Domain: The set of atomic values from which an attribute takes its values. Common domains include integers, real numbers, strings, dates, etc.

Attribute: A single property or characteristic of a relation. Each attribute has a name and a domain.

n-tuple: A tuple or row in a relation, containing one value for each attribute of the relation. An n-tuple has n values corresponding to the n attributes of the relation.

Relation schema: The definition of a relation, specifying the name of the relation and the names and domains of each attribute in the relation.

Relation state: The set of tuples currently stored in a relation at a given point in time.

Degree of a relation: The number of attributes in the relation schema.

Relational database schema: The set of relation schemas and integrity constraints that define the structure of the database.

Relational database state: The set of relation states for all relations in the database at a given point, satisfying all integrity constraints.

5.2. Tuples are not ordered

Tuples in a relation are not ordered because the relational model is based on set theory, where sets are unordered collections of elements (tuples). The order of tuples is immaterial and has no semantic significance.

5.3. Duplicate tuples not allowed

Duplicate tuples are not allowed in a relation because relations are defined as sets of tuples. By the mathematical definition of a set, all elements must be distinct and unique.

5.4. Key vs Superkey

A superkey is a set of one or more attributes that uniquely identifies each tuple in a relation. A key is a minimal superkey — a superkey with no redundant attributes. In other words, every key is a superkey, but not every superkey is a key.

5.5. Designating a primary key

One of the candidate keys (minimal superkeys) is designated as the primary key to uniquely identify tuples within the relation. This simplifies data access and referencing from other relations.

5.6. Relations vs Tables/Files

Relations differ from ordinary tables/files in several ways:

• Tuples are unordered
• Duplicate tuples are not allowed
• Attribute values are atomic (e.g. no nested relations)
• Conceptual representation uses mathematical notation and set theory
• Operations on relations follow strict mathematical rules

5.7. Reasons for NULL values

NULL values can occur in relations for several reasons:

• The value is unknown or missing
• The value is not applicable for that tuple
• The attribute itself is optional/nullable
• To represent “no value” or “value at present unknown”

5.8. Entity and Referential Integrity

Entity Integrity: States that no primary key value can be NULL, as primary keys identify tuples.

Referential Integrity: Ensures values representing relationships between tuples across relations remain valid and consistent. Foreign key values must match a primary key value in the referenced relation or be NULL.

These constraints are important for maintaining data consistency and validity.

5.9. Foreign Key

A foreign key is a set of attributes in one relation that references the primary key of another relation. It is used to represent and enforce relationships between tuples across relations based on the referential integrity constraint.

5.10. Transaction vs Update Operation

A transaction is a sequence of one or more database operations that must be executed as a single logical unit. Transactions enforce the ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure data integrity.

An update operation is a single operation that modifies data in the database, e.g. inserting, deleting or updating tuples in a relation. It is one component of a transaction.