Computer Knowledge

Database Management Systems

5,543 Questions

Database Management Systems (DBMS) form the core framework for data storage, retrieval, and security in modern software applications. Concepts such as the E-R model, backup planning, SQL integration, and big data architecture are essential for computer knowledge sections. This hub offers a comprehensive set of practice questions to master DBMS fundamentals and advanced database operations.

E-R Model ConceptsBackup and RecoverySQL Server UpgradesJDBC and ODBCBig Data CharacteristicsData VirtualizationOracle Database

Database Management Systems Questions

Multiple choice

Consider the following log sequence of two transactions on a bank account, with initial balance of Rs. 12,000, that transfers 2000 to a mortgage payment and then applies a 5% interest.

  1. T1 start
  2. T1 B old = 1200 new = 10000
  3. T1 M old = 0 new = 2000
  4. T1 commit
  5. T2 start
  6. T2 B old = 10000 new = 10500
  7. T2 commit

Suppose the database system crashes just before log record 7 is written. When the system is restarted, which statement is true for the recovery procedure?

  1. We must redo log record 6 to set B to 10500.

  2. We must undo log record 6 to set B to 10000 and then redo log records 2 and 3.

  3. We need not redo log records 2 and 3 because transaction T1 has committed.

  4. We can apply redo and undo operations in arbitrary order because they are idempotent.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Multiple choice

Consider the following relational schemes for a library database: Book Title, Author, Catalog_ no, Publisher, Year, Pr ice Collection Title, Author, Catalog_ no within the functional dependencies: I. Title Author $\rightarrow$ Catalog_no II. Catalog_no $\rightarrow$ Title Author Publisher Year III. Publisher Title Year $\rightarrow$ Pr ice

Assume {Author, Title} is the key for both schemes. Which of the following statements is true?

  1. Both Book and Collection are in BCNF

  2. Both Book and Collection are in 3NF only

  3. Book is in 2NF and Collection is in 3NF

  4. Both Book and Collection are in 2NF only
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Multiple choice

Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real) Assume that in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependency is implied other than those implied by primary and candidate keys.

Which of the following statements is TRUE about the above schema?

  1. The schema is in BCNF.

  2. The schema is in 3NF, but not in BCNF.

  3. The schema is in 2NF, but not in 3NF.

  4. The schema is not in 2NF.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

 The schema is in 3NF, but not in BCNF. (sname, city) forms the candidate key.
Suppliers(sid, sname, city, street)
sname is primary key.
sid, sname is also key.
city--> street
street is functionally dependent upon city.
sname--> city
So, transitivity occurs.
So, the schema is in 3NF, but not in BCNF.

Multiple choice

To be considered minimally relational, the DBMS must support of the following key relational functions.

  1. SELECT, PROJECT, UNION

  2. SELECT, PROJECT, JOIN

  3. SELECT, UNION, JOIN

  4. SELECT, UNION, INTERSECT
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Relational algebra defines the theoretical way of manipulating table contents using the eight relational functions. To be considered minimally relational, the DBMS must support the key relational functions SELECT, PROJECT and JOIN.

Multiple choice

Find the odd man out.

  1. Desktop database

  2. Work group database

  3. Distributed database

  4. Enterprise database
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Desktop database, workgroup database and enterprise database is the classification of DBMS according to number of users where a distributed DBMS is classification according to database site location.

Multiple choice

Database that are designed and managed specifically to meet information needs are called

  1. production databases

  2. transaction database

  3. data warehouses

  4. database management system
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

Production database are also known as transaction databases. Although such database yield streams of useful information, there are specialized databases, known as data warehouses that are designed and managed specifically to meet information needs. The data warehouses derive their data from production databases.

Multiple choice

A file system is said to exhibit data dependence, because

  1. a change in any file's structure such as the addition or deletion of a field, requires the modification of all programs using that file

  2. organization of the data, within the file is determined by the data's expected use

  3. data is the most important part of a file system, which also includes hardware software, people and procedures

  4. when any of the files data characteristics change, all data access programs are subjects to change
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A file system is said to exhibit structural dependence because a change in any file's structure such as addition or deletion of a field, requires modification of all programs using that file. That means access to a file is dependent on its structure.

Multiple choice

The data retrieval time factor is most critical for

  1. transactional DBMS

  2. decision DBMS

  3. data warehouse

  4. both (2) and (3)
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Transactions such as product or service sates payments and supply purchases reflect critical day to day operations such transactions are time critical and must be recorded accurately and immediately.

Multiple choice

Which of the following is not supported by the hierarchical database model?

  1. Structural independence

  2. Data independence

  3. Database integrity

  4. Database security
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The most important advantages of hierarchical models are conceptual simplicity, database security, data independence, database integrity and efficiency. Hierarchical database model lacks structural independence.

Multiple choice

Consider the following transaction T0 and T1<To start><To , A, 1050, 970><To, B, 2030, 2070><To commit><T1 start><T1, C, 720,640><T1 commit> If the system crashes just after log record for the step write C of transaction T1 has been written to stable storage, the log at the time of crash will be

  1. <T0 start> <T0, A, 935><T0, B, 2055>

  2. <T0 start><T0, A, 957><T0, B, 2055><T0 commit><T1 start><T1, C, 640>

  3. <T0 start><T0, A, 957><T0, B, 2055><T0 commit><T1 start><T1, C, 62><T1, Commit>

  4. none of the above
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The log at the time of crash will be option B, which reaches up to commit statement but not including commit statement. Option (2) gives the correct log at the time of crash.

Multiple choice

Which of the following does the database administrator coordinate?

  1. DBMS and data base

  2. Application program and data base

  3. Data base and users

  4. Application program and users
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The database administrator serves as the intermediary between the database system and its users, managing access, security, performance, and ensuring users can effectively utilize the database resources.

Multiple choice

A relational database, which is in 3NF, may have undesirable data redundancy because there may exist

  1. non-trivial functional dependencies involving prime attributes on the right side

  2. non -trivial functional dependencies involving only prime attributes

  3. either (1) or (2)

  4. both (1) and (2)
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Third Normal Form (3NF) only eliminates transitive dependencies where non-prime attributes depend on other non-prime attributes. Functional dependencies involving prime attributes (either as determinant or dependent) can still exist and cause data redundancy even in 3NF relations.