(SEM VI) THEORY EXAMINATION 2024-25 BASICS OF DATA BASE MANAGEMENT SYSTEM

BOE067 – Basics of Data Base Management System (B.Tech Sem VI)

All answers are written in simple, clear, humanized language, not in short points, and are prepared strictly as per the uploaded question paper (all 3 pages).
Reference: Uploaded Question Paper 

BOE067-BASICS-OF-DATA-BASE-MANA…

SECTION A

(Attempt all questions) — 2 × 7 = 14 marks

(a) Three levels of data abstraction in DBMS

DBMS provides three levels of data abstraction to hide complexity. The physical level describes how data is actually stored on disk. The logical level describes what data is stored and the relationships among data. The view level shows only the part of the database that a particular user needs, ensuring security and simplicity.

(b) Entity integrity vs referential integrity

Entity integrity ensures that the primary key of a relation is unique and never NULL, which guarantees that each record is uniquely identifiable.
Referential integrity ensures that a foreign key value must either match a primary key value in another table or be NULL, maintaining consistency between related tables.

(c) Difference between DDL and DML with example

DDL (Data Definition Language) is used to define or modify database structure. For example:

CREATE TABLE Student(id INT);

DML (Data Manipulation Language) is used to insert, update, or delete data. For example:

INSERT INTO Student VALUES(1);

(d) Prime and non-prime attributes

Relation: R(A, B, C, D, E)
FD set: { AB → C, B → E, C → D }

From B → E and AB → C → D, AB is a candidate key.
Prime attributes: A, B
Non-prime attributes: C, D, E

(e) When is a transaction rolled back?

A transaction is rolled back when a system failure occurs, a deadlock is detected, a constraint is violated, or the transaction cannot be completed successfully. Rollback restores the database to its previous consistent state.

(f) ACID properties of transaction

ACID stands for Atomicity, Consistency, Isolation, and Durability.
Atomicity ensures all operations execute completely or none.
Consistency ensures database rules are not violated.
Isolation ensures concurrent transactions do not affect each other.
Durability ensures committed data remains permanent even after failures.

(g) Lock in transaction management

A lock is a mechanism used to control access to data items during transactions. It prevents conflicts by allowing only authorized transactions to read or write data at a given time, ensuring consistency.

SECTION B

(Attempt any three) — 7 × 3 = 21 marks

(a) E-R design for sports league database

Each Team has many Players, and each Game involves multiple teams. Not all players participate in every game. The relationship Participation connects Player, Team, and Game, storing attributes such as position played and game result.

Relational Mapping:

Team(TeamID, TeamName)

Player(PlayerID, Name, TeamID)

Game(GameID, Date, Result)

Participation(PlayerID, GameID, Position)

(b) Testing serializability & conflict equivalence

Two schedules are conflict equivalent if conflicting operations occur in the same order.

S1: R1(A), R2(B), W1(A), W2(B)
S2: R2(B), R1(A), W2(B), W1(A)

In S1, T1 writes A before T2 writes B.
In S2, T2 writes B before T1 writes A.
Hence, S1 and S2 are NOT conflict equivalent.

(c) Log and database recovery techniques

A log records all transaction activities in sequential order. It is maintained on stable storage.
In deferred modification, updates are written only after commit.
In immediate modification, updates are written before commit but can be undone using logs.

(d) Types of users and role of DBA

DBMS users include naïve users, application programmers, sophisticated users, and database administrators.
A DBA manages database design, security, backup, recovery, performance tuning, and access control, while an end user only accesses data.

(e) Aggregate functions in SQL

Aggregate functions perform calculations on a set of values. Common functions include COUNT, SUM, AVG, MAX, and MIN.
Example:

SELECT AVG(Marks) FROM Student;

SECTION C

(Attempt any one) — 7 marks

(a) SQL table creation and insertion

CREATE TABLE STUDENT ( StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Age INT, Department VARCHAR(30), Marks INT CHECK (Marks >= 0) );

INSERT INTO STUDENT VALUES (1,'RAM',20,'CSE',85); INSERT INTO STUDENT VALUES (2,'SHYAM',21,'IT',78); INSERT INTO STUDENT VALUES (3,'MOHAN',22,'ECE',90);

(b) Database security layers

Physical security protects hardware, OS-level security protects operating systems, network security protects data during transmission, and database-level security controls user access. Together, they form a strong defense system.