Database homework assignment query optomizer Hash join SQL create an index calledIntroduction to Databases
Assignment 6
1. Assume a query optomizer processed the command SELECT * FROM person WHERE person.age
> 100 AND person.zipcode = ’45638’ If the optomizer discovers that the column person has
an average of 25 with a standard deviation of 5, and the zipcode column is evenly distributed,
which column (zipcode or age) would have a lower filter factor (or FF)?
2. (PDBM 13.2) See textbook for question.
13.2. Which of the following statements is not correct?
a. Index creation can help improve the performance of join queries.
b. Indexing makes it harder to enforce uniqueness on a (combination
of) column(s).
c. An index implies a logical ordering of the rows in a table.
d. An index can be used to create a physical ordering of rows.
3. (PDBM 13.3) See textbook for question.
13.3. Which of the following statements is not correct?
a. The more selective a query predicate’s FF, the less desirable it is to
use the index on the corresponding attribute type in the access plan.
b. For range queries, a primary or clustered index is more efficient
than a secondary index.
c. The number of block accesses determines the performance, not the
number of rows retrieved.
d. The more attribute types are included in the index, the higher the
performance impact is on update queries.
4. (PDBM 13.4) See textbook for question.
13.4. Given two tables R and S, which of the following join strategies is
described by the following algorithm:
Denote S → outer table
For every row s in S do
for every row r in R do
{if r(a) = s(b) then join r with s and place in output buffer}
}
a. Hash join.
b. Sort-merge join.
c. Nested-loop join.
d. None of the above.
5. (PDBM 13.2E) See textbook for question.
13.2E Illustrate how SQL can be used to create an index called
CUSTOMER_INDEX, on the table with name CUSTOMERS based on the
CUSTOMER_AGE (descending) and CUSTOMER_ZIPCODE (ascending)
attribute types. Give a minimum of three reasons why choosing an appropriate
index can be beneficial.
6. (Bonus) If a database is using the hashed join algorithm, with the hash function F(x) = 1,
the hashed join algorithm becomes what join algorithm? Argue/explain your answer.
7. (PDBM 14.1) See textbook for question.
14.1. Which statement is not correct?
a. A transaction is a set of database operations (e.g., a consecution of
SQL statements in a relational database), induced by a single user or
application, that should be considered as one undividable unit of work.
b. Transactions typically exist in isolation, and cannot be executed
concurrently with other transactions on the same data.
c. It should not be possible to terminate a transaction in such a way
that the database remains in an inconsistent state, because some
operations of a single transaction were executed successfully and
others were not.
d. Recovery is the activity of ensuring that, whichever problem
occurred, the database is returned to a consistent state afterwards, without any data loss.
8. (PDBM 14.2) See textbook for question.
14.2. When a transaction is aborted, it is important that…
a. all changes made by the individual operations belonging to that
transaction should be made permanent.
b. a rollback of the transaction is executed: all changes made by the
transaction’s respective operations should be undone.
9. (PDBM 14.3) See textbook for question.
14.3. Which of the following DBMS components is involved in
transaction management?
a. Scheduler.
b. Stored data manager.
c. Buffer manager.
d. Recovery manager.
e. All of the above.
10. (PDBM 14.10E) See textbook for question.
14.10E Discuss the ACID properties of transaction management and the
responsibility of the DBMS’s transaction management system to ensure this.
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.