23F-A3 SO

COMP 3005 Assignment #3 Due: Oct. 19 @11:59PM Instruction 1. Do the assignments independently. Copying is not allowed. 2. The database for this assignment is the same as in Assignment #1. Do the assignment directly on this document and rename it with your last name + first name and submit to brightspace . Make sure your uploaded file can be opened. 3. You can use any version of Oracle VM or download and install latest version of Oracle from its official website. Part 1 Concepts (20 marks) Explain the following concepts based on the definitions given in the lecture notes. Different answers found online will be marked wrong. The explanation should be complete; i.e, it does not contain any concept not explained here. Each concept is 2 marks. 1. Type compatibility: two relations are type compatible if a) they have the same number of attributes, b) each pair of corresponding attributes have the same or compatible domains 2. Relational complete: if a query language is at least as powerful as the relational calculus. 3. Basic relational operators: select, project, union, minus, rename, and times 4. Free variable: a variable in tuple/domain calculus that is not quantified by exists or forall 5. Bound variable: a variable in tuple/domain calculus that is quantified by exists or forall 6. Grouping: it is a way to divide a relation into groups based on some criteria 7. Aggregate functions: functions used on groups of a relation. 8. Use relational algebra to generate R1(S#, C#) divideby R2 (S#): T1:= project S#(R1); T2:= T1 times R2; T3:=T2 minus T; T1 minus T3 Part 2 (80 marks)

Given the employees and projects databases the same as in Assignment #1. Use Query By Example (QBE) to express queries 1,2,3,4,5,6 and use SQL to express all 10 queries. (80) Each QBE query is 5 marks. Each SQL query is 4 marks and the result is 1 mark . Screenshot of both SQL query and running results are needed in order to get the 5 marks. 1. Get the age of Last . QBE Employees SQL> SELECT Age FROM Employees WHERE Ename = 'Last'; 2. Get the name of Last’s manager QBE: Employees E# Name Age Manager “Last” _M Employees E# Name Age Manager _M P. SQL> SELECT M.Name FROM Employees E, Employees M WHERE E.Name = 'Last' AND E.Manager = M.E#; Employees E# Name Age Manager E1 Adam s 50 E2 Blake 40 E1 E3 Clark 35 E1 E4 David 30 E3 E5 Emily 25 E4 E6 Last 20 E5 Workon E# P# Hours E1 P1 700 E2 P1 300 E2 P2 200 E3 P1 100 E3 P2 200 E3 P3 300 E4 P1 100 E4 P2 200 E4 P3 300 E6 P1 200 E6 P2 300 E6 P3 400 E6 P4 500 Projects P# Name Location P1 CPU B1 P2 GPU B2 P3 GPU B2 P4 SSD B3 E# Name Age Manager Last P.

WHERE E.E=W.E AND W.P=P.P AND W.Hours < 300 6. Get the name of the employee who works on every project Employees E# Name Age Manager _E P. Worksons Projects SQL> SELECT Ename FROM Employees E WHERE NOT EXISTS (SELECT * FROM Projects P WHERE NOT EXISTS (SELECT * FROM Workon W WHERE E.E =W.E AND W.P = P.P)); 7. Get the name of the employee who works on every project except SSD. SQL> SELECT Ename FROM Employees E WHERE NOT EXISTS (SELECT * FROM Projects P WHERE (Pname != 'SSD'OR EXISTS (SELECT * FROM Worksons W WHERE E.E=W.E AND P.P=W.P)) AND (Pname = ‘SSD’ OR NOT EXISTS (SELECT * FROM Worksons W WHERE E.E=W.E AND P.P=W.P)); 8. Get the name of the employee who works on every project that Clark works on. SQL> SELECT E1.Ename FROM Employees E1, Employees E WHERE E1.Ename != ‘Clark’ AND E.Ename = ‘Clark’ AND NOT EXISTS ( SELECT W.P FROM Worsons W WHERE E.E=W.E MINUS SELECT W.P FROM Worksons W WHERE E1.E=W.E); 9. Get the name of the employee who works on the same projects that Clark works on. SQL> SELECT E1.Ename FROM Employees E1, Employees E WHERE E1.Ename != ‘Clark’ AND E.Ename = ‘Clark’ AND NOT EXISTS ( SELECT W.P FROM Worsons W WHERE E.E=W.E MINUS SELECT W.P FROM Worksons W WHERE E1.E=W.E) AND NOT EXISTS (

SELECT W.P FROM Worsons W WHERE E1.E=W.E MINUS SELECT W.P FROM Worksons W WHERE E.E=W.E); 10. Get the name of the employee who works on more than two projects. SQL> SELECT Ename FROM Employees WHERE E IN ( SELECT E FROM Workon GROUP BY E HAVING COUNT(P) > 2);