Shopping Database Many Query Questions Refer Following Database Schema Website Keeps Track Q43842939
the shopping database
For many query questions we refer to the following databaseschema for a website that keeps track of what people like to buy oractually buy in different on-line supermarkets. (Think of somethinglike a cross-store loyalty card system.)
customer(cID, cName, street, city)
Customers with unique cID and other attributes
store(sID, sName, street, city)
Stores with a unique ID and other attributes. Stores with thesame name but different sID (and address) form what we call astore chain.
product(pID, pName, suffix)
Products with a unique ID
shoppinglist(cID, pID, quantity, date)
What a customer wants to buy on a certain date. (Every customercreates at most one shoppinglist per day.)
purchase(tID, cID, sID, pID, date, quantity, price)
A transaction: what a customer bought in one go;
every transaction (tID) corresponds to one customer (cID) buyingfrom one store (sID) on one date (date); at some point during thecourse we see that this actually means tID → cID,sID,date
inventory(sID, pID, date, quantity, unitPrice)
Inventory of a store as published at the start of a date.
Primary key attributes are underlined. (Foreign keys are notindicated but can be inferred from the attribute names that havebeen used.)
- The interpretation of the schema is as follows:
Customers and stores should be pretty obvious. - Products have a unique id and name but also some textual suffixthat can indicate a content (weight in gram or content in liter, orsize such as large, medium, small, etc. we do not “interpret” thissuffix).
- A customer may wish to buy products on a certain date. Theshoppinglist contains each product only once, with a quantity (howmany “units” of the product to buy.)
- A customer then goes to an on-line store to buy some products.(The address of the store is only important for goods pick-up.)Such a purchase constitutes a transaction (hence tID). In apurchase a customer is at one store on one date and buys a numberof products in a certain quantity and pays a price for that product(and quantity). It’s like the “receipt” from the purchase. Eachproduct is listed only once on that receipt.
- Each supermarket has inventory. For each product it has anumber of units (quantity) in stock at the start of a certain date,and each product has a unit price. (We do not keep track ofdiscounts that may lower the total price when buying several itemsin a single purchase.)
The database is “complete” (closed world assumption): we do notconsider anything that may exist outside the database (like peoplewho are not a customer or products that nobody wants and no storesells).
All customers appear in customer, all products inproduct and all stores in store. A store namethat appears only once does not constitute a store chain. A storechain requires the existence of at least two stores with the samename.
When a store carries (sells) a product it may temporarily havean inventory quantity of 0 (meaning the product is out of stock).The product is then not removed from inventory. It ispossible that there are several products (different pID) with thesame pName and suffix, due to different identification schemes.
Although the true inventory changes constantly during the daythe published inventory is only updated once, at the start of theday. (At some point during the course we start dealing with thisissue.)
Question 4 1.4 pts Consider again the shopping database schema. Select all the safe Datalog queries that accurately represent the following task: “Two customers have ‘similar taste’ if they have ever purchased the same product (pID) in the same store (sID) or if there exists a third customer with which they both have ‘similar taste! Retrieve all pairs of customer names that have similar taste!” similar Taste(cID, ID2) :- purchase(tID,CID,sID,pID,date,quantity,price), purchase(tID2,c1D2,sID,PID,date2,quantity2 price2) similar Taste(cID, ID2) :- similar Taste(cID, ID3). similar Taste(cID3, cID2) ?similar Taste(cID, ID2) similar Taste(CID, CID2) :- purchase(tID cID,SID,pD,date, quantity,price), purchase(tID2,cID2,sID,pID,date, quantity2,price2) ?similar Taste(cID, ID2) None of the other options is correct similar Taste(cID, ID2):- purchase(tID,cID,sIDpID,date,quantity,price), purchase(tID2,c1D2,sID,pID,date2, quantity2, price2) similar TasteCusomerNames(cName, cName2) :- similar Taste(CID, CID2), customer(cID, Name, street, city), customer(cID2, cName2, street2, city2) ?similar Taste CusomerNames(cName, cName2) similar Taste(cID, ID2) :- purchase(tIDcID,sID,pID,date,quantity,price), purchase(tID2,cD2,sID,pID,date2, quantity2, price2) similar Taste(cID, CID2) :- similar Taste(cID, ID3). similar Taste(clD3, c/D2) similar TasteCusomerNames(cName, cName2):- similar Taste(cID, ID2), customer(cID, Name, street, city), customer(CID2, cName2, street2, city2) ?similar TasteCusomerNames(cName, cName2) Show transcribed image text Question 4 1.4 pts Consider again the shopping database schema. Select all the safe Datalog queries that accurately represent the following task: “Two customers have ‘similar taste’ if they have ever purchased the same product (pID) in the same store (sID) or if there exists a third customer with which they both have ‘similar taste! Retrieve all pairs of customer names that have similar taste!” similar Taste(cID, ID2) :- purchase(tID,CID,sID,pID,date,quantity,price), purchase(tID2,c1D2,sID,PID,date2,quantity2 price2) similar Taste(cID, ID2) :- similar Taste(cID, ID3). similar Taste(cID3, cID2) ?similar Taste(cID, ID2) similar Taste(CID, CID2) :- purchase(tID cID,SID,pD,date, quantity,price), purchase(tID2,cID2,sID,pID,date, quantity2,price2) ?similar Taste(cID, ID2) None of the other options is correct similar Taste(cID, ID2):- purchase(tID,cID,sIDpID,date,quantity,price), purchase(tID2,c1D2,sID,pID,date2, quantity2, price2) similar TasteCusomerNames(cName, cName2) :- similar Taste(CID, CID2), customer(cID, Name, street, city), customer(cID2, cName2, street2, city2) ?similar Taste CusomerNames(cName, cName2) similar Taste(cID, ID2) :- purchase(tIDcID,sID,pID,date,quantity,price), purchase(tID2,cD2,sID,pID,date2, quantity2, price2) similar Taste(cID, CID2) :- similar Taste(cID, ID3). similar Taste(clD3, c/D2) similar TasteCusomerNames(cName, cName2):- similar Taste(cID, ID2), customer(cID, Name, street, city), customer(CID2, cName2, street2, city2) ?similar TasteCusomerNames(cName, cName2)
Expert Answer
Answer to the shopping database For many query questions we refer to the following database schema for a website that keeps track …
OR