Relational Model Notation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

// CROW'S FOOT NOTATION EXAMPLES
// ================================
 
// One-to-Many: Department has many Employees
// ------------------------------------------
DEPARTMENT ─────────────┤ │──○<──── EMPLOYEE
           │              │         │
   One Department         │    Zero or more Employees
   (mandatory)            │    (can be empty dept)
 
Read as: "One DEPARTMENT has zero or more EMPLOYEE"
         "Each EMPLOYEE belongs to exactly one DEPARTMENT"
 
 
// One-to-One: Employee has one Parking Space (optional)
// -----------------------------------------------------
EMPLOYEE ───────────────○│─────────○│──── PARKING_SPACE
         One Employee         Zero or One parking space
         (may not have one)   (may not be assigned)
 
Read as: "One EMPLOYEE has zero or one PARKING_SPACE"
         "One PARKING_SPACE is assigned to zero or one EMPLOYEE"
 
 
// Many-to-Many (via junction): Student enrolls in Courses
// -------------------------------------------------------
STUDENT ─────┤│──○<─── ENROLLMENT ───○<──│├───── COURSE
   │                    │ │                   │
   One Student    Many enrollments      One Course
                  (junction table)
 
Read as: "One STUDENT has many ENROLLMENT"
         "One COURSE has many ENROLLMENT"
         "Each ENROLLMENT links one STUDENT to one COURSE"
 
 
// Self-Referencing: Employee reports to Manager
// ----------------------------------------------
            ┌───────────────────┐
            │                   │
            ○<                  │
            │                   │
    EMPLOYEE ───────────────────┘
      │ │
      │ └─ emp_id (PK)
      └─── manager_id (FK → emp_id)
 
Read as: "One EMPLOYEE manages zero or more EMPLOYEE"
         "Each EMPLOYEE reports to zero or one EMPLOYEE"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

// UML CLASS DIAGRAM NOTATION FOR DATA MODELING
// =============================================
 
// Association with Multiplicity
// ─────────────────────────────
 
DEPARTMENT ────────────── 1..* EMPLOYEE
     1        "employs"
 
// Reads as: One DEPARTMENT employs 1 or more EMPLOYEE
// Each EMPLOYEE works for exactly one DEPARTMENT
 
// UML Multiplicities:
// 1     = exactly one
// 0..1  = zero or one
// *     = zero or more  
// 1..*  = one or more
// m..n  = from m to n (e.g., 2..5)
 
// Complete Association Example:
┌──────────────┐         employs           ┌──────────────┐
│ <<table>>    │ 1                    1..* │ <<table>>    │
│ DEPARTMENT   │─────────────────────────▶│ EMPLOYEE     │
├──────────────┤          │                ├──────────────┤
│ +dept_id {PK}│          │                │ +emp_id {PK} │
│ +name        │          │                │ +name        │
│ +budget      │          │                │ +dept_id {FK}│
└──────────────┘          │                │ +salary      │
                          │                └──────────────┘
                          │
              Association name with direction
 
// Aggregation (hollow diamond): "has-a" weak relationship
DEPARTMENT ◇──────────── EMPLOYEE
// Department has employees, but employees can exist without department
 
// Composition (filled diamond): "owns" strong relationship  
ORDER ◆──────────── ORDER_LINE
// Order owns order lines; lines cannot exist without order
 
// Generalization (inheritance):
        ┌──────────────┐
        │   PERSON     │
        └──────┬───────┘
              /│             / │   ┌──────────┐ │ ┌──────────┐
  │ EMPLOYEE │ │ │ CUSTOMER │
  └──────────┘ │ └──────────┘
               │
    EMPLOYEE and CUSTOMER inherit from PERSON

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

// IDEF1X NOTATION ELEMENTS
// =========================
 
// Entity Box Structure:
// ┌─────────────────┐
// │   ENTITY_NAME   │ ← Entity name
// ├─────────────────┤ ← Separator line
// │ key_attr1       │ ← Key area (above separator)
// │ key_attr2       │    Attributes forming PK
// ├─────────────────┤ ← Another separator  
// │ non_key_attr1   │ ← Non-key area (below separator)
// │ non_key_attr2   │    Regular attributes
// └─────────────────┘
 
// Independent Entity (own primary key):
┌─────────────────┐
│   DEPARTMENT    │
├─────────────────┤
│ dept_id         │
├─────────────────┤
│ name            │
│ budget          │
│ location        │
└─────────────────┘
 
// Dependent Entity (PK includes foreign key):
╭─────────────────╮
│   DEPENDENT     │  ← Rounded corners indicate dependence
├─────────────────┤
│ emp_id (FK)     │  ← Part of primary key
│ dependent_num   │  ← Rest of primary key
├─────────────────┤
│ name            │
│ relationship    │
│ birth_date      │
╰─────────────────╯
 
// Relationship Types:
// ─────────────────────
// Identifying (solid line): Child PK includes parent PK
EMPLOYEE ─────────────● DEPENDENT
// The dot (●) indicates cardinality (filled = one, diamond = zero)
 
// Non-Identifying (dashed line): Child PK is independent  
DEPARTMENT - - - - - -◇ EMPLOYEE
// Diamond (◇) indicates optional (nullable FK)
 
// Cardinality Notation:
// P = One or more (positive)
// Z = Zero or more
// 1 = Exactly one
// n = Specific number
 
DEPARTMENT ─────────(P)─────── EMPLOYEE
// One department has one or more employees

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

// DBML (Database Markup Language)
// Used by dbdiagram.io and dbdocs.io
 
Table users {
  id integer [pk, increment]
  username varchar(50) [unique, not null]
  email varchar(255) [unique, not null]
  created_at timestamp [default: 'now()']
}
 
Table posts {
  id integer [pk, increment]
  title varchar(200) [not null]
  body text
  user_id integer [ref: > users.id]
  status post_status [default: 'draft']
  published_at timestamp
}
 
Table comments {
  id integer [pk, increment]
  body text [not null]
  post_id integer [ref: > posts.id]
  user_id integer [ref: > users.id]
  created_at timestamp
}
 
Enum post_status {
  draft
  published
  archived
}
 
// Relationships are inferred from ref: declarations
// > means many-to-one (many comments to one post)
// < means one-to-many
// - means one-to-one