Database Development Questions

1. Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)

The key fields are underlined, and the domain of each field is listed after the field name. Therefore sid is the key for Suppliers, pid is the key for Parts, and sid and pid together form the key for Catalog. The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in relational algebra, tuple relational calculus, and domain relational calculus:
a. Find the names of suppliers who supply some red part.
b. Find the sids of suppliers who supply some red or green part.
c. Find the sids of suppliers who supply some red part or are at 221 Packer Street.
d. Find the sids of suppliers who supply some red part and some green part.
e. Find the sids of suppliers who supply every part.
f. Find the sids of suppliers who supply every red part.
g. Find the sids of suppliers who supply every red or green part.
h. Find the sids of suppliers who supply every red part or supply every green part.
i. Find pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid.
j. Find the pids of parts supplied by at least two different suppliers.

2.(Consider the following relations containing airline flight information:
Flights(flno: integer, from: string, to: string,
distance: integer, departs: time, arrives: time)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)

Note that the Employees relation describes pilots and other kinds of employees as well; every pilot is certified for some aircraft (otherwise, he or she would not qualify as a pilot), and only pilots are certified to fly.

Write the following queries in relational algebra, tuple relational calculus, and domain relational calculus.

a. Find the eids of pilots certified for some Boeing aircraft.
b. Find the names of pilots certified for some Boeing aircraft.
c. Find the aids of all aircraft that can be used on non-stop flights from Bonn to Madras.
d. Identify the flights that can be piloted by every pilot whose salary is more than $100,000.
e. Find the names of pilots who can operate planes with a range greater than 3,000 miles but are not certified on any Boeing aircraft.
f. Find the eids of employees who make the highest salary.
g. Find the eids of employees who make the second highest salary.

3. What is an unsafe query? Give an example and explain why it is important to disallow such queries.

3. What is an unsafe query? Give an example and explain why it is important to disallow such queries.

Unsafe queries are queries using relational calculus with infinite number of results. An example of an unsafe query is {S | ¬ (S ∈ Students)} which shows everything not relevant to Students meaning everything else. There are infinite number of results and is an unsafe query and is significant to prohibit unsafe queries due to getting a list of all results after a limited time....

This is only a preview of the solution. Please use the purchase button to see the entire solution