CCNC/CCNC Module 5/The database application/Database Design

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

Database Keys | Database Indexes | Tables and Relationships | Self Assessment | Summary & FAQs

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


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.

Test your knowledge

Icon qmark.gif

Database keys and third normal form


1. A primary key is used to uniquely identify a record?

Absolutely Correct!
Incorrect, click here to review the topic.

2. A compound key uses two or more fields to uniquely identify a record?

Absolutely Correct!
Incorrect, click here to review the topic.

3. From the above example, to achieve third normal form you could?

(a) Move the teacher_name and hire_date columns into another table
Incorrect, click here to review the topic.
(b) Move the teacher_id, teacher_name and hire_date columns into another table, while leaving a foriegn key reference in the classroom table
Absolutely correct!
(c) Rename hire_date to class_start_date
Incorrect, click here to review the topic.
(d) Concatenate the teacher_name and hire_date fields
Incorrect, click here to review the topic.
(e) Move the teacher_id, teacher_name and hire_date columns into another table
Almost correct, click here to review the topic.

Your score is 0 / 0