I need help with this database question 10.21 Answer the following questions bas
ID: 3738789 • Letter: I
Question
I need help with this database question
10.21 Answer the following questions based on the company logical database sehema EMPLOYEE Fname Minit Lname Bdate Address SexSa Super ssnDno DEPARTMENT Mgr san Mar start date DEPT LOCATIONS PROJECT WORKS ON ena Hours DEPENDENT name Sex Bdate Relationship (a) Explain primary key constraint, and show an example based on the company database (b) Explain entity integrity constraint, and show an example based on the company database schema (c) Explain referential integrity constraint, and show two examples based on the company database schema. Explain the following query in English (based on company database) SELECT FNAME, LNAME, SSN FROM EMPLOYEE WHERE NOT EXISTS (SELECT FROM WORKS ON B WHERE (B.PNO IN (SELECT PNUMBER FROM PROJECT WHERE DNUMBER = 5) AND NOT EXISTS (SELECT FROM WORKS ON C WHERE C.ESSN-SSN AND C.PNO B.PNO) Modify the query to list employees who work on any of the projects controlled by department number5 e)
Explanation / Answer
a)
Primary key constraint is used to uniquely identify tuples in a table. Here the attribute {ssn} in table EMPLOYEE is a primary key because no two employees have the same ssn.
b)
Entity integrity constraint states that no primary key value can be null. This is because primary key is used to uniquely identify tuples in a table. If primary key attribute is having null values, it is not possible to identify some tuples.
For example, the attribute {ssn} in table EMPLOYEE is a primary key. If some tuples have null values for ssn attribute, it is not possible to reference individual tuples.
c)
Referential integrity constraint is specified between two tables and is used to maintain consistency among tables. It states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation.
For example the attribute DNO of EMPLOYEE gives the department number for which each employee works. DNO is designated as foreign key of EMPLOYEE referring to the DEPARTMENT relation. This means that a value of DNO in any tuple t1 of EMPLOYEE relation must match a value of primary key of DEPARTMENT which is DNUMBER attribute in some tuple t2 of DEPARTMENT relation or the value of DNO can be null if the employee does not belong to a department.
Also essn of WORKS_On is a foreign key which refers to ssn attribute (primary key) of EMPLOYEE
d)
SELECT Lname, Fname FROM EMPLOYEE WHERE NOT EXISTS ( SELECT * FROM WORKS_ON B WHERE ( B.Pno IN ( SELECT Pnumber FROM PROJECT WHERE Dnum=5 ) AND NOT EXISTS ( SELECT * FROM WORKS_ON C WHERE C.Essn=Ssn AND C.Pno=B.Pno )));
This query retrieve the name of each employee who works on all the projects controlled by department number 5
e)
This query retrieve the name of each employee who works on any of the projects controlled by department number 5
SELECT LNAME, FNAME, SSN FROM EMPLOYEE WHERE EXISTS (SELECT * FROM WORKS_ON B WHERE (B.PNO IN (SELECT PNUMBER FROM PROJECT WHERE DNUM = 5) AND EXISTS (SELECT * FROM WORKS_ON C WHERE C.ESSN = SSN AND C.PNO = B.PNO))