10.21 Answer the following questions based on the company logical database sehem

Question

10.21 Answer the following questions based on the company logical database sehema EMPLOYEE Fname | Minit Lname Bdate Address Sex SalarySupersan Dno DEPARTMENT Mgr san Mar start date DEPT LOCATIONS PROJECT Dnum WORKS ON Hours DEPENDENT ent came SexBdate Relationship (a) Explain primary key constraint, and show an example based on the company database schema (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 ONB 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) )Modity the query to list employees who work on any of the projects controlled by department number5

Explanation / Answer

a) The primary key constraint says that the primary key of each table should uniquely identify each tuple/record.The primary key should be UNIQUE and it cannot contain NULL values.Each table will consist of a single primary key which can be made up of more than one attribute.

In the company logical database:

Table EMPLOYEE has the primary key Ssn.

Table DEPARTMENT has the primary key Dnumber.

Table DEPT_LOCATIONS has the primary key Dnumber,Dlocation.

Table PROJECT has the primary key Pnumber,Plocation.

Table WORKS_ON has the primary key Essn,Pno.

Table DEPENDENT has the primary key Essn,Dependent_name.

All these primary keys should be unique and shouldn't contain null value.

b)Entity Integrity constraint simply states that primary key of each table shouldn't contain NULL values.

For an example the Ssn attribute of EMPLOYEE table cannot contain NULL value.

c)

The referential Integrity constraint is nothing but that it is used to specify interdependencies between relations/tables. This constraint specifies a column or list of columns(more that one column together) as a foreign key of the referencing table.A foreign key means the values in one table must also appear in another table which is being referenced. The foreign key in the child table(referencing table) will generally reference a primary key in the parent table(referenced table).

In the company logical database schema:

The Dno attribute in the EMPLOYEE table is referencing the Dnumber attribute in the DEPARTMENT table.

The Pno attribute in WORKS_ON table is referencing Pnumber attribute in PROJECT table and the Essn attribute in the WORKS_ON table is referencing Ssn attribute in the EMPLOYEE table.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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