Here are the complete answers to all the questions from the B.
Tech 5th
Semester Mid Semester Examination 2025 (DBMS) paper of Bhagalpur
College of Engineering.
Q1. (a) What is logical data independence?
• It is the ability to change the logical schema without altering the
external schema or application programs.
• For example, adding a new attribute to a table does not affect how
users view the data.
(b) Who is responsible for physical level data abstraction?
• Database Administrator (DBA) is responsible for physical data
abstraction.
• DBA handles storage, indexing, and performance tuning.
(c) Mention any four functions of DBA
1. Schema definition and modification.
2. Storage structure and access method definition.
3. Granting user access and managing security.
4. Backup and recovery of the database.
(d) Explain instances and schemas with an example.
• Schema: Structure of the database (tables, columns, types).
o Example: Student(Roll, Name, Dept) is a schema.
• Instance: Actual content in the database at a specific time.
o Example: A row like (1, 'Amit', 'CSE') is an instance.
(e) What is Query Evaluation Engine?
• It is a component of the DBMS responsible for executing SQL
queries.
• It parses the query, optimizes it, and interacts with the storage
engine to fetch data.
Q2 (a) Explain the two database system architecture with diagrams.
Explain the classification of DBMS.
1. Two-Tier Architecture:
• Client directly communicates with the database.
• UI and business logic at the client side.
• DBMS at the server side.
2. Three-Tier Architecture:
• Tier 1: Presentation layer (client).
• Tier 2: Application layer (business logic).
• Tier 3: Database layer (DBMS).
DBMS Classification:
1. Based on Data Model:
o Relational, Hierarchical, Network, Object-Oriented.
2. Based on Users:
o Single-user, Multi-user.
3. Based on Location:
o Centralized, Distributed, Cloud.
Q2 (b) ER Model – Keys and Dependencies
Keys:
• Primary Key: Uniquely identifies tuples.
o Example: RollNo in Student.
• Foreign Key: Refers to primary key of another table.
o Example: DeptID in Student refers Dept table.
Strong vs Weak Entity:
• Strong Entity: Has its own key.
• Weak Entity: Depends on a strong entity.
o Example: Dependent depends on Employee.
Functional Dependency:
• A → B means B is functionally dependent on A.
o Example: EmpID → EmpName.
Three types:
1. Full Functional Dependency: Non-key attribute depends on the
whole key.
2. Partial Dependency: Non-key attribute depends on part of the
key.
3. Transitive Dependency: A → B and B → C implies A → C.
Q3 (a) SQL Queries using given schema
Assume schema:
Employee(Empno, Minit, Lname, Ssn, Bdate, Address, Sex, Salary,
Super_ssn, Dno)
Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date)
Project(Pname, Pnumber, Plocation, Dnum)
Works_on(Essn, Pno, Hours)
Dependent(Essn, Dependent_name, Sex, Bdate, Relationship)
i.
SELECT Pnumber, Dnumber, Lname, Address, Bdate
FROM Project, Department, Employee
WHERE Plocation = 'Stafford' AND Dnum = Dnumber AND Mgr_ssn =
Ssn;
ii.
SELECT [Link], [Link], [Link] * 1.10 AS IncreasedSalary
FROM Employee E, Works_on W, Project P
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] =
'ProductX';
iii.
SELECT DISTINCT [Link]
FROM Project P, Works_on W, Employee E
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = 'Smith'
AND ([Link] = [Link] OR [Link] = [Link]);
iv.
SELECT Fname, Lname
FROM Employee
WHERE Bdate BETWEEN '1950-01-01' AND '1959-12-31';
v.
SELECT Fname, Lname
FROM Employee
WHERE Salary = (
SELECT DISTINCT Salary FROM (
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 8
) AS TopSalaries ORDER BY Salary LIMIT 1
);
Q3 (b) Queries in DRC, TRC, RA
Assume:
• DRC: Domain Relational Calculus
• TRC: Tuple Relational Calculus
• RA: Relational Algebra
i. DRC (Birthdate & Address of ‘John B. Smith’):
{<B, A> | ∃S ∃D ∃M (Employee(Name='John B. Smith' ∧ Bdate=B ∧
Address=A))}
ii. TRC (Managers with dependents):
{T | T ∈ Employee ∧ ∃D (D ∈ Dependent ∧ [Link] = [Link])}
iii. RA (Employees with same-name dependents):
π_E.Fname,[Link] (σ_E.Fname=D.Dependent_name (Employee E ⨝
Dependent D))
iv. RA (Avg salary per dept):
DeptEmp ← Employee ⨝ Department
Result ← γ_Dname, AVG(Salary)(DeptEmp)
v. RA (Employees not working on every project):
AllEmp ← π_Ssn(Employee)
AllProj ← π_Pnumber(Project)
EmpProj ← π_Essn,Pno(Works_on)
EmpNotAll ← AllEmp − π_Essn (
γ_Essn (EmpProj) HAVING COUNT(Pno) = (SELECT COUNT(*) FROM
Project)
)
Q4 (a) Extendable Hashing
Given keys: 2, 3, 5, 7, 11, 17, 19, 23, 29, 31
Hash function: h(z) = z mod 8
Binary Hash Values:
Key Hash (mod 8) Binary
2 2 010
3 3 011
5 5 101
7 7 111
11 3 011
17 1 001
19 3 011
23 7 111
29 5 101
31 7 111
Buckets can hold 3 records:
• Start with 1-bit directory, increase bits as overflow occurs.
• Split buckets when full and redistribute.
(Attach hash structure diagram if required.)
Q4 (b) Constraints in Relational Model + Canonical Cover
Constraints:
1. Domain Constraint: Attribute values must be from defined
domain.
2. Key Constraint: No duplicate tuples.
3. Entity Integrity: Primary key cannot be null.
4. Referential Integrity: Foreign key must match existing primary key
or be NULL.
Canonical Cover Algorithm:
• Input: Set of FDs.
• Steps:
1. Split RHS if FD has multiple attributes.
2. Remove extraneous attributes from LHS.
3. Remove redundant FDs.
4. Combine FDs with same LHS.
Example:
FDs: {A→BC, B→C, A→B, AB→C}
• Split: A→B, A→C
• Remove: AB→C is redundant
• Result: Canonical cover = {A→B, A→C}
If you want handwritten or diagrammatic solutions or SQL execution
examples, I can provide those too. Let me know!