Introduction to Data Base Management Systems

Assignment A

1 a) Consider the following Relations with their FD’s. 
 

b)   Explain the following:
i) Why R1 is in INF but not in 2NF.

ii) Why R1 is in 2NF but not in 3NF

2. a) What is a Transaction? Explain the ACID Properties of Transaction.

b) Explain two phase locking protocol
3. Seralizability is used to enforce concurrency control in a database system. Justify this statement with suitable example.
4. Explain architecture of DBMS with suitable diagram. How the architecture of DBMS helps in achieving data independence
5. Differentiate between following with suitable example:
a) Primary Key & Unique Key
c) Binary Locking & Two Phase Locking
6. Define relational algebra. Explain the different operations used in relational algebra with suitable examples. Solve by www. Solvezone .in
7. How DBMS is different from traditional file system. Explain with suitable example to justify your answer.
8. Define Entity Relationship Model (ERD)? Draw an ERD for an Inventory Management System. Mention all your assumptions used to draw an ERD.

Assignment B

1. Seralizability is used to enforce concurrency control in a database system. Justify this statement with suitable example.
2. a) What is a Transaction? Explain the ACID Properties of Transaction.
b) Explain two phase locking protocol.

Assignment C

Question No: 1
Overall logical structure of a database can be expressed graphically by
ER diagram
Records
Relations
Hierarchy

Question No: 2
A table can have how many unique key
1
any number
255
None of the above

Question No: 3
Entity is represented by the symbol.

Double Circle
Ellipse
Rectangle
Square

Question No: 4
Select the correct statement

ALTER statement is used to modify the structure of Database.
PL/SQL improves the capacity of SQL
SELECT statement is used to retrieve the data from the database
All of the above

Question No: 5
Which of the following are characteristics of an RDBMS?

Data are organized in a series of two-dimensional tables each of which contains records for one entity.
Queries are possible on individual or groups of tables.
It cannot use SQL.
Tables are linked by common data known as keys

Question No: 6
Normalization is step by step process of decomposing:

Table
Database
Group Data item
All of the above

Question No: 7
 
Which of the following statements contains an error?
Select * from EMP where EMPID = 493945;
Select EMPID from EMP where EMPID = 493945;
Select EMPID from EMP;
Select EMPID where EMPID = 56949 and LASTNAME = ’SMITH’;

Question No: 8 

Which of the following statements are NOT TRUE about ORDER BY clauses?
Ascending or descending order can be defined with the asc or desc keywords.
Only one column can be used to define the sort order in an order by clause.
Multiple columns can be used to define sort order in an order by clause.
Columns can be represented by numbers indicating their listed order in the select

Question No: 9
The keys that can have NULL values are

Primary Key
Unique Key
Foreign Key
Both b and c

Question No: 10
The number of columns in a table is known as its
Degree
Cardinality
Domain
Range ANS-(a)

Question No: 11
Rollback and Commit affect
Only DML statements
Only DDL statements
Both (a) and (b)
All statements executed in SQL*PLUS

Question No: 12
GRANT and REVOKE are
DDL statements
DML statements
DCL statements
None of these.

Question No: 13
Oracle 8i can be best described as
Object-based DBMS
Object-oriented DBMS
Object-relational DBMS
Relational DBMS

Question No: 14
 
Rows of a relation are called
Tuples
A relation row
A data structure
An entity

Question No: 15
INITCAP (‘oracle’) returns
oracle
ORACLE
ORACLe
None of these

Question No: 16
TRUNCATE TABLE statement

Drops a table temporarily
Removes all rows of a table
Removes specified number of rows from a table
Removes all constraints from a table

Question No: 17
 
Given an attribute x, another attribute y is dependent on it, if for a given x
There are many y values
There is only one value of y
There is one or more y values
There is none or one y value

Question No: 18
The name of the only column in the DUAL table is
X
C
DUMMY
None of this.

Question No: 19
 
A relation is said to be in 2 NF if
 
(i)    it is in 1 NF
(ii)     non-key attributes dependent on key attribute
(iii)     non-key attributes are independent of one another
(iv)     if it has a composite key, no non-key attribute should be dependent on
          part of the composite key.

i, ii, iii
i and ii
i, ii, iv
i, iv

Question No: 20
A relation is said to be in BCNF when

it has overlapping composite keys
it has no composite keys
it has no multivalued dependencies
it has no overlapping composite keys which have related attributes

Question No: 21
 
Transaction is defined as
Single logical unit of work
Adding a new column into the table.
Adding new constraints into the table..
Degree of Relation

Question No: 22
 
Select the correct statement.
View has no physical existence
Data from the view are retrieved through the Table.
Both (a) and (b)
None of these.

Question No: 23
 
A relation is said to be in 3 NF if

(i)    it is in 2 NF
(ii)     non-key attributes are independent of one another
(iii)     key attribute is not dependent on part of a composite key
(iv)     has no multi-valued dependency

i and iii
i and iv
i and ii
ii and iv

Question No: 24

Fourth normal form (4 NF) relations are needed when.
There are multivalued dependencies between attributes in composite key
There are more than one composite key
There are two or more overlapping composite keys
There are multivalued dependency between non-key attributes

Question No: 25
 
Data integrity in a file based system may be lost because
the same variable may have different values in different files
files are duplicated
unnecessary data is stored in files
redundant data is stored in files

Question No: 26
Data availability is often difficult in file based system

As files are duplicated
As unnecessary data are stored in files
As one has to search different files and these files may be in different e states
Redundant data are stored in files

Question No: 27
A good database design
 
(i)    Is expandable with growth and changes in organization
(ii)     Easy to change when software changes
(iii)     Ensures data integrity
(iv)     Allows access to only authorized users

i, ii
ii, iii
i, ii, iii, iv
i, ii, iii

Question No: 28
Access right to a database is controlled by

Top management
System designer
System analyst
Database administrator

Question No: 29
When a logical model is mapped into a physical storage such as a disk store the resultant data model is known as

Conceptual data model
External data model
Internal data model
Disk data model

Question No: 30
 
Designing physical model of DBMS requires information on
 
(i)    Data volume
(ii)     Frequency of access to data
(iii)     Programming language used
(iv)     Secondary memory characteristics

i, ii
i, ii, iii
i, ii, iii, iv
i, ii, iv

Question No: 31
By data security in DBMS we mean

Preventing access to data
Allowing access to data only to authorized users
Preventing changing data
Introducing integrity constraints

Question No: 32
 
Data independence allows
Sharing the same database by several applications
Extensive modification of applications
No data sharing between applications
Elimination of several application programs

Question No: 33
A database should be designed to allow providing

Different views of portions of data requested by an application
Data only to selected applications as decided by an organization
A uniform view of data to all applications
Data to all applications

Question No: 34
By data redundancy in a file based system we mean that

Unnecessary data is stored
Same data is duplicated in many files
Data is unavailable
Files have redundant data

Question No: 35
Entities are identified from the word statement of a problem by

Picking words which are adjectives
Picking words which are nouns
Picking words which are verbs
Picking words which are pronouns

Question No: 36
Number of entity set involved in the relationship is called.

Prime Entity
Degree of Relation.
Cardinality of relation
1: M relation

Question No: 37
Two pseudo columns present in every Oracle table are .

ROWID & ROWNUM
ROWID & COLUMNID
ROWNUM & COLUMNNUM
None of the above

Question No: 38
 
A relationship is
An item in an application
A meaningful dependency between entities
A collection of related entities
Related data

Question No: 39
 
Attributes are
 
Properties of relationship
Attributed to entities
Properties of members of an entity set

i
i and ii
i and iii
iii

Question No: 40
 
Pick entities from the following:
 
(i)    vendor
(ii)     student
(iii)     attends
(iv)     km/hour

i, ii, iii
i, ii, iv
i and ii
iii and iv