Saturday, 24 September 2016


July, 2015

(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 the following is NOT a basic element of the E-R model?
A) Entities
B) Attributes
C) Relationship
D) Primary keys
1.2 The result of the operation RUS includes all tuples that are
A) Either in R or S
B) Both in R and S
C) Both A) and B)
D) None of the above

1.3 Which one of the following is an example of 1:N relationship
A) Course-has-Student
B) Teacher-teaches-students
C) Teacher-heads-Department
D) None of the above
1.4 Following operation is used to update the structure of the database table
A) Alter
B) Update
C) Change
D) None of the above
1.5 In ER modeling weak entity is identified for an owner entity using
A) Primary key
B) Super key
C) Candidate key
D) Partial key
A7-R4 Page 2 of 5 July, 2015
1.6 Create table is a
A) DDL Command
B) DML command
C) Both A) and B)
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
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 Values of Key attributes cannot be NULL is a requirement of
A) Key Constraint
B) Entity Integrity Constraint
C) Referential Integrity Constraint
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 In an ER diagram single line represents partial participation of an entity in a relationship.
2.2 Primary Key of a relation is minimum super key.
2.3 It is not allowed to insert duplicate records in database.
2.4 Placing Null values for an attribute is avoided as they do not have any specific meaning.
2.5 Group by is an example of aggregate function.
2.6 Modifying the database without ensuring that transaction will commit may leave database in
a consistent state.
2.7 Tables in second normal form (2NF) eliminate all hidden dependencies.
2.8 A database is ordered collection of data.
2.9 A relation cannot have more than one key.
2.10 Union 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 High level data model                                              A. Multivalued
3.2 Joint operation on two tables is a subset of             B. Modify
3.3 Attribute of an entity having more then one           C. Non-trivial FD,s involving  value
                                                                                           prime attribute on RHS
3.4 To suppress any duplicate values in any                 D. Third normal form
column this clause is used
3.5 A relational database in 3NF and may                   E. Update
still have undesirable data-redundancy because
3.6 Elimination of transitive dependency on key          F. Conceptual level
3.7 When an entity is related to itself                        G. Cross-Product
3.8 X ® Y if X determines Y uniquely                     H. Distinct
3.9 Relationship of a weak entity with its                 I. ER Model
 owner entity
3.10 Command used to modify record of the            J. Recursive relationship
                                                                                  K. Composite attribute
                                                                                   L. Functional dependency
                                                                                   M. Identifying relationship

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. 2NF                                 B. Alternate Keys                                C. Range
D. External hashing              E. Ordered                                          F. Internal hashing
G. Key                                 H. Dependency preservation               I. 1NF
J. Column                            K. Relation                                          L. BCNF
M. Unordered

4.1 In the ________ normal form, a composite attribute is converted to individual attributes.
4.2 All Candidate Keys excluding the Primary Key are known as ________.
4.3 Target space is made of buckets in ________.
4.4 The term attribute refers to a ________ of a table.
4.5 Functional Dependencies are the types of constraints that are based on ________.
4.6 ________ is stricter than 3NF.
4.7 Primary index is created on a file when file is ________.
4.8 ________ is an association among two or more entities.
4.9 ________ is a property of lossless decomposition.
4.10 File of ________ records are called heap files.

(Answer any Four Questions)
a) What are views? How is it related to data Independence?
b) What is recursive relationship? Explain with example.
c) What is identifying relationship? When is it used?
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)
i) Find the names of the students who have enrolled in course Database Systems.
ii) Find all the courses which are NOT using room R128.
iii) Find all the students who are in any of the courses that professor “Ajay Kumar” teaches.
b) For a relational scheme R(ABCD) find the canonical cover for given set of FD,s.
F= {A®BC, B®C, A®B, AB®C}
a) Consider the following set of functional dependencies for a relation R(ABCDE):
F= {AB®C, CD®E, DE®B}
What is the key of this relation?
b) Given the following relational instance,
1 2 3
3 1 2
1 1 2
4 1 5
Which of the following functional dependencies are satisfied?
i) AB®C
ii) C®A
iii) AC®B
c) A relation ADDRESS is defined as follows.
ADDRESS(Name, Street, City, State, Postal_code)
Where name is unique, and for any given postal code, there is just one city and state.
i) Give a set of FDs for this relation.
ii) Is ADDRESS in 3NF or 2NF? Explain why? If it is not in 3NF, normalize it into 3NF
a) Differentiate between Deferred database modification and immediate database modification.
b) What is a checkpoint and when does it occur?
c) How privileges are granted and revoked in a secured database? Explain with example.
A7-R4 Page 5 of 5 July, 2015
a) A college contains many departments. Each department can offer any number of courses. Many
instructors can work in a department. An instructor can work only in one department. For each
department there is a Head. An instructor can be head of only one department. Each instructor
can take any number of courses. A course can be taken by only one instructor. A student can
enroll for any number of courses. Each course can have any number of students.
Draw an ER diagram for this schema that takes into account all the assertions given.
b) Discuss the role of high level data model in the database design process.

Next Set