CHAPTER 6 PL/SQL

When writing an SQL query the user must be aware of the efficiency of the code. For example he/she should write the code to minimise the size of intermediate tables during execution. True or False?
1. True
2. False
It is the job of the DBMS optimizer to execute an optimal version of a query. True or False?
1. True
2. False
The aim of Query Processing is
1. to provide a response that is correct and consistent for a particular instantiation of the database
2. to produce a response in an acceptable period of time
3. to minimise disk input/output by keeping the intermediate table as far as possible in main memory and not having to spill them out onto disk
4. all of the above
The English version of the Relational Algebra query (based on the Bus Drivers database) is one of the following. Which is it?

R1 = Cleaner join Depot where Cleaner dNo= Depot dNo
R2 = Bus join R1 where Bus cNo = R1 cNo
R3 = BusType join R2 where BusType tNo= R2 tNo
R4 = Restriction from R3 where typeDesc =’minibus’
R5 = Project over R4 [cNo,cName,dName,regNo]
1. For buses of type minibus list the bus registrations and the name of the depots that the buses are based at, with cleaners (number and name) employed at these depots.
2. List all cleaners (number and name), the name of their depot and the bus registration numbers for all cleaners responsible for type ‘minibus’.
The following two Relational Algebra queries are equivalent. True or False?
1. True
2. False
Given the following pairs of relational algebra operations which operations should be performed first during the execution of an SQL query, so as to increase efficiency?
1. union and intersection
2. select and project
3. project and union
4. Cartesian product and divide
Query Processing can be divided into distinct phases. Which of the following is not one of the phases of Query Processing?
1. Query Decomposition
2. Syntactic and semantic checking
3. Query Optimization
4. Code Generation
5. Runtime Query Execution
Query decomposition and optimisation can be divided into several distinct stages including some of the following. Which on is not a stage of Query decomposition and optimisation?
1. Syntactic and semantic checking
2. Casting the query into some internal representation
3. Heuristic optimisation
4. Systematic optimisation
5. Selection of the cheapest plan
6. Code Generation
Which of the following relating to parse trees are false?
1. The bottom level contains the relations in the query
2. The middle layer contains relation algebra operations to be performed on the relations or results of previous operations
3. The top level is a single operation that produces the final result
4. Parse trees are executed from the root (the top of the tree) to the leaves (the bottom of the trees)
5. None of these
Which of the following relating to the rule ‘Perform projection as early as possible’ are true?
1. Unless the cardinalities of the intermediate relations are reduced, the usefulness of pushing a projection before a join is questionable
2. Projections of more than one attribute can be split into sub-projections.
3. When a projection is preceded by a join it is possible to push the projection down before the join.
4. A projection as it stands cannot be simply moved down the tree
5. As the projection is pushed down the tree it will acquires new attributes
6. None of these
7. All of these

CHAPTER 6 PL/SQL

Test Yourself on MIS