FYUG Even Semester Exam, 2025
COMPUTER SCIENCE: CSCDSC-252
(Database Management System)

Subject: Computer Science

Course No: CSCDSC-252

Semester: 4th Semester

Year: 2025

Full Marks: 70 (Pass Marks: 28)

Time: 3 Hours

UNIT-I

Question 1 (a) [2 Marks]

What is database management system?

A Database Management System (DBMS) is a software package designed to define, manipulate, retrieve, and manage data in a database. It acts as an interface between the end-user and the database, ensuring that data is consistently organized and easily accessible.

Question 1 (b) [2 Marks]

Define strong and weak entity sets.

  • Strong Entity Set: An entity set that has a primary key and does not depend on any other entity for its existence.
  • Weak Entity Set: An entity set that does not have sufficient attributes to form a primary key and depends on an identifying (strong) entity for its existence.

Question 1 (c) [2 Marks]

What do you mean by schemas and instances?

  • Schema: The logical structure or design of the database (e.g., table definitions), which remains constant.
  • Instance: The actual data stored in the database at a specific moment in time, which changes frequently as data is updated.

Question 2 (a) [10 Marks]

(i) Write down the advantages of DBMS.

  • Data Redundancy Control: Minimizes duplicate data.
  • Data Integrity: Ensures data follows specific rules and constraints.
  • Data Security: Restricts unauthorized access through user permissions.
  • Concurrency Control: Allows multiple users to access data simultaneously without conflict.
  • Backup and Recovery: Provides automated tools to recover data after hardware or software failures.

(ii) Explain different data models in DBMS.

Data models define the logical structure of a database. Major models include:

  • Hierarchical Model: Data is organized in a tree-like structure (Parent-Child relationship).
  • Network Model: Similar to hierarchical but allows child nodes to have multiple parents (Graph structure).
  • Relational Model: Data is organized in tables (rows and columns). This is the most widely used model.
  • Entity-Relationship (ER) Model: A high-level conceptual model describing entities and their relationships.
  • Object-Oriented Model: Data is represented in the form of objects, similar to OOP languages.

Question 2 (b) [10 Marks]

(i) Explain the architecture of DBMS.

The standard architecture is the Three-Schema Architecture (or ANSI-SPARC architecture):

  1. External/View Level: The highest level describing how individual users see the data.
  2. Conceptual/Logical Level: Describes what data is stored and the relationships between them.
  3. Internal/Physical Level: The lowest level describing how data is actually stored on the physical storage devices.

(ii) What do you mean by physical data independence? Differentiate between physical and logical data independence.

Physical Data Independence: The ability to modify the physical schema (storage structures) without changing the conceptual schema.

Feature Physical Data Independence Logical Data Independence
Definition Ability to change physical storage without affecting conceptual level. Ability to change conceptual schema without affecting external views.
Level Between Internal and Conceptual levels. Between Conceptual and External levels.
Ease Easier to achieve. Difficult to achieve as it involves complex mapping.

UNIT-II

Question 3 (a) [2 Marks]

What are DDL and DML?

  • DDL (Data Definition Language): Used to define the database structure (e.g., CREATE, ALTER, DROP).
  • DML (Data Manipulation Language): Used to manage data within the structure (e.g., INSERT, UPDATE, DELETE).

Question 3 (b) [2 Marks]

Write down the features of SQL.

  • Non-procedural language (focuses on 'what' to do, not 'how').
  • Standardized language for all relational databases.
  • Allows for data definition, manipulation, and control.

Question 4 (a) [10 Marks]

Explain different relational algebra operations used in DBMS.

Relational algebra is a procedural query language consisting of operations that take one or two relations as input and produce a new relation:

  • Select (σ): Filters rows based on a condition.
  • Project (π): Selects specific columns from a relation.
  • Union (∪): Combines tuples from two relations.
  • Set Difference (−): Find tuples in one relation but not the other.
  • Cartesian Product (X): Combines every tuple of one relation with every tuple of another.
  • Join (⋈): Combines related tuples from two relations based on a common attribute.

UNIT-III

Question 5 (a) [2 Marks]

Define functional dependency.

A functional dependency (X → Y) is a constraint between two sets of attributes in a relation. It states that for any two tuples, if they agree on the value of attribute X, they must also agree on the value of attribute Y.

Question 6 (a) [10 Marks]

What is normalization? Explain different normal forms. How does BCNF differ from 3NF?

Normalization: The process of organizing data in a database to reduce redundancy and prevent anomalies (insert, update, delete).

  • 1NF: Atoms only; no multi-valued attributes.
  • 2NF: In 1NF + no partial dependency (all non-key attributes must depend on the whole primary key).
  • 3NF: In 2NF + no transitive dependency (non-key attributes should not depend on other non-key attributes).
  • BCNF (Boyce-Codd Normal Form): A stricter version of 3NF. A relation is in BCNF if for every functional dependency X → Y, X must be a superkey.

Difference: 3NF allows X → A where A is a prime attribute even if X is not a superkey, whereas BCNF strictly requires X to be a superkey for all dependencies.

UNIT-IV

Question 8 (a) [10 Marks]

(i) Explain ACID properties of transaction.

  • Atomicity: "All or nothing." Either the entire transaction succeeds or none of it is applied.
  • Consistency: A transaction transforms the database from one valid state to another.
  • Isolation: Transactions occurring concurrently do not interfere with each other.
  • Durability: Once a transaction is committed, the changes are permanent, even in case of system failure.

(ii) Explain two-phase locking protocol with an example.

2PL ensures serializability by dividing the transaction into two phases:

  1. Growing Phase: Transaction obtains all required locks but cannot release any.
  2. Shrinking Phase: Transaction releases locks but cannot obtain any new ones.

UNIT-V

Question 9 (c) [2 Marks]

Write down the advantages of B-tree.

  • Keeps data sorted and allows for efficient searches, insertions, and deletions.
  • Reduces the number of disk accesses due to its balanced nature.
  • Suitable for storage systems that read and write large blocks of data.

Question 10 (a) [10 Marks]

What is indexing? What are the different types of indices? Explain with example.

Indexing: A data structure technique used to quickly locate and access data in a database without searching every row.

  • Primary Index: Defined on an ordered data file where the index field is the primary key.
  • Clustered Index: Created on the column that determines the physical order of data in the table.
  • Secondary Index: Created on non-key columns to provide an alternate path to the data.

Exam Focus Enhancements

Exam Tips

  • For Normalization questions, always draw the dependency diagrams to explain 2NF and 3NF.
  • When discussing ACID properties, use a simple bank transfer example (Account A to B) to illustrate Atomicity.

Common Mistakes

  • Confusing DELETE (DML) with DROP/TRUNCATE (DDL).
  • Mixing up Physical and Logical data independence.

Important Formulas List

- Select Operation: σ_condition(Relation)
- Project Operation: π_attributes(Relation)
- Closure of Attributes: X+ (used for finding keys)

Answer Presentation Strategy

Use diagrams for 3-schema architecture and ER-modeling. Use tables for comparing commands like DROP vs TRUNCATE.