Reasoning. “By designation” = GROUP BY designation. “Who taught a course” forces a join to teaches. Both filters (department and year) are row filters, so they go in WHERE, before grouping.
The trap: an instructor may teach several sections in 2025, so the join produces several rows for that instructor. Without DISTINCT you would count sections, not instructors.
One row per designation, each with the number of distinct CSE instructors who taught in 2025.
Q1(b)smallFind the total number of (distinct) students who have taken course sections taught by the instructor with ID 10101. [3]
Reasoning. A section is identified by the full four-part key (courseno, secno, semester, year) — all four must appear in the join condition, otherwise you match a section of the same course from a different semester.
The trap: a student may take several sections taught by 10101, so COUNT(DISTINCT student_id) is required — the question says “distinct”.
Equivalent with a subquery: SELECT COUNT(DISTINCT student_id) FROM enrolls WHERE (courseno, secno, semester, year) IN (SELECT courseno, secno, semester, year FROM teaches WHERE instructor_id = 10101);
Q1(c)smallList all programs along with the number of students in each Program. [2]
Reasoning. Single table, one group per program. COUNT(*) counts the rows in each group.
Add ORDER BY total_students DESC if the examiner asks for “most students first”. COUNT(*) is right here because student-id is the key, so every row is a distinct student.
Part 2 — Views, Integrity Constraints, Authorization 10 marks
Q2bigGiven faculty (id, name, dept_name), create a view to show the count of faculties for each department. Then use the view to find the largest department (dept name and faculty count) in terms of number of faculties. [3 + 3]
Why the subquery and not ORDER BY … LIMIT 1?ORDER BY faculty_count DESC LIMIT 1 also works and would be accepted — but it returns only one department if two departments tie for the largest. The MAX subquery returns all tied departments, which is the safer answer.
Remember the definition marks: a view is a stored query / virtual table — creating it does not store any data; the DBMS rewrites the outer query against faculty when the view is used.
Q3bigWhat is referential integrity? For faculty (id, name, dept_name) and dept (dept_id, dept_name, budget), with a CREATE TABLE SQL example explain how you will implement referential integrity and different cascading integrity for the faculty table. [1 + 3]
Definition (1 mark).
Referential integrity ensures that a value appearing in one relation for a given set of attributes also appears for a certain set of attributes in another relation. Formally: if A is the primary key of S, then A is a foreign key of R if every value of A appearing in R also appears in S. It prevents “orphan” rows — a faculty member cannot belong to a department that does not exist.
Implementation (3 marks).
Q3
-- parent tableCREATETABLEdept(dept_idINTNOTNULL,dept_nameVARCHAR(40)NOTNULL,budgetDECIMAL(12,2),CONSTRAINTpk_deptPRIMARYKEY(dept_id),CONSTRAINTuq_dept_nameUNIQUE(dept_name)-- needed: an FK must reference a KEY);-- child table with referential integrity + cascading actionsCREATETABLEfaculty(idINTNOTNULL,nameVARCHAR(50)NOTNULL,dept_nameVARCHAR(40),CONSTRAINTpk_facultyPRIMARYKEY(id),CONSTRAINTfk_faculty_deptFOREIGNKEY(dept_name)REFERENCESdept(dept_name)ONDELETECASCADEONUPDATECASCADE);
The detail most students miss:dept_name is not the primary key of dept (dept_id is). A foreign key may only reference a key, so dept_name must be declared UNIQUE first — otherwise the CREATE TABLE fails.
The “different cascading integrity” options:
Clause
Effect on faculty when a dept row is deleted/updated
ON DELETE CASCADE
All faculty rows of that department are deleted too.
ON UPDATE CASCADE
Renaming the department automatically updates faculty.dept_name.
ON DELETE SET NULL
The faculty rows survive with dept_name = NULL (the column must be nullable).
ON DELETE SET DEFAULT
The faculty rows move to the column’s declared DEFAULT department.
ON DELETE RESTRICT / NO ACTION
The delete is refused while faculty rows still reference the department.
For a real faculty table, ON DELETE SET NULL or RESTRICT is the defensible choice — you do not want closing a department to erase the people.
Q4bigWrite a trigger named “emp_after_update” that fires after a salary is updated in the employee table. Insert into salary_log only if the new salary is not the same as the old one, and always insert a row in employee_audit. [5]
Q4 — emp_after_update
DELIMITER$$CREATETRIGGERemp_after_updateAFTERUPDATEONemployeeFOREACHROWBEGIN-- only log a real change of salaryIFNEW.salary<>OLD.salaryTHENINSERTINTOsalary_log(emp_id,old_salary,new_salary,changed_at)VALUES(NEW.emp_id,OLD.salary,NEW.salary,NOW());ENDIF;-- always record the update in the audit tableINSERTINTOemployee_audit(emp_id,action,note,action_time)VALUES(NEW.emp_id,'update','Employee record updated',NOW());END$$DELIMITER;
The five marks come from five things: ① the exact trigger name and AFTER UPDATE ON employee; ② FOR EACH ROW (mandatory in MySQL); ③ the IF NEW.salary <> OLD.salary guard; ④ using OLD for the old salary and NEW for the new one; ⑤ the second, unconditional insert into employee_audit.
id in both log tables is AUTO_INCREMENT, so it must not appear in the column list. And do not forget DELIMITER $$ … DELIMITER ; — the body contains semicolons.
Safer variant for NULL-able salaries: IF NOT (NEW.salary <=> OLD.salary) THEN … — the null-safe equality operator, because NULL <> NULL is unknown, not true.
Q5bigWrite a SQL function named “get_emp_salary” that finds and returns the salary of an employee identified by the emp_id passed as a parameter. [4]
Q5 — get_emp_salary
DELIMITER$$CREATEFUNCTIONget_emp_salary(p_emp_idINT)RETURNSDECIMAL(10,2)DETERMINISTICREADSSQLDATABEGINDECLAREv_salaryDECIMAL(10,2);SELECTsalaryINTOv_salaryFROMemployeeWHEREemp_id=p_emp_id;RETURNv_salary;END$$DELIMITER;-- use itSELECTemp_id,name,get_emp_salary(emp_id)ASsalaryFROMemployee;SELECTget_emp_salary(1);-- single value
Four marks: ① correct header with the parameter and RETURNS DECIMAL(10,2); ② the characteristics DETERMINISTIC READS SQL DATA; ③ DECLARE + SELECT … INTO; ④ exactly one RETURN statement.
Name the parameter p_emp_id, not emp_id. If the parameter had the same name as the column, WHERE emp_id = emp_id would compare the column with itself and be true for every row.
Part 4 — Storage and File Structure 13 marks
Q6smallMap each RAID level to its elaborated name. [3]
RAID LEVEL
Mapping
Elaborated name
RAID 0
RAID 0 = (b)
Block striping; non-redundant
RAID 1
RAID 1 = (f)
Mirrored disks with block striping
RAID 2
RAID 2 = (g)
Memory-Style Error-Correcting-Codes (ECC) with bit striping
RAID 3
RAID 3 = (e)
Bit-Interleaved Parity
RAID 4
RAID 4 = (d)
Block-Interleaved Parity
RAID 5
RAID 5 = (a)
Block-Interleaved Distributed Parity
RAID 6
RAID 6 = (c)
P + Q Redundancy scheme
0=b · 1=f · 2=g · 3=e · 4=d · 5=a · 6=c
Memory hook: the two bit-level levels are 2 (ECC) and 3 (parity); the two block-level parity levels are 4 (dedicated) and 5 (distributed); 6 adds the second parity Q.
Q7bigIn a RAID system with two disks, MTTF = 50,000 h (each) and MTTR = 5 h. Calculate the MTTDL. What conclusions can you draw in terms of system reliability? [3 + 1.5]
Step 1 — failure rate of one disk
λ=MTTF1=50,0001failures per hour
Step 2 — either of the two disks may fail first
rate of first failure=2λ
Step 3 — the vulnerable repair window
P(second disk fails during repair)≈λ×MTTR=λ×5
Step 4 — data-loss rate and the formula
2λ⋅(λ⋅MTTR)=2λ2MTTR⇒MTTDL=2λ2MTTR1=2⋅MTTRMTTF2
Step 5 — put the values in
MTTDL=2⋅5(50,000)2=102.5×109=2.5×108hours
Step 6 — convert to years
24×365250,000,000≈28,539years
MTTDL = 250,000,000 hours ≈ 28,539 years.
Conclusions (1.5 marks) — say all three:
The array is vastly more reliable than a single disk. One disk alone loses data after ~50,000 hours (≈ 5.7 years); the mirrored pair survives ~2.5 × 10⁸ hours — an improvement of about 5,000×.
It does not mean the system will run for 28,500 years. It means that, on average, data loss is extremely rare, because a single failure is tolerated and data loss needs two failures with the second one inside the short 5-hour repair window.
The result depends on strong assumptions: failures are independent, repair starts immediately, and there is no controller failure, common power problem, fire, theft, human error or other correlated failure. In practice those correlated events, not disk wear, dominate real data loss — which is why RAID is not a backup.
Note also: reducing MTTR is the cheapest way to raise MTTDL — halving repair time doubles the mean time to data loss, which is the whole argument for hot-spare disks.
Q8bigData = 968, with 3 disks for data blocks and 1 disk for parity, using RAID Level 4. (a) Binary data block (b) Calculate the parity block P step by step (c) Disk 1 fails — recover it step by step. [1 + 2.5 + 2]
(a) Binary data block
D1
D2
D3
Decimal
9
6
8
4-bit binary
1001
0110
1000
4 bits are needed because the largest digit is 9 = 1001₂ — all blocks must be the same width.
(b) Parity block P
Method 1 — bit by bit (even parity: even number of 1s → 0, odd → 1)
Recovered D1 = 1001₂ = 9 — exactly the original block. A single-disk failure is fully recovered from parity; that is the reliability guarantee of RAID 4.
Paper checklist. Q1 — GROUP BY + COUNT(DISTINCT) + the 4-part section key. Q2 — CREATE VIEW then MAX subquery. Q3 — definition + UNIQUE on the referenced column + the five cascade options. Q4 — AFTER UPDATE, FOR EACH ROW, the <> guard, OLD/NEW. Q5 — DECLARE, SELECT INTO, RETURN, distinct parameter name. Q6 — 0=b 1=f 2=g 3=e 4=d 5=a 6=c. Q7 — MTTF²/(2·MTTR) then divide by 8760. Q8 — 4-bit blocks, XOR twice, verify.