Relational Database Fundamentals

Study Notes

Each table holds information on a single entity, with multiple instances and attributes.
Types of entities include:
- Physical: e.g., customer, ingredient
- Events: e.g., purchase, return, appointment
- Relationships: e.g., supervision, movie/director

Attributes categorize information about entities and can vary in nature:
- Permanent, time-varying, required, or optional
- Single-valued (one value) or multivalued (multiple values)
- Stored (direct details) or derived (calculated details)

Defined as an attribute or combination of attributes that uniquely identifies instances of an entity.
Types of keys:
- Simple key: A single detail making each item unique.
- Composite key: A combination of details for uniqueness.
- Candidate key: A potential primary key based on uniqueness.
Primary key requirements include:
- No change in value post-creation.
- Cannot be null.
- Should not depend on changing attributes like location or personnel.
- Distinction between artificial keys and composite keys based on existing attributes.

Distinction between relationship types:
- Unary: Instances of the same entity relate (e.g., employees to supervisors).
- Binary: Instances of two different entities relate.
  - Types of cardinality:
    - 1-to-1: Each instance of A corresponds to exactly one instance of B.
    - 1-to-many: Each instance of A can link to multiple instances of B, but B links only back to one A.
    - Many-to-many: Each instance of A can connect to many instances of B and vice versa; requires transformation into two 1:M relationships.
Primary keys and foreign keys are used to connect related records across tables.

Categorization of relationships based on necessity:
- Mandatory: Links that must exist (e.g., students to classes).
- Optional: Links that may or may not exist (e.g., student club membership).

M:N relationships must be converted into 1:M relationships through a linking entity.
Types of M:N relationships:
- Due to repeated attribute groups (e.g., sales order linked to stock items).
- Genuine M:N relationships (e.g., suppliers and products).
Guidelines for creating link entities:
- Create a new entity to resolve the M:N relationship.
- Connect the link entity with two one-to-many relationships.
- The mandatory ends of relationships must involve one instance from the original entities.
- The optionality of the "many" end mirrors that of the original relationship.
- Naming convention for link entities can follow the format A/B link.

Relationships involving connections within the same entity type, where an entity relates to itself.
Types include:
- 1:1 recursive: A single corresponding instance.
- 1:M recursive: An entity may have multiple subordinate entities; each subordinate connects to just one parent (e.g., employees and managers).
- M:N recursive: Entails multiple relationships within the same entity type.