Physical and Relational Data Model

Candidate Key and Primary Key

A candidate key is a combination of attributes that uniquely identifies a database record without any extraneous data. Each table may have one or more candidate keys, with one selected as the primary key. Any candidate key that is not chosen as the primary key is referred to as an alternate key.

Primary Key Characteristics

• Must be unique
• Cannot contain null values
• Should remain constant throughout its lifetime

If a primary key consists of more than one attribute, it is known as a composite key.

Foreign Key

A foreign key is a primary key in a different table, establishing a relationship between the tables. Diagrammatically, a foreign key is represented as a line with an arrow at one end.

Fields and Tables

Fields are the columns in databases, representing specific pieces of information within a record. A table is an arrangement of data in rows and columns.

Referential Integrity

Referential Integrity enforces the following rules:

1. A record cannot be added to a table containing a foreign key unless there is a corresponding record in the linked table.
2. Cascading update should be implemented, ensuring that when a record in the linked table changes, all foreign keys in other tables are updated accordingly.
3. Cascading delete should be implemented, meaning that when a record in the linked table is deleted, all related records in the linking table are also deleted.