Consider the COMPANY database as specified in Figure 3.6 and the FK ? PK relatio

Question

Consider the COMPANY database as specified in Figure 3.6 and the FK ? PK relationships as specified in Figure 3.7, write relational algebra expressions to get answers from the following queries and show the answers (all answers are in the form of a relation/table).

1) Who are the employees (Ssn) who is not a supervisor?

2) List the managers (Mgr_ssn) who is also a supervisor.

EMPLOYEE Fname | Minit-name | Ssn | Bdate | Address | Sex | Salary | Super-ssn-no DEPARTMENT Dname Dnumber rMgr start date DEPT LOCATIONS Dnumber Dlocation PROJECT Phame Pnumber Plocation Dnum WORKS ON Essn Pno Hours Figure 3.7 Referential integrity constraints displayed ontn schema. DEPENDENT Essn Dependent name SexBdate Relationshipon the COMPANY relational database Essn-1-Dependent-name | Sex Bdate L Relationship-j |

Explanation / Answer

SQL Query for Question 1:
SELECT E.Ssn FROM Employee E
WHERE E.Super_ssn<> E.Ssn;

Explanation: Ssn amd Super_ssn are the members of Employee table. where Super_ssn is not a ssn of employees

SQL Query for Question 2:

SELECT D.Mgr_ssn FROM Employee E, Dependent D
WHERE D.Super_ssn<> E.Super_ssn;

Explanation: This Query has need two tables because Mgr_ssn is present in Dependent relation
Super_ssn and Mgr_ssn is not equal will list the Managers .

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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