DipIETE – CS (OLD SCHEME)
Code: DC10 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. A
primary key, if combined with a foreign key creates ______________.
(A) parent child relationship
between the tables that connect them
(B)
many to many relationship between the
tables that connect them
(C) one to one relationship
between the tables connect them
(D)
hierarchical mode
b. Manager’s
salary details are hidden from the employee. This is __________.
(A) integrity constraint (B) referential constraint
(C) over defined constraint (D) feasible constraint
c. A
relational model which allows non atomic domain is__________.
(A) non-atomic model (B) nested relational model
(C) network model (D) hierarchical model
d. A
schema describes ___________.
(A) data elements (B) records and files
(C) record relationships (D) all of these
e. Which
of the following are not characteristics of a relational database model?
(A) Table. (B) Tree like structure.
(C) Complex logical relationship. (D) Records.
f. A
file consist of __________.
(A) database and files (B) records and sorts
(C) fields and records (D) fields and sorts
g. In
E-R Diagram derived attribute is represented by ____________.
(A) double ovals (B) dashed oval
(C) single oval (D) triangle
h. The
way a particular application views the data from the database that the application
uses is a _________.
(A) module (B) schema
(C) subschema (D) relational model
i. Given
relations r(w,x) and s(y,z), the result of
select distinct w,x from r,s
is guaranteed to be the same as r,
provided:
(A) r has no duplicates and s is non-empty.
(B)
r and s have no duplicates.
(C) s has no duplicates and r is non-empty.
(D)
r and s have the same number of tuples.
j. Union
operator is a __________.
(A) unary operator (B) ternary operator
(C) binary operator (D) Not a operator
Answer any
FIVE Questions out of EIGHT Questions.
Each
question carries 16 marks.
Q.2 a. What
is Database Management System? Explain briefly the functions of database management
system. (8)
b. Explain
the difference between: (8)
(i) Instance and Schema.
(ii) Physical and Logical
data independence.
Q.3 a. Design
a E-R diagram showing generalization–specialization hierarchy for a
motor-vehicle sales company. The company sells motorcycles, passenger cars,
vans, and buses. Justify your placement of attributes at each level of the
hierarchy. Explain why they should not be placed at a higher or lower level. (8) 16
b. Explain
the difference between a weak and a strong entity set. We can convert any weak
entity set to a strong entity set by simply adding appropriate attributes. Why,
then, do we have weak entity sets? (8)
Q.4 a Discuss
the different types of user friendly interfaces provided by the DBMS and the
types of users who typically use each. (8)
b. What
do you understand by DBA? Explain the role of DBA in a database management
system? (8)
Q.5 a. Explain
sequential file organization. In the sequential file organization, why is an
overflow block used even
if there is, at the moment, only one overflow record? (8)
b What
are the causes of bucket overflow in a hash file organization? What can be done to reduce the occurrence of
bucket overflows? (8)
Q.6 Explain the following: (16)
(i) Heap
file.
(ii) Data Models.
(iii) Relational Completeness.
(iv) Mid square method of hashing.
Q.7 a. Consider
the following relational database, where the primary keys are underlined. (8)
employee (person-name,
street, city)
works (person-name,
company-name, salary)
company (company-name,
city)
manages (person-name,
manager-name)
Give an expression in the
relational algebra for each request:
(i)
Give all employees of First Bank Corporation a 10 percent salary raise.
(ii) Give all managers in this database
a 10 percent salary raise, unless the salary
would be greater than $100,000. In such cases, give only a 3
percent raise.
b. What
are the General Transformation Rules for Relational operations? (8)
Q.8 a. Consider
the employee database, where the primary keys are underlined (8)
employee (employee name,
street, city)
works (employee name,
company name, salary)
company (company name,
city)
manages (employee
name, manager name)
Give an expression in SQL for
each of the following queries
(i) Find all employees who earn more than the
average salary of all employees of their company.
(ii) Find the company that has the
smallest payroll.
b. Discuss
the various update operations on relations and the types of integrity constraints
that must be checked for each update operation. (8)
Q.9 a. What
is the purpose of tables, private synonyms and public synonyms? If there are
multiple objects of same name on an oracle database, which order are they accessed
in? (8)
b. What is the purpose and syntax of the
following SQL commands: UPDATE, ALTER, COMMIT, and ROLLBACK. (8)