Understanding Cardinality and Normalization in Databases

In the world of databases, managing and organizing data efficiently is crucial. Two

fundamental concepts that play a significant role in this process are cardinality and

normalization. This blog will explore these concepts in detail, explaining their

importance, types, and how they contribute to effective database design.

Cardinality in Databases

What is Cardinality?

Cardinality refers to the uniqueness of data values contained in a column. In the

context of database relationships, it defines the numerical relationships between

rows of one table to rows in another. Cardinality is a critical concept because it

influences how tables are linked together and how data is retrieved.

Types of Cardinality

1. One-to-One (1:1) Relationship

2. One-to-Many (1:N) Relationship

3. Many-to-Many (M:N) Relationship

1. One-to-One (1:1) Relationship

In a one-to-one relationship, each row in Table A is linked to one and only one row in

Table B, and vice versa. This type of relationship is less common and is typically

used to split a table for security or organizational purposes.

Example:

• Table A: Users (UserID, Username)

• Table B: UserDetails (UserID, Address, PhoneNumber)

2. One-to-Many (1:N) Relationship

In a one-to-many relationship, a single row in Table A can be related to one or more

rows in Table B. This is the most common type of relationship and is used to link

tables where one entity can have multiple related entities.

Example:

• Table A: Authors (AuthorID, Name)

• Table B: Books (BookID, Title, AuthorID)

3. Many-to-Many (M:N) Relationship

In a many-to-many relationship, rows in Table A can have multiple matches in Table B

and vice versa. This type of relationship is typically managed using a junction table

to break it into two one-to-many relationships.

Example:

• Table A: Students (StudentID, Name)

• Table B: Courses (CourseID, CourseName)

• Junction Table: StudentCourses (StudentID, CourseID)

Normalization in Databases

What is Normalization?

Normalization is the process of organizing data in a database to reduce redundancy

and improve data integrity. The main goal is to divide large tables into smaller, more

manageable pieces while maintaining relationships between the data. Normalization

helps to eliminate redundant data, ensure data dependencies make sense, and

protect the data from anomalies.

Example of Normalization: Larger table divided into smaller tables to remove data

redundancy(duplication)

Student Database Table

Student Table

Course Table

Subject Table

Instructor Table

Normal Forms

Normalization is carried out through a series of steps called normal forms. Each

normal form has specific requirements:

1. First Normal Form (1NF)

2. Second Normal Form (2NF)

3. Third Normal Form (3NF)

4. Boyce-Codd Normal Form (BCNF)

5. Fourth Normal Form (4NF)

6. Fifth Normal Form (5NF)

1. First Normal Form (1NF)

A table is in 1NF if:

• All columns contain atomic (indivisible) values.

• Each column contains values of a single type.

• Each column has a unique name.

• The order in which data is stored does not matter.

Example:

Before 1NF:

After 1NF:

2. Second Normal Form (2NF)

A table is in 2NF if:

• It is in 1NF.

• All non-key attributes are fully functional dependent on the primary key.

Example:

Before 2NF:

After 2NF:

Students Table:

Courses Table:

Enrollments Table:

3. Third Normal Form (3NF)

A table is in 3NF if:

• It is in 2NF.

• All the attributes are functionally dependent only on the primary key.

Example:

Before 3NF:

After 3NF:

Employees Table:

Departments Table:

4. Boyce-Codd Normal Form (BCNF)

A table is in BCNF if:

• It is in 3NF.

• Every determinant is a candidate key.

5. Fourth Normal Form (4NF)

A table is in 4NF if:

• It is in BCNF.

• It has no multi-valued dependencies.

6. Fifth Normal Form (5NF)

A table is in 5NF if:

• It is in 4NF.

• It cannot be decomposed into any number of smaller tables without losing data integrity.

Real Case Example

Consider an e-commerce database:

Customers Table:

Orders Table:

Products Table:

OrderDetails Table:

Here, normalization ensures that the database is structured efficiently, reducing

redundancy and maintaining data integrity. Cardinality is reflected in the

relationships between customers, orders, and products, allowing complex queries

and data management.

Conclusion

Cardinality and normalization are fundamental concepts in database design that

ensure efficient data organization and retrieval. Cardinality defines the relationships

between tables, while normalization structures the data to eliminate redundancy and

maintain integrity. Understanding these concepts is crucial for designing robust and

efficient databases that can handle complex data relationships and queries

effectively.