Note: Question number 1 is compulsory. Answer any three questions from the rest.
Q.1. (a) Design an ER diagram for an IT training group database that
will meet the information needs for its training program. Clearly
indicate the entities, relationships and the key constraints. The
description of the environment is as follows :
The company has 12 instructors and can handle upto 100 trainees for
each training session. The company offers 5 Advanced technology courses,
each of which is taught by a team of 2 or more instructors Each instructor
is assigned to a maximum of two teaching teams or may be assigned
to do research Each trainee undertakes one Advanced technology course
per training session.
(b) Examine the table shown below :
| Staff No. | Branch No. | Branch Address | Name | Position | Hrs/Week |
| E101 | B02 | Sun Plaza, Delhi, 110001 | Ram | Assistant | 16 |
| E101 | B04 | 2/3 UT, Delhi, 110111 | Ram | Assistant | 9 |
| E122 | B02 | Sun Plaza, Delhi, 110001 | Mohan | Assistant | 14 |
| E122 | B04 | 2/3 UT, Delhi, 110111 | Mohan | Assistant | 10 |
(i)Why is the table above not in 2NF ?
(ii) Describe the process of normalizing the data shown in the table
above to third normal form (3NF).
(iii) Identify the primary and foreign keys in your 3NF relations.
(c) With the help of an exampte, explain ihe Direct file organization method.
(d) Explain the following integrity rules of a relational model with
the help of an example :
(i) Entity Integrity
(ii) Referential Integrity
(e) Explain how the "GROUP By" clause works. What is the difference between the WHERE and HAVING clauses ? Explain them with the help of an example for each.
Q.2. (a) Consider the following relations :
Employee (emplD, FirstName, LastName, address, DOB, sex, position,
deptNo)
Department (dtptNo, deptName, mgr, empID)
Project (projNo, projName, deptNo)
Work on (empID, projNo, hours worked)
Write the SQL statements for the following :
(i) List the name and addresses of all employees who work for the
IT department.
(ii) List the total hours worked by each employee, arranged in order
of department number and within department, alphabetically by employee
surname.
(iii) List the total number of employees in each department for those
departments with more than 10 employees.
(iv) List the project number, project name and the number of employees
who work on that project.
(b) What is a database model ? Explain any two types of data models with an example for each.
Q.3. (a) What is the need for evaluation of a DBMS ? List the technical criteria that are to be considered during the evaluation process.
(b) What are the main features of the Object Oriented Database Management System ? How is this advantageous over RDBMS ?
(c) What is a knowledge-base system? With the help of an example for each, explain how it is different from a conventional DBMS.
Q.4. (a) Explain the issues that are to be addressed for a distributed database design. Explain how these issues apply to the global system catalogue.
(b) Why is a B+ tree a better structure than a B-tree for implementation of an indexed sequential file ? Explain this with an example.
(c) With the help of an example, explain the concept of multilist file organization.
Q.5. (a) Given the relational schemes :
ENROL (S#, C#, Section) - S# represents student number
TEACH (Prof, C#, Section) - C# represents course number
ADVISE (Prof, S#) - Prof is thesis advisor of S#
PRE_REQ (C#, Pre_C#) - Pre_C# is prerequisite course
GRADES (S#, C#, Grade, Year)
STUDENT (S#, Sname) - Sname is student name