Skip to main content

Normalization in Action: Building Better Databases

What is Database Normalization?

Database normalization is an approach to evaluate database structure and minimize data redundancy based on candidate keys and functional dependencies. Candidate keys are unique identifiers that have all the necessary conditions to become a primary key. Functional dependencies refer to the relationship between attributes, where one attribute uniquely determines another.

Simply put normalization is a process of eliminating attributes which is not identified by candidate keys.

For example, consider a studentnumber that can uniquely identify a surname and firstname. In this case, the student number acts as a determinant, which determines the surname or firstname.

Student (studentnumber, surname, firstname)

The goal of normalization is to group attributes with a close logical relationship into the same table, so that it ensure there is no data redundancy.

Benefits:

  • Efficiently retrieve data: Well-structured data allows for faster and more accurate queries.
  • Avoid Complexity: Normalization simplifies the database structure by breaking it down into smaller, related tables.
  • Eliminate Duplicates: Redundant data can lead to anomalies (insertion, deletion, and update issues). Normalization helps minimize these.
  • Consistent Data: By organizing data, normalization ensures consistent values across related tables.

What Define Good and Bad Database Design?

Good:

  • Tables doesn't have data redundancy
  • Each determinant is a candidate key
  • Each determinant can uniquely identify other attributes

For example coursecode and offeringnumber can confidently identify other attributes CourseOffering (coursecode, offeringnumber, startdate, teachernumber)

Bad:

  • Relation/table has undesired data redundancy
  • A determinant is not a candidate key but can still identify another attribute in the table

For example teachernumber is not a candidate key but it can identify surname

CourseOffering (coursecode, offeringnumber, coursename, startdate, teachernumber, surname)

  • Partial dependency exists

(empno, clubno, clubname, joindate)

In this case empno and clubno are together a primary key and it can determine clubname, however clubno can also identify clubname, this leads to redundancy since clubname is dependent only on a part of the primary key.

Types of Normalization:

First Normal Form (1NF):

  • Each cell holds only one value (atomicity).
  • Primary key for identification.
  • No duplicated rows or columns.
  • Each column has a single value for each row.

Example: A table with employee details, where each employee has a unique ID and no duplicate rows.

Second Normal Form (2NF):

  • Builds on 1NF.
  • Ensures that non-key attributes depend entirely on the entire primary key (no partial dependencies).

Example: Separate tables for employee data and their projects, linked by employee ID.

Third Normal Form (3NF):

  • Builds on 2NF.
  • Eliminates transitive dependencies (attributes indirectly depend on other attributes).

Boyce-Codd Normal Form (BCNF) The redundancy prevention tool

  • Builds on 3NF.
  • Each determinant is a candidate key
  • All attribute values are atomic (single values)
  • If an attribute is not part of a candidate key, it must have a determinant














Labels:

Comments

Post a Comment

Popular posts from this blog

Declarative Programming in Angular with Async Pipe and shareReplay

A declarative approach is a way that focuses on writing code that specifies what the application should do, rather than detailing how it should be done. For example, with the async pipe in Angular, we don’t need to write code to manually subscribe to an Observable, handle its data, and update the view. Instead, we simply specify in the template that we want the data from the Observable using the async pipe. Angular handles all the underlying processes to retrieve and display the data It's often used in reactive programming with RxJS and Angular's built-in features, such as the async pipe. export class ProductComponent { product$ = this.productService.getProduct(); constructor(private productService: ProductService) {} } The product observable will hold the product data and the async pipe in the template will automatically subscribe and unsubscribe observable <div *ngIf="product$ | async as product"> <h1>{{ product.name }}</h1> <p>{{...
Post a Comment
Read more

The Developer’s Guide to Clean Code: Tips and Techniques

What is clean code? Clean code is a term used to describe code that is easy to read, understand, and maintain. It is written in a way that makes it simple, concise, and expressive. Clean code follows a set of conventions, standards, and practices that make it easy to read and follow. Here are some signs indicating that the code is not clean: 1. Poor names The name is not clear to understand, meaningless, or misleading . It doesn't reveal the intention of what it want to achieve. Consider the following examples: SqlDataReader drl; int od; void Button1_Click(); Class Pages1 In the examples above, it’s challenging to get the purpose of drl, od, or what Button1_Click() does. To enhance clarity, we can rename these identifiers as follows: SqlDataReader dataReader/reader; int overdueDays; void CheckAvailability_Click(); Class ViewCustomerPage {} Ambiguous names int? incidentNameId for instance. incidentNameId lacks clarity because if it represents the ID of an incident, then the inclu...
Post a Comment
Read more

Date and Time in .NET: DateTime, DateTimeOffset, TimeZoneInfo, DateOnly, TimeOnly, and TimeSpan

What is UTC UTC (Coordinated Universal Time) is the world’s primary time standard used to regulate clocks and time zones. It serves as the reference point for civil time worldwide, ensuring that all local times are defined by their offset from UTC. DateTime The DateTime class provides a way to work with dates and times without including an offset. This approach can reduce a certain level of accuracy when dealing with time zones. For example, calling DateTime.Now returns the current date and time based on your computer’s local time zone. DateTime.Now gives you the local time, while DateTime.UtcNow returns the universal coordinated time (UTC). You typically use DateTime when you only need to track the date and time itself, without worrying about time zones. This is suitable for scenarios such as birthdays, deadlines, or local schedules, especially when your application is used primarily within a single time zone. The DateTime class also includes a Kind property, which provides limited in...
Post a Comment
Read more