Advanced Database Development

Data constraints requirements: Enrollment-Subscription consistency + single enrollment per course

Data requirements description

Course enrollments must remain consistent with the subscription and participation rules of the platform:

# A user must have an ACTIVE subscription in order to enroll in a course.

# A user cannot enroll in the same course more than once.

# enrollment status changes must follow a valid transition model.

# Each enrollment must reference a valid user and a valid course.

Implementation

Triggers

-- status transition helper
CREATE OR REPLACE FUNCTION olpms_is_valid_enrollment_transition(p_old text, p_new text)
RETURNS boolean
LANGUAGE sql
AS $$
    SELECT CASE
        WHEN p_old IS NULL AND p_new IN ('IN_PROGRESS', 'COMPLETED', 'CANCELLED') THEN true
        WHEN p_old = p_new THEN true
        WHEN p_old = 'IN_PROGRESS' AND p_new IN ('COMPLETED', 'CANCELLED') THEN true
        WHEN p_old = 'COMPLETED' AND p_new = 'COMPLETED' THEN true
        WHEN p_old = 'CANCELLED' AND p_new = 'CANCELLED' THEN true
        ELSE false
    END;
$$;

-- main enrollment consistency trigger function
CREATE OR REPLACE FUNCTION olpms_trg_enrollment_enforce()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_active_subscription_count integer;
BEGIN
    -- user must have an active subscription when inserting enrollment
    IF TG_OP = 'INSERT' THEN
        SELECT COUNT(*)
        INTO v_active_subscription_count
        FROM user_subscription us
        WHERE us.user_id = NEW.user_id
          AND us.status = 'ACTIVE'
          AND CURRENT_DATE BETWEEN us.start_date AND us.end_date;

        IF v_active_subscription_count = 0 THEN
            RAISE EXCEPTION 'User % does not have an active subscription', NEW.user_id;
        END IF;
    END IF;

    -- prevent duplicate enrollment for the same user and the same course
    IF EXISTS (
        SELECT 1
        FROM enrollment e
        WHERE e.user_id = NEW.user_id
          AND e.course_id = NEW.course_id
          AND (TG_OP <> 'UPDATE' OR e.enrollment_id <> NEW.enrollment_id)
    ) THEN
        RAISE EXCEPTION 'User % is already enrolled in course %', NEW.user_id, NEW.course_id;
    END IF;

    -- validate completion_status transition on UPDATE
    IF TG_OP = 'UPDATE' THEN
        IF NOT olpms_is_valid_enrollment_transition(OLD.completion_status, NEW.completion_status) THEN
            RAISE EXCEPTION
                'Invalid enrollment status transition: % -> %',
                OLD.completion_status,
                NEW.completion_status;
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_enrollment_enforce ON enrollment;

CREATE TRIGGER trg_enrollment_enforce
BEFORE INSERT OR UPDATE
ON enrollment
FOR EACH ROW
EXECUTE FUNCTION olpms_trg_enrollment_enforce();

Stored procedures / functions

CREATE OR REPLACE FUNCTION olpms_is_valid_enrollment_transition(p_old text, p_new text)
RETURNS boolean
LANGUAGE sql
AS $$
    SELECT CASE
        WHEN p_old IS NULL AND p_new IN ('IN_PROGRESS', 'COMPLETED', 'CANCELLED') THEN true
        WHEN p_old = p_new THEN true
        WHEN p_old = 'IN_PROGRESS' AND p_new IN ('COMPLETED', 'CANCELLED') THEN true
        WHEN p_old = 'COMPLETED' AND p_new = 'COMPLETED' THEN true
        WHEN p_old = 'CANCELLED' AND p_new = 'CANCELLED' THEN true
        ELSE false
    END;
$$;

CREATE OR REPLACE FUNCTION olpms_trg_enrollment_enforce()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_active_subscription_count integer;
BEGIN
    IF TG_OP = 'INSERT' THEN
        SELECT COUNT(*)
        INTO v_active_subscription_count
        FROM user_subscription us
        WHERE us.user_id = NEW.user_id
          AND us.status = 'ACTIVE'
          AND CURRENT_DATE BETWEEN us.start_date AND us.end_date;

        IF v_active_subscription_count = 0 THEN
            RAISE EXCEPTION 'User % does not have an active subscription', NEW.user_id;
        END IF;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM enrollment e
        WHERE e.user_id = NEW.user_id
          AND e.course_id = NEW.course_id
          AND (TG_OP <> 'UPDATE' OR e.enrollment_id <> NEW.enrollment_id)
    ) THEN
        RAISE EXCEPTION 'User % is already enrolled in course %', NEW.user_id, NEW.course_id;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        IF NOT olpms_is_valid_enrollment_transition(OLD.completion_status, NEW.completion_status) THEN
            RAISE EXCEPTION
                'Invalid enrollment status transition: % -> %',
                OLD.completion_status,
                NEW.completion_status;
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

Views

CREATE OR REPLACE VIEW v_enrollments_enriched AS
SELECT
    e.enrollment_id,
    e.user_id,
    ue.first_name,
    ue.last_name,
    ue.email,
    e.course_id,
    c.name AS course_name,
    e.enroll_date,
    e.completion_status,
    e.progress_percentage,
    us.subscription_id,
    us.status AS subscription_status,
    us.start_date AS subscription_start_date,
    us.end_date AS subscription_end_date
FROM enrollment e
JOIN users u
    ON u.id = e.user_id
JOIN user_entity ue
    ON ue.id = u.id
JOIN course c
    ON c.course_id = e.course_id
LEFT JOIN user_subscription us
    ON us.user_id = e.user_id
   AND us.status = 'ACTIVE'
   AND CURRENT_DATE BETWEEN us.start_date AND us.end_date;

Data constraints requirements: Certificate generation consistency + one certificate per completed enrollment

Data requirements description

Certificates must obey completion and consistency rules:

• A certificate can only be issued for an enrollment whose completion status is COMPLETED.
• The same enrollment cannot have more than one certificate.
• A certificate must always refer to a valid enrollment.
• Issuing a certificate before course completion is blocked.

Implementation

Triggers

CREATE OR REPLACE FUNCTION olpms_trg_certificate_enforce()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_completion_status VARCHAR(30);
BEGIN
    -- find the status of the referenced enrollment
    SELECT e.completion_status
    INTO v_completion_status
    FROM enrollment e
    WHERE e.enrollment_id = NEW.enrollment_id;

    IF v_completion_status IS NULL THEN
        RAISE EXCEPTION 'Enrollment % does not exist or has no completion status', NEW.enrollment_id;
    END IF;

    -- certificate can only be issued for completed enrollments
    IF v_completion_status <> 'COMPLETED' THEN
        RAISE EXCEPTION
            'Certificate cannot be issued because enrollment % is not COMPLETED',
            NEW.enrollment_id;
    END IF;

    -- prevent more than one certificate for the same enrollment
    IF EXISTS (
        SELECT 1
        FROM certificate c
        WHERE c.enrollment_id = NEW.enrollment_id
          AND (TG_OP <> 'UPDATE' OR c.certificate_id <> NEW.certificate_id)
    ) THEN
        RAISE EXCEPTION
            'Enrollment % already has a certificate',
            NEW.enrollment_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_certificate_enforce ON certificate;

CREATE TRIGGER trg_certificate_enforce
BEFORE INSERT OR UPDATE
ON certificate
FOR EACH ROW
EXECUTE FUNCTION olpms_trg_certificate_enforce();

Views

CREATE OR REPLACE VIEW v_certificates_enriched AS
SELECT
    c.certificate_id,
    c.enrollment_id,
    c.issue_date,
    c.certificate_code,
    c.status AS certificate_status,
    e.user_id,
    ue.first_name,
    ue.last_name,
    ue.email,
    e.course_id,
    cr.name AS course_name,
    e.enroll_date,
    e.completion_status,
    e.progress_percentage
FROM certificate c
JOIN enrollment e
    ON e.enrollment_id = c.enrollment_id
JOIN users u
    ON u.id = e.user_id
JOIN user_entity ue
    ON ue.id = u.id
JOIN course cr
    ON cr.course_id = e.course_id;

Data constraints requirements: Quiz attempt validity + score consistency

Data requirements description

Quiz attempts must obey assessment consistency rules:

• A quiz attempt must always refer to a valid user and a valid quiz.
• The achieved score must be between 0 and the quiz total_points.
• A user should only attempt quizzes that belong to lessons from courses in which the user is enrolled.
• Invalid quiz attempts are blocked

Implementation

Triggers

CREATE OR REPLACE FUNCTION olpms_trg_quiz_attempt_enforce()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_total_points INT;
BEGIN
    IF NEW.user_id IS NULL THEN
        RAISE EXCEPTION 'Quiz attempt must reference a valid user';
    END IF;

    IF NEW.quiz_id IS NULL THEN
        RAISE EXCEPTION 'Quiz attempt must reference a valid quiz';
    END IF;

    SELECT q.total_points
    INTO v_total_points
    FROM quiz q
    WHERE q.quiz_id = NEW.quiz_id;

    IF v_total_points IS NULL THEN
        RAISE EXCEPTION 'Quiz % does not exist', NEW.quiz_id;
    END IF;

    IF NEW.score IS NULL THEN
        RAISE EXCEPTION 'Quiz attempt score must not be NULL';
    END IF;

    IF NEW.score < 0 OR NEW.score > v_total_points THEN
        RAISE EXCEPTION
            'Invalid score % for quiz %, allowed range is 0 to %',
            NEW.score, NEW.quiz_id, v_total_points;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM enrollment e
        JOIN module m
            ON m.course_id = e.course_id
        JOIN lesson l
            ON l.module_id = m.module_id
        JOIN quiz q
            ON q.lesson_id = l.lesson_id
        WHERE e.user_id = NEW.user_id
          AND q.quiz_id = NEW.quiz_id
    ) THEN
        RAISE EXCEPTION
            'User % is not enrolled in the course associated with quiz %',
            NEW.user_id, NEW.quiz_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_quiz_attempt_enforce ON quiz_attempt;

CREATE TRIGGER trg_quiz_attempt_enforce
BEFORE INSERT OR UPDATE
ON quiz_attempt
FOR EACH ROW
EXECUTE FUNCTION olpms_trg_quiz_attempt_enforce();

Views

CREATE OR REPLACE VIEW v_quiz_attempts_enriched AS
SELECT
    qa.attempt_id,
    qa.attempt_date,
    qa.score,
    qa.user_id,
    ue.first_name,
    ue.last_name,
    ue.email,
    qa.quiz_id,
    q.total_points,
    q.passing_score,
    l.lesson_id,
    l.title AS lesson_title,
    m.module_id,
    m.title AS module_title,
    c.course_id,
    c.name AS course_name,
    CASE
        WHEN qa.score >= q.passing_score THEN 'PASSED'
        ELSE 'FAILED'
    END AS result_status
FROM quiz_attempt qa
JOIN users u
    ON u.id = qa.user_id
JOIN user_entity ue
    ON ue.id = u.id
JOIN quiz q
    ON q.quiz_id = qa.quiz_id
JOIN lesson l
    ON l.lesson_id = q.lesson_id
JOIN module m
    ON m.module_id = l.module_id
JOIN course c
    ON c.course_id = m.course_id;

Data constraints requirements: Support ticket status consistency + validated ticket attributes

Data requirements description

Support tickets must remain consistent and well-structured throughout their lifecycle:

• Ticket statuses must follow a controlled set of allowed values.
• Ticket subjects must not be empty.
• Ticket handling should support valid status transitions.
• Ticket data should be easy to inspect together with the related user and administrator information.

Implementation

Stored procedures / functions

CREATE OR REPLACE FUNCTION olpms_is_valid_ticket_transition(p_old text, p_new text)
RETURNS boolean
LANGUAGE sql
AS $$
    SELECT CASE
        WHEN p_old = p_new THEN true
        WHEN p_old = 'OPEN' AND p_new IN ('IN_PROGRESS', 'RESOLVED') THEN true
        WHEN p_old = 'IN_PROGRESS' AND p_new IN ('RESOLVED') THEN true
        WHEN p_old = 'RESOLVED' AND p_new = 'RESOLVED' THEN true
        ELSE false
    END;
$$;

Views

CREATE OR REPLACE VIEW v_support_tickets_enriched AS
SELECT
    st.ticket_id,
    st.subject,
    st.description,
    st.status,
    st.created_at,
    st.user_id,
    u.first_name AS user_first_name,
    u.last_name AS user_last_name,
    u.email AS user_email,
    st.admin_id,
    a.first_name AS admin_first_name,
    a.last_name AS admin_last_name,
    a.email AS admin_email
FROM support_ticket st
JOIN user_entity u
    ON u.id = st.user_id
JOIN user_entity a
    ON a.id = st.admin_id;

Custom domains

CREATE DOMAIN ticket_status_domain AS VARCHAR(30)
CHECK (VALUE IN ('OPEN', 'IN_PROGRESS', 'RESOLVED'));

CREATE DOMAIN non_empty_subject_domain AS VARCHAR(200)
CHECK (LENGTH(TRIM(VALUE)) > 0);