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

January, 2015

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 The separation of the data definition from the program is known as
A) Data dictionary
B) Data independence
C) Data integrity
D) Referential integrity
1.2 The data dictionary of a DBMS is called.
A) DBMS directory
B) DBMS Catalog
C) System Catalog
D) All of the above

1.3 Database __________, which is the logical design of the database, and the database
_______, which is a snapshot of the data in the database at a given instant in time.
A) Instance, Schema
B) Relation, Schema
C) Relation, Domain
D) Schema, Instance
1.4 Which order is correct for the analyst to normalize a data structure?
i) Remove all repeating groups and identify the primary key.
ii) Remove any transitive dependencies.
iii) All partial dependencies are removed and placed in another relation
A) i) – iii) – ii)
B) iii) – ii) – i)
C) ii) – i) – iii)
D) iii) – i) – ii)
1.5 ________ operator is basically a join followed by a project on the attributes of first relation.
A) Join
B) Semi-join
C) Full Join
D) Inner Join
``
1.6 The HAVING clause does which of the following?
A) Acts like a WHERE clause but is used for groups rather than rows.
B) Acts like a WHERE clause but is used for rows rather than columns.
C) Acts like a WHERE clause but is used for columns rather than groups.
D) Acts EXACTLY like a WHERE clause.
1.7 Each modification done in database transaction are first recorded into the
A) Hard Drive
B) Log File
C) Disk
D) Data Mart
1.8 Domain constraints, functional dependency and referential integrity are special forms of
_________.
A) Foreign key
B) Primary key
C) Referential constraint
D) Assertion
1.9 The level of abstraction describe only part of the entire database is called.
A) Physical level
B) Conceptual level
C) View level
D) None of the above
1.10 The language used in application programs to request data from the DBMS is referred to as
the
A) DML
B) DDL
C) VDL
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 Integrity constraints are rules that help ensure the quantity of the information.
2.2 The relationship between a superclass and a subclass is 1:1.
2.3 A foreign key is a field whose values identify one and only one record in the same file.
2.4 Most modern database management systems (DBMS) include powerful, menu-driven
database generators that automatically create a DDL and generate a prototype database
from that DDL.
2.5 Given a relational schema are with a set of functional dependencies F. Then it is always
possible to find a decomposition of R in BCNF which preserves the given dependencies
2.6 A Discriminator is considered as a strong primary key in E-R model.
2.7 Select operation in relational algebra is defined as sp(r) = {t | t Î r or p(t)}.
2.8 It is easy to grant and mange common privileges needed by different groups of database
users using the roles.
2.9 Security management facilities take queries from users and restructure them to minimize
response times.
2.10 Through explicitly we can assign transaction to a rollback segment.

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 Normalization                                    A. Alerts managers of need for immediate
attention
3.2 Data dictionary                                 B. Reduces data redundancy in a
database
3.3 Relational algebra                           C. Another name for the data dictionary
3.4 Database schema                             D. TIMESTAMP
3.5 Dependency among                        E. Contains historical data
non-prime attributes
3.6 View                                               F. SQL is based on this form of mathematics
3.7 Weak-entity type                            G. Contains metadata describing database structure
3.8 DATETIME data types                  H. External Model
3.9 The default level of                        I. 2NF
consistency in SQL is
3.10 Universal quantifiers                    J. Doubly outline box
                                                            K. Serializable
                                                            L. 3NF
                                                            M. NOT EXISTS

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)

4.1 The two most common queries used to extract data are select queries and ________
queries.
4.2 Checkpoints are a part of ________.
4.3 Relations produced from an E-R model will always be ________.
4.4 Cross Product is a _______.
4.5 ________ is a set of one or more attributes taken collectively to uniquely identify a record?
4.6 ________ operator is used for appending two strings.
4.7 A ________ indicates an absent value that may exist but be unknown or that may not exist at
all.
4.8 Fifth Normal form is concerned with ________.
4.9 Every weak entity set can be converted into strong entity set by ________.
4.10 The ________ keyword is used to access attributes of preceding tables or sub queries in the
from clause.
A. Binary Operator                 B. Candidate key                            C. Lateral
D. Super key                           E. ||                                                F. Where
G. Blank                                  H. First normal form                    I. Join dependency
J. Generalization                     K. NULL                                      L. Join
M. Recovery measures``

PART TWO

(Answer any FOUR questions)

5.
a) What is the difference between the database schema and the database instance and how do
external and conceptual schema relate to each other?
b) Given a relation R with four attributes, ABCD, and a set of functional dependencies:
AB ® C
AB ® D
C ® A
D ® B
i) Identify the candidate key(s) for R.
ii) Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF).
(6+9)
6.
a) Define the following terms: relation schema, relational database schema, relation instance,
relation cardinality, and relation degree.
b) Consider the following schedule S, consisting of transactions T1, T2 and T3.
T1 T2 T3
w(A)
r(A)
w(B)
w(B)
w(A)
r(B)
r(B)
i) Is S conflict serializable? Justify your answer.
ii) Is S view serializable? Justify your answer.
(6+9)
7.
a) Consider the following relational database:
Emp (eid: integer, ename: string, age: integer, salary: real)
Works (eid: integer, did: integer, pct_time: integer)
Dept (did: integer, dname: string, budget: real, managerid: integer)
Give an example of a foreign key constraint that involves the Dept relation. What are the
options for enforcing this constraint when a user attempts to delete a Dept tuple?
b) Write down the role of DBA.
c) Using mathematical notations to describe the PROJECTION operator of the relational
algebra.
(8+5+2)
8.
a) Differentiate Logical data independence and Physical data independence.
b) Write a short note on two-phase commit protocol.
c) What are the advantages of view?
(5+6+4)
``
A7-R4 Page 5 of 5 January, 2015
9.
a) Consider the following schema:
CUSTOMER(id, name, address)
ORDER(ordernbr, date, id)
ORDERLIST(ordernbr, barcode, company, quantity)
PRODUCT(barcode, description)
VENDOR(vendornbr, company, rating)
VENPRODLINK(barcode, vendornbr, price)
Construct an E-R diagram for the above schema; specify keys, mapping cardinalities,
participation constraints (if necessary).
b) Explain Log-based Recovery.
c) What is 1NF (Normal Form)?
(9+4+2)

Next Set