employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)
Q2. Consider the relational database of the above Figure, wherethe primary keys are underlined. Give an expression in tuplerelational calculus for each of the followingqueries: (1.5 Point)
a. Find all employees who work directly for “STC”
b. Find all cities of residence of all employees who workdirectly for “STC”
c. Find the name of the manager of the manager of “STC”
Q3: Write the following queries in SQL, using the universityschema. (1.5 Point)
classroom(building, room number, capacity)
department(dept name, building, budget)
course(course id, title, dept name, credits)
instructor(ID, name, dept name, salary)
section(course id, sec id, semester, year, building, roomnumber, time slot id)
teaches(ID, course id, sec id, semester, year)
student(ID, name, dept name, tot cred)
takes(ID, course id, sec id, semester, year, grade)
advisor(s ID, i ID)
time slot(time slot id, day, start time, end time)
prereq(course id, prereq id)
a.Find the titles of courses in the Comp. Sci. department thathave 3 credits.
b.Find the names of all students who have taken at least oneComp. Sci. course; make sure there are no duplicate names in theresult
c.Find the maximum enrollment, across all sections, in Autumn2009.
d.Find the IDs and names of all students who have not taken anycourse offering before spring 2009
e.Increase the salary of each instructor in the Comp. Sci.department by 10%.
f.Delete all courses that have never been offered (that is, donot occur in the section relation).
g.Insert every student whose tot cred attribute is greater than100 as an instructor in the same department, with a salary of$10,000.
h.Create a new course “CS-001”, titled “Weekly Seminar”,with 0credits.
i.Create a section of this course in autumn 2009, with sec id of1.
j.Enroll every student in the Comp. Sci. department in the abovesection.