67 questions the examiner is likely to ask on this project, grouped by task, each with a model answer short enough to say out loud. Click a question to reveal the answer — try to answer first.
The viva is worth roughly 44 of the 120 marks — more than any single written task. Written work that you cannot defend loses those marks, so read every answer here beside the task page it belongs to.
Expect “why did you model it that way?” — every answer should name a business rule from the scenario.
T1.1What is the difference between an entity, an entity set and an entity type?
An entity is one real-world object (the employee A.B.M Saeduzzaman). An entity set is the collection of all entities of the same type currently in the database (all rows of EMPLOYEE). The entity type is the schema — the name plus the attribute list.
T1.2Define super key, candidate key and primary key, with an example from your design.
Super key = any attribute set that uniquely identifies a tuple, e.g. {employee_id, full_name}. Candidate key = a minimal super key — employee_id, email, national_id. Primary key = the candidate key chosen by the designer: employee_id. So Super ⊇ Candidate ⊇ Primary.
T1.3Why did you choose employee_id as the primary key instead of email or national_id?
It is a surrogate key: short, integer (fast to compare and index), never NULL, and it never changes. An email can be re-issued and a national ID can be corrected — a changing PK would ripple through every foreign key.
T1.4What are the attribute types in the ER model? Which do you have?
Simple/atomic, composite, single-valued, multi-valued (double oval), derived (dashed oval) and key (underlined). full_name and the addresses are naturally composite; gross_salary and net_salary are derived (computed from basic + allowances − deduction); total_days is derived from start and end date.
T1.5Why did you store gross_salary and net_salary if they are derived?
For an audited payroll history you want the value that was actually paid, frozen at that time, even if the formula or an allowance rule later changes. Consistency is then guaranteed by the recalculation trigger rather than by hand.
T1.6What is a weak entity set? Do you have one?
A weak entity set has no primary key of its own; it is identified by the primary key of its owner plus a discriminator, through an identifying relationship (double rectangle, double diamond, dashed underline, total participation). In my design ATTENDANCE and SALARY are modelled as strong entities with a surrogate key — but identified as {employee_id, attendance_date} and {employee_id, effective_date} they would be textbook weak entities.
T1.7Explain mapping cardinality and participation in your diagram.
All six relationships are 1 : N. Participation is total on the many side of works-in, holds, has, applies-for and draws (an attendance row cannot exist without an employee), and partial for approves, because a Pending application has no approver yet.
T1.8Why does LEAVE_APPLICATION have two links to EMPLOYEE?
They are two different roles of the same entity set: employee_id is the applicant and approved_by is the approver (a department head or HR officer, who is also an employee). Roles are labelled on the connecting lines in Chen notation.
T1.9What is a domain, and how did you enforce it?
The set of permitted values for an attribute. Enforced by the column data type plus constraints: ENUM for fixed value sets like employment_status, CHECK for grade_level BETWEEN 1 AND 20, NOT NULL for mandatory attributes, UNIQUE for candidate keys.
T1.10How do you convert your ER diagram to relations?
Each entity set becomes a table with its attributes; the primary key stays the primary key. For a 1 : N relationship, the primary key of the “one” side becomes a foreign key on the “many” side — that is why dept_id and designation_id sit in EMPLOYEE. An M : N relationship would need its own junction table; a weak entity takes owner-PK + discriminator as its composite key.
Task 2 — MySQL, queries, triggers & access control 12 viva marks
The largest viva block. Be ready to explain any constraint, any clause and any trigger line.
T2.1What are the integrity constraints and which one enforces which rule?
Domain (data type/ENUM/CHECK), entity integrity (PRIMARY KEY: unique + not null), referential integrity (FOREIGN KEY), and user-defined (UNIQUE, CHECK). Concretely: uq_attendance_emp_day = one attendance row per employee per day; uq_salary_one_active = one Active salary; chk_leave_dates = end ≥ start.
T2.2How exactly does the “only one Active salary per employee” constraint work?
A generated column active_emp = CASE WHEN salary_status='Active' THEN employee_id END plus UNIQUE(active_emp). For an Active row it holds the employee id, so a second Active row for the same employee collides. For Inactive rows it is NULL — and SQL allows any number of NULLs in a unique index, so the history never collides.
T2.3Explain your ON DELETE choices.
RESTRICT on department/designation: you must not delete a department that still has employees. CASCADE on attendance, leave and salary: these have no meaning without their employee. SET NULL on approved_by: if the approver leaves, keep the application but forget the approver. The alternative is NO ACTION (the SQL default).
T2.4Why ENGINE=InnoDB?
MyISAM parses but silently ignores FOREIGN KEY, and gives no transactions. InnoDB provides referential integrity, row-level locking, ACID transactions and crash recovery via its redo log — everything an HR system needs.
T2.5What is the difference between WHERE and HAVING? (Query 5)
WHERE filters rows before grouping and cannot contain an aggregate. HAVING filters groups after GROUP BY has run, so it can. Order of evaluation: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
T2.6Why did Query 4 need a subquery?
Because you cannot write WHERE net_salary > AVG(net_salary): WHERE is evaluated per row, before any aggregation exists. The scalar subquery computes the single average first, and the outer query compares each row against it.
T2.7What is the difference between % and _ in LIKE?
% matches any sequence of zero or more characters; _ matches exactly one. So '%Rah%' finds Rah anywhere in the name, while '_ah%' would require it at the second position.
T2.8What is a trigger and when would you use one?
A named block of procedural SQL that the DBMS executes automatically on an INSERT, UPDATE or DELETE event. Use it for rules that must hold no matter which application writes the data — auto-computing derived values, validation, and audit logging.
T2.9Why is the late-attendance trigger BEFORE INSERT and not AFTER?
Only a BEFORE trigger can assign to NEW.column and change the row that is about to be stored. In an AFTER trigger the row is already written, so you would need a second UPDATE — which would also re-fire triggers.
T2.10When do you use NEW and OLD?
NEW = the incoming row: available in INSERT and UPDATE. OLD = the previous row: available in UPDATE and DELETE. That is why the delete-log trigger reads OLD.*.
T2.11Why do you need DELIMITER // around a trigger?
The trigger body contains semicolons. Without changing the client delimiter, MySQL would end the CREATE TRIGGER statement at the first internal ; and throw a syntax error.
T2.12Why log deleted leave applications instead of just deleting them?
Audit and accountability — HR data is regulated. The log records what was deleted, NOW() when, and CURRENT_USER() by whom, so a deletion can be reviewed or reconstructed. In production you would prefer a soft delete (an is_deleted flag) so nothing is lost at all.
T2.13What is DCL and how did you apply least privilege?
Data Control Language: GRANT and REVOKE. Each role gets only what its job needs — hr_officer may edit attendance and leave but only read salary; accounts_officer may edit salary but sees only identifying employee columns; dept_head has a column-level SELECT that hides national_id, addresses and date of birth.
T2.15How would you speed up a slow attendance report?
Add an index matching the query: CREATE INDEX idx_att_date ON attendance(attendance_date), or a composite (employee_id, attendance_date) — which the UNIQUE constraint already provides. Then confirm with EXPLAIN that the plan changed from a full scan to an index range scan.
Task 3 — RAID & recovery 12 viva marks
Heaviest viva-to-writing ratio in the whole project. Know XOR cold, and know RAID’s limits.
T3.1What does RAID stand for and why use it?
Redundant Array of Independent (originally Inexpensive) Disks — several cheap disks presented as one logical disk, to gain performance (parallel access via striping) and/or reliability (redundancy via mirroring or parity).
T3.2Describe RAID level 4.
Block-level striping across the data disks plus one dedicated parity disk. Each stripe’s parity block is the bitwise XOR of the corresponding data blocks. It survives any single disk failure and costs only one disk of overhead.
T3.3Why XOR, and not addition?
XOR is its own inverse and is associative and commutative: if P = A ⊕ B ⊕ C then A = P ⊕ B ⊕ C. It never carries, so the parity block is exactly the same width as a data block, and it is a single fast hardware gate.
T3.4Show how you computed P.
Column by column, even parity: an even number of 1s → 0, an odd number → 1. Running XOR: 1010 ⊕ 1100 = 0110 → ⊕ 0111 = 0001 → ⊕ 1001 = 1000 → ⊕ 0011 = 1011 → ⊕ 1110 = 0101.
T3.5D5 fails. Recover it.
D5 = P ⊕ D1 ⊕ D2 ⊕ D3 ⊕ D4 ⊕ D6 = 0101 ⊕ 1010 = 1111 → ⊕ 1100 = 0011 → ⊕ 0111 = 0100 → ⊕ 1001 = 1101 → ⊕ 1110 = 0011, which is the original Salary block.
T3.6How many disks can RAID 4 lose?
Exactly one per stripe. With two failures you have one parity equation and two unknowns, so the data is unrecoverable. RAID 6 adds a second, independent parity block to survive two failures.
T3.7What is RAID 4’s main weakness?
The dedicated parity disk is a write bottleneck: every write anywhere in the array must also update P, so writes serialise on one disk, which also wears out first. RAID 5 fixes this by distributing the parity blocks across all disks.
T3.8Does a write have to read all six data disks to update parity?
No. Use P_new = P_old ⊕ D_old ⊕ D_new — read the old data block and the old parity, write the new data block and the new parity: 2 reads + 2 writes regardless of array size.
T3.9Compare RAID 0, 1, 4, 5 and 6.
RAID 0 = striping only (fast, zero redundancy). RAID 1 = mirroring (50 % overhead, fast reads). RAID 4 = striping + dedicated parity. RAID 5 = striping + distributed parity (no bottleneck) — the common choice. RAID 6 = two parity blocks, tolerates two failures.
T3.10Is RAID a backup?
No. RAID protects only against disk/media failure. A wrong DELETE, a dropped table, corruption or ransomware is faithfully written to every disk. You still need database backups plus log-based recovery.
T3.11What other recovery mechanisms does a DBMS use?
Write-ahead logging (WAL) with undo of uncommitted and redo of committed transactions after a crash, checkpoints to bound the amount of log to replay, shadow paging, and periodic full + incremental dumps kept off-site.
T3.12Which ACID property is recovery mainly about?
Durability (committed work survives a crash) and Atomicity (a partly-done transaction is rolled back completely). The A, C, I, D are Atomicity, Consistency, Isolation, Durability.
Task 4 — Normalization 10 viva marks
You must be able to state each normal form in one sentence and point to the exact dependency you removed.
T4.1What is normalization and why do it?
A step-by-step decomposition of relations, guided by functional dependencies, to remove redundancy and the insertion, update and deletion anomalies it causes — while staying lossless and (ideally) dependency-preserving.
T4.2Define a functional dependency.
X → Y means: any two tuples that agree on X must agree on Y. X determines Y. Example: Dept_ID → Dept_Name.
T4.3State 1NF, 2NF and 3NF in one line each.
1NF: all values atomic, no repeating groups. 2NF: 1NF + no partial dependency on part of a composite key. 3NF: 2NF + no transitive dependency of a non-key attribute on another non-key attribute. Slogan: every non-key attribute depends on the key, the whole key and nothing but the key.
T4.4Which dependency violated 2NF in your table?
Emp_ID → Emp_Name, Dept_ID, Designation. The key was {Emp_ID, Effective_Date}, so those attributes depended on only part of the key — a partial dependency. I split them into EMPLOYEE and EMP_SALARY.
T4.5Which dependency violated 3NF?
Two transitive ones: Emp_ID → Dept_ID → Dept_Name, and Emp_ID → Designation → Basic_Scale. I extracted DEPARTMENT and DESIGNATION and left foreign keys behind.
T4.6Can a table in 1NF ever be automatically in 2NF?
Yes — if the primary key is a single attribute, no partial dependency is possible, so 1NF ⇒ 2NF.
T4.7What is BCNF and are your tables in it?
BCNF: for every non-trivial FD X → Y, X must be a super key. In my 3NF tables every determinant (Emp_ID, Dept_ID, Designation_ID, {Emp_ID, Effective_Date}) is a candidate key, so yes — they are in BCNF too. 3NF is slightly weaker: it also allows X → Y when Y is a prime attribute.
T4.8What is a lossless decomposition?
Decomposing R into R1 and R2 is lossless if joining them back gives exactly R — guaranteed when the common attributes R1 ∩ R2 form a key of R1 or of R2. Here the shared column is always the primary key of the extracted table, so no rows are invented or lost.
T4.9Give a concrete anomaly your normalization removed.
Update anomaly: renaming “CSE” previously meant editing every salary row of every CSE employee, and missing one produced inconsistent data. After 3NF it is a single-row UPDATE in DEPARTMENT. Insertion: a new department can now exist before it has any employee.
T4.10When would you deliberately denormalize?
For read-heavy reporting or a data warehouse, where joins dominate the cost and the data is loaded in bulk rather than edited (star schemas). For this OLTP HR system, integrity wins — keep it normalized.
T4.11What is 4NF?
Removes multi-valued dependencies: independent multi-valued facts about the same key (e.g. an employee’s phone numbers and their skills) must not be stored in one relation, or you get a spurious cross product.
Task 5 — Hashing & indexing 13 viva marks
The biggest single viva block. Be ready to re-draw a split live and to justify B+ over B and over hashing.
T5.1What is an index and what does it cost?
An auxiliary access structure mapping a search key to record locations, turning an O(n) table scan into an O(log n) lookup. Cost: extra storage, and slower INSERT/UPDATE/DELETE because every index must also be maintained.
T5.2Difference between a primary/clustered and a secondary index?
A clustered (primary) index determines the physical order of the rows — there can be only one, and in InnoDB the rows live in its leaves. A secondary index is a separate structure that points back to the primary key, so it needs one extra lookup.
T5.3Difference between dense and sparse index?
A dense index has an entry for every search-key value; a sparse index has an entry only per block, so it is smaller but requires a scan within the block. A sparse index is only possible on a sorted (clustered) file.
T5.4What is a B-Tree? What makes it balanced?
A multi-way search tree where every node holds between ⌈n/2⌉−1 and n−1 keys, and all leaves are at the same level. Balance is maintained by splitting on overflow (pushing the median up) and merging/redistributing on underflow — the tree grows and shrinks only at the root.
T5.5Why did the height increase when you inserted 19?
The leaf split promoted 17 into the root, giving the root four keys [11 13 15 17] — an overflow. Splitting the root pushes its median 13 into a brand-new root, and that is the only way a B/B+ tree grows taller, which is why it stays balanced.
T5.6Key difference between a B-Tree and a B+ Tree?
In a B-Tree data/record pointers sit in every node and each key appears once. In a B+ Tree all keys and all data pointers are in the leaves, internal nodes hold only separators (duplicated keys), and the leaves are linked — so every search costs exactly the height and range scans are sequential.
T5.7Copy up vs push up — explain.
Splitting a B+ tree leafcopies the separator up: the key must stay in the leaf because all data lives there. Splitting a B+ tree internal node (or any B-Tree node) pushes the median up and removes it from below — that is why 16 vanished from the level below the root.
T5.8Why do databases prefer B+ Trees?
Higher fan-out (internal nodes carry no data ⇒ more keys per disk page ⇒ shorter tree ⇒ fewer disk I/Os), uniform search cost, and the linked leaves make BETWEEN, ORDER BY and full scans cheap.
T5.9Trace a search for employee_id 21 in your B+ tree.
21 > 16 → right child [18 20]; 21 ≥ 20 → third child → leaf [20 21 22]; find 21 there. Three node reads = the height.
T5.10How would you answer BETWEEN 13 AND 20?
Descend once to the leaf containing 13 ([12 13]), then follow the leaf chain right, emitting keys until you pass 20. Only one root-to-leaf traversal is needed — a B-Tree would have to traverse up and down repeatedly.
T5.11What is hashing, and when does it beat a B+ tree?
A hash function maps a key directly to a bucket address, giving O(1) equality lookup. It beats a tree for exact-match queries (e.g. by national_id), but is useless for ranges or ORDER BY because hashing destroys ordering.
T5.12What are collisions and how are they handled?
Two keys hashing to the same bucket. Handled by chaining (overflow buckets linked to the primary bucket) or open addressing. Too many overflow chains degrade lookups to O(n), which is why static hashing needs rehashing as data grows.
T5.13Static vs dynamic hashing?
Static hashing fixes the number of buckets, so it degrades as the file grows and wastes space when it shrinks. Dynamic hashing (extendable or linear) grows and shrinks the bucket directory as needed, so performance stays flat without a full rebuild.
T5.14Which index would you actually create on this HR database?
The clustered B+ tree on employee_id (automatic from the PRIMARY KEY), a composite index on (employee_id, attendance_date) for attendance lookups (already implied by the UNIQUE constraint), and one on salary(employee_id, salary_status) for the “active salary” queries.
General / cross-cutting asked anywhere
Openers and closers the examiner uses to check you understand the project as a whole.
GEN.1Explain your project in one minute.
A University HR Management database: six entities — DEPARTMENT, DESIGNATION, EMPLOYEE, ATTENDANCE, LEAVE_APPLICATION and SALARY — implemented in MySQL with full integrity constraints, three triggers (late detection, salary recalculation, delete audit) and role-based access control. It is normalized to 3NF/BCNF, indexed with a B+ tree on employee_id, and its storage is protected by RAID 4 parity.
GEN.2Which queries from the scenario does your design answer?
Employees per department (join EMPLOYEE–DEPARTMENT); each employee’s designation (join DESIGNATION); salary structure (SALARY where status = Active); presence on a date (ATTENDANCE by employee_id + date, guaranteed unique); pending applications (COUNT with approval_status = 'Pending'); who approved (join back to EMPLOYEE through approved_by).
GEN.3What would you improve if you had more time?
A leave-balance/entitlement table per leave type and year, soft deletes instead of hard deletes, a view exposing only non-sensitive employee columns, an employee–designation history table (the current design keeps only the current designation), and stored procedures for the approval workflow.
GEN.4What is the difference between a database, a DBMS and a database system?
The database is the stored, logically-related data. The DBMS is the software that defines, creates, maintains and controls access to it. The database system is both together with the users and the applications.
GEN.5How does the DBMS keep two HR officers from corrupting the same row?
Transactions and isolation: InnoDB uses row-level locking plus MVCC so concurrent reads and writes are serializable in effect. Combined with the UNIQUE constraints, two officers cannot create two attendance rows for the same employee and date.
Three habits that earn viva marks. (1) Always tie a design choice back to a sentence in the scenario — “the system should not allow more than one attendance record per employee per date” → UNIQUE(employee_id, attendance_date). (2) Give the definition first, then your example. (3) Name the trade-off you accepted — normalization vs joins, RAID 4 parity disk vs write throughput, index speed vs write cost. Examiners reward the trade-off sentence more than the definition.