Please Specify the Queries in Relational Algebra using Select, Project, Rename,

Question

Please Specify the Queries in Relational Algebra using Select, Project, Rename, Join...Operations

Following is a relational database schema SCHOOL_DB = {STUDENT, STAFF, SCHOOL, SCHOOL_BOARD, ACTIVITY, PARTICIPATE}. In each relation schema, the underlined attribute represents the primary key. Specify the following queries on the SCHOOL_DB relational database schema using the relational operators. Retrieve the maximum grade of "Westboro School" students who got ranking more than 4 in the "Art" activity that they are participating in. List the names of all school heads who have staff in their school with the same first name as themselves. For each activity, list the activity name and the minimum ranking of each activity. For each school, retrieve the school name and the average salary of all staff working in that school. Retrieve the names of all students who participate on every activity. Retrieve the names of all students who do not participate on any activity.

Explanation / Answer

Hi,

Please find below the answers-

Ans a) select max(grade) from student a join school b

on a.school_id=b.school_id

and b.school_name='Westboro School'

join participate c on a.std_id=c.std_id

and c.ranking>4

join activity d on a.school_id=d.school_id

and d,act_name='Art'

Ans b) select a.school_head from school a join staff b

on a.school_id=b.school_id

right outer join staff c on b.sid=c.sid

and c.fname=b.fname

Ans c) select a.act_name,min(ranking) from activity a

join participate b

on a.act_id=b.act_id

group by a.act_name

Ans d) select a.school_name,b.avg(salary) from school a

join staff b on a.school_id=b.school_id

group by a.school_name;

Ans e) select a.fname,a.lname from student a

join participate b on a.std_id=b.std_id

where b.act_id = ALL(select act_id from activity);

Ans f)

select a.fname,a.lname from student a

where a.std_id not in(select std_id from participate);

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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