Question
In: Computer Science
Consider the following schema: Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string)...
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 WITH BASIC OPERATORS AND AGGREGATE FUNCTIONS ONLY
MEANS IF THE QUERIES CANNOT FULLY EXPRESSED BY BASIC OPERATORS, USE AGGREGATES.
1. Find the names of suppliers who supply some red part.
2. Find the sids of suppliers who supply some red or green part.
3. Find the sids of suppliers who supply some red part or are at 221 Packer Street.
4. Find the sids of suppliers who supply some red part and some green part.
5. Find the sids of suppliers who supply every part.
6. Find the sids of suppliers who supply every red part.
7. Find the sids of suppliers who supply every red or green part.
8. Find the sids of suppliers who supply every red part or supply every green part.
9. Find pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid.
10. Find the pids of parts supplied by at least two different suppliers.
11. Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.
12. Find the pids of parts supplied by every supplier at less than $200. (If any supplier either does not supply the part or charges more than $200 for it, the part is not selected.)
Solutions
venereology answered 8 months agoRelated Solutions
INRO TO DATABASES Consider the following Schema: Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname:...
INRO TO DATABASES
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 Catalog relation lists the prices charged for parts by
Suppliers. Write the following queries in SQL using join, nested
queries and set operators.
1. Find names of suppliers who supply every red or green
part.
2. Find the sids of suppliers who supply every red part or
supply every green part.
3. Find sids...
Consider the following schema: Suppliers(sid, sname, address) Parts(pid, pname, color) Catalog(sid, pid, cost) In a plain...
Consider the following schema:
Suppliers(sid, sname, address)
Parts(pid,
pname, color)
Catalog(sid, pid,
cost)
In a plain text file, write the following queries in SQL:
1.Find the names of all suppliers who supply a green part.
2.Find the names of all suppliers who are from Illinois.
3.Find the names of all suppliers who sell a red part costing
less than $100.
4.Find the names and colors of all parts that are green or
red.
In writing these
(basic) queries you may...
Consider the following schema: Suppliers(sid, sname, address) Parts(pid, pname, color) Catalog(sid, pid, cost) In a plain...
Consider the following
schema:
Suppliers(sid, sname,
address)
Parts(pid,
pname, color)
Catalog(sid, pid,
cost)
In a plain text file,
write the following queries in SQL:
Find the names of all suppliers who supply a green part.
Find the names of all suppliers who are from Illinois.
Find the names of all suppliers who sell a red part costing
less than $100.
Find the names and colors of all parts that are green or
red.
In writing these
queries you may make...
Consider the following schema: Suppliers(sid, sname, address) Parts(pid, pname, color) Catalog(sid, pid, cost) write the following...
Consider the following schema:
Suppliers(sid, sname,
address)
Parts(pid,
pname, color)
Catalog(sid, pid,
cost)
write the following queries in SQL:
* Find the names of all suppliers who supply a green part.
*Find the names of all suppliers who are from Illinois.
*Find the names of all suppliers who sell a red part costing
less than $100.
*Find the names and colors of all parts that are green or
red.
In writing these
queries you may make the following assumptions:
a....
Consider the following schema: Suppliers (sid, sname, address) Parts (pid, pname, colour) Catalog(sid, pid, cost) The key for Suppliers is sid, for Parts is pid, and for Catalog is sid and pid The Ca...
Consider the following schema: Suppliers (sid, sname, address) Parts (pid, pname, colour) Catalog(sid, pid, cost) The key for Suppliers is sid, for Parts is pid, and for Catalog is sid and pid The Catalog relation associates prices charged for parts by suppliers. Write the following queries using relational algebra. For items (a) through (e), use the "sequences of assignments" form. For items (f) and (g), use the "expression tree" form. List all assumptions. (Some marks will be given for the quality of your answers.) (a) Find...
Consider the University Database with the following relations: Professors (pid, pname, dept, ext) Students (sid, sname,...
Consider the University Database with the following
relations:
Professors (pid, pname, dept, ext)
Students (sid, sname, major-dept, year)
Courses (cid, cname, dept, credithours)
Enrollment (sem-year, sid, cid, grade)
Teaches (pid, cid, sem-year, class-size)
where,
Professors: All professors have professor id (pid), name
(pname), department that they work (dept), and a phone number
extension for their office (ext).
Students: All students have id (sid), name (sname), department
for their major (major-dept), and a year (year i.e, freshman,
sophomore, junior, etc). Courses:...
Consider the University Database with the following relations: Professors (pid, pname, dept, ext) Students (sid, sname,...
Consider the University Database with the following
relations:
Professors (pid, pname, dept, ext) Students (sid, sname,
major-dept, year)
Courses (cid, cname, dept, credithours)Enrollment (sem-year,
sid, cid, grade)
Teaches (pid, cid, sem-year, class-size),
Professors: All professors have professor id (pid), name
(pname), department that they work (dept), and a phone number
extension for their office (ext). Students: All students have id
(sid), name (sname), department for their major (major-dept), and a
year (yeari.e, freshman, sophomore, junior, etc). Courses: All
courses have...
Consider the University Database with the following relations: Professors (pid, pname, dept, ext) Students (sid, sname,...
Consider the University Database with the following
relations:
Professors (pid, pname, dept, ext) Students
(sid, sname, major-dept, year)
Courses (cid, cname, dept, credithours)
Enrollment (sem-year, sid, cid, grade)
Teaches (pid, cid, sem-year, class-size)
where,
Professors: All professors have professor id (pid), name
(pname), department that they work (dept), and a phone number
extension for their office (ext).
Students: All students have id (sid), name (sname), department
for their major (major-dept), and a year (year i.e, freshman,
sophomore, junior, etc).
Courses:...
Given a relational database that consists of the following relations: Performer (pid: integer, pname: string, years_of_experience:...
Given a relational database that consists of the following
relations:
Performer (pid: integer, pname: string, years_of_experience:
integer, age: integer) Movie (mname: string, genre: string,
minutes: integer, release_year: integer, did: integer) Acted (pid:
integer, mname: string)
Director (did: integer, dname: string, earnings: real)
Do the following using your Azure SQL database:
a) Use SQL statements to create the relations.
b) Populate the relations using SQL statements with the given data
posted on Canvas. c) Implement the SQL queries for the
following:...
INTRO TO DATABASE Consider the Sailors-Boats-Reserves database described below. Sailors(sid: integer, sname: string, rating: integer, age:...
INTRO TO DATABASE
Consider the Sailors-Boats-Reserves database described
below.
Sailors(sid: integer, sname: string, rating:
integer, age: real)
Boats(bid: integer, bname: string, color:
string)
Reserves(sid: integer, bid: integer, day:
date)
Write each of the following queries in SQL.
1. Find the names and ages of sailors who have reserved at least
two boats.
2. For each boat reserved by at least 2 distinct sailors, find
the boat id and the average age of sailors who reserved it.
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- Baked potatoes are normally wrapped in aluminum foil. How does this affect the cooling of the...
- What is the process to obtain biomass production
- A labor market has 50,000 people in the labor force. Each month, a fraction p of...
- A road with a radius of 72 m is banked so that a car can navigate...
- 1) If you had not dried the solid, would the calculated percent yield have been higher,...
- Two structures of alanine can be drawn that have a net charge of zero, but the...
- Co(II) was used as an internal standard to analyze a sample of Ti(IV) with atomic absorption...
ADVERTISEMENT