Given two relations: STUDENT(S#, D#, Age, Address). DEPT(D#, Name, Chair, Buildi

Question

Given two relations: STUDENT(S#, D#, Age, Address). DEPT(D#, Name, Chair, Building), ' consider the following query optimization issue. Assume that 25% of students are over 30 years old and that there is one department with name 'CIS'. For the following two equivalent queries: sigma_STUDENT Age > 30 AND DEPT Name = CIS^(STUDENT_STUDENT D DEPT D#)^DEPT) (sigma_STUDENT Age > 30^(STUDENT))_STUDENT D DEPT D#)^(sigma_DEPT NAME CIS^(DEPT)) which one is usually more efficient? Why? Give an example of anomalies caused by uncontrolled concurrent transactions (show your transaction schedule).

Explanation / Answer

7.

STUDENT. D# = DEPT.D#

This query is more efficient than the first one because selection is made first based on conditions in STUDENT and DEPT tables and then Join operation is done. So Number of rows Joined are lesser than the first query as in the first query first Join is made on all the rows in the two tables and then selection is made.

8.

  

Update a at T3 will be lost because Transaction N update a at T4 because of which it will be lost. The anomaly is called Lost update

Transaction M    Time Transaction N Access a T1 T2 Access a Update a T3 T4 Update a

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.