Multiple-Choice Questions on Database Management System for TU IT Officer

-

 

Multiple-Choice Questions on Database Management System for IT Officer Exams

Database Management System and Its Applications

  1. What is a primary function of a Database Management System (DBMS)?
    a) To design user interfaces
    b) To manage and organize data efficiently
    c) To compile application code
    d) To manage network protocols
    Answer: b) To manage and organize data efficiently
    Explanation: A DBMS provides an interface to store, retrieve, and manage data, ensuring data integrity and efficient access.

  2. Which application commonly uses a DBMS?
    a) Word processing
    b) Banking systems
    c) Image editing
    d) Operating system management
    Answer: b) Banking systems
    Explanation: DBMS is widely used in banking for managing transactions, customer data, and accounts due to its reliability and security features.

ER Modeling

  1. What does an entity represent in an ER diagram?
    a) A relationship between tables
    b) A real-world object with attributes
    c) A database query
    d) A normalization rule
    Answer: b) A real-world object with attributes
    Explanation: An entity in an ER diagram represents a real-world object (e.g., Student, Employee) with attributes like name or ID.

  2. Which symbol is used to represent a weak entity in an ER diagram?
    a) Single rectangle
    b) Double rectangle
    c) Diamond
    d) Oval
    Answer: b) Double rectangle
    Explanation: A weak entity, which lacks a primary key and depends on another entity, is represented by a double rectangle in an ER diagram.

Relational Languages and Relational Model

  1. Which relational language is used to define the structure of a database?
    a) DML
    b) DDL
    c) DCL
    d) TCL
    Answer: b) DDL
    Explanation: Data Definition Language (DDL) defines database structures, such as creating tables or schemas.

  2. In the relational model, what is a tuple?
    a) A column in a table
    b) A row in a table
    c) A primary key
    d) A foreign key
    Answer: b) A row in a table
    Explanation: A tuple represents a single row or record in a relational table, containing values for each attribute.

Database Constraints and Normalization

  1. Which constraint ensures that a column cannot have duplicate values?
    a) NOT NULL
    b) FOREIGN KEY
    c) UNIQUE
    d) CHECK
    Answer: c) UNIQUE
    Explanation: The UNIQUE constraint ensures that all values in a column are distinct, often used for candidate keys.

  2. What is the purpose of normalization in a DBMS?
    a) To increase data redundancy
    b) To eliminate data redundancy and anomalies
    c) To encrypt database data
    d) To reduce query performance
    Answer: b) To eliminate data redundancy and anomalies
    Explanation: Normalization organizes data to reduce redundancy and prevent insertion, update, and deletion anomalies.

Normalization: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DKNF

  1. A table is in 1NF if:
    a) It has no transitive dependencies
    b) All attributes contain atomic values
    c) All non-key attributes depend on the primary key
    d) Every determinant is a candidate key
    Answer: b) All attributes contain atomic values
    Explanation: First Normal Form (1NF) requires that each attribute holds only single-valued (atomic) data and no repeating groups.

  2. Which normal form eliminates transitive dependencies?
    a) 1NF
    b) 2NF
    c) 3NF
    d) BCNF
    Answer: c) 3NF
    Explanation: Third Normal Form (3NF) ensures no transitive dependencies, meaning non-key attributes depend only on the primary key.

  3. A table is in BCNF if:
    a) It is in 2NF and has no multi-valued dependencies
    b) Every determinant is a candidate key
    c) It has no partial dependencies
    d) It contains atomic values only
    Answer: b) Every determinant is a candidate key
    Explanation: Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF, requiring every determinant in a functional dependency to be a candidate key.

  4. Which normal form addresses multi-valued dependencies?
    a) 3NF
    b) 4NF
    c) 5NF
    d) DKNF
    Answer: b) 4NF
    Explanation: Fourth Normal Form (4NF) eliminates non-trivial multi-valued dependencies, ensuring no independent multi-valued facts in a table.

Architecture of DBMS: Client-Server, Open Architectures, Transaction Processing, Multi-User & Concurrency, Backup & Recovery

  1. What is a characteristic of a client-server DBMS architecture?
    a) All processing occurs on a single machine
    b) Clients send requests to a server that manages the database
    c) Data is stored on client machines
    d) No network communication is required
    Answer: b) Clients send requests to a server that manages the database
    Explanation: In a client-server architecture, clients send queries to a centralized server that processes and manages the database.

  2. What is the purpose of transaction processing in a DBMS?
    a) To optimize query performance
    b) To ensure data consistency and integrity
    c) To encrypt database records
    d) To manage hardware resources
    Answer: b) To ensure data consistency and integrity
    Explanation: Transactions follow the ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure reliable data operations.

  3. Which mechanism is used to manage concurrency in a multi-user DBMS?
    a) Indexing
    b) Locking
    c) Hashing
    d) Buffering
    Answer: b) Locking
    Explanation: Locking protocols, such as two-phase locking, manage concurrent access to prevent conflicts in multi-user environments.

Basic Concept of Major RDBMS Products: Oracle, Sybase, DB2, SQL Server

  1. Which RDBMS product is known for its PL/SQL programming language?
    a) Sybase
    b) Oracle
    c) DB2
    d) SQL Server
    Answer: b) Oracle
    Explanation: Oracle uses PL/SQL, a procedural language for writing stored procedures and triggers.

  2. Which RDBMS is developed by Microsoft?
    a) Oracle
    b) Sybase
    c) SQL Server
    d) DB2
    Answer: c) SQL Server
    Explanation: SQL Server is Microsoft’s RDBMS, widely used for enterprise applications.

SQL Queries, Views

  1. Which SQL command is used to modify an existing table’s structure?
    a) SELECT
    b) ALTER
    c) DROP
    d) UPDATE
    Answer: b) ALTER
    Explanation: The ALTER command modifies a table’s structure, such as adding or removing columns.

  2. What is a view in a DBMS?
    a) A physical table storing data
    b) A virtual table based on a query
    c) A backup of the database
    d) A type of index
    Answer: b) A virtual table based on a query
    Explanation: A view is a virtual table created from a query, providing a customized perspective of the data without storing it.

  3. Which SQL clause is used to combine rows from two tables based on a condition?
    a) WHERE
    b) JOIN
    c) GROUP BY
    d) ORDER BY
    Answer: b) JOIN
    Explanation: The JOIN clause combines rows from multiple tables based on a related column, such as a foreign key.

Query Processing and Optimization

  1. What is the first step in query processing?
    a) Execution
    b) Parsing
    c) Optimization
    d) Fetching
    Answer: b) Parsing
    Explanation: Query processing begins with parsing to check the syntax and semantics of the SQL query.

  2. What does query optimization aim to achieve?
    a) Increase data redundancy
    b) Minimize query execution time
    c) Encrypt query results
    d) Reduce data integrity
    Answer: b) Minimize query execution time
    Explanation: Query optimization selects the most efficient execution plan to reduce resource usage and execution time.

Database Storage, Indexing, and Hashing

  1. What is the purpose of indexing in a DBMS?
    a) To increase data redundancy
    b) To improve query retrieval speed
    c) To encrypt database records
    d) To manage transactions
    Answer: b) To improve query retrieval speed
    Explanation: Indexes create a data structure to speed up data retrieval, though they may slow down updates.

  2. Which indexing technique uses a hash function to locate data?
    a) B+ Tree
    b) Bitmap Index
    c) Hash Index
    d) Clustered Index
    Answer: c) Hash Index
    Explanation: Hash indexing uses a hash function to map keys to storage locations, enabling fast retrieval.

Transactions Management and Concurrency Control

  1. Which ACID property ensures that a transaction is fully completed or not executed at all?
    a) Atomicity
    b) Consistency
    c) Isolation
    d) Durability
    Answer: a) Atomicity
    Explanation: Atomicity ensures that a transaction is treated as a single, indivisible unit, either fully completed or rolled back.

  2. Which concurrency control technique uses timestamps to order transactions?
    a) Lock-based protocol
    b) Timestamp-based protocol
    c) Validation-based protocol
    d) Multi-version concurrency control
    Answer: b) Timestamp-based protocol
    Explanation: Timestamp-based protocols assign unique timestamps to transactions to ensure conflict-free execution.

Crash Recovery

  1. What is the role of a log file in crash recovery?
    a) To store query results
    b) To record transaction operations for recovery
    c) To index database records
    d) To encrypt data
    Answer: b) To record transaction operations for recovery
    Explanation: Log files record transaction details to enable recovery by undoing or redoing operations after a crash.

  2. Which recovery technique uses undo and redo operations?
    a) Shadow paging
    b) Log-based recovery
    c) Checkpointing
    d) Buffering
    Answer: b) Log-based recovery
    Explanation: Log-based recovery uses logs to undo incomplete transactions and redo committed ones after a crash.

Distributed Database Systems and Object-Oriented Database Systems

  1. What is a characteristic of a distributed database system?
    a) Data is stored on a single server
    b) Data is spread across multiple locations
    c) No support for transactions
    d) Limited to local access
    Answer: b) Data is spread across multiple locations
    Explanation: Distributed databases store data across multiple sites, requiring coordination for access and consistency.

Concept of Data Warehousing

  1. What is the primary purpose of a data warehouse?
    a) To manage real-time transactions
    b) To store and analyze large volumes of historical data
    c) To index database records
    d) To manage concurrent users
    Answer: b) To store and analyze large volumes of historical data
    Explanation: Data warehouses are designed for analytical processing, storing historical data for reporting and decision-making.

Comments

Post a Comment

Popular posts from this blog

Suppose that a data warehouse for Big-University consists of the following four dimensions: student, course, semester, and instructor, and two measures count and avg_grade. When at the lowest conceptual level (e.g., for a given student, course, semester, and instructor combination), the avg_grade measure stores the actual course grade of the student. At higher conceptual levels, avg_grade stores the average grade for the given combination. a) Draw a snowflake schema diagram for the data warehouse. b) Starting with the base cuboid [student, course, semester, instructor], what specific OLAP operations (e.g., roll-up from semester to year) should one perform in order to list the average grade of CS courses for each BigUniversity student. c) If each dimension has five levels (including all), such as “student < major < status < university < all”, how many cuboids will this cube contain (including the base and apex cuboids)?

-
Image
a) A Snowflake Shema is shown in figure below: b) The specific OLAP operations to be performed: 1. Roll-up on course from course_id to department. 2. Roll-up on a student from student_id to university. 3. Dice on course, student with department ="CS" and university = "biguniversity" 4. Drill-down on a student from the university to student_name. c) N = 4 dimensions The cube will contain 54 = 625 cuboids.
Read more

Pure Versus Partial EC

-
Image
Pure Versus Partial EC EC takes several forms depending on the degree of digitization (the transformation from physical to digital). Companies utilizing pure EC conduct all of their business online. Businesses utilizing partial EC conduct a portion of their business online and a portion of their business off-line OR,    Pure E-electronic commerce refers to a situation where a business transacts its business activities PURELY online. that is, the goods or services sold do not have any physical presence. take a business that sells software, for example, you just pay and download, or one that sells music files online. Partial E-Commerce on the other hand refers to a slightly opposite situation where a business has an online presence, but still has a physical location for the goods and services it sells. For example, Amazon, Walmart, and any other you can think of. you buy the goods online but receive them physically.                   ...
Read more

Suppose that a data warehouse consists of the three dimensions time, doctor, and patient, and the two measures count and charge, where a charge is the fee that a doctor charges a patient for a visit. a) Draw a schema diagram for the above data warehouse using one of the schemas. [star, snowflake, fact constellation] b) Starting with the base cuboid [day, doctor, patient], what specific OLAP operations should be performed in order to list the total fee collected by each doctor in 2004? c) To obtain the same list, write an SQL query assuming the data are stored in a relational database with the schema fee (day, month, year, doctor, hospital, patient, count, charge)

-
Image
Solution: a) Star Schema is shown in the figure below: b) First, we should use the roll-up operation to get the year 2004(rolling-up from the day then month to a year). After getting that, we need to use the slice operation to select (2004). Second, we should use roll-up operation again to get all patients. Then, we need to use the slice operation to select (all). Finally, we get a list of the total fee collected by each doctor in 2004. So, 1. roll up from day to month to year 2. slice for year = “2004” 3. roll up on patient from the individual patient to all 4. slice for patient = “all” 5. get the list of the total fees collected by each doctor in 2004 c) Select doctor, Sum(charge) From fee Where year = 2004 Group by doctor
Read more