Database Normalization is a technique of organizing the data in the database. No

Question

Database Normalization is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anomalies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables. In creating a database, normalization is the process of organizing it into tables in a way that the results of using the database are always obvious. Normalization may have the effect of duplicating data within the database and often results in the creation of additional tables. Although normalization tends to increase the duplication of data, it does not introduce redundancy, which is unnecessary duplication. Normalization is typically a refinement process after the initial exercise of identifying the data objects that should be in the database, identifying their relationships, and defining the tables required and the columns within each table. The following table is an example of Normalization.

Customer

Item Purchased

Purchase Price

Thomas

Shirt

$40

Maria

Tennis Shoes

$35

Evelyn

Shirt

$40

Pajaro

Trousers

$25

If this table is used for the purpose of keeping track of the price of items, and you want to delete one of the customers, you will also delete a price. Normalizing the data would mean understanding this and solving the problem by dividing this table into two tables, one with information about each customer and a product they bought and the second about each product and its price. Making additions or deletions to either table would not affect the other.

Customer

Item Purchased

Purchase Price

Thomas

Shirt

$40

Maria

Tennis Shoes

$35

Evelyn

Shirt

$40

Pajaro

Trousers

$25

Explanation / Answer

Normalization degrees of relational database tables have been defined and include:

First normal form (1NF)
>This is "basic" level of normalization and generally corresponds to the definition of any database's
>It contains a two-dimensional tables with rows and columns.
>Each column that corresponds to a sub-object or an attribute of the object which is represented by entire table.
>Each row represents a unique instance of that sub-object/attribute and must be different in some way from any other row (here there is no possible for the duplicate rows)
>All entries in any column must be of same kind.
For example:
In the column labeled "Customer" only customer names/numbers are permitted.

Second normal form (2NF)
>At this level of normalization, each column in a table that is not a determiner of the contents of another column must itself be a function of the other columns in the table.
For example:
In a table with three columns containing customer ID, product sold, and price of the product when sold, the price would be a function of the customer ID (entitled to a discount) and the specific product.

Third normal form (3NF)
At the second normal form, modifications are still possible because a change to one row in a table may affect data that refers to this information from another table.
For example:
Using the customer table just cited, removing a row describing a customer purchase (because of a return perhaps) will also remove the fact that the product has a certain price.
>In the third normal form, these tables would be divided into two tables so that product pricing would be tracked separately.

Domain/key normal form (DKNF)
>A key uniquely identifies each row in the table.
>A domain is the set of permissible values for an attribute.
>By enforcing key and domain restrictions, the database is assured of being freed from modification anomalies.
>DKNF is the normalization level that most designers aim to achieve

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.