Solved Paper MIDTERM EXAMINATION CS403- Database Management Systems

MIDTERM EXAMINATION

Fall 2008

CS403- Database Management Systems (Session - 1)

Dated 31-05-2010

Time: 60 min

Marks: 38

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

Which of the following functions are NOT performed by a database administrator?

► Planning, designing and implementing database systems

► Establishing standards and procedures for database systems

► Communicating with database users

► Allocation of storage locations and data structures

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

Identify the factor which enforces a relation in 3NF?

►

Every non-key attribute is fully functionally dependent on primary key

►

Every non-key attribute is partially dependent on super key

►

The domain of attribute must include only atomic values.

►

Every non-key attribute is non-transitively dependent on primary key.

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

Consider two sets A and B. A contains 2 elements and B contains 3. How many elements do their cartesian product

contains?

► 6

► 9

► 5

► 4

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

Identify the operation which is NOT one of the parts of the five basic set operations in relational algebra?

► Join

► Union

► Cartesian Product

► Set Difference

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

Consider the following statements.

A. An entity integrity constraint states that no primary key value can be null.

B. A referential integrity constraint is specified between two relations.

C. A foreign key cannot be used to refer to its own relation.

Identify which of the above statements is/are correct.

► Only A (may be this)

► Only B

► Only B and C

► Only A and B

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

User rights information is stored in

► Physical database

► Catalog

► Logical database

► Buffer

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

Given are the relations of student and Instructor

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: 8 ( Marks: 1 ) - Please choose one

Which one is the corrected way to implement M to M relation which designing data base

► using junction table with keys

► By splitting data into tables with PK and FK

► As a single table rarly as to tables with PK and FK

►By creating three tables and linking them through PK and FK

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

Which enforce the a relation into 2NF User rights information is stored in

► ------------------

► --------

► ----------------

► ------

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

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

► Full functional dependency

► Any kind of dependency

► Transitive dependency

► Partial functional dependency

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

Description on particular collection of data using data model

► Schema

► Relation

►Data base

► None of the above

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

Which one is true regarding relation?

► Every attribute value Non atomic

► Attribute in table may not have unique name

►table order of the column is relevant

► the order of the rows is irrelevant

Question No: 17 ( Marks: 2 )

Label the following diagram

1.Data store

2.Entity

Question No: 18 ( Marks: 3 )

What is the degree of relation?

Degree of a Relation

We will now discuss the degree of a relation not to be confused with the degree of a

relationship. You would be definitely remembering that the relationship is a link or

association between one or more entity types and we discussed it in E-R data model.

However the degree of a relation is the number of columns in that relation. For

Question No: 19 ( Marks: 3 )

Name three kind of basic binary operation on relational algebra

Ans:

Union

Set operation

Cartesian product

Question No: 20 ( Marks: 2 )

What is the data type

Data type is defined as a set of values along with the operations that can

be performed on those values. Some common data types are Integer, Float, Varchar,

Char,

String, etc.

Question No: 21 ( Marks: 2 )

What is the importance of minimum cardinality in relation while designing the database.

Minimum Cardinality:

This is a very important point, as minimum cardinality on one side needs special

attention. Like in previous example an employee cannot exist if project is not assigned.

So in that case the minimum cardinality has to be one. On the other hand if an

instance of EMPLOYEE can exist with out being linked with an instance of the

PROJECT then the minimum cardinality has to be zero. If the minimum cardinality is

zero, then the FK is defined as normal and it can have the Null value, on the other

hand if it is one then we have to declare the FK attribute(s) as Not Null. The Not Null

constraint makes it a must to enter the value in the attribute(s) whereas the FK

constraint will enforce the value to be a legal one. So you have to see the minimum

cardinality while implementing a one to many relationship.

Question No: 22 ( Marks: 5 )

What is the Cartesian product operation algebra. What is the result of Cartesian result

product.

Cartesian product:

The Cartesian product needs not to be union compatible. It means they can be of

different degree. It is denoted by X. suppose there is a relation R with attributes (A1,

A2,...An) and S with attributes (B1, B2……B

R X S

The resulting relation will be containing all the attributes of R and all of S. Moreover,

all the rows of R will be merged with all the rows of S. So if there are m attributes and

C rows in R and n attributes and D rows in S then the relations R x S will contain m +

n columns and C * D rows. It is also called as cross product. The Cartesian product is

also commutative and associative. For Example there are two relations COURSE and

STUEDNT

n). The Cartesian product will be:

Cource

crId courseTitle

C3456 Database Systems

C4567 Financial Management

C5678 Money & Capital Market

STUDENT

stId

stName

S101 Ali Tahir

S103 Farah Hasan

COURSE X STUDENT

crId courseTitle stId

stName

C3456 Database Systems S101 Ali Tahir

C3456 Database Systems S103 Farah Hasan

C4567 Financial Management S101 Ali Tahir

C4567 Financial Management S103 Farah Hasan

C5678 Money & Capital Market S101 Ali Tahir

C4567 Financial Management S103 Farah Hasan

Question No: 23 ( Marks: 5 )

Shortly describe Procedural DML and Non procedural DML also give the alternate

names.

Procedural Languages:

These are those languages in which what to do and how to do on the database is

required. It means whatever operation is to be done on the database that has to be told

that how to perform.

Non -Procedural Languages:

These are those languages in which only what to do is required, rest how to do is done

by the manipulation language itself.

Structured query language (SQL) is the most widely language used for manipulation

of data. But we will first study Relational Algebra and Relational Calculus, which are

procedural and non – procedural respectively.