Monday, 26 September 2016

July, 2014
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 In DBMS the structure of a data file is stored separately from the access program. This
feature allows us to enforce
A) Program-Operation Independence
B) Program-data Independence
C) Physical Data Independence
D) Logical Data Independence
1.2 In DBMS Protection includes
A) System Protection against software/hardware malfunction
B) Security against unauthorized access
C) both A) and B)
D) None of the above

1.3 Which one of the following is an example of 1:1 relationship?
A) Student-opt-Course
B) Teacher-teaches students
C) Teacher-heads-Department
D) None of the above
1.4 Roles name are significant if a relationship is:
A) Binary
B) Ternary
C) Recursive
D) None of the above
1.5 In Database System language used to specify conceptual and internal schema is called
A) DML
B) DDL
C) VDL
D) None of the above
1.6 ER model is an example of
A) Representational data model
B) Network data model
C) High level data model
D) None of the above
1.7 Single entity having more than one role in the same relationship is an example of
A) Binary relationship
B) Identifying relationship
C) Recursive relationship
D) Ternary relationship
1.8 Maximum number of relationship instances that an entity can participate in a relationship is
called
A) Degree
B) Cardinality ratio
C) Min-max constraints
D) None of the above
1.9 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.10 To make changes to the data in the database the following command is used
A) Alter
B) Update
C) Modify
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 Weak entities do not have key attributes.

2.2 Every super key of a relation is the candidate key.
2.3 It is allowed to insert duplicate records in database.
2.4 Null values can be inserted for any attribute for which values are not known.
2.5 MIN is an example of aggregate function.
2.6 The DISTINCT clause is used with SELECT to suppress any duplicate values in any
columns.
2.7 Views are database objects whose data are derived from another table. It does not contain
it’s own data.
2.8 A database is ordered collection of data.
2.9 A relation can have more than one key.
2.10 SELECT is a relational algebra operation.

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 Command used to modify structure of the table                     A. DDL
3.2 It gives physical representation of database                           B. Schema
3.3 An object with a physical or conceptual existence                C. Update
3.4 Number of participating entities in a relationship                 D. Alter
3.5 Description of database                                                         E. Count
3.6 This function determines the number of rows or                   F. Internal level
non-NULL columns values

3.7 Inserting NULL values for key attribute is a                         G. Metadata
violation of
3.8 X YZ => X Z                                                                      H. Entity integrity constraint
3.9 Language used to define structure of database                     I. Projectivity
3.10 Information about data                                                       J. Entity
                                                                                                   K. Degree
                                                                                                   L. Conceptual level
                                                                                                  M. Key constraint

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. Views                            B. SELECT                                               C. Range
D. WHERE                        E. SELF JOIN                                          F. CARTESIAN JOIN
G. SET                               H. Model based                                         I. 2NF
J. LIKE                              K. Schema based                                       L. 3NF
M. Conceptual level

4.1 Removing partial dependencies is a requirement of ________.
4.2 Querying database tables is done by using the ________ statement of SQL.
4.3 Pattern matching is done through the ________ operator.
4.4 The BETWEEN operator is used to specify a ________ of values.
4.5 Joining condition is specified in the ________ clause of the select.
4.6 Joining a table with itself is known as ________.
4.7 Without any joining condition the join becomes ________.
4.8 ________ operator combines two or more queries into one result.
4.9 Constraints inherent in data model are called ________ constraints.
4.10 ________ are virtual tables.

PART TWO
(Answer any FOUR questions)
5.
a) What is the difference between logical data independence and physical data independence?
Explain with example.
b) What is participation role? When is it necessary to use role names in the description of a
relationship?
c) Define the concept of weak entity. When is it used?
([5+2]+4+4)
6.
a) Consider the following database tables and answer queries using SQL.
Student (snum, sname, major, age)
Faculty (fid, fname, deptname)
Course (cname, meets_at, room, fid)
Enrolled (snum, cname)
1. Find the names of the students who have enrolled in course Database Systems.
2. Find all the courses which are NOT using room R128
3. Find all the students who are in any of the courses that professor “Akshay Kumar”
teaches.
b) What is referential Integrity constraint? Explain with example.
(9+6)
7.
a) Consider the following set of functional dependencies for a relation R(ACDEH):
F= {A C, AC D, E AD, E H}
What is the key of this relation?
b) Given the following relational instance,
A B C
1 2 3
3 1 2
1 3 2
4 1 5
Which of the following functional dependencies are satisfied?
i) A B
ii) B C
iii) A C
c) Consider the relation R(ABCDEFGHIJ) and set of functional dependencies
F ={ AB C,A DE, B F, F GH, D->IJ }
Decompose the relation into 2NF and 3NF.
(4+3+8)
A7-R4 Page 5 of 5 July, 2014
8.
a) What are the drawbacks of UNDO and REDO logging? Explain in brief. Suggest a solution
to overcome these drawbacks.
b) Consider the relation schema R(ABCDEF) with the following set of functional dependencies:
A B ,AE F
CD A ,CE F
BC D
Consider following decomposition:
R1(AEF) ,R2( AB), and R3(ACD).
Is this lossless join decomposition with respect to ABCDEF and the set of functional
dependencies?
(9+6)
9.
a) Given the following assertions for a relational database that represents the current term
enrollment at a large university. There are 200 instructors, 500 courses, and 5000students.
Use as many ER constructs as you can to represent the true semantics of the problem.
Assertions:
• An instructor may teach one or more courses in a given term (average is 2.0 courses).
• An instructor must direct the research of at least one student (average = 2.5 students).
• A course may have none, one, or two prerequisites (average = 1.5 prerequisites).
• A course may exist even if no students are currently enrolled.
• All courses are taught by exactly one instructor.
• The average enrollment in a course is 30 students.
• A student must select at least one course per term (average = 4.0 course selections).
• Draw an ER diagram for this schema that takes into account all the assertions given.
b) How privileges are granted and revoked in a secured data base? Explain with example.
(9+6)

Next Set