Formal Relational Model and Integrity Constraints in DBMS

Introduction

A college database stores information about students, departments, and courses. Organizing this information into tables makes it easier to manage, but the database also needs rules. Two students should not share the same student ID, and a student should not reference a department that does not exist.

The formal relational model provides the mathematical foundation for organizing this data. Integrity constraints define which values and relationships are valid. Together, they explain how a relational database represents information and maintains consistency when records are inserted, updated, or deleted.

Relational Model in DBMS

The relational model represents data as a collection of relations, commonly displayed as tables. Each relation describes a particular kind of information, such as students or departments, using named attributes and a set of tuples.

Consider these two relations as examples:

Student:

Department:

The main relational model terms are:

• Relation: A set of tuples with the same attributes. Student and Department are relations.

• Tuple: One record in a relation. The row containing Asha’s details is a tuple.

• Attribute: A named property, represented as a column, such as StudentID or Semester.

• Domain: The set of permitted values for an attribute. For an eight-semester programme, the domain of Semester could be the integers from 1 to 8.

• Degree: The number of attributes in a relation. The degree of Student is 5.

• Cardinality: The number of tuples in the current relation. The cardinality of Student is 3.

Here, cardinality means the number of rows. It is different from relationship cardinality in an ER model, where terms such as one-to-many describe how entities participate in relationships.

Relation Schema and Relation State

A relation schema describes the structure of a relation, including its name, attributes, and associated domains. For the student relation, a compact schema notation is Student(StudentID, Name, Email, Semester, DeptID).

A relation state, also called a relation instance, is the set of tuples stored at a particular time. Adding another student changes the relation state and its cardinality. The schema remains unchanged unless the structure itself is modified.

Formally, for a schema R(A₁, A₂, ..., A_n), each attribute Ai has a domain dom(A_i). A relation state satisfies:

r(R) ⊆ dom(A₁) × dom(A₂) × ... × dom(A_n)

The symbol × represents the Cartesian product: all possible combinations containing one value from each domain. A relation contains a subset of these combinations. Additional integrity constraints determine which subsets are valid database states.

A tuple can be written as t = (v₁, v₂, ..., v_n), where each value belongs to its corresponding domain. The notation t[Name] selects the value of the Name attribute; for Asha’s tuple, t[Name] = 'Asha'.

A relational database schema consists of the relation schemas and their constraints. The corresponding database state consists of their current relation instances. A state is valid only when it satisfies all applicable constraints.

Properties of a Relation

Relations have several properties that follow from their mathematical structure:

• Distinct tuples: A relation is a set, so it cannot contain duplicate tuples. Individual attribute values may repeat, as DeptID = 10 does in the student example.

• No meaningful row order: Rearranging the displayed rows does not change the relation.

• Values remain associated with attributes: Columns can be displayed in a different order without changing their meaning. In positional tuple notation, however, values must follow the stated attribute order.

• Atomic attribute values: In the conventional first-normal-form model, each attribute contains one value from its domain, rather than a repeating group of values.

• Consistent structure: Every tuple has the attributes specified by the relation schema, with values drawn from their corresponding domains.

The basic mathematical definition assumes actual domain values. Practical SQL databases also use NULL to represent missing information, such as an unknown or inapplicable value. NULL is a special marker; it should not be treated as zero or ordinary text.

SQL tables also differ from mathematical relations because they can contain duplicate rows unless suitable constraints prevent them. Similarly, a query does not guarantee a particular output order unless an ordering is explicitly requested.

Keys in the Relational Model

A key identifies a tuple through one or more attributes. Key selection must follow the rules of the application, rather than values that happen to be different in a small sample.

For the student example, assume every student has a unique StudentID and a required, unique Email.

“Minimal” means that no attribute can be removed from a candidate key. It does not mean every candidate key must have the same number of attributes.

A relation can have several candidate keys, but only one is designated as its primary key. That primary key can contain multiple attributes. A foreign key serves a different purpose: its values may repeat because several students can belong to the same department.

Types of Integrity Constraints in DBMS

An integrity constraint is a condition that every valid database state must satisfy. These conditions restrict individual values, identify records, and preserve relationships between relations.

Some restrictions, such as the absence of duplicate tuples, are inherent in the relational model. Others are declared in the database schema. Application-specific constraints express additional requirements, such as limits on the number of students a department can admit.

Domain Constraints

A domain constraint requires each attribute value to belong to its permitted domain. A domain can restrict the data type, range, or set of allowed values. Specifying an integer type alone does not necessarily enforce the complete business requirement.

For example, if Semester must be an integer from 1 to 8, the value 4 is valid, while 12 is outside the permitted range. SQL data types and CHECK constraints can enforce these restrictions. A separate NOT NULL constraint can require a value to be present.

Key Constraints

A key constraint requires distinct tuples to have different values for a declared key. Formally, for any two distinct tuples t1 and t2 and key K, the condition is t1[K] ≠ t2[K].

In the student relation, another record with StudentID = 101 violates the key constraint even if its name, email, and department are different. The complete rows would differ, but the chosen identifier would no longer identify exactly one student.

For a composite key, uniqueness applies to the combination of values. In Enrollment(StudentID, CourseID), a student may appear in several rows and a course may appear in several rows, while each student–course pair must remain unique.

Entity Integrity Constraints

Entity integrity requires every attribute of the primary key to be non-NULL. A primary key provides the identity of a tuple, so none of its components can be missing.

A student record with StudentID = NULL therefore violates entity integrity. If a relation uses a composite primary key, every component must be present. Primary-key uniqueness comes from the key constraint, while entity integrity adds the requirement that the identifier cannot contain NULL.

Referential Integrity Constraints

Referential integrity ensures that references between records remain valid. A foreign key identifies the referencing attributes, and the referenced key identifies the records to which those attributes may refer.

In the example, Student.DeptID references Department.DeptID. For this single-column foreign key, every non-NULL department ID in Student must match an existing department ID:

• Valid reference: A student with DeptID = 10 references Computer Science.

• Invalid reference: A student with DeptID = 30 violates referential integrity because department 30 does not exist.

• Optional reference: DeptID = NULL may be permitted if department assignment is optional and the column is not constrained as NOT NULL.

A foreign key commonly references a primary key, but it can also reference a suitable unique key supported by the DBMS. It may reference the same table, such as an employee’s ManagerID referencing another employee’s EmployeeID.

A composite foreign key must match the corresponding key combination in a single referenced row. Its treatment of partially missing values depends on the declared matching rules; the simple single-column rule should not be applied blindly to every composite case.

Semantic or Business Constraints

Some requirements come from the meaning of the application. For example, a department may admit at most 120 students, or a course registration’s end date may need to be on or after its start date.

A CHECK constraint can enforce many conditions involving values within one row. More complex rules involving several records may require assertions where supported, triggers, or carefully designed transactional logic. These are ways to enforce business constraints, rather than additional mathematical properties of relations.

Integrity Constraints During Insert, Update, and Delete

Data modification can turn a valid database state into an invalid one. The DBMS checks applicable constraints and rejects a violating change or performs a declared referential action that preserves consistency.

Deleting a referenced row requires a defined policy. SQL provides referential actions for deletion and for updates to referenced key values:

For example, ON DELETE CASCADE on the student–department relationship would delete the students referencing a deleted department. If student records must be retained, that action would not match the application’s requirements.

These actions must be chosen deliberately. SET NULL cannot override a NOT NULL requirement, and SET DEFAULT cannot create a reference to a department that does not exist.

A Compact SQL Example

The following SQL expresses the main constraints for the same student and department design. Student IDs identify records, emails are required and unique, semester values are restricted, and department references must remain valid.

Here, department assignment is optional because Student.DeptID permits NULL. Once a department ID is supplied, it must reference an existing department. A referenced department cannot be deleted, while a change to its ID is propagated to the students referencing it.

The combination of NOT NULL and CHECK on Semester is intentional. A range check alone can allow NULL because the comparison evaluates to unknown. Similarly, UNIQUE does not universally imply NOT NULL; the handling of NULL under unique constraints varies between database systems.

Summary

The formal relational model represents data through relations, tuples, attributes, and domains. A schema defines the structure, while a relation state contains the current records. Keys identify tuples, and a valid database state satisfies all applicable integrity constraints.

Domain constraints restrict values, key constraints enforce uniqueness, entity integrity prevents missing primary-key components, and referential integrity preserves valid references. Together with business rules and suitable update policies, these constraints maintain consistency as database records change.