Foundation of Modern Database Systems: Relations, Constraints, and Operations
The Evolution of the Relational Model
Introduced by Edgar Codd in 1970, the relational model revolutionized database management systems. It replaced complex network and hierarchical models with a simple, elegant table-based approach.
The main abstract element is a table - easy to understand and work with
Modify physical schema (storage) without affecting logical schema (structure)
Modify conceptual schema without changing applications through views
Today, the relational model is the most widely used model in the world. Competitors like NoSQL and XML databases have not achieved the same level of popularity or adoption.
Understanding Relations, Tuples, and Attributes
| Informal Terms | Formal Terms | Description |
|---|---|---|
| Table | Relation | A set of rows and columns representing entities |
| Column Header | Attribute | A property or field in a relation |
| All Possible Column Values | Domain | Set of allowable values for an attribute |
| Row | Tuple | A single record in a relation |
| Table Definition | Schema of Relation | Structure definition with attributes and types |
| Populated Table | State/Instance of Relation | Actual data at a specific moment |
A Relational Database consists of:
Number of columns (attributes) in a relation
STUDENT(ID, Name, Major) has degree = 3
Number of rows (tuples) in a relation
If STUDENT has 500 students, cardinality = 500
STUDENT Relation Schema:
Sample Instance:
| Student_number | Name | Class | Major |
|---|---|---|---|
| 17 | Ahmad Ali | 1 | CS |
| 8 | Sara Khalid | 2 | SE |
Properties that Define Relational Tables
All values are considered atomic (indivisible). No composite or multivalued attributes permitted. This is known as First Normal Form.
Each value in a tuple must be from the domain of its attribute, or it can be NULL if allowed.
Tuples are not ordered in a relation, even if they appear ordered in the tabular form.
Attributes and their values within tuples are considered to be ordered (though alternative definitions exist).
An alternative representation doesn't require attribute ordering and includes both name and value:
This is called a self-describing representation.
A special NULL value is used to represent values that are:
Rules That Ensure Data Integrity
Constraints are conditions that must hold on all valid relation states. They ensure data accuracy, consistency, and integrity.
Uniqueness constraint: No two tuples can have the same value for key attributes.
SerialNo is the primary key
Primary key cannot be NULL: PK attributes cannot have null values in any tuple.
Why? Primary key values identify individual tuples. Without a value, the tuple cannot be uniquely identified.
Foreign key constraint: Maintains consistency between related relations.
A tuple in referencing relation R1 has foreign key (FK) that references primary key (PK) in referenced relation R2.
FK value must be:
Type constraint: Every value must be from the attribute's domain.
If Age domain is Integer(0-120), then:
Specifies whether NULL values are permitted for specific attributes.
If every STUDENT must have a valid Name:
Scenario: EMPLOYEE references DEPARTMENT
Every employee's D_num must match an existing Dnumber in DEPARTMENT (or be NULL if allowed).
In relational database schema diagrams:
Modifying Database State
Database state changes through three basic operations:
Add a new tuple to a relation
Remove an existing tuple from a relation
Change attribute values of an existing tuple
Each update operation creates a new database state. The relational database state is the union of all individual relation states at that moment.
How Each Operation Can Violate Constraints
Violated if an attribute value is not of the specified domain
❌ INSERT STUDENT(123, "Ahmad", "junior", "CS")
If Class should be Integer, "junior" violates domain constraint
Violated if the key value already exists
❌ INSERT STUDENT(123, "Sara", 2, "SE")
If Student_number 123 already exists, violates uniqueness
Violated if FK references non-existent PK
❌ INSERT EMPLOYEE(..., D_num=99)
If Department 99 doesn't exist, violates referential integrity
Violated if PK value is NULL
❌ INSERT STUDENT(NULL, "Omar", 1, "IT")
Primary key cannot be NULL
DELETE may violate referential integrity if the PK value being deleted is referenced by FKs in other tuples.
When deletion would violate referential integrity:
One of these options must be specified during database design for each FK constraint.
If this employee is referenced by:
This deletion is unacceptable without CASCADE or other handling.
Modifying a tuple can violate constraints similar to INSERT and DELETE:
Similar options exist for UPDATE operations:
Key Takeaways from the Relational Model
Relations (tables) with tuples (rows) and attributes (columns) form the foundation of modern databases
Table-based abstraction is easy to understand, making relational databases accessible and widely adopted
Four main constraints (domain, key, entity, referential) ensure data remains accurate and consistent
INSERT, DELETE, UPDATE modify database state while respecting integrity constraints
Foreign keys establish relationships between tables, maintaining referential integrity
Physical and logical data independence separate storage from structure and structure from applications
With the relational model foundations established, we can now: