B561 Advanced Database Concepts Assignment 2 Fall 2021 Dirk Van Gucht This assignment relies on the lectures • SQL Part 1 and SQL Part 2 (Pure SQL); • Views; • Relational Algebra (RA); and • Joins and...

1 answer below »
Uploaded files have everything


B561 Advanced Database Concepts Assignment 2 Fall 2021 Dirk Van Gucht This assignment relies on the lectures • SQL Part 1 and SQL Part 2 (Pure SQL); • Views; • Relational Algebra (RA); and • Joins and semijoins. Furthermore, the lecture on the correspondence between Tuple Rela- tional Calculus and SQL should help you to solve problems in this assign- ment. To turn in your assignment, you will need to upload to Canvas a single file with name assignment2.sql which contains the necessary SQL statements that solve the problems in this assignment. The assignment2.sql file must be so that the AI’s can run it in their PostgreSQL environment. You should use the Assignment2Script.sql file to construct the assignment2.sql file. (Note that the data to be used for this assignment is included in this file.) In addition, you will need to upload a separate assignment2.txt file that contains the results of running your queries. Finally, you need to upload a file assignment2.pdf that contains the solutions to the problems that require it. The problems that need to be included in the assignment2.sql are marked with a blue bullet •. The problems that need to be included in the assignment2.pdf are marked with a red bullet •. (You should aim to use Latex to construct your .pdf file.) 1 Database schema and instances For the problems in this assignment we will use the following database schema:1 Person(pid, pname, city) Company(cname, headquarter) Skill(skill) worksFor(pid, cname, salary) companyLocation(cname, city) personSkill(pid, skill) hasManager(eid, mid) Knows(pid1, pid2) In this database we maintain a set of persons (Person), a set of companies (Company), and a set of (job) skills (Skill). The pname attribute in Person is the name of the person. The city attribute in Person specifies the city in which the person lives. The cname at- tribute in Company is the name of the company. The headquarter attribute in Company is the name of the city wherein the company has its headquarter. The skill attribute in Skill is the name of a (job) skill. A person can work for at most one company. This information is maintained in the worksFor relation. (We permit that a person does not work for any company.) The salary attribute in worksFor specifies the salary made by the person. The city attribute in companyLocation indicates a city in which the company is located. (Companies may be located in multiple cities.) A person can have multiple job skills. This information is main- tained in the personSkill relation. A job skill can be the job skill of multiple persons. (A person may not have any job skills, and a job skill may have no persons with that skill.) A pair (e,m) in hasManager indicates that person e has person m as one of his or her managers. We permit that an employee has multiple managers and that a manager may manage multiple employees. (It is possible that an employee has no manager and that an employee is 1The primary key, which may consist of one or more attributes, of each of these relations is underlined. 2 not a manager.) We further require that an employee and his or her managers must work for the same company. The relation Knows maintains a set of pairs (p1, p2) where p1 and p2 are pids of persons. The pair (p1, p2) indicates that the person with pid p1 knows the person with pid p2. We do not assume that the relation Knows is symmetric: it is possible that (p1, p2) is in the relation but that (p2, p1) is not. The domain for the attributes pid, pid1, pid2, salary, eid, and mid is integer. The domain for all other attributes is text. We assume the following foreign key constraints: • pid is a foreign key in worksFor referencing the primary key pid in Person; • cname is a foreign key in worksFor referencing the primary key cname in Company; • cname is a foreign key in companyLocation referencing the pri- mary key cname in Company; • pid is a foreign key in personSkill referencing the primary key pid in Person; • skill is a foreign key in personSkill referencing the primary key skill in Skill; • eid is a foreign key in hasManager referencing the primary key pid in Person; • mid is a foreign key in hasManager referencing the primary key pid in Person; • pid1 is a foreign key in Knows referencing the primary key pid in Person; and • pid2 is a foreign key in Knows referencing the primary key pid in Person The file Assignment2Script.sql contains the data supplied for this assignment. 3 Pure SQL and RA SQL In this assignemt, we distinguish between Pure SQL and RA SQL. Below we list the only features that are allowed in Pure SQL and in RA SQL. In particular notice that • JOIN, NATURAL JOIN, and CROSS JOIN are not allowed in Pure SQL. • The predicates [NOT] IN, SOME, ALL, [NOT] EXISTS are not allowed in RA SQL. The only features allowed in Pure SQL SELECT ... FROM ... WHERE WITH ... UNION, INTERSECT, EXCEPT operations EXISTS and NOT EXISTS predicates IN and NOT IN predicates ALL and SOME predicates VIEWs that can only use the above RA SQL features The only features allowed in Pure SQL features SELECT ... FROM ... WHERE WITH ... UNION, INTERSECT, EXCEPT operations JOIN ... ON ..., NATURAL JOIN, and CROSS JOIN operations VIEWs that can only use the above RA SQL features commas in the FROM clause are not allowed 4 1 Formulating queries in Pure SQL with and with- out set predicates An important consideration in formulating queries is to contemplate how they can be written in different, but equivalent, ways. In fact, this is an aspect of programming in general and, as can expected, is also true for SQL. A learning outcome of this course is to acquire skills for writing queries in different ways. One of the main motivations for this is to learn that different formulations of the same query can dramatically impact performance, sometimes by orders of magnitude. For the problems in this section, you will need to formulate queries in Pure SQL with and without set predicates. You can use the SQL op- erators INTERSECT, UNION, and EXCEPT, unless otherwise specified. You are however allowed and encouraged to use views including temporary and user-defined views. To illustrate what you need to do, consider the following example. Example 1 Consider the query “ Find the pid and name of each per- son who (a) works for a company located in Bloomington and (b) knows a person who lives in Chicago.” (a) Formulate this query in Pure SQL by only using the EXISTS or NOT EXISTS set predicates. You can not use the set operations INTERSECT, UNION, and EXCEPT. A possible solution is select p.pid, p.pname from Person p where exists (select 1 from worksFor w where p.pid = w.pid and exists (select 1 from companyLocation c where w.cname = c.cname and c.city = ’Bloomington’)) and exists (select from Knows k where p.pid = k.pid1 and exists (select 1 from Person p2 where k.pid2 = p2.pid and p2.city = ’Chicago’)); 5 (b) Formulate this query in Pure SQL by only using the IN, NOT IN, SOME, or ALL set membership predicates. You can not use the set operations INTERSECT, UNION, and EXCEPT. A possible solution is select p.pid, p.pname from Person p where p.pid in (select w.pid from worksFor w where w.cname in (select c.cname from companyLocation c where c.city = ’Bloomington’)) and p.pid in (select k.pid1 from Knows k where k.pid2 in (select p2.pid from Person p2 where p2.city = ’Chicago’)); Another possible solution using the SOME and IN predicates select p.pid, p.pname from Person p where p.pid = some (select w.pid from worksFor w where w.cname = some (select c.cname from companyLocation c where c.city = ’Bloomington’)) and p.pid in (select k.pid1 from Knows k where k.pid2 in (select p2.pid from Person p2 where p2.city = ’Chicago’)); (c) Formulate this query in Pure SQL without using set predicates. A possible solution is select p.pid, p.pname from Person p, worksFor w, companyLocation c where p.pid = w.pid and w.cname = c.cname and c.city = ’Bloomington’ intersect select p1.pid, p1.pname from Person p1, Knows k, Person p2 where k.pid1 = p1.pid and k.pid2 = p2.pid and p2.city = ’Chicago’; 6 We now turn to the problems for this section. 1. Consider the query “Find the pid and name of each person who works for Google and who has a strictly higher salary than some other person who he or she knows and who also works for Google.” (a) • Formulate this query in Pure SQL by only using the EXISTS or NOT EXISTS set predicates. (b) • Formulate this query in Pure SQL by only using the IN, NOT IN, SOME, or ALL set membership predicates. (c) • Formulate this query in Pure SQL without using set pred- icates. 2. Consider the query “Find the cname of each company with head- quarter in Cupertino, but that is not located in Indianapolis, along with the pid, name, and salary of each person who works for that company and who has the next-to-lowest salary at that company.”2 (a) • Formulate this query in Pure SQL by only using the EXISTS or NOT EXISTS set predicates. You can not use the set oper- ations INTERSECT, UNION, and EXCEPT. (b) • Formulate this query in Pure SQL by only using the IN, NOT IN, SOME, or ALL set membership predicates. You can not use the set operations INTERSECT, UNION, and EXCEPT. (c) • Formulate this query in Pure SQL without using set pred- icates. 3. Consider the query “Find each (c, p) pair where c is the cname of a company and p is the pid of a person who works for that company and who is known by all other persons who work for that company. (a) • Formulate this query in Pure SQL by only using the EXISTS or NOT EXISTS set predicates. You can not use the set oper- ations INTERSECT, UNION, and EXCEPT. 2By definition, a salary s is next-to-lowest if there exists salary s1 with s1 < s,="" but="" there="" do="" not="" exist="" two="" salaries="" s1="" and="" s2="" with="" s2="">< s1="">< s. 7 (b) • formulate this query in pure sql by only using the in, not in, some, or all set membership predicates. you can not use the set operations s.="" 7="" (b)="" •="" formulate="" this="" query="" in="" pure="" sql="" by="" only="" using="" the="" in,="" not="" in,="" some,="" or="" all="" set="" membership="" predicates.="" you="" can="" not="" use="" the="" set="">
Answered Same DaySep 22, 2021

Answer To: B561 Advanced Database Concepts Assignment 2 Fall 2021 Dirk Van Gucht This assignment relies on the...

Neha answered on Sep 23 2021
138 Votes
Question 1
a) select p.pid, p.pname from Person p where exists (select 1 from worksFor w where p.pid = w.pid and exists (s
elect 1 from Company where w.cname = c.cname and c.cname = ’Google’)) and exists (select from Knows k where p.pid = k.pid1 and exists (select 1 from Person p2 where k.pid2 = p2.pid and p2.cname = ’Google’));
b) select p.pid, p.pname from Person p where p.pid in (select w.pid from worksFor w where w.cname in (select c.cname from company c where c.cname = ’Google’)) and p.pid in (select k.pid1 from Knows k where k.pid2 in (select p2.pid from Person p2 where p2.cname = ’Google’));
c) select p.pid, p.pname from Person p, worksFor w, company c where p.pid = w.pid and w.cname = c.cname and c.cname = ’Google’ intersect select p1.pid, p1.pname from Person p1, Knows k, Person p2 where k.pid1 = p1.pid and k.pid2 = p2.pid and p2.cname = ’Google’;
Question 2
a) select p.pid, p.pname from Person p where exists (select salary from worksFor w where p.pid = w.pid and exists (select cname from Company where w.cname = c.cname and c.headquarter != ’ Indianapolis’)) ;
b) select p.pid, p.pname from Person p where p.pid in (select w.salary from worksFor w where w.cname in (select c.cname from company c where...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here