BSc CS Sem 4 BSc CS Semester 4 (2014 2015) 2015 DMS Question Paper - Mumbai University | munotes

Questions asked in this paper

• (2) Figures to the right indicate marks,

1. Q1 Attempt the following (any three) :— Explain decomposition consider the attribute set R=A B AG AD >E,B>D,BC>A,E>G } List and explain which Armstrong s Axioms will be applicable here 15 marks
   • (b) Describe the type of dependencies occur in Fourth and Fifth Normal Form. 3
   • (c) What is the phantom problem ? Can it occur in a database where the set of database objects is fixed and only the values of objects can Explain it with an example
   • (d) Consider a database with objects Y and assume and there are two transactions T1 and T2. Transaction T1 reads objects X and Y and then writes object X. Transaction T2 reads objects X andY and then writes objects X
   • (i) Give an example schedule of actions of transactions and T2 on objects X and Y that results in a write-read
   • (ii) Give an example schedule with actions of transactions T1 and T2 on objects X and Y that results in a read-write conflict
   • (e) Elaborate on, a DBMS must ensure four important properties of transactions to maintain data in the face of concurrent access failures (f} Comment on — Two actions on the same data object conflict if at least one of them is an in consistent state Attempt the following (any three) :— 15
   • (a) Define : granularity, deadlock, recovery manager
   • (b) Describe the steal and no-force policies
   • (c) Explain conflict equivalent and conflict serializale schedules
   • (d) State and justify the Thomas Write Rule
   • (8) What is trail known as ? Explain the contents of update log record
   • (f) What is ARIES ? Explain the phases. - ! 3, Attempt the folowing (any three) :—
   • (a) Elaborate on data types, variables and literals in PL SQL by giving example
   • (b) Write a PL SQL block to find factorial of a number which is accepted from the table facts (num, fact) and store it under fact given table Write a short note on conditional control in PL
   • (d) Explain the looping statement in PL SQL using ‘exit’ statement by giving an Write a PL/SQL block to accept the owner name from the user. The can be SYS, SYSTEM, HR or SCOTT. If the owner name SYS then result as The Owner is SYS’. If the owner name is SYSTEM then as ‘The Owner is SYSTEM’. In the same manner print the result using Statement for other owners Write a short note on nested tables. [ TURN OVER 4, Attempt the following (any three)
   • (a) Assuming a table accounts (acctno, balance), State 15 explain the queries to generate acctno using sequence. How to show Nd generated and current value of
   • (b) What is metadata ? What metadata is stored in system catalog ? Describe
   • (c) Write a PL SQL block to execute the transaction operation as either Debi OR C(Credit). Assuming Account-details (acctno, balance) ; if ig executed, then transaction-amount can be credited to the bank. If operation ig executed, the transaction-amount can be debited from bank. The transaction i amount and operation are accepted from the user. Make the changes in
   • (d) Explain the types of cursor. Write any two differences between the types oj
   • (e) Elaborate on, commit, rollback and savepoint by giving these statements in
   • (f) Define lock in SQL. Elaborate on the types of Locks with an example
2. Q5 Attempt the following (any three) 15 marks
   • (a) State and elaborate on the desirable properties of decomposition
   • (b) Elaborate on validation conditions in optimistic concurrency control
   • (c) Write a PL/ SQL block to accept job from employee (empno, emp-name, salary job, table. Assume that this table is already created. Give the following raise in salary by 9% if the job is clerk i by 8% if the job is Accountant by 7% if the job is Executive, update the total-salary in the table
   • (d) Write a PL/ SQL to find the total amount to be paid for the book “Black Book of Oracle log” using book (bookid, bookname, book-title, price) ordered by through bookorders (orderno, dateoforder, quantity). This order is e by a customer for a certain quantity. All the data should be taken from the above tables, assuming the tables are created already
   • e) Define ici Explain by givi y giving an example, how cursor for loop is different from the norme!

Read from the scan above, so a character or two may differ. The scan is the original.