Database Normalization Exercise Guide UNEFA

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
consiste cada una de las reglas, vamos a considerar los datos de la siguiente tabla.

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 cannot 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 Pedro Maracay 3141 Fund 2 10,00

FIRST NORMAL FORM (1NF)

Upon examining these records, we can realize that they contain a repeated group for
ITEM_NUMBER, ITEM_NAME, QUANTITY 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 duplicated groups.
We need to create a new table with the PK of the base table and the repeated group.

The records are now structured into two tables that we will call ORDERS and
ORDER_ITEMS

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

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

Orders
Order_id Fecha Id_cliente Nom_cliente State
2301 23/02/11 101 Martin Caracas
2302 25/02/11 107 Herman Chorus
2303 February 27, 2011
110 Pedro Maracay

Order Articles
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 Foundation 2 10.00

Prof. Wilfredo Diaz Page 1/9

Database Normalization Exercise Guide UNEFA

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 column(s) of the PK from which they depend.

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

For its part, the ARTICULOS_ORDENES table is not in 2NF because 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.
Remove those columns from the base table.

Prof. Wilfredo Diaz Page 2/9

Database Normalization Exercise Guide UNEFA

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 ARTICLES 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
show 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)

2. INVOICE OF PURCHASE SALE: The company COLOMBIAN SYSTEMS has hired you as the
"Project Manager" for systematizing billing. In the following PURCHASE SALE INVOICE,
you must analyze all the available information and apply the normalization process until you reach the
Third Normal Form.
A detailed justification of each of the steps leading to the result is requested.
final.

Invoice(NUM_FAC, FECHA_FAC, NOM_CLIENTE, CLIENT_DIRECTORY

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

Prof. Wilfredo Diaz Page 3/9

Guía de Ejercicios de Normalización de Base de Datos UNEFA

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
CATEGORIA:Categoría del producto
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. GOODS SHIPPING COMPANY: below are grouped all the attributes that are part of
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 = Identification of Origin Company
* ORGN_NOM = Nombre de Empresa Origen
ORGN_ACT = Origin Company Commercial Activity
* ORGN_CIUDAD= Ciudad de Empresa Origen
* ORGN_DIR = Direccion de Empresa Origen
* ORGN_TEL = Telefono de Empresa Origen
* ORGN_CEL = Celular de Empresa Origen
* 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

Prof. Wilfredo Diaz Page 4/9

Database Normalization Exercise Guide UNEFA

4. Video club: In a video store, it is necessary to keep information about around 3000 booths, each one
The tapes are assigned a number for each movie, and it is necessary to know a title and category for it.
example: comedy, suspense, drama, action, science fiction, etc. Some copies of many are kept.
movies. Each movie is assigned an identification and tracking is kept on what it contains.
cassettes.
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 who belong 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 only keeps information about the actors who appear in the movies and that is 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
Of all the tapes that a customer rents, when a customer rents a tape, the name should be known.
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:

Alquiler(cod_alquiler, num_membresia, cod_cliente, nom_cliente, dir_cliente, telef_cliente, cod_cassette,

fecha_alquiler, fecha_dev, valor_alquiler, cantidad)

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

fechanac_actor, cod_tipo

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
category = movie category
cod_actor = código del actor
nom_actor = nombre del actor
fechanac_actor = fecha de nacimiento del actor
cod_tipo = código del tipo de película.

Prof. Wilfredo Diaz Page 5/9

Database Normalization Exercise Guide UNEFA

5. Given the following relationship 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 normalization rules and obtain its model
relational, indicate its keys, main attributes.

Subject/
School Professor Classroom Course Book Editorial Loan date
skill
Learn and
Thought teach in
C.P Cervantes John Pérez 1.A01 1st Grade Grazing 09/09/2010
Logical education
childish
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 A01 1st Grade Grao 05/05/2010
Numeric education
childish
Thought
Alicia Spatial, Education Prentice
C.P Cervantes 1.B01 1st Grade 06/05/2010
García Temporal and Infant N9 Hall
causal
Learn and
Alicia Thought teach in
C.P Cervantes 1.B01 1st Grade Grao 06/05/2010
García Numeric education
childish
Learn and
Andrés teach in
C.P Cervantes Writing 1.A01 2nd Grade Graó 09/09/2010
Fernández education
childish
to know
educate: guide
Andrés Themes 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
Juan Thought to teach in
C.P Quevedo 2.B01 1st Grade Grao 06/05/2010
Méndez Numeric education
childish

6. Given the relation EMPLOYEE_EXPENSES (employee_code, trip_code, destination, total_expense) in which we

the following functional dependencies are satisfied:
Cod_empleado Gasto_total
Travel code Destiny

It is requested: In what normal form is the relationship? Why?

Prof. Wilfredo Diaz Page 6/9

Database Normalization Exercise Guide UNEFA

7. There is a relation of the ENROLLMENT_REPORT (student_code, student_name, specialty,

course_code, course_name, instructor_name, office, section) it is requested to apply the normalization rules
reaching up to the 3FN.

Code/ Name/ Code/ Name/

Specialty Nombre_curso Office course
student student curso 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
Cesar
360247k Raúl Rojas Systems PA714 Investigation 1 SC-220 V
Fernadez
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

8. A database of a library is presented, apply the normalization rules simplifying until

third normal form

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

codLibro Title Author Editorial nombreLector Fechadev

1001 Complex variable 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 20/04/2005
1007 Clipper 5.01 Ramalho McGraw Hill Pérez Gómez, Juan 18/04/2005

9. USE OF UNIVERSITY RESOURCES: Given the following relationship USE

(DNI_Prof, Nombre_Prof, Despacho_Prof, Recurso, Ubicación, Fecha_inicio, Fecha_fin,) que contiene
information related to the use of university resources by professors during periods
of time:
Professional ID
Prof_Name Professor's Office Resource Description Start_date Fecha_fin
33456456 P. Martínez 2.2.B05 R122 Tablet PC 1-1-2007 15-1-2007
44556689 L. Moreno 2.2.A10 R100 Printer 1-1-2007 15-1-2007
33456456 P. Martínez 2.2.B05 R200 Laptop 12-1-2007 22-1-2007
11223344 E. Castro 2.2.B23 R122 Tablet PC 16-1-2007 31-1-2007
99771122 D. Cuadra 2.1.B23 R200 Laptop 23-1-2007 26-1-2007
33456456 P. Martínez 2.2.B05 R300 Scanner 1-1-2007 15-1-2007
99887766 F. Street 2.1.C11 R300 Scanner January 2, 2007 6-2-2007
33456456 P. Martínez 2.2.B05 R122 Tablet PC 1-2-2007 6-2-2007

It is requested, considering only the extent of the relationship shown in the table:
a. Indicate an example of modification anomaly
b. Indicate an example of a deletion anomaly

Prof. Wilfredo Diaz Page 7/9

Database Normalization Exercise Guide UNEFA

c. Provide an example of an insertion anomaly

d. Apply the corresponding normalization

[Link] SHIFTS: Given the following relationship ASSIGNMENT (DNI, Name, Store_Code,
Dirección_Tienda, Fecha, Turno) que contiene información relativa a la asignación de los turnos de trabajo
of the employees of the different centers of a fashion retail chain:

ID Name Store_Code Store Address Shift Date

33456456 P. Martin 100A Avd. Mayor, 23, Alcorcón M 2/9/06
44556689 L. Sanz 100A Avenida Mayor, 23, Alcorcón M 2/9/06
99771122 D. Díaz 100A Avd. Mayor, 23, Alcorcón T 2/9/06
33456456 P. Martín 200B C/ Sur 13, Alcorcón T 3/9/06
11223344 E. López 300C Plaza Central, Getafe M 3/9/06
99887766 F. Monte 200B C/ Sur 13, Alcorcón M 3/9/06
11223344 E. López 300C Plaza Central, Getafe M 4/9/06
99887766 F. Monte 100A Avd. Mayor, 23, Alcorcón M 4/9/06
44556689 L. Sanz 100A Avd. Mayor, 23, Alcorcón T 4/9/06
33456456 P. Martín 200B C/ Sur 13, Alcorcón M 5/9/06

It is requested, considering only the extent of the relationship shown in the table:
a. Indicate an example of a deletion anomaly
b. Indicate the functional dependencies using the following abbreviations: ID (P), Name
(N), Código_Tienda (C), Dirección_Tienda (D), Turno (T), Fecha (F).
c. What Normal Form is the relationship in? What are its keys?

11. SPORTS ACTIVITIES: Given the following relationship, IT IS CARRIED OUT (Activity_Code,
Nombre_Actividad, DNI_Monitor, Nombre_monitor, Sala, Fecha, Hora_I, Hora_F) utilizada para
store information about the date and duration of the sports activities that are organized in
a school is requested:

It is requested, considering that the names of the monitors are not unique and the names of the activities
neither and adhering to the tuples of the relationship IS CARRIED OUT:

Prof. Wilfredo Diaz Page 8/9

Database Normalization Exercise Guide UNEFA

a. Indicate an example of a deletion anomaly

b. Indicate the functional dependencies using the following abbreviations:
Cod_Actividad (A), Nombre_Actividad (N), ID_Monitor (D),
Nombre_Monitor(M), Sala (S), Fecha(F), Hora_I (I), Hora_F(E).
c. What are the keys to the relationship? In what FN is the SE relationship?
DO YOU DO? Why?

Prof. Wilfredo Diaz Page 9/9