Logical Design: From Concepts to Schemas

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

=====================================================
RELATIONAL SCHEMA NOTATION EXAMPLES
=====================================================
 
--------------------------------------------------
NOTATION STYLE 1: Inline Markers
--------------------------------------------------
Employee(EmployeeID [PK], FirstName, LastName, 
         Email [UK], DepartmentID [FK → Department.DepartmentID])
Department(DepartmentID [PK], DepartmentName [UK], Budget)
Project(ProjectID [PK], ProjectName, StartDate, EndDate)
EmployeeProject(EmployeeID [PK,FK], ProjectID [PK,FK], 
                HoursPerWeek, Role)
 
Legend: PK = Primary Key, FK = Foreign Key, UK = Unique Key
 
--------------------------------------------------
NOTATION STYLE 2: Separate Constraint Listing
--------------------------------------------------
Employee(EmployeeID, FirstName, LastName, Email, 
         Salary, HireDate, DepartmentID)
 
Constraints:
  PK: Employee(EmployeeID)
  UK: Employee(Email)
  FK: Employee(DepartmentID) → Department(DepartmentID)
  NOT NULL: FirstName, LastName, Email, HireDate, DepartmentID
  CHECK: Salary >= 0
 
--------------------------------------------------
NOTATION STYLE 3: Textual Description (Formal)
--------------------------------------------------
Schema: COMPANY_DB
 
R1 = EMPLOYEE(ID: INTEGER, NAME: VARCHAR(100), 
              DEPT_ID: INTEGER, SALARY: DECIMAL(10,2))
    PRIMARY KEY: {ID}
    FOREIGN KEY: {DEPT_ID} REFERENCES DEPARTMENT
 
R2 = DEPARTMENT(ID: INTEGER, NAME: VARCHAR(100), 
                BUDGET: DECIMAL(15,2))
    PRIMARY KEY: {ID}
    UNIQUE: {NAME}
 
R3 = WORKS_ON(EMP_ID: INTEGER, PROJ_ID: INTEGER, 
              HOURS: DECIMAL(4,1))
    PRIMARY KEY: {EMP_ID, PROJ_ID}
    FOREIGN KEY: {EMP_ID} REFERENCES EMPLOYEE
    FOREIGN KEY: {PROJ_ID} REFERENCES PROJECT
 
--------------------------------------------------
NOTATION STYLE 4: Markdown-Friendly
--------------------------------------------------
## Employee
| Column       | Type          | Constraints          |
|--------------|---------------|----------------------|
| **EmployeeID** | INT         | PK                   |
| FirstName    | VARCHAR(50)   | NOT NULL             |
| LastName     | VARCHAR(50)   | NOT NULL             |
| Email        | VARCHAR(100)  | UNIQUE, NOT NULL     |
| DepartmentID | INT           | FK → Department(ID)  |
 
--------------------------------------------------
NOTATION STYLE 5: CompSci Textbook (Elmasri/Navathe)
--------------------------------------------------
EMPLOYEE
  EmployeeID        INTEGER         NOT NULL
  FirstName         VARCHAR(50)     NOT NULL
  LastName          VARCHAR(50)     NOT NULL
  Email             VARCHAR(100)    NOT NULL, UNIQUE
  Salary            DECIMAL(10,2)   
  DepartmentID      INTEGER         NOT NULL
 
  PRIMARY KEY (EmployeeID)
  FOREIGN KEY (DepartmentID) REFERENCES DEPARTMENT(DepartmentID)
    ON DELETE SET NULL
    ON UPDATE CASCADE

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

-- ==================================================
-- DOMAIN DEFINITIONS IN SQL
-- ==================================================
 
-- Standard SQL supports CREATE DOMAIN (not all DBs implement)
-- PostgreSQL example:
 
-- Define semantic domains
CREATE DOMAIN EmailAddress AS VARCHAR(254)
    CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
CREATE DOMAIN PositiveMoney AS DECIMAL(19,4)
    CHECK (VALUE >= 0);
 
CREATE DOMAIN PhoneNumber AS VARCHAR(20)
    CHECK (VALUE ~ '^\+?[0-9\-\s\(\)]+$');
 
CREATE DOMAIN Percentage AS DECIMAL(5,2)
    CHECK (VALUE >= 0 AND VALUE <= 100);
 
-- Using domains in table definition
CREATE TABLE Customer (
    CustomerID      SERIAL          PRIMARY KEY,
    Email           EmailAddress    NOT NULL UNIQUE,
    Phone           PhoneNumber,
    LoyaltyPoints   INTEGER         DEFAULT 0 CHECK (LoyaltyPoints >= 0),
    DiscountRate    Percentage      DEFAULT 0.00
);
 
-- For databases without CREATE DOMAIN, use CHECK constraints
CREATE TABLE Product (
    ProductID       INT             PRIMARY KEY,
    ProductName     VARCHAR(200)    NOT NULL,
    SKU             CHAR(12)        NOT NULL UNIQUE,
    
    -- Inline domain constraints
    Price           DECIMAL(10,2)   NOT NULL CHECK (Price >= 0),
    Weight          DECIMAL(8,3)    CHECK (Weight > 0),
    StockLevel      INT             NOT NULL DEFAULT 0 CHECK (StockLevel >= 0),
    
    -- Enumerated domain as CHECK
    Category        VARCHAR(50)     NOT NULL 
                    CHECK (Category IN ('Electronics', 'Clothing', 
                                        'Food', 'Furniture', 'Other'))
);
 
-- Enum type (PostgreSQL)
CREATE TYPE OrderStatus AS ENUM (
    'pending', 'confirmed', 'processing', 
    'shipped', 'delivered', 'cancelled'
);
 
CREATE TABLE Orders (
    OrderID         SERIAL          PRIMARY KEY,
    CustomerID      INT             NOT NULL REFERENCES Customer(CustomerID),
    Status          OrderStatus     NOT NULL DEFAULT 'pending',
    OrderDate       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    TotalAmount     PositiveMoney   NOT NULL
);

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
88
89
90
91
92
93
94
95
96
97
98
99

-- ==================================================
-- COMPREHENSIVE KEY SPECIFICATION
-- ==================================================
 
-- EXAMPLE 1: Multiple Candidate Keys
-- -------------------------------------------------
-- A book can be uniquely identified by ISBN or by 
-- (Title, Author, Edition) combination
 
CREATE TABLE Book (
    BookID          SERIAL,                     -- Surrogate key
    ISBN            CHAR(13)        NOT NULL,   -- Candidate key 1
    Title           VARCHAR(500)    NOT NULL,   -- \
    Author          VARCHAR(200)    NOT NULL,   -- | Candidate key 2
    Edition         INT             DEFAULT 1,  -- /
    Publisher       VARCHAR(200),
    PublishYear     INT,
    
    -- Primary Key (chosen surrogate for efficiency)
    PRIMARY KEY (BookID),
    
    -- Alternate Keys (other candidate keys)
    UNIQUE (ISBN),
    UNIQUE (Title, Author, Edition)
);
 
-- EXAMPLE 2: Composite Primary Key
-- -------------------------------------------------
CREATE TABLE Enrollment (
    StudentID       INT             NOT NULL,
    CourseID        INT             NOT NULL,
    Semester        CHAR(6)         NOT NULL,  -- e.g., '2024SP'
    Grade           CHAR(2),
    EnrollmentDate  DATE            NOT NULL,
    
    -- Composite Primary Key
    PRIMARY KEY (StudentID, CourseID, Semester),
    
    -- Foreign Keys
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
        ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
        ON DELETE RESTRICT
);
 
-- EXAMPLE 3: Self-Referencing Foreign Key
-- -------------------------------------------------
CREATE TABLE Employee (
    EmployeeID      SERIAL          PRIMARY KEY,
    Name            VARCHAR(100)    NOT NULL,
    ManagerID       INT,            -- Nullable (CEO has no manager)
    
    -- Self-referencing FK
    FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID)
        ON DELETE SET NULL
);
 
-- EXAMPLE 4: Multiple Foreign Keys to Same Table
-- -------------------------------------------------
CREATE TABLE Flight (
    FlightID        SERIAL          PRIMARY KEY,
    FlightNumber    VARCHAR(10)     NOT NULL,
    DepartureCity   INT             NOT NULL,
    ArrivalCity     INT             NOT NULL,
    DepartureTime   TIMESTAMP       NOT NULL,
    ArrivalTime     TIMESTAMP       NOT NULL,
    
    -- Both FKs reference same table (different roles)
    FOREIGN KEY (DepartureCity) REFERENCES City(CityID),
    FOREIGN KEY (ArrivalCity) REFERENCES City(CityID),
    
    -- Constraint: can't fly to same city
    CHECK (DepartureCity != ArrivalCity)
);
 
-- EXAMPLE 5: Identifying Relationship (Weak Entity)
-- -------------------------------------------------
-- Room is uniquely identified only within a Building
 
CREATE TABLE Building (
    BuildingCode    CHAR(3)         PRIMARY KEY,
    BuildingName    VARCHAR(100)    NOT NULL,
    Address         VARCHAR(200)
);
 
CREATE TABLE Room (
    BuildingCode    CHAR(3)         NOT NULL,
    RoomNumber      VARCHAR(10)     NOT NULL,
    Capacity        INT             CHECK (Capacity > 0),
    RoomType        VARCHAR(50),
    
    -- Composite PK includes parent's PK
    PRIMARY KEY (BuildingCode, RoomNumber),
    
    -- Identifying relationship
    FOREIGN KEY (BuildingCode) REFERENCES Building(BuildingCode)
        ON DELETE CASCADE   -- If building deleted, rooms deleted
        ON UPDATE CASCADE   -- If building code changes, propagate
);

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
88
89
90
91

-- ==================================================
-- DOCUMENTED SCHEMA EXAMPLE
-- ==================================================
 
/*
===================================================
TABLE: Customer
===================================================
Purpose: 
    Stores information about individuals or 
    organizations that purchase products or services.
    
Business Owner: Sales Department
Data Steward: CRM Team Lead
 
Source Systems:
    - WebShop: Online registrations
    - SalesForce: Imported leads
    - Legacy CRM: Historical migration
 
Privacy Classification: PII (Personally Identifiable)
Retention Policy: 7 years after last activity
===================================================
*/
CREATE TABLE Customer (
    -- Primary identifier, system-generated
    -- Format: Sequential integer, no gaps guaranteed
    -- Source: Auto-generated on insert
    CustomerID      SERIAL          PRIMARY KEY,
    
    -- Legal name for individuals, company name for B2B
    -- Source: User registration or sales input
    -- Note: May contain international characters (UTF-8)
    CustomerName    VARCHAR(200)    NOT NULL,
    
    -- Primary contact email
    -- Validation: Standard email regex at app layer
    -- Used for: Account recovery, marketing (with consent)
    Email           VARCHAR(254)    NOT NULL UNIQUE,
    
    -- Customer segment classification
    -- Values:
    --   'individual' - B2C personal accounts
    --   'business' - B2B company accounts
    --   'enterprise' - Large accounts with special terms
    -- Set by: Sales team based on contract value
    CustomerType    VARCHAR(20)     NOT NULL DEFAULT 'individual'
                    CHECK (CustomerType IN ('individual', 'business', 'enterprise')),
    
    -- Accumulated loyalty points
    -- Calculation: 1 point per $10 spent
    -- Expiration: Points expire 24 months after earning
    -- Updated by: Daily batch process
    LoyaltyPoints   INT             NOT NULL DEFAULT 0 CHECK (LoyaltyPoints >= 0),
    
    -- Account creation timestamp
    -- Timezone: Stored in UTC
    CreatedAt       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Soft delete flag
    -- When true: Account deactivated but data retained
    -- Retention: Required for 7-year audit trail
    IsActive        BOOLEAN         NOT NULL DEFAULT TRUE
);
 
-- Index for email lookups (login, duplicate check)
CREATE INDEX idx_customer_email ON Customer(Email);
 
-- Index for active customer queries
CREATE INDEX idx_customer_active ON Customer(IsActive) WHERE IsActive = TRUE;
 
/*
---------------------------------------------------
RELATIONSHIP: Customer -> Address (1:N)
---------------------------------------------------
Business Rule:
    A customer may have multiple addresses (shipping,
    billing, etc.). At least one primary address is 
    required for order fulfillment.
    
Integrity Rules:
    - Deleting a customer cascades to addresses
    - At most one primary address per customer per type
---------------------------------------------------
*/
 
-- External data dictionary entry (often in separate doc)
COMMENT ON TABLE Customer IS 'Core entity for customer relationship management';
COMMENT ON COLUMN Customer.CustomerID IS 'Unique identifier (PK, auto-increment)';
COMMENT ON COLUMN Customer.Email IS 'Primary contact email (UK, login credential)';
COMMENT ON COLUMN Customer.LoyaltyPoints IS 'Accumulated rewards points, batch-updated daily';

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
88

-- ==================================================
-- MIGRATION VERSIONING EXAMPLES
-- ==================================================
 
-- Migration: V001__Create_Customer_Table.sql
-- Date: 2024-01-15
-- Author: Jane Developer
-- Description: Initial customer table for CRM module
 
CREATE TABLE Customer (
    CustomerID      SERIAL          PRIMARY KEY,
    Email           VARCHAR(254)    NOT NULL UNIQUE,
    CreatedAt       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- ==================================================
 
-- Migration: V002__Add_Customer_Name.sql
-- Date: 2024-02-01
-- Author: John Engineer
-- Description: Add name field per JIRA-1234
 
ALTER TABLE Customer 
    ADD COLUMN CustomerName VARCHAR(200);
 
-- Backfill existing records
UPDATE Customer 
    SET CustomerName = 'Unknown' 
    WHERE CustomerName IS NULL;
 
-- Make NOT NULL after backfill
ALTER TABLE Customer 
    ALTER COLUMN CustomerName SET NOT NULL;
 
-- ==================================================
 
-- Migration: V003__Add_CustomerType_Enum.sql
-- Date: 2024-03-15
-- Author: Jane Developer
-- Description: Customer segmentation feature
 
-- Add new column with default
ALTER TABLE Customer 
    ADD COLUMN CustomerType VARCHAR(20) NOT NULL DEFAULT 'individual';
 
-- Add constraint
ALTER TABLE Customer 
    ADD CONSTRAINT chk_customer_type 
    CHECK (CustomerType IN ('individual', 'business', 'enterprise'));
 
-- ==================================================
 
-- Rollback: V003__Add_CustomerType_Enum_ROLLBACK.sql
-- ALWAYS test rollbacks before deployment
 
ALTER TABLE Customer 
    DROP CONSTRAINT chk_customer_type;
 
ALTER TABLE Customer 
    DROP COLUMN CustomerType;
 
-- ==================================================
 
-- Migration: V010__Split_Name_Into_FirstLast.sql
-- Date: 2024-06-01
-- Author: John Engineer
-- Description: Enable proper name sorting/searching
-- WARNING: Destructive change - thorough testing required
 
-- Step 1: Add new columns
ALTER TABLE Customer 
    ADD COLUMN FirstName VARCHAR(100),
    ADD COLUMN LastName VARCHAR(100);
 
-- Step 2: Migrate data (simplified; real migration handles edge cases)
UPDATE Customer 
SET 
    FirstName = SPLIT_PART(CustomerName, ' ', 1),
    LastName = SUBSTRING(CustomerName FROM POSITION(' ' IN CustomerName) + 1);
 
-- Step 3: Set NOT NULL after migration
ALTER TABLE Customer 
    ALTER COLUMN FirstName SET NOT NULL,
    ALTER COLUMN LastName SET NOT NULL;
 
-- Step 4: Remove old column (AFTER apps updated)
-- NOTE: This is often a separate migration after verification
-- ALTER TABLE Customer DROP COLUMN CustomerName;