130 likes | 251 Views
Relational Model. Lecture Notes. COP 4720 Lecture 9. Outline of Lecture. Relational Algebra Queries Sec. 3.2. Summary of Relational Algebra (1). E ::= R | SELECT C (R) or c (R) | PROJECT A 1 ,A 2 ,...,A n (R) or A 1 ,A 2 ,...,A n (R) | E1 X E2 | E1 U E2 | E1 - E2
E N D
Relational Model Lecture Notes COP 4720 Lecture 9
Outline of Lecture • Relational Algebra • Queries • Sec. 3.2 COP 4720
Summary of Relational Algebra (1) E ::= R | SELECTC(R) orc(R) | PROJECTA1,A2,...,An(R) orA1,A2,...,An(R) | E1 X E2 | E1 U E2 | E1 - E2 | RENAMER(A1,A2,...,Am)(E) or R(A1,A2,...,Am)(E) COP 4720
Summary of Relational Algebra (2) E ::= R | E1 JOIN E2 or E1 E2 | E1 JOINC E2 or E1 C E2 | E1 INTERSECT E2 or E1 E2 COP 4720
Sample Schema for Exercises Student(ID, name, address, GPA, SAT) Campus(location, enrollment, rank) Apply(ID, location, date, major, decision) COP 4720
Sample Queries PROJECTdate,decision(Apply) Campus X Apply COP 4720
Sample Queries name, address (GPA>3.7 decision=‘No’ major=‘CS’ (Student Student.ID=Apply.ID Apply)) name, address ((Students) Students.ID= Not_Apply.ID (Not_Apply(ID (Students) - ID (Apply)))) Find Names and addresses of all students with GPA > 3.7 who applied to CS major and were rejected. List name and address of all students who didn’t apply anywhere. COP 4720
Schema for examples Employee(fname,lname,ssn,bdate,address,sex,salary,superssn,dno) Department(dname, dnumber,mgrssn,mgrstartdate) Dept_Locations(dnumber, dlocation) Project(pname,pnumber,plocation,dnum) Works_on(essn,pno,hours) Dependent(essn,dependent_name,sex,bdate,relationship) COP 4720
Examples • Retrieve the name and address of all employees who work for the • ‘Research’ department. research dname = ‘Research’(Department) research_emps research dnumber = dno(Employee) Result fname,lname,address(research_emps) or research Selectdname = ‘Research’(Department) research_emps research joindnumber=dno Employee result projectfname,lname,address(research_emps) COP 4720
Examples(refer to schema on previous slide) Option 1: Fname,Lname (Employee SSN=ESSN (Pname=‘Project X’ hours>10 Dnum=5 (Project Pnumber=Pno Works_On) ) ) Option 2: Emp_Work_X Pname=‘Project X’ (Project Pnumber=Pno Works_On) Emp-Work_10 Employee SSN=ESSN (hours>10 (Emp_Work_X) ) Result Fname,Lname (Dnum=5(Emp_Work_10) ) b) Retrieve the names of all employees in dept. 5 who work more than 10 hrs./week on project X. COP 4720
Same (Employee) Fname=Dependent_Name(Dependent) Result Fname,Lname(Same) ??????? c) List names of employees who have a dependent with the same first name. COP 4720
Same (Employee) Fname=Dependent_Name ssn=essn(Dependent) Result Fname,Lname(Same) c) List names of employees who have a dependent with the same first name. COP 4720
d) Retrieve the names of employees who work on every project. Emp_proj Project essn,pno (Works_on) T1 Projectessn (Emp_proj) T2 Project pno (Works_on) T3 T2 x T1 T4 T3 – Emp_proj T5 Project essn T4 Ans T1 – T5 COP 4720