Reference no: EM13703117

Design of Database Systems

1. Create the database for following relational schema with Employee, Department, and Works tables:

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

Works(eid: integer, did: integer, hours: integer)

Department(did: integer, dname: string, budget: real, managerid: integer)

2. Define a table constraint on Department that will ensure that managerid is always positive and less than 1000.

3. Insert the following records in the database:

Employee

eld	ename	age	salary
101	Jones	23	60000
105	Adams	44	55000
110	Rivera		43000
120	Tanaka		60000
122	Lee	57	45000

Works

eld	did	hours
101	10	15
105	10	20
101	12	25
122	20	20
122	22	20
105	20	10
120	12	40
105	12	10

Department

did	dname	budget	managerid
10	Accounting	250000	234
12	Research	100000	453
20	Sales		678
22	IT	400000	324

4. Get the sorted list of name and budget of each department in which employee Jones works.

5. Get the name and id of youngest employee in Research department.

6. Create a view that shows the eids and managerids in Sales department.

7. Add a column called numHours to the Employee table. Use the UPDATE command to insert values into this new column to correspond to the current information in the Works table.

8. Write a trigger that will increment the new field numHours in the Employee table automatically whenever a record is inserted in the Works table.

9. Write a trigger that will decrement the new field numHours in the Employee table automatically whenever a record is deleted from the Works table.