Database Management Systems Past Paper PDF - GUJARAT TECHNOLOGICAL UNIVERSITY Winter 2023

Seat No.: ________ Enrolment No.___________ GUJARAT TECHNOLOGICAL UNIVERSITY BE - SEMESTER–III (NEW) EXAMINATION – WINTER 2023 Subject Code:3130703 Date:18-01-2024 Subject Name...

Seat No.: ________ Enrolment No.___________ GUJARAT TECHNOLOGICAL UNIVERSITY BE - SEMESTER–III (NEW) EXAMINATION – WINTER 2023 Subject Code:3130703 Date:18-01-2024 Subject Name:Database Management Systems Time:10:30 AM TO 01:00 PM Total Marks:70 Instructions: 1. Attempt all questions. 2. Make suitable assumptions wherever necessary. 3. Figures to the right indicate full marks. 4. Simple and non-programmable scientific calculators are allowed. MARKS Q.1 (a) Define Superkey, Candidate Key and Primary Key. 03 (b) Enlist advantages of Database Management Systems over traditional 04 File System (c) Explain DBMS architecture with a neat sketch. 07 Q.2 (a) Differentiate terms relation and relation schema with suitable example. 03 (b) List various integrity constraints in reltional model. Explain referential 04 integrity constraint with suitable example. (c) Briefly explain generalization and specialization in E-R diagram with 07 suitable example. OR (c) Differentiate Weak Entity and Strong Entity with suitable example. 07 Also draw a sample E-R diagram which depicts weak entity. Q.3 (a) What is functional dependency? Explain armstrong’s axioms. 03 (b) Consider following relations and write queries in the form of relational 04 algebra. Students(std_id, std_name, semester, dept_id) Departments(dept_id, dept_name, no_of_faculties) 1) List names of students studying in “Computer” department. 2) Display all information of the student whose name is “Pratik” (c) What is normalization? Explain 2NF, 3NF and BCNF with suitable 07 examples. OR Q.3 (a) Let R = (A, B, C, D, E, F) be a relation schema with the following 03 dependencies- C→F E→A EC → D A→B What is the Candidate key of the relation R? (b) Consider following relations and write queries in the form of relational 04 algebra. Accounts(acc_no, acc_name, balance, bank_id) Bank(bank_id, bank_name, city) 1) List all Acount Numbers in “HDFC” bank. 1  2) List all bank names in “Ahmedabad” city. (c) Consider the relation Employees(empId, empName, carName, 07 parkingSlot) with dependencies F ={empId→ carName, empId→ empName, carName→ parkingSlot}. Does the above table appear in 3NF? If not, normalize it in 3NF. Q.4 (a) List and explain ACID properties with respect to Database transaction. 03 (b) Write a short note on Intrusion Detection in DBMS. 04 (c) Discuss various steps of query processing with diagram. 07 OR Q.4 (a) List and explain different types of locks in transactions. 03 (b) Write a short note on SQL injection. 04 (c) What do you mean by Serializable Schedule? Explain View 07 Serializability with example. Q.5 (a) What is a trigger? List various types of triggers and explain any one 03 with example. (b) Differentiate Sparse and Dense dense index in DBMS with example. 04 (c) Consider the following tables and answer the queries in SQL. 07 Books (isbn_no, title, publisher_id, year) Authors (author_id, author_name, country, city) Publishers (publisher_id, publisher_name, city) WrittenBy (isbn_no, author_id) 1) List all the books published after 1998. 2) Update the city of author to “Baroda” whose author id is 10. 3) List all the book titles written by author “korth” 4) Add column “price” in the table Books. 5) Display number of Publishers from the city “Ahmedabad”. 6) List all the books published by “McGaw Hill” 7) Display all publishers in the ascending order of their name. OR Q.5 (a) What is a cursor? Differentiate implicit and explicit cursor with 03 example. (b) Differentiate open and closed hashing in DBMS. 04 (c) Consider the following tables and answer the queries in SQL. 07 Products(prod_id, prod_name, category, price) Customers(cust_id, cust_name, country, city) Orders(order_id, cust_id, prod_id, order_date, quantity) 1) List all the products having price less than 2000. 2) Update price of a product to 3000 whose product id is 43. 3) Display names of customer who have placed atleast one order. 4) Display customer id and total number of orders placed by them. 5) List all Customers in the descending order of their city. 6) Display names of customers who have ordered “Television” (Product Name) 7) Remove column “category” from the Products table. ************* 2