1. The ER model
- A conceptual data model: describes the data requirements of an application in an easy‑to‑understand way, independent of how a DBMS organizes/manages data.
- Built on 3 concepts: Entity, Attribute, Relationship.
2. Entity & entity type
| Concept | Definition | Example |
|---|---|---|
| Entity | a specific object/thing of the mini‑world represented in the DB | employee John Smith, the Research department |
| Entity type | set of entities sharing the same attributes | EMPLOYEE (John Smith, Tony Adams…), DEPARTMENT (Research, Maintenance…) |
Symbol: rectangle [ EMPLOYEE ]
3. Attributes
- Properties describing an entity (EMPLOYEE: Name, SSN, Address, Sex, BirthDate).
- Each entity has a value per attribute: Name=‘John Smith’, SSN=‘123456789’, Sex=‘M’, BirthDate=‘09‑JAN‑55’…
- Each attribute has a value set / data type (domain): integer, string, date…
- Symbol: oval
( attribute )connected to its entity type.
3.1 Attribute types
| Type | Meaning | Example | Symbol |
|---|---|---|---|
| Simple (atomic) | single indivisible value | SSN, Sex | oval |
| Composite | made of components; may form a hierarchy (components themselves composite) | Address(Apt#, House#, Street, City, State, ZipCode, Country); Name(FirstName, MiddleName, LastName) | oval with component ovals attached to it |
| Multivalued | several values per entity | PreviousDiplomas | double oval |
| Derived | computed from other stored data | Age (from stored BirthDate) | dashed oval |
| Key | unique value for each entity | SSN | oval with underlined name |
3.2 Key attributes
- Every entity has a unique value → identifies it (SSN of EMPLOYEE).
- A key can be composite: VehicleTagNumber(Number, State) for CAR.
- An entity type can have more than one key: CAR → VIN and VehicleTagNumber (license plate).
3.3 Drawing rules
- Entity type → rectangle; attributes → ovals linked to it.
- Components of a composite attribute → linked to the composite’s oval.
- Key → underlined · Multivalued → double oval · Derived → dashed oval.
flowchart TB E[EMPLOYEE] SSN(["<u>SSN</u>"]) --- E Name([Name]) --- E Fname([Fname]) --- Name Minit([Minit]) --- Name Lname([Lname]) --- Name Bdate([Bdate]) --- E Age([age — derived, dashed]) -.- E Address([Address]) --- E Sex([Sex]) --- E Salary([Salary]) --- E
4. Relationships
- A relationship relates two or more distinct entities with a meaning (John Smith works on ProductX).
- Whenever an attribute of one entity refers to another entity type, a relationship exists.
- Relationship type = set of relationships of the same kind: WORKS_ON (EMPLOYEE–PROJECT), MANAGES (EMPLOYEE–DEPARTMENT).
- Symbol: diamond
< RELATIONSHIP >. - Several relationship types can exist between the same entity types, with different meanings and instances: MANAGES and WORKS_FOR both link EMPLOYEE and DEPARTMENT.
4.1 Degree
= number of participating entity types.
- Binary (degree 2): MANAGES, WORKS_ON.
- Ternary (degree 3): SUPPLY between SUPPLIER, PROJECT, PART.
flowchart LR S[SUPPLIER] --- R{SUPPLY} --- P[PROJECT] R --- PA[PART]
4.2 Recursive relationship
- Relates two entities of the same entity type, each in a different role.
- Role names are required.
- SUPERVISE: an EMPLOYEE (supervisor) supervises another EMPLOYEE (supervisee).
flowchart LR E[EMPLOYEE] -- supervisor --- S{SUPERVISE} E -- supervisee --- S
4.3 Cardinality ratios (binary relationships)
| Ratio | Meaning | Example |
|---|---|---|
| 1:N (one‑to‑many) | one instance ↔ zero or several of the other | PAINTER 1 — paints — N PAINTING (equivalently PAINTING–PAINTER is N:1) |
| N:M (many‑to‑many) | many ↔ many | STUDENT N — register — M COURSE |
| 1:1 (one‑to‑one) | one ↔ exactly one | MAYOR 1 — govern — 1 CITY |
Numbers are written on the lines near each entity.
4.4 Participation constraint & existence dependency
- Says whether an entity’s existence depends on being related to another entity.
- Total participation (existence dependency): every entity must participate. Rule: every employee must work for a department → EMPLOYEE’s participation in WORKS_FOR is total. → double line.
- Partial participation: some entities may not participate. Not every employee manages a department → EMPLOYEE in MANAGES is partial. → single line.
flowchart LR E[EMPLOYEE] === W{WORKS_FOR} --- D[DEPARTMENT] E --- M{MANAGES} === D
(=== = double line / total.)
4.5 (min, max) notation — alternative to ratio + participation
- Written on each participation of entity type E in relationship R: each entity of E participates in at least min and at most max instances of R.
- Default (no constraint): min = 0, max = n (no limit).
- Must satisfy min ≤ max, min ≥ 0, max ≥ 1.
- Derived from mini‑world constraints.
- min = 0 → partial participation; min > 0 → total participation.
- ⚠ Placed on the side of the entity it describes (opposite reading direction from 1:N ratios).
Examples:
| Rule | Notation |
|---|---|
| a department has exactly one manager | DEPARTMENT in MANAGES: (1,1) |
| an employee manages at most one department | EMPLOYEE in MANAGES: (0,1) |
| an employee works for exactly one department | EMPLOYEE in WORKS_FOR: (1,1) |
| a department has any number of employees (≥1) | DEPARTMENT in WORKS_FOR: (1,n) |
EMPLOYEE ──(1,1)── <WORKS_FOR> ──(1,n)── DEPARTMENT
EMPLOYEE ──(0,1)── <MANAGES> ──(1,1)── DEPARTMENT
4.6 Attributes of relationship types
- A relationship can have its own attributes, whose value depends on the combination of participating entities.
- WORKS_ON(EMPLOYEE, PROJECT) has HoursPerWeek: hours a given employee works on a given project.
flowchart LR E[EMPLOYEE] --- W{WORKS_ON} --- P[PROJECT] H([HoursPerWeek]) --- W
5. Weak entity type
- An entity type without a key attribute of its own. Symbol: double rectangle.
- Must participate in an identifying relationship (double diamond) with an owner / identifying entity type.
- Its entities are identified by:
- a partial key (dashed underline), and
- the related entity of the owner type.
- Participation of the weak entity in the identifying relationship is total.
Example: DEPENDENT identified by its Name (partial key) + the EMPLOYEE it belongs to, via DEPENDENT_OF.
flowchart LR E[EMPLOYEE] --- R{{"DEPENDENT_OF (double diamond)"}} === D[["DEPENDENT (double rectangle)"]] SSN(["<u>SSN</u>"]) --- E EN([Name]) --- E DN(["Name — partial key (dashed underline)"]) --- D BD([BirthDate]) --- D
6. Enhanced ER (EER) model
- Since the 1980s, more demanding applications (engineering, telecommunications…) needed richer modeling.
- EER = all ER concepts + :
- subclasses / superclasses
- specialization / generalization
- categories (UNION types)
- attribute & relationship inheritance
- Models applications more completely and accurately; brings in object‑oriented ideas (inheritance).
6.1 Superclass / subclass
- Meaningful subgroupings of an entity type’s entities = subclasses (subtypes).
- EMPLOYEE → SECRETARY, ENGINEER, TECHNICIAN (by job)
- EMPLOYEE → MANAGER (employees who manage)
- EMPLOYEE → SALARIED_EMPLOYEE, HOURLY_EMPLOYEE (by pay method)
- Link symbol: line with the subset sign ⊂ (“U”‑shaped cup) pointing toward the subclass.
- Also called an IS‑A relationship: a MANAGER IS‑A EMPLOYEE.
- A subclass entity inherits all attributes and all relationships of its superclass, and can add its own (specific attributes/relationships).
flowchart TB E[EMPLOYEE] -- "⊂ IS-A" --> M[MANAGER]
6.2 Specialization / generalization
- Specialization (top‑down): defining a set of subclasses of a superclass based on a distinguishing characteristic.
{SECRETARY, ENGINEER, TECHNICIAN} is a specialization of EMPLOYEE on Job_type. - Generalization (bottom‑up): the reverse — several classes with common features are generalized into a superclass; the originals become its subclasses.
- Drawn with a circle below the superclass, lines to each subclass with the ⊂ sign.
flowchart TB E[EMPLOYEE] --- d((d)) d -- ⊂ --> S[SECRETARY] d -- ⊂ --> T[TECHNICIAN] d -- ⊂ --> G[ENGINEER] TS([Typing_speed]) --- S TG([Tgrade]) --- T ET([Eng_type]) --- G N([Name: Fname, Minit, Lname]) --- E SS(["<u>Ssn</u>"]) --- E BD([Birth_date]) --- E AD([Address]) --- E JT([Job_type]) --- E
Here: disjoint (d), partial (single line) — an employee can be in at most one subclass and may be in none.
6.3 Constraints on specialization
Disjointness
| Meaning | Symbol in circle | |
|---|---|---|
| Disjoint | an entity belongs to at most one subclass | d |
| Overlapping | an entity may belong to several subclasses | o |
Completeness
| Meaning | Line superclass → circle | |
|---|---|---|
| Total | every superclass entity must be in some subclass | double line |
| Partial | an entity may belong to no subclass | single line |
→ 4 combinations: disjoint‑total · disjoint‑partial · overlapping‑total · overlapping‑partial.
Example overlapping, total: PART (Part_no, Description) → MANUFACTURED_PART (Manufacture_date, Batch_no, Drawing_no) and PURCHASED_PART (Supplier_name, List_price). Every part is manufactured and/or purchased; a part can be both.
flowchart TB P[PART] ===|"total (double line)"| o((o)) o -- ⊂ --> MP[MANUFACTURED_PART] o -- ⊂ --> PP[PURCHASED_PART] PN(["<u>Part_no</u>"]) --- P DE([Description]) --- P MD([Manufacture_date]) --- MP BN([Batch_no]) --- MP DN([Drawing_no]) --- MP SN([Supplier_name]) --- PP LP([List_price]) --- PP
6.4 Union type (category)
- A single subclass linked by one superclass/subclass relationship to more than one superclass.
- The superclasses are different entity types (unlike specialization, where there is one superclass).
- The category represents a subset of the union of its superclasses.
- A category member must exist in at least one of its superclasses (inherits from the one it belongs to — selective inheritance).
- Symbol: circle with ∪ (“U”).
Example: a vehicle OWNER can be a PERSON, a BANK, or a COMPANY.
flowchart TB PE[PERSON] --- U((∪)) B[BANK] --- U C[COMPANY] --- U U -- ⊂ --> O[OWNER] SSN(["<u>Ssn</u>"]) --- PE DL(["<u>Driver_license_no</u>"]) --- PE PN([Name]) --- PE PA([Address]) --- PE BN(["<u>Bname</u>"]) --- B BA([Baddress]) --- B CN(["<u>Cname</u>"]) --- C CA([Caddress]) --- C
7. Symbol cheat‑sheet
| Concept | Symbol |
|---|---|
| Entity type | rectangle |
| Weak entity type | double rectangle |
| Relationship type | diamond |
| Identifying relationship | double diamond |
| Attribute | oval |
| Key attribute | oval, name underlined |
| Partial key (weak entity) | oval, name dashed‑underlined |
| Multivalued attribute | double oval |
| Derived attribute | dashed oval |
| Composite attribute | oval with component ovals |
| Total participation | double line |
| Partial participation | single line |
| Cardinality | 1, N, M on lines — or (min,max) |
| Recursive relationship | role names on both lines |
| Subclass link (IS‑A) | line with ⊂ |
| Disjoint / overlapping specialization | circle with d / o |
| Total / partial specialization | double / single line superclass → circle |
| Union (category) | circle with ∪ |
8. Quick comparison: specialization vs union
| Specialization / generalization | Union (category) | |
|---|---|---|
| Superclasses | one | several, different types |
| Subclass members | subset of the superclass | subset of the union of superclasses |
| Inheritance | everything from the single superclass | only from the superclass the member actually belongs to |
| Circle symbol | d / o | ∪ |