Relational Calculi Please read carefully and please try to type your answers as
ID: 3757065 • Letter: R
Question
Relational Calculi
Please read carefully and please try to type your answers as handwritting can be difficult to read at times.
Thanks!
Consider the following database schema: Bold are keys:
EMPLOYEE (emp_name, address_street, address_city, phone_no)
DEPARTMENT (dept_name, mgr_name, mgr_start_date)
WORKS_ON (emp_name, industry_name, salary)
INDUSTRY (industry_name, location_city)
MANAGES (emp_name, mgr_name)
Write Tuple Relational Calculus expressions for problems 1 and 2.
Write Domain Relational Calculus expressions for problems 3, 4, and 5.
1. List the names of all employees who don’t work for ‘Verizon Wireless’ and make more than $50,000 per year.
2. Find the names and phone number of all employees who work for Intel and live in Santa Clara.
3. Find the names of all employees who live in the same city and on the same street as their managers.
4. Find the names of all employees who live in the same city as the industry they work at is located in.
5. Find the names of all industries located in every city in which Intel is located.
Explanation / Answer
1. List the names of all employees who don’t work for ‘Verizon Wireless’ and make more than $50,000 per year.
Tuple Relational Calculus-> {s.emp_name | works_on(s) ^ s.industry_name != 'Verizon Wireless' ^ s.salary > 50000}
2. Find the names and phone number of all employees who work for Intel and live in Santa Clara.
Tuple Relational Calculus-> {t.emp_name t.phone_no | ( s) (works_on(s) ^ (s.emp_name = t.emp_name ^ s.industry_name = 'Intel' ^ t.address_city = 'Santa Clara'}
3. Find the names of all employees who live in the same city and on the same street as their managers.
Domain Relational Calculus-> {<t> | s, c, m (<t, s, c> employee <t, m> manages <m,s,c> employee)}
4. Find the names of all employees in this database who live in the same city
Domain Relational Calculus-> { <p> | st, c, co, sa (<p, st, c> employee <p, co, sa> works <co, c> company)}
5. Find all companies located in every city in which Intel is located.
Domain Relational Calculus-> {<C> | co2, ci2 (<co2, ci2> company co2 = Intel' <co2, ci2> company)}