DBMS Final Exam Revision Guide
Prepared for Guljar Hosen
SET 1: Data Models & DBA Responsibilities
Q1.a. Define Data Model + Semi-Structured Example
Answer: A data model is a conceptual representation that defines how data is organized, stored, and
accessed in a database system. It includes definitions for data elements, relationships, and constraints.
Semi-structured Model Example: A semi-structured data model doesn't follow a strict tabular
schema like relational databases. Instead, it uses flexible formats such as tags or key-value pairs.
Example: XML, JSON — where data is nested, irregular, or self-descriptive.
Q1.b. How DBMS Solves File System Issues
Answer: File Management Systems store data in flat files, which causes:
Data redundancy
Inconsistencies
Difficult access
Security risks
No backup/recovery
DBMS Solves It By:
Centralizing storage
Using normalization
Providing SQL access
Enforcing access control
Offering backup and recovery
Q1.c. DBA Role at Grameen Phone
Answer: As a Database Administrator (DBA) at Grameen Phone, the responsibilities include:
Designing efficient database schemas for customer info, call records, and billing
Creating backups and handling recovery
Monitoring and optimizing performance
Managing access control and permissions
Securing data from unauthorized access
Automating tasks like backups and report generation
Assisting teams in data queries and reporting
SET 3: Schema, Instance, ERD, M
Q3.a. Explain Instance and Schema with Examples
Answer: A schema is the design or structure of the database. It tells us what tables are there, what
columns each table has, and what type of data it stores. A schema is like a plan or layout.
An instance is the actual data stored in those tables at any point in time. It keeps changing when we
add, update, or delete data.
Example:
Schema: STUDENT (student_no, name, dept, city)
Instance:
student_no name dept city
232-134-022 Faiza SWE Sylhet
233-134-004 Borsha CSE Dhaka
Here, the schema stays the same, but the instance can change every day.
Q3.b. ERD for Appendix A
(To be drawn separately, including STUDENT, UNIT, INSTRUCTOR, RESULT, TIMETABLE, CAMPUS)
Q3.c. Explain Method to Decompose Many-to-Many in ERD
Answer: A many-to-many (M
) relationship occurs when multiple records in one table are associated with multiple records in
another.
Example: One student can enroll in many units, and one unit can be taken by many students.
Relational databases do not support direct M
relationships. To handle this, we decompose the M
into two 1
relationships using a bridge (junction) table.
Steps:
1. Create two main entities:
STUDENT(student_no, name)
UNIT(unit_no, name)
2. Introduce a bridge table (junction table):
RESULT(student_no, unit_no, final_grade)
3. In RESULT:
student_no is a foreign key referencing STUDENT
unit_no is a foreign key referencing UNIT
final_grade is an extra attribute specific to the relationship
Benefits:
Breaks M
into two manageable 1
relationships:
One student → many result records
One unit → many result records
Improves data clarity, consistency, and normalization
Makes queries easier and the database more scalable
SET 4: Procedure, DDL & Triggers
Q4.a. Stored Procedure: sp_update_credit
sql
CREATE PROCEDURE sp_update_credit(param_credit INT, param_unit_no INT)
BEGIN
UPDATE UNIT
SET credit = param_credit
WHERE unit_no = param_unit_no;
END;
Q4.b. DDL and Trigger for tbl_unit_log
sql
CREATE TABLE tbl_unit_log (
lo_last_credit INT,
lo_action VARCHAR(50),
lo_unitNo INT
);
CREATE TRIGGER trg_log_credit
AFTER UPDATE ON UNIT
FOR EACH ROW
BEGIN
INSERT INTO tbl_unit_log(lo_last_credit, lo_action, lo_unitNo)
VALUES (OLD.credit, 'Credit Updated', OLD.unit_no);
END;
SET 5: Relational Algebra – Address & Institution
Q5.a. Instructors from Different Institutions
E1 = π name, city (INSTRUCTOR)
E2 = π name, city (STUDENT)
E3 = E1 − E2
E4 = π name (E3)
Q5.b. Students Same Address as Riaz
E1 = σ name = 'Riaz' (STUDENT)
E2 = π city, street (E1)
E3 = ρ riaz_address(riaz_city, riaz_street)(E2)
E4 = STUDENT × riaz_address
E5 = σ city = riaz_city ∧ street = riaz_street (E4)
E6 = π student-no, name (E5)
SET 6: Relational Algebra – Units & Teaching
Q6.a. Units Not Offered
E1 = π unit-no (UNIT)
E2 = π unit-no (TIMETABLE)
E3 = E1 − E2
Q6.b. Instructors Who Taught All Units
E1 = π unit-no (UNIT)
E2 = π instructor-no, unit-no (TIMETABLE)
E3 = E2 ÷ E1
E4 = π instructor-no (E3)
Note: ERD diagram and query trees can be prepared separately upon request as image files.
You've got this, bro. April 24 is your win day! 📚 🌟
