Database Keys

Study Notes

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).

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)).

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).

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 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.