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
50
51

// ================================================
// FORMAL RELATION SCHEMA NOTATION EXAMPLES
// ================================================
 
// Basic Schema Notation with Explicit Domains
// Syntax: R(A₁: D₁, A₂: D₂, ..., Aₙ: Dₙ)
 
EMPLOYEE(
    emp_id: INTEGER,
    first_name: VARCHAR(50),
    last_name: VARCHAR(50),
    birth_date: DATE,
    salary: DECIMAL(10,2),
    dept_id: INTEGER
)
 
DEPARTMENT(
    dept_id: INTEGER,
    dept_name: VARCHAR(100),
    location: VARCHAR(100),
    budget: DECIMAL(12,2)
)
 
// Simplified Schema Notation (domains implicit)
// Syntax: R(A₁, A₂, ..., Aₙ)
 
EMPLOYEE(emp_id, first_name, last_name, birth_date, salary, dept_id)
DEPARTMENT(dept_id, dept_name, location, budget)
 
// Set-Theoretic Definition of a Relation
// r(R) ⊆ dom(A₁) × dom(A₂) × ... × dom(Aₙ)
 
r(EMPLOYEE) ⊆ INTEGER × VARCHAR(50) × VARCHAR(50) × DATE × DECIMAL(10,2) × INTEGER
 
// Tuple Notation
// t = (v₁, v₂, ..., vₙ) where vᵢ ∈ dom(Aᵢ)
 
t₁ = (101, 'Alice', 'Johnson', '1985-03-15', 75000.00, 10)
t₂ = (102, 'Bob', 'Smith', '1990-07-22', 62000.00, 20)
 
// Attribute Access Notation
// t[Aᵢ] or t.Aᵢ denotes the value of attribute Aᵢ in tuple t
 
t₁[first_name] = 'Alice'
t₁.salary = 75000.00
t₁[emp_id] = 101
 
// Multiple Attribute Access (Projection on Tuple)
// t[{A₁, A₂}] returns subtuple with only specified attributes
 
t₁[{first_name, last_name}] = ('Alice', 'Johnson')

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

// ================================================
// TEXTUAL SCHEMA NOTATION STYLES
// ================================================
 
// Style 1: Underline for PK (common in academic contexts)
// ------------------------------------------------
STUDENT(student_id̲, first_name, last_name, email, enrollment_date, major_id)
         ̲̲̲̲̲̲̲̲̲̲̲        
COURSE(course_id̲, course_name, credits, department)
 
ENROLLMENT(student_id̲, course_id̲, semester̲, grade)
           ̲̲̲̲̲̲̲̲̲̲         ̲̲̲̲̲̲̲̲̲̲   ̲̲̲̲̲̲̲̲
 
// Style 2: Asterisk for PK, (FK) for Foreign Keys
// ------------------------------------------------
STUDENT(*student_id, first_name, last_name, email, enrollment_date, major_id(FK))
MAJOR(*major_id, major_name, department_id(FK))
DEPARTMENT(*department_id, dept_name, building, budget)
 
// Style 3: Explicit PK/FK Markers
// ------------------------------------------------
EMPLOYEE(
    emp_id          PK,
    first_name,
    last_name,
    hire_date,
    salary,
    manager_id      FK → EMPLOYEE(emp_id),    -- Self-referencing
    department_id   FK → DEPARTMENT(dept_id)
)
 
// Style 4: Compact with Domain Types
// ------------------------------------------------
PRODUCT(
    product_id: INT [PK],
    name: VARCHAR(100),
    description: TEXT [NULL],
    price: DECIMAL(10,2),
    category_id: INT [FK],
    stock_quantity: INT [DEFAULT 0]
)
 
// Style 5: Full Specification with Constraints
// ------------------------------------------------
ORDER(
    order_id        INT             PRIMARY KEY,
    customer_id     INT             FOREIGN KEY → CUSTOMER(customer_id),
    order_date      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status          ENUM            CHECK ('pending','processing','shipped','delivered'),
    total_amount    DECIMAL(12,2)   CHECK (total_amount >= 0),
    shipping_addr   VARCHAR(500)    NULLABLE
)
 
// Style 6: Database Schema Overview Document
// ================================================
// E-COMMERCE DATABASE SCHEMA
// Version: 2.3
// Last Updated: 2024-01-15
// ================================================
 
// Core Entities
// -------------
CUSTOMER(*customer_id, email, password_hash, first_name, last_name, 
         created_at, last_login)
 
PRODUCT(*product_id, sku, name, description?, price, weight?, 
        category_id(FK), vendor_id(FK), is_active)
 
// Relationship Tables
// -------------------
ORDER(*order_id, customer_id(FK), order_date, status, total_amount)
ORDER_ITEM(*order_id(FK,PK), *line_number(PK), product_id(FK), 
           quantity, unit_price, discount)
 
// Reference Tables
// ----------------
CATEGORY(*category_id, name, parent_category_id?(FK → CATEGORY))
VENDOR(*vendor_id, company_name, contact_email, rating)

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

// ================================================
// DEGREE AND CARDINALITY NOTATION
// ================================================
 
// Schema Definition
EMPLOYEE(emp_id, name, dept_id, salary, hire_date)
 
// Degree (number of attributes)
deg(EMPLOYEE) = 5
|EMPLOYEE| = 5
EMPLOYEE is a 5-ary (quinary) relation
 
// Attribute Set Notation
attr(EMPLOYEE) = {emp_id, name, dept_id, salary, hire_date}
 
// Instance with Specific Cardinality
Let r(EMPLOYEE) be an instance with these tuples:
r = {
    (101, 'Alice', 10, 75000, '2020-01-15'),
    (102, 'Bob', 20, 62000, '2019-07-22'),
    (103, 'Carol', 10, 81000, '2018-03-10')
}
 
// Cardinality (number of tuples)
|r(EMPLOYEE)| = 3
card(r) = 3
 
// After INSERT operation
INSERT INTO r: (104, 'David', 30, 55000, '2023-09-01')
|r(EMPLOYEE)| = 4  // Cardinality increased by 1
 
// After DELETE operation  
DELETE FROM r WHERE emp_id = 102
|r(EMPLOYEE)| = 3  // Cardinality decreased by 1
 
// Degree remains constant throughout
deg(EMPLOYEE) = 5  // Always 5, regardless of operations on instances
 
// Empty Relation (valid state)
Let s(EMPLOYEE) = ∅  (empty set)
|s| = 0              // Zero cardinality is valid
deg(EMPLOYEE) = 5    // Degree still defined by schema
 
// Domain Cardinality Relationship
// Maximum possible cardinality = |dom(A₁)| × |dom(A₂)| × ... × |dom(Aₙ)|
// For EMPLOYEE with typical domains:
// |INTEGER| × |VARCHAR| × |INTEGER| × |DECIMAL| × |DATE|
// This is astronomically large, so practical cardinality is always much less

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

// ================================================
// DOMAIN DICTIONARY NOTATION
// ================================================
 
// DOMAIN DEFINITIONS CATALOG
// ==========================
 
// Identity Domains
DOMAIN PersonId     = BIGINT UNSIGNED NOT NULL
DOMAIN ProductId    = BIGINT UNSIGNED NOT NULL  
DOMAIN OrderId      = BIGINT UNSIGNED NOT NULL
DOMAIN UUID         = CHAR(36) PATTERN '^[0-9a-f]{8}-[0-9a-f]{4}-...'
 
// String Domains
DOMAIN PersonName   = VARCHAR(100) NOT NULL
DOMAIN Email        = VARCHAR(255) PATTERN '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$'
DOMAIN PhoneNumber  = VARCHAR(20) PATTERN '^\+?[0-9\-\s]+$'
DOMAIN PostalCode   = VARCHAR(20)
DOMAIN URL          = VARCHAR(2048) PATTERN '^https?://.*'
 
// Numeric Domains
DOMAIN MonetaryAmount = DECIMAL(14,2) CHECK (value >= 0)
DOMAIN Percentage     = DECIMAL(5,2) CHECK (value >= 0 AND value <= 100)
DOMAIN Quantity       = INTEGER CHECK (value >= 0)
DOMAIN Rating         = DECIMAL(2,1) CHECK (value >= 0 AND value <= 5)
 
// Temporal Domains
DOMAIN Timestamp      = TIMESTAMP WITH TIME ZONE
DOMAIN FutureDate     = DATE CHECK (value > CURRENT_DATE)
DOMAIN BirthDate      = DATE CHECK (value <= CURRENT_DATE AND value >= '1900-01-01')
 
// Enumeration Domains
DOMAIN OrderStatus    = ENUM('pending', 'confirmed', 'processing', 
                             'shipped', 'delivered', 'cancelled', 'refunded')
DOMAIN PaymentMethod  = ENUM('credit_card', 'debit_card', 'bank_transfer', 
                             'paypal', 'crypto', 'cash')
DOMAIN UserRole       = ENUM('admin', 'manager', 'user', 'guest')
 
// Composite/Structured Domains (for advanced systems)
DOMAIN Address = STRUCT(
    street: VARCHAR(200),
    city: VARCHAR(100),
    state: VARCHAR(100),
    postal_code: PostalCode,
    country: CountryCode
)
 
// ================================================
// SCHEMA USING DOMAIN REFERENCES
// ================================================
 
CUSTOMER(
    customer_id:    PersonId        [PK],
    email:          Email           [UNIQUE],
    name:           PersonName,
    phone:          PhoneNumber     [NULL],
    created_at:     Timestamp
)
 
PRODUCT(
    product_id:     ProductId       [PK],
    name:           VARCHAR(200),
    price:          MonetaryAmount,
    rating_avg:     Rating          [NULL],
    stock:          Quantity
)
 
ORDER(
    order_id:       OrderId         [PK],
    customer_id:    PersonId        [FK → CUSTOMER],
    status:         OrderStatus,
    total:          MonetaryAmount,
    placed_at:      Timestamp
)

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
56
57
58
59
60
61
62
63

// ================================================
// SCHEMA COMPARISON NOTATION
// ================================================
 
// Define two schemas
R = EMPLOYEE(emp_id, name, dept_id, salary, hire_date)
S = CONTRACTOR(contractor_id, name, agency_id, hourly_rate, start_date)
 
// Attribute sets
attr(R) = {emp_id, name, dept_id, salary, hire_date}
attr(S) = {contractor_id, name, agency_id, hourly_rate, start_date}
 
// Set operations on schemas
attr(R) ∪ attr(S) = {emp_id, name, dept_id, salary, hire_date, 
                     contractor_id, agency_id, hourly_rate, start_date}
 
attr(R) ∩ attr(S) = {name}  // Common attributes
 
attr(R) - attr(S) = {emp_id, dept_id, salary, hire_date}
 
attr(S) - attr(R) = {contractor_id, agency_id, hourly_rate, start_date}
 
// Degree comparison
deg(R) = 5
deg(S) = 5
deg(R) = deg(S)  // Same degree, but not equivalent schemas
 
// Union compatibility check
// For R ≈ᵤ S (union compatible):
// 1. deg(R) = deg(S)  ✓ (both have 5 attributes)
// 2. Corresponding domains must match
//    R.attr₁ ↔ S.attr₁: INTEGER ↔ INTEGER  ✓
//    R.attr₂ ↔ S.attr₂: VARCHAR ↔ VARCHAR  ✓
//    R.attr₃ ↔ S.attr₃: INTEGER ↔ INTEGER  ✓
//    R.attr₄ ↔ S.attr₄: DECIMAL ↔ DECIMAL  ✓
//    R.attr₅ ↔ S.attr₅: DATE ↔ DATE        ✓
// Result: R and S are union-compatible (syntactically)
 
// ================================================
// SCHEMA EVOLUTION NOTATION
// ================================================
 
// Original schema version
V1: CUSTOMER(customer_id, name, address, phone)
 
// Schema modifications - notation for changes
V2: CUSTOMER(customer_id, first_name, last_name, email, address, phone)
    Changes: 
    - RENAME: name → (first_name, last_name)  // Decomposition
    - ADD: email
    
// Delta notation
Δ(V1 → V2) = {
    DROP: {name},
    ADD: {first_name, last_name, email},
    MODIFY: ∅,
    RENAME: ∅
}
 
// Schema diff (what changed)
attr(V2) - attr(V1) = {first_name, last_name, email}
attr(V1) - attr(V2) = {name}
deg(V2) - deg(V1) = 6 - 4 = +2

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

-- ================================================
-- SCHEMA NOTATION TO SQL TRANSLATION
-- ================================================
 
-- Textual Notation:
-- DEPARTMENT(*dept_id: INT, name: VARCHAR(100), budget: DECIMAL(12,2), 
--            location: VARCHAR(200)??, manager_id: INT? → EMPLOYEE)
 
-- SQL Implementation:
CREATE TABLE department (
    dept_id     INTEGER         NOT NULL,
    name        VARCHAR(100)    NOT NULL,
    budget      DECIMAL(12,2)   NOT NULL,
    location    VARCHAR(200),   -- Nullable (? in notation)
    manager_id  INTEGER,        -- Nullable FK
    
    CONSTRAINT pk_department PRIMARY KEY (dept_id),
    CONSTRAINT fk_dept_manager FOREIGN KEY (manager_id) 
        REFERENCES employee(emp_id) ON DELETE SET NULL
);
 
-- ================================================
-- Textual Notation:
-- EMPLOYEE(*emp_id: INT, first_name: VARCHAR(50), last_name: VARCHAR(50),
--          email: Email, hire_date: DATE, salary: DECIMAL(10,2) CHECK >=0,
--          dept_id: INT → DEPARTMENT)
 
-- SQL Implementation:
CREATE TABLE employee (
    emp_id      INTEGER         NOT NULL,
    first_name  VARCHAR(50)     NOT NULL,
    last_name   VARCHAR(50)     NOT NULL,
    email       VARCHAR(255)    NOT NULL,
    hire_date   DATE            NOT NULL DEFAULT CURRENT_DATE,
    salary      DECIMAL(10,2)   NOT NULL,
    dept_id     INTEGER         NOT NULL,
    
    CONSTRAINT pk_employee PRIMARY KEY (emp_id),
    CONSTRAINT uq_employee_email UNIQUE (email),
    CONSTRAINT chk_salary CHECK (salary >= 0),
    CONSTRAINT chk_email CHECK (email ~ '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$'),
    CONSTRAINT fk_employee_dept FOREIGN KEY (dept_id) 
        REFERENCES department(dept_id) ON DELETE RESTRICT ON UPDATE CASCADE
);
 
-- ================================================
-- Textual Notation (Composite Key):
-- ENROLLMENT(*student_id → STUDENT, *course_id → COURSE, *semester: VARCHAR(20), 
--            grade: CHAR(2)?, enrollment_date: DATE)
 
-- SQL Implementation:
CREATE TABLE enrollment (
    student_id      INTEGER         NOT NULL,
    course_id       INTEGER         NOT NULL,
    semester        VARCHAR(20)     NOT NULL,
    grade           CHAR(2),        -- Nullable (grade assigned later)
    enrollment_date DATE            NOT NULL DEFAULT CURRENT_DATE,
    
    -- Composite primary key
    CONSTRAINT pk_enrollment PRIMARY KEY (student_id, course_id, semester),
    
    -- Foreign keys
    CONSTRAINT fk_enrollment_student FOREIGN KEY (student_id)
        REFERENCES student(student_id) ON DELETE CASCADE,
    CONSTRAINT fk_enrollment_course FOREIGN KEY (course_id)
        REFERENCES course(course_id) ON DELETE RESTRICT,
        
    -- Domain constraint on grade
    CONSTRAINT chk_grade CHECK (grade IN ('A+', 'A', 'A-', 'B+', 'B', 'B-',
                                          'C+', 'C', 'C-', 'D+', 'D', 'F', 'W'))
);
 
-- ================================================
-- MATHEMATICAL NOTATION TO SQL
-- ================================================
 
-- Mathematical: R(A₁: D₁, A₂: D₂) with constraint A₁ ∈ dom(A₁), A₂ ∈ dom(A₂)
-- SQL: CREATE TABLE with column definitions and CHECK constraints
 
-- Mathematical: t[A] denotes value of attribute A in tuple t
-- SQL: SELECT A FROM R (retrieves values of column A from rows)
 
-- Mathematical: |r| = cardinality
-- SQL: SELECT COUNT(*) FROM R
 
-- Mathematical: deg(R) = n
-- SQL: SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'R'