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

Question

Emp(ed integer, ename: string, age: integer, salary: real) Worksin (eid: integer, did : integer, hours: integer) Dept (did: integer, doame: 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 in department. The rest of the attributes did, dname, budget and managerid are the department id, name, budget and managerid respectively. A manager is an employee. Assume that an update for the Employee-Worksin-Dept database to enter information about a new employee who is now the manager of an existing department (replacing the old manager) and has worked some hours in another existing department is made. Provide your answers both in descriptive sentence and using the formal database operations of INSERT, MODIFY, DELETE with specific attributes and relations when possible. (a) Give the operations for this update. 5 marks (b) What types of constraints would you expect to check? 5 marks (c) Which of these constraints are key, entity integrity, and referential integrity constraints and which are not? 5 marks (d) Specify all the referential integrity constraints on this database. 5 marks

Explanation / Answer

a) First of all we will insert the new employee in the Emp table.

SQL > INSERT into EMP values (1023,'John',25,1500.00);

Now we assume that John (eid:1023) who has previosly workied in Department Sales (DID: 15) for 10 hours will be replacing say Steve (eid : 1011) who is the manager of Department Accounts (DID:11) . Thes we will have to the following SQL operations.

1. Insert in the Worksin table the number of hours(10) John(1023) has worked in Sales(15) Department.

SQL > Insert into Worksin values (1023,15,10)

2. Update the Department table to remove Steve and update john as Manager

SQL > Update Dept set managerid = 1023 where DID = 11;

----------------------------------------------------------------------------------------------------------------------------

b) Constraints to check :

Unique Key Constraint : Employee ID and Department ID must be having Unique Key Constraint so that each employee is having unique emplyee ID and all the Departments are having unique Department ID.

--------------------------------------------------------------------------------------------------------------------------

c) Employee ID and Department ID should be Key Constraint. As the should be unique and not null.

Employee ID must be Entity intigrity for Employee table and Department ID must be entity intigrity for Department table.

Employee ID should be refrential intgity Employee and Worksin table and Between Employee and Department table.

Depatment ID must be refrential intigrity for Department and worksin table.

---------------------------------------------------------------------------------------------------------------------

D)

Employee ID should be refrential intgity Employee and Worksin table and Between Employee and Department table.

Depatment ID must be refrential intigrity for Department and worksin table.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

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