January, 2012 A7-R4: INTRODUCTION TO DATABASE MANAGEMENT SYSTEM

January, 2012

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 Which one of the following is not an example of 1:N relationship?
A) Faculty-belongsto-Department
B) BANK-has-Branches
C) University-has-Vicechancellor
D) None of the above
1.2 Consider the relation R(ABCDE) with following functional dependencies:
AB ® C, CD ® E, DE ® B
The key of the relation is
A) AC
B) AB
C) ABD
D) A

1.3 Capacity to change Conceptual schema without making changes in external schema is called
A) Logical Data Independence
B) Physical Data Independence
C) Both A) and B)
D) None of the above
1.4 In Database Systems, language used to specify Conceptual and Internal Schema is called
A) DML
B) DDL
C) VDL
D) None of the above
1.5 Collection of concepts used to describe a database is called
A) Schema
B) Model
C) Meta data
D) None of the above
A7-R4 Page 2 of 6 January, 2012
1.6 Clustering indexes are created on
A) Every file
B) File sorted on a key value
C) File sorted on a Non-key value
D) None of the above
1.7 A relational database which is in 2NF may still have undesirable data redundancies because
there may exist:
A) Non-Trivial functional dependencies involving prime attributes on RHS.
B) Trivial functional dependencies involving prime attribute only on LHS.
C) Transitive functional dependencies.
D) Non-Trivial functional dependencies involving prime attributes on LHS only.
1.8 Relationship is an association among
A) Two entities
B) Three entities
C) Two or More entities
D) None of the above
1.9 Which of the following command is used to remove the table from database?
A) Delete
B) Remove
C) Drop
D) None of the above
1.10 Which of the following is not an example of aggregate function?
A) COUNT
B) MAX
C) AVC
D) None of the above

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.1 COUNT function will not count NULL values if used with a column name.
2.2 Group by function cannot group the data from the source table based on more than one
column.
2.3 Null values are required when the value of the attribute is 0.
2.4 An entity in a subclass inherits all the attributes as well as relationships of its super class.
2.5 An entity can participate in more than one relationship type.
2.6 Primary key can have redundant attributes.
2.7 Tuples in a relation are always inserted in a order.
2.8 Using Distinct clause we can print duplicate values.
2.9 Having clause is used with grouping function
2.10 A category can be total or partial.

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)

                                     X                                                                         Y
3.1 Primary key values cannot be duplicated                                 A. Views
3.2 Parameter substitution symbol used with INSERT                  B. 1:N relationship
INTO command
3.3 Relationship of a weak entity with its owner entity                 C. Key constraints
3.4 Database objects whose data are derived from                        D. &
another table
3.5 To stop database privileges of the user                                   E. Identifying relationship
3.6 College-has-Department is                                                     F. Augmentation Rule
3.7 Desirable properties of a transaction                                     G. Revoke
3.8 Process of defining new classes from already existing          H. Join
classes
3.9 X Y XZ YZ                                                                           I. ACID
3.10 This operation can violate key constraint                             J. Weak entity
                                                                                                      K. Insert
                                                                                                     L. Generalization /Specialization
                                                                                                     M. Null

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. Metadata                        B. Complex                                         C. Key
D. LIKE                              E. Entity Integrity                               F. DML
G. DDL                               H. Foreign                                             I. Set
J. Cartesian Product            K. Weak                                             L. Range
M. Pattern matching

4.1 The BETWEEN operator is used to specify a ________ of values.
4.2 ________ entity do not have key attribute.
4.3 ________ is done through the like operator.
4.4 Without any joining condition the join becomes ________.
4.5 ________ operator combines two or more queries into one result.
4.6 ________ attributes can be divided into smaller parts.
4.7 Description of data stored in a database is called ________.
4.8 ________ commands are used to insert records in a table.
4.9 Constraints that says primary key cannot be null is called ________ constraints.
4.10 ________ key can have NULL value.

PART TWO

(Answer any FOUR questions)

5.
a) What is the goal of having three different levels of a database?
b) What is Data Independence? Explain Logical Data Independence and Physical Data
Independence with the help of examples.
c) What is Program-data Independence? Explain.
(4+6+5)
6.
a) How redundancy is controlled in DBMS?
b) What are cardinality ratios? What are the possible cardinality ratios for a binary relationship?
Explain with examples.
c) Differentiate between Primary Key and Candidate Key.
(5+6+4)
7.
a) Discuss various cases when Null values are required for an attribute.
b) Consider the following relations:
BOOK
BookNo BookName AuthorNo Publication
1 Database Management Systems 10 Pearson Education
2 Operating Systems 20 Tata McGraw Hill
3 Operating Systems 10 BPB Publication
AUTHOR
AuthorNo AuthorName
10 Ramez Elmasri
20 Herbert Schidlt
30 Galvin
Inserting a record (“5”, “Introduction to AI”, 40, “Tat McGraw Hill”) in BOOK relation is a
violation of which of the following relational constraints. Explain.
i) Entity Integrity Constraints
ii) Key Constraints
iii) Referential Integrity Constraints
c) Consider the database given in 7. b) and answer the following query in SQL:
i) Write an SQL command to insert a new record in BOOK table.
ii) Display number of books written by each author.
iii) Display all the books written by Galvin.
(4+3+[2+3+3])
8.
a) What is the difference between TRUNCATE and DELETE commands?
b) Consider a relation R(A, B, C, D, E, F, G, H, I, J) with the following dependencies:
{A, B} C
{B, D} {E, F}
{A, D} {G, H}
A I
H J
What is the key for R? Decompose the relation in to 2NF and 3NF.
c) What are the pitfalls in Relational DB Design?
(5+7+3)
A7-R4 Page 6 of 6 January, 2012
9.
a) Discuss UNDO/REDO operations. How recovery is done using these operations?
b) How privileges are assigned to different users? Explain with the help of an example.
c) Write Min-Max constraints on the following ER diagram:
(5+5+5)
College
has
Department
has
Faculty

Next Set