(SEM VI) THEORY EXAMINATION 2022-23 BASICS OF DATA BASE MANAGEMENT SYSTEM

BASICS OF DATABASE MANAGEMENT SYSTEM – KOE-067

Section-wise Important Questions & Ready Answers

SECTION A

(Attempt all questions – 2 marks each)

(a) Database, DBMS, and Metadata

A database is an organized collection of related data stored electronically. A DBMS (Database Management System) is software that allows users to create, store, retrieve, and manage data efficiently. Metadata is data about data; it describes the structure, constraints, and meaning of data stored in the database.

(b) Problems with File System

File systems suffer from data redundancy, data inconsistency, difficulty in accessing data, lack of security, poor data sharing, and absence of recovery mechanisms. These limitations led to the development of DBMS.

(c) Instance and Domain

An instance is the actual content of the database at a specific time, whereas a domain is the set of permissible values that an attribute can take.

(d) Recursive Relationship with Example

A recursive relationship occurs when an entity is related to itself.
Example: In an Employee entity, an employee may supervise another employee.

(e) Third Normal Form (3NF) and BCNF

A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on a candidate key.
A relation is in BCNF if for every functional dependency X→YX \rightarrow YX→Y, X is a super key.

(f) Data Dictionary

A data dictionary is a centralized repository that stores metadata such as table definitions, attribute names, data types, constraints, and relationships.

(g) SQL Logical Operators

SQL logical operators include AND, OR, and NOT. They are used to combine multiple conditions in SQL queries.

(h) DROP Command with Options

The DROP command removes database objects permanently.
Example:

DROP TABLE Student CASCADE;

The CASCADE option removes dependent objects, while RESTRICT prevents deletion if dependencies exist.

(i) Two-Phase Commit Protocol

The two-phase commit protocol ensures atomicity in distributed databases. It consists of a prepare phase and a commit phase, ensuring all participating sites either commit or abort the transaction together.

(j) Checkpoint

A checkpoint is a recovery technique where the DBMS saves the current state of the database to disk, reducing recovery time after a system crash.

SECTION B

(Attempt any three – 10 marks each)

2(a) Data Abstraction and Levels of Abstraction

Data abstraction hides internal details and shows only relevant information. The three levels are physical level (how data is stored), logical level (what data is stored and relationships), and view level (user-specific views).

2(b) Integrity Constraints with Examples

Integrity constraints maintain data accuracy and consistency. These include domain constraints, entity integrity, referential integrity, and key constraints. For example, a primary key cannot contain NULL values.

2(c) GROUP BY and HAVING Clause

The GROUP BY clause groups rows with similar values, while HAVING filters groups based on conditions.
Example:

SELECT dept, COUNT(*) FROM Employee GROUP BY dept HAVING COUNT(*) > 5;

2(d) Decomposition and Redundancy

Decomposition divides a relation into smaller relations to reduce redundancy and anomalies. Poor decomposition may cause lossy join and dependency preservation problems.

2(e) Serializability

Serializability ensures that concurrent transaction execution gives the same result as serial execution. Types include conflict serializability and view serializability.

SECTION C

3(a) Structure of DBMS and Views

DBMS structure includes query processor, storage manager, transaction manager, and data dictionary.
Views are virtual tables derived from base tables. Updates on views are allowed only if they satisfy certain conditions.

3(b) Strong vs Weak Entity Set

A strong entity has a primary key, whereas a weak entity depends on a strong entity and does not have a complete key.
Example: Employee (strong) and Dependent (weak).