Question 1.

List and explain the memory constructs of the Oracle 12c databases.

List and explain the background processes that support the 12c database.

Explain why container and pluggable databases on a Linux server will use less memory compared to many Oracle 11g Instances.

Question 2. Storage

(a) Evaluate and explain two of the key factors that determine the reliability of storage media.

(b) Compare and contrast the following two methods for reducing the risk associated with physical failure of storage media: (file) disk multiplexing and RAID.

(c) One method for reducing disk I/O is to maintain as many blocks as possible in primary storage. Analyze by example and explain the role of buffers and responsible subsystems in managing the allocation of space available in main memory for the storage of blocks.

(d) Differentiate between logical and physical storage in the Oracle database system. Evaluate by example and explain the relationship between tablespaces and physical data files.

(e) How does the remapping of bad sectors by disk controllers affect data-retrieval rates? Support your answer by example.

Question 3.

Part 1. Indexing

Given that a relation is stored in a B+ -tree file organization. Suppose secondary indices stored record identifiers that are pointers to records on disk:

(a) What would be the effect on the secondary indices if a page split happens in the file organization?

(b) What would be the cost of updating all affected records in a secondary index?

(c) How does using the search key of the file organization as a logical identifier solve this problem?

(d) What is the extra cost due to the use of such logical record identifiers?

Part 2. Transaction Management

(a) The consistency and reliability aspects of transactions are due to the "ACIDity" properties of transactions. Discuss each of these properties and explain how they relate to the concurrency control and recovery mechanisms. Give examples to illustrate your answer.

(b) Evaluate and explain the types of problems that can occur in a multi-user environment when concurrent access to the database is allowed.

(c) Why would two-phase locking not be an appropriate concurrency control scheme for indexes? Explain and then discuss a more appropriate locking scheme for tree-based indexes.

Question 4

Object-Relational Databases

(a) Fully explain what is intended in the following syntax:

CREATE ROW TYPE employee_type (
ename VARCHAR(25),
ssn CHAR(9),
salary INT) ;

CREATE ROW TYPE engineer_type (
degree VARCHAR(10),
license VARCHAR(20))
UNDER employee_type ;

CREATE ROW TYPE engr_mgr_type (
manager_start_date DATE
dept_managed VARCHAR(20))
UNDER engineer_type ;

A proof is a formal method for proving or disproving some particular phenomena.

(b) In this case, you are asked to prove that DML can transact against column object data. Formulate and present all relevant code for the proof and the subsequent output from the Oracle DBMS to reach conclusions in the proof.

(c) In this case, you are asked to prove that DML can transact against row object data. Formulate and present all relevant code for the proof and the subsequent output from the Oracle DBMS to reach a conclusion in the proof.

(d) In this case, you are asked to prove that DML can transact against VARRAY data. Formulate and present all relevant code for the proof and the subsequent output from the Oracle DBMS to reach a conclusion in the proof.

(e) In this case, you are asked to prove that DML can transact against nested table data. Formulate and present all relevant code for the proof and the subsequent output from the Oracle DBMS to reach a conclusion in the proof.