Monday, 26 September 2016

January, 2014
A7-R4: INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS

PART ONE
(Answer all the questions)

1. Each question below gives a multiple choice of answers. Choose the most appropriate one and enter in the “OMR” answer sheet supplied with the question paper, following instructions therein. (1x10)

1.1 Which of the following schemas defined the stored data structures in terms of the database
model used?
A) External
B) Conceptual
C) Internal
D) None of the above
1.2 Object oriented data models are used to
A) specify overall structure of the database
B) describe data and its relationships.
C) higher level description of storage structure and access mechanism.
D) all of the above

1.3 A composite attribute is composed of
A) single component with an independent existence.
B) Multiple components, each with an independent existence
C) Both A) and B)
D) None of the above
1.4 CREATE operation of SQL is a
A) data query language
B) data definition language
C) data manipulation language
D) data control language
1.5 The property of transaction that states “The changes applied to the database by a committed
transaction must persist in the database. These changes must not be lost because of any
failure.”
A) Atomicity
B) Consistency
C) Isolation
D) Durability

1.6 If attribute A determines both attributes B and C, then it is also true that:
A) A B.
B) B A.
C) C A.
D) (B,C) A.
1.7 Which of the following is an advantage of partitioning?
A) Complexity
B) Extra space
C) Consistent access speed
D) Security
1.8 Three level architecture of the database system provides
A) Data Dependency
B) Data Manipulation Facility
C) Data Abstraction
D) None of the above
1.9 A relation schema R is in ________ if, whenever a non – trivial functional dependency X A
holds in R, then X is a super key of R:
A) 3NF
B) BCNF
C) 2NF
D) 4NF
1.10 A normal form is
A) A state of a relation that results from applying simple rules regarding FDs.
B) The highest normal form condition that it meets.
C) An induction of the degree to which it has been normalized.
D) All of the above.

2. Each statement below is either TRUE or FALSE. Choose the most appropriate one and ENTER in the “OMR” answer sheet supplied with the question paper, following instructions therein. (1x10)

2.1 The data communications manager is a part of the DBMS.
2.2 A view is a virtual named derived relation, while a snapshot is real.
2.3 The SQL is strongly typed.
2.4 The physical design is DBMS specific while the logical design is DBMS independent.
2.5 A primary key is a composite key that was selected to be the main identifier for the relation.
2.6 A NULL value means that a value is not known, or that a feature does not apply to a certain
individual.
2.7 A relational DBMS is a single data repository in which data independence is maintained.
2.8 Creation of Indexes makes sorting of records operation efficient.
2.9 Database administrator is responsible for performance monitoring of the database system.
2.10 Generalization is the process of defining a set of subclasses of an entity.

3. Match words and phrases in column X with the closest related meaning/word(s)/phrase(s) in column Y. Enter your selection in the “OMR” answer sheet supplied with the question paper, following instructions therein. (1x10) 
                      X                                                                                         Y
3.1 The immunity of applications to changes in the                          A. DDL
way data is stored and accessed.
3.2 Supports the processing of database objects.                              B. Dirty Read Problem
3.3 The number of tuples.                                                                 C. Lossless Join
3.4 The smallest semantic unit of data.                                            D. Data independence
3.5 The problem that occurs when one transaction                          E. Cardinality
updates a database item and then the
transaction fails for some reason.
3.6 Set of tuples of a relation that satisfy some                                F. View Definition Language
selection condition.
3.7 The property which ensures that each functional                       G. Query Compiler
dependency is represented in some individual
relation resulting after
3.8 Extracts DML commands from an application                         H. Scalars
program written in a host language.
3.9 The operation that produces all combinations of                      I. Theta - Join
tuples from relations R1 and R2 that satisfy a
join condition.
3.10 Often used to specify user views and their
mappings.
                                                                                                          J. Population
                                                                                                         K. Precompiler
                                                                                                         L. Dependency Preservation
                                                                                                        M. Degree

4. Each statement below has a blank space to fit one of the word(s) or phrase(s) in the list below. Enter your choice in the “OMR” answer sheet supplied with the question paper,following instructions therein. (1x10)

A. Schema                                  B. Non-precodural                            C. Domain
D. Successful                             E. Sequential file                               F. Physical Data Independence
G. View mechanism                  H. Divide                                             I. Range
J. Many-to-One                          K. Difference                                    L. Relation
M. Logical Data Independence

4.1 Immunity of the conceptual schemas to changes in the internal schema is referred to as
________.
4.2 The ________ is a pool of values from where specific attributes of specific relations draw their
actual values.
4.3 The ________ operation returns a relation consisting of all tuples appearing in the first, but not
in the second of the two specified relations.
4.4 Relational calculus is ________.
4.5 The tuple variable ranges over a ________.
4.6 In an indexed organization if the records are stored sequentially based on the primary key
value then the file organization is called an ________.
4.7 A ________ consists of the descriptors for that portion of that database that belongs to some
individual.
4.8 A functional dependency is a ________ relationship between two sets of attributes in a given
relation.
4.9 The COMMIT TRANSACTION operations signals ________ end of transaction.
4.10 The ________ subsystem can be used to hide sensitive data from unauthorized users.

PART TWO
(Answer Any FOUR Questions)
5.
a) Show the block diagram of 3 level database architecture. Explain the significance of each
level. What do you mean by physical and logical data independence with respect to the above
diagram?
b) Given the following assertions for the relational database that represents the current term at a
university, draw an ER diagram for this schema that takes into account all the assertion given
below.
Assertions:
i) An instructor may teach none, one, or more courses in a given term.
ii) An instructor must direct the research of at least one student.
iii) A course may have none, one or two pre requisite.
iv) A course may exist even if no students are currently enrolled.
v) All courses are taught by only one instructor.
vi) The average enrollment in a course is 30 students.
vii) A student must select at least one course per time.
(8+7)
6.
a) Define 3NF, BCNF. Give an example of a relation that is in 3NF but not in BCNF.
b) Given the following set of Functional Dependencies, find the minimum set of 3NF relations.
Designate the candidate key attribute of these relations. Is the set of relations you derived,
also BCNF?
A BCDEF, AB CDEF, ABC DEF, ABCD EF, ABCDE F, B DG, BC DEF,
BD DEF, E BF
c) What do you mean by integrity constraint? Give example. What are the allowed integrity
constraints?
(5+5+5)
7.
a) Explain briefly how checkpoints are used to reduce the amount of operations that are to be
UNDONE in a recovery phase.
b) What is the difference between a candidate key and the primary key for a given relation?
What is a superkey?
c) Explain deferred database modification approach Versus Immediate database modification
approach. Which is better in various different situations? Why?
(5+5+5)
8.
a) What is data encryption? Name the different categories of encryption algorithm with one
example per category.
b) Write a short note on shadow paging. State some of its disadvantages.
c) What is Trigger in SQL? Give an example to define a trigger.
(5+6+4)
9.
a) Write a short note on two-phase commit protocol.
b) A transaction reads a data item balance which have a value of Rs. 1000/-, modifies it to
Rs.1500/- and writes back on to database. Show the log entries and the database value of
balance when
i) The value is read.
ii) The value is updated.
iii) The transaction partially commits.
c) What is the purpose of division operator in relational algebra? Give an Example.
(6+6+3)

Next Set