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

Relational algebra, Define basic operators of relational algebra with an ex...

Define basic operators of relational algebra with an example each

I need sql data base, I need SQL Data Base Project Description: Netwo...

I need SQL Data Base Project Description: Network SQL database and SQL Setup two 4 workstation Skills required are Data Entry, MySQL, SQL

Updating tables in sql, Updating Tables in SQL The topic of updating b...

Updating Tables in SQL The topic of updating by describing the assignment operator, ":=" in Tutorial D. SQL uses a different syntax for assignment, using the key word SET and

Recursive subprograms, Recursive Subprograms The recursive subprogram ...

Recursive Subprograms The recursive subprogram is the one that calls itself. Think of a recursive call as a call to a few other subprograms that does the similar task as your

Cursor variables in pl sql, Cursor Variables:   To execute the multi-...

Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

Collection methods in pl sql, Collection Methods:   The collection me...

Collection Methods:   The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the C

Selecting objects in pl sql, Selecting Objects: Suppose that you have ...

Selecting Objects: Suppose that you have run the SQL*Plus script below that creates object type Person and object table persons, and that you have settled the table: CREATE

Organic evolution, Organi c Evolution Evolution is a slow continuou...

Organi c Evolution Evolution is a slow continuous, irreversible and natural process of change to give rise to advance and diverse forms of life i.e. formation of new specie

Sql script to create and populate the tables, Create the four tables and po...

Create the four tables and populate them with the given data. Answer the following queries in SQL. 1. Get all part-color/part-city combinations. Note: Here and subsequently, the

Fetch statement - syntax, FETCH Statement The FETCH statement retrieve ...

FETCH Statement The FETCH statement retrieve rows of data one at a time from the result set of the multi-row query. The data is stored in fields or variables which correspond t

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