Gujarat University
MCA Semester II
Data base Management System –I
Date: 22nd July, 1999 Marks:50
1 Do as directed:
( a ) What do you understand by the term ‘Relational System ‘ ? Distinguish between relational and non-relational systems. 3
(b) List the major functions performed by DBA. 2
(c) Explain non-loss decomposition with example . 4.
2 (a) What is Data Dictionary? what does it contain when & why is it referred?. 4
(b) Explain the referential integrity with example, what are the impacts of it on insert, 'delete and update
operations. 4
OR
2 (a) What are the concurrency problems in database systems? Explain the various solutions to these
problems. 6
(b) Define. The primary key in terms of function dependency. Give example. 2
3 Consider the following database of blood-bank : 8
Donner ( Donner-ld, Name, Blood-group, City)
Collection (Collection-Center-ld, Collection-date, Bag-no, Donner-ld)
Issue( Bag-no, Issue-Date, Hospital_id)
Hospital (Hospital- Id, Name, City)
Solve the following queries in SQL {any six)
(i) List the donners with details who donated on 1-1-1997.
(ii) List hospitals with details which have not taken blood bags so far.
{iii) List the hospitals, blood group wise.
(iv) List the details of donners who have donated blood more than 10 times.
(v). List the collection centres where more than 100 bags are collected.
(vi) List the name of hospitals to whom the blood of donner "Mr. Ramakant'* is issued.
(vii) List pairs of Donner, hospital names which are in same city and that hospital has been issued blood
of that donner.
(ix) List the details of blood donations made by "Mr Paresh" in the order of date.
(x) List the current stock of blood bags
SECTION II
4. A travel agency is providing following services to its regular customers through the no. of branches
located in various cities of the state t Hotel booking and travel reservation. This agency has been
authorised to make the booking In various hotels at different places as well as reserve .the tickets in
airlines/railways The management of travel agency wants to computerize the above information. Draw
the E.R. diagram mentioning Entities, attributes & relations . 9
5. The following table describes the projects on which groups of employees are working
PROJECT-EMPLOYEE-TABLE (Project-Id, Emp-ld, Project-desc, Project -leader, Date-of-start. Period, Emp-Name, Experience-in-years, Date-of-Employee-Joined-project.)
Identify all the FD’s and normalise above relation into set of 3 mF. relations.
OR
5 (a) What is two-phase commit ? How does it help in recovery? 4
(b) What are views? Show its usefulness in DBMS by examples. 4
6 Write short notes on (any 3) - 8
(i) Optimization
(ii) Problems in Distributed DBMS
(iii) Problems of Null Values in database
(iv) Compare hierarchical, Network & Relational Models
(v) Relational Algebra operations
