Consider the following four relations: EmpDept(empID,deptNo) empID is key EmpOff

Question

Consider the following four relations:
EmpDept(empID,deptNo) empID is key
EmpOffice(empID,office) empID is key
EmpPhone(empID,phone) empID is key
DeptMgr(deptNo,mgrID) deptNo is key

and the following view that joins all four relations:
CREATE VIEW Info as
(SELECT ED.empID, ED.deptNo, EO.office, EP.phone, DM.mgrID
FROM EmpDept as ED, EmpOffice as EO, EmpPhone as EP, DeptMgr as DM
WHERE ED.empID = EO.empID
AND EO.empID = EP.empID
AND ED.deptNo = DM.deptNo)

(a) Write a SQL query to find the offices of all employees whose department manager has ID 123. Do not use the view, and eliminate duplicate offices in your result.

(b) Write the same query using the view and not the base relations. Please eliminate duplicate offices in your result.

(c) Are the queries in parts (a) and (b) equivalent? Why?

Explanation / Answer

               Where DM.mgrID=123

                AND EO.empID = ED.empID

                AND ED.deptNo=DM.deptNo;

b) SQL> CREATE VIEW office Info as

             ( SELECT DISTINCT EO. Office

                FROM EmpOffice as EO, deptMgr as DM, EmpDept as ED

               Where DM.mgrID=123

               AND EO.empID = ED.empID

               AND ED.deptNo = DM.deptNo);

c) The above select and view statement statements returns the same result, that is it returns the office’s of all employees whose mgrID is 123, but the small difference is there between two statements. Select statement just select the selected columns whereas by using Views we can be granted different permissions, so that we can show only a portion of data to a user.By using views control the access permissions. View is more complicated than select statement.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.