Database Normalization Exercises Guide
Translated by
ScribdTranslationsDatabase Normalization Exercises Guide
Translated by
ScribdTranslationsDatabase Normalization Exercise Guide
Exercise Guide
Apply the normalization rules to the following exercises.
1. Given the unnormalized relation does not comply with any normalization rule. To explain with a
Example of 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
Order ID Date Id_cliente Nom_cliente Estado Num_art nom_art cant 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
1FN
ordenes(id_orden, fecha, id_cliente, nom_cliente, estado)
Id_orden Date Id_cliente Customer Name State
2301 23/02/11 101 Martin Caracas
2302 25/02/11 107 Herman Choir
2303 27/02/11 110 Peter Maracay
Articulos (num_art, nom_art, cant, precio, Id_Ordem)
Num_art nom_art cant Precio Id_orden
3786 Red 3 35.00 2301
4011 Racket 6 65.00 2301
9132 Paq-3 8 4,75 2301
5794 Paq-6 4 5.00 2302
4011 Racket 2 65.00 2303
3141 Fund 2 10.00 2303
2NF
ordenes(id_orden, fecha, id_cliente)
Id_orden Date Id_cliente
2301 23/02/11 101
2302 25/02/11 107
2303 27/02/11 110
Cliente(id_cliente, nom_cliente, estado)
Id_cliente Client_name State
101 Martin Caracas
107 Herman Chorus
110 Peter Maracay
Page 1/9
Database Normalization Exercise Guide
Articulos (num_art, nom_art, cant, precio, Id_Ordem)
Num_art nom_art cant Precio Id_orden
3786 Red 3 35.00 2301
4011 Racket 6 65,00 2301
9132 Paq-3 8 4.75 2301
5794 Paq-6 4 5.00 2302
4011 Racket 2 65,00 2303
3141 Fund 2 10.00 2303
3FN
ordenes(id_orden, fecha, id_cliente)
Id_orden Date Id_cliente
2301 23/02/11 101
2302 25/02/11 107
2303 27/02/11 110
Cliente(id_cliente, nom_cliente, estado)
Id_cliente Customer_name State
101 Martin Caracas
107 Herman Chorus
110 Peter Maracay
Articulos (num_art, nom_art, precio)
Num_art nom_art Price
3786 Red 35,00
9132 Paq-3 4.75
5794 Paq-6 5.00
4011 Racket 65,00
3141 Fundamentals
10,00
Articulos_Ordenes(cant, Id_Ordem,, num_art)
can't Id_orden Num_art
3 2301 3786
6 2301 4011
8 2301 9132
4 2302 5794
2 2303 4011
2 2303 3141
Page 2/9
Database Normalization Exercises Guide
2. PURCHASE SALE INVOICE: The company COLOMBIAN SYSTEMS has contracted you as the
"In charge engineer" to systematize the 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 final result is requested.
Invoice(NUM_FAC, FECHA_FAC, NOM_CLIENTE, CLIENT_DIRECTORY,
CLIENT_REF,
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
DESCRIPCION:Descripción del producto
VAL_UNIT:Valor unitario del producto
CANT_PROD:Cantidad de productos q compra el cliente
The primary key is Invoice Sales Number: NUM_FAC
Factura(NUM_FAC, FECHA_FAC, NOM_CLIENTE, DIR_CLIENTE, RIF_CLIENTE, CIUDAD_CLIENTE, TELEF_CLIENTE,
CATEGORIA, COD_PROD, DESP_PROD, VAL_UNIT, CANT_PROD)
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 attributes are included.
with its meaning
* GUIA_NO = Numero de Guia
* GUIA_FECHA= Fecha de la Guia
* GUIA_HORA= Hora de la Guia
* ORGN_RIF = Identificacion de Empresa Origen
Page 3/9
Database Normalization Exercise Guide
* 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 = Source Company Cell
* 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
Page 4/9