৪৯তম বিসিএস ⎯ কম্পিউটার সায়েন্স (CSE) [৯৭১]

 Correct Answer: A
    A DBMS is software that allows users to create, store, manage, and retrieve data efficiently.
    It combines data (database) with programs (software modules) such as query processor, storage manager, transaction manager.
   
Example: Oracle, MySQL, SQL Server.

Correct Answer: B

  Data abstraction = DBMS hides low-level storage details from the user.
    User sees only a logical view (tables, rows, attributes) instead of how data is physically stored.
    Example: You can query SELECT * FROM Student without worrying about how it’s stored on disk.

 Other options:
    A) DBMS reduces redundancy, unlike file-based systems.
    C) DBMS offers better security.
    D) It provides flexible querying using SQL.

Correct Answer: D

Edgar F. Codd (IBM researcher) proposed the relational model in 1970.
His paper “A Relational Model of Data for Large Shared Databanks” introduced tables, keys, relational algebra.
This model became the foundation of SQL-based RDBMS.

Other options:
A) Ritchie = Invented C language, UNIX
B) Berners-Lee = Invented WWW.
C) Babbage = Father of Computer.

 Correct Answer: B
    In 3-schema architecture:
    External schema = user views.
    Conceptual schema = logical design of database.
    Internal schema = physical storage.
    Conceptual schema provides a buffer, so physical changes don’t affect applications.
 Other options:
    A) External = for end users.
    C) Internal = lowest level, physical.
    D) Network = unrelated.

Correct Answer: C
ER Model: Developed by Peter Chen (1976). Represents data as entities, attributes, and relationships.
Used in database design before converting to relational schema.
Example: A “Student” entity with attributes (Roll, Name, Dept) linked to a “Course” entity.

Other options:
A) Agile = software development methodology.
B) OSI = networking model 
D) Von Neumann = computer architecture.

 Correct Answer: B
   Ellipses (ovals) represent attributes.
   Example: Student (entity rectangle) → Name (attribute ellipse).
Other options:
    A) Rectangles = entities.
    C) Diamonds = relationships.
    D) Lines = connections.

 Correct Answer: D

In mapping:
Relationships → Relations (or foreign keys).
Attributes → Columns.
Tuples → Rows.
Entities → Relations (tables).

 Correct Answer: D
    Integrity constraints: Domain, Key, Referential, Assertions.
    Procedural constraint ≠ part of DBMS theory.

 Other options:
    A, B, C = valid integrity constraints.

 Correct Answer: B
    Weak entity = depends on strong entity + partial key.
    Example: “Dependent” depends on “Employee”.
    Requires discriminator + owner’s primary key.

Other options:
• A = strong entity.
• C = false (weak entities do participate).
• D = unrelated.

 Correct Answer: A
    Total participation = double line in ER diagram.
    Example: “Employee” must work in at least one “Department”.

Other options:
B = partial participation.
C, D = not definitions of participation.

 Correct Answer: B
    Normalization ensures that a database schema is structured to reduce data redundancy and prevent update, insert, and delete anomalies.
    Example: If student info is repeated in multiple rows, updating address in one row but not in others → anomaly. Normalization solves this.

Other options:
    A) Speed is not the primary goal (sometimes normalized DBs are slower due to joins).
    C) Storage efficiency is a side effect, not the main purpose.
    D) Normalization may increase tables, but that’s a result, not the goal.

Correct Answer: C

    1NF rule: all attributes must hold atomic values (no repeating groups, no arrays, no composite attributes).
    Example: PhoneNumbers column should not contain {123,456} but be stored in separate rows.

Other options:
   A) That’s 2NF condition.
   C) That’s 3NF condition.
   D) "All anomalies free" = too broad, achieved only in higher normalization

Correct Answer: B
    2NF removes partial dependency: a non-key attribute should not depend on part of a composite key.
    Example: In table (StudentID, CourseID → Grade, StudentName), StudentName depends only on StudentID, not on the full key → violates 2NF.

Other options:

    A) 1NF only removes repeating groups.
    C) 3NF deals with transitive dependencies.
    D) BCNF is stricter form beyond 3NF

 Correct Answer: A
    BCNF stricter than 3NF: In BCNF, every determinant (attribute on left side of FD) must be a candidate key.

Other options:
B) That’s 1NF.
C) That’s 2NF.
D) That’s 3NF

Correct Answer: C
    BookID → BookID is a trivial dependency (attribute depends on itself). Though technically valid, it is not meaningful.
Other options:
    A, B, D = valid functional dependencies.

A functional dependency occurs when the value of one attribute (or a set of attributes) uniquely determines the value of another attribute. This relationship is denoted as:  X → Y

Here, X is the determinant, and Y is the dependent attribute. This means that for each unique value of X, there is precisely one corresponding value of Y.

Example: Consider a table named Students with the following attributes:
StudentID
StudentName
StudentAge
If each student has a unique StudentID, and this ID determines the student's name, we can express this functional dependency as:

StudentID → StudentName

 Correct Answer: B
    Sometimes, decomposing to BCNF creates too many tables, making queries inefficient due to joins.
    Hence, many databases stay in 3NF for balance.

Other options:
    A) BCNF reduces anomalies, not increases.
    C) BCNF removes more redundancy than 3NF.
    D) BCNF is stricter, not weaker.

 Correct Answer: B
Query optimization = DBMS finds the most efficient strategy (execution plan) to run a query (e.g., which index to use, which join order).

Other options:
A) Normalization = schema design, not query execution.
C) Compilation = converting SQL into intermediate relational algebra.
D) Translation = SQL → relational algebra step, before optimization.

Correct Answer: D
   Normalization = schema design concept, not query processing.

Other options:
A) Parsing = SQL → relational algebra.
B) Optimization = find efficient plan.
C) Evaluation = execute the plan.

 Correct Answer: B
  Selection (σ) = filters tuples based on a condition. Example: σ(Salary > 50000)(Employee).

Other options:
   A) Projection = choose attributes (columns).
   C) Join = combines relations.
   D) Union = combines tuples from relations.

Correct Answer: D
    ACID = Atomicity, Consistency, Isolation, Durability.
   Redundancy is related to file storage, not transactions.

Other options:
A, B, C = correct ACID properties

 Correct Answer: B
    Atomicity = transaction is “all or nothing”.
    Example: Money transfer → debit + credit must both happen, or none.

Other options:
    A) Consistency = ensures database moves from valid state to valid state.
    C) Isolation = ensures transactions don’t interfere.
    D) Durability = once committed, changes survive crashes

Correct Answer: A
   Deadlock = cycle of waiting (T1 waits for T2, T2 waits for T1).

Other options:
• B) Sequential → no concurrency → no deadlock.
• C) Shared locks do not block reads → no deadlock.
• D) One transaction → no conflict.

 Correct Answer: B
    Wait-Die (older waits) / Wound-Wait (older preempts younger) → use timestamps to prevent cycles.

Other options:
A) Conservative 2PL = locks everything upfront.
C) Timeout = abort after time, not timestamp.
D) Waits-for graph = detection, not prevention.

 Correct Answer: C – Ensure consistency during concurrent transactions
    Multiple transactions may access and modify data simultaneously → concurrency control ensures database remains consistent.

Other options:
   A) Deadlock prevention is part of concurrency control, but not the primary goal. 
   B) Atomicity is ensured per transaction; concurrency control ensures interactions do not violate consistency.
   D) Speed may be affected; it’s not the main objective.

Correct Answer: C – Serializability
Serializability = correctness criterion for concurrent execution → ensures database state is as if transactions ran one after another.

Other options:
    A, B, D = ACID properties, not specifically for concurrent execution.

Correct Answer: B
    MVCC = multiple versions of data. Readers access snapshot, writers update new version → avoids blocking.

Other options:
    A) False, many readers allowed.
    C) Requires extra storage.
    D) Deadlocks possible among writers.

Correct Answer: C – DDL (Data Definition Language)

   Commands: CREATE, ALTER, DROP, TRUNCATE.
    Used for schema definition, table creation, etc.

Other options:
    A) DML = insert/update/delete data.
    B) DCL = manage permissions (GRANT, REVOKE).
    D) TCL = manage transactions (COMMIT, ROLLBACK).

 Correct Answer: B – SELECT

SELECT is standard command to query and display data from a table.

Other options:

A, C, D = incorrect SQL keywords; may be used in programming APIs but not standard SQL.

Correct Answer: C – TRUNCATE
    Removes all rows quickly, keeps structure, cannot be rolled back in most DBMS.

Other options:
    A) DROP = deletes table completely (structure + data).
    B) DELETE = row by row removal, can use WHERE, supports rollback.
    D) REMOVE = not a valid SQL command.

Correct Answer: A – PRIMARY KEY
   A primary key uniquely identifies each row, cannot be NULL.

Other options:
    B) FOREIGN KEY = ensures referential integrity.
    C) CHECK = validates condition (e.g., salary > 0).
    D) DEFAULT = assigns default values if none provided

 Correct Answer: C– GROUP BY
Used with aggregate functions (COUNT, SUM, AVG).

Other options:
    A) ORDER BY = sorts rows.
    C) DISTINCT = removes duplicates.
    D) HAVING = applies conditions on groups, not rows

Correct Answer: B – Referential Integrity
      Foreign Key = references primary key in another table. Ensures consistency.

Other options:
    A) Duplicates allowed, but must match referenced PK.
    C) Must reference primary/unique key, not just any column.
    D) Uniqueness enforced by primary key, not foreign key.

Correct Answer: A
       Triggers fire on data modifications (INSERT/UPDATE/DELETE). Used for auditing, enforcing rules.

Other options:
     B) System startup triggers exist in some DBs, but not standard.
    C) Not limited to COMMIT.
    D) SELECT doesn’t fire triggers.

 Correct Answer: A – DCL (Data Control Language)

Commands: GRANT, REVOKE → manage permissions.

Other options:

B) DDL → defines schema.

C) DML → manipulate data.

D) TCL → manages transactions (COMMIT, ROLLBACK).

 Correct Answer: C – Query Parsing
            Parsing = checks syntax + semantics of SQL query. Produces parse tree.

Other options:
    A) Optimization comes later.
    B) Execution is the final step.
   D) Translation = after parsing, before optimization

 Correct Answer: B
    Optimizer chooses lowest cost execution plan (CPU + I/O).

 Other options:
     A) Redundancy ↑ = bad.
    C) Length irrelevant.
•   D) Syntax correctness checked in parsing, not optimization.

Correct Answer: D
Projection = choose specific columns (attributes).

Other options:
   A) Selection reduces tuples.
   C, D unrelated.

Correct Answer: B – Sequential
Records physically stored in order → efficient for range queries.
 
Wrong options:
A) Heap = unordered.
C) Indexed = index helps, but data may be unordered.
D) Hashed = key determines storage location

Correct Answer: A – Faster Retrieval
 Index points directly to record → reduces search time.

Wrong options:
B) Index adds storage overhead.
C) Indexes require updates on insert/delete.
D) Constraints control duplicates, not index type

Correct Answer:C–
  In B+ Tree
    Internal nodes guide search; leaves store actual key pointers.
    Supports fast point and range queries.

Other options:
    B) Root guides search, not only storage.
    C) Internal nodes = keys only, not full records.
    D) B+ tree ideal for range queries.

Correct Answer: B – Hash Function
Hash(key) → address/block → fast direct access.

Other options:
A) Key used, but via hash.
C, D) Not used in hash index.

 Correct Answer: B –
Hash Collision  Two keys → same hash → collision → resolved via chaining/overflow.


Other options:
    A) May result from collision, not definition.
    C) Sequential insertion unrelated.
    D) Dense/sparse = irrelevant.

 Correct Answer: A
Dense Index:  Each record has an index entry → fast access.
 
Other options:
     B) Sparse index → one per block.
    C) Not practical.
    D) Index can be on any key.

Correct Answer: B – Candidate Key

Superkey:
Any set of attribute(s) that can uniquely identify a row (tuple) in a relation.
Example: In a Student table (RollNo, Name, Email), {RollNo, Email} is a superkey.

Minimal Superkey:
If no proper subset of a superkey can uniquely identify tuples → it is minimal.
These minimal superkeys are called Candidate Keys.
Example: {RollNo}, {Email} are candidate keys.

Primary Key:
Among candidate keys, one is chosen as the primary key.
Example: {RollNo}.

Composite Key:
A key formed by combining multiple attributes (e.g., {CourseID, StudentID}).

Foreign Key:
Attribute(s) in one table that refers to the primary key of another table.

Correct Answer: A 

A → B and B → C → so
A → B, C.

Hence A determines all attributes (A, B, C).

Therefore, A is the minimal super key → candidate key.

 Wrong options:

B) B → C, but B cannot determine A → not candidate key.

C) C does not determine A or B.

D) (A, B) is a superkey, but not minimal → not candidate key.

Answer: c) A repository that stores metadata about the database

A data dictionary is not user data, but a structured repository that contains metadata (data about data) in a database.
 It stores:

Names of tables
Columns/fields of each table (name, data type, size, constraints)
Primary keys, foreign keys
Views, indexes, triggers, stored procedures
Access rights, security info.

Answer: c) MongoDB

Relational Database (RDBMS):

1. Stores data in tables (rows & columns).
2. Follows relational model proposed by E.F. Codd.
3. Uses SQL (Structured Query Language).
4. Examples: Oracle, MySQL, PostgreSQL, SQL Server.

Non-Relational Database (NoSQL):

1. Stores data in other formats (document, key-value, graph, wide-column).
2. Flexible schema (no need for strict table structure).
3. Often used for big data, real-time apps, unstructured/semi-structured data.

Examples: MongoDB, Cassandra, CouchDB, Neo4j.

MongoDB specifically:

It is a document-oriented NoSQL database.
Stores data in JSON-like documents (BSON internally).