January, 2013
A7-R4: INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS (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)
containing nine fields”. Translating this statement in database terminology means
A) 200 attributes and 9 rows
B) 200 rows 9 tuples
C) 200 tuples and 9 attributes
D) 200 attributes and 9 tuples
1.2 Given the following relation schemas
EMPLOYEE (EMP_CODE, EMP_NAME, JOB_CODE)
PLAN (PLAN_CODE,PLAN_DESCRIPTION)
BENEFIT(EMP_CODE,PLAN_CODE)
Attribute PLAN_CODE in relation BENEFIT is
A) Primary Key
B) Foreign Key
C) Candidate Key
D) Alternate Key
1.3 In Question 1.2 above, what is the cardinality of relationship between EMPLOYEE and
PLAN?
A) One to One
B) One to Many
C) Many to One
D) Many to Many
1.4 “A Course is a prerequisite of another Course” represents
A) Ternary relationship
B) Recursive relationship
C) Strong relationship
D) Weak relationship
1.5 Given a relation R(A,B,C,D) and following functional dependencies
A,B ------ CD, C- D, and C-- B
What is the highest normal form of relation R?
A) 2NF
B) 3NF
C) BCNF
D) 1NF
1.6 How would you describe a condition in which one attribute determines another attribute
when neither attribute is part of the primary key
A) Partial dependency
B) Transitive dependency
C) Full Functional dependency
D) Intransitive Dependency
1.7 Three level architecture of the database system provides
A) Data Dependency
B) Data Manipulation Facility
C) Data Abstraction
D) None of the above
1.8 Log is used by
A) Concurrency Control Component
B) Query Processing Component
C) Recovery Component
D) None of the above
1.9 DROP TABLE statement is a
A) DML statement
B) DDL statement
C) Query statement
D) None of the above
1.10 ON DELETE and ON UPDATE statements are associated with
A) Primary Key
B) Foreign Key
C) Alternate Key
D) Surrogate Key
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.2 Number of participating entity types in a relation defines the degree of the relationship.
2.3 Storing the same data multiple times leads to inconsistency.
2.4 Specialization is the process of creating sub entity types from an existing entity type.
2.5 Logical data independence is capacity to change physical schema without changing
conceptual schema.
2.6 “Primary Key cannot take NULL values”, is known as Key Constraint.
2.7 COUNT operation is an aggregate function.
2.8 Creation of Indexes makes sorting of records operation efficient.
2.9 Database administrator is responsible for performance monitoring of the database system.
2.10 Normalization is a Bottom Up approach for designing databases.
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 Y3.1 When the database is used by the entire A. JOIN operation
organization and support usually hundreds of
users it is called
3.2 Second Normal Form of relations is related to B. IBM company
3.3 DB2 is a database management System of C. System Log
3.4 Information from two or more than two tables can D. Enterprise Database
be combined using
3.5 The condition that each entity instance of an E. Views
entity participates in a relationship is known as
3.6 Users are provided only a selected portion of the F. Union compatibility of relations
complete table using
3.7 Performing set operations on two relations G. Full Functional Dependency
require
3.8 A sub query that executes once for each row in H. Total Participation
outer query is called
3.9 If a transaction T1 is being executed and is using I. Lock
a data item X that data item can not be accessed
by any other transaction T2, T3, … Tn until T1
ends, is known as
3.10 It guarantees the exclusive use of a data item to J. Isolation property
a transaction
K. Disjoint
L. Checkpoints
M. Correlated nested query
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. Data Dictionary B. Exists clause C. Authorized Accesses
D. Having Clause E. Degree F. Group By
G. Select operation H. Access privileges I. Count
J. Rollback K. Project operation L. In operation
M. Serializability
4.1 Condition specified in the ________ is applicable on the results of aggregate function which
is applied to a group of tuples.
4.2 ________ undo any changes since the last Commit.
4.3 ________ is used to check whether a sub query returns any rows.
4.4 ________ is itself a data base of predefined nature.
4.5 Number of attributes in a table defines its ________.
4.6 One of the main responsibilities of database administrator is to allow ________ to the
database.
4.7 Grant/Revoke SQL statements are used to define ________.
4.8 ________ function is used to obtain the number of not null values of an attribute or number
of rows.
4.9 ________ clause is valid only when used in conjunction with one of the SQL aggregate
functions.
4.10 A join operation of two relation states is equivalent to applying Cartesian product of two
relations followed by a ________ on the equality of common attribute values.
PART TWO
(Answer any FOUR questions)
5.
a) What is data redundancy? Explain with the help of an example why it is an inherent featurein the file system?
b) Given the following table:
Project_Code Project_Manager Manager_Phone Manager_Address Project_Bid_Price
21-5Z Harshit Mathur 9945873206 21/5, Karol Bagh, Delhi 345423000.00
25-2D Jagdish Sharma 9872054329 34-A,TagoreTown,
Kanpur, UP
651237000.00
25-5A Gagan Saxena 9316671905 1/304, Vikas Nagar,
Lucknow
321876000.00
25-9T Harshit Mathur 9945873206 21/5, Karol Bagh, Delhi 980562000.00
27-4Q Jagdish Sharma 9872054329 34-A,TagoreTown,
Kanpur, UP
650428000.00
29-2D Harshit Mathur 9945873206 21/5, Karol Bagh, Delhi 820213000.00
31-7P Varsha Deb 9910076842 56, D.B.Gupta Road,
Kolkata
450987000.00
i) What problem would you encounter if you want to produce a listing by city? How would you
solve this problem by altering the table structure?
ii) What data redundancies do you detect? Suggest the change in table schema to solve this
problem.
c) Given the following tables:
DIRECTOR
PLAY
i) Identify the Primary and Foreign Keys of both the tables.
ii) Do the tables exhibit Entity and Referential Integrity Constraints? Explain.
iii) Draw the entity relationship diagram to show the relationship between director and play.
Specify the cardinality and participation constraints also.
(5+[2+2]+[2+2+2])
6. Consider the tables given in Question No. 5. c), write the following queries in SQL.
i) Display the names of plays directed by Faizal Alkazi.
ii) Display the number of plays directed by each director.
iii) Create a view DIR_PLAY based on the above two tables and has fields DIR_NUM,
DIR_NAME, PLAY_NAME.
iv) Write the DDL statement to create the table PLAY. Specify primary and foreign keys also in
the definition. Choose appropriate data type for each attribute.
v) Write SQL statement to insert a tuple (1008, ‘Sara AAkash’, 107) in the table PLAY. What
will be the result of this operation? Explain.
vi) Write SQL statement(s) to change the schema of the table PLAY to add an attribute
Duration which is a composite attribute comprising of hour and minute fields.
(2+2+3+3+2+3)
DIR_NUM DIR_NAME DIR_DOB
100 Arvind Gaur 30/6/43
101 Faizal Alkazi 12/8/50
102 Anuradha Kapoor 21/9/62
PLAY_CODE PLAY_NAME
DIR_NUM
1001 Jivit ya Mrit 102
1002 Bade Bhai Saab 101
1003 Galib in Delhi 102
1004 Safarnama 100
1005 Aadhe Adhure 101
1006 Konark 101
1007 Adhoori Kahani NULL
A7-R4 Page 6 of 6 January, 2013
7.
a) Use the following business rules to draw an entity relationship diagram. Specify cardinality
ratios and participation constraints clearly. You need not specify attributes.
i) A department employs many employees but each employee is employed by only one
department.
ii) Some employees known as ‘rovers’, are not assigned to any department.
iii) A division operates many departments, but each department is operated by one
division.
iv) An employee may be assigned many projects, and a project may have many
employees assigned to it.
v) A project must have at least one employee assigned to it.
vi) One of the employees manages each department, and each department is managed
by only one employee.
vii) One of the employees runs each division and each division is run by only one
employee.
b) An employee table has to store emp_no, emp_name and skills attributes. Attribute skill is a
multi valued attribute. In relational model a table can not have multiple values for an attribute
for one entity instance. Suggest a database schema to store the relevant information about
employees.
c) With the help of an example illustrate the usefulness of
i) Logical Data Independence and
ii) Physical data independence.
(6+3+[3+3])
8.
a) Given a relation R(A,B,C,D,E,F,G) and the following functional dependencies
A--- D; C-- B; AB --- C,E,F and E--- G
i) Identify the primary key of the relation.
ii) Decompose R such that each relation is at least in 2NF.
iii) Decompose R such that each relation is at least in 3NF.
iv) Decompose each relation such that each relation is in BCNF.
b) Explain insertion, deletion and update anomalies with the help of an example.
([2.5x4]+5)
9.
a) Given two tables R and S:
R S
--------------------------------------------- -----------------------------------------------
A B C D E F
--------------------------------------------- ----------------------------------------------
3 1 5 2 10 6
5 6 2 5 7 8
7 2 5 6 5 4
9 3 3 9 3 3
---------------------------------------------- ---------------------------------------------
Find the result of the following relational algebraic statements. Attribute C and D of relations
R and S respectively represent the same attribute.
i) R equi join S
ii) R Left outer join S
iii) R right outer join S
iv) R full outer join S
v) R U S
vi) R-S
b) Explain briefly how checkpoints are used to reduce the amount of operations that are to be
UNDONE in a recovery phase.
c) Write the SQL statement for (i) Creating a user account “student5” (ii) and granting it the
permission for all DML operations.
([1.5x6]+4+2)
No comments:
Post a Comment