Database Keys

what is a key?

A data item that allows us to uniquely identify an individual occurrence, record or an entity[table] in databases

Types of keys:

1.Primary key : One or more column or attribute that distinguishes a specific record from another e.g Employee_id in employee table,
employee_id & id_number also being used as primary key

2.Foreign Key-One or more attribute in an entity/table that enables a relationship to another entity

e.g deptno in employee table

3.Candidate key-Any column that could be used as the primary key.

4. Secondary Key-Candidate key that is not chosen as the primary
key. Primary key is selected from candidate keys, and those not selected are referred to as secondary key

5.Simple key: A single attribute that uniquely distinguishes a specific record from another e,g employee_id only or id_number only

6.compound key: A key made up more than one simple key and each of the keys making up the compound key are simple keys on their own

7.composite key : A key made up or more than one simple key and each of the keys making up the composite key are not simple keys on their own

e.g employee_id and employee_jobtitle employee_id is a simple key but employee_jobgroup is not a simple key on its on

8.Artificial Key: Keys generated by a business e.g., VIN numbers for vehicles, ISBN for books.

9:Natural keys : keys derived from real world occurrences e.g
SSN,ID number

10: Surrogate key: A key that uniquely identifies a row, has no relationship with the record it is identifying other than identifying the row uniquely, it is system generated and is mainly used in OLAP systems to maintain history

Advantages of surrogate key

•It is good for maintaining history, used mainly in OLAP e.g., in slowly changing dimension

•it is immutable and has high performance because of its compact datatype (such as a four-byte integer).

•Surrogate keys are also less expensive to join (fewer columns to compare)

•Uniformity - When every table has a uniform surrogate key, some tasks can be easily automated by writing the code in a table-independent way.

Disadvantage:

•Disassociation- The values of generated surrogate keys have no relationship with the real world meaning of data stored/held in a row.

11. Business Key: Unlike surrogate key , business key is a type of key that uniquely identifies an object or a record in the
database.