Q.1. (a) Draw an E R diagram for inventory management systems by selecting suitable entity types, attributes and their relationship. Also give the relationship cardinality and participation constraints.
(b) Consider the following relational schema:
EMPLOYEE (Fnarne. Mname. Lname, SSN, Bdate. Address, Sex, Salary,
SuperSSN, DNO)
DEPARTMENT (Dname, DNO, MgrSSN, MgrStartDate)
PROJECT (PNO, PNAME, PLOCATION, DNO)
DEPENDENT (ESSN. DEPENDENT_NAME, SEX, BDATE. RELAT1ONSHIP)
Make necessary assumptions, if any. Formulate SQL queries for
the following:
(i) Retrieve the name and address of all employees who work
for the Computer Science and Engineering’ Department.
(ii) Retrieve the name of the manager of each department.
(iii) Retrieve, for each female employee, a list of the names
of her dependents.
(iv) Retrieve the first name, last name, and salary of all empioyees
who work on Project number 10.
(v) Retrieve the SSNs of all employees who either work in Department
Number 2 or supervise an employee who works in Department Number
2.
(c) What are the insertion, deletion and update anomalies that occur in a database ? Explain the mechanism to remove these anomalies from tables, with the help of an example.
(d) Explain the 2-phase locking protocol with the help of an example. What are the disadvantages of basic 2-phase locking ? How can these disadvantages be overcome ?
(e) Explain the indexed sequential file organisation. What are its advantages over sequential organisation?
Q.2. (a) Consider a relation R(A, B, C, D, E) and the set of FD’s: AB->C, C->D, D->A, BD->E.
(i) List the candidate keys for the relation R.
(ii) In what normal form is the relation presently, and why?
(iii) Normalise the relation till BCNF.
(b) Explain five advantages and five disadvantages of a distributed database system.
Q.3. (a) Explain the various components of a DBMS, with the help of a suitable diagram.
(b) (i) What is a transaction ? Explain this help of a transaction
psendocode.
(ii) Explain the properties of transactions.
(iii) Explain the various states through which a transaction
passes during execution.
Q.4. (a) (i) What are sparse and dense indices ? Give one example
of each.
(ii) Can we use Binary Search tree as an index ? Justify your
answer.
(iii) Which data structure is most suitable for implementing
indices ? Justify your answer.
(b) For each of the following, write suitable SQL commands and illustrate them through an example:
(i) Creation of views
(ii) Creation of a sequence
(iii) Outer join
(iv) To give access permission to a user