Advanced Database Topics Assignment -
1) Consider the relation r (Key, Name, Address). The relation takes 200 blocks on disk and holds 10000 tuples.
a) What is the selectivity of an equality predicate on Key?
b) Given a secondary IndexName (height = 3) and a range predicate on Name with selectivity of 0.02, estimate the cost of doing the lookup on A.
c) Given a clustered IndexName (height = 3) and a range predicate on Name with selectivity of 0.02, estimate the cost of doing the lookup on A.
d) Given a predicate PA with selectivity of 0.2 and a predicate PB with a selectivity of 0.5, what are the selectivities of:
(NOTE: you just need to compute the selectivity value, you do not need to estimate any costs in this part. You may have to make some assumptions about correlation between PA and PB)
i) NOT PA
ii) PA AND PB
iii) PA OR PB
iv) PA AND (NOT PB)
2) Consider the following transaction schedules. For each one, determine if the schedule is conflict serializable and determine the equivalent serial schedules. Please note that there may be several equivalent serializable schedules and you should determine all of them.
a) T1: R(X); T3: R(X); T1: W(X); T2: R(X); T3: W(X)
b) T3: R(X); T2: R(X); T3: W(X); T1: R(X); T1: W(X)
3) For each of the following transaction schedules, add a minimal set of Lock commands (shared or exclusive) necessary to access the variables. Note that the transaction does not need to request the same lock twice. For example
R(X), W(X), R(X), W(Y), R(Y) =>
Lock-S(X) R(X), Lock-X(X) W(X), R(X), Lock-X(Y) W(Y), R(Y)
a) R(P), R(Q), R(N), R(P), R(Q), W(P), R(P), R(Q), R(N)
b) What is the difference between releasing locks as soon as you are done with the access (e.g., Lock-X(A), W(A), Release(A), Lock-X (B), Write(B), Release(B)) or holding onto everything until transaction commit (e.g., Lock-X(A), W(A), Lock-X (B), Write(B), Release(A), Release(B))
4) Using PostgreSQL DBMS, download and load SSBM benchmark and execute and time queries as described below. Just like previously, you can use a local PostgreSQL installation. Windows PostgreSQL is easy to install, and I have included Linux (RedHat/CentOS flavor) for setting up Postgres in this assignment.
a) I suggest that you drop the previous index every time you try a new one, because otherwise in addition to evaluating performance changes you will have to verify which index was actually used. You can drop indexes by DROP INDEX [INDEX_NAME]. You can see the query plan by running EXPLAIN [insert your SQL query text]. Running explain on Q1.1 without any indexes produces an estimate of 187297
csc553_db=> explain select sum(lo_extendedprice*lo_discount) as revenue from lineorder, dwdate where lo_orderdate = d_datekey and d_year = 1993 and lo_discount between 1 and 3 and lo_quantity < 25; QUERY PLAN
Aggregate (cost=187297.63..187297.64 rows=1 width=8)
b) Time the cost of an INSERT to Part table and Lineorder table. You would need to create these INSERT statements similar to the data in the table. NOTE: you can execute \timing in PostgreSQL prompt to ensure that every command is timed by the database.
c) Re-create your Q1.1 index from the previous assignment and test the effects on Q1.1 only. Compare the cost before and after as reported by EXPLAIN.
d) Execute and time Q1.1 (Include a screenshot here)
e) Execute and time another INSERT for Lineorder table. Is the cost different?
f) Next, let's consider the benefits of using a clustered index. Using your index from c), recluster the lineorder table. You can do this by running: CLUSTER lineorder USING MyIndexNameFromB;
Did that index improve query performance (looking at both estimated and real times)? Why or why not?
g) Execute and time another INSERT for Lineorder table. Is the cost different?
h) Create a secondary index for Q2.2 (make sure it is being used). Report the runtime and the explain cost
i) Create a covering index for Q2.3. Report the runtime and the explain cost.
j) Create a clustered index for Q2.3. Report the runtime and the explain cost.
Attachment:- Assignment File.rar