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

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.

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; An example of this would be; 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.
 * 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.