Lesson 2 — Relationships, Domains, and Keys

Relationship properties answer different questions

A relationship line can carry several independent ideas. The exam often tests whether you can name the right one.

Cardinality

Cardinality asks how many instances may or must participate: zero, one, or many, including optionality.

Example: one Course may have many Students; one Student may enroll in many Courses.

Arity

Arity asks how many entity types participate in the relationship.

• Unary / recursive — one entity type relates to itself. Example: Employee reports to Employee.
• Binary — two entity types participate.
• Ternary — three entity types must participate in one relationship.

Memory hook: cardinality counts occurrences; arity counts entity types.

A scenario containing the word “many” is not automatically about arity.

Unary hierarchy vs unary network

A unary relationship can form different structures.

• Hierarchy: an instance has at most one parent in the same entity type.
• Network: an instance can have multiple parents.

The clue is one-parent maximum vs multiple-parent possibility.

Foreign keys and identifying relationships

In a relational implementation, a foreign key is a migrated key from a related parent entity/table used to represent the relationship.

Identifying relationship

The parent primary key migrates into the child and becomes part of the child’s primary key.

Non-identifying relationship

The parent primary key migrates into the child as a foreign key, but does not become part of the child’s primary key.

That key behavior also explains dependent vs independent entities in Chapter 5: a dependent entity’s identity relies at least partly on an attribute contributed by another entity; an independent entity can be identified without that identifying dependency.

Domain forms

Chapter 5 gives five common ways to define valid values.

Domain form	What it constrains	Example
Data type	basic value type	integer, date
Format	required pattern	AAA-9999
List	enumerated allowed values	Open, Closed, Cancelled
Range	bounded values	1–5
Rule-based	conditional validity	HireDate < today

These are alternative forms, not a sequence.

Key taxonomy: construction vs function

One of the most important Chapter 5 insights is that key labels answer different classification questions. A key can correctly have several labels at once.

Construction — what is the key made of?

• Simple key: one attribute.
• Composite key: two or more attributes.
• Compound key: a composite key whose components are themselves foreign keys.

Every compound key is composite, but a composite key is not automatically compound.

Identification function — what job does the key perform?

• Super key: any attribute set that uniquely identifies an instance, even if it contains unnecessary attributes.
• Candidate key: a minimal unique set — remove any component and uniqueness is lost.
• Primary key: the candidate selected as the main identifier.
• Alternate key: a candidate key not selected as primary.
• Business key: a business-meaningful identifier used to recognize/retrieve an occurrence.
• Surrogate key: a system-generated identifier with no business meaning/intelligence, usually hidden from business users.

Important layered classification example

Suppose (Order_ID, Product_ID) uniquely identifies an Order Line and both columns are foreign keys.

It can simultaneously be: - composite; - compound; - candidate; - primary, if selected.

This is why memorizing key words as mutually exclusive buckets fails.

Surrogate-key trap

If a surrogate replaces a meaningful natural/composite primary key in physical design, do not erase the original business uniqueness. Preserve the natural uniqueness as an alternate key or equivalent constraint when the requirement still applies.

Stop-and-check

• Email and Account Number are both minimal unique Customer identifiers. What are they before one is chosen? After Account Number is chosen?
• (Student_ID, Course_ID) is a pair of FKs identifying Registration. Which construction labels apply?
• Parent ID migrates to Child but is not part of Child PK. Identifying or non-identifying?
• HireDate is a Date but must be before today. Which domain form adds the missing restriction?

Answer check

• Both are candidate keys; chosen one becomes primary, the other alternate.
• Composite and compound; it can also be candidate/primary depending on function.
• Non-identifying.
• Rule-based domain, in addition to the Date type.

Source anchors: current Mastery Lab relationship/key/domain sections, pp. 128–135 and physical-key guidance.

← Lesson 1 · Next: Modeling Schemes →