NOTE: There are 11 Questions in all.
Question
1 is compulsory and carries 16 marks. Answer to Q. 1. must be written in the
space provided for it in the answer book supplied and nowhere else.
Answer
any THREE Questions each from Part I and Part II. Each of these questions
carries 14 marks.
Any
required data not explicitly given, may be suitably assumed and stated.
Q.1 Choose
the correct or best alternative in the following: (2x8)
a.
The
property / properties of a database is / are :
(A) It is an integrated collection of
logically related records.
(B) It
consolidates separate files into a common pool of data records.
(C) Data stored in a database is
independent of the application programs using it.
(D) All of the above.
b.
The
DBMS language component which can be embedded in a program is
(A) The data definition language (DDL).
(B) The
data manipulation language (DML).
(C) The
database administrator (DBA).
(D) A query language.
c. A relational database developer refers to a
record as
(A) a criteria. (B) a relation.
(C) a tuple. (D)
an attribute.
d. The relational model feature is that there
(A) is no need for primary key
data.
(B) is much more data independence
than some other database models.
(C) are explicit relationships
among records.
(D) are tables with many dimensions.
e. Conceptual design
(A) is a documentation technique.
(B) needs data volume and
processing frequencies to determine the size of the database.
(C) involves modelling independent
of the DBMS.
(D) is designing the relational model.
f. The method in which records are physically stored in a specified order according to a key field in each record is
(A) hash. (B) direct.
(C) sequential. (D) all of the above.
g. A subschema expresses
(A) the logical view. (B) the physical view.
(C) the external view. (D) all of the above.
h. Count function in SQL returns the number of
(A) values. (B) distinct values.
(C) groups. (D) columns.
Answer
any THREE Questions. Each question carries 14 marks.
Q.2 a. Discuss the main advantages of using a DBMS. (7)
b. What are the different types of database end
users? Discuss the main activities of
each. (7)
Q.3 a. Describe the three-schema architecture. (7)
b. Discuss the
typical user friendly interfaces and the types of users who use each. (7)
Q.4 a. Differentiate between
(i) Procedural and non procedural languages.
(ii)
Key
and superkey.
(iii)
Primary
and secondary storage. (3
x 3)
b. With the help of an example show how records can be deleted and updated in QBE. (5)
Q.5 a. Describe
cardinality ratios and participation constraints for relationship types. (4)
b. Information about a bank is about customers and their account. Customer has a name, address which consists of house number, area and city, and one or more phone numbers. Account has number, type and balance. We need to record customers who own an account. Account can be held individually or jointly. An account cannot exist without a customer.
Arrive at an E-R diagram. Clearly indicate attributes, keys, the cardinality ratios and participation constraints. (10)
Q.6 a. Describe the static hash file with buckets and chaining and show how insertion, deletion and modification of a record can be performed. (9)
b. What are the reasons for having variable
length records? What types of separator
characters are needed for each? (5)
Answer
any THREE Questions. Each question carries 14 marks.
Q.7 Define the following terms
(i)
Derived
and stored attribute.
(ii)
Distributed
system.
(iii)
Interblock
gap.
(iv)
Degree
of a relation.
(v)
Catalog.
(vi)
Conceptual
schema.
(vii)
DDL
and SDL. (7
x 2 = 14)
Q.8 a. Define a relation. (2)
b. Describe entity integrity and referential
integrity. Give an example of each. (6)
c. Consider the two relations given below
R S
|
A |
B |
C |
|
|
|
|
|
a1 |
b1 |
c1 |
|
D |
A |
F |
|
null |
b2 |
null |
|
d1 |
a1 |
f1 |
|
a1 |
b1 |
c1 |
|
d1 |
a2 |
null |
Given that A is the primary key of R, D is the
primary key of S and there is a referential integrity between S.A and R.A,
discuss all integrity constraints that are violated. (6)
Q.9 Given the following relations
TRAIN
(NAME, START, DEST)
TICKET
(PNRNO., START, DEST, FARE)
PASSENGER (NAME, ADDRESS, PNRNO.)
Write SQL expressions for the following queries:
(i)
List
the names of passengers who are travelling from the start to the destination
station of the train.
(ii)
List
the names of passengers who have a return journey ticket.
(iii)
Insert
a new Shatabdi train from Delhi to Bangalore.
(iv)
Cancel
the ticket of Tintin. (3.5
x 4 = 14)
Q.10 a. Define
outer union operation of the relational algebra. Compute the outer union for the relations R ans S given below. (2+3)
R S
|
A |
B |
C |
|
D |
A |
F |
|
a1 |
b1 |
c1 |
|
d1 |
a1 |
f1 |
|
a3 |
b2 |
c2 |
|
d1 |
a2 |
null |
b. Given the
following relations
Vehicle
(Reg_no, make, colour)
Person(eno,
name, address)
Owner(eno, reg_no)
Write expressions in the relational algebra to
answer the following queries:-
(i)
List
the reg_no of vehicles owned by John.
(ii)
List
the names of persons who own maruti cars.
(iii)
List
all the red coloured vehicles. (3
x 3)