Answer all the questions.
1) Explain file organization? Name all the file organization techniques. Make a detailed comparison among all the file organization techniques.
2) What are the functions related with role of the database administrator? How does data dictionary help a database administrator?
3) Define following terms.
(i) Inverted list.
(ii) Referential integrity.
(iii) Foreign key.
(iv) Transaction.
(v) Candidate key.
4) Compare and contrast the following.
(i) Primary indices and Secondary indices.
(ii) Centralized DBMS and Distributed DBMS.
(iii) B tree and B+ tree.
(iv) Data replication and data Fragmentation.
(v) Procedural and non procedural DMLs.
5) Describe normalization. Write the conditions under which relation requires to be normalized to 2NF and 3NF with the help of an example.
6) Project handling organization has persons identified by PER – ID and a LAST – NAME. Persons are assigned to departments identified by DEP – NAME. Persons work on projects and each project has a PROJ – ID and a PROJ – BUDGET. Each project is managed by one department and the department might manage many projects. But the person might work on only some (or none) of the projects in his or her departments. Identify entities and relationship for this organization and construct an E – R diagram.
7) Describe the role of hashing function in direct file organization.
8) Consider following tables in database:
Employee (Emp_No, skill, Pay_Rate)
Duty Allocation (Posting_No, Emp_No, day, shifts)
Write SQL statements for performing the following tasks:
(i) Retrieve shift details for employee named “Vijay”.
(ii) Get a count of different employees on each shift.