DATABASE I Database Normalization Exercise Guide

Apply the normalization rules to the following exercises.

[Link] ORDERS: A non-normalized data does not comply with any rule of
normalization. To explain with an example what each of the
rules, we will consider the data from the following table.

ordenes(id_orden, fecha, id_cliente, nom_cliente, estado, num_art, nom_art,

can't, price

Orders
Id_orden Fecha Id_cliente Nom_cliente Estado Num_art nom_art cant Precio
2301 23/02/15 101 Martin Caracas 3786 Red 3 35.00
2301 23/02/15 101 Martin Caracas 4011 Racket 6 65,00
2301 23/02/15 101 Martin Caracas 9132 Paq-3 8 4.75
2302 25/02/15 107 Herman Choir 5794 Paq-6 4 5.00
2303 27/02/15 110 Pedro Maracay 4011 Racket 2 65,00
2303 27/02/15 110 Peter Maracay 3141 Foundation 2 10.00

FIRST NORMAL FORM (1NF)

Upon examining these records, we can realize that they contain a
repeated group for NUM_ART, NOM_ART, CANT, and PRECIO. The 1NF prohibits the
repeated groups, so we have to convert to the first form
normal.

The steps to follow are:

We need to eliminate the duplicate groups.
We have to create a new table with the PK of the base table and the
repeated group.

The records are now organized into two tables that we will call
ORDERS and ARTICLES_ORDERS

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

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

Orders
Id_orden Fecha Id_cliente Nom_cliente Estado
2301 23/02/15 101 Martin Caracas
2302 25/02/15 107 Herman Chorus
2303 27/02/15 110 Pedro Maracay

Order Articles
Id_orden Num_art nom_art cant 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
DATABASE I Database Normalization Exercise 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 key
primary of the table.

The steps to follow are:

Determine which non-key columns do not depend on the key.
primary of the table.
Delete those columns from the base table.
Create a second table with those columns and the column(s) of the
PK they depend on.

The ORDERS table is in 2NF. Any unique value of ID_ORDER

determine a single value for each column. Therefore, all the
Columns are dependent on the primary key ID_ORDER.

For its part, the ARTICULOS_ORDENES table is not in 2NF since

the columns PRICE and NOM_ART are dependent on NUM_ART, but they are not
dependents of ID_ORDER. What we will do next is eliminate
these columns from the table ARTICULOS_ORDENES and create a table ARTICULOS
with those columns and the primary key they depend on.

The tables are now as follows.

Order_Items(order_id, item_num, quantity)

Order Articles
Id_orden Num_art cant
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 Precio
3786 Red 35.00
4011 Racket 65.00
9132 Paq-3 4.75
5794 Paq-6 5.00
3141 Fund 10.00

2
DATABASE I Database Normalization Exercise Guide

Third Normal Form (3NF)

The third normal form tells us that we have to eliminate any

a non-key column that is dependent on another non-key column.

The steps to follow are:

Determine the columns that are dependent on another column not
key.
Remove those columns from the base table.
Create a second table with those columns and with the non-key column.
they are dependent on.

Upon observing the tables we have created, we realize that both the
Table ARTICLES, like the table ARTICLES_ORDERS, is in 3NF.
However, the ORDERS table is not, as CLIENT_NAME and STATUS are
dependent on CLIENT_ID, and this column is not the primary key.

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

Ordenes(id_orden, fecha, id_cliente)

Orders
Id_orden Fecha Id_cliente
2301 23/02/15 101
2302 25/02/15 107
2303 27/02/15 110

Clientes(id_cliente, nom_cliente, estado)

Clients
Id_cliente Nom_cliente Estado
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)

3
DATABASE I Database Normalization Exercise Guide

2. INVOICE OF PURCHASE SALE: The company COLOMBIAN SYSTEMS has hired you as the
"In-Charge Engineer" to systematize billing. In the following INVOICE
FOR BUYING AND SELLING, you must analyze all the available information and apply
the normalization process, up to reaching the Third Normal Form.

Detailed justification of each step is requested.

that lead to the final result.

Factura(NUM_FAC, FECHA_FAC, NOM_CLIENTE, DIR_CLIENTE, RIF_CLIENTE,

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

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
DESCRIPTION: Product description
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 all the attributes are grouped

that are part of the database to apply normalization rules.
Where the names of the attributes are included with their meaning:

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 = Actividad Comercial de Empresa Origen
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
4
DATABASE I Database Normalization Exercise Guide

DEST_DIR = Direccion del destinatario

DEST_TEL = Telefono del destinatario
DEST_KM = Distancia kilometraje de Ciudad origen a ciudad del
recipient
CODIGO = Codigo del paquete
TIPO = Tipo de paquete
NOMBRE = Nombre del paquete
DESCRIPCION = Descripción del paquete
VALR_ FLETE = Valor del flete

VIDEO CLUB: In a video store, it is necessary to maintain information about

around 3000 booths each of the booths has an assigned number
For each movie, it is necessary to know a title and category, for example:
comedy, suspense, drama, action, science fiction, etc. Some are maintained.
copies of many movies. Each movie is given an identification and is
keeps track of what each cassette contains.

A cassette can come in various formats and a movie is recorded in a single one.
cassettes; movies are often requested according to an actor
specific Tom Cruise and Demi Moore are the most popular, this is why it should be
to keep information about the actors belonging to each movie.

Not all movies feature famous artists, to the store customers.

He/She likes to know details such as the actor's real name and his/her date of birth.

In the store, information is maintained only about the actors that appear in the
movies and that is available. Videos are only rented to those who
they belong to the video club. To belong to the club, one must have a good
credit. For each club member, a record is kept with their name,
phone and address, each club member is assigned a number of
membership. Information about all the tapes that a customer wants to be maintained
rental, when a customer rents a cassette, the name of the should be known
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,
quantity

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
5
DATABASE I Database Normalization Exercise Guide

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 = código del tipo de película.

5. BOOK LOAN: Given the following BOOK_LOAN relationship (School,

profesor, asignatura/ habilidad, aula, curso, libro, editorial, fecha_prestamo)
which contains information related to the loans made by the publishers
to the primary school teachers for their evaluation in some of
the subjects/skills that are taught.

It is requested to apply the normalization rules and obtain its relational model.
indicate your keys, main attributes.

Subject/ Date
Colegio Profesor Class Course Book Editorial
skill loan
Learn and
C.P Juan Thought 1st teach in
1.A01 Grao 09/09/2015
Cervantes Pérez Logical Grado educación
infantile
C.P Juan 1st Preschool Techniques
Writing 1.A01 05/05/2015
Cervantes Pérez Rubio Grade, N56 Rubio
Learn and
C.P John Thought 1st Teach in
1.A01 Grao 05/05/2015
Cervantes Pérez Numeric Degree in Education
infantile
Thought
Education
C.P Alicia Spatial, 1st Prentice
1.B01 Childish 06/05/2015
Cervantes García Temporal and Grado Hall
N9
causal
Learn and
C.P. Alicia Thinking 1st teach in
1.B01 Graó 06/05/2015
Cervantes García Numeric Education degree
childish
Learn and
C.P Andres to teach in
Writing 1.A01 Graó 09/09/2015
Cervantes Fernández Education degree
infantile
C.P Andrés 2do Saber Topics of
English 1.A01 05/05/2015
Cervantes Fernández Education degree: Today
6
DATABASE I Database Normalization Exercise Guide
guide for
Parents and
Teachers
to know
to educate
C.P Juan Thought 1st Themes of
2.B01 guide for 18/12/2015
Quevedo Méndez Logical Degree Parents and
Today
Teachers
Learn and
C.P Juan Thought 1st teach in
2.B01 Graó 06/05/2015
Quevedo Méndez Numeric Degree in education
childish

[Link]: A relationship of the ENROLLMENT_REPORT (student_code,

nombre_alumno, especialidad, código_curso, nombre_curso, nombre_docente,
office, section) it is requested to apply the normalization rules up to
the 3NF.

Name Code
Code/ Special Nombre_curs Nombre/
/ / Workshop course
student dad o teacher
student course
Luis
Industry Mathematics Carlos
382145A Zuloag MA123 CB-214 U
l 2 Arambulo
a
Luis
Industry Physics Petra
382145A Zuloag QU514 CB-110 U
l Chemistry Rondinel
a
Luis
Industry Victor
382145A Zuloag AU521 Descriptive CB-120 W
l Moncada
a
Raúl Cesar Investigation
360247k PA714 Systems SC-220 V
Rojas on 1 Fernandez
Raúl Mathematics Carlos
360247k Systems MA123 CB-214 V
Rojas 2 Arambulo
Raúl Victor
360247k AU511 Systems Drawing CB-120 U
Rojas Moncada

7
DATABASE I Database Normalization Exercise Guide

[Link]: A database of a library is presented, apply the rules.

normalization simplifying up to the third normal form.

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

codLibro Titulo Author Editorial nombreLector Fechadev

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