Relational Integrity
Relational integrity refers to the set of principles and rules that ensure the accuracy and consistency of data in a relational database. It helps maintain data correctness, enforces relationships between tables, and prevents anomalies. Here’s a detailed overview of relational integrity:
1. *Types of Relational Integrity*
#### *1.1 Entity Integrity*
*Definition:* Ensures that each row (or tuple) in a table is uniquely identifiable and that there are no duplicate rows.
*Primary Key Constraint:* Enforces entity integrity by ensuring that the primary key of a table is unique and not null.
*Uniqueness:* Each primary key value must be unique.
*Non-nullability:* A primary key column cannot contain null values.
*Example:* In an `Employees` table, the `EmployeeID` column is a primary key that uniquely identifies each employee.
#### *1.2 Referential Integrity*
*Definition:* Ensures that relationships between tables are maintained correctly. It guarantees that a foreign key value in one table matches a primary key value in the related table or is null.
*Foreign Key Constraint:* Enforces referential integrity by linking foreign keys to primary keys in other tables.
*Matching Values:* The foreign key must either be null or match an existing primary key value in the referenced table.
*Cascading Actions:* Actions such as `ON DELETE CASCADE` or `ON UPDATE CASCADE` define what happens when a referenced record is deleted or updated.
*Example:* In an `Orders` table, the `CustomerID` foreign key must match an `CustomerID` in the `Customers` table.
#### *1.3 Domain Integrity*
*Definition:* Ensures that the data entered into a column adheres to the defined data type and constraints.
*Data Types and Constraints:* Ensures that values in a column conform to a specific type (e.g., integer, date) and adhere to constraints such as `CHECK` constraints.
*Data Type Constraints:* Enforces valid data types for each column.
*CHECK Constraints:* Ensure that values meet specified conditions (e.g., age must be greater than 0).
*Example:* A `Salary` column must contain values that are non-negative and of type `DECIMAL`.
#### *1.4 User-Defined Integrity*
*Definition:* Encompasses rules specific to a particular application or business logic that are not covered by entity, referential, or domain integrity.
*Custom Constraints:* Defined based on business rules or application needs.
*Application-Specific Rules:* May include constraints that are not standard SQL constraints but are necessary for the specific use case.
*Example:* An `Employees` table might have a rule that `HireDate` cannot be in the future, which might not be enforced by default constraints but is crucial for business logic.
2. *Implementing Relational Integrity*
#### *2.1 Primary Key Constraints*
*Purpose:* Enforce entity integrity by ensuring each record in a table is uniquely identifiable.
*Syntax Example:*
```sql
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
```
#### *2.2 Foreign Key Constraints*
*Purpose:* Enforce referential integrity by ensuring foreign key values correspond to primary key values in related tables.
*Syntax Example:*
```sql
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
```
#### *2.3 CHECK Constraints*
*Purpose:* Enforce domain integrity by ensuring data values meet specific criteria.
*Syntax Example:*
```sql
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Salary DECIMAL CHECK (Salary = 0)
);
```
#### *2.4 UNIQUE Constraints*
*Purpose:* Ensure that all values in a column (or a set of columns) are unique.
*Syntax Example:*
```sql
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Email VARCHAR(255) UNIQUE
);
```
3. *Cascading Actions*
*Definition:* Define how changes in the parent table (such as updates or deletions) affect related rows in child tables.
*Types:*
*ON DELETE CASCADE:* Automatically deletes rows in the child table when the corresponding row in the parent table is deleted.
*ON UPDATE CASCADE:* Automatically updates rows in the child table when the corresponding row in the parent table is updated.
*Syntax Example:*
```sql
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE
);
```
4. *Normalization and Data Integrity*
-
