Sample Paper

FINALTERM EXAMINATION
Fall 2022
CS312 - Database Modelling and Design

Time: 90 min
Marks: 60

Question No: 1 (Marks: 01) - Please choose the correct option

Which of the following is a(n) irregularity and is different from the expected or normal
state?
A. Data Dictionary
B. Anomaly
C. Table
D. Entity

Question No: 2 (Marks: 01) - Please choose the correct option

Which of the following is NOT a type of database anomaly?

A. Insert
B. Delete
C. Cancel
D. Update

Question No: 3 (Marks: 01) - Please choose the correct option

Functional Dependencies are the types of constraints that are based on which of the
following concept?
A. Key
B. Functions
C. Relationships
D. Tables
Question No: 4 (Marks: 01) - Please choose the correct option

Which of the following is normalized after it has been organized?

 A. Primary Key
B. Database
C. Attribute
D. Row
Question No: 5 (Marks: 01) - Please choose the correct option

In which normal form, no two Rows of data must contain repeating group of
information?

A. First Normal Form

B. Second Normal Form
C. Third Normal Form
D. Ten Normal Form

Question No: 6 (Marks: 01) - Please choose the correct option

Which of the following is not a type of Normal Form?

A. First Normal Form

B. Second Normal Form
C. Third Normal Form
D. Ten Normal Form

Question No: 7 (Marks: 01) - Please choose the correct option

__________________is the process in which redundancy is added in a database with the

purpose of improving the database performance.

A. Normalization
B. Denormalization
C. Standardization
D. Duplication

Question No: 8 (Marks: 01) - Please choose the correct option

Which of the following is NOT DDL command?

A. Create
 B. Insert
C. Alter
D. Drop

Question No: 9 (Marks: 01) - Please choose the correct option

DML stands for:

A. Data Manual Language

B. Data Modify Language
C. Data Manipulation Language
D. Data Merge Language

Question No: 10 (Marks: 01) - Please choose the correct option

The __________ Statement is used to undo transactions that have not already been saved
to the database.

A. Undo
B. Rollback
C. Commit
D. Delete

Question No: 11 (Marks: 01) - Please choose the correct option

In Select statement, * means ___________.

A. Some columns
B. Important columns
C. All the columns
D. Limited columns

Question No: 12 (Marks: 01) - Please choose the correct option

GRANT is __________ command.

A. DDL
B. DML
 C. DCL
D. DAL

Question No: 13 (Marks: 01) - Please choose the correct option

Which of the following is NOT a component of an Entity Relationship Diagram (ERD)?

A. Entity
B. Data Flow
C. Attribute
D. Relationship

Question No: 14 (Marks: 01) - Please choose the correct option

The first step in creating the ERD is to extract __________ from the given scenario.

A. Relationships
B. Primary Key
C. Entities
D. Data Flows

Question No: 15 (Marks: 01) - Please choose the correct option

What is the full form of SQL?

A. Structured Query List
B. Structure Query Language
C. Sample Query Language
D. Standard Query List

Question No: 16 (Marks: 01) - Please choose the correct option

Which of the following is not a valid SQL data type?

A. Number
B. Varchar2
C. Character
D. Date
Question No: 17 (Marks: 01) - Please choose the correct option

By default, “order by” clause sorts data in ___________ order.

A. Descending
B. Ascending
C. Level
D. None of the given option

Question No: 18 (Marks: 01) - Please choose the correct option

Which of the following clause is not part of the basic SELECT statement?
A. Select
B. From
C. Where
D. Group By
Question No: 19 (Marks: 01) - Please choose the correct option

Choose valid SQL statement for the following scenario?

Scenario: Select CID and CName from Customer Table

A. Select CID, CName from table Customer

B. Select * From Customer
C. Select CID, CName from Customer
D. Select All FROM Customer

Question No: 20 (Marks: 01) - Please choose the correct option

___________clause cannot be used in the aggregated/grouped data.

A. Having
B. Where
C. Group By
D. None of the given

Question No: 21 (Marks: 01) - Please choose the correct option

The wildcard character % means:

 A. Zero or more characters
B. Exactly one character
C. Zero and One character
D. One or more characters

Question No: 22 (Marks: 01) - Please choose the correct option

Mathematically, A cartesian product is a ____________operation.

A. Unary
B. Binary
C. Ternary
D. Additive

Question No: 23 (Marks: 01) - Please choose the correct option

A_____ is a query that retrieves rows from more than one table or view:

A. Start
B. End
C. Join
D. Combine

Question No: 24 (Marks: 01) - Please choose the correct option

Which of the following creates a virtual relation for storing the query?

A. Function
B. View
C. Procedure
D. Virtual

Question No: 25 (Marks: 01) - Please choose the correct option

Which of the following is the syntax for views where v is view name?
A. Create view v as “query name”;
B. Create “query expression” as view;
C. Create view v as “query expression”;
 D. Create view “query expression”;

Question No: 26 (Marks: 01) - Please choose the correct option

A ____________contains the definitions of all schema objects in the database.

A. Data dictionary
B. Data Definition
C. Object Dictionary
D. Database Definition

Question No: 27 (Marks: 01) - Please choose the correct option

IN SQL, __________ are one-word command that return a single value.

A. Table
B. Functions
C. Column
D. View

Question No: 28 (Marks: 01) - Please choose the correct option

A ______ is defined as a query within a query.

A. Subquery
B. Function
C. InQuery
D. MetaQuery

Question No: 29 (Marks: 01) - Please choose the correct option

_____________ produces unique auto number values on demand.

.
A. Unique
B. AutoNo
C. Sequence
D. SerialNo
Question No: 30 (Marks: 01) - Please choose the correct option

What is true about index?

A. Indexes are special lookup tables that the database search engine can use to speed
up data retrieval.
B. Indexes are special lookup tables that the database search engine can use to speed
up data deletion.
C. Indexes can be created or dropped with an effect on the data.
D. An index helps to speed up insert statement.

Question No: 31 (Marks: 01) - Please choose the correct option

What will the following statement display?

SELECT * FROM user_indexes;

A. It will display only one index created by currently logged in user schema.
B. It will display all the indexes created by currently logged in user schema.
C. It will display specific columns of indexes created by currently logged in user
schema.
D. It will display only one column of indexes created by currently logged in user
schema.

Question No: 32 (Marks: 01) - Please choose the correct option

Which of the following is correct CREATE INDEX Command?

A. INSERT INDEX index_name ON table_name;

B. INSERT INDEX index_name ON database_name;
C. CREATE INDEX index_name ON database_name;
D. CREATE INDEX index_name ON table_name;

Question No: 33 (Marks: 01) - Please choose the correct option

Which of the following property of transaction ensures that there must be no state in a
database where a transaction is left partially completed?

A. Atomicity
B. Consistency
 C. Isolation
D. Durability

Question No: 34 (Marks: 01) - Please choose the correct option

____________ property of a transaction states that all the steps during the execution of
transactions are done without interference and no transaction will affect the existence of
any other transaction.

A. Atomicity
B. Consistency
C. Isolation
D. Durability

Question No: 35 (Marks: 01) - Please choose the correct option

__________ is a mechanism to avoid multiple users to access the shared data at the same
time.

A. Keys
B. Locks
C. Tables
D. Transactions

Question No: 36 (Marks: 01) - Please choose the correct option

_________ specifies how many values of the sequence the database pre-allocates and
saves in memory for the faster access.

A. CYCLE
B. CACHE
C. No CYCLE
D. No CACHE

Question No: 37 (Marks: 01) - Please choose the correct option

_________ is use to indicate that the sequence continues to generate values after reaching
either its maximum or minimum value.
 A. CACHE
B. CYCLE
C. MINVALUE
D. MAXVALUE

Question No: 38 (Marks: 01) - Please choose the correct option

Which of the following is a read-only set of tables that provides information about its
associated database?

A. Index
B. Data dictionary
C. SQL
D. Lock

Question No: 39 (Marks: 01) - Please choose the correct option

Which of the following is a situation in database in which two or more transactions are
waiting for one another to give up locks?

A. Simple Lock
B. Deadlock
C. Exclusive Lock
D. Shared Lock

Question No: 40 (Marks: 01) - Please choose the correct option

“Set the lock wait” is:

A. Deadlock Avoidance Technique

B. Deadlock Prevention Technique
C. Deadlock Detection Technique
D. Exclusive Lock

Question No: 41 (Marks: 03)

Write any three DDL commands of SQL.

Answer
Question No: 42 (Marks: 03)

Write the general syntax of the following:

a. LIKE operator
b. Between operator

Answer

Question No: 43 (Marks: 03)

Consider Employee table with attributes “EmpID”, and “Salary”. Write a query to display
information of all those employees who are earning maximum salary using sub-query
method.
.
Answer

Question No: 44 (Marks: 03)

Why Views are used in SQL? Write any two points.

Answer

Question No: 45 (Marks: 03)

Write any three properties of Transaction.

Answer
Question No: 46 (Marks: 03)

Briefly explain two levels of locks in database.

Answer

Question No: 47 (Marks: 05)

Covert following relation into Second Normal Form. The scenario shows books borrowed
by students from Library.

Stu_Book(StudentID,StudentName,BookID,BookTitle,BorrowingDetail)

Dependencies:

- StudentName depends upon StudentID.

- BookTitle depends upon BookID.
- BorrowingDetail depends upon StudentID and BookID.

Answer

Question No: 48 (Marks: 05)

Consider given table Customer, and for the following.

1. Write SQL statement to create Table Customer with columns “CID”, “CName”
and “City”. Set CID as Primary key.
2. Write SQL statement to insert Given Data in Customer Table.

CID CName City

 C1 Asad Islamabad

C2 Bilal Karachi

Answer

Question No: 49 (Marks: 05)

Consider the following Tables:

Table: Student
SID SName CID
S1 Ali CS312
S2 Nida CS201
S3 Ahsan CS312
S4 Shazia CS201

Table: Course
CID Title
CS201 Introduction to Programming
CS302 Digital Logic Design
CS301 Data Structures
CS312 Database Modelling and Design

Perform Left Outer Join on above tables and provide resultant table.

Answer

Question No: 50 (Marks: 05)

Explain two common approaches of deadlock detection.

Answer