What is Coded Rules in DBMS ? DBMS NotesBCA

1. Introduction

A Database Management System (DBMS) is responsible for managing and organizing data efficiently. One of its key roles is enforcing rules to maintain data integrity, consistency, and automation. These rules can be implemented through constraints, triggers, stored procedures, functions, and views.

Coded rules ensure:

Data accuracy (preventing invalid entries).
Data consistency (enforcing relationships).
Automation (reducing manual interventions).
Security (preventing unauthorized modifications).

2. Types of Coded Rules in DBMS

Coded rules in DBMS are implemented using various mechanisms:

A. Integrity Constraints

Constraints are rules applied to table columns to enforce data integrity. They prevent invalid data from being entered into the database.

1. Primary Key Constraint

Ensures that each record in a table has a unique identifier.

Prevents duplicate or NULL values.

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

2. Foreign Key Constraint

Enforces referential integrity by ensuring that a value in one table corresponds to a value in another.

Prevents deletion of referenced data.

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 values in a column remain unique.

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

4. Check Constraint

Ensures that values in a column meet a specific condition.

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

5. Not Null Constraint

Ensures that a column cannot have NULL values.

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

B. Triggers

A trigger is a piece of SQL code that is automatically executed when a specific event (INSERT, UPDATE, DELETE) occurs in a table.

1. Types of Triggers

Before Trigger: Executes before the operation.
After Trigger: Executes after the operation.
Instead Of Trigger: Used for views to override default behavior.

2. Example: Preventing Low Salaries

The following trigger prevents inserting a salary below 30,000.

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: Auto-updating Last Modified Date

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 precompiled set of SQL statements that can be executed as a single unit.

1. Advantages of Stored Procedures

Improves performance by reducing network traffic.
Ensures consistency in operations.
Enhances security by restricting direct table access.

2. 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;

3. Example: Getting 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 by users to perform calculations or return a value.

1. Example: Calculating Bonus

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

2. Example: Checking Employee Age

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

E. Views with Rules

A view is a virtual table that provides a customized representation of data.

1. Example: Creating a View for High Salary Employees

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

2. Example: Read-Only Customer Details

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

3. Importance of Coded Rules in DBMS

Feature	Benefit
Data Integrity	Prevents invalid or inconsistent data.
Automation	Reduces manual interventions using triggers.
Security	Restricts unauthorized access using constraints.
Performance	Optimizes queries and execution through stored procedures.
Consistency	Ensures uniformity in data operations.

4. Conclusion

Coded rules in DBMS help maintain data integrity, consistency, and security. By using constraints, triggers, stored procedures, functions, and views, organizations can enforce business rules effectively.

Would you like me to add more examples or a specific topic in detail? 🚀

What is Coded Rules in DBMS ? DBMS Notes