DipIETE – CS (NEW SCHEME) – Code: DC62
Subject: DATABASE MANAGEMENT SYSTEMS
Time: 3 Hours
Max. Marks: 100
NOTE: There are 9 Questions in all.
· Question 1 is compulsory and
carries 20 marks. Answer to Q. 1 must be written in the space provided for it
in the answer book supplied and nowhere else.
· Out of the remaining EIGHT
Questions, answer any FIVE Questions. Each question carries 16 marks.
· Any required data not
explicitly given, may be suitably assumed and stated.
Q.1 Choose
the correct or the best alternative in the following: (2
10)
a. In an E-R diagram ellipses represent
(A) entity sets (B) relationship among entity sets
(C) attributes (D) link between attributes and entity sets
b. Redundancy is dangerous as it is a potential threat
to data
(A) integrity (B) consistency
(C) sufficiency (D) both (A) & (B)
c. An attribute of one table matching the primary
key of another table is called a
(A) foreign key (B) secondary key
(C) candidate key (D) composite key
d. The column of a
table is referred to as the
(A) tuple (B) attribute
(C) entity (D) degree
e. If every
non-key attribute is functionally dependent on the primary key, then the
relation will be in
(A) first normal form (B) second normal form
(C) third normal form (D) fourth normal form
f. A data model
is a collection of conceptual tools for describing
(A) data and data relationships
(B) data semantics and
consistency constraints
(C) data, data relationship, data
semantics and consistency constraints
(D) none of the above
g. The database administrator
is, in effect, the coordinator between the_______ and the________
(A) DBMS; database (B) Application program; database
(C) Database; users (D) Application programs; users
h. A trigger is
(A) a statement that enables to
start any DBMS.
(B) a statement that is executed
by the user when debugging an application program.
(C) a condition the system tests for
the validity of the database user.
(D) a statement that is executed
automatically by the system as a side effect of a modification to the database.
i. Data items
grouped together for storage purpose is called a
(A) record (B) title
(C) list (D) string
j. The SQL
expression
Select distinct T.branch_name from branch T, branch
S
Where T.assets > S.assets and S.branch_city = “
finds the names of
(A) all branches that have greater assets
than any branch located in
(B) all branches that have greater assets than all branches located
in
(C) the branch that has the greatest
asset in
(D) any branch that has greater
asset than any branch located in
Answer any FIVE Questions out
of EIGHT Questions.
Each question carries 16
marks.
Q.2 a. Explain the
difference between conceptual and external schema. (5)
b. What are the different ways of classifying a DBMS? (5)
c. List significant differences between a file-processing
system and a DBMS. (6)
Q.3 a. Explain the
distinctions among the terms primary key, candidate key and superkey. (6)
b. Explain with example the concept of a subclass
and superclass. (5)
c. Define the concept of aggregation. Give an example where
this concept is useful. (5)
Q.4 a. Consider the EMPLOYEE database as shown below:
employee (person name,
street, city)
works (person name,
company name, salary)
company (company name,
city)
Give expressions in
tuple relational calculus for each of the following queries:
(i) Find the names of
all employees who work for First Bank Corporation.
(ii) Find the names
and cities of residence of all employees who work for First Bank
Corporation.
(iii) Find the names,
street addresses and cities of residence of all employees who work
for First Bank Corporation and earn more than $10,000 per annum. (9)
b. Describe the
differences in meaning between the terms relation and relation schema. (4)
c. List two reasons why NULL values might be introduced into
the database. (3)
Q.5 a. Consider the
following table:
WORKS (Pname, Cname, Salary)
LIVES (Pname, Street, City)
LOCATED-IN (Cname, City)
MANAGER (Pname, Mgrname)
Write SQL for the following:
(i)
Find the names of the companies that are located in every city
where the company ‘Infosys’ is located.
(ii)
Find the names of the persons who live and work in the same
city. (8)
b. Let R = (A,B,C)
and let r1 and r2 both be relations on schema R. Give an
expression in SQL that is equivalent to each of the following queries.
(i) r1 U r2 (ii) r1 - r2 (8)
Q.6 a. Define and explain the following normal forms:
(i)
First normal form (ii)
Second normal form (5+5)
b. List the three goals of relational-database design.
(6)
Q.7 a. Explain why 4NF
is a normal form more desirable than BCNF. (5)
b. In designing a relational database, why might
we choose a non-BCNF design?
(6)
c. Why are certain
functional dependencies called trivial functional dependencies?
Explain with the help of an example. (5)
Q.8 a. Differentiate between primary storage and secondary
storage.
(5)
b. Explain the
distinction between closed and open hashing. Discuss the relative merits of
each technique in database applications. (8)
c. What is the
difference between a clustering index and a secondary index? (3)
Q.9 a. Explain the different search algorithms that
can be used to implement a select operation. (8)
b. What is the difference between pipelining and
materialisation? (8)