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.

Answer 1

Given relations are:

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)

Employee table:

CREATE TABLE Employee (
eid int not null,
ename varchar(255),
job_title varchar(255),
years_of_experiance int,
primary key(eid),
  
);

project table:

CREATE TABLE Project (
pid int not null,
pname varchar(255),
parea varchar(255),
mid int,
budget int,
primary key(pid),
FOREIGN KEY (mid) REFERENCES Manager(mid)
);

CREATE TABLE Works_on (
eid int not null,
pid int not null,
FOREIGN KEY (eid) REFERENCES Employee(eid)
FOREIGN KEY (pid) REFERENCES projrct(pid)
  
);

CREATE TABLE Manager(
mid int not null,
mname varchar(255),
parea varchar(255),
deptid int,
primary key(mid),
  
);

2) select m.mname,p.count(pid) from manager m,Project p where m.mid=p.mid and project like '%Networking'.