GBCSISCHEME USN | | scan Fifth Semester B.E. Degree Examination, Jan./Feb. 2021 Database Management System Time: 3 hrs. Max. Marks: 1 Note: Answer any FIVE full questions, choosing ONE full question fro 2 Module-1 1a, Define the following terms : i) Database i) DBMS catalog iii) Entity iv) Snapshot v) Degree ofa relationship. (05 Marks) b. Explain types of end-users with suitable examples (05 Marks) ¢. List and explain advantages of using DBMS approac (10 Marks 2. a. Define the following terms “S) iii) Program data independence iv) Total participation v) Value sets, (0S Marks) b. Describe three schema archi OF" do we need mappings between schema levels? (0S Marks) ©. Explain different typ R model with suitable examples for each. (10 Marks Module-2 3a cee integrity constraints, Why is each considered (05 Marks) ’. join with suitable examples using relational algebra notation, (05 Marks) » pgender, peity) ime, acity) , time, sre, dest) id, fid, f complete details of all flights to new Delhi ‘ind only the flight numbers for passenger with paid 123 for flights to Chennai before 11/2020 jii) Find the passenger names for those who do not have any bookings in any flights ’) Get the details of flights that are scheduled on both dates 01/12/2020 and 02/12/2020 at 16:00 hours ¥) Find the details of all male passengers who are associated with jet agency. (10 Marks) lof2 Vv Scanned with CamScannerOR 4 a. Explain the ER to relational mapping algorithm with suitable example for each Pond b, Write SQL query for the following database scheme : Employee(employee_name, street, city) Works (employee_name, company_name, salary) Company(company_name, city) ages(employee_name, manager_name) d the names, street address, and cities of residence for all empl ‘First Bank Corporation’ and earn more than $10,000 ii) Find the names of all employees in the database who do not Corporation’. Assume that all people work for exactly one co iii) Find the names of all employees in the database who cam pore’ that ‘Small Bank Corporation’. Assume that all people work fofiat most ie company iv) Find the name of the company that has the smallest payzol v) Find the names of all employecs in the database wh; in fc cities and on the same strects as do their managers. (10 Marks) Module-3 5 a. Explain cursors and its propertics in embedded SQL, with stlffable cxample. (05 Marks) b. How are triggers defined in SQL? Explain with exatle. (05 Marks) cc. Illustrate insert, delete, update, alter and drop gent QL. (10 Marks) 6 a. With an example, explain stored procs (OS Marks) b. Briefly explain types of JDBC driv; (05 Marks) cc. Illustrate aggregate functions in SQ (10 Marks) 7 a. Explain types of update ang ‘amples. (05 Marks) b. Explain Armstrong in (05 Marks) c. What is the need for plam INF, 2NF and 3NF with examples. (10 Marks oR 8 a, What is functional Write an algorithm to find minimal cover for set of functional d ieSMGonstruct minimal cover m for set of functional dependencies which de : (BBY A, D> A, AB D} (10 Marks) b. Conside: = ABCD, subjected to FDs F = {A > B, B > C}, and the non- binary {ACD, AB, BC}. State whether D1 is a lossless decomposition? [give all steps (10 Marks) Module-5 9 a Define transaction. Discuss ACID properties. (05 Marks) With a peat diagram explain transition diagram of a transaction. (05 Marks) yy GBicurrency control and recovery are needed in DBMS? Explain types of problems may occur when two simple transactions run concurrently. (10 Marks) OR en deadlock and starvation problem occur? Explain how these problems can be resolved. oo . . 10 Mark: iN b. Briefly discuss the two-phase locking techniques for concurrency control. if joy seen 2of2 Scanned with CamScannerme: z . 3 hrs. Fifth Semester B.E. Degree Examination, Feb./Mar. 2022 ‘ Database Management System « ssing System. (06 Marks) Rchema level. (08 Marks) Explain different types of attributes that occur in an E — R.di@gram mptel with example. (06 Marks) List and discuss advantages of Database } Explain three Schema Architecture and reason for need of ig ai Eyplain characteristics of the Datat Discuss the different types of User frie (06 Marks) (06 Marks) with atleast five entities. Also (08 Marks) (06 Marks) (04 Marks) en the following schema emp (fname, Lname . SSN z gender , salary , superSSN , Dno) dept (Dname . number , dept_loc (Dnumber , Dloe! project ( Pn: works on ( Dependent Give the re we the project number roject, es of employees who work on all the project controlled by project name and number of employee ye the Tame of employees who have no depend. number of Male and Female employee working in the Company. (10 Marks) OR the steps of an algorithm for ER to Rational mapping with example. (06 Marks) nand that is used for table creation, Explain how constraints are specified in SQL table creation, with s le example. (04 Marks) Scanned with CamScanner18CSH ¢. Given the following schema Emp (Fname, Lname , SSN , bdate , address, gender, salary , superSSN , dno) dept (dname . number, mgrSSN , mgrstartdate) dept_loc (dnumber, dloc) project (Pname, Pnumber, Ploc , dnum) works_on (ESSN, Pno, hours) dependent (ESSN . dependent_ name, gender, bdate, relationship) Give the relation algebra expression for the following : Retrieve the name and address of all employees who work for “sports” depen. Retrieve each department number, number of employers and age @lary. List the project number, controlling department number and manager's last name . address and birthdate. iv) Retrieve the name of employees with 2 or more dependg ¥) _ List female employees from dno = 20 earning more th: (10 Marks) Module. Define Database stored procedure. Explain creatin@@igd calling stored procedure witl example. (06 Marks} b, What is SQLI and how is it different from JDBC? (06 Marks} c. Consider the following schema : Sailors (S me , rating . age) Boats (bid. bname, color) Reservers (Sid . bid , day) Write que s i) Find the ages of sailors whose name B@gins agd ends with A and has atleast three character ii) Find the age of the youngest for each rating level with atle iii) Find the names of sailoy iv) Compute increments fj i ited two different boats on thy same day, (08 Marks} OR a. 'GI introduced? What are the disadvantages of an architecturi (06 Marks) b. how is it different from embedded SQL? (06 Marks} c. Rema: Milbrs whose rating is better than some sailors called ‘Jennifer’, (Use nested query) the average age of sailor for each rating level that at least two sailors. the name and age of the oldest sailor. (08 Marks) Module-4 Vhich normal form is based on 6 transitive functional dependencies and full functiona jependency? Explain the same with example. (08 Marks) iN 2of3 Scanned with CamScannerA relation R satisfies the following: FDS : A>C , AC>D,E SAD, Find the cover for this set of FDS. Consider the universal relation: R = {A. B,C. D. cl dependencies. F = {AB>C,A>DE,BoF, Determine whether each decomposition has the lo: Di = €Ri, Rz, Rs} 5 Ri = {A B,C, D,E} 3 Re OR Write an algorithm to check whether decomposed relations are h dependency preservation and non — additive join property. Consider universal ldfion B= (U. C, L, A) and the set of functional dependencies. F = {P > LCA, LOGS@AP ‘C}. Decompose the relation R into 3NF with dependency preservation and nf — addiGve join property. (06 Marks) Define Normal Form. Explain INF, 2NF and 3NF with les for each. (08 Marks) Consider two set of functional dependencies F = { 7D,E>AD,E— H} and G= {A> CD, E> AH}. Are they equivalent? (06 Marks) Module-: What are the anomalies occur due to interleay Explain them with example. (08 Marks) xplain different types of locks used in (06 Marks) Explain how shadow paging helps to recd ansaction failure. (06 Marks) Explain ACID property of transa id SWatem log. (06 Marks) When deadlock and starvation problem@occurs? Explain how these problems can be resolved. (06 Marks) Explain ARIES recovery ith example. (08 Marks) Scanned with CamScanner| | | iscs%q Fifth Semester B.E. Degree Examination, July/August 2022 Database Management System ime: 3 hrs. May larks@J 00. Note: Answer any FIVE full questions, choosing ONE full question from each ul Module-1 06 Marks) pping between (06 Marks) ofa diagram, (08 Marks) in the component modules of DBMS and their interacti it OR a. Define the following terms: (Weak enti Gi) DBMS catalog ii shot (iv) Value sets (v) Cardinality ratio i, gofarelationship (06 Marks) b. Explain the different categories of data models. (06 Marks) ¢. Write the ER diagram for an employee databas @ ints are as follows: n employee works for a department department is headed by a m: ployee works on one or mor n employee has dependents (¥) A department controls the projects (08 Marks) plain the importance of referential integrity int is implemented in SQL. (08 Marks) to perform the following queries: 's of all employees who work for the “Accounts” constrain b. Write the relational algebrg (Retrieve the name department. (ii) (ii) Find the number 2. ¢. Explain the re) ‘vers who have no dependents loyees who work on all the projects controlled by department (06 Marks) (06 Marks) OR Xo FeFifional mapping algorithm with suitable example for each step. (10 Marks) b. ies for the following data ME, BRANCH. PI mes of all students enrolled for the course *CS_S4° artments having an average salary of the faculties above Rs.10,000, List the names of the students enrolled for the course"CS_51° and having “B" grade. (06 Marks) command (04 Marks) ‘xamples in SQL: (i) INSERT command (ii) UPDATE lof2 Scanned with CamScannerModule-3 How are assertions and triggers defined in SQL? Explain with examples. (0§ lain stored procedures in SQL with an example. q List out and explain the different types of JDBC drivers. « OR What is a three-tier architecture? What advantages it offer over single tic tier architectures? Give a short overview of the functionality at each of the three-tit Parks) How to create views in SQ plain with an example. Marks) What is SQLJ? How it is different from JDBC? ea" Marks) Module-4 Explain an informal design guidelines for relational schema desi What is the need for normalization? Explain INF, 2NF and 3 What do you understand by attribute cl (08 Marks) les. (08 Marks) ure? Give an exam (04 Marks) OR What is functional dependency? Explain the inferenc&@ies for functional dependency with proof. (08 Mark Define 4NF. When it ted? Why is it useful? (06 Marks) Consider two sets of functional dependency F@fA > \C > D, E > AD, E > H} and G= {A CD, E > AH}. Are they equivalent? (06 Marks) Why concurrency control is needed? De Discuss the UNDO and REDO operg (10 Marks) ch. ith an example. ery techniques that use (06 Marks) Explain the ACID properties of a| (04 Marks) Discuss Two-Phase Locki te for concurrency control. (10 Marks) When deadlock and starya n occur? lain how these problems can be resolved. (10 Marks) eeeee Scanned with CamScanner