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.

This ensures that only users with valid subscriptions can access course content, prevents duplicate enrollments, and maintains consistency between subscription and course participation data. It also improves data integrity and reduces the possibility of invalid or inconsistent records in the system.

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.

This ensures that certificates are only issued for successfully completed courses, preventing invalid or premature certification. It also maintains the integrity and reliability of the certification system within the platform.

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);