Question

Given the same simple Employee-Workson-Project database schema , which contains three files described as follows:

Emp (eid : integer, ename : string, age : integer, salary: real)

Workson (eid : integer, pid : integer, hours : integer)

Project (pid : integer, pname : string, budget : real, managerid : integer)

Note : eid, ename, age and salary are the employee id, name, age and salary respectively. Also, hours is the number of hours worked by employee on a project. The rest of the attributes pid, pname, budget and managerid are the project id, name, budget and managerid respectively. A manager is an employee.

Assume that an update is to be made to this database to enter information about a new project manager who is now the new manager of an existing project (replacing the old manager) and although he is new, he has worked some hours in another existing project. Answer the following questions on what specific relations, attributes and operations (eg. insert, modify, delete) that need to be done for this update to be implemented in the entire database.

Provide your answers both in descriptive sentences and using the formal database operations of INSERT, MODIFY, DELETE with specific attributes and relations when possible. An example formal insert of an employee record into the Emp table is:
INSERT < eid, ename, age, salary> into Emp; // for new Employee record

And an example descriptive sentence is:

i). do an insert operation for a new employee record into the Emp table.

(a) Give the set of needed insert, modify or delete operations for this update.

(b) What types of integrity constraints (explain using attributes, eg, eid of relevant files)) would you expect to check for this update to be done?

(c) Which of these integrity constraints are key, entity integrity, and referential (foreign key) integrity constraints and which are not?

(d) Specify all the referential integrity (foreign key) constraints on this database in the format Referring_Relation.Attribute --> Referred_Relation.Attribute

Answer 1

specified tables are with dummy values:

Emp:

eid	ename	age	salary
1	ram	25	10000
2	raju	26	20000
3	titu	27	15000

workson

eid	pid	hours
1	10	20
2	1	10
3	2	25

Project

pid	pname	budget	managerid
1	abc	10000	2
2	cde	20000	3

as per the given question:

We need to update new project manager for the existing project. So , Let's update project with pid=1

and managerid=2 to pid=1 and managerid=1

to do this following is the sql query:

update project set managerid=1 where pid=1;

resulting changes will be as follow: project table

pid	pname	budget	managerid
1	abc	10000	1
2	cde	20000	3

similar change is required in workson table since it holds number of hours of work done by emp. to do the same following is the sql querry:

update workson set pid=1 where eid=1;

=> update pid column in workson table where eid=1.

resultant table: workson table

eid	pid	hours
1	1	20
2	1	10
3	2	25

b) integrity constraints : in these given table eid from emp table will be the primary key and managerid will be the foreign key referring emp table in project table and this intigrity constraint will be checked for the update to be done.

c) entity integrity: is the mechanism provided to maintain the primary keys of the table. so in our case primary key is eid from emp table and pid from project table.

referential integrity: mechanism to maintain foreign keys of the table.In out example : managerid from project table and eid and pid from workson table are foreign keys

d) referential integrity (foreign key) constraints on this database :=

Project.managerid-->emp.eid

workson.pid-->project.pid

workson.eid--> emp.eid