Question
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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started