CCNC/CCNC Module 5/The database application/Database Design
 |
Database Design |
Database Keys | Database Indexes | Tables and Relationships | Self Assessment | Summary & FAQs |
Contents
Database Keys
Upon completion of this tutorial the learner will:
|
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.
Using the People and PhoneNumbers tables;
|
| 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 |
- 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;
- are in second normal form
- 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
|
|
