the table description  and questions are listed on screenshots. There is no need for the table data because we already know what those table looks like.

Consider the database schema created by the following commands: CREATE TABLE Customers (Cid INTEGER PRIMARY KEY,
cname VARCHARQO) NOT NULL); CREATE TABLE Products (pid INTEGER,
pname VARCHAR(20) NOT NULL,
price INTEGER NOT NULL, PRIMARY KEY (pid)) : CREATE TABLE Purchases (pid INTEGER, cid INTEGER,
store_id INTEGER, time TIMESTAMP,
PRIMARY KEY(store_id, time) ,
FOREIGN KEY (Cid) REFERENCES Customers (Cid),
FOREIGN KEY (pid) REFERENCES Products (pidlli This schema describes a simple shopping scenario. The Customers table contains the names of customers,
the Products table contains product names and prices, and the Purchases table stores time and location
of purchases as well as the customer and product. Write the following queries in SQL. Each of your answers must be a single SQL query. Your SQL queries
will be graded by running them in MySQL on test cases; queries that return the wrong answer might
receive 0 points. In particular, please make sure your query returns the right column(s).

a) (10 points) For each product, find the stores with the fewest sales of that product among all stores.
The result should be a set of (pid,store_id) pairs such that the store with store_id has sold a minimal
number of the product with pid. If multiple stores have the same minimal sales count, return all
appropriate (pid,storejd) pairs. Ignore products that have not been sold at all (i.e., they should not be
part of the query result). (b) (5 points) Find for each product the customers who have bought it exactly thrice. The result should be
a set of (aid, pid) pairs such that the customer with aid has bought the product with pid esxactly three
times. (c) (10 points) Find all pairs of customers who have no purchase in common (i.e., they have not bought
the same product). The result is a set of (cid1,cid2) pairs such that the customer with cidl has never bought a product that was also bought by the customer with cid2. Note that each pair of customers
should appear only once in the result (e.g., if (1,3) is in the result then (3,1) should not be in the result ) . (d) (10 points) Find all pairs of customers who have purchased exactly the same set of products. The
result should be a set of (cid1,cid2) pairs such that the customer with cidl has bought exactly the
same set of products as the customer with cid2. Again, do not output any duplicates (e.g., if (1,3) is in
the result then (3,1) should not be in the result). Write the following queries in Relational Algebra. Use only the base operators of relational algebra
(projection, selection, cross product, join, set union, intersection, set minus, and the renaming operator).
To enhance readability, you are encouraged to split off subexpressions and name them as seen in the lecture. (e) (10 points) Write a relational algebra query for b)
(f) (10 points) Write a relational algebra query for c) (g) (10 points) Write a relational algebra query for d)

• 1493445779-Screen Shot 2017-02-27 at 08.02.34.png
• 1493445780-Screen Shot 2017-02-27 at 08.02.41.png

ComputerScienceExpert

(11)

Status NEW Posted 29 Apr 2017 06:04 AM My Price 8.00

-----------

Attachments

file 1493445781-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer