Question

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 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....

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

Assisting Tutor

Related Homework Solutions

E-R Diagram
Homework Solution
$25.00
Computer
Science
E-R Diagram
Database
Development
Customer
Service
Installation
Transparent Data Encryption (2780 words)
Homework Solution
$100.00
TDE
Data
Transparent
Encryption
Database
Oracle
Technology
Tablespace
Two-tier
Architecture
Network
Authentication
Advanced
Security
Cryptographic
Overhead
Metric
PKCS
Standard
Compliance
SQL Server Installation Report
Homework Solution
$20.00
SQL Server
Installation
How To
Guide
Computer
Science
Database
Development
Murach
Script
Management
Reviewing PL/SQL
Homework Solution
$12.00
Computer
Science
Database
Development
Data
PL/SQL
DBA
Procedures
Triggers
Technology
Applications
Computer Science Questions
Homework Solution
$60.00
Computer Science
Integrity Constraints
DBMS
Registration Forms
Serializability Properties
Lock Based Protocols
Diagrams
Get help from a qualified tutor
Live Chats