Relational Algebra Tutorial PDF

Relational Algebra Tutorial Consider the following RDB schema: City (cityId, cityName, population) Major (mcode, mname, mcredits) Student (sid, fn, ln, balance, email, cityId*, mcode*) RDB Schema Diagram: City...

Relational Algebra Tutorial Consider the following RDB schema: City (cityId, cityName, population) Major (mcode, mname, mcredits) Student (sid, fn, ln, balance, email, cityId*, mcode*) RDB Schema Diagram: City Major cityId integer not null mcode char(5) not null cityName char(30) not null mname char(30) not null population smallint not null mcredts smallint not null cityId = cityId mcode = mcode Student sid integer not null fn char(20) not null ln char(30) not null balance float not null email varchar2(100) null cityId integer not null mcode char(5) not null 1. Get the list of all cities City ∏(City) cityId, cityName, population σ( City ) 2. Get the list of all cities that have population > 100 σ population > 100 City( ) 3. Get the ids and names of all cities having a population < 200 ∏( σ ( )) population < 200 City cityId, cityName 4. Get the id, name, major code, and balance of all students having a balance between 500 and 5000 inclusive ∏( σ ( balance ≥ 500 ˄ balance ≤ 5000 Student )) sid, fn, ln, mcode, balance 5. Get the id, name, major code, and balance of all CC students or students having a balance > 500 ∏( σ ( mcode = “CC” ˅ balance > 500 Student )) sid, fn, ln, mcode, balance R1 ∏(σmcode = “CC” (Student)) sid, fn, ln, mcode, balance R2 ∏(σbalance > 500 (Student)) sid, fn, ln, mcode, balance R1 U R2 6. Get the id, name, and balance of all students such that their balance is not between 200 and 1000 ∏( σ ( balance < 200 ˅ balance > 1000 Student )) sid, fn, ln, balance R1 ∏(σbalance < 200 (Student)) sid, fn, ln, balance  R2 ∏(σbalance > 1000 (Student)) sid, fn, ln, balance R1 U R2 7. Get the id, name, and city name of all the students ∏ (Student sid, fn, ln, cityName ∞ ) City 8. Get the id, name, major code and major name and the city name of all non CCE students ∏ (σ mcode ≠ “CCE” (City ∞ ∞ Student )) Major sid, fn, ln, S.mcode, mname, cityName CSM City ∞ ∞ Student Major ∏ (σ mcode ≠ “CCE” CSM ( )) sid, fn, ln, S.mcode, mname, cityName 9. Get the id, name, major code and major name of all CCE, CC, and CS students ∏ ( σ mcode = “CCE” (Student) ) sid, fn, ln, mcode ˅ mcode = “CC” ˅ mcode = “CS” 10. Get the major code of all majors that have no students yet. ∏ (Major) -∏( Student) mcode mcode 11. Get the major code and name of all majors that have no students yet. ∏ (Major) mcode,mname - ∏ (Student ∞ mcode,mname Major) 12. Get the student id and name of all the students living in Beirut. ∏ (σcityName = “Beirut” (Student sid, fn, ln ∞ City)) 13. Get the student id, name, and balance of all Computer Communication students that are living in Beirut. ∏ (σcityName = “Beirut” ( City sid, fn, ln, balance ∞ ∞ ˄ mname = “Computer Communication” Student )) Major 14. Give the highest student balance ς MAX (balance) (Student) 15. Give the lowest balance of all CC students ς MIN (balance) (σ mcode = ”CC” (Student)) 16. How many students do we have? ς COUNT (*) (Student) 17. How many students are living in Beirut? ς COUNT (*) (σcityName = “Beirut” (Student ∞ City )) 18. For each major code, give the total of students’ balance. In other terms, give the total of students balance per major code. mcode ς SUM (balance) (Student) 19. For each major name, give the total of students’ balance. mname ς SUM (balance) ∞ (Student Major ) 20. Show the student id and name of all the students that have emails. ∏ ( σ sid, fn, ln ( email ≠ NULL Student)) 21. Show the student id and name of all the students that do not have emails. ∏ ( σ ( email = NULL Student )) sid, fn, ln 22. delete all students that live in Beirut student student - σcityname =”Beirut” (Student∞ city) 23. add a new major with code: MEE, name: mechatronics, number of credits 110 major  major U  {(“MEE”, “mechatronics”, 110)} 24. How many majors are students majored / registered in? ς COUNT (DISTINCT mcode) (Student) 25. Give the total number of students that have emails. ς COUNT (email) (Student) 26. Give the list of all major codes that students are registered in. Every major code should appear once. ∏ (DISTINCT mcode) (Student)