Task 2 — Create, Manage & Access Control (MySQL) 35 marks

The schema (5), five queries (15) and three triggers (15). Access control is the CLO2 skill behind this task, so the GRANT/REVOKE section is included at the end.

2(a) — Tables, relations and integrity constraints

Every rule from the scenario is enforced by the DBMS, not by application code. Read the constraint names — uq_, chk_, fk_ — they are the answer to “where is that rule implemented?”.

university_hr — DDL

CREATE DATABASE IF NOT EXISTS university_hr;
USE university_hr;

CREATE TABLE department (
    dept_id         INT NOT NULL AUTO_INCREMENT,
    dept_name       VARCHAR(100) NOT NULL,
    office_location VARCHAR(120) NOT NULL,
    official_email  VARCHAR(120) NOT NULL,
    official_phone  VARCHAR(20)  NOT NULL,
    CONSTRAINT pk_department     PRIMARY KEY (dept_id),
    CONSTRAINT uq_department_name  UNIQUE (dept_name),
    CONSTRAINT uq_department_email UNIQUE (official_email)
) ENGINE=InnoDB;

CREATE TABLE designation (
    designation_id     INT NOT NULL AUTO_INCREMENT,
    designation_title  VARCHAR(80) NOT NULL,
    job_category       VARCHAR(50) NOT NULL,
    grade_level        INT NOT NULL,
    basic_salary_scale DECIMAL(12,2) NOT NULL,
    CONSTRAINT pk_designation        PRIMARY KEY (designation_id),
    CONSTRAINT uq_designation_title  UNIQUE (designation_title),
    CONSTRAINT chk_designation_grade CHECK (grade_level BETWEEN 1 AND 20),
    CONSTRAINT chk_designation_scale CHECK (basic_salary_scale >= 0)
) ENGINE=InnoDB;

CREATE TABLE employee (
    employee_id       INT NOT NULL AUTO_INCREMENT,
    full_name         VARCHAR(80)  NOT NULL,
    email             VARCHAR(120) NOT NULL,
    phone             VARCHAR(20)  NOT NULL,
    date_of_birth     DATE NOT NULL,
    gender            ENUM('Male','Female','Other') NOT NULL,
    national_id       VARCHAR(30)  NOT NULL,
    present_address   VARCHAR(200) NOT NULL,
    permanent_address VARCHAR(200) NOT NULL,
    joining_date      DATE NOT NULL,
    employment_type   ENUM('Faculty','Officer','Staff','Research Assistant',
                           'Lab Assistant','Support Staff') NOT NULL,
    employment_status ENUM('Active','On Leave','Resigned','Retired','Terminated')
                      NOT NULL DEFAULT 'Active',
    dept_id           INT NOT NULL,
    designation_id    INT NOT NULL,
    CONSTRAINT pk_employee        PRIMARY KEY (employee_id),
    CONSTRAINT uq_employee_email  UNIQUE (email),
    CONSTRAINT uq_employee_natid  UNIQUE (national_id),
    CONSTRAINT fk_employee_dept   FOREIGN KEY (dept_id)
        REFERENCES department (dept_id)   ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_employee_desig  FOREIGN KEY (designation_id)
        REFERENCES designation (designation_id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE attendance (
    attendance_id     INT NOT NULL AUTO_INCREMENT,
    employee_id       INT NOT NULL,
    attendance_date   DATE NOT NULL,
    check_in_time     TIME NULL,
    check_out_time    TIME NULL,
    attendance_status ENUM('Present','Absent','Late','On Leave','Holiday') NOT NULL,
    late_status       ENUM('Yes','No') NOT NULL DEFAULT 'No',
    remarks           VARCHAR(150) NULL,
    CONSTRAINT pk_attendance       PRIMARY KEY (attendance_id),
    CONSTRAINT uq_attendance_emp_day UNIQUE (employee_id, attendance_date),
    CONSTRAINT fk_attendance_emp   FOREIGN KEY (employee_id)
        REFERENCES employee (employee_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE leave_application (
    leave_app_id     INT NOT NULL AUTO_INCREMENT,
    employee_id      INT NOT NULL,
    leave_type       ENUM('Annual Leave','Sick Leave','Casual Leave',
                          'Maternity Leave','Study Leave','Unpaid Leave') NOT NULL,
    start_date       DATE NOT NULL,
    end_date         DATE NOT NULL,
    total_days       INT NOT NULL,
    reason           VARCHAR(200) NULL,
    application_date DATE NOT NULL,
    approval_status  ENUM('Pending','Approved','Rejected') NOT NULL DEFAULT 'Pending',
    approved_by      INT NULL,
    remarks          VARCHAR(150) NULL,
    CONSTRAINT pk_leave          PRIMARY KEY (leave_app_id),
    CONSTRAINT chk_leave_dates   CHECK (end_date >= start_date),
    CONSTRAINT chk_leave_days    CHECK (total_days >= 1),
    CONSTRAINT fk_leave_applicant FOREIGN KEY (employee_id)
        REFERENCES employee (employee_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_leave_approver  FOREIGN KEY (approved_by)
        REFERENCES employee (employee_id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE salary (
    salary_id            INT NOT NULL AUTO_INCREMENT,
    employee_id          INT NOT NULL,
    basic_salary         DECIMAL(12,2) NOT NULL,
    house_rent_allowance DECIMAL(12,2) NOT NULL DEFAULT 0,
    medical_allowance    DECIMAL(12,2) NOT NULL DEFAULT 0,
    transport_allowance  DECIMAL(12,2) NOT NULL DEFAULT 0,
    other_allowance      DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_deduction      DECIMAL(12,2) NOT NULL DEFAULT 0,
    gross_salary         DECIMAL(12,2) NOT NULL DEFAULT 0,
    net_salary           DECIMAL(12,2) NOT NULL DEFAULT 0,
    effective_date       DATE NOT NULL,
    salary_status        ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    active_emp           INT GENERATED ALWAYS AS
                         (CASE WHEN salary_status = 'Active' THEN employee_id END) VIRTUAL,
    CONSTRAINT pk_salary          PRIMARY KEY (salary_id),
    CONSTRAINT chk_salary_basic   CHECK (basic_salary >= 0),
    CONSTRAINT uq_salary_one_active UNIQUE (active_emp),
    CONSTRAINT fk_salary_emp      FOREIGN KEY (employee_id)
        REFERENCES employee (employee_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE leave_application_log (
    log_id          INT NOT NULL AUTO_INCREMENT,
    leave_app_id    INT NOT NULL,
    employee_id     INT NOT NULL,
    leave_type      VARCHAR(30) NOT NULL,
    start_date      DATE NOT NULL,
    end_date        DATE NOT NULL,
    total_days      INT NOT NULL,
    approval_status VARCHAR(15) NOT NULL,
    approved_by     INT NULL,
    deleted_at      DATETIME NOT NULL,
    deleted_by      VARCHAR(80) NOT NULL,
    CONSTRAINT pk_leave_log PRIMARY KEY (log_id)
) ENGINE=InnoDB;

Constraint → business rule map

Constraint	Enforces
`uq_attendance_emp_day`	At most one attendance record per employee per date.
`uq_salary_one_active`	Only one Active salary row per employee. The generated column is the employee id when Active and NULL otherwise — and MySQL allows many NULLs in a UNIQUE index, so inactive history rows never collide.
`fk_leave_approver`	The approver must be a real employee; NULL while the application is Pending.
`chk_leave_dates`, `chk_leave_days`	end ≥ start and at least one day of leave.
`ON DELETE RESTRICT` (dept, designation)	A department/designation that still has employees cannot be deleted.
`ON DELETE CASCADE` (attendance, leave, salary)	Child records of a deleted employee are removed with them — they have no meaning alone.
`ON DELETE SET NULL` (approved_by)	If the approver leaves the university, the application survives with an unknown approver.

ENGINE=InnoDB matters: MyISAM silently ignores foreign keys, and CHECK is only enforced from MySQL 8.0.16 onward.

2(b) — The five queries

Query 1 — Employee name, department, designation and active salary

Three joins from EMPLOYEE out to its two lookup tables, plus a join to SALARY filtered to the Active row. Putting s.salary_status = 'Active' in the ON clause (not WHERE) keeps the filter with the join it belongs to.

query-1.sql

SELECT e.full_name           AS employee_name,
       d.dept_name           AS department,
       des.designation_title AS designation,
       s.basic_salary, s.gross_salary, s.net_salary, s.effective_date
FROM employee e
JOIN department  d   ON e.dept_id        = d.dept_id
JOIN designation des ON e.designation_id = des.designation_id
JOIN salary      s   ON e.employee_id    = s.employee_id
                    AND s.salary_status  = 'Active';

Query 2 — Employees whose name contains “Rah”

Pattern matching with LIKE: % matches any sequence of characters, so %Rah% finds Rahman, Rahim, Abdur Rahaman…

query-2.sql

SELECT e.employee_id, e.full_name, d.dept_name AS department
FROM employee e
JOIN department d ON e.dept_id = d.dept_id
WHERE e.full_name LIKE '%Rah%';

Query 3 — Employees with a university-domain email

Anchor the wildcard on the left only — '%@iub.edu.bd' forces the address to end with the domain, so x@iub.edu.bd.fake.com is not matched.

query-3.sql

SELECT e.employee_id, e.full_name, e.email, d.dept_name AS department
FROM employee e
JOIN department d ON e.dept_id = d.dept_id
WHERE e.email LIKE '%@iub.edu.bd';

Query 4 — Employees earning above the average active net salary

A scalar subquery. It must be a subquery, not WHERE net_salary > AVG(net_salary) — aggregates are not allowed in WHERE, because WHERE is evaluated row-by-row before grouping.

query-4.sql

SELECT e.full_name, s.net_salary
FROM employee e
JOIN salary s ON e.employee_id = s.employee_id
             AND s.salary_status = 'Active'
WHERE s.net_salary > (SELECT AVG(net_salary)
                      FROM salary
                      WHERE salary_status = 'Active');

Query 5 — Departments whose average active net salary exceeds 60,000

Group per department, then filter the groups with HAVING. WHERE filters rows (before grouping); HAVING filters groups (after).

query-5.sql

SELECT d.dept_name,
       COUNT(*)                  AS employees,
       ROUND(AVG(s.net_salary),2) AS avg_net_salary
FROM department d
JOIN employee e ON d.dept_id     = e.dept_id
JOIN salary   s ON e.employee_id = s.employee_id
               AND s.salary_status = 'Active'
GROUP BY d.dept_id, d.dept_name
HAVING AVG(s.net_salary) > 60000;

What each query demonstrates (say this in the viva): Q1 multi-table inner join · Q2 & Q3 LIKE pattern matching · Q4 scalar subquery with an aggregate · Q5 GROUP BY + HAVING. Together they cover joins, pattern matching, subqueries and aggregation — the four query skills the rubric looks for.

2(c) — Triggers

Trigger 1 — automatic late detection (after 09:15)

BEFORE INSERT because we want to modify the row being stored. Only a BEFORE trigger may assign to NEW.col; in an AFTER trigger the row is already written.

trg_attendance_late_check

DELIMITER //
CREATE TRIGGER trg_attendance_late_check
BEFORE INSERT ON attendance
FOR EACH ROW
BEGIN
    IF NEW.check_in_time IS NOT NULL AND NEW.check_in_time > '09:15:00' THEN
        SET NEW.late_status = 'Yes';
        IF NEW.attendance_status = 'Present' THEN
            SET NEW.attendance_status = 'Late';
        END IF;
    ELSE
        SET NEW.late_status = 'No';
    END IF;
END;
//
DELIMITER ;

Sample inserts showing it work:

demo — late vs on-time

-- Arrives 09:41 → trigger flips late_status to 'Yes' and status to 'Late'
INSERT INTO attendance
      (employee_id, attendance_date, check_in_time, check_out_time, attendance_status)
VALUES (1, '2026-08-18', '09:41:00', '17:05:00', 'Present');

-- Arrives 08:55 → stays 'Present', late_status 'No'
INSERT INTO attendance
      (employee_id, attendance_date, check_in_time, check_out_time, attendance_status)
VALUES (2, '2026-08-18', '08:55:00', '17:00:00', 'Present');

SELECT employee_id, check_in_time, attendance_status, late_status
FROM attendance WHERE attendance_date = '2026-08-18';

employee_id	check_in_time	attendance_status	late_status
1	09:41:00	Late (was ‘Present’)	Yes
2	08:55:00	Present	No

Trigger 2 — recalculate gross and net salary

Gross = Basic + House Rent + Medical + Transport + Other · Net = Gross − Total Deduction

trg_salary_recalc_update / _insert

DELIMITER //
CREATE TRIGGER trg_salary_recalc_update
BEFORE UPDATE ON salary
FOR EACH ROW
BEGIN
    SET NEW.gross_salary = NEW.basic_salary
                         + NEW.house_rent_allowance
                         + NEW.medical_allowance
                         + NEW.transport_allowance
                         + NEW.other_allowance;
    SET NEW.net_salary   = NEW.gross_salary - NEW.total_deduction;
END;
//
DELIMITER ;

-- Same rule on INSERT, so a new row is never stored with a wrong total
DELIMITER //
CREATE TRIGGER trg_salary_recalc_insert
BEFORE INSERT ON salary
FOR EACH ROW
BEGIN
    SET NEW.gross_salary = NEW.basic_salary + NEW.house_rent_allowance
                         + NEW.medical_allowance + NEW.transport_allowance
                         + NEW.other_allowance;
    SET NEW.net_salary   = NEW.gross_salary - NEW.total_deduction;
END;
//
DELIMITER ;

demo — an increment

UPDATE salary
SET basic_salary = 85000, house_rent_allowance = 34000,
    medical_allowance = 5000, transport_allowance = 4000,
    other_allowance = 2000,  total_deduction = 6000
WHERE salary_id = 1;

-- gross = 85000+34000+5000+4000+2000 = 130000
-- net   = 130000 - 6000              = 124000   (both set by the trigger)

The task only asks for the UPDATE trigger; adding the INSERT twin is the correct engineering answer — otherwise a freshly inserted row can hold a wrong total until someone updates it.

Trigger 3 — archive deleted leave applications

AFTER DELETE, using the OLD row: the deletion must actually succeed before we log it. NOW() stamps the time and CURRENT_USER() records who did it.

trg_leave_delete_log

DELIMITER //
CREATE TRIGGER trg_leave_delete_log
AFTER DELETE ON leave_application
FOR EACH ROW
BEGIN
    INSERT INTO leave_application_log
        (leave_app_id, employee_id, leave_type, start_date, end_date,
         total_days, approval_status, approved_by, deleted_at, deleted_by)
    VALUES
        (OLD.leave_app_id, OLD.employee_id, OLD.leave_type, OLD.start_date,
         OLD.end_date, OLD.total_days, OLD.approval_status, OLD.approved_by,
         NOW(), CURRENT_USER());
END;
//
DELIMITER ;

DELETE FROM leave_application WHERE leave_app_id = 3;
SELECT * FROM leave_application_log;

Trigger cheat sheet. NEW exists for INSERT and UPDATE; OLD exists for UPDATE and DELETE. You can only assign to NEW, and only in a BEFORE trigger. DELIMITER // is needed because the trigger body itself contains semicolons.

2(d) — User access control (DCL)

HR data is sensitive, so privileges follow the principle of least privilege: each role gets exactly the rights its job needs and nothing more.

GRANT / REVOKE

-- 1. HR manager: full control over the HR data
CREATE USER 'hr_manager'@'localhost' IDENTIFIED BY 'StrongPass#1';
GRANT SELECT, INSERT, UPDATE, DELETE ON university_hr.* TO 'hr_manager'@'localhost';

-- 2. HR officer: day-to-day operations, but must not touch salary
CREATE USER 'hr_officer'@'localhost' IDENTIFIED BY 'StrongPass#2';
GRANT SELECT, INSERT, UPDATE ON university_hr.employee          TO 'hr_officer'@'localhost';
GRANT SELECT, INSERT, UPDATE ON university_hr.attendance        TO 'hr_officer'@'localhost';
GRANT SELECT, INSERT, UPDATE ON university_hr.leave_application TO 'hr_officer'@'localhost';
GRANT SELECT                 ON university_hr.salary            TO 'hr_officer'@'localhost';

-- 3. Accounts officer: salary only
CREATE USER 'accounts_officer'@'localhost' IDENTIFIED BY 'StrongPass#3';
GRANT SELECT, INSERT, UPDATE ON university_hr.salary   TO 'accounts_officer'@'localhost';
GRANT SELECT                 ON university_hr.employee TO 'accounts_officer'@'localhost';

-- 4. Department head: read-only, and only the columns that are not personal
CREATE USER 'dept_head'@'localhost' IDENTIFIED BY 'StrongPass#4';
GRANT SELECT (employee_id, full_name, email, employment_type, employment_status, dept_id)
      ON university_hr.employee TO 'dept_head'@'localhost';
GRANT SELECT ON university_hr.attendance TO 'dept_head'@'localhost';

-- Withdrawing a privilege
REVOKE DELETE ON university_hr.* FROM 'hr_manager'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'hr_officer'@'localhost';

Role	Privileges	Justification
hr_manager	SELECT, INSERT, UPDATE, DELETE on all tables	Owns the HR process end to end.
hr_officer	Read/write employee, attendance, leave; read-only salary	Runs daily HR operations but must not change pay.
accounts_officer	Read/write salary; read employee	Payroll needs salary and employee identity only.
dept_head	Column-level SELECT on employee + read attendance	Can monitor their team without seeing NID, addresses or salary.

Column-level grants are the neat trick here — they hide national_id, present_address and date_of_birth without needing a separate view.

SQL refresher: DDL & CREATE TABLE → · Aggregates & GROUP BY → · Subqueries →