Relational Algebra is essential in database since it plays an important role in query optimization. To reach this goal, the first step is transforming the SQL to Relational Algebra.
Many materials can be found on the net, such as http://www.databasteknik.se/webbkursen/relalg-lecture/index.html and phungdinhvu.110mb.com/uploadfordown/shiyong_DB1_exercise1.ppt
Besides, I want to give a more complex example, as follows.
Consider a movie review database with the following schema:
MOVIE (Mid, Mname, Director, Earning)
CRITIC (Cid, Cname, City)
PAPER (Pid, Pname, City)
REVIEW (Mid, Cid, Pid, Rating)
The goal is to find cities such that all critics living there have reviewed all movies directed by ABC.
It can be well solved by SQL:
- select c1.City from CRITIC as c1 where not exists ( select * from CRITIC as c2 where c2.City = c1.City and c2.Cid not in (select r1.Cid from REVIEW as r1, MOVIE as m1 where r1.Mid=m1.Mid) )
After transforming the SQL to Relational Algebra:
A1 <- /pi_Mid (/sigma_{Director=ABC} (MOVIE) )
A2 <- /pi_{Mid, Cid} (Review) / A1
A3 <- /pi_Cid (CRITIC) - A2
A4 <- /pi_City (CRITIC) - /pi_City( CRITIC /join_{Cid=A3.Cid} A3)
Finally, A4 contains the result that we want.
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
