Ql. Magnetic tape is not as good as hard disk as operational database storage media because

(a) Its capacity is too small  

(b) it is volatile
(c) is not a random access device  

(d) it is too expensive

(c) is not a random access device

Q2. A disk block is
(a) a contiguous sectors from a single track
(b) is a smallest of unit of data transfer between hard disk and main memory
(c) has a physical address of the form cylinder + surface + block
(d) all of the above

(d) all of the above

Q3. An I/0 operation refers to
(a) Reading or writing a data item    

(b) reading or writing a file
(c) Reading or writing a table                

(d) reading or writing a disk block

Q4. In a DBMS, tables and index are stored in data files, and
(a)each table is stored in a separate file
(b)several tables can be stored in the same file
(c)the records in each file is of fixed length
(d)the records in each file can be of variable length
(e)all of the above are possible

Q5. The blocking factor refers to
(a) The number of file records that can be stored in a block
(b)the number of files that can be stored in a disk block
(c)the number of blocks that are required to store a file
(d)the number of sectors in a block

(a) The number of file records that can be stored in a block

Q6 a point query refers to a query that retrieves
(a) exactly one record
(b) at most one record
(c) records that satisfy an equality condition
(d) records that satisfy an inequality condition

Q7, a heap file
(a) refers to a file where the records are not ordered at all
(b) refers to a file where the records are ordered on a non-key attribute
(c) is efficient for point queries but not for range queries
(d) is efficient for deletion of records

Q8 Compared with a heap file, a file ordered on field A is
(a) efficient for point queries with condition A= value
(b efficient for range queries with. condition A > value
(c) inefficient for insertion of records
(d)all of the above

Q9 In a hashed file organization
(a) There is always a hash function defined on a primary key field
(b) records with the same hash key values are always mapped to the same bucket
(c) records with different hash key values may be stored in the same bucket
(d) the number of buckets are usually more than the number of records

Q 10. A hashed file is
(a) efficient for range queries over the hash key field
(b) efficient for point queries over the hash key field
(c) efficient for point queries over any field
(d) efficient for range queries over any field

Quiz 2
Q1. An index entry consists of a search key value and a pointer which points to
(a) the start of the data file which contains the search key value
(b) The end of the data file which contains the search key value
(c) The physical address (on disk) of the data record with the search key value
(d) The position of the data record in the buffer

Q2. An ordered index refers to an index where
(a) the index entries are ordered on the search key values
(b) the data records are ordered on the search key value
(c) the data records and index entries are both ordered
(d) the index entries are ordered on the search key values but the data records are not ordered.

Q3. A sparse index on search key K can be built on
(a) an unordered data file
(b) a data file ordered on any field
(c) a data file ordered on K
(d) a data file hashed on K

Q4. A primary index
(a) is built on a data file ordered on the search key, which is also a key field of the data file
(b) is usually a dense index
(c) can also be built on a data file ordered on a non-key field
(d) all of the above

Q5. A clustered index can be built on
(a) an unordered data file
(b) a data file ordered on the search key, which is also a key field of the data file
(c) a data file ordered on a non-key field
(d) none of the above

Q6 a secondary index is an index
(a) built on a file not ordered on the search key
(b) the index entries are ordered on the search key
(c) which is dense
(d) all of the above

Q7. Which of the following is true?
(a) There can be one or more primary indexes on a data file
(b) There can be one or more clustered indexes on a data file
(c) There can be one or more secondary indexes on a data file
(d) An index can be both primary and secondary

Q8 In a multi-level index,
(a) All levels of indexes are dense
(b) Different levels of indexes can use different search keys
(c) The data file itself must be ordered
d) None of the above

Q9 A hash-based index is an index where
(a) The index entries are ordered
(b)The index file is organized as a hashed file
(c) The data file is a hashed file
(d) The data file and the index file are both hashed file and use the same hash function

 
Q 10. A clustered index is
(a) more helpful than a secondary index for range queries defined on the search key
(b) more helpful than a hash-based index for point queries defined on the hash key
(c) easier to update than secondary indexes if there are insertions
(d) helpful for range queries over any field

Quiz 3

Q 1. A B+ tree
(a) is a balanced tree
(b) has nodes containing an alternate sequence of pointers and search key values
(c) usually a node takes one disk block
(d) all of the above

Q2. In a B+ tree
(a) The search key values in each node may not be ordered
(b) The search key values in every node are ordered
(c) The pointers in a node points to its parent node
(d) The root usually contains no more than two pointers

Q3. In a B+ tree,
(a) The leaf nodes are linked together so they form a ordered first-level index for the data file
(b) The first level index can be clustered or secondary(c)
(c)Every level above the leaf level can be regarded as a sparse index on the previous level index
(d) All of the above

Q4. A B+ tree
(a) must be built for data files ordered on the search key
(b) must be built for data files not ordered on the search key
(c) can be built for data files not ordered on any field
(d) is more efficient for range queries than for point queries on the search key

Q5. A B-tree is different from a B+ tree in that
(a) it is not a balanced tree
(b) it does not require the nodes to be of similar size
(c) the leaf nodes are not linked together
(d) each search key value only appears once in the tree.

Quiz 4

Q1. The Oracle Instance
(a) Is another name for the System Global Area
(b) Is another name for the Program Global Area
(c) comprises the System Global Area + Oracle Processes
(d) Comprises the Program Global Area + Oracle Processes

Q2. A Datafile
(a) Can be distributed across several physical locations
(b) Can be shared between several databases
(c) can be shared between several tablespaces
(d) None of the above

Q3. A Control File
(a) Is needed for Database Startup
(b) Can be multiplexed to protect against a failure(c
(c) Is also used for database recovery
(d) All of the above

Q4. A Tablespace
(a) Can be distributed across several physical location
(b) Can be shared between several databases
(c) Can be shared between several datafiles
(d) None of the above

Q5. At the finest level of granularity, database data are stored in
(a) Segments
(b) Extents
(b) Blocks
(d) Tablespaces

1

2

3

4

5

Quiz 5
Q 1. The storage structure at the finest level of granularity is
(a) A tablespace
(b) An extent
(c) A data block
(d) A segment

Q2. Row Chaining
(a) Happens when the row is too large to fit into one data block
(b) Improves 1/0 performance
(c) Involves moving the data for the entire row to a new data block
(d) All of the above

Q3. Oracle will make a block available for new rows to be added after the amount of
used space falls below
(a) PCTFREE
(b) PCTUSED
(c) DB BLOCK SIZE
(d) DB_USED_PERCENT

Q4. The minimum percentage of a datablock to be reserved as free space for possible
updates to rows that already exist in that block is defined by
(a) PCTFREE
(b) PCTUSED
(c) DB_BLOCK_SIZE
(d) DB_UPDATE_ PERCENT

Q5. To reduce the occurrence of row migration
(a)Increase PCTFREE
(b)Decrease PCTFREE
(c) Increase PCTUSED
(d) Decrease PCTUSED

Q6. You can limit the number of extents the table can allocate by specifying the storage parameters of a table in the STORAGE clause of the storage

(a)CREATE EXTENTS statement 
(b) ALLOCATE EXTENTS statement

(c) CREATE TABLE statement

(d) CREATE SEGMENT statement

Q7. To eliminate the complexities of managing rollback segment space, we would use
(a)Automatic Undo Management
(b) Dynamically sized datafiles
(c) Oracle Managed Files manage
(d) Locally managed tablespaces

Q8. To eliminate the need for you to directly manage the operating system files comprising an Oracle database, we would use
(a) Automatic Undo Management    
(b) Dynamically sized datafiles  

(c) Oracle Managed Files

(d) Locally managed tablespaces

Q9. You can enlarge a database by
(a) Adding a datafile to a tablespace
(b) Adding a new tablespace
(c) Increasing the size of a datafile
(d) all of the above

Q10. Extents are managed by the user inmanage
(a) Locally managed tablespaces
(b) Dictionary managed tablespaces
(c) Both of the above                       

(d) None of the above

True or false

Q1. Within each database a user name must be unique with respect to other user names and roles.

Q2. To create a user, you must have the CREATE USER system privilege.
True or false

Q3.When we create a new user using the CREATE USER command, they are automatically assigned the CREATE SESSION system privilege.
True or false

Q4. Revoking an object privilege may have a cascading effect that should be investigated before a REVOKE statement is issued.
True or false

Q5. Revoking a system privilege may have a cascading effect that should be investigated before a REVOKE statement is issued.
True or false

Q6. To specify limitations on several system resources available to the user, we assign an appropriate role to the user
True or false

Q7. To limit the collective amount of disk space available to a user on a tablespace, we can set a QUOTA for each tablespace available to the user.
True or false

Q8. CREATE ANY TABLE is an example of an object privilege because it allows a user to create table objects.
True or false

Q9. It you grant a role to someone WITH ADMIN OPTION, the grantee automatically gets the ability to alter or drop the role.
True or false

Q10. WITH GRANT OPTION allows the grantee to grant the system privilege or role to other users or roles.
True or false

Quiz 7

Q1. A transaction consists of a series of actions
(a) that may read data from the database
(b) that may modify data in the database
(c) That should either be all done, or none is done
(d) all of the above

Q2. In a transaction,
(a) all read and write operations are important
(b) only write operations are important
(c) only read operations are important
(d) some read or write operations are not important

Q3. The ACID property of transactions refers to
(a) atomicity, consistency, isolation, and durability
(b) abort, commit, isolation, and durability
(c) abort, commit, inconsistency, do-them-all or do-nothing
(d) atomicity, consistency, inconsistency, durability

Q4. The property that requires that partial effects of uncommitted transaction do not affect
other transactions is called
(a) Atomicity
(b) Consistency
(c) Isolation
(d) Durability

Q5. The property that requires actions in a transaction be all done or not done at all is called
(a) Atomicity
(b)Consistency
(c) Isolation
(d) Durability

Q6. An example of a potential problem caused by concurrent operation is
(a) Lost Update Problem
(b) Temporary Update Problem
(c) Incorrect Summary Problem.
(d) All of the above

Q7. The problem that occurs when one transaction reads a value set by another transaction,
which subsequently fails is called
(a) Lost Update Problem
(b)Temporary Update Problem
(c) Incorrect Summary Problem.
(d) Failed Transaction Problem

Q8. Two actions in a schedule are conflicting if
(a) they belong to different transactions
(d) one of them is a write operation
(c) they access the same data item
(d) all of the above

Q9. Which of the following statements is true?
(a) Schedules S I and S2 are conflict equivalent if the conflicting operations in S I and S2 of are arranged in the same order
(b) Schedules S I and S2 are conflict equivalent if S I can be transformed to S2 by swapping the non-conflicting operations
(c) non-conflicting operations can be arranged in any order
(d) all of the above

Q10. Which of the following is not true?
(a) Serializability refers to whether a schedule has the same effect on the database as
some serial schedule
(d) All serial schedules are conflict equivalent
(c) Two serial schedules may have different effects
(d) Recoverability refers to whether the actions already carried out can be reversed if
some transaction fails.

Q11. One can test conflict serializability using
(a) a wait-for graph
(b) a precedence graph
(c) the two-phase locking protocol
(d) all of the above

Q 12. Strict two phase locking
(a) ensures a strict schedule.
(b) is not a commonly used locking protocol.
(c) can help prevent deadlock.
(d) All of the above

Q 13. The Two Phase-Locking (2PL) protocol
(a) can guarantee a schedule is serializable
(b) can guarantee a schedule is recoverable
(c) can guarantee a schedule does not have cascading rollback
(d) all of the above

Q 14. In the strict 2PL protocol, a transaction
(a) cannot release any of its locks until it commits
(b) must release its exclusive locks before it commits
(c) cannot release its shared locks before it commits
(d) cannot release its exclusive locks before it commits or aborts

Q15. Which of the following is not true?
(a)The strict 2PL protocol can make sure a schedule is always conflict equivalent to some serial schedule
(b) The strict 2PL protocol can guarantee there are no cascading rollbacks
(c) The strict 2PL protocol can guarantee the schedule is recoverable
(d) The strict 2PL protocol can guarantee there are no deadlocks

Quiz 8
Q1. A transaction
(a) is a logical unit of work that contains one or more SQL statements
(b) begins with the first executable SQL statement
(c) ends when A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause
(d) all of the above

Q2. When a transaction is committed
(a) A unique system change number (SCN) is assigned to the transaction and recorded
(b) The database writer process (DBWR) writes redo log entries in the SGA's redo log buffers to the redo log file.
(c) Oracle acquires locks on rows and tables (as required)   

(d) all of the above

Q3. In rolling back an entire transaction, Oracle
(a) undoes all changes made by all the SQL statements in the transaction by using the corresponding undo tablespace.
(b) releases all the transaction's locks of data
(c) ends the transaction           

(d) all of the above

Q4. A dirty read occurs when
(a) a transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
(b) a transaction reads data that I has been written by another transaction that has not been committed yet.
(c) a transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.
(d) all of the above

Q5. When a database raises the locks to a higher level of granularity, it is called
a) Lock escalation     (b) Lock conversion
(c) Lock Raising        (d) Deadlock Prevention

Q6. Changing a table lock of lower restrictiveness to one of higher restrictiveness is called releases all-the transaction's locks of data
a) Lock escalation     (b) Lock conversion
(c) Lock Raising        (d) Deadlock Prevention

Q7. What types of Locking does the Oracle Database offer?
(a) DML Locks  (c) Internal Locks
(b) DDL Locks    (d) AII of the above

Q8. Which of the following statements is true with respect to row lock contention
(a) Readers of data do not wait for writers of the same data rows.
(b) Writers of data do not wait for readers of the same data rows unless SELECT ... FOR UPDATE is used, which specifically requests a lock for the reader.
(c) Writers only wait for other writers if they attempt to update the same rows at the same time.
(d) All of the above

Q9. When a Deadlock occurs, Oracle will
(a) Wait for it to resolve itself
(b) Rollback the statement belonging to the transaction that detected the deadlock.
(c) Rollback the statement belonging to the transaction that failed to detect the deadlock.
(d) Rollback all statements

Q 10. To avoid deadlocks we should:
(a) Make transactions explicitly override the default locking of Oracle.
(b) Take advantage of lock escalation
(c) Acquire the most exclusive (least compatible) lock first.
(d) All of the above

Quiz 9

Q1. The write-ahead protocol means
(a) The database write operations must be performed first, then force write the system log
(b) The database write operations must be performed first, followed by read operations
(c) The system log must be force written to stable storage, before database item is written to disk.
(d) None of the above.

Q2. If deferred update is used, then when there is a system crash
(a) no undo operations are necessary
(b) no redo operations are necessary
(c) neither undo nor redo is necessary
(d) both redo and undo may be necessary

Q3. If immediate update is used, then crash happens
(a) no undo operations are necessary
(b) no redo operations are necessary
(c) neither undo nor redo is necessary
(d) both redo and undo may be necessary

Q4. When recovering, using a log file,
(a) redo must be done before undo
(b) undo must be done before redo
(c) undo and redo can be done in any order
(d) none of the above

Q5. The purpose of checkpoints is to
(a) avoid redo operations
(b) avoid undo operations
(c) reduce the number of redo and undo operations that must be performed
(d) to ensure the system log is up to date

1) The Process which writes the redo log buffer to redo log files is called
a) Oracle Instance
b) DBWR
c) LGWR
d) CKPT

2) The Process which writes dirty buffers to disk is called
a) Oracle Instance
b) DBWR
c) LGWR
d) CKPT

3) In Archivelog mode
a) The system keep copies of all redo log files by archiving them
b) The system keep copies of all data files by archiving them
c) The LGWR writes redo log file to the archive
d) The CKPT process writes the control file information to the archive

4) A log switch
a) occurs when LGWR has filled the current log file group
b) can be forced by the DBA if current redo log file (group) needs to be archived
c) occurs when the database is shutdown
d) all of the above

5) Checkpoints occur
a) at every log switch
b) when a predetermined number of redo log blocks have been written to disk since the last checkpoint
c) when a tablespace is taken off-line
d) all of the above

6) Recovery at instance startup is performed by
a) SMON                b) PMON
c) CKPT                d) RECO

7) After starting an instance, Oracle associates the instance with the specified database. The SGA is created and the control file is opened. The instance is now in
a) Nomount state            b) Mount state
c) Open State                d) All of the above

8) During Oracle startup, The Data Files are opened
a) In the Nomount state      

b) In the Mount state
c) In the Open state           

d) When the user connects

9) Database recovery is required if the database is shutdown in the
a) NORMAL mode            b) ABORT mode
c) IMMEDIATE mode        d) CRASH mode

10) To enable RESTRICTED SESSION, we must use the
a) ALTER TABLE statement       

b) ALTER SYSTEM statement
c) ALTER SESSION statement   

d) CREATE RESTRICTED SESSION statement

1) A popular optimization strategy that uses the intuitive rules to choose a better query execution plan is
a) cost-based optimization           

b) heuristic optimization
c) semantic optimization           

d) None of the above

2) A popular optimization strategy that uses domain knowledge (such as integrity constraints) to choose a better query execution plan is
a) cost-based optimization           

b) heuristic optimization
c) semantic optimization           

d) None of the above

3) The new Oracle 10g cost model is improved because unlike previous versions it now also accounts for
a) I/O Costs                    b) CPU Costs
c) Block access                d) Network Errors

4) For interactive applications such as Oracle Forms application, or SQL*PLUS queries, where the user is normally waiting to see the first few rows of the query result, we should optimize for:
a) Best throughput               

b) Best response time
c) Both of the above               

d) Either of the above

5) To see the optimization plan chosen by Oracle for a particular statement, we use
a) SHOW PLAN                b) SHOW OPTIMAL PLAN
c) EXPLAIN PLAN               

d) SELECT OPTIMIZE PLAN

1)    What are the major advantages and disadvantages of each of the following file organisations?: exam question

a)    heap file organization

b)    ordered file organization

c)    hash file organization

a)    heap file organization
+ Quick writing
- Slow searching (Average number of accessBLOCKS/2)

b)    ordered file organization
+ Quick searches (they are in sequential order)
- Slow to insert (need to re-order records with each insert)

c)    hash file organization
+ Point queries
- Ordered search