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

Avoiding collection exceptions, Avoiding Collection Exceptions   In ma...

Avoiding Collection Exceptions   In many cases, if you reference a nonexistent collection element, then PL/SQL raises a predefined exception. Consider the illustration shown b

Package dbms pipe in pl/sql, DBMS_PIPE: The Package DBMS_PIPE allows va...

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

An active database in pl-sql, Consider the following set of database tables...

Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary ke

CURSOR, #quesWrite a cursor to open an employee database and fetch the empl...

#quesWrite a cursor to open an employee database and fetch the employee record whose age is greater than 45.tion..

Other monadic - sql, Other monadic - SQL In 2VL there are just 4 (2 2 ...

Other monadic - SQL In 2VL there are just 4 (2 2 ) monadic operators, of which negation is really the only "useful" one. When a third truth value is introduced we have 27 (3 3

Semidifference and not - sql, Semidifference and NOT - SQL In this sec...

Semidifference and NOT - SQL In this section first describe the relational difference operator, named MINUS. Example here shows SQL's closest counterpart of that operator.

Parameter and keyword description - update statement, Parameter and Keyword...

Parameter and Keyword Description:   table_reference: This keyword identifies the table or view that should be accessible when you execute the UPDATE statement, and for wh

Data types in sql - xml, Data Types in SQL - XML, Array, Row ...

Data Types in SQL - XML, Array, Row BINARY LARGE OBJECT for arbitrarily large bit strings. XML for XML documents and fragments. ARRAY types for arrays.

Product-specific packages in pl/sql, Product-specific Packages The Ora...

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illu

%rowtype - cursors, %ROWTYPE: This attribute gives a record type which ...

%ROWTYPE: This attribute gives a record type which represents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresp

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: +1-415-670-9521

Phone: +1-415-670-9521

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

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