I CS-15I

MCA (III Year) Term-End Examination December, 2009 CS-15 : RELATIONAL DATABASE MANAGEMENT SYSTEM

CT)

Lc) C\J

O Maximum Marks : 75

Time : 3 hours

Note : Question number 1 is compulsory. Answer any three questions from the rest.

1.

(a) Design an ER diagram for managing a study 10 centre of IGNOU. The activities include student information, programmes on offer, sale of prospectus, Assignments handling, conduct of theory and practical sessions and Academic counsellors' information. Identify the entities, relationships and also mention the cardinality in the ER/EER Diagram. (b) Suppose that we decompose the schema R = (A,B,C,D,E) into : (A,B,C,) (A,D,E)

CS-15



1

P.T.O.

Show that this decomposition is a lossless decomposition if the following set F of functional dependencies holds : A --> BC CD --> E BAD D E —> A (c) Consider the following relations P and Q 21/2)(2=5

P: EID 101 103 104 107 110 112

NAME 1 Jones Smith Lalonde Evan Drew Smith

EID 103 104 106 110

NAME 2 Smith Lalonde Byron Drew

Perform the following operation on the relation P and Q Cartesian Product Projection of the Cartesian product on the attribute Name 1.

CS-15

(d) Given the relational schemes.

5x2=10

ENROLL (S.No., Course-No, Section) TEACH (Prof-name, Course-No, Section) ADVISE (Prof-name, S.No.) PRE-REQ (Course-No, Pre-Course-No) GRADES (S.No., Course-No, Grade, Year) STUDENT (S.No. S.Name) Write the SQL queries for the following : List all students taking courses with Smith or Jones. List all students taking atleast one course that their advisor teaches. List all the students who are enrolled for course-no "101". List the name of the students enrolled in the prerequisite course for courseNo. '105'. (v)

2.

List all the professors who teach course-no "109".

(a) Describe briefly the following with respect to the checkpoints and logs : 5x2=10 Write - ahead log strategy Transaction - consistent checkpoint Action - consistent check point Transaction Oriented check point (v)

CS-15

Two - phase commit 3

(b)

3.

Explain the differences between the file - oriented system and the database - oriented systems.

5

What do you mean by serializability of schedules ? Explain with example. How it is different serial schedules.

5

Find a BCNF decomposition of the following relation scheme (Faculty, Dean, Department, Chair person, Professor, Rank, Student). The relation satisfies the following functional dependencies (and any other that are logically implied by these) :

10

Faculty -+ Dean Dean --> Faculty Department —> Chair person Professor

Rank, Chair person

Department —> Faculty Student —> Department Faculty Dean Professor Rank —> Department Faculty Give proper explanation for each decomposition : 4.

(a)

CS-15



With the help of an example, explain the Authorization Grant tree and its scope. 4

5

P.T.O.

What are Statistical Databases ? What is its main objective ? With the help of relevant examples, explain the uses of them. Explain the role of join - index with the help

5

5

of an example. How is it different from Hash - Index ?

5.

Explain the following with the help of suitable diagram/example if needed. 5x3=15 Database Integrity Distributed Locking Tree - Locking Protocol Buffer Management (e) Project Join Normal Form

-o0o-

CS-15

5