Scribd
Upload
5 views8 pages

Database Normalization Steps Explained

The document describes the normalization process of a PURCHASE ORDER database, detailing the transition from unnormalized form (UNF) to third normal form (3NF). It outlines the separation of repetitive groups, composite key dependencies, and non-key field interdependencies to achieve a structured database design. The final result includes four distinct groups that streamline data entry and reduce redundancy.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views8 pages

Database Normalization Steps Explained

The document describes the normalization process of a PURCHASE ORDER database, detailing the transition from unnormalized form (UNF) to third normal form (3NF). It outlines the separation of repetitive groups, composite key dependencies, and non-key field interdependencies to achieve a structured database design. The final result includes four distinct groups that streamline data entry and reduce redundancy.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Solved Normalization Exercise

Let us consider the PURCHASE ORDER document in figure 4, used to place an order for
order to the compact disc supplier.

OrdenDeCompra(ORD-NO, ORD-DATE, PROV-NO, PROV-NAME, PROV-DIR,


PROV-NIT, CODIGO, TITULO, CANT, VR-UNIT)

Figure 4

ORD-NO: Número de Orden de Compra

ORD-DATE: Fecha de la Orden de Compra

PROV-NO: Numero del Proveedor

PROV-NAME: Nombre del Proveedor

PROV-DIR: Supplier's Address

PROV-NIT: NIT or Supplier ID

CODE: Code of the CD or Album

TITULO: Titulo del CD o Álbum

CANT: Cantidad de CDs a pedir

VR-UNIT: Unit value of the CD or Album

Even unnormalized forms must have a key. In the example above, we can deduce
the ORD-NO is the key. Keys are usually underlined during the ARD analysis.
FIRST NORMAL FORM (1NF)

Rule 1NF. Separate the repeating group:

In the list above, the items after PROV-NIT are repetitive, this means that for a
the same order appears several times, given that multiple items can be ordered in the same order
categories, or several titles of the same category.

The repetitive groups must be separated and written as an independent group with its
respective key. This group must relate to the non-repetitive group.

Non-Repetitive Group

ORD-NO

ORD-DATE

PROV-NO

PROV-NAME

PROV-DIR

PROV-IT

Repetitive Group

CODE

TITLE

CANT

VR-UNIT
The repetitive group has CODIGO as the key. However, this key is not unique, given that it
Can you repeat in other order numbers? It needs to be combined with the key of the first group. To the
combine the ORD-NO field together with the CODE field for the second group, we can
to deduce that this combination can act as a unique key, as there cannot be the same one
order that has 2 identical codes. Therefore, after applying the first normal form,
we obtain these groups:

GROUP 1

ORD-NO

ORD-DATE

PROV-NO

PROV-NAME

PROV-DIR

PROV-NIT

GROUP 2

ORD-NO

CODE

TITLE

CANT

VR-UNIT
SECOND NORMAL FORM (2NF)

Rule 2NF. Separate dependencies from composite keys.

Only those data groups that have combined keys are analyzed. (keys that have more
of a field or attribute to achieve uniqueness). Therefore, for the second normal form, we
We will focus only on group 2, which has a composite key.

In group 2, any attribute that does not depend entirely on the composite key (i.e., that
does not depend on all the attributes of the key at the same time but rather on just one of them) is separated from
main group, and is isolated in an independent group along with the initial key attribute of the
which is indeed dependent. Let's look at the process for greater clarity:

When analyzing group 2, we found that the TITLE field entirely depends on the field
CODE, and not of the composite key. We reached this conclusion by deducing that the title of the CD
it is associated with a single code, which is why we could think that CODE and TITLE are
campos redundantes ya que con cualquiera de ellos podemos identificar al elemento, pero pensemos
in which the design does not allow us to get rid of any of the fields, since the instructions do not allow us
they are required to use and store ALL the information available in the data dictionary.

Therefore, what we can do, applying the second normal form, is to isolate a third group, which
tenga a CODIGO como llave, y TITULO como campo de la tabla. Igual sucede con el campo VR-
UNIT. This field is exclusively associated with the CODE field. That is, each CD Title
with a specific code, it must correspond to a selling price that is set only once by
each element. In this way, if at any point we need to alter the unit value of a
CD, we should only do it in the group 3 table, once for each element.
In conclusion, after applying the second normal form, we obtain these groups:

GROUP 1

ORD-NO

ORD-DATE

PROV-NO

PROV-NAME

PROV-DIR PROV-NIT

GROUP 2

ORD-NO

CODE

CANT

GROUP 3

CODE

TITLE

VR-UNIT

At this level, we can already mentally envision the usefulness of separating the data dictionary.
in different groups. Let's suppose we want to enter 50 orders into the system, and in all of them there is
included the Juanes CD, whose code is 1520. The title associated with code 1520 is 'Fíjate bien'.
If group 3 did not exist, for each of the orders we would be entering not only 50 times the
code 1520, but it also requires us to write the text 'Pay attention' 50 times. We consider that
this last one is a task that can be saved by applying the second normal form, since if
we leave a separate table for CODE and TITLE, when entering the orders we only have to type
50 times the code 1520 in the group 2 table (each time associated with a different order number and
unique), and the same code only once in table 3, which means the text "Pay close attention" would only have
that should be typed only once therefore. In the event that we are asked to check the title of the CD in
A record from table 2, we will simply use the value of the CODE field from that record to
transfer the query to table 3, which will return the requested information about the Title.
THIRD NORMAL FORM (3NF)

Rule 3NF. Examine the interdependencies between the fields or attributes that are not keys.

All fields or attributes in each group that are not keys should be examined for
check that there are no interdependencies between them. If any are found, such dependencies
they must be separated into different groups whose key must be the field they are dependent on,
leaving this key field also in the original group.

If we analyze group 1, we find that the fields PROV-NAME, PROV-DIR, and PROV-NIT
they are entirely dependent on the PROV-NO field.

From group 2, we already removed the interdependencies during the second normal form, and group three is
precisely the result of that separation in the second normal form, therefore we ignore it
At this stage, we focus only on group 1.
By separating the supplier information into one group, leaving a fourth group with this information,
we obtain the third normal form, which is as follows:
GROUP 1

ORD-NO

ORD-DATE

PROV-NO

GROUP 2

ORDER-NO

CODE

CANT

GROUP 3

CODE

TITLE

VR-UNIT

GROUP 4

PROV-NO

PROV-NAME

PROV-DIR

PROV-NIT
RESUMEN DE LA NORMALIZACION (hasta 3FN)

UNF - UNFORMATTED FORM


Data dictionary

1NF - FIRST NORMAL FORM


Separate the repetitive group

2NF - SECOND NORMAL FORM


Separate dependencies of composite keys

3NF - THIRD NORMAL FORM


Separate dependencies of non-key fields

[Link]

You might also like