employee (person-name, street, city) works (person-name, company-name, salary) c

Question

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, where the primary keys are underlined. Give an expression in tuple relational calculus for each of the following queries:                                                (1.5 Point)

a. Find all employees who work directly for “STC”

b. Find all cities of residence of all employees who work directly for “STC”

c. Find the name of the manager of the manager of “STC”

Q3: Write the following queries in SQL, using the university schema.                                              (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, room number, 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 that have 3 credits.

b.Find the names of all students who have taken at least one Comp. Sci. course; make sure there are no duplicate names in the result

c.Find the maximum enrollment, across all sections, in Autumn 2009.

d.Find the IDs and names of all students who have not taken any course 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, do not occur in the section relation).

g.Insert every student whose tot cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.

h.Create a new course “CS-001”, titled “Weekly Seminar”,with 0 credits.

i.Create a section of this course in autumn 2009, with sec id of 1.

j.Enroll every student in the Comp. Sci. department in the above section.

Explanation / Answer

ANS 1
a) SELECT T1.PERSON_NAME
FROM EMPLOYEE T1 INNER JOIN WORKS T2
ON T1.PERSON_NAME=T2.PERSON_NAME
WHERE T2.COMPANY_NAME='STC'

B) SELECT T1.PERSON_NAME,T1.CITY FROM WORKS T1 INNER JOIN COMPANY T2
ON T1.COMPANY_NAME=T2.COMPANY_NAME
WHERE T2.COMPANY_NAME='STC'

C) SELECT T1.MANAGER_NAME FROM MANAGES T1 INNER JOIN WORKS T2
T1.PERSON_NAME=T2.PERSON_NAME
WHERE T2.COMPANY_NAME='STC'

ANS 2

A) SELECT TITLE FROM COURSE WHERE DEPT_NAME='COMPUTER SCIENCE' AND CREDIT=3
B) SELECT DISTINCT NAME FROM STUDENT T1 INNER JOIN COURSE T2
T1.DEPT_NAME=T2.DEPT_NAME
WHERE T2.DEPT_NAME='COMPUTER SCIENCE'

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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