July, 2010 A7-R4: INTRODUCTION TO DBMS

 July, 2010

A7-R4: INTRODUCTION TO DBMS

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 Description of data stored in database is called
A) Schema
B) Meta Data
C) Information
D) None of the above
1.2 When one transaction updates a database item and then transaction fails for some reason.
The updated item is accessed by another transaction before it is changed back to its original
value is a
A) Lost update
B) Temporary update
C) Incorrect summary
D) None of the above

1.3 In Databases, Redundancy leads to the following problems
A) Inconsistency
B) Duplication of effort
C) Accuracy
D) Inconsistency as well as duplication of efforts
1.4 Which one of the following is necessarily an example of 1:1 relationship?
A) employee-assigned_to-Department
B) BANK-has-Branches
C) College-has-Principal
D) None of the above
A7-R4 (New) Page 2 of 6 July, 2010
1.5 Degree of a relationship is
A) Number of relationship instances
B) Number of participating entities in that relationship
C) Number of attributes of a relationship
D) None of the above
1.6 Constraints inherited in every schema of that model is called
A) Schema based
B) Application based
C) Model based
D) None of the above
1.7 If X ->Y holds then
A) X determines Y uniquely
B) Y determines X uniquely
C) Both A) and B) are true
D) None of the above
1.8 The write ahead logging (WAL) protocol simply means that
A) The writing of data item should be done ahead of any logging operation
B) The log record for an operation should be written before the actual data is written
C) All log records should be written before a new transaction begin
D) The log never needs to be written
1.9 The following command is used to change the structure of the table.
A) UPDATE
B) ALTER
C) MODIFY
D) None of the above
1.10 Two relations R(a1,a2… an) and S(b1,b2… ba) are said to be union compatible if
A) Their degree is same
B) dom(Aa)=dom(Ba ) 1<=i<=n
C) Both A) and B) are true
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 Role names are required in recursive relationships.
2.2 Key attributes can have NULL values.
2.3 Every relation has a super Key.
2.4 In Generalization/specialization every entity from super class has to be part of the sub class.
2.5 DELETE command is used to remove the table from a data base.
2.6 Intersection operation can be expressed by using UNION and MINUS operations.
2.7 TRUNCATE is a DDL command used to deletes all the rows from a table.
2.8 Derived attributes are stored in database.
2.9 Key constraints specify that primary key values can not be NULL.
2.10 We can guarantee that BCNF relational schema will be produced by dependency preserving
decomposition of non-BCNF relational schemas.

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 Process of defining new classes from                       A. Truncate
already existing class is called
3.2 If entity in super class cannot exist in more             B. Deferred update
than one sub class then all sub classes are
3.3 Storing the same data multiple time leads to           C. ARIES recovery algorithm
3.4 The maximum number of relationship                    D. Discretionary Access Control (DAC)
instances that an entity can participate in is
called
3.5 Capacity to change conceptual schema                    E. Degree
without making changes in External
schema
3.6 It is a DDL command and deletes all the                 F. Disjoint
rows from a table. All memory space for
that table is also released
3.7 A transaction cannot change the database              G. Specialization
on disk until it reaches the commit point
3.8 Steal/No force approach for writing                      H. Delete
3.9 Vulnerability to malicious attack                           I. Join
3.10 This operation can violate Referential                J. Data Independence
Integrity Constraint
                                                                                    K. Weak entity
                                                                                     L. Cardinality Ratio
                                                                                     M. Redundancy

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. DML                                   B. Serial                                            C. Application
D. LIKE                                   E. Model                                          F. BETWEEN
G. WHERE                              H. Having                                        I. High Level
J. Representational                  K. SELECT                                      L. DDL
M. Group by

4.1 ER modeling is an example of ________ data model.
4.2 The ________ operation of SQL is an example of unary operation.
4.3 The ________ operator is used for Pattern matching.
4.4 The ________ operator is used to specify a range of values.
4.5 Joining condition is specified in the ________ clause.
4.6 Schedules A and B are called ________ because the operations of each transactions are
executed consecutively.
4.7 Collection of concepts used to describe the structure of a database is called database
________.
4.8 ________ commands are used to create tables and schemas.
4.9 Constraints that cannot be directly expressed in schema are called ________ based
constraints.
4.10 To provide a condition on group of tuples associated with each value of the Grouping
attribute, ________ clause is used.

PART TWO

(Answer any four questions)

5.

a) Design an ER diagram for Bank database. The database designer provides the following
description:
i) Each bank has a unique name, code and address and it can have multiple branches.
ii) Store each customer’s detail(Name, address, phone number)
iii) Store Accounts details (Account number, type , Balance)
iv) Also store details of the Loans taken by the customers
Specify Min-Max constraints.
State clearly any additional assumptions you make
b) What is a referential integrity constraint? Why do we need it.? Explain with the help of
suitable example.
(9+6)
6.
a) Consider a relation R(A,B,C,D,E) with the following dependencies:
AB->C, CD->E, DE->B
Is AB the candidate key of the relation? Explain your answer.
b) Consider a relation R(A,B,C,D,E,F,G,H,I,J) with the following dependencies:
AB->C
A->D,E
B-> F
F->G,H
D->I,J
What is the key for R? Decompose the relation in to 2NF and 3NF.
c) What is Dependency Preserving decomposition? Explain.
(4+7+4)
7.
a) What is a schedule? Define the concepts of recoverable, cascade less and strict schedule
and compare them in terms of their recoverability.
b) How checkpoints are used in database recovery?
c) What are the main differences between designing a Relational database and Object
database?
(6+4+5)
8.
a) Consider the following database:
STUDENT
ROLLNO NAME COURSENO ADDRESS
1 Roma Singh 10 10, Morris Nagar
2 Megha Chowdhary 20 101, Civil Lines
3 Anita Goyal 10 23, Under Hill Road
A7-R4 (New) Page 6 of 6 July, 2010
COURSE
CNO CNAME
10 B.Sc.(H) Computer Science
20 MCA
30 M.Sc Computer science.
Write the following queries in SQL:
i) Write a query to display Name and Course Name of all the students who have opted for
B.Sc. (H) computer Science.
ii) Write a query to display Number of students in each course.
iii) Write a query to display total number of students in all the courses.
iv) Write a command to add one more field SectionNo to table STUDENT.
b) Explain total and partial participation of an entity in a relationship with the help of examples.
c) What is data Independence? Explain.
(6+5+4)
9.
a) Does a DBMS allow propagating privileges? Explain with the help of an example.
b) What is dummy inbuilt table in Oracle?
c) What is the difference between GRANT and REVOKE command?
(6+3+6)

Next Set