Designing a database is a fundamental skill for cyber security professionals tasked with safeguarding sensitive information in Kenyan institutions. Whether for a county government office managing citizen records or a financial institution protecting client data, a well-designed database ensures data integrity, confidentiality, and availability. This chapter focuses on designing relational databases, a widely adopted model that supports structured data storage and complex queries, and on creating entity relationship diagrams (ERDs) to visualize database structure effectively.

2.1 Design a relational database

2.1.1 Principles of Relational Database Design

Relational database design is governed by principles that ensure data is logically organized and redundancies minimized. The core principle is that data is stored in tables called relations, each representing an entity such as customers or transactions. Each table has rows (records) and columns (attributes), and relationships between tables are established through keys.

Key principles include:

• Atomicity of Data: Attributes should be indivisible units ensuring each cell contains only one value to simplify querying and updates.
• Uniqueness of Rows: Each row must be uniquely identifiable, typically through a primary key, which prevents duplication and maintains data integrity.
• Avoidance of Redundancy: Repeating data across tables leads to inconsistencies; normalization techniques reduce redundancy by splitting data into related tables.
• Referential Integrity: Foreign keys maintain consistency between related tables by ensuring referenced data exists.
• Data Independence: The design should allow changes in the database schema without affecting application programs, supporting long-term adaptability.

2.1.2 Normalization and Its Role in Security

Normalization is a systematic approach to decomposing tables to eliminate data anomalies and redundancies. For cyber security professionals, normalization reduces the attack surface by limiting repeated sensitive data and simplifying access controls.

Stages of normalization include:

• First Normal Form (1NF): Ensures each table cell holds atomic values and each record is unique.
• Second Normal Form (2NF): Removes partial dependencies by ensuring all non-key attributes depend on the entire primary key.
• Third Normal Form (3NF): Eliminates transitive dependencies so that non-key attributes depend only on the primary key.
• Boyce-Codd Normal Form (BCNF): Handles exceptions to 3NF for stricter dependency rules.

Normalization supports security by making it easier to implement row-level permissions and reduces the risk of unauthorized data duplication or exposure.

2.1.3 Designing Tables and Defining Keys

Effective table design involves specifying the right attributes and keys to support data relationships and security requirements. In Kenya’s banking sector, for example, customer tables might include sensitive information requiring encryption and access restrictions.

Important considerations:

• Primary Key Selection: Choose attributes that uniquely identify each record, such as account numbers or employee IDs, to support efficient indexing and retrieval.
• Foreign Keys: Define relationships between tables, such as linking customer IDs in transaction tables to customer records, enforcing referential integrity.
• Composite Keys: Sometimes, a combination of attributes forms a unique key, important in scenarios like order details where order ID and product ID together identify a record.
• Indexing: Create indexes on frequently queried columns to improve performance but balance this with security as indexes can reveal data patterns.
• Constraints: Use constraints like NOT NULL and UNIQUE to enforce data validity and prevent insertion of invalid or duplicate data.

2.1.4 Security Considerations in Database Design

Designing a relational database with security in mind is critical in Kenya’s cyber security landscape. Protecting data from unauthorized access and ensuring accountability requires embedding security controls at the design stage.

Key security design aspects:

• Access Control: Define user roles and permissions at the table and column level to restrict data access based on job function.
• Data Encryption: Plan for encryption of sensitive fields such as personal identification numbers and financial details to protect data at rest.
• Audit Trails: Design tables to log access and changes, supporting forensic investigations in case of data breaches.
• Input Validation: Structure the database to support checks that prevent SQL injection and other input-based attacks.
• Backup and Recovery: Ensure the design supports regular backups and recovery mechanisms to maintain data availability and integrity.

Chapter Summary

This chapter focused on designing a relational database, emphasizing the importance of structuring data efficiently to support secure and reliable storage and retrieval. It introduced the concept of entity relationships, explaining their role in representing real-world objects and the associations between them within the database context. The process of creating entity relationship diagrams (ERDs) was detailed, highlighting how these visual tools aid in mapping out the database’s structure before implementation. The chapter also covered the critical phase of normalization, demonstrating how to systematically organize data to eliminate redundancy and ensure data integrity. Through normalization, database designers can optimize tables to reduce anomalies and enhance performance. Overall, the chapter provided a comprehensive guide to the foundational steps in building a secure and well-structured relational database.