1) What do you mean by database utilities? List a few common functions that the utilities perform.
2) What do you mean by a recursive relationship type? Provide some examples of recursive relationship types.
3) Consider a bank database taking care of accounts (saving, current, fixed deposit, flexi,....) and loans (house, auto, business,....). Assume it is essential to keep track of each account transaction (deposit, withdrawal, checks, ATMs,...) and loan payment. All transactions should include the amount, date, and time. Draw the bank schema and EER diagram. State any assumptions you make about additional requirements.
4) Describe the purpose of Boyce-Codd normal form and describe how BCNF differs from and is stronger than 3NF. Demonstrate your answer with suitable example.
5) Given below are two sets of FDs for a relation R (A, B, C, D, E). Are they equivalent?
i. A→B, AB→C, D→AC, D→E
ii. A→BC, D→AE
6) What types of constraints are inclusion dependencies meant to represent?
7) Discuss the entity integrity and referential integrity constraints. Why is each considered important?
8) For relations Employee(name, ssn, sex, salary, superssn, dno); Manager(mgrssn, mgrjoindt); deptloc(dno, dloc); proj(pno, pname, ploc, dno); works(ssn, pno, hrs); dept(dname, dno, mrgssn); dependent(essn, deptname, sex, relationship).
Write querries for the schema of figure in relational algebra.
i) Retrieve the names of all employees in department 5 who work more than 10 hours per week on the product X project.
ii) For each project, list the project name and the total hours per week (by all employees) spent on that project.
iii) Retrieve the names of all employees who do not work on any project.
iv) For each department, retrieve the department name and the average salary of all employees working in that department.