Attributes and Domains in the Relational Model

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

-- In SQL, attributes manifest as column definitions
-- Each column name is an attribute, and the data type specifies the domain
 
CREATE TABLE Employee (
    -- Attribute: employee_id, Domain: INTEGER
    employee_id     INTEGER         NOT NULL,
    
    -- Attribute: first_name, Domain: VARCHAR(50)
    first_name      VARCHAR(50)     NOT NULL,
    
    -- Attribute: last_name, Domain: VARCHAR(50)
    last_name       VARCHAR(50)     NOT NULL,
    
    -- Attribute: hire_date, Domain: DATE
    hire_date       DATE            NOT NULL,
    
    -- Attribute: salary, Domain: DECIMAL(10,2)
    salary          DECIMAL(10,2)   NOT NULL,
    
    -- Attribute: department_id, Domain: INTEGER (foreign reference)
    department_id   INTEGER,
    
    PRIMARY KEY (employee_id)
);
 
-- The relation schema can be written as:
-- Employee(employee_id: INTEGER, first_name: VARCHAR(50), 
--          last_name: VARCHAR(50), hire_date: DATE, 
--          salary: DECIMAL(10,2), department_id: INTEGER)

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

-- These two relation definitions are THEORETICALLY EQUIVALENT
-- even though SQL tracks a different column order
 
-- Definition 1
CREATE TABLE Person_v1 (
    person_id   INT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    birth_date  DATE
);
 
-- Definition 2 (same attributes, different order)
CREATE TABLE Person_v2 (
    last_name   VARCHAR(50),
    birth_date  DATE,
    person_id   INT PRIMARY KEY,
    first_name  VARCHAR(50)
);
 
-- Both tables can store exactly the same data
-- Both have identical attribute sets: {person_id, first_name, last_name, birth_date}
-- From a relational theory perspective, they are the SAME SCHEMA
 
-- Best practice: Always use explicit column names
SELECT person_id, first_name, last_name, birth_date 
FROM Person_v1;
 
-- Avoid: Relying on column order
SELECT * FROM Person_v1;  -- Order depends on table definition

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

-- Demonstrating different attribute roles in a schema
 
CREATE TABLE Employee (
    -- KEY ATTRIBUTE: Uniquely identifies each employee
    employee_id         INT             PRIMARY KEY,
    
    -- DESCRIPTOR ATTRIBUTES: Properties of the employee
    first_name          VARCHAR(50)     NOT NULL,
    last_name           VARCHAR(50)     NOT NULL,
    email               VARCHAR(100)    NOT NULL UNIQUE,
    phone_number        VARCHAR(20),
    birth_date          DATE,
    
    -- REFERENCE ATTRIBUTE: Links to Department relation
    department_id       INT             REFERENCES Department(department_id),
    
    -- REFERENCE ATTRIBUTE: Self-referencing for manager hierarchy
    manager_id          INT             REFERENCES Employee(employee_id),
    
    -- STATUS ATTRIBUTE: Lifecycle state
    employment_status   VARCHAR(20)     DEFAULT 'active'
                        CHECK (employment_status IN ('active', 'on_leave', 'terminated')),
    
    -- TEMPORAL ATTRIBUTES: Audit trail
    hired_at            TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    terminated_at       TIMESTAMP       NULL
);
 
-- DERIVED ATTRIBUTE: Age computed from birth_date
-- In many databases, this would be a computed column or view
CREATE VIEW Employee_With_Age AS
SELECT 
    *,
    -- Derived attribute: computed at query time
    EXTRACT(YEAR FROM AGE(birth_date)) AS age
FROM 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

-- APPROACH 1: Keeping composite attributes (AVOID unless access is always atomic)
CREATE TABLE Customer_v1 (
    customer_id     INT PRIMARY KEY,
    full_name       VARCHAR(150),      -- Composite: cannot easily sort by last name
    full_address    TEXT               -- Composite: cannot filter by city
);
 
-- Problem: How do you find customers in California?
-- SELECT * FROM Customer_v1 WHERE full_address LIKE '%California%';  -- Unreliable!
 
-- APPROACH 2: Properly decomposed attributes (PREFERRED)
CREATE TABLE Customer_v2 (
    customer_id     INT PRIMARY KEY,
    
    -- Decomposed name
    first_name      VARCHAR(50)     NOT NULL,
    middle_name     VARCHAR(50),
    last_name       VARCHAR(50)     NOT NULL,
    
    -- Decomposed address
    street_line_1   VARCHAR(100)    NOT NULL,
    street_line_2   VARCHAR(100),
    city            VARCHAR(50)     NOT NULL,
    state_province  VARCHAR(50)     NOT NULL,
    postal_code     VARCHAR(20)     NOT NULL,
    country_code    CHAR(2)         NOT NULL DEFAULT 'US'
);
 
-- Now queries are efficient and correct:
SELECT * FROM Customer_v2 WHERE state_province = 'California';
SELECT * FROM Customer_v2 WHERE country_code = 'CA' ORDER BY last_name;
 
-- If you need the full composite view, create it:
CREATE VIEW Customer_Full_View AS
SELECT 
    customer_id,
    CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name,
    CONCAT_WS(', ', street_line_1, street_line_2, city, state_province, postal_code, country_code) AS full_address
FROM Customer_v2;

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

-- VIOLATION: Storing multi-valued attributes in single column (NEVER DO THIS)
CREATE TABLE Employee_Bad (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100),
    phone_numbers   VARCHAR(500),     -- '555-1234,555-5678,555-9999'
    skills          TEXT              -- 'Python,SQL,Project Management'
);
 
-- Problems:
-- 1. How do you find employees with 'SQL' skill? LIKE '%SQL%' catches 'MySQL', 'NoSQL'
-- 2. How do you count phone numbers per employee? Parse the string
-- 3. What if a skill name contains a comma? Corruption
-- 4. No constraint enforcement on individual values
 
-- CORRECT: Normalize multi-valued attributes into separate relations
CREATE TABLE Employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100)    NOT NULL
);
 
-- Multi-valued attribute: phone_numbers → separate relation
CREATE TABLE Employee_Phone (
    employee_id     INT             REFERENCES Employee(employee_id),
    phone_type      VARCHAR(20)     NOT NULL,   -- 'mobile', 'home', 'work'
    phone_number    VARCHAR(20)     NOT NULL,
    is_primary      BOOLEAN         DEFAULT FALSE,
    PRIMARY KEY (employee_id, phone_type, phone_number)
);
 
-- Multi-valued attribute: skills → separate relation with proper normalization
CREATE TABLE Skill (
    skill_id        INT PRIMARY KEY,
    skill_name      VARCHAR(100)    NOT NULL UNIQUE,
    skill_category  VARCHAR(50)
);
 
CREATE TABLE Employee_Skill (
    employee_id     INT             REFERENCES Employee(employee_id),
    skill_id        INT             REFERENCES Skill(skill_id),
    proficiency     VARCHAR(20)     CHECK (proficiency IN ('beginner', 'intermediate', 'expert')),
    acquired_date   DATE,
    PRIMARY KEY (employee_id, skill_id)
);
 
-- Now queries are clean and correct:
SELECT e.name, s.skill_name, es.proficiency
FROM Employee e
JOIN Employee_Skill es ON e.employee_id = es.employee_id
JOIN Skill s ON es.skill_id = s.skill_id
WHERE s.skill_name = 'SQL';

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

-- PostgreSQL supports COMMENT for documenting database objects
 
CREATE TABLE Customer (
    customer_id         SERIAL PRIMARY KEY,
    email_address       VARCHAR(255) NOT NULL UNIQUE,
    tier_status         VARCHAR(20) DEFAULT 'bronze',
    lifetime_value      DECIMAL(12,2) DEFAULT 0.00,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Add attribute-level documentation
COMMENT ON COLUMN Customer.customer_id IS 
    'System-generated unique identifier. Never exposed to customers.';
 
COMMENT ON COLUMN Customer.email_address IS 
    'Primary contact email. Validated format enforced by application. 
     PII - requires encryption at rest. Source: registration form.';
 
COMMENT ON COLUMN Customer.tier_status IS 
    'Loyalty program tier. Valid values: bronze, silver, gold, platinum.
     Updated monthly by batch job based on lifetime_value thresholds.
     Business owner: Marketing Department.';
 
COMMENT ON COLUMN Customer.lifetime_value IS 
    'Sum of all completed order totals for this customer in USD.
     Derived attribute updated by trigger on Order completion.
     Used for: tier calculation, segmentation, recommendation weighting.';
 
COMMENT ON COLUMN Customer.created_at IS
    'Account creation timestamp. System-managed, never modified after insert.
     Time zone: UTC. Used for: cohort analysis, retention calculations.';
 
-- Query the documentation
SELECT 
    column_name,
    data_type,
    col_description(
        'customer'::regclass, 
        ordinal_position
    ) AS documentation
FROM information_schema.columns
WHERE table_name = 'customer'
ORDER BY ordinal_position;