2023年11月25日

数据库代写 | Database Assignment Three Questions

本次美国代写主要为数据库索引相关的assignment

Question 1.

Below lists three queries (A)-(C), two tables and schema, and indices I1- I6.
For each of three queries (A)-(C) below, show two different query plans P1 and P2 such that (i) both use one
or more indices I1 to I6 shown below, (ii) P1 is most efficient and (iii) P2 is less efficient than P1. Here, use
show algebra trees if possible. Explain your plans.

Query (A)
SELECT cname
From Course C
WHERE grade > 2015 AND
credit = 2
A: I5, B: I2, C: i4

Query (B)
SELECT S.sname
FROM Student S
WHERE S.age > 20 AND S.sid
IN (SELECT C.sid
FROM Course C
WHERE C.credit = 4
AND C.grade >= 80)

Query (C)
SELECT S.enroll, COUNT(S.enroll)
FROM Student S
WHERE EXISTS (SELECT C.cid
FROM Course C WHERE C.sid =
S.sid AND C.grade < 70)
GROUP-BY enroll

Table schema
Student(sid: integer, sname: string, age: integer, enroll: integer)
Course(cname: string, sid: integer, credit: integer, grade: integer)
Indices
I1: clustered, hash index on <sid> of Student
I2: clustered, B+ index on <grade> of Course
I3: unclustered, B+ index on <grade, credit> of Course
I4: unclustered, B+ index on <grade, sid> of Course
I5: unclustered, B+ index on <enroll> of Student
I6: unclustered, hash index on < age, enroll> of Student

1. Answer the following questions on Query (A).
(a) Regarding on the selection conditions on “grade” and “credit”, which one will have higher reduction
factor? Also, explain assumptions you used to derive your answer.
(b) Show the most efficient query plan, using one of the indices from I1-16.
(c) Show a new index that can be used for an index-only query plan for Query (A).

2. Answer the following questions on Query (B).
(a) Transform Query (B) into a non-nested SQL query.
(b) Show the relational algebra tree for the answer of (a), where projections and selections need to be
optimized.
(c) Show an efficient query plan, by choosing indices. Discuss which index in I2, I3 and I4 should be
used.

3. Answer the following questions on Query (C).
(a) Show an efficient query plan, which uses indices from I1-16.
(b) Design a more efficient query plan for Query (C), by adding a new index to I1-16.