Data Modelling
1. Introduction to Data Modelling
What is Data Modelling?
Data modelling is the process of defining how data is structured, stored, and related within a
database. It helps in designing a database that ensures data integrity, reduces redundancy, and
facilitates efficient data retrieval.
Importance of Data Modelling
Helps in database design and organization.
Ensures consistency and accuracy of stored data.
Improves data retrieval efficiency.
Reduces redundancy and prevents anomalies.
2. Four Steps of Data Modelling
Step 1: Defining Entity Classes and Primary Keys
What is an Entity Class?
An entity class is a set of objects (people, places, things) about which data needs to be stored.
Each entity class has unique characteristics that distinguish it from others.
Primary Key
A primary key is a field (or combination of fields) that uniquely identifies each record in an
entity. It ensures no duplicate records exist.
Example
Entity Class Primary Key
Employee Employee ID
Job Job Number
Department Department Num
Step 2: Defining Relationships Among Entities
Understanding Relationships
Relationships describe how entities interact with each other. They are categorized based
on cardinality:
1. One-to-One (1:1) – Each record in entity A is related to only one record in entity B.
2. One-to-Many (1:M) – One record in entity A can be associated with multiple records
in entity B.
3. Many-to-Many (M:M)Multiple records in entity A relate to multiple records in entity
B (requires an intersection table).
Entity-Relationship (E-R) Diagram Symbols
Rectangle → Represents an entity class.
Line → Represents a relationship.
Crow’s Foot Notation → Shows the cardinality:
o | (single relationship)
o O (zero or optional relationship)
o Three-pronged foot (crow's foot) (multiple relationships)
Example of Employee-Department Relationship
Rule 1: An Employee must be assigned to at least one Department.
Rule 2: A Department may have many Employees or none at all.
Step 3: Defining Information (Fields) for Each Entity
This step ensures:
The right attributes are stored in the correct entity.
No unnecessary redundancy exists.
Fields should depend only on the primary key of their respective tables.
Example of Correct Field Assignment
Entity Class Correct Fields Incorrect Fields
Employee ID, Name, Salary, Job Department Name (should be in Department
Employee
ID entity)
Department Num, Department
Department Number of Employees (can be derived)
Name
Step 4: Creating the Database
Once entities, attributes, and relationships are defined, the database is created using a Data
Definition Language (DDL).
Steps include:
1. Translating the logical design into SQL tables.
2. Implementing primary and foreign keys.
3. Creating relationships using constraints.
3. The Process of Normalization
Normalization is used to eliminate redundancy and ensure a well-structured relational
database.
Normalization Rules
1. Eliminate many-to-many relationships (use an intersection relation).
2. Ensure attributes depend only on the primary key.
3. Remove derived fields (fields that can be computed).
Example of an Intersection Table for Many-to-Many Relationship
Employee ID Job Number
101 J001
102 J002
Here, "Job Assignment" is an intersection table that resolves the many-to-many
relationship between Employees and Jobs.
4. Key Database Components
Foreign Key
A foreign key is a field in one table that refers to the primary key in another table,
establishing a relationship.
Composite Primary Key
A composite key consists of two or more fields used together as a primary key (e.g.,
"Job Assignment" table using Employee ID and Job Number).
5. Conclusion
Data modelling is critical for designing databases that are structured, efficient, and scalable.
By following best practices like entity identification, relationship mapping, and
normalization, data consistency is maintained, and redundancy is reduced.
6. Visual Representation
Basic E-R Diagram Example
[ Employee ] 1 -- (works in) -- M [ Department ]
[ Employee ] M -- (performs) -- M [ Job ] (resolved using Job Assignment
table)
Corrected Table Structure
Employee Table
Employee ID (PK)
Name
Job ID (FK)
Job Assignment Table (Intersection Table)
Employee ID (PK, FK)
Job Number (PK, FK)
Department Table
Department Num (PK)
Department Name
