What is Coded Rules in DBMS ? DBMS Notes

1. Introduction

A Database Management System (DBMS) is designed to store, manage, and organize data in an efficient manner. One of its most important responsibilities is enforcing coded rules that help maintain data accuracy, consistency, and security while also enabling automation.

These rules are implemented using constraints, triggers, stored procedures, user-defined functions, and views.

Coded rules help to:

• Ensure data accuracy by preventing invalid data entries.
• Maintain data consistency by enforcing relationships between tables.
• Enable automation to reduce manual database operations.
• Improve security by restricting unauthorized data modifications.

---

2. Types of Coded Rules in DBMS

In a DBMS, coded rules are implemented through different mechanisms, each serving a specific purpose.

A. Integrity Constraints

Integrity constraints are rules applied to table columns to ensure valid and reliable data. They restrict the type of data that can be stored in a database.

1. Primary Key Constraint

• Uniquely identifies each record in a table.
• Does not allow duplicate or NULL values.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

2. Foreign Key Constraint

• Maintains referential integrity between related tables.
• Ensures values in one table exist in another referenced table.

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    InstructorID INT,
    FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);

3. Unique Constraint

• Ensures that all values in a column are unique.

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

4. Check Constraint

• Restricts values based on a specific condition.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18)
);

5. NOT NULL Constraint

• Ensures that a column always contains a value.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL
);

---

B. Triggers

A trigger is a special SQL program that executes automatically when a specific database event such as INSERT, UPDATE, or DELETE occurs.

1. Types of Triggers

• BEFORE Trigger – Executes before the operation.
• AFTER Trigger – Executes after the operation.
• INSTEAD OF Trigger – Used mainly with views.

2. Example: Preventing Low Salary Insertion

CREATE TRIGGER check_salary
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    IF NEW.Salary < 30000 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary must be at least 30,000';
    END IF;
END;

3. Example: Automatically Updating Timestamp

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON Orders
FOR EACH ROW
BEGIN
    SET NEW.LastModified = NOW();
END;

---

C. Stored Procedures

A stored procedure is a collection of SQL statements stored in the database and executed as a single unit.

Advantages of Stored Procedures

• Improves performance by minimizing network traffic.
• Ensures consistent execution of operations.
• Enhances security by controlling direct table access.

Example: Updating Employee Salary

CREATE PROCEDURE UpdateSalary(IN empID INT, IN newSalary DECIMAL(10,2))
BEGIN
    UPDATE Employees 
    SET Salary = newSalary 
    WHERE EmployeeID = empID;
END;

Example: Fetching Customer Details

CREATE PROCEDURE GetCustomerDetails(IN custID INT)
BEGIN
    SELECT * FROM Customers WHERE CustomerID = custID;
END;

---

D. User-Defined Functions (UDFs)

A User-Defined Function (UDF) is a custom function created to perform calculations or return a specific value.

Example: Bonus Calculation

CREATE FUNCTION CalculateBonus(salary DECIMAL(10,2)) 
RETURNS DECIMAL(10,2)
RETURN salary * 0.10;

Example: Age Validation

CREATE FUNCTION IsAdult(age INT) 
RETURNS BOOLEAN
RETURN age >= 18;

---

E. Views with Rules

A view is a virtual table that displays selected data from one or more tables.

Example: High Salary Employees View

CREATE VIEW HighSalaryEmployees AS
SELECT * FROM Employees WHERE Salary > 50000;

Example: Read-Only Customer View

CREATE VIEW CustomerView AS
SELECT CustomerID, Name, Email FROM Customers;

---

3. Importance of Coded Rules in DBMS

Aspect	Benefit
Data Integrity	Prevents invalid and inconsistent data.
Automation	Reduces manual work using triggers.
Security	Protects data through controlled access.
Performance	Optimizes execution using stored procedures.
Consistency	Ensures uniform database operations.

---

4. Conclusion

Coded rules play a vital role in a DBMS by ensuring data integrity, consistency, security, and automation. Using constraints, triggers, stored procedures, functions, and views allows organizations to effectively enforce business rules and maintain high-quality data.

Would you like additional examples, diagrams, or exam-oriented notes on this topic? 🚀