Consider the relational database shown below, consisting of four tables of information on suppliers (S), parts (P), projects (J), and project profiles (SPJ). Supplier numbers (S#), part numbers (P#), and project numbers (J#), are unique in tables S, P, and J, respectively see http://wiki.c2.com/?SupplierPartsProjectsDatabase. The SPJ table contains which suppliers supply which part (in what quantities) to which project. From a database perspective, the following tables would be defined:

Table name Purpose Key

-------------------------------------------------------------------

S Suppliers (S#)

P Parts (P#)

J Jobs (J#)

SPJ Parts supplied by Suppliers for Jobs (S#, P#, J#)

 S 

 S# SNAME STATUS CITY 

 ---------------------------- 

 s1 Smith 20 London 

 s2 Jones 10 Paris 

 s3 Blake 30 Paris 

 s4 Clark 20 London 

 s5 Adams 30 Athens 

 P 

 P# PNAME COLOR WEIGHT CITY 

 ---------------------------------- 

 p1 Nut Red 12 London 

 p2 Bolt Green 17 Paris 

 p3 Screw Blue 17 Oslo 

 p4 Screw Red 14 London 

 p5 Cam Blue 12 Paris 

 p6 Cog Red 19 London 

 J 

 J# JNAME CITY 

 ---------------------- 

 j1 Sorter Paris 

 j2 Display Rome 

 j3 OCR Athens 

 j4 Console Athens 

 j5 RAID London 

 j6 EDS Oslo 

 j7 Tape London 

 SPJ 

 S# P# J# QTY 

 ----------------- 

 s1 p1 j1 200 

 s1 p1 j4 700 

 s2 p2 j1 400 

 s2 p2 j2 200 

 s2 p2 j3 200 

 s2 p2 j4 500 

 s2 p2 j5 600 

 s2 p2 j6 400 

 s2 p2 j7 800 

 s2 p5 j2 100 

 s3 p2 j1 200 

 s3 p4 j2 500 

 s4 p6 j3 300 

 s4 p6 j7 300 

 s5 p2 j2 200 

 s5 p2 j4 100 

 s5 p5 j5 500 

 s5 p5 j7 100 

 s5 p6 j2 200 

 s5 p1 j4 100 

 s5 p2 j4 200 

 s5 p4 j4 800 

 s5 p5 j4 400 

 s5 p6 j4 500 

In the Prolog part of the assignment, each of the tables will be stored as relations of facts. For example, the assignment would have the set of facts as below:

supp(s1,'Smith',20,'London').

supp(s2,'Jones',10,'Paris').

supp(s3,'Blake',30,'Paris').

supp(s4,'Clark',20,'London').

supp(s5,'Adams',30,'Athens').

part(p1, 'Nut', 'Red', 12, 'London').

part(p2, 'Bolt', 'Green', 17, 'Paris').

part(p3, 'Screw', 'Blue', 17, 'Oslo').

part(p4, 'Screw', 'Red', 14, 'London').

part(p5, 'Cam', 'Blue', 12, 'Paris').

part(p6, 'Cog', 'Red', 19, 'London').

proj(j1, 'Sorter', 'Paris').

proj(j2, 'Display', 'Rome').

proj(j3, 'OCR', 'Athens').

proj(j4, 'Console', 'Athens').

proj(j5, 'RAID', 'London').

proj(j6, 'EDS', 'Oslo').

proj(j7, 'Tape', 'London').

sppj(s1, p1, j1, 200).

sppj(s1, p1, j4, 700).

sppj(s2, p2, j1, 400).

sppj(s2, p2, j2, 200).

sppj(s2, p2, j3, 200).

sppj(s2, p2, j4, 500).

sppj(s2, p2, j5, 600).

sppj(s2, p2, j6, 400).

sppj(s2, p2, j7, 800).

sppj(s2, p5, j2, 100).

sppj(s3, p2, j1, 200).

sppj(s3, p4, j2, 500).

sppj(s4, p6, j3, 300).

sppj(s4, p6, j7, 300).

sppj(s5, p2, j2, 200).

sppj(s5, p2, j4, 100).

sppj(s5, p5, j5, 500).

sppj(s5, p5, j7, 100).

sppj(s5, p6, j2, 200).

sppj(s5, p1, j4, 100).

sppj(s5, p2, j4, 200).

sppj(s5, p4, j4, 800).

sppj(s5, p5, j4, 400).

sppj(s5, p6, j4, 500).

Requirements:

Given the database of tables (i.e., lists in Scheme and facts in Prolog), you must now write queries (i.e., functions in Scheme and rules in Prolog) for each of the following:

When given a pair of projects, find all suppliers who supply both projects. Return the entire entry (i.e., S#, Sname, Status, City) for the supplier.

When given a city, find all parts supplied to any project in that city. Once again, return the entire entry for the part. Find all parts supplied to any project by a supplier in the same city. In this case, results are organized by all parts for every city in the database.

Find all projects supplied by at least one supplier not in the same city.

Find all suppliers that supply at least one part supplied by at least one supplier who supplies at least one red part.

Find all pairs of city values such that a supplier in the first city supplies a project in the second city.

Find all triples of city, part#, city, such that a supplier in the first city supplies the specified part to a project in the second city, and the two city values are different.

When given a supplier, find all projects supplied entirely by that supplier.

One possible approach to the problem would be to write functions (rules) that perform the basic operations of selection (finding appropriate rows of tables), projections (printing out an entire row of a table), and join (merging two tables on a column-basis, similar to a Cartesian product). These three basic operations can then be combined to answer the queries posed above. A second approach would be to write functions (rules) specifically to answer each of the queries.