Progressive Project — University HR Management Database
CSE 303 · Summer 2026 · CLO2 / PLO-c · 120 marks (scaled to 20). The whole project worked end-to-end: design, MySQL implementation, RAID-4 recovery, normalization and indexing — plus a viva bank for the oral marks.
2310826 · Name: A.B.M Saeduzzaman · Section: 2 · Course: CSE 303 (Database Management, 4 credits) · Semester: Summer 2026. Mark map — where the 120 marks sit
| Task | MA | What it asks for |
|---|---|---|
| Task 1 — Designing the database | 20 | Entities, attributes, domains, candidate & primary keys + the full ER diagram. |
| Task 2 — Create, manage & access control (MySQL) | 35 | CREATE TABLE with constraints, 5 queries, 3 triggers, GRANT/REVOKE. |
| Task 3 — Recovery mechanism (RAID 4) | 18 | RAID 4 structure, parity by XOR, and recovering a failed disk. |
| Task 4 — Normalization | 22 | UNF → 1NF → 2NF → 3NF on a salary/employee report table. |
| Task 5 — Hashing & indexing | 25 | B-Tree and B+ Tree built key-by-key for employee IDs 10–22. |
| Total | 120 | A large share is VIVA — see the question bank. |
The scenario
A university needs a small Human Resource Management System for its HR office: employees, departments, job designations, daily attendance, leave applications and salary records. Departments include CSE, EEE, Business Administration, Registrar Office, Finance Office, Library and IT Office.
Things the database must be able to answer
- Which employees work in a given department?
- What designation does each employee hold?
- What is the current salary structure of an employee?
- Was an employee present on a specific date?
- How many leave applications are pending, and who approved a given request?
Business rules → relationships
| Relationship | Cardinality | Rule |
|---|---|---|
| DEPARTMENT — EMPLOYEE | 1 : N | A department has many employees; an employee belongs to exactly one department (total participation on the employee side). |
| DESIGNATION — EMPLOYEE | 1 : N | One designation is held by many employees; each employee has exactly one current designation. |
| EMPLOYEE — ATTENDANCE | 1 : N | Many attendance rows per employee, but at most one row per employee per date (unique constraint). |
| EMPLOYEE — LEAVE_APPLICATION | 1 : N | An employee submits many applications; each application belongs to one applicant. |
| EMPLOYEE — LEAVE_APPLICATION (approves) | 1 : N (recursive) | The approver is also an employee → a second, role-named FK (approved_by) back to EMPLOYEE. Nullable while Pending. |
| EMPLOYEE — SALARY | 1 : N | Salary history over time; only one row may be Active per employee at any moment. |
Enumerated domains (fixed value sets)
| Attribute | Allowed values |
|---|---|
| employment_type | Faculty · Officer · Staff · Research Assistant · Lab Assistant · Support Staff |
| employment_status | Active · On Leave · Resigned · Retired · Terminated |
| attendance_status | Present · Absent · Late · On Leave · Holiday |
| leave_type | Annual · Sick · Casual · Maternity · Study · Unpaid Leave |
| approval_status | Pending · Approved · Rejected |
| salary_status | Active · Inactive |
These become ENUM(...) columns in MySQL — the DBMS itself then enforces the domain, so no invalid value can ever reach the table. That is the “domain constraint” mark in Task 1.
Three constraints that carry the design marks
- One attendance row per employee per day →
UNIQUE (employee_id, attendance_date). - Only one Active salary per employee → a generated column that is the employee id only when the row is Active, then
UNIQUEon it. - The approver is an employee → a recursive foreign key
approved_by → employee, nullable so a Pending application has no approver yet.
Where to go next
Task 1 — Designing the database
Entities, attributes, domains, candidate & primary keys + the full ER diagram.
Task 2 — Create, manage & access control (MySQL)
CREATE TABLE with constraints, 5 queries, 3 triggers, GRANT/REVOKE.
Task 3 — Recovery mechanism (RAID 4)
RAID 4 structure, parity by XOR, and recovering a failed disk.
Task 4 — Normalization
UNF → 1NF → 2NF → 3NF on a salary/employee report table.
Task 5 — Hashing & indexing
B-Tree and B+ Tree built key-by-key for employee IDs 10–22.
Viva Question Bank
~60 likely questions across all five tasks, each with a short model answer.