Learning Objectives

By the end of this lesson, learners should be able to:

• Explain the relational data model.
• Identify entities, attributes and relationships.
• Distinguish primary keys, foreign keys and candidate keys.
• Explain cardinality and relationship types.
• Apply normalization principles to relational database design.
• Identify common database design anomalies.
• Interpret entity-relationship diagrams.
• Evaluate database structures from a business analytics perspective.

1. Introduction to Relational Data Models

A relational data model organizes information into tables consisting of rows and columns.

The model is based on the principle that related business entities can be represented separately and connected through keys.

For example, a retail organization may have:

• Customers.
• Products.
• Orders.
• Payments.

Instead of putting all information into one large table, each major entity can be represented separately.

2. Entities

An entity represents a distinct object, concept or business subject about which an organization needs to maintain information.

Examples include:

• Customer.
• Employee.
• Product.
• Supplier.
• Invoice.
• Branch.

A business analyst should identify entities based on the business processes being represented rather than simply based on the columns appearing in an existing spreadsheet.

3. Attributes

An attribute describes a characteristic of an entity.

For a Customer entity, attributes could include:

• Customer_ID.
• Customer_Name.
• Date_of_Birth.
• Region.
• Customer_Type.

Attributes should have clear business meanings and appropriate data types.

4. Records

A record represents one occurrence of an entity.

For example:

Customer_ID	Customer_Name	Region
C001	Amina	Nairobi
C002	Brian	Kiambu

Each row represents a customer record.

5. Primary Keys

A primary key uniquely identifies each record in a table.

For example:

Customer_ID

may uniquely identify each customer.

A primary key should normally:

• Be unique.
• Not be null.
• Remain sufficiently stable.
• Identify one record unambiguously.

6. Candidate Keys

A candidate key is an attribute or combination of attributes capable of uniquely identifying a record.

A table may have several candidate keys, but one is selected as the primary key.

For example, an employee table might have:

• Employee_ID.
• National_ID.
• Corporate_Email.

Depending on organizational rules, more than one may potentially provide uniqueness.

7. Composite Keys

A composite key consists of two or more attributes used together to uniquely identify a record.

For example:

Order_ID + Product_ID

may uniquely identify a particular product line within an order.

Composite keys are particularly common in junction tables representing many-to-many relationships.

8. Foreign Keys

A foreign key creates a logical relationship between tables by referencing a key in another table.

Example:

Customers

Customer_ID

Orders

Order_ID
Customer_ID

The Customer_ID in Orders links each order to its customer.

9. Referential Integrity

Referential integrity helps ensure that relationships between tables remain valid.

For example, an order should not normally reference a Customer_ID that does not exist in the Customers table.

This protects the logical consistency of related data.

10. Relationship Types

One-to-One

One record in one table corresponds to one record in another.

One-to-Many

One record can be related to many records.

Example:

One customer → many orders.

Many-to-Many

Many records in one table can relate to many records in another.

Example:

Students ↔ Courses.

A junction table is commonly used to represent the relationship.

11. Cardinality

Cardinality describes how many records of one entity can be associated with records of another entity.

For example:

One customer can place many orders.

This is a one-to-many relationship.

Understanding cardinality is essential because incorrect assumptions about relationships can produce incorrect analytical results.

12. Optionality

Optionality describes whether a relationship is mandatory or optional.

For example:

A customer may exist before placing an order.

Therefore:

Customer → Order

may be optional from the customer’s perspective.

A valid database model should reflect actual business rules.

13. Entity-Relationship Diagrams

An Entity-Relationship Diagram (ERD) visually represents:

• Entities.
• Attributes.
• Relationships.
• Keys.
• Cardinality.

A simplified model might be:

CUSTOMER

Customer_ID
Customer_Name

↓

ORDER

Order_ID
Customer_ID
Order_Date

The relationship indicates that orders belong to customers.

14. Normalization

Normalization is a structured approach to designing relational databases to reduce unnecessary duplication and improve data integrity.

It generally involves organizing data into related tables according to defined dependencies.

15. First Normal Form

A table generally satisfies First Normal Form (1NF) when:

• Each field contains a single atomic value.
• Repeating groups are avoided.
• Records can be uniquely identified.

For example, storing:

Products = “Laptop, Mouse, Keyboard”

inside one field creates difficulties for relational analysis.

A more appropriate structure would represent products separately.

16. Second Normal Form

Second Normal Form (2NF) generally requires:

• Compliance with 1NF.
• Non-key attributes must depend on the whole primary key when a composite key is used.

This is particularly important for tables containing composite keys.

17. Third Normal Form

Third Normal Form (3NF) generally requires:

• Compliance with 2NF.
• Non-key attributes should not depend on other non-key attributes.

For example, if:

Customer_ID → Region_ID

and:

Region_ID → Region_Name

then storing Region_Name directly alongside Customer_ID may introduce a transitive dependency.

Separating region information can improve structural integrity.

18. Database Anomalies

Poor database design can produce anomalies.

Update Anomaly

The same information must be changed in multiple places.

Insertion Anomaly

A new fact cannot be recorded without introducing unrelated information.

Deletion Anomaly

Removing one record unintentionally removes other important information.

Normalization helps reduce these problems.

19. Denormalization

Denormalization deliberately introduces some redundancy to improve performance or simplify analytical querying.

For example, an analytical database may store frequently required descriptive information alongside transaction data.

Denormalization can improve query performance, but it introduces additional data maintenance considerations.

20. OLTP and OLAP Design

OLTP

Online Transaction Processing systems are optimized for frequent operational transactions.

Examples:

• Processing sales.
• Updating customer accounts.
• Recording payments.

OLAP

Online Analytical Processing environments are designed for complex analysis.

Examples:

• Revenue trends.
• Regional performance.
• Customer segmentation.
• Historical comparisons.

The optimal database design may therefore depend on whether the system is intended primarily for transactions or analysis.

21. Fact and Dimension Concepts

Analytical databases often distinguish between:

Fact Tables

Contain measurable business events.

Examples:

• Sales amount.
• Quantity sold.
• Discount.

Dimension Tables

Contain descriptive information.

Examples:

• Customer.
• Product.
• Region.
• Date.

This approach is common in dimensional modeling.

22. Star Schema

A star schema typically contains a central fact table connected to multiple dimension tables.

For example:

Sales Fact

connected to:

• Date Dimension.
• Product Dimension.
• Customer Dimension.
• Store Dimension.

This structure can simplify analytical queries.

23. Snowflake Schema

A snowflake schema extends dimensional modeling by normalizing some dimension structures.

For example:

Product → Category → Department

may be separated into multiple related tables.

The choice between star and snowflake designs depends on analytical requirements, performance and governance considerations.

24. Data Dictionary

A data dictionary documents information about database fields.

It may include:

• Field name.
• Description.
• Data type.
• Allowed values.
• Business definition.
• Source system.
• Ownership.

A strong data dictionary reduces ambiguity between technical and business users.

25. Business Rules

Database design should reflect actual business rules.

For example:

A customer may have multiple orders.

is a business rule.

Another rule might be:

Every order must belong to exactly one customer.

These rules influence relationships, constraints and analytical interpretation.

26. Database Design and Analytics

Poor database design can create analytical problems such as:

• Duplicate counts.
• Missing relationships.
• Incorrect aggregation.
• Inconsistent customer totals.
• Double-counted revenue.

Therefore, a business analyst should understand the database structure before interpreting SQL results.

27. Example

Suppose an analyst joins:

Customers

with:

Orders

and then calculates the number of customers.

If one customer has ten orders, the join may produce ten rows for that customer.

If the analyst simply counts rows, the customer may be counted ten times.

Understanding relationships and cardinality prevents this mistake.

Lesson Summary

Relational database design is based on:

• Entities.
• Attributes.
• Keys.
• Relationships.
• Cardinality.
• Referential integrity.
• Normalization.

For business analytics, understanding the underlying data model is critical because database structure directly affects how queries and analytical results should be interpreted.