oO ~~ vert Total No. of Questions: 4] seat no. J P6446 [6158]-32 [Totat No. of Pages : 4 Y.B.C.A (Science) BCA 232 : DATABASE MANAGEMENT SYSTEMS-II (2019 Pattern) (Semester-I11) Time :3 Hours} [Max. Marks 70 Instructions to the candidates: LD) Figures to the right indicate full marks. 2) Draw the diagram wherever necessary. Q1) Attempt the following. [5*1=5] A) Choose the correct option: a) The Property assures that once transaction Completes successfully, all updates that it carried out on database persists, even if system crashes. i) atomicity i) Consistency iii) Isolation iv) durability 5) deadlock detection is preemptive. i) Wait-die i) Wound-wait ii) Wound wait wound iv) Wait-die wound c) No-UNDO, REDO) Policy is also known as i) Deferred update i) Immediate update iii) Buffered management iv) Shadow paging d) Prevention of access to database by unauthorized user is referred as ) Integrity i) Consistency iii) Security iv) Reliability e) In system, each entity is treated as an object i) Parallel database i) Object oriented Database iii) Relational database iv) Distributed database PTO.[5x1=5] B) Answer the followi 8) What is meaning of varindic funetion argument type? 25 b) Define concurrent schedule 1° ©) Show compatibility matrix for shared and exclusive lock % -3 d) What are the contents of log? ©) Give the syntax of GRANT command 9 -& Q2) Attempt the following (Any five) [5*3=15] ®) Write structure of PL/PgSQL code block .S, 5) What do you mean by cascadeless, strict and non recoverable schedule? 2-2.7,/, ¢) Discuss the variations of two phase locking protocol 3.6 d) What are the reasons of transaction failure? «2. ©) Explain the components of clinet server architecture? 6-3 / 6-4 f) How DBA is responsible for security of database systems? 5 .3 Q3) Attempt the following (Any five) [5*4=20] a) Explain the timestamp ordering protocol for concurrency 3 .8) 3-% b) Write note on shadow paging «13 /u.J4 c) Explain the mandatory access control for multilevel security. 5.8 1S “4 4) Explain the distributed database management system. © °!? ©) Check whether given schedule $ is conflict serializable or not S: RA), RA), RB), RB), R,(B), W,(A), W,(B) [6158]-32 2Following is list of events ' ‘ n an interleaved execution of set T1, T2, and 13 assuming 2PL. Is there any deadlock? If yes, which transactions are involved in deadlock? Time — Tansaction Code a 11 Lock (A.X) 2 T2 Lock (B.S) B 73 Lock (A,S) t4 TI Lock (C,X) 8 T2 Lock (D,X) 16 Tl Lock (D,S) 7 12 Lock (C,S) 2) What is trigger ? Explain the syntax to create trigger along with its advantages. |-96 4) Attempt the following (Any five) [5*5=25] a) Define transaction. Discuss the properties of transaction 2-! /2-2 b) Explain the concept of locks with multiple granularity 3-1) ©) Dicuss the encryption technique for security $.1) 4) What are the steps to manage explict cursor? write the cursor which will accept year and display all movies released in that year for following table Film (film_id, title, description, release_year, language_id, rental_ duration, rental_rate, length, replacement_cost). {6158]-32 3©) Explain the deferred update technique of log based recovery following at time of system crash are the log entries [Start_transaction, T,] [write_item, T,, A, 10] [Commit [Start_transaction, T,] [Write item, T,, B, 15] [Checkpoint] [Commit T,] [Start_Transaction, T,] [Write_item T,, B, 20] [Start_transaction, T,] [Write_item T,, D, 25] [Write_item T,, C, 30] System crash If a deferred update technique with check point is used, what will be recovery procedure? f) Consider the following transactions. Give two non-serial schedules that are serializable. a cs Read (x) Read (2) X=x+100 Read (x) Write (x) Xaez Read (y) Write (x) Read (z) Read (y) yoytz, y-y-100 Write (y) Write (y) g) What is view? write syntax to create view? How views are useful in database security? \.2°L & && [6158]-32Tota No of Questions = 04 sear No f7 P5252 Time : 3 Hours} BCA 232-DA 15826]-302 Mota No, of Page S.Y. BCA. BASE MANAGE) (2019 Pattern) (Semester-I11) (Max. Marks : 70 Instructions to the candidates: 2D Figure to the right indicate full marks. 2 All questions are compulsory. 3) Draw neat sketches wherever necessary to iMlustrate the answer Each question carries equal marks. Q1) A) Choose the correct option: [5*1=5] a) Collection of operations that form a single logical unit of work is called i) View ii) Network iil) Structure iv) Transaction b) Ifa transaction may obtain locks but moy not release any locks then it is in____phase. i) Growing i) Shrinking iii) Deadlock iv) Starved °c) A is a special kind of a store procedure that executes in Tesponse to certain action on table like insertion, deletion or updating of data. i) Procedure ii) Trigger iil) Function iv) View B) Answer in one or two sentences. [sx a) Defineview 27 b) List the properties of transaction 7-2. c) What is cascading rollback of transaction? perd 4) What is use of GRANT command? 5, 6 ©) List different types of database sysem architecture PTO.Q2) Answer the following. (any five) a) What is exception? How to handle exception in Postgressql?_ \.27 b) Explain wound - wait beadlock prevention algorithm. «2° c) Explain the states of transaction using state diagram. 1+“ 4) What is shadow paging? 4. ©) What is simple and star security property? S-% 1) Write short note on parallel systems. @ -% 23) Answer the following (Any five) 15*4=20] a) What is cursor? Explain types of cursor with example. \.30 b) What is Serializability? Explain view Serializability with example 2 -\2 c) What is checkpoint? consider the following log entries at the time of system crash. [start - transaction, T,] [write - item, T,, A, 10] [commit T,] [start - transaction, T,] [write -item T,, B, 15] [checkpoint] [commit T,] [start - transaction T,] [write -item T,, B, 20] [start - transaction, T,] [write - item T,, D, 25] [write - item T,, C, 30] < System crash If immediate update technique with checkpoint is used, what will be recovery procedure? 15826}-302 2d) Explain statistical database security with suitable example. S- '® €) Discuss the benefits of client server system. 6-9 1) What is need of concurrency control? Explain two phase locking protocol. 2) Consider student - teacher database Student (Sno integer, s-name char(30), S-class char (10) S-addr char (50) Teacher (tno integer, t-name char(20), qualification char (10) experience integer) The relationship student - teacher is many-many with descriptive attribute subject name and marks. i) Create a view containing details of all the teachers teaching the subject Mathematics. ii) Create a view to list the details of all the students who are taught by a teacher having experience of more than 3 years. 24) Answer the following (Any five). [5*5=25] a) What is database security? Discuss different encryption techniques for database security. b) What is log based recovery? Explain deferred modification technique and immediate modification for log based recovery. c) Consider the following list of events in an interleaved execution of set of transaction T,, T,, T, and T, assuming 2PL Is there a beadlock? If yes, which transactions are involved in beadlock? Time Transaction Code t T, Lock (A, X) t T, Lock (A, S) t T, Lock (A, 8) t, T, Lock (B, $) t, T, Lock (B, X) t, Ty Lock (C, X) t T Lock (D, $) ty T, Lock (D, X) [5826]-302 3E Check whether given schedule $ is confict serializable or not. Ifyes, then determine all possible scrialized schedules, c i: R(A) RA) R(A) wo) Wa) RB) WB) ©) Explain timestamp based protocol for concurrency control. 1) _ Explain classification of client server architecture. 8) Consider student - teacher database student (Sno integer, s-name char (30), S-Class (10), S-addr char (50)). ‘Teacher (tno integer, t-name char (20), qualification char (15), experience integer) The relationship student - teacher is many - many with descriptive attribute as subject name and marks, Write a function to accept riame of subject and count the number of teachers who teach that subject. a & & 15826]-302Totat No. oF Qu PA-1087 Times 8 Hours] tou jestions 4) [Max. Marks: 70 he candidates: 1D Figures to the right side indicate full marks. 2) Draw diagram wherever necessary. QD Attempt the following: A) Choose the correct options. [5*1=5] a) b) ©) 4) °) is used to throw the exception in PL/SQL. ij) THROW ii) RAISE ii) NOTICE iv) WARNING Record is i) Placeholder i) Variable iil) Datatype iv) Keyword is not type of parallel database. i) Shared memory ii) Shared disk iii) Shared nothing iv) Shared processor Log record contains __. i) Oldvalue i) Newvalue iii) Both (i) & (i) iv) Errorvalue Prevention of access to the database by unauthorized users is referred to as : i) Integrity ii) Productivity iil) Security iv) ReliabilityB) Answer the following, [Sxt=5) a) What is function? :24 b) What is Schedule? ‘U7 ©) What is lock? 4) 1 various types of errors in transaction failure Wy. ©) _ List types of server systems. 2) Answer the following: (Any Five) [5*3=15] a) Define view. Explain how to create view with example. +... 1.2% bd) Whatis serializability? Explain conflict serializability. 2.42 c) Defineterms 4.6 Strict 2PL, Rigorous 2PL Conservative 2PL 4) What is system crash? Explain in detail. 4.2 €) Write a note on statistical database security.5.\> f) State the features of distributed databases. ¢-\> Q3) Answer the followin; a) b) [5«4=20] Consider following database: Student (sno, sname, sclass, saddr) Teacher (tno, tname, qualification, experience) Relationship between Student and Teacher is M-M with descriptive attribute subject. Create a trigger for following: Write a trigger before insert the record of student table. If sno is less than or equal to zero give message “Invalid Number”. Consider the following transaction. Give two non-serial schedule TT T2 ead (6) Read () Read (a) Read (a) a=ae a=ate Write (a) Write (a) Read (b) b= be Write (b) {5905]-32 2©) d) °°) D) 8) Folloy is the list of event T, and T, assuming 2PL. Is the are involved in Deadlock’? {Time — [Transaction [Code u TI Lock (A,X) 2 r2 Lock (BX) B T3 Lock (A,S) 4 T4 Lock (B,S) 6 TI Lock (B,S) 6 T2 Lock (D,S) 7 13 Lock (C,S) 8 T4 Lock (C,X) Explain different types of failure in detail, 4-2 Explain methods for database security. ¢ Explain two-tier client-server structure. Also state advantages & disadvantages of it. 6-9 Write a short note on cascadeless schedule. 2-27 in an interleaved execution of set T,, T,, Deadlock? If yes which transactions 24) Attempt the following (Any Five) [5x5=25] a) b) Consider following database Movie (mno, mname, release_year, budget) Actor (ano, aname, role, charges, addr) Relationship between Movie and Actor is M-M Write a stored function to accept movie name as input and print the name of actors working in that movie. Consider the following transactions. Give two non-serial schedule that are serializable. Ti T2 Read(Y) | Read (X) Read(A) | Read (A) Y=Y+A | X=X+A Write (Y) Write (X) Read (Y) Y=Y+A Write (¥) 15905}-32 3ye ©) Explain Deadlock Recovery Technique: 4) Consider the following log image, that is obtained during recovery after crash ‘ < System crash 1) _ List contents in the List L 2) List contents in )} Undo list ii) Redo list ©) Whats a cursor? How to declare it? Explain with example. 1-22 1) _ List and Explain properties of transaction, 0 8) What is shadow paging? State advantages and disadvantages of shadow paging, =X a & & 15905}-32 4‘Total No. of Questions : 4 P1355 [6058}-302 S.Y.B.C.A (Science) - 232 : DATABASE MANAGEMENT. SYSTEMS-IT (2019 Pattern) (Semester - 11) BC. Time : 3 Hours} Instructions to the candidates: Y All quetions are compulsory 2) Figures to the right indicates full marks. 4) Draw tebeled diagram whenever necessary. QI) Attempt the following. A) Choose the correct option a) Record is a (ax. Marks :70 [S71=5} i) Placeholder i) Variable iii) Datatype iv) Keyword b) The execution sequences in concurrency control are termed 2s i) Serial fi) Schedule if) Organization iv) Timetables ¢) The default timestamp ordering protocol generates schedule that are i) Recoverable i) non-recoverable iif) Starving iv) none of the mentioned 4) alternative of log based recovery i) disk recovery ii) shadow paging if) disk shadowing iv) crash recovery €) The typical techique of discreationary access control in database system is based on the ___of previlege i) Commit and Rollback ii) Granting & revoking iif) Serialandnon-serial_ iv) Alllthe above PIO.B) 2) Answer the following (Any Five) a) b) °) d) °°) f) 23) Answer the following (Any Five) a) b) c) Answer the following a) Whatis view? \.22 b) What is schedule? 2.7 ¢) Whats Timestamp? 57 4) Enlist various types of errors 4-L. ©) Whatis shared memory? ¢.7 15*3=15] What is Exception? How to handle exception in postgresalt . 32 Explain cascadeless schedule 1.17 ‘State and explain Thomas write rule 10 Explain Log based recovery \ 6 Discuss the database security threats Explain client/Server system architecture 6-> [54=20) What is function? Explain with example \-?"* With suitable diagram explain different states oftransaction 2-4 Following is the list of events in an interleaved execution of set T,. and T4 assuming 2PL. IS there a deadlock? If yes which tansactions are involved in a deadlock? Code Lock(A.X) Lock (B,X) Lock (A,S) Lock (B,S) Lock (B.S) Lock (D.X) Lock (D,S) Lock (C.X)Boe Q4) Attempt the following (Any five) a) b) Describe Differred update modification with example, ‘1+ Explain various methods for database security in brief 6. Explain client / Server architecture 6 <3, Consider the following transaction Give 2 non serial schedules that are serializable TI T2 Read (A) Read (B) A=A- 1000 B=B+100 Write (A) Write (B) Read (B) Read (C) B-100 Cc=C+100 Write (B) Write (C) [5x5=25] Consider following database student (Sno, Sname, Sclass, Saddr) Teacher (tno, tname, qualification, experience) The relatioship of student and teacher is M-M with descriptive attribute as subject & Marks write a trigger before deleting a student record from the student table. Raise notice and display the message “Student record is being deleted” Consider the following schedule and draw precedence graph for that state whether schedule is serializable or not. TI T2 Read (A) A=A-50 Read (A) temp =A* 0.1 A=A-temp Write (A) Read (B) Write (A) Read (B) B=B+50 Write (B) B=B + temp write (B) [6058]-302 3¢) Explain variation of 2 phase locking protocol 4-6 d) What is checkpoint? How are they useful in crash recovery U.S ©) Consider following database Movie (mno, mname, relese-year, budget) Actor (ano, aname, role, charges, addr) Relationship between movie and Actor is M-M Write a function to list moviewise charges of Amitabh Bachchan 1) Following are the log entries at the time of system crash <73, Start> = <_ System Crush If defferred update technique with checkpoint is used, what will be the recovery procedure? 8) What is deadlock? Explain Deadlock detection and prevention technique & & A&A {6058}-302 4