MCA IInd semester:CS06 : Database Management System

Upto December 2004 OLD SYLLABUS

CS06 Database Management System

 

Time: 3 hours
Maximum Marks: 75
Note : Question No. 1 is compulsory. Answer any three questions from the rest.

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 : (7)
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 Branch Branch Address Name Position Hrs/Week
No. No.
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. (8)

(c) With the help of an exampte, explain ihe Direct file organization method. (6)

(d) Explain the following integrity rules of a relational model with the help of an example : (4)
(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. (5)

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. (7)

3. (a) What is the need for evaluation of a DBMS ? List the technical criteria that are to be considered during the evaluation process. (4)

(b) What are the main features of the Object Oriented Database Management System ? How is this advantageous over RDBMS ? (4)

(c) What is a knowledge-base system? With the help of an example for each, explain how it is different from a conventional DBMS. (7)

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. (5)

(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. (5)

(c) With the help of an example, explain the concept of multilist file organization. (5)

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

Give queries expressed in relational algebra, tuple calculus and domain calculus for the following :
(i) List all students taking courses with Zeba.
(ii) List all students taking at least one course that their advisor teaches.
(iii) List those professors who teach more than one section of the same course. (9)

(b) What are the design goals of a good relational database design ? Is it always possible to achieve these goals ? If some of these goals are not achievable, what alternate goals should you aim for and why ? (6)

Leave a Reply

You can use these XHTML tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>