QuestionQuestion

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.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

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....
$12.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats