Progressive Project — University HR Database
The full 120-mark CSE 303 project solved: ER design, MySQL schema + queries + triggers + access control, RAID-4 recovery, normalization to 3NF, B/B+ trees — plus a viva question bank.
Sections
Project Overview
The scenario, the business rules → relationships, the enumerated domains, and where all 120 marks sit.
Task 1 — Database Design
Entities, attributes, domains, candidate & primary keys, plus the full Chen-notation ER diagram with cardinality and participation.
Task 2 — MySQL Implementation
The complete DDL with every constraint explained, the five queries, three triggers with sample runs, and role-based GRANT/REVOKE.
Task 3 — RAID 4 Recovery
The RAID-4 structure drawn out, parity by XOR two ways, and the bit-by-bit recovery of the failed disk D5.
Task 4 — Normalization
An unnormalized HR table taken through 1NF → 2NF → 3NF, with the functional dependency removed at each step.
Task 5 — B-Tree & B+ Tree
Insert 10–22 step by step into both trees, the final diagrams, B vs B+ comparison, and where hashing fits.
Possible Viva Questions
~60 likely oral questions across all five tasks, each with a short model answer you can say out loud.
Quick summary
PROJECT: University HR Management Database · 120 marks (scaled to 20) · CLO2 / PLO-c.
ENTITIES (6): DEPARTMENT · DESIGNATION · EMPLOYEE · ATTENDANCE ·
LEAVE_APPLICATION · SALARY. All relationships are 1 : N.
approves = a SECOND role-based link EMPLOYEE → LEAVE_APPLICATION (approved_by).
TASK 1 (20) design: attributes + domains + candidate/primary keys + ER diagram.
TASK 2 (35) MySQL: CREATE TABLE + constraints · 5 queries · 3 triggers · GRANT/REVOKE.
TASK 3 (18) RAID 4: 6 data disks + dedicated parity P.
P = D1⊕…⊕D6 = 0101 ; recover D5 = P ⊕ (all survivors) = 0011.
TASK 4 (22) normalization: UNF → 1NF (atomic, PK {Emp_ID, Effective_Date})
→ 2NF (drop partial dep. on Emp_ID) → 3NF (drop transitive Dept_ID→Dept_Name,
Designation→Basic_Scale). Result: 4 relations, also BCNF.
TASK 5 (25) indexing: keys 10…22, max 3 keys/node.
B-Tree → root [13]; [11] , [15 17 19]; leaves [10][12][14][16][18][20 21 22].
B+ Tree → root [16]; [12 14] , [18 20]; leaves [10 11][12 13][14 15][16 17][18 19][20 21 22], chained.
KEY CONSTRAINTS: UNIQUE(employee_id, attendance_date) · one ACTIVE salary via a
generated column + UNIQUE · approved_by FK nullable (partial participation).
~44 of the 120 marks are VIVA — see the question bank.