Friday, 23 September 2016

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

January, 2016
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 these is an advantage of the database systems?
A) Data Abstraction
B) Program-data Independence
C) Centralized data management
D) All of the above
1.2 Which of the following phases of database design is the E-R model related to?
A) Conceptual Database design
B) Logical Database design
C) Requirement analysis
D) None of the above

1.3 What does referential integrity prevent?
A) Recursive Joins
B) Loss of data from employee sabotage
C) Loss of data from any one corrupted table
D) Data Redundancy
1.4 A functional Dependency is a relationship between/among
A) Tuples
B) Relations
C) Attributes
D) None of the above
1.5 In which of the following situations, 3NF and BCNF are considered identical?
A) If there are more than one candidate keys in the relation and they overlap
B) If there is only one determinant upon which other attributes depend and it is a candidate Key
C) If the candidate keys in the relation are composite keys
D) None of the above
1.6 Threats to the databases result in the loss or degradation of the following:
A) Loss of integrity
B) Loss of availability
C) Loss of confidentiality
D) All of the above
1.7 Which of the following techniques maintain transaction logs to keep track of all updates
operation of the transactions?
A) Log-based Recovery
B) Shadow Paging
C) Both A) and B)
D) None of the above
1.8 The operation that is used to retrieve those tuples present in one relation, and not present in
another relation is
A) UNION
B) ALTER
C) MINUS
D) DIFFERENCE
1.9 Which of the following is the binary operation?
A) Union
B) Join
C) Cartesian Product
D) All of the above
1.10 A NULL means
A) Unknown
B) Known
C) Known partially
D) None 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 Using a set of files is better than using a Database.
2.2 DML is concerned with Data Definition.
2.3 A query within a query is called as Subquery.
2.4 The process of joining of multiple strings is known as Concatenation.
2.5 5NF is also known as DKNF.
2.6 A table in 3NF must not have Transitive dependency.
2.7 The blocks residing on the disks are called as Buffer Blocks.
2.8 The point of synchronization between the database and the transaction log file is called as a
UNDO and REDO.
2.9 Two actions on the same data object conflict if at least one of them is a Read/Write.
2.10 The complement of Generalization is Specialization.

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 To_Char                                                  A. Information about data in a database
3.2 Entity Integrity                                       B. Mapping Cardinality
3.3 Number Function                                   C. Foreign Key
3.4 Meta Data                                               D. Date and Number is converted to Character
3.5 Many-to-many                                       E. 2NF
3.6 Quantifier                                               F. Data control language
3.7 A→B, B→C, THEN A→C                   G. BCNF
3.8 Determinant                                          H. No primary key can be Null
3.9 Access Matrix                                       I. CEIL
3.10 Grant, Revoke command                    J. Armstrong’s Axiom
                                                                     K. Authorization
                                                                     L. AVG
                                                                    M. Tuple relational Calculus

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. Range                               B. m*n                                         C. Aggregation
D. Decode                             E. IN                                            F. One-to-many relationship
G. DBA                                 H. HAVING                                I. Internal
J. External                             K. Logical Data
Independence
L. One-to-one relationship
M. m+n

4.1 ________ play an important role in defining and maintaining a database for an Organization.
4.2 ________ Level deals with Physical representation of the database on the computer.
4.3 The ability to change the conceptual schema without affecting the external schema or
application Program is known as ________.
4.4 The strong entity type and weak entity type participates in ________ relationship.
4.5 If relation R has m tuples and relation S has n tuples, then the result of Join on relation R and S
will have up to ________ tuples.
4.6 ________ Operator selects values that match any value in a given List of Values.
4.7 ________ Clause is used to restrict groups returned by the Group by Clause.
4.8 ________ Function is used to expand any abbreviations used in the table.
4.9 The Between operator is used to specify a ________ of values.
4.10 ________ is an abstraction through which relationships are treated as higher level entities which
can then participate in relationship with other entities.

PART TWO
(Answer any FOUR questions)
5.
a) Explain the Three-level Architecture of DBMS? Also mention its advantages.
b) What are the different types of database users who interact with the database system?
c) Discuss the clear difference between specialization and generalization with the help of an
example? Is it possible to represent their difference with the help of an E-R diagram? Explain.
(5+5+5)
6.
a) Consider the following database tables and answer queries using SQL
Employee (emp_no, name, skill, pay_rate)
Position (posting_no, skill)
Duty-allocation (posting_no, emp_no, day, shift)
i) Get employee whose rate of pay is more than or equal to the rate of pay of employee
“XYZ”.
ii) Find the employee with the lowest pay rate.
iii) Get a count of different employee on each shift.
b) What is Relational Database? What are its advantages? Explain with example.
(9+6)
7.
a) Draw an E-R diagram to show the cricket team statistics stating any assumptions you make to
show all the key features of the E-R modeling concept.
b) What is database security? Why is it Important? Also discuss the various security issues.
(9+6)
8.
a) Consider a relation schema R (A,B,C,D,E,G) with set F of Functional dependencies
{A →B, BC →D, D →E, D →G}
Find the closure of F.
b) Consider the Universal relation R(A,B,C,D,E,F,G,H,I,J) and the set of FD’s:
AB →C, A →DE, B →F, F →GH, D →IJ
What is the key for R? Decompose R into 2NF and 3NF relations.
c) A relation R (A, B, C, D, E, F) has the following set of FD’s:
A →CD, B →C, F →DE, F →A
Is the decomposition of R in R1 (A, B, C), R2 (A, F, D) and R3 (E, F) dependency preserving
and lossless decomposition?
(5+5+5)
9.
a) Explain Checkpoints. How does it help in reducing the amount of time required during recovery?
Also discuss the concept of fuzzy checkpoint.
b) What is Log based Recovery techniques? Explain the deferred and Immediate- modification
versions of the Log-based recovery techniques?
(8+7)

Next Set