Week 8 — Database Views: Design, Security & Reporting¶

-- Create a simple view
CREATE VIEW student_summary AS
SELECT
    student_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    dept_id,
    gpa,
    enrolled_on
FROM students
WHERE is_active = 1;

-- Query the view exactly like a table
SELECT full_name, gpa FROM student_summary WHERE dept_id = 5;

-- MySQL: show the original CREATE VIEW statement
SHOW CREATE VIEW student_summary\G

-- Via information_schema (both MySQL and PostgreSQL)
SELECT
    TABLE_NAME      AS view_name,
    VIEW_DEFINITION,
    IS_UPDATABLE,
    SECURITY_TYPE,
    DEFINER,
    CHECK_OPTION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'university'
ORDER BY TABLE_NAME;

-- Find views that depend on a specific table (MySQL)
SELECT TABLE_NAME AS dependent_view
FROM information_schema.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA  = 'university'
  AND TABLE_NAME   = 'students';   -- tables referenced by views

-- ✅ Updatable view (simple single-table projection)
CREATE VIEW active_students AS
SELECT student_id, first_name, last_name, dept_id, gpa, email
FROM students
WHERE is_active = 1;

-- These all work on the view:
UPDATE active_students SET gpa = 3.8 WHERE student_id = 1001;
DELETE FROM active_students WHERE student_id = 1099;
INSERT INTO active_students (student_id, first_name, last_name, dept_id, gpa, email)
VALUES (5000, 'Alice', 'Nguyen', 3, 3.5, 'anguyen@frostburg.edu');

-- ❌ NOT updatable (GROUP BY present)
CREATE VIEW dept_avg_gpa AS
SELECT dept_id, AVG(gpa) AS avg_gpa
FROM students
GROUP BY dept_id;
-- UPDATE dept_avg_gpa ... → ERROR 1288: target not updatable

-- Without CHECK OPTION: you can insert a row the view can't see
CREATE VIEW cs_students AS
SELECT student_id, first_name, last_name, dept_id
FROM students WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_code = 'CS');

-- Inserting a student from a different department succeeds (silently disappears from view)
INSERT INTO cs_students (student_id, first_name, last_name, dept_id)
VALUES (6001, 'Bob', 'Lee', 7);  -- dept_id 7 = Mathematics — user won't see this via view!

-- ✅ WITH CHECK OPTION prevents this
CREATE OR REPLACE VIEW cs_students AS
SELECT student_id, first_name, last_name, dept_id
FROM students
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_code = 'CS')
WITH CHECK OPTION;

-- Now this raises an error:
INSERT INTO cs_students VALUES (6002, 'Carol', 'Kim', 7);
-- ERROR 1369: CHECK OPTION failed 'university.cs_students'

-- Base table (never grant SELECT to application users)
CREATE TABLE students (
    student_id   INT          NOT NULL AUTO_INCREMENT,
    first_name   VARCHAR(50)  NOT NULL,
    last_name    VARCHAR(50)  NOT NULL,
    email        VARCHAR(100) NOT NULL,
    ssn          CHAR(11),           -- Social Security Number — highly sensitive
    date_of_birth DATE,              -- PII
    gpa          DECIMAL(3,2),
    dept_id      INT,
    is_active    TINYINT(1)   DEFAULT 1,
    PRIMARY KEY (student_id)
);

-- ✅ Safe view: SSN and DOB never exposed
CREATE VIEW students_public AS
SELECT
    student_id,
    first_name,
    last_name,
    email,
    -- Mask SSN: show only last 4 digits
    CONCAT('***-**-', RIGHT(ssn, 4)) AS ssn_masked,
    gpa,
    dept_id,
    is_active
FROM students;

-- Grant view access to application role; never grant table access
GRANT SELECT ON university.students_public TO 'app_readonly'@'%';
REVOKE ALL ON university.students FROM 'app_readonly'@'%';

-- Each instructor can only see their own course enrollments
-- (Row-level access enforced by SESSION_USER() or application-passed variable)

CREATE VIEW my_course_enrollments AS
SELECT
    e.student_id,
    s.first_name,
    s.last_name,
    c.course_code,
    c.title,
    e.semester,
    e.grade
FROM enrollments e
JOIN students  s ON e.student_id = s.student_id
JOIN courses   c ON e.course_id  = c.course_id
JOIN instructors i ON c.instructor_id = i.instructor_id
WHERE i.email = CURRENT_USER();   -- row restricted to logged-in instructor

-- Students see only their own transcript
CREATE VIEW my_transcript AS
SELECT
    c.course_code,
    c.title,
    c.credits,
    e.semester,
    e.grade,
    g.points,
    g.max_points
FROM enrollments e
JOIN courses c ON e.course_id  = c.course_id
JOIN grades  g ON e.student_id = g.student_id AND e.course_id = g.course_id
WHERE e.student_id = (
    SELECT student_id FROM students WHERE email = CURRENT_USER()
);

-- Full masking library view for compliance
CREATE VIEW students_masked AS
SELECT
    student_id,
    first_name,
    CONCAT(LEFT(last_name, 1), REPEAT('*', LENGTH(last_name)-1))   AS last_name,
    CONCAT(LEFT(email, 2), '***@', SUBSTRING_INDEX(email,'@',-1))  AS email,
    CONCAT('***-**-', RIGHT(ssn, 4))                                AS ssn,
    CONCAT(YEAR(date_of_birth), '-**-**')                           AS birth_year_only,
    gpa,
    dept_id
FROM students;

-- Department summary for enrollment reports
CREATE VIEW v_dept_summary AS
SELECT
    d.dept_id,
    d.dept_name,
    d.dept_code,
    COUNT(DISTINCT s.student_id)                            AS total_students,
    COUNT(DISTINCT CASE WHEN s.is_active = 1 THEN s.student_id END) AS active_students,
    ROUND(AVG(CASE WHEN s.is_active = 1 THEN s.gpa END), 3) AS avg_gpa,
    SUM(CASE WHEN s.gpa >= 3.5 THEN 1 ELSE 0 END)          AS honors_count,
    COUNT(DISTINCT f.instructor_id)                          AS faculty_count
FROM departments d
LEFT JOIN students    s ON d.dept_id      = s.dept_id
LEFT JOIN instructors f ON d.dept_id      = f.dept_id
GROUP BY d.dept_id, d.dept_name, d.dept_code;

-- Faculty workload view
CREATE VIEW v_faculty_workload AS
SELECT
    i.instructor_id,
    CONCAT(i.first_name,' ',i.last_name) AS instructor_name,
    d.dept_name,
    COUNT(DISTINCT c.course_id)              AS courses_assigned,
    SUM(c.credits)                           AS total_credit_hours,
    COUNT(DISTINCT e.student_id)             AS total_enrolled_students
FROM instructors i
JOIN departments  d ON i.dept_id     = d.dept_id
JOIN courses      c ON c.instructor_id = i.instructor_id
LEFT JOIN enrollments e ON e.course_id = c.course_id
                       AND e.semester = '2024FA'
GROUP BY i.instructor_id, instructor_name, d.dept_name;

-- PostgreSQL: Create materialized view
CREATE MATERIALIZED VIEW mv_semester_gpa_summary AS
SELECT
    e.semester,
    d.dept_name,
    COUNT(DISTINCT e.student_id)                     AS enrolled_count,
    ROUND(AVG(g.points::DECIMAL / g.max_points * 4), 3) AS avg_gpa_equiv,
    COUNT(CASE WHEN e.grade = 'A' THEN 1 END)        AS a_grade_count
FROM enrollments e
JOIN students   s ON e.student_id = s.student_id
JOIN departments d ON s.dept_id   = d.dept_id
JOIN grades     g ON e.student_id = g.student_id AND e.course_id = g.course_id
GROUP BY e.semester, d.dept_name
ORDER BY e.semester DESC, d.dept_name;

-- Populate immediately (default)
-- CREATE MATERIALIZED VIEW ... WITH DATA;

-- Create empty shell (populate later)
CREATE MATERIALIZED VIEW mv_semester_gpa_summary
WITH NO DATA AS ...;

-- Refresh strategies
REFRESH MATERIALIZED VIEW mv_semester_gpa_summary;
-- Blocks all reads during refresh (exclusive lock)

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_semester_gpa_summary;
-- Non-blocking: builds new data then swaps atomically
-- REQUIRES a UNIQUE index on the materialized view

-- Create required unique index for CONCURRENTLY
CREATE UNIQUE INDEX ON mv_semester_gpa_summary (semester, dept_name);

-- Automate refresh with pg_cron (PostgreSQL extension)
SELECT cron.schedule('refresh-mv-daily', '0 2 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_semester_gpa_summary');

-- Step 1: Create summary table
CREATE TABLE mv_dept_summary (
    dept_id          INT NOT NULL,
    dept_name        VARCHAR(80),
    total_students   INT,
    avg_gpa          DECIMAL(5,3),
    last_refreshed   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (dept_id)
);

-- Step 2: Stored procedure to refresh
DELIMITER //
CREATE PROCEDURE refresh_mv_dept_summary()
BEGIN
    TRUNCATE TABLE mv_dept_summary;
    INSERT INTO mv_dept_summary (dept_id, dept_name, total_students, avg_gpa, last_refreshed)
    SELECT
        d.dept_id,
        d.dept_name,
        COUNT(DISTINCT s.student_id),
        ROUND(AVG(s.gpa), 3),
        NOW()
    FROM departments d
    LEFT JOIN students s ON d.dept_id = s.dept_id
    GROUP BY d.dept_id, d.dept_name;
END //
DELIMITER ;

-- Step 3: Schedule with MySQL Event Scheduler
SET GLOBAL event_scheduler = ON;
CREATE EVENT ev_refresh_dept_summary
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO CALL refresh_mv_dept_summary();

-- Check which algorithm MySQL chose
SELECT TABLE_NAME, VIEW_DEFINITION, IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'university';

-- EXPLAIN shows <derived> for TEMPTABLE views:
EXPLAIN SELECT * FROM v_dept_avg WHERE avg_gpa > 3.2;
-- table: <derived2>  type: ALL  -- this is the temp table scan!

-- EXPLAIN shows base table directly for MERGE views:
EXPLAIN SELECT * FROM v_active_cs_students WHERE gpa > 3.5;
-- table: students  type: ref  key: idx_dept  -- base table index used!

CREATE VIEW v_student_transcript AS
SELECT
    s.student_id,
    CONCAT(s.first_name, ' ', s.last_name)      AS student_name,
    d.dept_name,
    c.course_code,
    c.title                                      AS course_title,
    c.credits,
    e.semester,
    e.grade,
    ROUND(g.points / g.max_points * 100, 1)      AS score_pct,
    CASE e.grade
        WHEN 'A'  THEN 4.0 WHEN 'A-' THEN 3.7
        WHEN 'B+' THEN 3.3 WHEN 'B'  THEN 3.0
        WHEN 'B-' THEN 2.7 WHEN 'C+' THEN 2.3
        WHEN 'C'  THEN 2.0 WHEN 'D'  THEN 1.0
        ELSE 0.0
    END                                          AS grade_points
FROM students  s
JOIN departments  d ON s.dept_id      = d.dept_id
JOIN enrollments  e ON s.student_id   = e.student_id
JOIN courses      c ON e.course_id    = c.course_id
JOIN grades       g ON e.student_id   = g.student_id
                    AND e.course_id   = g.course_id;

-- Example usage: calculate cumulative GPA
SELECT
    student_name,
    SUM(credits * grade_points) / SUM(credits) AS cumulative_gpa
FROM v_student_transcript
WHERE student_id = 1001
GROUP BY student_name;

-- Schema change: students table splits full_name into first + last
-- Application code queries v_student_profile — no change needed in app

CREATE OR REPLACE VIEW v_student_profile AS
SELECT
    s.student_id        AS id,
    CONCAT(s.first_name,' ',s.last_name) AS display_name,
    s.email,
    d.dept_code         AS department,
    s.gpa,
    s.enrolled_on       AS enrollment_date,
    s.is_active         AS active
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id;