Mumbai University Sample Paper CSE 4th Sem DBMS

Mumbai University Sample Paper CSE 4th Sem DBMS

 

1) Question NO.1 is compulsory.

(2) Attempt any four questions out of remaining six questions.

(3) Make suitable assumptions if needed. ”

1. (a) Describe following terms :-

(i) Natural Join and Equi Join

(ii) 3 layer data obstr.uction

(iii) Different types of DBMS users.

(b) State whether following statements are true or false –

(i) ISAM Index is dense Index

(ii) The outcome of database design is only set of table schemas

. (iii) Relational model better supports physical data independence than logical

data independence

(iv) No further FDs can be generated once initial analysis lists given set of FDs

from mini world description

(v) Heap based storage needs more disk sjS>acethan that required for hashing

based storage organization

(vi) Tree based locking protocol prevents deadlock

(vii) Shadow paging requires no redo and undo operation overheads

(viii) View can provide dynamic security control.

2. (a) Describe 2 NF, 3 NF and BCNF with examples.

Decompose 2 NF table into 3 NF tables with example

Decompose 3NF table into BCNF tables with example.

(b) Consider following employee database.”

emp (empid, empname, bdate, address, sex, salary, mgrid, dno)

dept (dno, dname, dmgrid: appoin~edate)

proje”ct(pno, pname, ‘plocation, dno)

dept location (dno, d.location)

works on (~mpid, pno, Hours)

Most attributes are self explanatory ‘bdate’ is birth date and address stores full address

in string”format ‘mgrid’ is the manager / supervisor of the employee orking in dept

‘dno’, ‘dmgrid’ is the manager of the depa rementand is appointed on ‘appointe date’.

Department may’ be located at multiple locations. Every project is managed by a

department – “dno’. Write SQL queries – .”

(i) Li$t the department na~es which are located at multiple locations.

(ii) List d.epartment,its manager and number of employees in the dept.

(iii) List the employees who do not have any supervisors.

(iv) List the employees working on project ‘myERP’ with their present salary and

salary after 10% raise. . .

(v) List the projects whose location is same as location of their controlling department.

Con. 5~64-RC-61 07-08. 2

3. (a) Library needs to be computerised. The main functions to be supported are to query

existing titles based on either author / titl~3!.koery strings. Users are registered to borrow

the books. Alltran sactions, Le. to issue and return are stored. Users can put the claim.

Write any assumption you make.

(i) Draw E-R diagram for above requirements. Show relationship types and

primary key of entities.

(ii) Convert E-R diagram into set of relation schema.

(b) Describe database design process. What are ideal characteristic of database design out put?

(a) Draw state diagram for transaction and describe concept of transaction in DBMS.

(b) Explain shadow paging technique for recovery.

(c) Explain ‘Undo’ and ‘Redo’ operations for log based recovery.How they are used during

recovery ?

5. (a) Explain time stamp based concurrency control.

(b) Describe different methods of recovery from deadlock.

(c) Describe one protocol that prevents deadlock.

6. (a) Describe hashed based storage organization.

(b) Compare ISAM index vs B-tree index.

(c) Consider a disk with block size = 2048 bytes. A block pointer = 6 bytes. Employee table

schema is as follows –

employee (name char (20),

id                    char (9),

dept               char (9),

address          char (36),

phone            char (9),

bdate              date,

sex                  char (1),

Johcode         char (4),

Salary Number,

Where ‘date’ type needs 8 bytes and ‘number’ type needs 4 bytes of storage. Calculate

if there are 30,000 employees –

(i) Record size in bytes

(ii) Number of records per disk bloc~

(iii) Number of Block to store employee table on the disk

(iv) Number of Blocks to store ISAM index on ‘id’ on the disk.

7. Write short notes (any four) :- .

(a) Check pointing in crash recovery

(b) ‘View’ in SQL

(c) Foreign key and refer ntial integrity

(d) Proceduralvs non procedural query languages

(e) Conflict serializability.

 

Leave a Comment