Question 1 - Normalization

Considering the following example of Invoice, you will design relational database by following the step suggested. Please, answer each step.

Step1: Identifying all attributes and decide primary key of INVOICE relation.

Step2: Identifying all (minimal) functional dependencies.

Step3: Normalize INVOICE to 3 NF.

Question 2 - Query Optimizer

You are managing the following database system:

• DBMS: Oracle 11g
• OS: Sun Solaris
• System architecture: Web-based application using ASP.Net
• Purpose of Server: HR management
• Total size of database: 120 MB
• Total number of table: 12
• Total number of index including Primary Key: 18
• Daily average number of transactions: 400
• Complexity of SQL: simple
• Update: not frequently

As a database administrator, you need to decide query optimizer, i.e., CBO (Cost-based optimizer) or RBO (Rule-based optimizer).

Which one are you going to select for your system? Any Why?

Question 3 - Query Execution Plan

You are using Oracle 11g DBMS with Cost-Based Optimizer (CBO). In order to perform SQL tuning for a query, you decide to use ‘set autotrace' option of SQ*Plus as below:

SQL> SET AUTOTRACE ON
SQL> select d.department_name, sum(e.salary)
2 from employees e, departments d
3 where e.department_id = d.department_id
4 and e.salary>= 3000
5* group by d.department_name

Execution Plan

Explain about the output (i.e., Execution Plan) including order of execution and statistical data.

Question 4 - Indexing: B+-tree.

Consider constructing a B+-tree of order 3 (i.e. p=3 and p leaf = 2)

Show the each step and resulting trees for inserting keys 8, 5, 1, 7, 3, 12, 9, 6 in this order.

Step 2: After inserting 3

Step 3: After inserting 12

Step 4: After inserting 9

Step 5: After inserting 6

Consider the B+-tree constructed above, i.e., the result of Step 5, and then assign nodeID (1, 2, 3 ....) from top to bottom, and let to right. For example, root node has nodeID=1.

When you look up the key "8", how many nodes do you need to visit? Also, state the list of nodeID that you need to visit in order.

Question 5 - A bout B+ Tree properties which of the following is FALSE?
A. All the leaf nodes are at the same level
B. Deletion always happens at the leaf level
C. All the data pointers are spread among internal nodes and leaf nodes
D. Insertion always happens at the leaf level

Question 6 - Suppose a data file contains 30,000 records and each record is 200 bytes.The size of the disk block is 2.048 bytes,the length of key field is 9 bytes and the length of data pointer is 6 bytes.How many disk blocks do we need to create a secondary index file?
A. 10 blocks
B. 136 blocks
C. 221 blocks
D. 442 blocks

Question 7 - Which of the following is TRUE?
A. Secondary index provides a logical ordering
B. Primary index provides a physical ordering
C. Cluster index provides physical ordering
D. All of above

Question 8 - Which of the following statement is TRUE?
A. Dense index has an index entry for every search key values
B. Sparse index has an index entry only for some search key values
C. For primary index the total number of index entries is same as the number of disk blocks in the ordered data file
D. All of above

Question 9 - Which of the following statement is TRUE?
A. The index field of primary is on key field hence is dense index
B. The index field of cluster index is on non-key filed hence is dense index
C. We could have multiple primary and secondary indexes on a data file
D. None of above