Copyright

SQL Normalization: Example & Rules

Instructor: David Gloag

David has over 40 years of industry experience in software development and information technology and a bachelor of computer science

The amount of information we capture these days is staggering, so removing duplication is important. In this lesson, we'll take a look at SQL, normalization, and the rules associated with them.

The Prevalence of Databases

Databases are widely used by business, and private individuals, these days. It is hard to do anything on a computer that doesn't involve them. Walmart stores employee information in a database, Amazon stores customer information in a database, and individuals store contact and email information in a database. In fact, the filing system on your personal computer is a database, of sorts. With the prevalence of the technology, and the amount of information stored in them, it makes sense that effort would be spent to improve efficiency, and reduce duplication. Many techniques exist. One, in particular, uses SQL and normalization.

What is SQL?

SQL is a language designed to retrieve information, change information, and perform general maintenance, in a database. Developed in the early 1970's by IBM, Structured Query Language (SQL) became publicly available through a product offering from Oracle in 1979. The language is robust, meaning that it provides a wide variety of capabilities for your use. The command set includes the following:

  • SELECT - retrieve information in a database
  • INSERT - add information to a database
  • UPDATE - change information that already exists in a database
  • DELETE - remove information from a database
  • CREATE - generate a new object in a database

As a whole, SQL provides an ordered method for the maintenance, and management, of information.

What is Normalization?

Normalization is the process of organizing the information in a relational database to reduce duplication. A relational database is a collection of information that is organized into a table or tables, where each row is a record, and each column is a field in that record. In this type of organization, each row must be unique. Duplication is reduced by splitting the information up into smaller tables, each with a specific focus. For example, if you have a table that contains a person's eye, hair color, and the movies they like, we might split that into a table that contains personal attributes, and a table that contains movie preferences. It is important to note that normalization is not automatic. The database management system will not take care of it for you. You must design your database with normalization in mind.

Example

Okay, now that we have the idea, let's look at an example. Say we have a table that contain various pieces of information about some people:

Database Table Example
Database-Table

Notice that there is a fair amount of duplication in order to keep the rows unique. Karen, Brown, and Red, for example is duplicated a number of times. Normalization would split this information up into two tables to reduce this affect:

Normalized Database Example
Database-1NF

This organization removes the duplicate name, eye color, and hair color information and only stores what is necessary. Duplicate name information cannot be removed as this is the way the information is linked.

What are the Rules for Normalization?

There are a number of normalization forms that have been proposed, and used. Selection depends on the information being stored, and its intended use. The target form in the example above is called first normal form (1NF). The rules to create this form are as follows:

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support