Keys

• Keys with two or more columns are called composite keys
• A superkey is a field, or set of fields that uniquely identify an entity in a relation.
  • A relation may have many superkeys.
• A candidate key is the minimal set of attributes that uniquely identifies each occurrence of an entity type.
  • IE it determines all other columns in a relation.
  • It lets you select a unique row.
  • In SKU_DATA
    • both SKU and SKU_Description are candidate keys.
    • (SKU, SKU_Description) is not a candidate key since it is not minimal, it does, however, determine an entity.
    • In other words, (SKU) ⊂ (SKU, SKU_Description), so (SKU, SKU_Description) is not a candidate key.
    • Buyer is a determinate of Department, but it does not determine any other column, therefore it is not a candidate key.
    • in ORDER_ITEM
      • (OrderNumber, SKU) is a candidate key.
    • As a minimum, the entire entity in a relation can be a candidate key.
• A primary key is selected from the candidate keys.
  • Primary key selection is based upon
    • attribute length
    • Minimal number of attributes required
    • Certainty of uniqueness
• A Surrogate Key is an artificial column that is added to a table to serve as a primary key.
  • The surrogate key is not derived from application data.
  • Normally surrogate keys are not displayed in reports
  • The primary duty is to act as a primary key
• A Foreign Key is a column or composite of columns that is the primary key of a table other that the one in which it appears.
  • A foreign key is a field that matches a candidate key in another table.
  • A foreign key is a referential constraint between two tables.
  • In ORDER_ITEM
    • SKU and OrderNumber are both foreign keys.
    • They are also both primary keys for their tables, but this is not a requirement.
  • The book gives:
    • DEPARTMENT(DepartmentName, BudgetCode, ManagerName)
    • EMPLOYEE(EmployeeNumber, EmployeeName, DepartmentName)
    • In this case, the DepartmentName is not a primary key for EMPLOYEE