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

Expert Solution

venereology answered 7 months ago
Next > < Previous

Related 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
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT