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

Oracle 9i features, Bitmap Join Indexes - This feature will increase th...

Bitmap Join Indexes - This feature will increase the performance and detains the size and format of your databases in data Character Semantics and Globalization -This featur

Level - sql pseudocolumns, LEVEL You use the LEVEL with the SELECT CON...

LEVEL You use the LEVEL with the SELECT CONNECT BY statement to categorize rows from a database table into a tree structure. The LEVEL returns the level number of a node in a

Oracle development , Write a stored procedure that accepts the post code in...

Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

Creating a table, Creating a Table Syantax: CREATE TABLE ENROL...

Creating a Table Syantax: CREATE TABLE ENROLMENT (StudentId SID, Name   VARCHAR (30) NOT NULL, CourseId CID, PRIMARY KEY (StudentId, CourseId)) ; Explan

Using %rowtype-declarations in sql, Using %ROWTYPE The %ROWTYPE attribut...

Using %ROWTYPE The %ROWTYPE attribute gives a record type which represents a row in a table (or view). The record can store the whole row of data selected from the table or fetc

Committing and rolling back - autonomous transaction, Committing and Rollin...

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

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

Using savepoint, Using SAVEPOINT The SAVEPOINT names and marks the pre...

Using SAVEPOINT The SAVEPOINT names and marks the present point in the processing of a transaction. Used with the ROLLBACK TO statement, the savepoints undo parts of a transac

Semidifference via except and join - sql, Semidifference via EXCEPT and JOI...

Semidifference via EXCEPT and JOIN - SQL SELECT * FROM (SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' EXCEPT DISTINCT CORRESPONDING SELECT StudentId

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

Parameter and Keyword Description:   table_reference: This identifies a table or view which should be available when you execute the INSERT statement, and for that you sho

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