Relational Database Design and Normalization Principles

Design of Relational Databases

The first step in creating a database is planning the type of information to be stored, taking into account two aspects: the information available and the information we need. Planning the structure of the database, including tables, is vital for its effective management.

Understanding Table Structure

The design of the structure of a table is a description of each of the fields that make up the registry and the values or data containing each of these fields. Fields are the different types of data that make up the table; for example: name, surname, and address. The definition of a field requires: the field name, field type, field width, etc. The records provide the information to be contained in the fields of the table, such as a patient's name and address.

First Normal Form (1NF)

The First Normal Form (or minimally 1NF) is a normal form used in database normalization. A table in a relational database that adheres to 1NF is one that satisfies a certain minimum set of criteria. These criteria apply primarily to ensure that the table is a faithful representation of a relationship and is free of "repeating groups."

Functional Dependency and Second Normal Form (2NF)

A functional dependency is a relationship between specific attributes of the same relation (table). To qualify for the Second Normal Form (2NF), a table that is already in First Normal Form (1NF) must meet additional criteria.

Third Normal Form (3NF)

A table is in 3NF if and only if the following conditions are maintained:

• The table is in Second Normal Form (2NF).
• No non-primary attribute of the table is transitively dependent on the primary key.

A non-primary attribute is an attribute that does not belong to any candidate key.

Boyce-Codd Normal Form (BCNF)

The Boyce-Codd Normal Form (BCNF) is a normal form used in the standardization of databases. It is a slightly stronger version of the Third Normal Form (3NF). The Boyce-Codd Normal Form requires that there are no non-trivial functional dependencies of attributes other than a set of candidate keys.

In a table in 3NF, all attributes are dependent on a key, the whole key, and nothing else except the key (excluding trivial dependencies). It is said that a table is in BCNF if and only if it is in 3NF and every non-trivial functional dependency has a candidate key as a determinant. In less formal terms, a table is in BCNF if it is in 3NF and the only determinants are candidate keys.