UNIT 1 : File Organization

Objective Questions
1. Which of the following is not the type of file organization?
a. heap
b. sort
c. hash
d. sparse

2. A clustering index is defined on the fields which are of type ____________

a. non-key and ordering
b. key and ordering
c. non-key and non-ordering
d. key and non-ordering

3. _________describes what data items and possible processing operations can be performed on a file.
a. Logical file
b. physical file
c. both
d. none of these

4. In _______index, index records are created for only some of the records.
a. sparse
b. dense
c. both
d. none of the above

5. A sequential file that is indexed is called as _________file.

a. heap
b. sorted
c. hash
d. indexed-sequential

Short Answer Questions

1. Define variable length record. (L1: Remember)
2. Define record type. (L1: Remember)
3. What is ISAM? (L1: Remember)
4. What do you mean by logical and physical file? (L2 : Understand)
5. Explain difference between fixed length and variable length record. (L4: Analyze)
6. Give the different types of file organization. (L2 : Understand)
7. Define clustered index. (L1: Remember)

Long Answer Questions

1. Explain how insertion and deletion are done in B+ tree index. (L4:Analyze)
2. Differentiate between ISAM and B+ tree structure. (L4: Analyze)
3. What do you mean by index organization? How is it implemented using dense index and sparse
index? (L5: Evaluate)
4. What is mean by fixed and variable length record? Explain with an example. (L2: Understand)
5. What is the problem in ISAM? How it is overcome by B+ tree index structure? (L5: Justify)
6. Explain the concept of overflow pages in ISAM. (L2: Understand)
7. Can a secondary index be a sparse index? Justify your answer. (L5: Evaluate)

UNIT 2: Introduction of DBMS

Objective Questions

1. A set of logically related record forms a _________

a. database
b. file
c. record
d. none

2. ______________ is the capacity to chnage the schema at one level of database system without having
to chnage schema at next higher level.
a. Logical data independence
b. Physical data independence
c. Data independence
d. None of the above

3. The language that requires a user to specify the data to be retrived without specifying exact to get it is
a. procedural DML
b. non-procedural DML
c. procedural DDL
d. non-procedural

4. Data is better organized and is easily accessible by using __________

a. DBMS
b. FMS
c. EMS
d. All of the above

5. In ________model, we have a strict parent-child relationship only.

a. hierarchical
b. network
c. relational
d. all

6. ___________ is a collection of different fields that provide information on an entity.

a. field
b. record
c. data item
d. none of the above

Short answer questions

1. Define : instance of database. (L1: Remember)
2. Define schema. (L1: Remember)
3. What is DBMS? (L1: Remember)
4. What are the types of DBMS Query language? (L1: Remember)
5. What is mean by data isolation? (L1: Remember)
6. Explain data redundancy. (L2 :Understand)
Long answer questions
1. What is DBA? Explain any two functions of DBA. (L2: Understand)
2. What are the advantages and disadvantages of DBMS? (L2: Understand)
3. What is data model? Write a short note on relational data model. (L2: Undrerstand)
4. Differentiate between File system and Database management system. (L5: Evaluate)
5. Write a note on Data Models? (L2: Understand)
6. Differentiate between logical and physical data independence. (L5: Evaluate)
UNIT 3: Conceptual Design (E-R Model)

Objective questions
1. In ER diagram, weak entity is represented by __________
a. double rectangle
b. rectangle
c. double ellipse
d. dashed ellipse

2. In entity-relationship diagrams double ellipe represents ____________

a. multivalued attribute
b. derived attribute
c. weak entity
d. primary key

3. E –R model was developed by _________

a. Peter Chen
b. E. F Codd
c. Gordon everest
d. none of the above

4. _______is a bottom up process.

a. Specialization
b. Generalization
c. Aggregation
d. None of the above

5. Weak entity set contains a __________

a. Primary key
b. Foreign key
c. dicscriminator
d. none of the above

Short answer questions:

1. Define weak entity set. (L1: Remember)
2. What do you mean by strong and weak entity sets? (L1: Remember)
3. Define Generalization. (L1: Remember)
4. What is mean by Specialization.(L2: Understand)
5. Explain the term Association. (L2: Understand)
6. Define Relationship. (L1: Remember)

Long answer questions

1. Explain any four ER notations with example. (L2:Understand)
2. Write a short note on key constraints. (L2:Understand)
3. Define constraint state and explain any two types of constraints. (L2:Understand)
4. Define key. Explain the following terms: (L1: Remember)
a. Primary key b. Foreign key
c. Super key d. Candidate key
5. Explain any three types of attributes of entity relationship model in detail. (L4: Analyze)
6. Consider following relational schema (L6: Create)
Game(gno, gname, no.of players, coach name, captain)
Player(pno, pname)
Game and Player are related with many-many.
Draw an ER diagram.
7. Consider the following Relational Database 'Star' is an agency for flat booking and it has number of
builders and agents who are joinly working. A customer can get a flat for residential or commercial
purpose. If customer is approched through an agent, the agency and builders giving some commision to
the agent. Agent shows various flats and builders are giving some commissions to the agent. Agent
shows various flats and sites within various locations. Study above case end: (L6: Create)
a. Design an ER diagram
b. Identity all entities
8. A motor vehicle branch administers driving tests and issues driver's licence. Any person who wants a
driver's license must first take learner's exam at any motor vehicle branch in the province. If he/she fails
the exam, he can take the exam at any time after a week of the failed exam date, at any branch. If he
passes the exam, he is issued a license (type = learners) with a unique license number. The person may
take his drivers exam at any branch any time before the learner's license expiry date (which is ususally
set at six months after the license date). If he passes the exam, branch him a driver's license. (L6: Create)
Identify the entities and attributes.
Draw ER diagram.

UNIT 4: Structure of Relational Database

Objective Questions
1. In relationship model, relations are termed as ____________.
a. tuples
b. attributes
c. tables
d. rows

2. Relational data model stores data in the form of __________

a. row
b. column
c. relation
d. table

3. ____________key cannot contain duplicate or null values.

a. Primary
b. Foreign
c. Unique
d. None of the above

4. A table is a _________ dimensional structure.

a. two
b. three
c. four
d. five

5. ___________is a set of possible values for one or more attributes.

a. Attribute
b. Domain
c. Column
d. none of the above

6. __________integrity is also known as Integrity Rule 1.

a. Referential
b. Entity
c. Table
d. none of the above

Short answer questions:

1.Define relationship set. (L1: Remember)
2. Define tuple. (L1: Remember)
3. What do you mean by a domain?(L2: Understand)
4. The primary key constraint and unique constraint mean one and the same. Justify. (L5: Evaluate)
5. Define constraint. State the different types of constraints. (L1: Remember)
6. Write a short note on referential integrity constraint. (L2: Understand)

Long answer questions

1. Define constraint state and explain any two types of constraints. (L2 :Understand)
2. Discuss various types of keys used in RDB. (L2:Understand)
3. Define the terms : (L1: Remember)
a. Primary key
b. Foreign key
c. Super key
d. Candidate key
4. Write a short note on integrity constraint on database design. (L2 ;Understand)
5. Convert the above E-R diagram into relational model

Unit 5 : SQL
Objective questions
1. SQL uses logical connectives not as _________
a. and
b. or
c. not
d. as
2. Which of the following is not an aggregate function?
a. min
b. max
c. avg
d. order by

3. Inner join is also known as __________

a. Full join
b. Equi join
c. Natural join
d. Semi join

4. FULL JOIN = ________ + RIGHT JOIN

a. LEFT JOIN
b. CROSS JOIN
c. SELF JOIN
d. all of the above

5. Which of the following is a DML command?

a. CREATE
b. ALTER
c. DROP
d. DELETE

Short answer questions

1. Explain use of ORDER BY clause. (L2 : Understand)
2. Give an example of nested subquery. (L1: Remember)
3. Explain basic structure at SQL queries.(L4: Analyze)
4. Explain different datatypes in SQL. (L2:Understand)
5. Explain GROUP BY clause with an example. (L2:Understand)

Long answer questions:

1. What are the types of SQL joins? (L2: Understand)
2. What are SQL clauses to modify to database? Explain. (L2: Understand)
3. Explain the aggregate functions in SQL. (L2: Understand)
4. Explain set membership and set comparison in SQL. (L4: Analyze)
5. What are the different data types available in SQL? Explain in detail. (L2: Understand)
6. Explain pattern matching operators in SQL. (L2: Understand)

7. Consider the following relations and solve the queries: (L6: Create)
Item (i_code, i_name, price)
Order(o_code, date, cust_name)
Item_order(o_code, o_code, qty)
 a. List all order numbers along with different items.
b. List all orders before 4th October, 2010.
c. List all items along with their price.
8. Consider the following database: (L6: Create)
Person(SS_NO. name, address)
Car(lic, year,model)
Accident(date, driver, damage_amount)
Owns(SS_No, lic)
Log(lic, date, drivers)
Write SQL statements for the following:
a. Find the total number of people whose cars were involved in accidents in 2000
b. Find the number of accidents in which the cars belonging to 'Sachin' wew involved.
c. Add a new person to the database.
d. Delete the Fiat belong to "Rahul".
e. Add a new accident record for the Toyata belong to "Rahul"\

Unit 6: Relational Database Design

Objective questions
1. If A -> C, CD -> E then A -> E is ____________
a. transivity rule
b. pseudo transivity rule
c. union rule
d. none
2. The meaning of the notation X -> Y is___________
a. X functionally determines Y
b. Y functionally depend on Y
c. Both a and b
d. None of the above
3. Process of braking a large relation R into a set of small relations r1, r2, r3, ....rn is called as __________
a. association
b. generalization
c. decomposition
d. none of the above
4. A record is referred as __________
a. a criteria
b. a relation
c. a tuple
d. an attribute
5. BCNF is more strict form than is __________
a. 1NF
b. 2NF
c. 3NF
d. 4 NF

Short answer questions

1. What do you mean by decomposition? (L2:Understand)
2. State the purpose of normalization. (L1: Remember)
3. Define Prime attribute. (L1: Remember)
4. What is mean by Transitive dependency? (L2:Understand)
5. Define Partial dependency. (L1: Remember)
6. Give armstrong axioms.(L4: Analyze)
7. Explain pitfalls in RDBMS. (L2:Understand)

Long answer questions

1. Explain the 3NF algorithm. (L2: Understand)
2. What are pitfalls in relational database design? (L2:Understand)
3. Consider the following (L6: Create)
Wholesaler (wno,wname,addr,city)
Product (pno, pname)
Wholesaler and product are related with m-m. Create a relational dB for above and convert it in 3NF
and solve following queries.
a. List all the wholesalers of product books.
b. Count the number of wholesalers in city Mumbai.
c. To print wholesaler wise product.
4. Write a short note on desirable properties of decomposition. (L2:Understand)
5. What is decomposition? What are desirable properties of decomposition? (L2:Understand)
6. What is normalization? Define the terms : (L1:Remember)
a. 1NF b. 2NF c. 3NF
7. Consider, R (A, B, C, D, G, H, I) and set of functional dependency are
F= { A -> B, A -> C, CG -> H, CG -> I, B ->H }
Compute F+. (L5: Evaluate)