CS403- Database Management Systems Solved 2010

MIDTERM  EXAMINATION

Spring 2010

CS403- Database Management Systems (Session - 6)

Time: 60 min

Marks: 38

    

Question No: 1    ( Marks: 1 )    - Please choose one

 Consider the following statements.

A. Conceptual schema which is the result of conceptual design is a logical description of all

data elements and their relationships.

B. Internal level of the database architecture consists of the physical view of the database.

C. External level of the database architecture provides the user view of the database.

With respect to the ANSI/SPARC three level database architecture, which of the above is/are

correct?

       ► Only A.

       ► Only C.

       ► Only A and B.

       ► Only B and C.

   

Question No: 2    ( Marks: 1 )    - Please choose one

 The ER- data model is an example of:

       ► Physical database

       ► Logical database

       ► Relational database

       ► Conceptual database

   

Question No: 3    ( Marks: 1 )    - Please choose one

 Which one of the following E-R diagrams most correctly represents the relationship between Student and Grade entities?

       ►

       ►

       ►

       ►

right answer is 4

   

Question No: 4    ( Marks: 1 )    - Please choose one

 Which of the following constraints enforces referential integrity?

       ► FOREIGN KEY

       ► CHECK

       ► PRIMARY KEY

       ► UNIQUE

   

Question No: 5    ( Marks: 1 )    - Please choose one

 Given are the relations of student and Instructor

Consider the following table obtained using Student and Instructor relations.

Which relational algebra operation could have been applied on the pair of relations Student and Instructor to obtain the above data?

       ► Instructor – Student

       ► Student ∩ Instructor

       ► Instructor ÷ Student

       ► Student – Instructor

   

Question No: 6    ( Marks: 1 )    - Please choose one

 Consider the relation Interview(CandidateNo, InterviewDate, InterviewTime, StaffNo, RoomNo)

and the following functional dependencies.

FD1 : CandidateNo, InterviewDate -> InterviewTime, StaffNo, RoomNo

FD2 : RoomNo, InterviewDate, InterviewTime -> StaffNo, CandidateNo

FD3 : StaffNo, InterviewDate -> RoomNo

Which of the following is correct?

       ► The relation Interview is in 3NF

       ► The relation Interview is in BCNF.

       ► The FD3 violates 3NF.

       ► The FD2 violates 2NF.

   

Question No: 7    ( Marks: 1 )    - Please choose one

 Which of the following is INCORRECT statement concerning the database design process?

       ► During requirements collection and analysis phase, one can gather the data requirements of database users.

       ► By referring to a high level data model, it is possible to understand the data requirements of the users, entity types, relationships and constraints.

       ► Transformation of the high level data model into the implementation data model is called logical design or data model mapping.

       ► During the logical design phase of internal storage structures, access paths and file organization for the database files are specified.

   

Question No: 8    ( Marks: 1 )    - Please choose one

 Consider the following diagram depicting a kind of a relationship type where X and Z are entities and Y is a relationship type:

Select the correct statement among the following on the above diagram.

       ► The relationship type Y is of cardinality ratio 1 : N.

       ► The diagram depicts existence dependencies.

       ► The participation of X in the Y relationship type is total.

       ► The participation of Z in the Y relationship type is partial.

   

Question No: 9    ( Marks: 1 )    - Please choose one

 Identify the correct statement.

       ► Entity integrity constraints specify that primary key values can be composite.

       ► Entity integrity constraints are specified on individual relations.

       ► Entity integrity constraints are specified between weak entities.

       ► When entity integrity rules are enforced, a tuple in one relation that refers to another relation must refer to an existing tuple.

   

Question No: 10    ( Marks: 1 )    - Please choose one

 Identify the correct statement.

       ► Referential integrity constraints check whether the primary key values are unique.

       ► Referential integrity constraints check whether an attribute value lies in the given range.

       ► Referential integrity constraints are specified between entities having recursive relationships.

       ► When Referential integrity rules are enforced, a tuple in one relation that refers to another

relation must refer to an existing tuple.

   

Question No: 11    ( Marks: 1 )    - Please choose one

 Identify the correct way to implement one-to-one relationship in tables?

       ► by splitting the data into two tables with primary key and

foreign key relationships.

       ► as a single table and rarely as two tables with primary

and foreign key relationships.

       ► using a junction table with the keys from both the tables

forming the composite primary key of the junction table.

       ► by creating two separate tables

   

Question No: 12    ( Marks: 1 )    - Please choose one

 A collection of related data is

       ► Logical model

       ► Database

       ► Data

       ► Relational model

   

Question No: 13    ( Marks: 1 )    - Please choose one

 A collection of concepts that can be used to describe the structure of a database

       ► Database

       ► DBMS

       ► Data model

       ► Data

   

Question No: 14    ( Marks: 1 )    - Please choose one

 A superkey that does not contain a subset of attributes that is itself a superkey is called a ____.

       ► candidate key

       ► primary key

       ► superkey

       ► secondary key

   

Question No: 15    ( Marks: 1 )    - Please choose one

 As part of database naming conventions, attribute names should use suffixes such as ID, NUMBER or CODE for the _______.

       ► primary key   

       ► foreign key

       ► index

       ► determinant

   

Question No: 16    ( Marks: 1 )    - Please choose one

 Which of the following concepts is applicable with respect to 2NF?

       ► Full functional dependency

       ► Any kind of dependency

       ► Transitive dependency

       ► Non-transitive dependency

   

Question No: 17    ( Marks: 2 )

 State the two conditions which are imposed on candidate key?

1.                   identifies the entity instances uniquely, in case of super key,

2.                   No proper subset of candidate key is a key.

Question No: 18    ( Marks: 2 )

 What is the importance of determining minimum cardinality in a relationship while designing database?

It is important to determine the minimum cardinality because it defines the way a database system will be implemented.

It shows us that how many instance of an  entity can be placed in another relation at least.

Question No: 19    ( Marks: 2 )

 What do you know about Insertion anomaly?

It is wrong state of database. It  occurs when a new record is inserted in the relation. In this case the user cannot insert a fact about an entity until he has an additional fact about another entity.

   

Question No: 20    ( Marks: 3 )

 Why do the relational data model considered as simple?

Because  there is one structure and that is a relation (table).

Plus this  single structure is very easy to understand. Due to which  a user of a moderate knowledge can understand it easily.

It has strong math foundation which gives it extra strength.

   

Question No: 21    ( Marks: 3 )

 Name the three different kinds of anomalies which can be eliminated through normalization?

1.                   Update anomaly  .

2.                   Delete anomaly

3.                   Insert anomaly

.   

Question No: 22    ( Marks: 5 )

 The following diagram describes a part of an ER diagram.

Considering the above diagram, which of the given statements are True and which are False.

i-Entity2 is a weak entity. True

ii-Cardinality ratio for Entity1:Entity2 in Rel1 is 1:N.  True

iii-Attrib6 represents an attribute which is having composite nature. False

iv-Attrib3 is a kind of a derived attributes. True

v-Entity2 is participating totally in the Rel1 relationship. True

   

Question No: 23    ( Marks: 5 )

 Consider the relation R with four attributes A,B,C and D and the functional dependencies

(A,B) -> (C,D) and

C -> D .

a)The above relation is a normalized relation upto which normal form?

2NF

b)Write the PK of relation R