2. Relational Model

Question 1

What are the three core components of a formal logical data model?

Answer 1

Structure (how data is organised, e.g., relations)

Integrity Rules (how data is kept correct/consistent)

and Operators (how data is manipulated).

Question 2

What is a Relation?

Answer 2

Informally like a table; formally, a set of n-tuples. Tuples (rows) are unordered. Attributes (column headers) are unordered. No two tuples are identical across all attributes. Attribute values must be single-valued (“atomic”)

Question 3

What is a relation heading?

Answer 3

Denoted R{A1, A2, …, An}, consisting of a relation name R and a set of attributes {A1, …, An}.
Each attribute draws values from a specific domain. The degree is the number of attributes.

Question 4

What are the keys?

Answer 4

◦ Superkey: Any subset of attributes whose combined value is unique across all tuples.
◦ Key (Candidate Key - CK): A minimal superkey (removing any attribute means it’s no longer unique). A relation can have multiple CKs.
◦ Primary Key (PK): One designated CK. The PK uniquely identifies each tuple.

Question 5

Key and Non-Key attributes:

Answer 5

A key (prime) attribute is part of some CK.
A non-key (nonprime) attribute is not part of any CK.

Question 6

Domain Integrity Constraint:

Answer 6

Values must be from the attribute’s domain (data type, range, etc.).

Question 7

Entity Entegrity Constraints:

Answer 7

The PK cannot be null, and the entire PK value must be unique. Implies NOT NULL on PK attributes

Question 8

Referential Integrity:

Answer 8

A foreign key (FK) in a referencing relation must match an existing PK value in the referenced relation or be entirely null. Prevents “broken links”.
A FK can reference its own relation (recursive). Oracle doesn’t support ON UPDATE CASCADE natively, requiring triggers.
PostgreSQL does support FK actions like ON DELETE/ON UPDATE CASCADE, SET NULL, RESTRICT, NO ACTION

Question 9

Nulls:

Answer 9

A special marker representing “value not known”, “not applicable”, etc..
Comparisons with null (even x = null) evaluate to “unknown” (behaves like false).
IS NULL or IS NOT NULL must be used. Nulls can cause storage issues, make meanings unclear, affect joins/aggregates, break uniqueness checks, and make understanding difficult