Integrity Constraints

Definition

An Integrity Constraint is a rule or condition imposed on a database to ensure the accuracy, consistency, and validity of data stored in the database. Integrity constraints prevent invalid data from being entered into the database and maintain the correctness of data throughout all operations (insertion, deletion, and update).

Three Main Categories of Integrity Constraints

1. Domain Integrity Constraints

• These constraints specify that the value of each attribute must be an atomic value from its domain (data type).
• They restrict the type, format, and range of values that can be stored in a column.
• Example: An attribute Age must contain only positive integer values; a Gender attribute may only accept 'M' or 'F'.

2. Entity Integrity Constraints

• This constraint states that the primary key of a relation must be unique and not null.
• Every tuple (row) in a relation must be uniquely identifiable.
• No attribute that is part of the primary key can have a NULL value.
• Example: In a Student table, the StudentID (primary key) must have a unique, non-null value for every student record.

3. Referential Integrity Constraints

• This constraint is defined between two relations and is used to maintain consistency among tuples.
• It states that a foreign key in one relation must either match a primary key value in the referenced relation, or be NULL.
• It ensures that references between tables remain valid.
• Example: In an Enrollment table, the StudentID (foreign key) must correspond to an existing StudentID in the Student table.

Summary Table

Mapping 1:N and N:M Relationships in ER-to-Relational Mapping

1. Mapping 1:N (One-to-Many) Relationship

Rule / Approach

In a 1:N relationship, the entity on the N-side (many-side) gets the primary key of the 1-side entity added as a foreign key in its relation. No new relation is created.

Steps

1. Create relations for both participating entity types.
2. Identify the entity on the many side.
3. Add the primary key of the one-side entity as a foreign key into the many-side relation.
4. Also include any attributes of the relationship in the many-side relation.

Example

ER Diagram: A DEPARTMENT employs many EMPLOYEES (1:N)

DEPARTMENT (1) ----manages---- EMPLOYEE (N)

Resulting Relations:

DEPARTMENT (Dept_No, Dept_Name, Location)
              PK

EMPLOYEE (Emp_ID, Emp_Name, Salary, Dept_No)
           PK                        FK → DEPARTMENT

The primary key Dept_No from DEPARTMENT is placed as a foreign key in the EMPLOYEE relation.

2. Mapping N:M (Many-to-Many) Relationship

Rule / Approach

In an N:M relationship, a new relation (cross/junction table) is created to represent the relationship. This new relation contains:

• The primary key of the first entity (as FK)
• The primary key of the second entity (as FK)
• Any attributes of the relationship itself
• The combination of both foreign keys forms the primary key of the new relation.

Steps

1. Create relations for both participating entity types.
2. Create a new relation for the relationship.
3. Include the primary keys of both entities as foreign keys in the new relation.
4. The composite primary key = PK of entity 1 + PK of entity 2.
5. Add any relationship attributes to the new relation.

Example

ER Diagram: A STUDENT enrolls in many COURSES, and a COURSE has many STUDENTS (N:M), with an attribute Grade.

STUDENT (N) ----enrolls---- COURSE (M)
                  |
                Grade (relationship attribute)

Resulting Relations:

STUDENT (Student_ID, Student_Name, Address)
          PK

COURSE (Course_ID, Course_Name, Credits)
         PK

ENROLLMENT (Student_ID, Course_ID, Grade)
             FK→STUDENT  FK→COURSE
             |________________________|
                  Composite PK

A new relation ENROLLMENT is created with Student_ID and Course_ID as a composite primary key, and Grade as a relationship attribute.

Summary Table