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

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

Tutorial - Database Keys

Icon objectives.jpg


Upon completion of this tutorial the learner will:
  • Be able to discuss the concept of database keys
  • Normalize a database to third normal form

Database Keys

Within a database table it is preferred that every record in the table should be unique, for a lack of uniqueness creates redundancy and violates the rules of normalization. This uniqueness is enforced through the use of keys. There are two primary types of keys; unique identifiers and compound keys. The unique identifier is usually an id field that is unique for every row in a table. The id field is sometimes autogenerated, in that it is automatically created when a new row is added to a table. An email address could also be considered a unique identifier, for no two email addresses can be exactly the same. Compound keys are the concatenation of two of more fields, this concatenation creates uniqueness. A review of the results of the previous activity provide a look at unique identifiers.

Icon activity.jpg
Using the People and PhoneNumbers tables;
  • Can you identify all the unique identifiers?
  • Are there any compound keys?

person_id first_name last_name birth_date gender
001 Bill Smith 28/12/1963 M
005 Fiona Jones 26/10/1961 F
012 Lisa Ballard 11/03/1965 F
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-6543 012

Icon reflection.gif


There are two unique identifiers;
  • The person_id field is a unique identifier
  • The phone_number field is a unique identifier

Within this example (if you also considered the current data) you could consider first_name combined with last_name a compound key. This holds true with the given data, when the next Bill Smith came along it would violate the tables uniqueness. Even including birth_date and gender in the compound key could not guarantee uniqueness for there could be more than one male Bill Smith with a birth date of 28/12/1963. That is why the People table uses an id column as its uniqueness identifier.

There are many types of database keys and the main five types are explained here.

Primary Key

A primary key is used to identify the records in the table and must be unique for each record. You can choose whether you want to make a specific field the primary key or whether you want a separate id field created. You can also choose whether you want to autogenerate the number or type it in manually for each record. A table can have only ONE primary key.

Candidate Key

Any unique identifier or compound key that guarantees the uniqueness of a record can be considered a candidate key. In some circumstances a table may have more than one unique identifier, each of these identifiers is considered a candidate key.

Compound Key

A compound key is a unique identifier formed by concatenating two or more fields into a key that guarantees uniqueness.

Alternate Key

An alternate key (or secondary key) is any candidate key which is not selected to be the primary key.

Foreign Key

A foreign key is a reference between two tables. In the previous activity the person_id field in the PhoneNumbers table is a foreign key for it establishes the relationship back to the primary key of the People table.

3rd Normal Form

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

  1. are in second normal form
  2. do not feature any functional dependencies between non-prime attributes. A non-prime attribute is one that does not belong to any candidate key. In other words, all the fields in the table must relate back to the entity described by the table.

An example of this would be;

classroom_id class_name teacher_id teacher_name hire_date
100A physics 2002-0911 Jenny Jones 28/12/1963
100B languages 1998-3412 Martin Clark 26/10/1961
100D arts 2003-6788 Bob Williams 11/03/1965

For example, consider a "Classrooms" table whose attributes are classroom_id, classroom_name, teacher_id, teacher_name, and hire_date; and suppose that each teacher can teach in one or more classrooms. {classroom_id} is a candidate key. Although hire_date is functionally dependent on {classroom_id}, it is also functionally dependent on the non-prime attribute teacher_id. This means the table is not in third normal form.