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.