Consider the following relational schemas: Employee (eid: integer, ename: string

Question

Consider the following relational schemas: Employee (eid: integer, ename: string, Job_title: string, Years_of_experience: integer) Project (pid: integer, pname: string, parea: string, mid: integer, budget: integer) Works_on (eid: integer, pid: integer) Manger (mid: integer, mname: string, deptid: integer) The meaning of these relations is straightforward; for example, Works_on has one record per Employee-Project pair such that the Employee Works_on the Project. 1. Write the SQL statements required to create these relations, including appropriate versions of all primary and foreign key integrity constraints. 2. Write the following query in SQL. For each Manger that supervise only ‘Networking’ projects, print the Manger member’s name and the total number of Projects she or he has supervised. 3. Express each of the following integrity constraints in SQL unless it is implied by the primary and foreign key constraint; if so, explain how it is implied. If the constraint cannot be expressed in SQL, say so. For each constraint, state what operations (inserts, deletes, and updates on specific relations) must be monitored to enforce the constraint. (a) Every Project has a minimum enrollment of 5 Employees and a maximum enrollment of 30 Employees.(b) Every Manger must supervise at least two projects. (c) Only employees with at least 3 Years_of_experience can work in more than 3 projects.

Explanation / Answer

1)

create table Employee(eid int , ename varchar2(30), job_title varchar2(30), years_of_experience int,primary key(eid));

create table Project(pid int , pname varchar2(30), parea varchar2(30), mid int references Employee(eid), budget int,primay key(pid));

create table Works_on(eid int references Employee(eid), pid int references Project(pid), primary key(eid,pid));

create table Manager (mid int references Employee(eid), mname varchar2(30), deptid int,primary key(mid));

2)

select count(Project.pid),mname where mid in ( select mid from Project where pname like '%Networking%');

3)

a) the insert, delete statement on works_on relation must be monitored, the number of employees enrolled for a paticular project should be between 5 and 30.
b) insert,update and delete statement for mid in Porject relation must be monitored, for each entry for mid there must be atleast two pid's
c) each insert statement on works_on relation must be monitored with reference to Employee table, if the count of same eid in works_on relation is more that 3 then it must be checked whether this particualr eid has Employee.years_of_experience > 3 or not.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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