← CSC 213 Database Systems
Relational Data Model and Integrity Constraints
Notes
Introduction to Database Systems
A database management system or DBMS is specialized system software designed to store, manage, and retrieve structured data reliably. In enterprise computing, databases guarantee persistence, concurrency control, security, and atomicity, resolving the fundamental pitfalls associated with traditional file processing systems, such as data redundancy, inconsistency, and unauthorized access.
The Relational Data Model
Introduced by Edgar F. Codd, the relational model represents all data logically in the form of two-dimensional tables known as relations. Each relation consists of rows called tuples and columns termed attributes. The domain specifies the permissible set of atomic values for each attribute. Relational schemas define the formal structure and metadata, establishing entity names, data types, and inter-relational constraints.
Relational Integrity Constraints
Integrity constraints are declarative assertions enforcing business rules and structural correctness across state changes:
1. Domain Integrity: Every column value must belong to its declared data type and defined range.
2. Entity Integrity: Every relation must possess a primary key that uniquely identifies each tuple, and no component attribute of this primary key can evaluate to null.
3. Referential Integrity: Foreign keys define links between tables; any foreign key value must either precisely match an existing candidate key value in the referenced parent relation or be completely null.
Practical example
Consider Safaricom building an M-Pesa agent transaction ledger in Nairobi. We define two relations: Agent and Transaction.
Agent relation:
AgentID (Primary Key), AgentName, Location, NationalID
Sample tuple: (AG045, Jamia Communications, CBD Nairobi, 24890123)
Transaction relation:
TransactionID (Primary Key), TransactionTimestamp, Amount, AgentID (Foreign Key referencing Agent.AgentID)
Sample tuple: (TX99102, 2024-03-12 10:45:00, 5000.00, AG045)
Under domain integrity, Amount rejects negative or textual inputs. Under entity integrity, TransactionID cannot be blank or duplicate. Under referential integrity, an agent cannot process transaction TX99102 referencing AgentID AG999 if AG999 does not exist in the registered Agent table. This architecture guarantees that every shilling transacted in the system traces back unambiguously to a legally verified agent operating within Kenya.
Quiz
1. What happens if a user attempts to record an M-Pesa transaction with an AgentID not registered in the Agent table?
2. Which integrity rule dictates that primary key attributes cannot contain null values?
3. In the relational model, what is the formal mathematical term used to denote a table row?
Sign in to submit and securely grade your quiz.