Database Keys

Key Fields

• Keys are special fields that serve two main purposes:
  • Primary keys: Unique identifiers of the relation, ensuring all rows are unique (e.g., employee numbers, social security numbers).
  • Foreign keys: Identifiers that enable a dependent relation to refer to its parent relation (e.g., customer ID in an order relation).

Key Characteristics

• Keys can be simple (a single field) or composite (more than one field).
• A shorthand notation can be used to express the structure of a relation, including the name of the relation and its attributes (e.g., EMPLOYEE1(EmpID, Name, DeptName, Salary)).

Integrity Constraints

• Domain constraints: Allowable values for an attribute.
• Entity integrity: No primary key attribute can be null, and all primary key fields must have data.
• Referential integrity: Foreign key values must match primary key values in the parent relation (or be null).

Transforming EER Diagrams into Relations

• Mapping weak entities: The weak entity becomes a separate relation with a foreign key from the strong entity, and a primary key composed of the weak entity's partial identifier and the strong entity's primary key.
• Mapping binary relationships:
  • One-to-many: Primary key on the one side becomes a foreign key on the many side.
  • Many-to-many: Create a new relation with the primary keys of the two entities as its primary key.
  • One-to-one: Primary key on the mandatory side becomes a foreign key on the optional side.
• Mapping associative entities:
  • Without an identifier: Primary key is composed of the primary keys of the two entities.
  • With an identifier: Primary key differs from foreign keys.
• Mapping unary relationships: Recursive foreign key in the same relation.

Functional Dependencies and Keys

• Functional dependency: The value of one attribute (the determinant) determines the value of another attribute.
• Candidate key:
  • A unique identifier.
  • One of the candidate keys will become the primary key.
  • Each non-key field is functionally dependent on every candidate key.

Normalization

• First normal form (1NF):
  • No multivalued attributes (repeating groups).
  • Every attribute value is atomic.
  • All relations are in 1st normal form.