July, 2011
A7-R4: INTRODUCTION TO DATABASE MANAGEMENT SYSTEM
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 “tear-off” answer sheet attached to the question paper, following instructions therein. (1x10)
1.1 A view is aA) Base table
B) Derived table
C) Named derived table
D) None of the above
1.2 System failures are also known as
A) Soft crashes
B) Media crashes
C) Hard crashes
D) System failures are non-existent
1.3 Considering the following statements
i) A given user has different access rights on different objects.
ii) Each data object has a certain classification level, while the user has a clearance level.
iii) Is flexible.
iv) Is rigid.
Which of the following combinations of the above given statements, best describes discretionary
control?
A) Only i)
B) Only ii)
C) i) and iii)
D) ii) and iv)
1.4 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.” is
A) Atomicity
B) Consistency
C) Isolation
D) Durability
1.5 [TS => Timestamp; Ti => ith transaction] The following operation sequence :
If TS(Ti) < TS(Tj) [Ti is older than Tj]
Ti is allowed to wait
Else abort Ti and restart later with the same timestamp;
corresponds to
A) Wait – Die
B) Wound – Wait
C) Continuous – Wait
D) Strict Timestamp Ordering
1.6 The Deferred – Update technique is synonymous to
A) NO UNDO/ REDO algorithm
B) UNDO/ REDO algorithm
C) UNDO/ NO REDO algorithm
D) None of the above
1.7 Given the query:
{t.DOB, t.ADDRESS | EMPLOYEE(t) ^ t.FNAME = ‘MAHESH’ ^ t.LNAME = ‘KUMAR’}
Consider the following query formats
i) Select DOB, ADDRESS
From EMPLOYEE
Where FNAME = ‘MAHESH’ AND LNAME = ‘KUMAR’
ii) {uv | ( $ q)( $ r)( $ s)( $t)( $w) EMPLOYEE(qrstuvw) ^ q = ‘MAHESH’ ^ s = ‘KUMAR’}
Which of the following combinations best describes the given relational calculus query?
A) Only i)
B) Only ii)
C) i) and ii)
D) All the three queries retrieve different data
1.8 A relation schema R is in ________ if, whenever a non-trivial functional dependency X A holds
in R, then X is a superkey of R
A) 3NF
B) BCNF
C) 2NF
D) 4NF
1.9 Considering the following functional dependencies
DM NP and D M; after applying the elimination rule of extraneous attributes, the resultant
dependency becomes
A) D M
B) D NP
C) M NP
D) M N
1.10 The capacity to change the conceptual schema without having to change the external schema
or application programs
A) Data independence
B) Physical data independence
C) Logical Data independence
D) Such change mechanisms are non-existent
2. Each statement below is either TRUE or FALSE. Choose the most appropriate one and ENTER in the “tear-off” sheet attached to the question paper, following instructions therein. (1x10)
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 The commit point corresponds to the end of a logical unit of work.
2.6 An attribute is prime if it is not a member of some candidate key.
2.7 The Cartesian product of two relations R1 and R2, includes as tuples all possible permutations of
tuples from R1 and R2.
2.8 A cohert channel specifies the channels along which information is allowed to move.
2.9 The Relational Algebra query: is equivalent
to the SQL query: Select NAME, ADDRESS
From EMPLOYEE
Where DNAME = ‘RESEARCH’.
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 “tear-off” answer sheet attached to the question paper, following instructions therein. (1x10)
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 “tear-off” answer sheet attached to the question paper,following instructions therein. (1x10)
A. Schema B. Non-procedural C. Domain
D. Relation E. Many-to-One F. One-to-One
G. Successful H. Catalog I. Conceptual View
J. View mechanism K. Procedural L. Divide
M. Range
4.1 The ________ is a representation of the entire information content of the database.
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 A ________ in SQL consists of the descriptors for an individual database.
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 signal ________ 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?
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 at a time.
(8+7)
6.
a) Define 3NF and 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.
(5+5+5)
7.
a) How are the operations of EQUI-JOIN and NATURAL-JOIN different? Explain with an example
based on relational algebra.
b) Write SQL commands for the following schema:
EMP(ENO, NAME, ADDRESS, SALARY, DNO)
DEPT(DNO, DNAME)
DEPT_LOC(DNUM, DLOC)
PROJECT(PNO, NAME, LOCATION, DNUM).
i) Retrive the name and address of all the employees who work for the ‘R&D’ department.
ii) For every project located in Kolkata, display the project number and the controlling
department name.
iii) Retrieve the names of the projects, whose locations do not match the location of their
respective controlling departments.
c) What is a safe expression? Give an example.
(5+7+3 )
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)
A7-R4 Page 6 of 6 July, 2011
9.
a) How does Oracle support very large databases using different horizontal partitioning of tables?
What are the advantages of this partitioning scheme?
b) A transaction reads a data item balance which has 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)
No comments:
Post a Comment