Databases and database users

1. Given the simple Employee-Worksin-Dept database schema that contains three files described as follows, answer the following questions with regards to this database. (Total for que 1 is 10 marks)
Emp (eid : integer, ename : string, age : integer, salary: real)
Worksin (eid : integer, did : integer, hours : integer)
Dept (did : integer, dname : 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.
i) Create a valid instance of this database containing values for its records with at least four records in each file.

Answer:

An instance of the Employee-Worksin-Dept database is :
Emp
eid   ename     age   salary
10  Jobe Bata   25    50000
20 Monica Kap   29    55000
30 Peter Good   22    45100
40 Kate Lee     47    20000
50 Ted Tam      50    70000
Worksin
eid  did hours
10    1   40
20    1   40
30    2   30
40    3   20
50    4   30
50    3   10

Dept
did   dname           budget   managerid
1     Sales           200000     10
2   Research          100000     10
3   Payroll           110000     20
4   Customer service  60000      20

Files involved:
Emp
Workin
Dept

ii) Provide 2 informal English queries from this database with their answers. Each query should involve at least 2 of the files in the database and your answer should indicate the files (e.g., Emp, Worksin) needed to answer each query and specify what fields are being retrieved as the result (e.g., ename, age). Please, provide your solution in the 3 column table below.

Answer:

Result of query
i.
ename       salary
Jobe Bata   50000
Monica Kap  55000
ii.
managerid    ename     sum(budget)
10         Jobe Bata    300000
20         Monica Kap   170000

Files involved
Emp
Worksin
Dept

Emp
Dept

iii) Specify at least 3 relationships (one for each of the 3 database files) among the records of the database. For each file (e.g., Emp) list any relationships it has with other files through its fields (e.g., eid). Provide your solution using the table below.

Answer:

Each EMP record is related to one Works_in record through the field eid.

Each WORKs_IN record is related to one EMP record through eid and one DEPT record through did.

Each DEPT record is related to several WORKS_IN records through the field did.

Files involved

Emp,
Worksin

Worksin,
Emp.

Dept,
Worksin

2-Recall that a database has many types of users, each of whom may require a different view of the database. For example, one user of the Employee-Worksin-dept database of question 1 may be accessing and printing the details and salaries of each employee frequently and
4
thus a view for this user is created. Another view for this database is checking that department has available budget before expenditure such as paying salaries. (Total for que 2 is 10 marks)
i) Using this Employee-Worksin-dept database, give 2 additional views that may be needed by other user groups for the database.

Answer:

(a) A view that groups all the employees working in each department.
(b) A view that gives the total salary paid by each department.

ii) Give 5 examples of integrity constraints that you think can apply to the Employee-Worksin-dept database of question 1.

Answer:

(a) The eid should be unique for each EMPLOYEE record (key constraint).
(b) The did should be unique for each DEPT record (key constraint).
(c) A value of eid in a WORKSIN record must also exist in EMP record (referential integrity constraint).
(d) A value of did in a WORKSIN record must also exist in DEPT record (referential integrity constraint).
(e) The value of did in a WORKSIN record must be one of the values in the set
{1, 2, 3, 4} (domain constraint).
(f) Every record in EMP must have a value for eid (entity integrity constraint).
(g) A DEPT cannot have the total salaries of its employees exceeding the assigned departmental budget (general semantic integrity constraint).