Referential Integrity in DBMS

Integrity constraints are a set of rules used in Database Management Systems (DBMS)
to ensure that the data in a database is accurate, consistent and reliable. These rules
helps in maintaining the quality of data by ensuring that the processes like adding,
updating or deleting information do not harm the integrity of the database. In simple
terms, they act as guidelines to protect the database and keep it error-free.

Integrity constraints also define how different parts of the database are connected and
ensure that these relationships remain valid. They play an essential role in making sure
the data is meaningful and follows the logical structure of the database.

What are Integrity Constraints?

Integrity constraints in a Database Management System (DBMS) are rules that help keep
the data in a database accurate, consistent and reliable. They act like a set of guidelines
that ensure all the information stored in the database follows specific standards.

For example:

• Making sure every customer has a valid email address.

• Ensuring that an order in the database is always linked to an existing customer.

These rules prevent mistakes, such as adding incomplete or incorrect data, and make
sure the database is secure and well-organized. In simple terms, integrity constraints
keep the database neat, correct and trustworthy.

Types of Integrity Constraints

Domain Constraints

Domain constraints are a type of integrity constraint that ensure the values stored in a
column (or attribute) of a database are valid and within a specific range or domain. In
simple terms, they define what type of data is allowed in a column and restrict invalid
data entry. The data type of domain include string, char, time, integer, date, currency
etc. The value of the attribute must be available in comparable domains.

This table demonstrates domain constraints in action by enforcing rules for each
column:

1. Student_Id: Must be unique and follow a specific format like 21CSE###. No

duplicates or invalid formats allowed.

2. Name: Accepts only valid text (no numbers) and cannot be left empty (NOT
NULL constraint).

3. Semester: Allows specific values like 5th, 6th, etc., and ensures valid input (e.g.,
no 10th if not permitted).

4. Age: Must be an integer within a reasonable range (e.g., 18-30) and cannot
contain invalid data like negative numbers or text.

Types of Domain Constraints

• NOT NULL Constraint

• CHECK Constraint

Why Domain Constraints Are Important ?

• They prevent invalid or inconsistent data from entering the database.

 • They ensure the database is reliable and follows predefined business rules.

• They make the database easier to manage and maintain by reducing errors.

Example:

Let, the not-null constraint be specified on the "Semester" attribute in the relation/table
given below, then the data entry of 4th tuple will violate this integrity constraint,
because the "Semester" attribute in this tuple contains null value. To make this
database instance a legal instance, its entry must not be allowed by database
management system.

Entity Integrity Constraints

Entity integrity constraints state that primary key can never contain null value because
primary key is used to determine individual rows in a relation uniquely, if primary key
contains null value then we cannot identify those rows. A table can contain null value in
it except primary key field.

Key Features of Entity Integrity Constraints

Uniqueness

• The primary key value must be unique for each row in the table.

• No duplicate entries are allowed in the primary key column.

NOT NULL

• The primary key column cannot contain NULL values, as every row must have a
valid identifier.
Essential for Table Design

• Ensures that every record in the table can be uniquely identified, preventing
ambiguity.

Example:

It is not allowed because it is containing primary key (Student_id) as NULL value.

Key Constraints

Key constraints ensure that certain columns or combinations of columns in a table

uniquely identify each row. These rules are essential for maintaining data integrity and
preventing duplicate or ambiguous records.

Why Key Constraints Are Important ?

• Prevent Duplicates: Ensure unique identification of rows.

• Maintain Relationships: Enable proper linking between tables (via foreign keys).

• Enforce Data Integrity: Prevent invalid or inconsistent data.

Primary Key Constraints

It states that the primary key attributes are required to be unique and not null. That is,
primary key attributes of a relation must not have null values and primary key attributes
of two tuples must never be same. This constraint is specified on database schema to
the primary key attributes to ensure that no two tuples are same.

Example

Here, in the below example the Student_id is the primary key attribute. The data entry of
4th tuple violates the primary key constraint that is specifies on the database schema
and therefore this instance of database is not a legal instance.

Unique Values: Each student_id must be unique.

• 101, 102, 103 are valid.

• Inserting 101 again would result in an error.

Not NULL: student_id cannot be NULL.

• Valid: 103.

• Invalid: A row with NULL for student_id will be rejected.

Unique Key Constraints

A Unique Key ensures that the values in a column are unique, but unlike a primary key, it
allows one NULL value.
Unique Values: The email column must contain unique values.

• aniket@example.com and shashwat@example.com are valid.

• Adding another row with aniket@example.com would result in an error.

Allows One NULL: The email column can contain one NULL value.

• Valid: NULL in the second row.

• Invalid: Adding another row with NULL in email will be rejected.

Referential integrity constraints

Referential integrity constraints are rules that ensure relationships between tables
remain consistent. They enforce that a foreign key in one table must either match a
value in the referenced primary key of another table or be NULL. This guarantees the
logical connection between related tables in a relational database.

Why Referential Integrity Constraints Are Important ?

• Maintains Consistency: Ensures relationships between tables are valid.

• Prevents Orphan Records: Avoids cases where a record in a child table

references a non-existent parent record.

• Enforces Logical Relationships: Strengthens the logical structure of a relational

database.

Example:

Here, in below example Block_No 22 entry is not allowed because it is not present in
2nd table.
Assertion

An assertion is a declarative mechanism in a database that ensures a specific condition

or rule is always satisfied across the entire database. It is a global integrity constraint,
meaning it applies to multiple tables or the entire database rather than being limited to
a single table or column.

Purpose

This assertion enforces a global business rule:

• The sum of all loan amounts for a branch must always be less than the sum of all
account balances in the same branch. This prevents branches from issuing loans
beyond their financial capacity.

How It Works

• Subqueries:

o The first subquery (SELECT SUM(amount) FROM loan) calculates the total
loan amount for each branch.

o The second subquery (SELECT SUM(amount) FROM account) calculates

the total balance of accounts for the same branch.

• Condition:

o The NOT EXISTS clause ensures there is no branch where the loan amount
is greater than or equal to the account balance.

• Behavior:

o If a transaction (e.g., inserting a loan or updating an account) violates this

rule, the operation will be rejected by the database.
Triggers

A trigger is a procedural statement in a database that is automatically executed in

response to certain events such as INSERT, UPDATE, or DELETE. Triggers are often used
to enforce complex integrity constraints or implement business rules that cannot be
captured using standard constraints like primary keys or foreign keys.

Explanation:

• Trigger Type: The trigger runs after an update on the account table.

• Condition: It checks if the account balance is negative after the update.

• Actions:

1. If the balance is negative, the account balance is reset to zero.

2. A loan is created with the same account number as the loan number, and
the loan amount is the absolute value of the negative balance.
The ABS() function ensures the loan amount is positive.
What is Functional Dependency in DBMS?

Functional dependency in DBMS is an important concept that describes the

relationship between attributes (columns) in a table. It shows that the value of one
attribute determines the other. In this article, we will learn about functional
dependencies and their types. Functional dependencies help maintain the quality of
data in the database.

Suppose we have a student table with attributes: Stu_Id, Stu_Name, Stu_Age. Here
Stu_Id attribute uniquely identifies the Stu_Name attribute of student table because if
we know the student id we can tell the student name associated with it. This is known
as functional dependency and can be written as Stu_Id→Stu_Name or in words we can
say Stu_Name is functionally dependent on Stu_Id.

Formally: If column A of a table uniquely identifies the column B of same table then it
can represented as A->B (Attribute B is functionally dependent on attribute A).

A B
1 3
2 3
4 0
1 3
4 0
How to represent functional dependency in DBMS?

Functional dependency is expressed in the form of equations. For example, if we have

an employee record with fields "EmployeeID", "FirstName" and "LastName" we can
specify the function as follows:

EmployeeID -> FirstName, LastName

To represent functional dependency in DBMS has two main features: left (LHS) and right
(RHS) of the arrow (->).

For example, if we have a table with attributes "X", "Y" and "Z" and the attribute "X" can
determine the value of the attributes "Y" and "Z".

X -> Y, Z

This symbol indicates that the value in property "X" determines the values in property
"Y" and "Z". So if you know the value of "X", you can also determine the value of "Y" and
"Z".

Types of Functional Dependency in DBMS

The following are some important types of FDs in DBMS:

Trivial Functional Dependency

The dependency of an attribute on a set of attributes is known as trivial functional

dependency if the set of attributes includes that attribute.

Non-trivial Functional Dependency

If a functional dependency X→Y holds true where Y is not a subset of X then this
dependency is called non trivial Functional dependency.

Multivalued Dependency

A multivalued dependency happens when there are at least three attributes (let us say
X, Y and Z), and for a value of X there is a well defined set of values of Y and a well
defined set of values of Z. However, the set of values of Y is independent of set Z and
vice versa.

Semi Non Trivial Functional Dependencies

X -> Y is called semi non-trivial when X intersect Y is not NULL.

Transitive Functional Dependency

Transitive functional dependency in DBMS is the relationship between attributes

(columns) of a database table. This occurs when the price of one property determines
the price of another property through an intermediate (third) factor.
Please refer types of functional dependencies for more details.

Armstrong’s Axioms in Functional Dependency

Reflexivity: If A is a set of attributes and B is a part of A, then the function A -> B is valid.

Augmentation: If the A -> B dependency is valid, adding multiple elements to either

side of the dependency will not affect the dependency.

Transitivity: If the functions X → Y and Y → Z are both valid, then X → Z is also valid
according to the transitivity rule.

Read more about Armstrong’s Axioms in Functional Dependency in DBMS.

Benefits of Functional Dependency in DBMS

Functional dependency in a database management system offers several advantages

for businesses and organizations:

• Prevents Duplicate Data:

Functional dependency helps avoid storing the same data repeatedly in the
database, reducing redundancy and saving storage space.

• Improves Data Quality and Accuracy:

By organizing data efficiently and minimizing duplication, functional dependency
ensures the data is reliable, consistent, and of high quality.

• Reduces Errors:
Keeping data organized and concise lowers the chances of errors in records or
datasets, making it easier to manage and update information.

• Saves Time and Costs:

Properly organized data allows for quicker and easier access, improving
productivity and reducing the time and cost of managing information.

• Defines Rules and Behaviors:

Functional dependency allows setting rules and constraints that control how
data is stored, accessed, and maintained, ensuring better data management.

• Helps Identify Poor Database Design:

It highlights issues like scattered or missing data across tables, helping identify
and fix design flaws to maintain consistency and integrity.