CCNC/CCNC Module 5/The database application/Database Concepts/Normalization

From WikiEducator
Jump to: navigation, search
Tutorial.png Database Concepts 

What is a Database | How a Database is Organized | Data Types | Normalization | Self Assessment | Summary & FAQs


Database Normalization

Icon objectives.jpg

Outcomes

Upon completion of this tutorial the learner will:
  • Identify and describe relationships between entities
  • Compare and contrast first normal form with second normal form
  • Normalize entities to second normal form


Entity relationships

An understanding of a subject domain increases as entities and their attributes are identified. The relationships among the entities becomes an essential part of your database design. When identifying entities and their relationships, think in terms of nouns and verbs. The entities are nouns and relationships are verbs. In the previous example of the Students and their Addresses; "Students" and "Addresses" are the entities (nouns) and "live at" and "contain" are the relationships (verbs).



Icon inter.gif

Web Resources

Wikipedia has a number of entity relationship and normalization based entries, here are a few of the most relevant;

A review of object role modeling can also provide a good method for identifying entities and relationships.



Exploring first and second normal form

A solid understanding of normalization and normals forms is essential for database design. During database normalization you analyze entities and attributes through a series of rules known as normal forms. There are five basic normal forms, and each builds upon the rules of the previous. In other words, you cannot have a database design in third normal form without also meeting all the rules of first and second normal form. Consider the process of normalization as the process of simplification and the removal of redundancy. For our first review of normalization we are only going to look at the first and second normal forms. What you learn from these two normal forms will provide enough foundation to begin normalizing a database design.

First Normal Form

A database is said to be in first normal form when the tables;

  1. are guaranteed to not have any duplicate records (i.e. have a way to identify uniqueness within each record)
  2. do not have any repeating groups (i.e. {grade-id, course1, course2, course3} where course is the repeating group)

Second Normal Form

A database is said to be in second normal form when the tables;

  1. are in first normal form
  2. have fields (or attributes) that create a logical entity. And the fields relate back to the unique identifier of the record. (i.e. Students consist of first-name, last-name, gender, and birth-date. A phone number field would NOT be a part of the Student)

Normalize this

Normalize the following table into second normal form.

People
person_id first_name last_name birth_date gender phone_numbers
001 Bill Smith 28/12/1963 M home: (604) 555-1234; fax : (604) 555-2345
001 Bill Smith 28/12/1963 M cell: (778) 555-2345
005 Fiona Jones 26/10/1961 F home: (604) 555-1234; pager (605) 555-9988
006 John Morrison 14/07/1951 M home: (604) 555-0987; fax: (604) 555-2345; cell: (877) 555-7654
012 Lisa Ballard 11/03/1965 F home: (604) 555-1234


Icon activity.jpg
Activity
Using these 5 steps modify the above table into first, then second normal form;
  1. Create an entity relationship diagram for the above table
  2. Field entries should be singular (i.e. one field should not contain more than one entry)
  3. Identify the columns that would create uniqueness
  4. Identify duplicate data
  5. Groups fields within entities so duplicate records can be reduced to one record




Step 1 (entity relationship diagram)

CCNC-5.1.1.2-PeoplePhoneNumbers.jpg

Step 2 (first normal form)

People
person_id first_name last_name birth_date gender phone_type phone_number
001 Bill Smith 28/12/1963 M home (604) 555-1234
001 Bill Smith 28/12/1963 M fax (604) 555-2345
001 Bill Smith 28/12/1963 M cell (778) 555-2333
005 Fiona Jones 26/10/1961 F home (604) 555-4455
005 Fiona Jones 26/10/1961 F pager (604) 555-9988
006 John Morrison 14/07/1951 M home (604) 555-0987
006 John Morrison 14/07/1951 M fax (604) 555-0096
006 John Morrison 14/07/1951 M cell (877) 555-7654
012 Lisa Ballard 11/03/1965 F home (604) 555-6543

Step 5 (second normal form)

People
person_id first_name last_name birth_date gender
001 Bill Smith 28/12/1963 M
005 Fiona Jones 26/10/1961 F
006 John Morrison 14/07/1951 M
012 Lisa Ballard 11/03/1965 F
PhoneNumbers
phone_type phone_number person_id
home (604) 555-1234 001
fax (604) 555-2345 001
cell (778) 555-2333 001
home (604) 555-4455 005
pager (604) 555-9988 005
home (604) 555-0987 006
fax (604) 555-0096 006
cell (877) 555-7654 006
home (604) 555-6543 012
Icon present.gif
Tip: person_id is a key
  • You will notice that the person_id is present in both the People and PhoneNumbers tables. This allows you to join the phone numbers to their people 'owners'. More on keys in the next section.


If your getting bored with all this theory

Well we've covered enough material where you could jump into actually building a database. So go to tutorial 5.2 (Designing and creating tables) and get your hands dirty. I would strongly recommend you come back to this section as we are going to get deeper into normalization and how to build a database in third normal form.

Test your knowledge



Icon qmark.gif

Normalization

 

1. A database is in second normal form when?

(a) It is in first normal form.
Almost correct, click here to review the topic.
(b) has no repeating groups.
Almost correct, click here to review the topic.
(c) Table attributes make up logical entities.
Almost correct, click here to review the topic.
(d) All of the above
Absolutely Correct!

Your score is 0 / 0