Normalisation and Binary Relationships

By /

What is Normalisation ?

Normalization of data in a database is a process designed to ensure data integrity by eliminating duplication. This involves dividing the database into multiple tables and creating linkages between them. There are various levels of normalization, including First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF).

First Normal Form (1 NF)

In 1NF, each column in a table contains only atomic values, meaning the values cannot be further divided. There should be no repeating groups or arrays of values within a single column. Each row in the table must be uniquely identifiable. Here’s an example:

Second Normal Form (2 NF)

The table must already be in First Normal Form (1NF). Additionally, each non-key column must depend on the entire primary key, not just a part of it. If there are partial dependencies (where a non-key column depends on only a part of the primary key), those columns should be moved to a separate table. Here’s an example:

Third Normal Form (3 NF)

In 3NF, a table must already be in 2NF, ensuring there are no partial dependencies on a composite primary key. Additionally, there should be no transitive dependencies, meaning non-key columns should not depend on other non-key columns. If such dependencies exist, those columns should be moved to a separate table. Here’s an example:

Boyce-Codd Normal Form (BCNF)

BCNF (Boyce-Codd Normal Form) is an advanced form of normalization that addresses certain anomalies that can still occur in 3NF. It ensures that there are no non-trivial functional dependencies where a non-key attribute determines a candidate key. Achieving BCNF often involves further decomposing tables to eliminate these dependencies, ensuring a more robust database design.

Binary Relationships

A binary relationship exists when two different entities are involved. In such a relationship, every entity in one set is uniquely associated with an entity in the other set. For instance, a passport can only be issued to one individual, and a person is only allowed to have one passport at a time. An illustration of a one-to-one relationship might be this:

Cardinality

Cardinality refers to the number of instances of one entity that can be associated with an instance of another entity in a relational database. Understanding cardinality is crucial for designing accurate and efficient database schemas. There are four main types of cardinality:

  1. One-to-One (1:1)
    • Definition: Each instance of Entity A is associated with exactly one instance of Entity B, and each instance of Entity B is associated with exactly one instance of Entity A.
    • Example: Each person has one unique passport, and each passport is issued to one person.
    • Illustration:
Person
--------
PersonID | Name
1        | John Doe
2        | Jane Smith

Passport
----------
PassportID | PersonID | IssueDate
A1234567   | 1        | 2022-01-01
B7654321   | 2        | 2022-02-15
  1. One-to-Many (1:M)
    • Definition: Each instance of Entity A can be associated with multiple instances of Entity B, but each instance of Entity B is associated with only one instance of Entity A.
    • Example: A teacher can teach multiple classes, but each class is taught by only one teacher.
    • Illustration:

Teacher
--------
TeacherID | Name
1         | Mr. Smith
2         | Ms. Johnson

Class
--------
ClassID | TeacherID | Subject
101     | 1         | Math
102     | 1         | Science
201     | 2         | English
  1. Many-to-One (M:1)
    • Definition: Multiple instances of Entity A can be associated with one instance of Entity B, but each instance of Entity B can be associated with multiple instances of Entity A.
    • Example: Many students enroll in one specific course, but each course can have many students.
    • Illustration:
Student
--------
StudentID | Name
1         | Alice
2         | Bob
3         | Charlie

Course
--------
CourseID | CourseName
101      | Mathematics
102      | Literature
  1. Many-to-Many (M:M)
    • Definition: Each instance of Entity A can be associated with multiple instances of Entity B, and each instance of Entity B can be associated with multiple instances of Entity A. This often requires a junction table to manage the relationships.
    • Example: Students can enroll in multiple courses, and each course can have multiple students enrolled.
    • Illustration: 
Student
--------
StudentID | Name
1         | Alice
2         | Bob

Course
--------
CourseID | CourseName
101      | Mathematics
102      | Literature

Enrollment
-----------
StudentID | CourseID
1         | 101
1         | 102
2         | 101

In summary, understanding and correctly implementing cardinality in database design ensures that relationships between entities are accurately represented, leading to efficient data retrieval and integrity.

Related Posts

Scroll to Top