Database Normalization Exercise Guide

Exercise Guide
Apply the normalization rules to the following exercises.

1. A non-normalized data does not comply with any normalization rule. To explain with an example in what
each of the rules consists of, we will consider the data from the following table.

ordenes(id_orden, fecha, id_cliente, nom_cliente, estado, num_art, nom_art, cant, precio)

Orders
Id_orden Date Id_cliente Nom_cliente Estado Num_art nom_art can't Price
2301 23/02/11 101 Martin Caracas 3786 Red 3 35.00
2301 23/02/11 101 Martin Caracas 4011 Racket 6 65.00
2301 23/02/11 101 Martin Caracas 9132 Paq-3 8 4.75
2302 25/02/11 107 Herman Chorus 5794 Paq-6 4 5.00
2303 27/02/11 110 Peter Maracay 4011 Racket 2 65.00
2303 27/02/11 110 Peter Maracay 3141 Fund 2 10.00

FIRST NORMAL FORM (1NF)

Examining these records, we can realize that they contain a repeated group for
NUM_ART, NOM_ART, CANT and PRICE. The 1NF prohibits repeated groups, therefore
we have to convert to the first normal form. The steps to follow are:
We need to eliminate the duplicate groups.
We need to create a new table with the PK of the base table and the repeated group.

The records are now conformed into two tables that we will call ORDERS and
ORDER_ARTICLES

ordenes(id_orden, fecha, id_cliente, nom_cliente, estado)

Articulos_ordenes(id_orden, num_art, nom_art, cant, precio)

Orders
Id_orden Date Id_cliente Nom_cliente State
2301 23/02/11 101 Martin Caracas
2302 25/02/11 107 Herman Chorus
2303 27/02/11 110 Peter Maracay

Order_Items
Id_orden Num_art nom_art can't Price
2301 3786 Red 3 35,00
2301 4011 Racket 6 65.00
2301 9132 Paq-3 8 4.75
2302 5794 Paq-6 4 5,00
2303 4011 Racket 2 65.00
2303 3141 Fund 2 10.00

1/9
Database Normalization Exercises Guide

SECOND NORMAL FORM (2NF)

Now we will proceed to apply the second normal form, that is, we have to eliminate any
non-key column that does not depend on the primary key of the table. The steps to follow are:
Determine which non-key columns do not depend on the primary key of the table.
Remove those columns from the base table.
Create a second table with those columns and the PK column(s) from which they depend.

The ORDERS table is in 2NF. Any unique value of ORDER_ID determines only one value.
for each column. Therefore, all the columns are dependent on the primary key
ID_ORDEN.

For its part, the TABLE ARTICLES_ORDERS is not in 2NF since the columns
PRICE and NOM_ART are dependent on NUM_ART, but they are not dependent on
ORDER_ID. What we will do next is remove these columns from the table.
ARTICLES_ORDERS and create a TABLE ARTICLES with those columns and the primary key
on which they depend.

The tables are now as follows.

Articulos_ordenes(id_orden, num_art, cant)

Order Articles
Id_orden Num_art can't
2301 3786 3
2301 4011 6
2301 9132 8
2302 5794 4
2303 4011 2
2303 3141 2

Articulos( num_art, nom_art, precio)

Articles
Num_art nom_art Price
3786 Red 35.00
4011 Racket 65,00
9132 Paq-3 4.75
5794 Paq-6 5,00
3141 Fund 10.00

THIRD NORMAL FORM (3NF)

The third normal form tells us that we must eliminate any non-key column that is
dependent on another non-key column. The steps to follow are:
Determine the columns that are dependent on another non-key column.
Delete those columns from the base table.

2/9
Database Normalization Exercise Guide

Create a second table with those columns and with the non-key column of which they are.
dependents.

Upon observing the tables we have created, we realize that both the ARTICULOS table and
The ARTICULOS_ORDENES table is in 3NF. However, the ORDENES table is not.
is, since NOM_CLIENTE and ESTADO are dependent on ID_CLIENTE, and this column is not
the primary key.

To normalize this table, we will move the non-key columns and the key column from which
dependent within a new CLIENTS table. The new CLIENTS and ORDERS tables are
shown below.

ordenes(id_orden, fecha, id_cliente)

Orders
Id_orden Date Id_cliente
2301 23/02/11 101
2302 25/02/11 107
2303 27/02/11 110

Clientes(id_cliente, nom_cliente, estado)

Orders
Id_cliente Nom_cliente State
101 Martin Caracas
107 Herman Chorus
110 Peter Maracay

Therefore, the database is as follows:

ordenes(id_orden, fecha, id_cliente)

Clientes(id_cliente, nom_cliente, estado)
Articulos( num_art, nom_art, precio)
Articulos_ordenes(id_orden, num_art, cant)

[Link] SALE INVOICE: The company COLOMBIAN SYSTEMS has contracted you as the
"In Charge Engineer" to systematize billing. In the following SALES PURCHASE INVOICE,
you must analyze all available information and apply the normalization process until reaching the
Third Normal Form.
A detailed justification of each of the steps that lead to the result is requested.
final.

Factura(NUM_FAC, FECHA_FAC, NOM_CLIENTE, CLIENT_DIRECTORY

CUSTOMER_REF,
CIUDAD_CLIENTE, TELEF_CLIENTE, CATEGORIA, COD_PROD, DESP_PROD, VAL_UNIT,
CANT_PROD

3/9
Database Normalization Exercise Guide

Where:

NUM_FAC:Número de la factura de compra venta

FECHA_FAC:Fecha de la factura de compra venta
NOM_CLIENTE:Nombre del cliente
DIR_CLIENTE:Dirección del cliente
RIF_CLIENTE:Rif del cliente
CIUDAD_CLIENTE:Ciudad del cliente
TELEF_CLIENTE:Teléfono del cliente
CATEGORY:Product category
COD_PROD:Código del producto
DESCRIPCION:Descripción del producto
VAL_UNIT:Valor unitario del producto
CANT_PROD:Cantidad de productos q compra el cliente
The primary key is Sales Invoice Number: NUM_FAC

3. SHIPPING COMPANY: below are grouped all the attributes that are part
from the database to apply normalization rules. Where the names of the
atributos con su significado
* GUIA_NO = Numero de Guia
* GUIA_FECHA= Fecha de la Guia
* GUIA_HORA= Hora de la Guia
* ORGN_RIF = Identificacion de Empresa Origen
* ORGN_NOM = Nombre de Empresa Origen
ORGN_ACT = Commercial Activity of Origin Company
* ORGN_CIUDAD= Ciudad de Empresa Origen
* ORGN_DIR = Direccion de Empresa Origen
* ORGN_TEL = Telefono de Empresa Origen
ORGN_CEL = Origin Company Cellular
* DEST_ID = Identificacion del destinatario
* DEST_NOM = Nombre del destinatario
* DEST_COD_CIUDAD = Codigo de la ciudad del destinatario
* DEST_CIUDAD= Ciudad del destinatario
* DEST_DIR = Direccion del destinatario
* DEST_TEL = Telefono del destinatario
* DEST_KM = Distancia kilometraje de Ciudad origen a ciudad del destinatario
* CODIGO = Codigo del paquete
* TIPO = Tipo de paquete
* NOMBRE = Nombre del paquete
* DESCRIPCION = Descripción del paquete
* VALR_ FLETE = Valor del flete

4. Video club: In a video store, it is necessary to maintain information about around 3000 cassettes, each one
The tapes are assigned a number for each movie, and it is necessary to know a title and category for
example: comedy, suspense, drama, action, science fiction, etc. Some copies of many are kept
movies. Each movie is given an identification and tracking of what it contains is maintained.
cassettes.

4/9
Database Normalization Exercise Guide

A cassette can come in various formats and a movie is recorded on a single cassette; frequently the
Movies are requested according to a specific actor Tom Cruise and Demi Moore are the most popular.
For this reason, information about the actors belonging to each movie must be maintained.
Not all movies feature famous actors, store customers like to know facts such as the
nombre real del actor, y su fecha de nacimiento.
The store keeps information only about the actors who appear in the movies and who are available.
disposition. Videos are only rented to those who belong to the video club. To belong to the club, you
must have good credit. For each club member, a record is kept with their name, phone number, and
address, each club member is assigned a membership number. It is desired to maintain information
de todos los casetes que un cliente alquila, cuando un cliente alquila un casete se debería conocer el nombre
from the movie, the date it is rented and the return date.

It is requested to apply the normalization rules up to the third normal form, having the following entities.
with their respective attributes:

Rental(rental_code, membership_number, client_code, client_name, client_address, client_phone, cassette_code,

fecha_alquiler, fecha_dev, valor_alquiler, cantidad)

Cassettte(cod_cassette, num_copias, formato, cod_pelicula, titulo, categoría, cod_actor, nom_actor,

birth_date_actor, type_code

Where:

cod_alquiler = Codigo del alquiler

num_membresia = Numero de membresia
cod_cliente = código del cliente
nom_cliente = nombre del cliente
dir_cliente = dirección del cliente
telef_cliente = teléfono del cliente
cod_cassette = código del cassette
fecha_alquiler = fecha del alquiler del al película
fecha_dev = fecha de devolución de la pelicula
valor_alquiler = valor del alquiler de la película
cantidad = cantidad de película alquilada
num_copias = números de copias de cassette
format = cassette format
titulo = nombre de la película
categoría = categoría de la película
cod_actor = código del actor
nom_actor = nombre del actor
fechanac_actor = fecha de nacimiento del actor
cod_tipo = code of the type of movie.

5. Given the following relation LOAN_BOOKS (School, teacher, subject/skill, classroom, course,
book, publisher, loan_date) that contains information related to the loans made by the
editorials to primary school teachers for their evaluation in some of the
Subjects/skills they teach. It is requested to apply the rules of normalization and obtain its model.
relational, indicate its keys, main attributes.

5/9
Database Normalization Exercise Guide

Subject/
School Professor Classroom Course Book Editorial Loan date
skill
Learn and
Thought to teach in
C.P Cervantes Juan Pérez 1.A01 1st Grade Grao 09/09/2010
Logical education
infantile
Preschool Techniques
C.P Cervantes Juan Pérez Writing 1.A01 1st Grade 05/05/2010
Rubio,N56 Rubio
Learn and
Thought Teach in
C.P Cervantes Juan Pérez 1.A01 1st Grade Grao 05/05/2010
Numeric education
infantile
Thought
Alicia Spatial, Education Apprentice
C.P Cervantes 1.B01 1st Grade 06/05/2010
García Temporal and Infantil N9 Hall
causal
Learn and
Alicia Thinking teach in
C.P Cervantes 1.B01 1st Grade Grau 06/05/2010
García Numeric education
infantile
Learn and
Andrés to teach in
C.P Cervantes Writing 1.A01 2nd Grade Graó 09/09/2010
Fernandez education
infantile
to know
educate: guide
Andrés Topics of
C.P Cervantes English 1.A01 2nd Grade For Parents 05/05/2010
Fernández Today
y
Teachers
to know
educate: guide
Juan Thought Themes of
C.P Quevedo 2.B01 1st Grade for Parents 18/12/2010
Méndez Logical Today
y
Teachers
Learn and
John Thought to teach in
C.P Quevedo 2.B01 1st Grade Graó 06/05/2010
Mendez Numeric education
childish

6. There is a report of ENROLLMENT_REPORT (student_code, student_name, specialty,

código_curso, nombre_curso, nombre_docente, oficina, sección) se pide aplicar las reglas de normalización
reaching up to the 3FN.

Code/ Name/ Code/ Name/

Specialty Nombre_curso Office course
student student Course teacher
Luis Carlos
382145A Industrial MA123 Mathematics 2 CB-214 U
Zuloaga Arambulo
Luis
382145A Industrial QU514 Physical Chemistry Petra Rondinel CB-110 U
Zuloaga
Luis Victor
382145A Industrial AU521 Descriptive CB-120 W
Zuloaga Moncada
360247k Raúl Rojas Systems PA714 Research 1 Caesar SC-220 V

6/9
Database Normalization Exercise Guide

Fernandez
Carlos
360247k Raúl Rojas Systems MA123 Mathematics 2 CB-214 V
Arambulo
Victor
360247k Raúl Rojas Systems AU511 Drawing CB-120 U
Moncada

7. A database of a library is presented, apply the normalization rules simplifying to the

third normal form.

Prestamos_libro (codLibro, Titulo, Autor, Editorial, NombreLector, Fechadev)

codLibro Title Author Editorial nombreLector Fechadev

Complex variable 1001 Murray Spiegel McGraw Hill Pérez Gómez, Juan 15/04/2005
1004 Visual Basic 5 E. Petroustsos Anaya Ríos Terán, Ana 17/04/2005
1005 Statistics Murray Spiegel McGraw Hill Rock, René 16/04/2005
1006 Oracle University Nancy Greenberg and Priya Nathan Oracle Corp. García Roque, Luis April 20, 2005
1007 Clipper 5.01 Ramalho McGraw Hill Pérez Gómez, Juan 18/04/2005

7/9