User Account

All Pages

query optimization, PL-SQL Programming

Assignment Help:
1.( /5 marks) Suppose that a B+-tree index with the search key (dept_name, building) is available on
relation department. What would be the best way to handle the following selection?
?(building = “Watson”) ? (budget < 55000) ? (dept_name = “Music”)(department)
a. 2 marks for clearly showing an equivalent relation algebra expression using the
appropriate, well identified rule supplied in the textbook (e.g., the derivation with rule #
applied).
b. 1.5 marks for clearly explaining how the B+ tree index would be used to evaluate the
conditions involving attributes that are specified as part of the index
c. 1.5 marks for clearly explaining how the remaining conditions that involve attributes that
are not specified as part of the index would be evaluated
2. ( /5 marks) Show how to derive the following relational algebra equivalences by a sequence of
transformations using the relational algebra equivalence rules in Section 13.2.1. Hint: make sure
you clearly define all rules you use; as well identify which rule is being applied and apply only one
rule for each line in your solution – show your work.
a. ??1??2??3 (E) = ??1 (??2 (??3 (E))) (2 marks)
b. ??1??2 (E1 ??3 E2) = ??1 (E1 ??3 (??2 (E2))), where ?2 involves only attributes from E2 (3
marks)
3. ( /5 marks) Consider the two expressions ?? (E1 E2) and ?? (E1 ? E2).
a. Show using a concrete example that the two expressions are not equivalent in general
(3 marks). Hint: first identify what the difference is between natural join and left outer join
(what is extra in the left outer join), and then make sure you give the concrete relation instances
for E1 and E2 that result in a different expression result.
b. Give a simple condition on the predicate ?, which if satisfied will ensure that the two
expressions are equivalent. Hint: Think about what extra tuples you would have in the left

1
Some of the problems are adapted from “Database System Concepts”, 6th Edition, 2011, Avis Silberschatz, Henry
F. Korth, S. Sudarshan.
outer join, how they joined tuple is constructed, and a condition that will exclude these extra
tuples.
4. ( /5 marks) Explain how to use a histogram to estimate the size of a selection of the form ?A=v(r).
Hint: Divide the distribution into n buckets {rj | j ? [1,n]} and consider the two cases: Part 1) the
selection value v is greater than or equal to the largest value of a given bucket ri (2 marks) and Part 2) the
selection value v is “part way” between the max and min values of bucket ri+1. (3 marks).
5. ( /5 marks) Give an example of a relational algebra expression defining a materialized view and
two situations (for the differentials) such that incremental view maintenance is better than
recomputation (recomputing the materialized view from scratch) in one situation (2.5 marks),
and recomputation is better in the other situation (2.5 marks). Hint: think about different values for
x between 0 and 100, where you insert x% tuples are deleted.

Related Discussions:- query optimization

Example of when or then constraints - sql, Example of WHEN or THEN Constrai...

Example of WHEN or THEN Constraints A concrete example showing how SQL supports WHEN/THEN constraints CREATE TABLE SAL_HISTORY (EmpNo CHAR (6), Salary INTEGER NOT NULL,

Scope rules - user-defined exceptions, Scope Rules You cannot declare ...

Scope Rules You cannot declare an exception twice in the similar block. Though, you can, declare the similar exception in 2 different blocks. The Exceptions declared in a bloc

Parameter modes - pl sql, Parameter Modes: You do not require to speci...

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Package body in pl/sql, Package Body: The package specification is imp...

Package Body: The package specification is implemented by the package body. That is, the package body has the definition of every cursor and the subprogram declared in the pac

Biochemical origin of life - modern concept, BIOCHE M ICA L ORIGIN OF LI...

BIOCHE M ICA L ORIGIN OF LIFE - It is generally agreed by astronomers, geologists and biologists that the earth is approximately 4500-5000 million years old. It is an

Forward declarations - subprograms, Forward Declarations The PL/SQL ne...

Forward Declarations The PL/SQL needs that you declare an identifier before using it. And hence, you should declare a subprogram before calling it. For illustration, the decla

Keyword and parameter description - forall statement, Keyword &Parameter De...

Keyword &Parameter Description: index_name: This is an undeclared identifier which can be referenced only within the FORALL statement and only as the collection subscript

Do you know anyone that can do this type of coding or not?, Task 2 [12 mark...

Task 2 [12 marks] Write the package body for the following package specification (the detailed description of each function and procedure is provided in the appendix below). Place

Parameter and keyword description - records, Parameter and Keyword Descript...

Parameter and Keyword Description: record_type_name: This identifies the user-defined type specifier that is used in the subsequent declarations of the records. NOT N

In mode - parameter modes, IN Mode An IN parameter pass the values to ...

IN Mode An IN parameter pass the values to the subprogram being called. Within the subprogram, an IN parameter acts like a constant. And hence, it cannot be assigned a value.

Write Your Message!

Captcha
Order Assignment

Featured Services

Order Now

Popular Subjects

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +91-977-207-8620

Phone: +91-977-207-8620

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd