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

---

Tutorial 5.1.1.3 - Database Keys

Outcomes

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.

Activity

Using the People and PhoneNumbers tables; Can you identify all the unique identifiers? Are there any compound keys?

People

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

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-6543	012

Solution

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

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.