July, 2012 A7-R4: INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS

July, 2012

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 “tear-off” answer sheet attached to the question paper, following instructions therein. (1x10)

1.1 The language used application programs to request data from the DBMS is referred to as
A) DML
B) DDL
C) Query Language
D) All of the above
1.2 When two transactions run concurrently types of problems encounter are
A) Data delete Problem, Dirty read problem, Incorrect summary problem
B) Lost update problem, Dirty read problem, Incorrect summary problem
C) Lost update problem, Data delete Problem, Incorrect summary problem
D) Lost update problem, Dirty read problem, Data delete Problem

1.3 The data dictionary of a DBMS is called.
A) DBMS directory
B) DBMS Catalog
C) System Catalog
D) Al l of the above
1.4 Which of the following statements about view is true?
A) A view can be created as a join on two or more tables
B) A view can not have an ORDER BY clause in the SELECT statement
C) A view can not be created with a GROUP BY clause in the SELECT statement
D) A view can be created as read only
1.5 Examine the description of the STUDENT table:
(STD_ID NUMBER(4), COURSE_ID VARCHAR2 (10),START_DATE DATE,
END_DATE DATE)
Which of the following aggregate function is valid on the START_DATE column?
A) SUM(START_DATE)
B) AVG(START_DATE)
C) COUNT(START_DATE)
D) MAXIMUM(START_DATE)
1.6 In which case would you use a FULL OUTER JOIN?
A) Both tables have NULL values
B) You want all unmatched data from one table
C) You want all matched data from both tables
D) You want all unmatched data from both tables
1.7 A table is in fourth normal form (4NF) if ________.
A) All attributes are dependent on the primary key
B) It is in 3NF and has no independent multivalued dependencies
C) No column contains the same values
D) It is in 3NF and there are no repeating groups
1.8 The ________ model views the data as part of a table or collection of tables in which all key
values must be identified.
A) Relational
B) Object-oriented
C) Conceptual
D) External
1.9 Who is responsible for authorizing access to the database, for coordinating and monitoring
its use?
A) Database designers
B) Database administrators
C) End users
D) Application programmers
1.10 The functional dependency between two attributes represents which kind of relationship
A) One-to-one
B) One-to-many
C) Many-to-many
D) All 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 A field is a collection of records arranged in a predefined format.
2.2 Integrity constraints are rules that help ensure the quantity of the information.
2.3 We have normalized a table into BCNF if all candidate keys are determinants.
2.4 Given the functional dependency R (S, T), then it is also true that R S.
2.5 A primary key is a composite key that was selected to be the main identifier for the relation.
2.6 A NULL value means that a value is not known, or that a feature does not apply to a certain
individual.
2.7 A relational DBMS is a single data repository in which data independence is maintained.
2.8 The relationship between a superclass and a subclass is 1:1.
2.9 The hierarchical model was the first to define a standard DML and DDL.
2.10 Security management facilities take queries from users and restructure them to minimize
response times.

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 The default level of consistency in SQL is                        A. y Í x
3.2 Relational Algebra does not have                                      B. Binary operator
3.3 Universal quantifiers                                                         C. Data dictionary
3.4 A functional dependency of the form x ® y is                  D. Read committed
trivial if
3.5 Network datamodel                                                            E. Serializable
3.6 The graphical representation of a query is                        F. x Í y
3.7 Union operator                                                                 G. B-Tree
3.8 DATETIME data types                                                     H. Aggregation operators
3.9 A table can have only one                                                I. NOT EXISTS
3.10 Schema specification storage                                         J. Query Tree
                                                                                               K. Projection operator
                                                                                               L. Primary key
                                                                                              M. TIMESTAMP

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)

4.1 The values of the attribute describe a particular ________.
4.2 The normal form of a relation refers to the ________ normal form it meets.
4.3 The ________ clause specifies a summary query.
4.4 A subschema is a ________ of the schema.
4.5 A functional dependency is the property of the ________.
4.6 The main goal of recovery is to ensure the ________ property of a transaction.
4.7 A transaction is ________ preserving if its complete execution takes the database from one
consistent state to another.
4.8 ________ Relational algebra operations do not require the participating tables to be unioncompatible.
4.9 Checkpoints are a part of ________.
4.10 Relations produced from an E-R model will always be ________.
A. Atomicity                           B. Having                                 C. Second normal form
D. Derived                               E. Entity instance                     F. Where
G. Consistency                         H. First normal form                 I. Semantics
J. Inherits                                K. Highest                                L. Join
M. Recovery measures

PART TWO

(Answer any FOUR questions)

5.

a) Explain ANSI/SPARC architecture of Database system with diagram.
b) Explain deferred database modification approach Versus Immediate database modification
approach. Which is better in various different situations? Why?
c) Define check point and its impact on data base recovery.
(5+6+4)
6.
a) Define the following terms: relation schema, relational database schema, relation instance,
relation cardinality, and relation degree.
b) What is the difference between a candidate key and the primary key for a given relation?
What is a superkey?
c) Explain conflict Serializability. Why is it preferred?
(6+5+4)
7.
a) Consider the following relational database
Employee (emp_cd, emp_nm, salary, manager_cd)
Project (proj_cd, proj_nm)
Assigned_to (emp_cd, proj_cd)
Assume the following. Write your assumptions, if any.
• Employee may be assigned to more that one project.
• Manager is an employee.
Specify the following queries in relational algebra and in SQL
i) Retrieve names of all employees with the name of the project to which they are
assigned.
ii) For a given emp_cd, find the name of the manager.
iii) Count the number of employees assigned to each project.
b) Write down the role of DBA.
c) What is NULL value? When do we need to use NULL values? Illustrate your answer by
example.
(9+3+3)
8.
a) Compute the closure of the following set F of functional dependencies for relation schema
R = (A, B, C, D, E).
A BC
CD E
B D
E A
List the candidate keys for R.
b) Write a short note on two-phase commit protocol.
(9+6)
9.
a) A hospital maintains data about the following entities:
i) Patients, entity set with attributes SSNo, LastName, FirstName, HomePhone, Sex,
DateofBirth, Age, Street, City, State, Zip
ii) Doctors, entity set with attributes SSNo, LastName, FirstName, OfficePhone, Pager,
Specialty
iii) Beds, entity set with attributes RoomNumber, BedNumber, Type, Status,
PricePerHour
iv) Accounts, entity set with attributes DateIn, DateOut, Amount
Construct an E-R diagram for the Hospital Management; specify keys, mapping cardinalities,
participation constraints (if necessary).
b) What is RDBMS? Mention advantages of the RDBMS.
c) Differentiate between natural join and outer join.
(9+4+2)

Next Set