ER model

Entity sets and entity instances

Definition. An entity is a distinguishable real-world object; an entity set groups objects of the same kind. An entity set is described by attributes and constraints. In a relational implementation, entity instances usually become tuples.

Example. A Student entity may have StudentID, name, and date of birth.

Exam point. A name is not automatically a key; uniqueness must be stated or implied by a constraint.

Solved examples

Example 1 — entity or attribute. In a university model, classify Student, Course, and a student's phone number.

1. Student represents an independently distinguishable object type: entity set.
2. Course also represents an object type: entity set.
3. Phone number describes a student and need not be an independent object: attribute, unless it has its own relationships or identity requirements.
4. Student and Course are entities; phone number is usually an attribute.

Example 2 — composite entity. A person has a postal address with street, city, and postal code.

1. If the address is only described as components, model it as a composite attribute.
2. If addresses need independent identity, history, or relationships, model Address as an entity.
3. Model choice follows required semantics, not the number of fields alone.

Attributes and value structure

Definition. A simple attribute is treated as indivisible; a composite attribute has components; a multivalued attribute may have several values for one entity; a derived attribute is computed from stored data. Attribute domains restrict allowed values.

Example. Age can be derived from birth date and the current date.

Exam point. Derived values need not be stored, but may be materialized for performance with consistency rules.

Solved examples

Example 1 — multivalued attribute mapping. A Student can have several email addresses. How should a relational mapping represent them?

1. A single Student row should not store an arbitrary list in one atomic field under first normal form.
2. Create StudentEmail(StudentID, Email), one row per address.
3. A natural key may be (StudentID,Email), with StudentID a foreign key.
4. The multivalued attribute becomes a separate relation.

Example 2 — derived attribute. A database stores birth date but displays age.

1. Age depends on birth date and the date of evaluation.
2. Its value changes over time without the birth date changing.
3. Age is derived; storing it risks becoming stale.

Keys and identifying attributes

Definition. A superkey uniquely identifies entity instances; a candidate key is a minimal superkey; a primary key is the selected candidate key. A composite key has multiple attributes. A partial key identifies a weak entity relative to its owner.

Example. If StudentID and email each uniquely identify a student, both may be candidate keys.

Exam point. Minimality means removing any attribute destroys uniqueness; a superkey may contain unnecessary attributes.

Solved examples

Example 1 — candidate keys. Relation-like entity attributes are (A,B,C), and constraints state A uniquely identifies instances and B,C together uniquely identify them.

1. {A} is a superkey and minimal, hence a candidate key.
2. {B,C} is also a minimal superkey.
3. {A,B} is a superkey but not minimal because A alone suffices.
4. Candidate keys are {A} and {B,C}.

Example 2 — primary-key choice. A registration entity is identified by StudentID and CourseID together, with multiple terms allowed.

1. Pair (StudentID,CourseID) is insufficient if a student may retake the course.
2. Include Term or AttemptNo to distinguish registrations.
3. A suitable key is (StudentID,CourseID,Term) if that combination is unique.

Relationships, degree, and roles

Definition. A relationship set associates entities from one or more entity sets. Its degree is the number of participating entity sets: unary, binary, ternary, and so on. Role names distinguish the meanings of repeated participation by the same entity set.

Example. Employee supervises Employee is unary with roles supervisor and subordinate.

Exam point. A ternary relationship may express constraints that cannot be recovered from three binary projections.

Solved examples

Example 1 — relationship degree. Supplier supplies Part to Project.

1. A relationship instance associates one supplier, one part, and one project.
2. Three entity sets participate.
3. It is a ternary relationship.

Example 2 — role labels. Course has prerequisite Course.

1. The same entity set participates twice.
2. One role is dependent course; the other is prerequisite course.
3. Role names prevent the two ends from being confused.

Cardinality constraints

Definition. Cardinality specifies how many entities may participate in a relationship. In a binary relationship, 1:1, 1:N, and M:N constrain maximum participation; min-max notation can separately state lower and upper bounds for each entity role.

Example. Each department has at most one manager; a manager may manage at most one department: 1:1.

Exam point. Cardinality is about permitted relationship instances, not the number currently stored.

Solved examples

Example 1 — one-to-many. Each employee belongs to one department; a department may have many employees.

1. From Employee to Department, maximum is 1.
2. From Department to Employee, maximum is many.
3. The relationship is many-employees-to-one-department (N:1), equivalently 1:N from Department's perspective.

Example 2 — min-max participation. Every order must have one or more line items; each line item belongs to exactly one order.

1. Order participation has minimum 1 and maximum many.
2. LineItem participation has minimum 1 and maximum 1.
3. Bounds: Order (1,N), LineItem (1,1).

Participation constraints

Definition. Total participation requires every entity instance in an entity set to participate in at least one relationship instance. Partial participation allows entities with no such relationship. Total participation is a minimum-cardinality constraint, distinct from maximum cardinality.

Example. If every employee must work for a department, Employee has total participation in WorksFor.

Exam point. A 1:N relationship can still have partial participation on either side.

Solved examples

Example — minimum versus maximum. Every employee must have a department; a department may temporarily have no employees.

1. Employee minimum participation is 1, maximum 1.
2. Department minimum is 0, maximum many.
3. Employee participation is total; Department participation is partial.