Learning Activity 3

Normalization 1NF, 2NF, 3NF

Felipe Andrés Morales Arango.

May 2020.

UNIPANAMERICANA University Foundation.

Database Construction

Bogotá D.C.
 First Part

What is normalization?

Es el proceso de organizar los datos de una base de datos. Se debe tener en cuenta la creación

of tables and the rules that are used to define the relationships, these rules are designed to

protect the data, and to make the database flexible in order to eliminate redundancies

and incoherent dependencies.

For our database tables to be normalized, they must comply with the following rules:

• Each table must have its unique name.

• There cannot be two identical rows.

• Duplicates are not allowed.

• All the data in a column must be of the same type.

2. ¿Cuáles son los Tipos de Normalización y de un ejemplo de cada uno?

Rules or levels of normalization

To normalize a database, there are mainly 3 rules that should be followed.

to avoid redundancies and inconsistencies in the dependencies. These rules are known as

Normal form

in the 'first or second or third normal form'

Although other levels of normalization are possible, the third normal form is considered the

maximum level needed for most applications.

• Remove the duplicate groups from the individual tables.

• Create an independent table for each related dataset.

• Identify each set of data related to a primary key.

Example

For this example, there is a denormalized table that contains:

Students

Tutor

Room

Classes 1, 2, and 3

By applying the first normal form, we eliminate the repeated groups, leaving us with only one.

class column and repeating the student, tutor, and room data and now we don't have

repeated groups because we apply the first normal form (1NF).

Unnormalized table
First Normal Form: Remove repeating groups

Second normal form

• Create independent tables for sets of values that apply to multiple records.

• Relate these tables with a foreign key.

Example:

By moving to the second normal form, we will eliminate redundant data, and for this, we are going to

create two tables. One table will be called Students where we will eliminate redundant data

keeping only the unique data (Student, Tutor, and Room) and in a second table that

we will call Registration for the student number and the classes he will take in the example

student 1606 and 2602 will each take three classes. The content of the (1FN) First Form

A normal that was in one table has been divided into two tables to eliminate redundant data and

introduce it into the (2NF) Second Normal Form.

Students:

Record:

Records should not depend on anything other than the primary key of the table, including the key.

composed if necessary

Third Normal Form

• Remove the fields that do not depend on the key.

Example:

To move to the third normal form, fields that do not depend on the key must be eliminated.

and to achieve this, we divided the student table into two tables and created the Faculty table where

we will move the room column that does not depend on the key which is the student column,
The tutor's name will be the link to the student table, although it could also be the column.

student.

Third Normal Form: Remove non-dependent columns

Students:

Faculty:

Registration:

3. Define why the normalization of a database is so important?

Database normalization is a very important point that we should take into account.

serious to establish solid foundations on which we can build robust applications

that in the future they do not present difficult database problems to solve.

Considering that such normalization will benefit the following aspects:

Normalizing data ensures that information is organized and always kept in its place.

corresponding, without duplicates or outdated versions.

Data consistency

The reliability of the data is increased for all parties involved who access the databases, and there is

greater consistency in the stored information. It avoids storing outdated versions,

find duplicate data in different parts of the database taking into account the types of

relationships and paying attention to data without a clear hierarchy.

Solid data relationship

The main advantage of normalizing data, apart from eliminating redundancies, is the design of

data integrity that clearly shows how information from different tables is related

between each other. This facilitates the identification of data relationships and corrects any isolation or

inconsistency of information that may exist in the product database.

Connection to other systems

A database normalization process is essential to be able to implement any

data management software system. With a good database organization, install this system.

es más rápido y sencillo, y podrá conectarse fácilmente a las fuentes de datos externas o internas

without delays or the need to correct synchronization issues.

More security

Security is increased, as standardization ensures that data has a location.

more precise.
Cost savings

It is much easier to maintain the existing databases and make new improvements.

It is also faster to connect data sources to any internal or external system, as it does not

It will be necessary to carry out revisions to ensure that the data sent is correct.

What is a primary key?

It is a set of one or more attributes of a table, which taken collectively allow us to

to identify a record as unique, that is, in a table we can know which is a record in

specific only by knowing the primary key. In an entity-relationship architecture, the primary key

allows the relationships of the table that has the primary key, with other tables that will use it

information from this table.

Example:

Table: Invoices

Fields:

- ID_Factura (ID_Factura es la clave primaria, única e irrepetible para cada factura)

Sales branch (another field)

Sale date (another field)

It is called a foreign key, it is one or more fields of a table that reference the field or

primary key fields from another table, a foreign key indicates how the tables are related.

The data in the foreign key and primary key fields must match, even though the names of

the fields are not the same.

The foreign key is also determined at the time of the relationships between the tables in which

the primary key performs the join with the other field of another table and automatically becomes

a foreign key

Example:

Table: Invoice Details

Fields:

ID_InvoiceDetail (ID_InvoiceDetail is the primary key, unique and non-repeating for each

detail of each invoice

ID_Invoice (ID_Invoice is now our foreign key since it is the key by which we

they relate both tables

- Ítem (otro campo)

Quantity (another field)

Continuing with the project that provided a solution in activity 2 through the design of the diagram

entity relationship, at this stage of the course you must apply normalization up to the third normal form

normal and the construction of the definitive Relational Diagram.

1. Apply the first, second, and third normal forms (1NF, 2NF, 3NF) to the design.

First Normal Form

The first normal form is applied in the Customers and Employees tables for the Names columns.

and Last Names, based on what was explained in the readings; we can define for each of the

tables the following

Clients Table:

The columns Customer_Names and Customer_Surnames are assigned.

Tabla Empleados:

- Se asigna las columnas Nombres_empleado y Apellidos_empleado

In order to maintain data integrity and ensure that no issues arise during queries

repeated columns that hinder better performance of the database

2NF: Second Normal Form

The second normal form is applied by creating a new table (Area) to be able to have a

control of the possible areas of work where the employee has a relationship; this is removed from the table

Cargo
Charge Table

New Area Table

A new table (Tipo_identificacion) is created to establish related identification types.

to the tables Clients, Suppliers Employee

New Table Identification_Type

A new table (Tipos_comision) is created to establish a one-to-one relationship with the Employee table.

in order to establish that an employee can only have one type of commission
Se crea una nueva tabla (Estado) para referenciar los posibles estados en los cuales están los

suppliers, customers, employees, sales and orders

A new table (Tipos_credito) is created in order to establish a relationship between Branch and

Suppliers

A new table (Payment_Methods) is created in order to establish a relationship between Branch and

Client

A new table (Contacts) is created in order to manage the contacts associated with a supplier.

independently and thus visualize how many contacts there are per provider
 3NF: Third Normal Form

For the case study, considering the application of the previous standardizations, we do not

must apply a Third Normal Form.

2. Construction of the Definitive Relational Diagram

Relational Diagram - Previous

3. Construction of the database schema using a database management system.

Cod_area INTEGER NOT NULL AUTO_INCREMENT,

Areas_trabajo VARCHAR(50) NULL,

Cant_personal INTEGER UNSIGNED NULL,

PRIMARY KEY(Cod_area));

CREATE TABLE Cargo (

Cod_position INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Nombre_cargo VARCHAR(50) NULL,

Sueldo_base INTEGER NULL,

Commission BOOL NULL,

PRIMARY KEY(Cod_cargo));

CREATE TABLE Client (

Customer_code INTEGER UNSIGNED NOT NULL AUTO_INCREMENT

Tipo_identificacion_CodTipo_identificacion INTEGER UNSIGNED NOT NULL,

Nombres_cliente VARCHAR(50) NULL,

Customer_lastname VARCHAR(50) NULL,

No_identificación INTEGER NULL,

Phone INTEGER UNSIGNED NULL

Address VARCHAR(100) NULL,

Email VARCHAR(45) NULL,

PRIMARY KEY(Cod_cliente));
CREATE TABLE Contacts (

Cod_Contacts INTEGER UNSIGNED NOT NULL AUTO_INCREMENT

Nombres VARCHAR(50) NULL,

Surnames VARCHAR(50) NULL,

Telefono INTEGER UNSIGNED NULL,

PRIMARY KEY(Cod_Contactos));

CREATE TABLE Employee (

Employee_code INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Tipo_identificacion_CodTipo_identificacion INTEGER UNSIGNED NOT NULL,

Employee_names VARCHAR(50) NULL

Apellidos_empleado VARCHAR(50) NULL,

Identification_Number INTEGER NULL

Fecha_ingreso DATE NULL,

PRIMARY KEY(Cod_employee));

CREATE TABLE State (

State_code INTEGER UNSIGNED NOT NULL AUTO_INCREMENT

State VARCHAR(45) NULL

PRIMARY KEY(Cod_estado));
CREATE TABLE Payment_Method (

Payment_Method_Code INTEGER UNSIGNED NOT NULL AUTO_INCREMENT

Metodo_pago VARCHAR(45) NULL,

PRIMARY KEY(Cod_Metodo_pago),);

CREATE TABLE Order (

Order_number INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Fecha_pedido DATE NULL,

Fecha_entrega DATE NULL,

PRIMARY KEY(Order_number));

CREATE TABLE Product (

Cod_barras INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Nombre_producto VARCHAR(255) NULL,

Precio INTEGER NULL,

Fecha_vencimiento DATE NULL,

Stock INTEGER UNSIGNED NULL

PRIMARY KEY(Cod_barras),);

CREATE TABLE Provider (

Supplier_code INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Tipo_identificacion_CodTipo_identificacion INTEGER UNSIGNED NOT NULL,

Business_name VARCHAR(100) NULL

No_identificacion VARCHAR(20) NULL,

PRIMARY KEY(Cod_provider));

CREATE TABLE Branch_super (

Branch_code INTEGER UNSIGNED NOT NULL AUTO_INCREMENT

Nombre_sucursal VARCHAR(100) NULL,

Phone INTEGER NULL,

Address VARCHAR(100) NULL

PRIMARY KEY(Cod_sucursal));

CREATE TABLE Commission_Types (

Cod_Tipos_comision INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Tipos_comision VARCHAR(45) NULL,

Commission_Value INTEGER UNSIGNED NULL,

PRIMARY KEY(Cod_Tipos_comision),);

CREATE TABLE Credit_Types (

Cod_Tipos_credito INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Descripcion VARCHAR(45) NULL,

PRIMARY KEY(Cod_Tipos_credito));
CREATE TABLE Identification_Type (

CodTipo_identificacion INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

Tipo_identificacion VARCHAR(45) NULL,

PRIMARY KEY(CodTipo_identificacion));

CREATE TABLE Sale (

No_venta INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

PRIMARY KEY(No_sale));
 Reference List

[Link]

[Link]

[Link]