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

Product-specific packages, Product-specific Packages The Oracle and dif...

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 illus

Cursor variables, What Are Cursor Variables  ? The Cursor variables ar...

What Are Cursor Variables  ? The Cursor variables are like C or Pascal pointers that hold the memory location (address) of some item rather of the item itself. Therefore, decl

Autonomous versus nested transactions, Autonomous versus Nested Transaction...

Autonomous versus Nested Transactions Though an autonomous transaction is started by the other transaction, it is not a nested transaction for the reasons shown below: (i)

Forall statement - syntax, FORALL Statement The FORALL statements instr...

FORALL Statement The FORALL statements instruct the PL/SQL engine to bulk-bind the input collections before sending them to the SQL engine. Though the FORALL statement consists

Using inner join, Using INNER JOIN INNER JOIN is used to retrieve the ...

Using INNER JOIN INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows ar

Using forall and bulk collect - bulk bind performance, Using FORALL and BUL...

Using FORALL and BULK COLLECT Together You can unite the BULK COLLECT clause with the FORALL statement, in that case, the SQL engine bulk-binds column values incrementally. In

Effects of null for multiple assignments - sql, Effects of NULL for Multipl...

Effects of NULL for Multiple Assignments - SQL If the row expression given as the source for a multiple assignment evaluates to NULL, then NULL is assigned to each target. If

Pl/sql assignment, 1. a. Write a trigger that fires when a part's price...

1. a. Write a trigger that fires when a part's price is updated. The trigger will write a record into a table called PriceUpdates. The record should contain the information of

Why use cursor variables, Why Use Cursor Variables ? Primarily, you use...

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

Assignment source not a literal - variable, Assignment Source Not a Literal...

Assignment Source Not a Literal - Variable Syntax: SET SN = SID (SUBSTRING (SN.C FROM 1 FOR 1)||'5');

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