Scenario: A bus company operates buses on various routes according to a schedule.

Step 1: Conceptual Data Model

Identify entities and their relationships:

• Entities: Route, Bus Company, Schedule
• Relationships:
  • One route can have one or many bus companies (1:M)
  • One route can have one or multiple schedules (1:M)

Step 2: Logical Data Model

Identify attributes and keys:

Route entity:

• route_number (Primary Key)
• route_name
• origin
• destination

Bus Company entity:

• company_id (Primary Key)
• route_number (Foreign Key)
• owner_name
• post_code

Schedule entity:

• schedule_number (Primary Key)
• route_number (Foreign Key)
• departure_time
• arrival_time

Step 3: Physical Data Model

Define tables with column names and data types:

Table: route

Table: bus_company

Table: schedule

ER modeling uses three main elements to represent database structure:

• Entities: Real-world objects stored in the database (represented as rectangles)
• Attributes: Properties of entities (represented as ovals)
• Relationships: How entities interact with each other (represented as diamonds)

Cardinality Types

• One-to-One (1:1): One entity associates with at most one entity in another set
• One-to-Many (1:M): One entity associates with multiple entities in another set
• Many-to-One (M:1): Multiple entities associate with single entity
• Many-to-Many (M:N): Multiple entities associate with multiple entities

Normalization organizes data into tables to minimize redundancy and prevent anomalies. The main normal forms are:

First Normal Form (1NF)

A table is in 1NF when it contains only atomic (single) values—no multi-valued attributes.

Example: Converting to 1NF

Given this unnormalized table:

The emp_mobile column contains multiple values, violating 1NF. After normalization:

Second Normal Form (2NF)

A table is in 2NF when it is in 1NF and all non-key attributes are fully functionally dependent on the entire primary key—no partial dependencies.

Example: Converting to 2NF

Given this table:

The primary key is {Teacher_id, Subject}. However, Teacher_age depends only on Teacher_id (a subset of the key), creating partial dependency. Decompose into:

Teacher table:

Teacher_Subject table:

Third Normal Form (3NF)

A table is in 3NF when it is in 2NF and no transitive functional dependencies exist—non-prime attributes depend only on the primary key.

Example: Converting to 3NF

Given this table with transitive dependency:

Here, emp_reg, emp_city, and emp_district depend on emp_pc, which depends on emp_id—creating transitive dependency. Decompose into:

Employee table:

Employee_address table:

• Primary Key (PK): Unique identifier for each row; cannot be null
• Candidate Key: Minimal set of attributes that uniquely identify a row
• Alternate Key: Candidate keys not selected as primary key
• Foreign Key (FK): Attribute linking to another table's primary key

In Tanzania, database design principles are applied when developing student information systems for schools. For example, when creating a school management database, you would design a conceptual model identifying entities like Students, Subjects, and Results, then create a logical model with proper relationships (a student studies multiple subjects), and finally implement the physical model with tables, data types, and indexes in MySQL or Access. This ensures data is stored efficiently without redundancy, allowing quick retrieval of student marks, generation of reports, and prevention of data inconsistencies—such as a student appearing with different names in different records.