CS2102 event (eid, ename, ecity) registration(cid, eld customer (cid, cname, cci
ID: 3756096 • Letter: C
Question
CS2102 event (eid, ename, ecity) registration(cid, eld customer (cid, cname, ccity) Translate the following queries into the indicated language. Use your knowledge of integrity constraints to simplify the queries. For SQL queries, do not use nested queries in the SELECT and FROM clause, if possible and unless otherwise indicated. For SQL queries, prefer simpler queries to aggregate queries, to nested queries, to queries with algebra operators (UNION, INTERSECT, JOIN, EXCEPT, MINUS etc.) and other complicated answers, if possible and unless otherwise indicated Question 11. (4 marks) (Algebra) Find the names of the customers who registered to some of the events taking place in the city where they live. Do not use Join operators (a); prefer Cartesian Product (x). Feel encouraged drawing the query as a tree. Question 12. (4 marks) (SQL) Find the names of the customers who registered to some of the events taking place in the city where they live.Explanation / Answer
11.answer)
cname ( customer.cid=registration.cid ^ registration.eid=event.eid ^ event.ecity=customer.ccity ( event x registation x customer) )
First of all we cross product the tuples event,registration,customer tables.from the cross product we will take the tuples where customer.cid present in the registration.cid and registration.eid present in the event.eid and event.ecity present in the customer.ccity.finally we select the cnames form resultant tuples.
12.answer)
SELECT cname FROM customer,registration,event WHERE customer.cid=registration.cid AND registration.eid=event.eid AND event.ecity=customer.ccity;
First of all we cross product the tuples event,registration,customer tables.from the cross product we will take the tuples where customer.cid present in the registration.cid and registration.eid present in the event.eid and event.ecity present in the customer.ccity.finally we select the cnames form resultant tuples.