Mumbai University question papers
IV Sem CSE – Examination DEC 2008
Database Management System
N.S.: (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 birthdate 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’r~mentand is appointed on ‘appointedate’. 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 projectswhose location is same as location of their controlling department.
3. (a) Library needs to be computerised. The main functions to be supported are to query existingtitles based on either author / titl~3!.koery strings. Users are registered to borrow the books. Alltransactions, 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) ConvertE-R diagraminto set of relation schema.
(b) Describedatabasedesignprocess.What are idealcharacteristicof databasedesignoutput?
(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.Howthey 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 blocksize = 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),
sex char (1),
Johcode char (4),
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.