Category: Database Management System Objective Questions

Database Multiple Choice Questions on “Sorting”.

1. Two main measures for the efficiency of an algorithm are
a) Processor and memory
b) Complexity and capacity
c) Time and space
d) Data and space

Answer: c
Clarification: Depending on the time and space complexity only the algorithm for sorting will be chosen.

2. The time factor when determining the efficiency of an algorithm is measured by
a) Counting microseconds
b) Counting the number of key operations
c) Counting the number of statements
d) Counting the kilobytes of algorithm

Answer: b
Clarification: The operations taking place with the time and space is counted.

3. The space factor when determining the efficiency of an algorithm is measured by
a) Counting the maximum memory needed by the algorithm
b) Counting the minimum memory needed by the algorithm
c) Counting the average memory needed by the algorithm
d) Counting the maximum disk space needed by the algorithm

Answer: a
Clarification: Time complexity maintains the maximum time needed.

4. Which of the following case does not exist in complexity theory
a) Best case
b) Worst case
c) Average case
d) Null case

Answer: d
Clarification: Null case cannot be counted as the factor for complexity.

5. The Worst case occur in linear search algorithm when
a) Item is somewhere in the middle of the array
b) Item is not in the array at all
c) Item is the last element in the array
d) Item is the last element in the array or is not there at all

Answer: d
Clarification: Algorithmic complexity is concerned about how fast or slow particular algorithm performs.

6. The Average case occur in linear search algorithm
a) When Item is somewhere in the middle of the array
b) When Item is not in the array at all
c) When Item is the last element in the array
d) When Item is the last element in the array or is not there at all

Answer: a
Clarification: Algorithmic complexity is concerned about how fast or slow particular algorithm performs.

7. The complexity of the average case of an algorithm is
a) Much more complicated to analyze than that of worst case
b) Much more simpler to analyze than that of worst case
c) Sometimes more complicated and some other times simpler than that of worst case
d) None of the mentioned

Answer: a
Clarification: Algorithmic complexity is concerned about how fast or slow particular algorithm performs.

8. The complexity of a linear search algorithm is
a) O(n)
b) O(log n)
c) O(n2)
d) O(n log n)

Answer: a
Clarification: It refers to n values complexity in the algorithm which can be reduced by choosing the other algorithms.

9. The complexity of Binary search algorithm is
a) O(n)
b) O(log )
c) O(n2)
d) O(n log n)

Answer: b
Clarification: This shows that it has a standard complexity in addressing.

10. The complexity of Bubble sort algorithm is
a) O(n)
b) O(log n)
c) O(n2)
d) O(n log n)

Answer: c
Clarification: Bubble sort, is a simple sorting algorithm that works by repeatedly stepping through the list to be sorted, comparing each pair of adjacent items and swapping them if they are in the wrong order.

Database Multiple Choice Questions on “Lock-Based Protocols”.

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: c
Clarification: 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: d
Clarification: 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: a
Clarification: 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: b
Clarification: 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: c
Clarification: 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: d
Clarification: 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: a
Clarification: 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: c
Clarification: 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: d
Clarification: 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: d
Clarification: 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 Multiple Choice Questions on “Integrity Constraints”.

1. To include integrity constraint in an existing relation use :
a) Create table
b) Modify table
c) Alter table
d) Drop table

Answer: c
Clarification: SYNTAX – alter table table-name add constraint, where constraint can be any constraint on the relation.

2. Which of the following is not an integrity constraint?
a) Not null
b) Positive
c) Unique
d) Check ‘predicate’

Answer: b
Clarification: Positive is a value and not a constraint.

3.

CREATE TABLE Employee(Emp_id NUMERIC NOT NULL, Name VARCHAR(20) , dept_name VARCHAR(20), Salary NUMERIC UNIQUE(Emp_id,Name));
INSERT INTO Employee VALUES(1002, Ross, CSE, 10000)
INSERT INTO Employee VALUES(1006,Ted,Finance, );
INSERT INTO Employee VALUES(1002,Rita,Sales,20000);

What will be the result of the query?
a) All statements executed
b) Error in create statement
c) Error in insert into Employee values(1006,Ted,Finance, );
d) Error in insert into Employee values(1008,Ross,Sales,20000);

Answer: d
Clarification: The not null specification prohibits the insertion of a null value for the attribute.
The unique specification says that no two tuples in the relation can be equal on all the listed attributes.

4.

CREATE TABLE Manager(ID NUMERIC,Name VARCHAR(20),budget NUMERIC,Details VARCHAR(30));

Inorder to ensure that the value of budget is non-negative which of the following should be used?
a) Check(budget>0)
b) Check(budget<0)
c) Alter(budget>0)
d) Alter(budget<0)

Answer: a
Clarification: A common use of the check clause is to ensure that attribute values satisfy specified conditions, in effect creating a powerful type system.

5. Foreign key is the one in which the ________ of one relation is referenced in another relation.
a) Foreign key
b) Primary key
c) References
d) Check constraint

Answer: b
Clarification: The foreign-key declaration specifies that for each course tuple, the department name specified in the tuple must exist in the department relation.

6.

CREATE TABLE course
( . . .
FOREIGN KEY (dept name) REFERENCES department
. . . );

Which of the following is used to delete the entries in the referenced table when the tuple is deleted in course table?
a) Delete
b) Delete cascade
c) Set null
d) All of the mentioned

Answer: b
Clarification: The delete “cascades” to the course relation, deletes the tuple that refers to the department that was deleted.

7. Domain constraints, functional dependency and referential integrity are special forms of _________
a) Foreign key
b) Primary key
c) Assertion
d) Referential constraint

Answer: c
Clarification: An assertion is a predicate expressing a condition we wish the database to always satisfy.

8. Which of the following is the right syntax for the assertion?
a) Create assertion ‘assertion-name’ check ‘predicate’;
b) Create assertion check ‘predicate’ ‘assertion-name’;
c) Create assertions ‘predicates’;
d) All of the mentioned

Answer: a
Clarification: None.

9. Data integrity constraints are used to:
a) Control who is allowed access to the data
b) Ensure that duplicate records are not entered into the table
c) Improve the quality of data entered for a specific property (i.e., table column)
d) Prevent users from changing the values stored in the table

Answer: c
Clarification: None.

10. Which of the following can be addressed by enforcing a referential integrity constraint?
a) All phone numbers must include the area code
b) Certain fields are required (such as the email address, or phone number) before the record is accepted
c) Information on the customer must be known before anything can be sold to that customer
d) When entering an order quantity, the user must input a number and not some text (i.e., 12 rather than ‘a dozen’)

Answer: c
Clarification: The information can be referred to and obtained.

Database Multiple Choice Questions on “Querying database part-1 DDL”.

SQL data definition for part of the university database.

CREATE TABLE department
(dept_name VARCHAR (20),
building VARCHAR (15),
budget NUMBER,
PRIMARY KEY (dept_name));
 
CREATE TABLE course
(course_id VARCHAR (7),
title VARCHAR (50),
dept_name VARCHAR (20),
credits NUMERIC (2,0),
PRIMARY KEY (course_id),
FOREIGN KEY (dept_name) __________ department);
 
CREATE TABLE instructor
(ID VARCHAR (5),
name VARCHAR (20) NOT NULL,
dept_name VARCHAR (20),
salary NUMERIC (8,2),
FOREIGN KEY (dept_name) _______ department);
 
CREATE TABLE SECTION
(course_id VARCHAR (8),
sec_id VARCHAR (8),
semester VARCHAR (6),
YEAR NUMERIC (4,0),
building VARCHAR (15),
room_number VARCHAR (7),
time_slot id VARCHAR (4),
PRIMARY KEY (course_id, sec_id, semester, YEAR),
FOREIGN KEY (_______) ______ course);
 
CREATE TABLE teaches
(ID VARCHAR (5),
course_id VARCHAR (8),
sec_id VARCHAR (8),
semester VARCHAR (6),
YEAR NUMERIC (4,0),
PRIMARY KEY (ID, course_id, sec_id, semester, YEAR),
FOREIGN KEY (course_id, sec_id, semester, YEAR) REFERENCES SECTION,
FOREIGN KEY (ID) _______ instructor);

Answer questions based on the above commands

1. Which is the main relation which is used in the university database which is referenced by all other relation of the university?
a) Teaches
b) Course
c) Department
d) Section

Answer: c
Clarification: Department is the only relation which forms the main part of the university database.

2. The department relation has the an entry budget whose type has to be replaced by
a) Varchar (20)
b) Varchar2 (20)
c) Numeric (12,2)
d) Numeric

Answer: c
Clarification: Department is the only relation which forms the main part of the university database.

3. In the course relation, the title field should throw an error in case of any missing title. The command to be added in title is
a) Unique
b) Not null
c) 0
d) Null

Answer: b
Clarification: By specifying not null the value cannot be left blank.

4. In the above DDL command the foreign key entries are got by using the keyword
a) References
b) Key reference
c) Relating
d) None of the mentioned

Answer: a
Clarification: References (table_name) give the prior table name for the entry.

5. Identify the error in the section relation
a) No error
b) Year numeric (4,0)
c) Building varchar (15)
d) Sec_id varchar (8)

Answer: a
Clarification: The building and the sec_id have varchar values and year is of numeric type. So no such errors are found in the relation.

6. The following entry is given in to the instructor relation .

(100202,Drake,Biology,30000)

Identify the output of the query given
a) Row(s) inserted
b) Error in ID of insert
c) Error in Name of insert
d) Error in Salary of the insert

Answer: b
Clarification: The varchar(5) value cannot hold the entry 100202.

7. Which of the following can be used as a primary key entry of the instructor relation.
a) DEPT_NAME
b) NAME
c) ID
d) All of the mentioned

Answer: c
Clarification: The value ID can only be primary key unlike dept_name which is used as a foreign key.

8. In the section relation which of the following is used as a foreign key?
a) Course_id
b) Course_id,sec_id
c) Room_number
d) Course_id,sec_id,room_number

Answer: a
Clarification: Course_id is the only field which is present in the course relation.

9. In order to include an attribute Name to the teaches relation which of the following command is used?
a) Alter table teaches include Name;
b) Alter table teaches add Name;
c) Alter table teaches add Name varchar;
d) Alter table teaches add Name varchar(20);

Answer: d
Clarification: The form of the alter table command is
alter table r add AD;
where r is the name of an existing relation, A is the name of the attribute to be added, and D is the type of the added attribute.

10. To replace the relation section with some other relation the initial step to be carried out is
a) Delete section;
b) Drop section;
c) Delete from section;
d) Replace section new_table ;

Answer: b
Clarification: Droping the table drops all the references to that table.

Database Multiple Choice Questions on “Physical Storage Media”.

1. Which of the following is a physical storage media?
a) Tape Storage
b) Optical Storage
c) Flash memory
d) All of the mentioned

Answer: d
Clarification: The storage media are classified by the speed with which data can be accessed, by the cost per unit of data to buy the medium, and by the medium’s reliability.

2. The _________ is the fastest and most costly form of storage, which is relatively small; its use is managed by the computer system hardware.
a) Cache
b) Disk
c) Main memory
d) Flash memory

Answer: a
Clarification: Cache storage is easy to access because it is closer to the processor.

3. Which of the following stores several gigabytes of data but usually lost when power failure?
a) Flash memory
b) Disk
c) Main memory
d) Secondary memory

Answer: c
Clarification: The contents of main memory are usually lost if a power failure or system crash occurs.

4. The flash memory storage used are
a) NOR Flash
b) OR Flash
c) AND Flash
d) All of the mentioned

Answer: a
Clarification: NAND flash has a much higher storage capacity for a given cost, and is widely used for data storage in devices such as cameras, music players, and cell phones.

5. __________ is increasingly being used in server systems to improve performance by caching frequently used data, since it provides faster access than disk, with larger storage capacity than main memory.
a) Flash memory
b) Disk
c) Main memory
d) Secondary memory

Answer: a
Clarification: Flash memory is of two types – NAND and NOR.

6. Which is the cheapest memory device in terms of costs/ bit?
a) Semiconductor memory
b) Magnetic disks
c) Compact disks
d) Magnetic tapes

Answer: c
Clarification: Compact disk is used for easy storage at lower cost.

7. The primary medium for the long-term online storage of data is the __________ where the entire database is stored on magnetic disk.
a) Semiconductor memory
b) Magnetic disks
c) Compact disks
d) Magnetic tapes

Answer: b
Clarification: The system must move the data from disk to main memory so that they can be accessed.

8. Optical disk _______ systems contain a few drives and numerous disks that can be loaded into one of the drives automatically (by a robot arm) on demand.
a) Tape Storage
b) Jukebox
c) Flash memory
d) All of the mentioned

Answer: b
Clarification: The most popular form of optical disks are CD and DVD.

9. There are “record-once” versions of the compact disk and digital video disk, which can be written only once; such disks are also called __________ disks.
a) Write-once, read-many (WORM)
b) CD-R
c) DVD-W
d) CD-ROM

Answer: a
Clarification: There are also “multiple-write” versions of compact disk (called CD-RW) and digital video disk (DVD-RW, DVD+RW, and DVD-RAM), which can be written multiple times.

10. Tape storage is referred to as __________ storage.
a) Direct-access
b) Random-access
c) Sequential-access
d) All of the mentioned

Answer: c
Clarification: Tape storage is used primarily for backup and archival data.

Database Multiple Choice Questions on “Join Operations”.

1. A_____ is a query that retrieves rows from more than one table or view:
a) Start
b) End
c) Join
d) All of the mentioned

Answer: c
Clarification: An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each.

2. A condition is referred to as __________
a) Join in SQL
b) Join condition
c) Join in SQL & Condition
d) None of the mentioned

Answer: b
Clarification: An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each.

3. Which oracle is the join condition is specified using the WHERE clause:
a) Oracle 9i
b) Oracle 8i
c) Pre-oracle 9i
d) Pre-oracle 8i

Answer: c
Clarification: Oracle 9i is a version of the Oracle Database. The i stands for “Internet” to indicate that 9i is “Internet ready”.

4. How many join types in join condition:
a) 2
b) 3
c) 4
d) 5

Answer: d
Clarification: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, EQUIJOIN.

5. Which are the join types in join condition:
a) Cross join
b) Natural join
c) Join with USING clause
d) All of the mentioned

Answer: d
Clarification: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, EQUIJOIN are the types of joins.

6. Which product is returned in a join query have no join condition:
a) Equijoins
b) Cartesian
c) Both Equijoins and Cartesian
d) None of the mentioned

Answer: b
Clarification: A Cartesian coordinate system is a coordinate system that specifies each point uniquely in a plane by a pair of numerical coordinates.

7. Which is a join condition contains an equality operator:
a) Equijoins
b) Cartesian
c) Both Equijoins and Cartesian
d) None of the mentioned

Answer: a
Clarification: An equi-join is a specific type of comparator-based join, that uses only equality comparisons in the join-predicate.

8. Which join refers to join records from the write table that have no matching key in the left table are include in the result set:
a) Left outer join
b) Right outer join
c) Full outer join
d) Half outer join

Answer: b
Clarification: A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order.

9. Which operation are allowed in a join view:
a) UPDATE
b) INSERT
c) DELETE
d) All of the mentioned

Answer: d
Clarification: The DELETE statement is used to delete rows in a table. The UPDATE statement is used to update existing records in a table. The INSERT INTO statement is used to insert new records in a table.

10. Which view that contains more than one table in the top-level FROM clause of the SELECT statement:
a) Join view
b) Datable join view
c) Updatable join view
d) All of the mentioned

Answer: c
Clarification: The DELETE statement is used to delete rows in a table. The UPDATE statement is used to update existing records in a table. The INSERT INTO statement is used to insert new records in a table.