Ternary Relationships

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

-- Relational Schema for Medical Prescriptions
CREATE TABLE Doctor (
    doctor_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    specialty VARCHAR(50),
    license_number VARCHAR(20) UNIQUE NOT NULL
);
 
CREATE TABLE Medication (
    medication_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    generic_name VARCHAR(100),
    controlled_class CHAR(2)  -- NULL, C2, C3, C4, C5
);
 
CREATE TABLE Patient (
    patient_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth_date DATE,
    insurance_id VARCHAR(20)
);
 
-- Ternary relationship as associative entity
CREATE TABLE Prescription (
    prescription_id INT PRIMARY KEY,
    doctor_id INT NOT NULL REFERENCES Doctor(doctor_id),
    medication_id INT NOT NULL REFERENCES Medication(medication_id),
    patient_id INT NOT NULL REFERENCES Patient(patient_id),
    dosage VARCHAR(50) NOT NULL,
    prescription_date DATE NOT NULL,
    refills INT DEFAULT 0,
    instructions TEXT,
    -- M:M:M allows multiple prescriptions for same triple (over time)
    -- If we wanted at most one active prescription per triple:
    -- UNIQUE (doctor_id, medication_id, patient_id)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Create indexes for common query patterns
CREATE INDEX idx_prescription_doctor ON Prescription(doctor_id);
CREATE INDEX idx_prescription_patient ON Prescription(patient_id);
CREATE INDEX idx_prescription_medication ON Prescription(medication_id);

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

-- Relational Schema for Supply Chain
CREATE TABLE Supplier (
    supplier_id INT PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL,
    contact_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    rating DECIMAL(2,1) CHECK (rating BETWEEN 1.0 AND 5.0)
);
 
CREATE TABLE Part (
    part_id INT PRIMARY KEY,
    part_number VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(200),
    unit_of_measure VARCHAR(20),
    standard_cost DECIMAL(12,2)
);
 
CREATE TABLE Project (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(15,2),
    status VARCHAR(20) DEFAULT 'Planning'
);
 
-- Ternary relationship: SUPPLY
CREATE TABLE Supply (
    supply_id INT PRIMARY KEY,
    supplier_id INT NOT NULL REFERENCES Supplier(supplier_id),
    part_id INT NOT NULL REFERENCES Part(part_id),
    project_id INT NOT NULL REFERENCES Project(project_id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(12,2) NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    contract_date DATE,
    delivery_schedule VARCHAR(100),
    lead_time_days INT,
    -- Allow multiple supply records for same triple (different contracts/times)
    UNIQUE (supplier_id, part_id, project_id, contract_date)
);
 
-- Enforce: at most one primary supplier per (Part, Project)
-- Using partial unique index (PostgreSQL syntax)
CREATE UNIQUE INDEX idx_supply_primary 
ON Supply(part_id, project_id) 
WHERE is_primary = TRUE;
 
-- Sample queries demonstrating ternary relationship usage
 
-- Q1: Who supplies Widget-A to Project Alpha?
-- SELECT s.company_name, sp.unit_price
-- FROM Supply sp
-- JOIN Supplier s ON sp.supplier_id = s.supplier_id
-- WHERE sp.part_id = (SELECT part_id FROM Part WHERE part_number = 'Widget-A')
--   AND sp.project_id = (SELECT project_id FROM Project WHERE project_name = 'Alpha');
 
-- Q2: What's the total cost for Project Alpha from each supplier?
-- SELECT s.company_name, SUM(sp.quantity * sp.unit_price) as total_cost
-- FROM Supply sp
-- JOIN Supplier s ON sp.supplier_id = s.supplier_id
-- WHERE sp.project_id = (SELECT project_id FROM Project WHERE project_name = 'Alpha')
-- GROUP BY s.company_name;

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

-- Key queries for the advising system
 
-- Q1: Find all advisors for a specific student on a specific project
SELECT p.name as professor, a.role, a.start_date
FROM Advises a
JOIN Professor p ON a.professor_id = p.professor_id
WHERE a.student_id = 123 
  AND a.project_id = 456
ORDER BY 
    CASE a.role 
        WHEN 'Primary' THEN 1 
        WHEN 'Co-Advisor' THEN 2 
        ELSE 3 
    END;
 
-- Q2: Find all students advised by Prof. Adams across all projects
SELECT s.name as student, pr.title as project, a.role
FROM Advises a
JOIN Student s ON a.student_id = s.student_id
JOIN Project pr ON a.project_id = pr.project_id
WHERE a.professor_id = (SELECT professor_id FROM Professor WHERE name = 'Adams')
  AND a.end_date IS NULL  -- Currently active
ORDER BY pr.title, a.role;
 
-- Q3: Projects where the same professor-student pair appears
SELECT p.name as professor, s.name as student, 
       STRING_AGG(pr.title, ', ') as projects,
       COUNT(*) as project_count
FROM Advises a
JOIN Professor p ON a.professor_id = p.professor_id
JOIN Student s ON a.student_id = s.student_id
JOIN Project pr ON a.project_id = pr.project_id
GROUP BY p.professor_id, p.name, s.student_id, s.name
HAVING COUNT(*) > 1;
 
-- Q4: Verify constraint - at most one primary per (student, project)
SELECT student_id, project_id, COUNT(*) as primary_count
FROM Advises
WHERE role = 'Primary'
GROUP BY student_id, project_id
HAVING COUNT(*) > 1;  -- Should return empty if constraint holds

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

-- Relational Schema for Sports Statistics
-- Note: This exploits the M:1:M cardinality for optimal design
 
CREATE TABLE Player (
    player_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth_date DATE,
    nationality VARCHAR(50),
    position VARCHAR(30)
);
 
CREATE TABLE Team (
    team_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    league VARCHAR(50),
    founded_year INT
);
 
CREATE TABLE Season (
    season_id INT PRIMARY KEY,
    year_start INT NOT NULL,
    year_end INT NOT NULL,
    description VARCHAR(50),  -- e.g., "2023-2024 Regular Season"
    UNIQUE(year_start, year_end)
);
 
-- Ternary relationship with M:1:M cardinality
-- Key insight: PRIMARY KEY is (player_id, season_id) due to FD
CREATE TABLE PlayerSeasonStats (
    player_id INT NOT NULL REFERENCES Player(player_id),
    season_id INT NOT NULL REFERENCES Season(season_id),
    team_id INT NOT NULL REFERENCES Team(team_id),  -- Determined by player+season
    -- Statistics (relationship attributes)
    games_played INT DEFAULT 0,
    goals INT DEFAULT 0,
    assists INT DEFAULT 0,
    minutes_played INT DEFAULT 0,
    yellow_cards INT DEFAULT 0,
    red_cards INT DEFAULT 0,
    rating DECIMAL(3,2),
    -- Primary key exploits the functional dependency
    PRIMARY KEY (player_id, season_id)
    -- Note: team_id is NOT part of the key 
    -- because Player + Season → Team
);
 
-- The FD means we can query efficiently:
 
-- Q1: What team was Player X on in 2023 season?
-- Single lookup by primary key!
SELECT t.name as team
FROM PlayerSeasonStats pss
JOIN Team t ON pss.team_id = t.team_id
WHERE pss.player_id = 123 
  AND pss.season_id = (SELECT season_id FROM Season WHERE year_start = 2023);
 
-- Q2: All players on Team Y in Season Z
SELECT p.name, pss.goals, pss.assists
FROM PlayerSeasonStats pss
JOIN Player p ON pss.player_id = p.player_id
WHERE pss.team_id = 456 AND pss.season_id = 789
ORDER BY pss.goals DESC;
 
-- Q3: Player's career across all teams
SELECT s.description as season, t.name as team, 
       pss.games_played, pss.goals, pss.assists
FROM PlayerSeasonStats pss
JOIN Season s ON pss.season_id = s.season_id
JOIN Team t ON pss.team_id = t.team_id
WHERE pss.player_id = 123
ORDER BY s.year_start;