SQL代写 - COMS W4111.001-Introduction to Databases
时间:2020-12-08
1. (3 pts.) Consider the following relation: Employees(eid: integer, sal: integer, age: real, did: integer), where attribute eid is the primary key of the relation. There is a hash index on eid and also a B+-tree index on age. a. Explain how the DBMS could use the available indexes to enforce the constraint that eid is the primary key of the relation when Employees tuples are inserted, deleted, or updated. Discuss all three cases, namely, tuple insertions, deletions, and updates. b. Give an example of an update that is definitely speeded up because of the available indexes. (An English description is sufficient, but be precise and concrete.) c. Give an example of an update that is definitely slowed down because of the presence of the indexes. (An English description is sufficient, but be precise and concrete.) 2. (4 pts.) Consider the following relation: Sailors(sid: integer, sname: string, rating: integer, age: real), where attribute sid is the primary key of the relation, and the following two selection queries: Q1: SELECT * FROM Sailors S WHERE S.rating=5 Q2: SELECT * FROM Sailors S WHERE S.rating>5 For each of the following indexes, decide whether the index matches the selection condition in query Q1 and in query Q2. You should use the exact definition of when an index matches a selection condition that we covered in class. Explain your answer: Index 1: A B+-tree index on rating Index 2: A hash index on rating 3. (3 pts.) Consider the following two relations: Sailors(sid: integer, sname: string, rating: integer, age: real), where attribute sid is the primary key of the relation; and Reserves(sid: integer, bid: integer, day: date), where all three attributes collectively are the primary key of the relation and attribute sid is a foreign key that references the Sailors relation. There is only one index in the database, namely, a hash index on attribute sid of the Sailors relation. Consider the following join of Sailors and Reserves: SELECT * FROM Sailors S, Reserves R WHERE S.sid=R.sid Explain how you can improve the "naive" nested loops algorithm that we covered in class so that you can take advantage of the hash index on Sailors.sid when computing the above join.
essay、essay代写