Information SEcurity > Multilevel Security (MLS) > Architectures of Database Integrity
Architectures of Database Physical and Logical Integrity
Database integrity is the fundamental requirement that data remains accurate and consistent throughout its entire lifecycle. To achieve this, the architecture of a database system is divided into two primary layers: Physical Integrity and Logical Integrity.
Physical Integrity: Safeguarding the Storage Layer
Physical Integrity focuses on the mechanical and system-level accuracy of data storage and retrieval. It ensures that data remains intact even when subjected to hardware malfunctions, power failures, natural disasters, or data corruption.
Enforcement Mechanisms:
- Redundancy & Hardware: RAID (Redundant Array of Independent Disks), ECC memory (Error-correcting codes), and UPS (Uninterruptible Power Supply).
- Software & Logging: Backup and recovery systems, transaction logs, and journaling to reconstruct data after a failure.Logical Integrity: Enforcing Rules and RelationshipsWhile physical integrity protects the "bits and bytes," Logical Integrity ensures that the data is accurate, meaningful, and consistent with the specific rules defined by the database schema and business logic. These prevent inconsistencies, invalid entries, or violations of defined data rules.
The Four Pillars of Logical Integrity:
- Entity Integrity: Ensures every table has a primary key and that no part of that key is NULL, guaranteeing each record is uniquely identifiable.
- Referential Integrity: Ensures that foreign keys correctly reference primary keys in related tables, maintaining the "links" between data.
- Domain Integrity: Ensures that data values fall within acceptable ranges or formats (e.g., a date field must contain a valid date).
- User-defined Constraints: Custom rules tailored to specific business needs, such as ensuring a "salary" column remains greater than zero.
Implementing and Enforcing Logical Integrity
Logical integrity is enforced through a combination of database features and application-level logic.
- SQL Constraints: Common tools include NOT NULL, UNIQUE, CHECK, and FOREIGN KEY.
- Programmable Logic: Use of Triggers, Stored Procedures, and specific business rules to handle complex data validations.
- Application-level Validation: Checks performed within the software interface before the data even reaches the database.
- Practical Example: A system must prevent a st
- dent’s enrollment record from referencing a course ID that does not exist in the "Course" table; this is a classic application of referential integrity
Differences between Physical and Logical Integrity
|
Feature
|
Physical Integrity
|
Logical Integrity
|
|
Focus
|
Storage reliability and error recovery
|
Data validity and logical consistency
|
|
Concerned with
|
Hardware/software failures
|
Rule violations, relational model consistency
|
|
Protection Methods
|
Backup, journaling, RAID, fault tolerance
|
Constraints, triggers, schema design
|
|
Example Threat
|
Disk failure corrupts data
|
Entering a negative value for age
|
|
Layer
|
System/Hardware level
|
Database schema and application level
|