Relational Databases  «Prev  Next»

Lesson 6 An example of a customer table
Objective Understand what a table looks like if it is not normalized.

Customer Database Table Example

Let us take a look at an example table that contains customer data:

CustID Name City Item_ID Description Qty Total
000001 Smith Tucson 100101 Green Widgets 1 $50.00
000001 Smith Tucson 100102 Blue Widgets 2 $100.00
000001 Smith Tucson 100103 Yellow Widgets 1 $50.00
000002 Jones L.A. 100101 Green Widgets 2 $100.00
000002 Jones L.A. 100106 Orange Widgets 1 $50.00

Basic Order Table

Because this is a basic order table, you can easily see that the table contains the
  1. customer ID, (CustID)
  2. name, (Name)
  3. city, (City)
  4. ordered item ID, (Item_ID) and
  5. description. (Description)

For each item, the quantity and extended total are included. You can quickly see that many elements are duplicated, making the table redundant to read through and requiring additional storage space. The way the data is currently displayed in the table, you would need to add two new columns to include the additional address information and the individual cost per item before this becomes a useful set of information. Normalization, in simple terms, says that there will be one and only one reference to information in a given database, and that tables that use that information will use a pointer (primary key to be introduced later). For example, customer ID (CustID) and item ID above. We will normalize this table in the next lesson, so click on the Next» link located at the top or bottom of this page.

Characteristics of a Database Table which is not normalized

A database table that is not normalized, often referred to as a non-normalized or unnormalized table, exhibits several distinct characteristics that differentiate it from normalized tables. These characteristics generally lead to inefficiencies and potential issues in data management. Key characteristics of a non-normalized database table include:
  1. Redundant Data: There is a significant presence of duplicate data across multiple rows. This redundancy not only consumes more storage space but also increases the risk of data inconsistencies.
  2. Data Update Anomalies: Due to redundancy, any update to a data item must be carried out in multiple places. Failure to update all instances of the data can lead to inconsistencies. This is known as an update anomaly.
  3. Insertion Anomalies: Inserting new data can be problematic. If certain attributes are dependent on others, the absence of a particular piece of data might prevent the insertion of a new row, leading to incomplete or skewed data representation.
  4. Deletion Anomalies: Similarly, deleting a row of data can inadvertently result in the loss of valuable information that is not specifically related to the entity being removed. This is because multiple types of information are often entangled within a single table.
  5. Lack of Primary Key: In some cases, non-normalized tables may not have a well-defined primary key, making it difficult to uniquely identify each row of data.
  6. Multi-valued Attributes: Tables may contain columns designed to hold multiple values. For example, a column might contain a list of phone numbers instead of a single number, which goes against the principles of atomicity.
  7. Attribute Dependency Irregularities: There is often no clear separation of dependencies. Non-primary attributes may depend on other non-primary attributes, leading to a complex and inefficient structure.
  8. Complex Queries for Simple Tasks: Retrieving simple data can require complex and inefficient queries, as information is not optimally organized and segmented.
  9. Composite Attributes: Columns in unnormalized tables might consist of composite attributes, meaning they contain multiple data points in one field. For example, a "full address" column including street, city, and zip code.
  10. Lack of Data Integrity: Without proper normalization, maintaining data integrity becomes challenging, as there are no strict rules to govern the relationships between data types and tables.

In summary, a non-normalized table typically suffers from data redundancy, update, insertion, and deletion anomalies, lacks a clear primary key, and shows dependency irregularities and integrity issues, which make database management more cumbersome and error-prone.

SEMrush Software