Database MCQ Set 1
1. Which one of the following is a set of one or more attributes taken collectively to uniquely identify a record?
a) Candidate key
b) Sub key
c) Super key
d) Foreign key
Answer
Answer: c [Reason:] Super key is the superset of all the keys in a relation.
2. Consider attributes ID , CITY and NAME . Which one of this can be considered as a super key ?
a) NAME
b) ID
c) CITY
d) CITY , ID
Answer
Answer: b [Reason:] Here the id is the only attribute which can be taken as a key. Other attributes are not uniquely identified .
3. The subset of super key is a candidate key under what condition ?
a) No proper subset is a super key
b) All subsets are super keys
c) Subset is a super key
d) Each subset is a super key
Answer
Answer: a [Reason:] The subset of a set cannot be the same set.Candidate key is a set from a super key which cannot be the whole of the super set
4. A _____ is a property of the entire relation, rather than of the individual tuples in which each tuple is unique.
a) Rows
b) Key
c) Attribute
d) Fields
Answer
Answer: b [Reason:] Key is the constraint which specifies uniqueness.
5. Which one of the following attribute can be taken as a primary key ?
a) Name
b) Street
c) Id
d) Department
Answer
Answer: c [Reason:] The attributes name , street and department can repeat for some tuples.But the id attribute has to be unique .So it forms a primary key.
6. Which one of the following cannot be taken as a primary key ?
a) Id
b) Register number
c) Dept_id
d) Street
Answer
Answer: d [Reason:] Street is the only attribute which can occur more than once.
7. A attribute in a relation is a foreign key if the _______ key from one relation is used as an attribute in that relation .
a) Candidate
b) Primary
c) Super
d) Sub
Answer
Answer: b [Reason:] The primary key has to be referred in the other relation to form a foreign key in that relation.
8. The relation with the attribute which is the primary key is referenced in another relation. The relation which has the attribute as primary key is called
a) Referential relation
b) Referencing relation
c) Referenced relation
d) Referred relation
Answer
Answer: b [Reason:] None.
9. The ______ is the one in which the primary key of one relation is used as a normal attribute in another relation.
a) Referential relation
b) Referencing relation
c) Referenced relation
d) Referred relation
Answer
Answer: c [Reason:] None.
10. A ___ integrity constraint requires that the values appearing in specified attributes of any tuple in the referencing relation also appear in specified attributes of at least one tuple in the referenced relation.
a) Referential
b) Referencing
c) Specific
d) Primary
Answer
Answer: a [Reason:] A relation, say r1, may include among its attributes the primary key of another relation, say r2. This attribute is called a foreign key from r1, referencing r2. The relation r1 is also called the referencing relation of the foreign key dependency, and r2 is called the referenced relation of the foreign key.
Database MCQ Set 2
1. In order to maintain transactional integrity and database consistency, what technology does a DBMS deploy?
a) Triggers
b) Pointers
c) Locks
d) Cursors
Answer
Answer: c [Reason:] Locks are used to maintain database consistency.
2. A lock that allows concurrent transactions to access different rows of the same table is known as a
a) Database-level lock
b) Table-level lock
c) Page-level lock
d) Row-level lock
Answer
Answer: d [Reason:] Locks are used to maintain database consistency.
3. Which of the following are introduced to reduce the overheads caused by the log-based recovery?
a) Checkpoints
b) Indices
c) Deadlocks
d) Locks
Answer
Answer: d [Reason:] Checkpoints are introduced to reduce overheads caused by the log-based recovery.
4. Which of the following protocols ensures conflict serializability and safety from deadlocks?
a) Two-phase locking protocol
b) Time-stamp ordering protocol
c) Graph based protocol
d) None of the mentioned
Answer
Answer: b [Reason:] Time-stamp ordering protocol ensures conflict serializability and safety from deadlocks.
5. Which of the following is the block that is not permitted to be written back to the disk?
a) Dead code
b) Read only
c) Pinned
d) Zapped
Answer
Answer: c [Reason:] A block that is not permitted to be written back to the disk is called pinned.
6. If transaction Ti gets an explicit lock on the file Fc in exclusive mode, then it has an ____ on all the records belonging to that file.
a) Explicit lock in exclusive mode
b) Implicit lock in shared mode
c) Explicit lock in shared mode
d) Implicit lock in exclusive mode
Answer
Answer: d [Reason:] If transaction Ti gets an explicit lock on the file Fc in exclusive mode, then it has an implicit lock in exclusive mode on all the records belonging to that file.
7. Which refers to a property of computer to run several operation simultaneously and possible as computers await response of each other
a) Concurrency
b) Deadlock
c) Backup
d) Recovery
Answer
Answer: a [Reason:] Concurrency is a property of systems in which several computations are executing simultaneously, and potentially interacting with each other.
8. All lock information is managed by a ____ which is responsible for assigning and policing the locks used by the transactions.
a) Scheduler
b) DBMS
c) Lock manager
d) Locking agent
Answer
Answer: c [Reason:] A distributed lock manager (DLM) provides distributed software applications with a means to synchronize their accesses to shared resources.
9. The ____ lock allows concurrent transactions to access the same row as long as they require the use of different fields within that row.
a) Table-level
b) Page-level
c) Row-level
d) Field-level
Answer
Answer: d [Reason:] Lock is limited to the attributes of the relation.
10. Which of the following is a procedure for acquiring the necessary locks for a transaction where all necessary locks are acquired before any are released?
a) Record controller
b) Exclusive lock
c) Authorization rule
d) Two phase lock
Answer
Answer: d [Reason:] Two-phase lock is a procedure for acquiring the necessary locks for a transaction where all necessary locks are acquired before any are released.
Database MCQ Set 3
1. Which lock should be obtained to prevent a concurrent transaction from executing a conflicting read, insert or delete operation on the same key value.
a) Higher-level lock
b) Lower-level lock
c) Read only lock
d) Read write
Answer
Answer: a [Reason:] Operations acquire lower-level locks while they execute, but release them when they complete; the corresponding transaction must however retain a higher-level lock in a two-phase manner to prevent concurrent transactions from executing conflicting actions.
2. Once the lower-level lock is released, the operation cannot be undone by using the old values of updated data items, and must instead be undone by executing a compensating operation; such an operation is called
a) Logical operation
b) Redo operation
c) Logical undo operation
d) Undo operation
Answer
Answer: a [Reason:] It is important that the lower-level locks acquired during an operation are sufficient to perform a subsequent logical undo of the operation.
3. Which of the following is used for undo operations alone ?
a) Logical logging
b) Physical logging
c) Physical log records
d) Physical logging and Physical log records
Answer
Answer: a [Reason:] If the operation inserted an entry in a B+-tree, the undo information U would indicate that a deletion operation is to be performed, and would identify the B+-tree and what entry to delete from the tree. Such logging of information about operations is called logical logging.
4. Redo operations are performed exclusively using
a) Logical logging
b) Physical logging
c) Physical log records
d) Both Physical logging and Physical log records
Answer
Answer: d [Reason:] Logging of old-value and new-value information is called physical logging.
5. To perform logical redo or undo, the database state on disk must be operation _____ that is, it should not have partial effects of any operation.
a) Persistent
b) Resistant
c) Consistent
d) None of the mentioned
Answer
Answer: c [Reason:] Data structures such as B+-trees would not be in a consistent state, and neither logical redo nor logical undo operations can be performed on an inconsistent data structure.
6. An operation is said to be ____ if executing it several times in a row gives the same result as executing it once.
a) Idempotent
b) Changed
c) Repetitive
d) All of the above
Answer
Answer: a [Reason:] Operations such as inserting an entry into a B+-tree may not be idempotent, and the recovery algorithm must therefore make sure that an operation that has already been performed is not performed again.
7. Immediate database modification technique uses
a) Both undo and redo
b) Undo but no redo
c) Redo but no undo
d) Neither undo nor redo
Answer
Answer: a [Reason:] Undo erases all the changes and redo makes the deleted changes.
8. Shadow paging has
a) no redo
b) no undo
c) redo but no undo
d) neither redo nor undo
Answer
Answer: a [Reason:] Undo erases all the changes and redo makes the deleted changes.
9. For correct behaviour during recovery, undo and redo operation must be
a) Commutative
b) Associative
c) Idempotent
d) Distributive
Answer
Answer: c [Reason:] Undo erases all the changes and redo makes the deleted changes.
10. If _____ are not obtained in undo operation it will cause problem in undo-phase.
a) Higher-level lock
b) Lower-level lock
c) Read only lock
d) Read write
Answer
Answer: b [Reason:] Operations acquire lower-level locks while they execute, but release them when they complete; the corresponding transaction must however retain a higher-level lock in a two-phase manner to prevent concurrent transactions from executing conflicting actions.
Database MCQ Set 4
1. In magnetic disk ________ stores information on a sector magnetically as reversals of the direction of magnetization of the magnetic material.
a) Read–write head
b) Read-assemble head
c) Head–disk assemblies
d) Disk arm
Answer
Answer: d [Reason:] Each side of a platter of a disk has a read–write head that moves across the platter to access different tracks.
2. A ____ is the smallest unit of information that can be read from or written to the disk.
a) Track
b) Spindle
c) Sector
d) Platter
Answer
Answer: c [Reason:] The disk surface is logically divided into tracks, which are subdivided into sectors.
3. The disk platters mounted on a spindle and the heads mounted on a disk arm are together known as _____
a) Read-disk assemblies
b) Head–disk assemblies
c) Head-write assemblies
d) Read-read assemblies
Answer
Answer: b [Reason:] Each side of a platter of a disk has a read–write head that moves across the platter to access different tracks.
4. The disk controller uses ________ at each sector to ensure that the data is not corrupted on data retrieval.
a) Checksum
b) Unit drive
c) Read disk
d) Readsum
Answer
Answer: a [Reason:] A disk controller interfaces between the computer system and the actual hardware of the disk drive.
5. ___ is the time from when a read or write request is issued to when data transfer begins.
a) Access time
b) Average seek time
c) Seek time
d) Rotational latency time
Answer
Answer: a [Reason:] To access (that is, to read or write) data on a given sector of a disk, the arm first must move so that it is positioned over the correct track, and then must wait for the sector to appear under it as the disk rotates.
6. The time for repositioning the arm is called the ________ and it increases with the distance that the arm must move.
a) Access time
b) Average seek time
c) Seek time
d) Rotational latency time
Answer
Answer: c [Reason:] Typical seek times range from 2 to 30 milliseconds, depending on how far the track is from the initial arm position.
7. ___ is around one-half of the maximum seek time.
a) Access time
b) Average seek time
c) Seek time
d) Rotational latency time
Answer
Answer: b [Reason:] Average seek times currently range between 4 and 10 milliseconds, depending on the disk model.
8. Once the head has reached the desired track, the time spent waiting for the sector to be accessed to appear under the head is called the ___
a) Access time
b) Average seek time
c) Seek time
d) Rotational latency time
Answer
Answer: d [Reason:] Rotational speeds of disks today range from 5400 rotations per minute (90 rotations per second) up to 15,000 rotations per minute (250 rotations per second), or, equivalently, 4 milliseconds to 11.1 milliseconds per rotation.
9. In Flash memory, the erase operation can be performed on a number of pages, called an _______ at once, and takes about 1 to 2 milliseconds.
a) Delete block
b) Erase block
c) Flash block
d) Read block
Answer
Answer: b [Reason:] The size of an erase block (often referred to as just “block” in flash literature) is usually significantly larger than the block size of the storage system.
10. Hybrid disk drives are hard-disk systems that combine magnetic storage with a smaller amount of flash memory, which is used as a cache for frequently accessed data.
a) Hybrid drivers
b) Disk drivers
c) Hybrid disk drivers
d) All of the mentioned
Answer
Answer: b [Reason:] Frequently accessed data that are rarely updated are ideal for caching in flash memory.
Database MCQ Set 5
1. Which normal form is considered adequate for normal relational database design?
a) 2NF
b) 5NF
c) 4NF
d) 3NF
Answer
Answer: d [Reason:] A relational database table is often described as “normalized” if it is in the Third Normal Form because most of the 3NF tables are free of insertion, update, and deletion anomalies.
2. Consider a schema R(A, B, C, D) and functional dependencies A -> B and C -> D. Then the decomposition of R into R1 (A, B) and R2(C, D) is
a) dependency preserving and lossless join
b) lossless join but not dependency preserving
c) dependency preserving but not lossless join
d) not dependency preserving and not lossless join
Answer
Answer: d [Reason:] While decomposing a relational table we must verify the following properties:
i) Dependency Preserving Property
ii) Lossless-Join Property.
3. Relation R with an associated set of functional dependencies, F, is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set of relations is
a) Zero
b) More than zero but less than that of an equivalent 3NF decomposition
c) Proportional to the size of F+
d) Indeterminate
Answer
Answer: b [Reason:] Redundancy in BCNF is low when compared to 3NF.
4. Which one of the following statements about normal forms is FALSE?
a) BCNF is stricter than 3NF
b) Lossless, dependency-preserving decomposition into 3NF is always possible
c) Lossless, dependency-preserving decomposition into BCNF is always possible
d) Any relation with two attributes is in BCNF
Answer
Answer: c [Reason:] Achieving Lossless and dependency-preserving decomposition property into BCNF is difficult.
5. A table has fields F1, F2, F3, F4, and F5, with the following functional dependencies:
F1->F3 F2->F4 (F1,F2)->F5
in terms of normalization, this table is in
a) 1NF
b) 2NF
c) 3NF
d) None of the mentioned
Answer
Answer: a [Reason:] Since the primary key is not given we have to derive the primary key of the table. Using the closure set of attributes we get the primary key as (F1,F2). From functional dependencies, “F1->F3, F2->F4”, we can see that there is partial functional dependency therefore it is not in 1NF. Hence the table is in 1NF.
6. Which of the following is TRUE?
a) Every relation in 2NF is also in BCNF
b) A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
c) Every relation in BCNF is also in 3NF
d) No relation can be in both BCNF and 3NF
Answer
Answer: c [Reason:] A relational database table is often described as “normalized” if it is in the Third Normal Form because most of the 3NF tables are free of insertion, update, and deletion anomalies.
7. Consider the following functional dependencies in a database.
Date_of_Birth->Age Age->Eligibility Name->Roll_number Roll_number->Name Course_number->Course_name Course_number->Instructor (Roll_number, Course_number)->Grade
The relation (Roll_number, Name, Date_of_birth, Age) is
a) In second normal form but not in third normal form
b) In third normal form but not in BCNF
c) In BCNF
d) None of the mentioned
Answer
Answer: d [Reason:] For the given relation only some of the above FDs are applicable. The applicable FDs are given below:
Date_of_Birth->Age
Name->Roll_number
Roll_number->Name
Finding the closure set of attributes we get the candidate keys:(Roll_number,Date_of_Birth), and (Name,Date_of_Birth) .
On selecting any one of the candidate key we can see that the FD Date_of_Birth->Age is a partial dependency. Hence the relation is in 1NF.
8. The relation schema Student_Performance (name, courseNo, rollNo, grade) has the following FDs:
name,courseNo->grade rollNo,courseNo->grade name->rollNo rollNo->name
The highest normal form of this relation scheme is
a) 2NF
b) 3NF
c) BCNF
d) 4NF
Answer
Answer: b [Reason:] A super key is a combination of prime attributes and one or more non-prime key attribute(s). It also uniquely identifies a record in a table. Primary key can be defined as super key with minimal attributes.
9. The relation EMPDT1 is defined with attributes empcode(unique), name, street, city, state, and pincode. For any pincode,there is only one city and state. Also, for any given street, city and state, there is just one pincode. In normalization terms EMPDT1 is a relation in
a) 1NF only
b) 2NF and hence also in 1NF
c) 3NF and hence also in 2NF and 1NF
d) BCNF and hence also in 3NF, 2NF and 1NF
Answer
Answer: b [Reason:] Empcode is unique, therefore it is the primary key. Since the primary key consists of a single attribute there will be no partial dependency, hence the relation is in 2NF.
From the question we get the FDs as below:
pincode -> city, state
street,city,state -> pincode
From the FDs we can see that there are transitive dependencies, hence the table is not in 3NF.
10. Which one of the following statements if FALSE?
a) Any relation with two attributes is in BCNF
b) A relation in which every key has only one attribute is in 2NF
c) A prime attribute can be transitively dependent on a key in a 3 NF relation
d) A prime attribute can be transitively dependent on a key in a BCNF relation
Answer
Answer: d [Reason:] A table is in 3NF if and only if, for each of its functional dependencies X -> A, at least one of the following conditions holds:
* X contains A (that is, X -> A is trivial functional dependency), or
* X is a superkey, or
* A should be prime attribute.
