BSc CS Sem 3 ATKT 2017-18 ATKT Database Management System Question Paper - Mumbai University | munotes

Questions asked in this paper

• 2) Figures to the right indicate marks

1. Q3 Illustrations, in-depth answers and diagrams will be appreciated
2. Q4 Mixing of sub-questions is not allowed
3. Q1 Attempt All (Each of 5Marks) (15M)
   • (a) Choose the correct alternative
4. Q1 To get the server output result and display it into the screen, you need to write —
   • a. set serveroutput on b. set server output on
   • c. set dbmsoutput on d. set dbms output on
5. Q2 Which of the following is a benefit of a database trigger? b logging and storing information on table access
6. Q3 CREATE OR REPLACE FUNCTION totalCustomers SELECT count(*) into total
   • a. It doesn’t have the RETURN clause in function declaration
   • b. The RETURN statement is wrong
   • c. Function definition should not use the IS keyword
   • d. Nothing wrong
7. Q4 Which datatype is used to store large blocks of character data in the database, in line or out-of-line? Both fixed-width and variable-width character sets are supported
8. Q5 PL/SQL is a transaction processing procedural language that has which of the
   • a. Integration with database b. Better Performance
   • c. Portability d. All mentioned above
   • Q.P. Code: 23722
   • (b) Fill in the blanks exit when, currval , Il , rowid,)
9. Q1 In Sequence, the current value is seen by
10. Q2 ------ values contain information necessary to locate a row allows to declare a constant, variable, field of the same data type to a previously declared variable, field of the table
11. Q4 the loop.end loop is used:
12. Q5 Compound symbols ------- are used in dbms_output.put_line as a separator
    • (c) Answer in 1 — 2 sentences
13. Q1 Write a syntax to generate a trigger
14. Q2 Write on the terms in hashing in short: Bucket and Hash function
15. Q3 State the use of %type in PL/SQL
16. Q4 State the SQL command lock the table
17. Q5 How and where to use commit
18. Q2 Attempt the following (Any THREE) (15M)
    • (a) Compare the procedures and functions under the along with syntax
    • (b) write a trigger to execute the primary generation order_id for
    • (c) State the various types of file organization methods and explain any 2
    • (d) Write a short note on creation, alteration and dropping a sequence
    • (e) Write down a procedure in PL/SQL to insert the values in the products table(product_id,
    • (f) Write a short note on using insert/update clause used in trigger using before/after clause by giving an example Attempt the following (Any THREE) (15M)
    • (a) Write a PL/SQL block to carry out the transaction for D(Debit) or C(Credit)operation Account.details(accoutno,balance)is the table to be used, where acctno, transaction_amount transaction_type are to be accepted from the user. If the transaction_type is then the transaction_amount can be credited and whereas for the transaction_type the transaction. amout can be debited from the balance. Display the remaining amount in both the cases for the said accountno
    • (b) Differentiate between if . else condition and case structure by stating PL/SQL block
    • (c) Explain the role of Null values with an example in PL/SQL block
    • (d) Write.a PL/SQL block to reverse the string using for loop
    • (e) Discuss the role of exit when statement in loop . end loop with an example
    • (f) Write a PL/SQL block to calculate the total and average of first 10 numbers using while
    • Q.P. Code: 23722
19. Q4 Attempt the following (Any THREE) 15 marks
    • (a) What is ACID? What does each property say with respect to the execution of
    • (b) State the role of locks in serializability
    • (c) How do two transactions conflict? Explain the scenario
    • (d) Comment on - The recovery manager is responsible for ensuring two important properties: Atomicity and durability
    • (e) Define log. What are the contents of log record?
    • (f) Elaborate on important recovery related structure
20. Q5 the following (Any THREE) 15 marks
    • (a) Write a short note on PL/SQL data types
    • (b) Write a PL/SQL block to update the price of the book by its book_title which is to be accepted from the user. If the price is between 300 and 500, then give the discount of 10%, else if the price < 300 then give the discount of 5%. Display the error message if a specific book_title is not available. Use the table book_title, price) Display the updated price
    • (c) Write a short note on growing phase and shrinking phase
    • (d) What is the meaning of the error “exact fetch returns more than one row” which occurs when executing PL/SQL block?
    • (e) What is ARIES? Explain its phases

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