Assignment - 6 Relational Algebra

Assignment – 6 Relational Algebra    Submission Date 12-05-11  

Q.1	Consider the following relations: Suppliers (Sid, S_name, Address) Parts (Pid, P_name, Colour) Catalogue (Sid, Pid, Cost) The key fields are underlined. Write the following queries in relational algebra.
	1) Find the names of suppliers who supply some red parts. 2) Find the Pid of parts supplied by every supplier at less than INR 500. 3) Find the Pid of parts that are supplied by at least two different suppliers. 4) Find the Sid of suppliers who supply every part or green part. 5) Find the Sid of suppliers who supply every part. 6) Find the Sid of suppliers who supply some red or green part. 7) Find the Sid of suppliers who supply some red and some green parts.
Q.2	Consider the relation schemas as follows and Convert Following query into SQL and Relational Algebra. WORKS           (PERSON_NAME, COMPANY_NAME, SALARY) LIVES              (PERSON_NAME, STREET, CITY) LOCATED_IN   (COMPANY_NAME, CITY) MANAGERS     (PERSON_NAME, MANAGER_NAME)           Where manager_name referes to Person_name
	1)    Find the name of the persons who work for company ‘FBC’ 2)    List the names of the persons who work for company ’FBC’ along with the cities they live in. 3)    Find the persons who work for company ’FBC’ with a salary of more than 10000. List the names of these persons along with the streets and cities where they live. 4)    Find the names of the persons who live and  work in the same city. 5)    Find the names of the persons who live in the same city and on the same street as their managers. 6)    Find the names of the persons who do not work for Company ’FBC’. 7)    Find the persons whose salaries are more than the salary of everybody who work for company ’SBC’. 8)    Find the names of the companies that is located in every city where company ’SBC’ is located in.
Q.3	Write down Relational Algebra for the given schema. SOFTWARE(sid,software_name,develop_in,selling_cost,development_cost) PROGRAMMER (pid, pname, date_join, dept_name, sid,Salary, gender)
	Find out the selling cost average for packages developed in Oracle. List details of programmer whose salary is greater than Rs.10,000. Display details of Software develop by “Mr. Ram”. How many software has highest development cost. List details of programmer who join in the current month. Retrieve name of programmer who do not involve in any software development. Retrieve all male programmers working in ‘MCA’ department.