Database MCQ Set 1
1. A relational database system needs to maintain data about the relations, such as the schema of the relations. This is called
a) Metadata
b) Catalog
c) Log
d) Dictionary
Answer
Answer: a [Reason:] Each side of a platter of a disk has a read–write head that moves across the platter to access different tracks.
2. Relational schemas and other metadata about relations are stored in a structure called the ______
a) Metadata
b) Catalog
c) Log
d) Data Dictionary
Answer
Answer: d [Reason:] Data dictionary is also called as system catalog.
3. _____ is the collection of memory structures and Oracle background processes that operates against an Oracle database.
a) Database
b) Instance
c) Tablespace
d) Segment
Answer
Answer: b [Reason:] Instance is a snapshot of database at any point of time.
4. A ________ is a logical grouping of database objects, usually to facilitate security, performance, or the availability of database objects such as tables and indexes.
a) Tablespace
b) Segments
c) Extents
d) Blocks
Answer
Answer: a [Reason:] A tablespace is a storage location where the actual data underlying database objects can be kept.
5. A tablespace is further broken down into ________
a) Tablespace
b) Segments
c) Extents
d) Blocks
Answer
Answer: b [Reason:] Segment names are used in create table and create index commands to place tables or indexes on specific database devices.
6. ____ is a contiguous group of blocks allocated for use as part of a table, index, and so forth.
a) Tablespace
b) Segment
c) Extent
d) Block
Answer
Answer: c [Reason:] An extent is a set of contiguous blocks allocated in a database.
7. ________ is the smallest unit of allocation in an Oracle database.
a) Database
b) Instance
c) Tablespace
d) Database Block
Answer
Answer: d [Reason:] Data block is a form of database space allocation.
8. An Oracle ____ is a set of tables and views that are used as a read-only reference about the database.
a) Database dictionary
b) Dictionary table
c) Data dictionary
d) Dictionary
Answer
Answer: c [Reason:] Data dictionary is also called as system catalog.
9. A data dictionary is created when a ____ created.
a) Instance
b) Segment
c) Database
d) Dictionary
Answer
Answer: c [Reason:] Data dictionary is also called as system catalog.
10. An Oracle object type has two parts the ___ and____
a) Instance and body
b) Segment and blocks
c) Specification and body
d) Body and segment
Answer
Answer: c [Reason:] Segment names are used in create table and create index commands to place tables or indexes on specific database devices. An extent is a set of contiguous blocks allocated in a database.
Database MCQ Set 2
1. Dates must be specified in the format
a) mm/dd/yy
b) yyyy/mm/dd
c) dd/mm/yy
d) yy/dd/mm
Answer
Answer: b [Reason:] yyyy/mm/dd is the default format in sql .
2. An ________ on an attribute of a relation is a data structure that allows the database system to find those tuples in the relation that have a specified value for that attribute efficiently, without scanning through all the tuples of the relation.
a) Index
b) Reference
c) Assertion
d) Timestamp
Answer
Answer: a [Reason:] Index is the reference to the tuples in a relation.
3. Create index studentID_index on student(ID);
Here which one denotes the relation for which index is created ?
a) StudentID_index
b) ID
c) StudentID
d) Student
Answer
Answer: d [Reason:] The statement creates an index named studentID index on the attribute ID of the relation student.
4. Which of the following is used to store movie and image files ?
a) Clob
b) Blob
c) Binary
d) Image
Answer
Answer: b [Reason:] SQL therefore provides large-object data types for character data (clob) and binary data (blob). The letters “lob” in these data types stand for “Large OBject.” .
5. The user defined data type can be created using
a) Create datatype
b) Create data
c) Create definetype
d) Create type
Answer
Answer: d [Reason:] The create type clause can be used to define new types.Syntax : create type Dollars as numeric(12,2) final; .
6. Values of one type can be converted to another domain using which of the following ?
a) Cast
b) Drop type
c) Alter type
d) Convert
Answer
Answer: a [Reason:] Example of cast :cast (department.budget to numeric(12,2)). SQL provides drop type and alter type clauses to drop or modify types that have been created earlier.
7.
CREATE DOMAIN YearlySalary NUMERIC(8,2) CONSTRAINT salary VALUE test ____;
In order to ensure that an instructor’s salary domain allows only values greater than a specified value use:
a) Value>=30000.00
b) Not null;
c) Check(value >= 29000.00);
d) Check(value)
Answer
Answer: c [Reason:] Check(value ‘condition’) is the syntax.
8. Which of the following closely resembles Create view ?
a) Create table . . .like
b) Create table . . . as
c) With data
d) Create view as
Answer
Answer: b [Reason:] The ‘create table . . . as’ statement closely resembles the create view statement and both are defined by using queries.The main difference is that the contents of the table are set when the table is created, whereas the contents of a view always reflect the current query result.
9. In contemporary databases the top level of the hierarchy consists of ______ each of which can contain _____
a) Catalogs, schemas
b) Schemas, catalogs
c) Environment, schemas
d) Schemas, Environment
Answer
Answer: a [Reason:] None.
10. Which of the following statements creates a new table temp instructor that has the same schema as instructor.
a) create table temp_instructor;
b) Create table temp_instructor like instructor;
c) Create Table as temp_instructor;
d) Create table like temp_instructor;
Answer
Answer: b [Reason:] None.
Database MCQ Set 3
1. The _______ is that part of main memory available for storage of copies of disk blocks.
a) Buffer
b) Catalog
c) Storage
d) Secondary storage
Answer
Answer: a [Reason:] There is always a copy kept on disk of every block, but the copy on disk may be a version of the block older than the version in the buffer.
2. A major goal of the database system is to minimize the number of block transfers between the disk and memory. This is achieved by
a) Buffer
b) Catalog
c) Storage
d) Secondary storage
Answer
Answer: a [Reason:] There is always a copy kept on disk of every block, but the copy on disk may be a version of the block older than the version in the buffer.
3. The subsystem responsible for the allocation of buffer space is called the _____
a) Buffer
b) Buffer manager
c) Storage
d) Secondary storage
Answer
Answer: b [Reason:] Programs in a database system make requests (that is, calls) on the buffer manager when they need a block from disk.
4. In the buffer where there is no space for another block, the bllock can be inserted using
a) Pinned block strategy
b) Forced output block
c) Buffer replacement strategy
d) All of the mentioned
Answer
Answer: c [Reason:] Most operating systems use a least recently used (LRU) scheme, in which the block that was referenced least recently is written back to disk and is removed from the buffer.
5. A block that is not allowed to be written back to disk is said to be ________
a) Pinned
b) Forced
c) Buffer
d) All of the mentioned
Answer
Answer: a [Reason:] Although many operating systems do not support pinned blocks, such a feature is essential for a database system that is resilient to crashes.
6. There are situations in which it is necessary to write back the block to disk, even though the buffer space that it occupies is not needed. This write is called the
a) Pinned block strategy
b) Forced output block
c) Buffer replacement strategy
d) All of the mentioned
Answer
Answer: b [Reason:] The main-memory contents and thus buffer contents are lost in a crash, whereas data on disk usually survive a crash.
7. The frequently used buffer replacement strategy is
a) Most recently used
b) Least recently used
c) Longest block
d) All of the mentioned
Answer
Answer: b [Reason:] If a block must be replaced, the least recently referenced block is replaced.
8. In case the buffer manager do not write the blocks properly then the buffer manager uses
a) Replacement strategy
b) Forced strategy
c) Crash recovery system
d) Both Replacement and Forced strategy
Answer
Answer: c [Reason:] The crash-recovery subsystem imposes stringent constraints on block replacement.
9. The technique where the blocks which have been used are replaced is called
a) Replacement strategy
b) Forced strategy
c) Crash recovery system
d) Most recently used
Answer
Answer: d [Reason:] The optimal strategy for block replacement is the most recently used (MRU) strategy.
10. ___ frees the space occupied by a block as soon as the final tuple of that block has been processed.
a) Replacement strategy
b) Forced strategy
c) Toss immediate strategy
d) Most recently used
Answer
Answer: c [Reason:] The optimal strategy for block replacement is the most recently used (MRU) strategy.
Database MCQ Set 4
1. _______ can help us detect poor E-R design.
a) Database Design Process
b) E-R Design Process
c) Relational scheme
d) Functional dependencies
Answer
Answer: d [Reason:] For eg.,Suppose an instructor entity set had attributes dept name and dept address, and there is a functional dependency dept name -> dept address.
2. If a multivalued dependency holds and is not implied by the corresponding functional dependency, it usually arises from one of the following sources.
a) A many-to-many relationship set
b) A multivalued attribute of an entity set
c) A one-to-many relationship set
d) Both A many-to-many relationship set and A multivalued attribute of an entity set
Answer
Answer: d [Reason:] For a many-to-many relationship set each related entity set has its own schema and there is an additional schema for the relationship set. For a multivalued attribute, a separate schema is created consisting of that attribute and the primary key of the entity set.
3. Which of the following has each related entity set has its own schema and there is an additional schema for the relationship set.
a) A many-to-many relationship set
b) A multivalued attribute of an entity set
c) A one-to-many relationship set
d) All of the mentioned
Answer
Answer: a [Reason:] If a multivalued dependency holds and is not implied by the corresponding functional dependency, it usually arises from this source.
4. In which of the following , a separate schema is created consisting of that attribute and the primary key of the entity set.
a) A many-to-many relationship set
b) A multivalued attribute of an entity set
c) A one-to-many relationship set
d) All of the mentioned
Answer
Answer: b [Reason:] If a multivalued dependency holds and is not implied by the corresponding functional dependency, it usually arises from this source.
5. Suppose the user finds the usage of room number and phone number in a relational schema there is confusion.This is reduced by
a) Unique-role assumption
b) Unique-key assignment
c) Role intergral assignment
d) None of the mentioned
Answer
Answer: a [Reason:] A desirable feature of a database design is the unique-role assumption, which means that each attribute name has a unique meaning in the database.
6. What is the best way to represent the attributes in a large database?
a) Relational-and
b) Concatenation
c) Dot representation
d) All of the mentioned
Answer
Answer: b [Reason:] Example inst sec and student sec.
7. Designers use which of the following to tune performance of systems to support time-critical operations?
a) Denormalization
b) Redundant optimization
c) Optimization
d) Realization
Answer
Answer: a [Reason:] The process of taking a normalized schema and making it nonnormalized is called denormalization.
8. In the schema (dept name, size) we have relations total inst 2007, total inst 2008 . Which dependency have lead to this relation ?
a) Dept name, year->size
b) Year->size
c) Dept name->size
d) Size->year
Answer
Answer: a [Reason:] The process of taking a normalized schema and making it nonnormalized is called denormalization.
9. Relation dept year(dept name, total inst 2007, total inst 2008, total inst 2009) .Here the only functional dependencies are from dept name to the other attributes .This relation is in
a) Fourth NF
b) BCNF
c) Third NF
d) Second NF
Answer
Answer: b [Reason:] BCNF has only one normal form.
10. Thus a _______ of course data gives the values of all attributes, such as title and department, of all courses at a particular point in time.
a) Instance
b) Snapshot
c) Both Instance and Snapshot
d) All of the mentioned
Answer
Answer: b [Reason:] We use the term snapshot of data to mean the value of the data at a particular point in time.
11. Representations such as the in the dept year relation, with one column for each value of an attribute, are called _______ they are widely used in spreadsheets and reports and in data analysis tools.
a) Cross-tabs
b) Snapshot
c) Both Cross-tabs and Snapshot
d) All of the mentioned
Answer
Answer: a [Reason:] SQL includes features to convert data from a normal relational representation to a crosstab.
Database MCQ Set 5
1. A system is in a ______ state if there exists a set of transactions such that every transaction in the set is waiting for another transaction in the set.
a) Idle
b) Waiting
c) Deadlock
d) Ready
Answer
Answer: c [Reason:] When one data item is waiting for another data item in a transaction then system is in deadlock.
2. The deadlock state can be changed back to stable state by using _______ statement.
a) Commit
b) Rollback
c) Savepoint
d) Deadlock
Answer
Answer: b [Reason:] Rollback is used to rollback to the point before lock is obtained.
3. What are the ways of dealing with deadlock ?
a) Deadlock prevention
b) Deadlock recovery
c) Deadlock detection
d) All of the mentioned
Answer
Answer: d [Reason:] Deadlock prevention is also called as deadlock recovery.Prevention is commonly used if the probability that the system would enter a deadlock state is relatively high; otherwise, detection and recovery are more efficient.
4. When transaction Ti requests a data item currently held by Tj , Ti is allowed to wait only if it has a timestamp smaller than that of Tj (that is, Ti is older than Tj ). Otherwise, Ti is rolled back (dies). This is
a) Wait-die
b) Wait-wound
c) Wound-wait
d) Wait
Answer
Answer: a [Reason:] The wait–die scheme is a non-preemptive technique.
5. When transaction Ti requests a data item currently held by Tj , Ti is allowed to wait only if it has a timestamp larger than that of Tj (that is, Ti is younger than Tj ). Otherwise, Tj is rolled back (Tj is wounded by Ti ). This is
a) Wait-die
b) Wait-wound
c) Wound-wait
d) Wait
Answer
Answer: c [Reason:] The wound–wait scheme is a preemptive technique. It is a counterpart to the wait–die scheme.
6. The situation where the lock waits only for a specified amount of time for another lock to be released is
a) Lock timeout
b) Wait-wound
c) Timeout
d) Wait
Answer
Answer: a [Reason:] The timeout scheme is particularly easy to implement, and works well if transactions are short and if longwaits are likely to be due to deadlocks.
7. The deadlock in a set of transaction can be determined by
a) Read-only graph
b) Wait graph
c) Wait-for graph
d) All of the mentioned
Answer
Answer: a [Reason:] Each transaction involved in the cycle is said to be deadlocked.
8. A deadlock exists in the system if and only if the wait-for graph contains a _____
a) Cycle
b) Direction
c) Bi-direction
d) Rotation
Answer
Answer: a [Reason:] Each transaction involved in the cycle is said to be deadlocked.
9. Selecting the victim to be rollbacked to the previous state is determined by the minimum cost. The factors determining cost of rollback is
a) How long the transaction has computed, and how much longer the transaction will compute before it completes its designated task
b) How many data items the transaction has used
c) How many more data items the transaction needs for it to complete
d) All of the mentioned
Answer
Answer: d [Reason:] We should roll back those transactions that will incur the minimum cost.
10. ____ rollback requires the system to maintain additional information about the state of all the running transactions.
a) Total
b) Partial
c) Time
d) Commit
Answer
Answer: b [Reason:] In total rollback abort the transaction and then restart it.
