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

ConceptDefinitionExample
Entitya specific object/thing of the mini‑world represented in the DBemployee John Smith, the Research department
Entity typeset of entities sharing the same attributesEMPLOYEE (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

TypeMeaningExampleSymbol
Simple (atomic)single indivisible valueSSN, Sexoval
Compositemade 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
Multivaluedseveral values per entityPreviousDiplomasdouble oval
Derivedcomputed from other stored dataAge (from stored BirthDate)dashed oval
Keyunique value for each entitySSNoval 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)

RatioMeaningExample
1:N (one‑to‑many)one instance ↔ zero or several of the otherPAINTER 1 — paints — N PAINTING (equivalently PAINTING–PAINTER is N:1)
N:M (many‑to‑many)many ↔ manySTUDENT N — register — M COURSE
1:1 (one‑to‑one)one ↔ exactly oneMAYOR 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:

RuleNotation
a department has exactly one managerDEPARTMENT in MANAGES: (1,1)
an employee manages at most one departmentEMPLOYEE in MANAGES: (0,1)
an employee works for exactly one departmentEMPLOYEE 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:
    1. a partial key (dashed underline), and
    2. 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

MeaningSymbol in circle
Disjointan entity belongs to at most one subclassd
Overlappingan entity may belong to several subclasseso

Completeness

MeaningLine superclass → circle
Totalevery superclass entity must be in some subclassdouble line
Partialan entity may belong to no subclasssingle 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

ConceptSymbol
Entity typerectangle
Weak entity typedouble rectangle
Relationship typediamond
Identifying relationshipdouble diamond
Attributeoval
Key attributeoval, name underlined
Partial key (weak entity)oval, name dashed‑underlined
Multivalued attributedouble oval
Derived attributedashed oval
Composite attributeoval with component ovals
Total participationdouble line
Partial participationsingle line
Cardinality1, N, M on lines — or (min,max)
Recursive relationshiprole names on both lines
Subclass link (IS‑A)line with ⊂
Disjoint / overlapping specializationcircle with d / o
Total / partial specializationdouble / single line superclass → circle
Union (category)circle with ∪

8. Quick comparison: specialization vs union

Specialization / generalizationUnion (category)
Superclassesoneseveral, different types
Subclass memberssubset of the superclasssubset of the union of superclasses
Inheritanceeverything from the single superclassonly from the superclass the member actually belongs to
Circle symbold / o∪