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

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use 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....

By purchasing this solution you'll be able to access the following files:
Solution.docx.

$1.00
for this solution

or FREE if you
register a new account!

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