week 8

8.8 Database Integrity Constraints

Beyond entity and referential integrity, DBMS supports additional integrity mechanisms:

1.    CHECK Constraints — Enforce domain-specific rules: CHECK (Age >= 18 AND Age <= 120); CHECK (Salary > 0);

2.    UNIQUE Constraints — Ensure no duplicates: BVN VARCHAR(11) UNIQUE;

3.    NOT NULL Constraints — Prevent null values in critical fields

4.    Triggers — Automatic actions that execute when specified events occur (INSERT, UPDATE, DELETE)

 

TRIGGER EXAMPLE — Audit log for salary changes:

CREATE TRIGGER salary_audit BEFORE UPDATE ON STAFF

FOR EACH ROW

INSERT INTO AUDIT_LOG (TableName, FieldChanged, OldValue, NewValue, ChangedBy, ChangeDate)

VALUES ('STAFF', 'Salary', OLD.Salary, NEW.Salary, USER(), NOW());

 

Reading List / References

1.    Elmasri, R. & Navathe, S. B. (2016). Fundamentals of Database Systems, Chapter 24: Database Security and Authorisation. Pearson.

2.    NITDA (2019). Nigeria Data Protection Regulation (NDPR). www.nitda.gov.ng

3.    OWASP (2023). SQL Injection. owasp.org/www-community/attacks/SQL_Injection

 

Activities

Self-Assessment Quiz: 1. What is the difference between authentication and authorisation? 2. Write SQL commands to: (a) create a user 'report_user', (b) grant SELECT on all tables in 'hr_db' to that user, (c) revoke that privilege. 3. Explain how a SQL injection attack works. Give a specific example. 4. How do database views help with security? Give an example from a Nigerian bank context.