Database Management Past Paper PDF

 DR. BABASAHEB AMBEDKAR TECHNOLOGICAL UNIVERSITY, LONERE Supplementary End Semester Examination – Summer Semester 2023 Course: B. Tech. Branch: Computer /CSE Semester: V Subject Code & Name: (BTCOC501) Database System Max Ma...

 DR. BABASAHEB AMBEDKAR TECHNOLOGICAL UNIVERSITY, LONERE Supplementary End Semester Examination – Summer Semester 2023 Course: B. Tech. Branch: Computer /CSE Semester: V Subject Code & Name: (BTCOC501) Database System Max Marks: 60 Date:07/08/2023 Duration: 3 Hr. z Instructions to the Students: 1. All the questions are compulsory. 2. The level of question/expected answer as per OBE or the Course Outcome (CO) on which the question is based is mentioned in ( ) in front of the question. 3. Use of non-programmable scientific calculators is allowed. 4. Assume suitable data wherever necessary and mention it clearly. (Level/CO) Marks Q. 1 Solve any two of the following. A) Illustrate the database characteristics. How they are different from File L2 6M systems. B) Illustrate about integrity and key constraints with suitable examples? L3 6M C) Explain the importance of Null values in Relational Model. L2 6M Q.2 Solve all two of the following. A) Explain in detail, the form of a basic SQL query with a suitable L2 6M example. B) List out various SET comparison operator in SQL and also write about L3 6M its use in writing SQL queries. Q. 3 Solve any two of the following. A) Write SQL Queries for the following set of tables: L3 6M account(account_number, branch_name, balance) branch (branch_name, branch_city, assets) customer (customer_name customer_street, customer_city) loan (loan_number, branch_name, amount) depositor((customer_name, account_number) borrower(customer_name, loan_number) a. For all customers who have a loan from the bank, find their names and loan numbers with the attribute loan_number replaced by loan_id. b. Find the names of all customers whose street address includes the substring ‘Main’. c. List loan data, ordered by decreasing amounts, then increasing loan numbers.  B) Write SQL Queries for the following set of tables: L3 6M EMPLOYEE (FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, #SUPERSSN, #DNO), DEPARTMENT (DNAME, DNUMBER, #MGRSSN, MGRSTARTDATE), DEPT_LOCATIONS (#DNUMBER, DLOCATION), PROJECT (PNAME, PNUMBER, PLOCATION, #DNUM), WORKS_ON (#ESSN, #PNO, HOURS), DEPENDENT (#ESSN, DEPENDENT_NAME, SEX, BDATE, RELATIONSHIP) a. Retrieve the name and address of all employees who work for the ‘Research’ department b. Retrieve the birthdate and address of the employee(s) whose name is ‘Raj V Naik’ c. Retrieve all the attributes of an EMPLOYEE and the attributes of the DEPARTMENT he or she works in for every employee of the ‘Research’ department C) By considering an example describe various data update operations in L2 6M SQL. Q.4 Solve all two of the following. A) State BCNF. How does it differ from 3NF? L2 6M B) Explain insertion, deletion and modification anomalies with suitable L2 6M examples. Q. 5 Solve any two of the following. A) Explain in detail about internal hashing Techniques. L2 6M B) Discuss in detail about cluster and Multilevel indexes. L2 6M C) Explain Concurrency control with locking methods. L2 6M *** End ***  DR. BABASAHEB AMBEDKAR TECHNOLOGICAL UNIVERSITY, LONERE Supplementary Examination – Summer 2022 Course: B. Tech. Branch : Computer Science & Engineering Semester :VI Subject Code & Name: (BTCOC501), Database Systems Max Marks: 60 Date: Duration: 3 Hr. Instructions to the Students: 1. All the questions are compulsory. 2. The level of question/expected answer as per OBE or the Course Outcome (CO) on which the question is based is mentioned in ( ) in front of the question. 3. Use of non-programmable scientific calculators is allowed. 4. Assume suitable data wherever necessary and mention it clearly. (Level/CO) Marks Q. 1 Solve Any Two of the following. A) Explain purpose of database system. Remembering 6 B) Explain Data Abstraction. Understanding, 6 applying C) Explain data storage & Querying. Understanding 6 Q.2 Solve Any Two of the following. A) Give formal definition of tuple relational calculus and explain it. Remembering 6 B) Explain aggregation in relational algebra. Understanding, 6 applying C) Explain domain relational calculus. Remembering 6 Q. 3 Solve Any Two of the following. A) Consider following schema & Solve following queries by SQL. 6 Instructor ( id, name, department name, salary) Teaches ( id, course_id, sec_id, semester, year) Course ( couse_id, tittle, department name, credits ) Department( department name, building, budget ) Understanding, analyze a. Find the set of all courses taught in summer 2020 semester but not in winter 2021. b. List the names of instructor with salary amounts between 90000 and 100000. B) Explain aggregation with example. Remembering 6 C) Explain Function and procedure with example. Understanding, 6 analyze Q.4 Solve Any Two of the following.  A) Construct B+ tree for the given set of key values ( 6 Understading, 2,3,5,7,11,17,19,23,29,31) using four & six pointers that will fit in one applying node. B) Suppose that we are using extendable hashing on a file that contains 6 records with the following search key values (2,3,5,7,11,19,23,29,31) Understanding, show the extendable hash structure for this file if the hash function is applying h(x) = x mod 8 and buckets can hold three records. C) Explain BCNF. Understanding, 6 analyze Q. 5 Solve Any Two of the following. A) Explain ACID properties of transaction. Remembering 6 B) Explain serializability. Understanding, 6 analyze C) Explain deadlock handling. Understanding, 6 *** End *** The grid and the borders of the table will be hidden before final printing.  DR. BABASAHEB AMBEDKAR TECHNOLOGICAL UNIVERSITY, LONERE Winter Examination – 2022 Course: B. Tech. Branch : CSE Semester : V Subject Code & Name: BTCOC501 Max Marks: 60 Date:28/01/2023 Duration: 3 Hr. Instructions to the Students: 1. All the questions are compulsory. 2. The level of question/expected answer as per OBE or the Course Outcome (CO) on which the question is based is mentioned in ( ) in front of the question. 3. Use of non-programmable scientific calculators is allowed. 4. Assume suitable data wherever necessary and mention it clearly. (Level/CO) Marks Q. 1 Solve Any Two of the following. 12 A) Why would you choose a database system instead of simple storing data in L1,L2/CO 6 file processing system? Compare file processing system and DBMS. 1 B) Define an Entity and Attribute. Explain the different types of attributes that L2/CO1 6 occur in an ER diagram model, with an example. C) Draw an E-R diagram of Banking system taking into account at least five L3/CO1 6 entities, indicate all keys, constraints and assumptions that are made. Q.2 Solve Any Two of the following. 12 A) Define and differentiate the following relational algebra operators with L1,L2/ 6 suitable example: CO2 (i) Cartesian product (ii) Natural join B) Consider the following relational schema. L3,L5/ 6 Suppliers(sid, sname, address) CO2 Parts(pid, pname, address) Catalog(sid, pid, cost) (i) Write relational algebra query to find the names of suppliers who supply some red part (ii) Write relational algebra query to find the sid of suppliers who supply some red or green parts C) Consider the following employee database. Give expression in tuple rela- L3,L5/ 6 tional calculus for each of the following queries. CO2 employee(employee_name,street,city) works(employee_name,company_name,salary) company(company_name,city) manages(employee_name,manager_name) (i)Find the names of all employees who work for First Bank Corporation.  (ii)Find the names and cities of residence of all employees who work for First Bank Corporation (iii)Find all employees who live in the same city as that in which the com- pany for which the work is located. Q. 3 Solve Any Two of the following. 12 A) Consider the following schema and solve following queries using SQL. L3,L5/ 6 CO2 employee (emp_no, name, skill ,pay_rate) position (posting_no, skill) duty-allocation (posting_no, emp_no, day, shift) (i) Get duty allocation details for emp_no 123461 for the month of april 1986. (ii) Get employees whose rate of pay is more than or equal to the rate of pay of employee ‘XYZ’. (iii) Get the names and pay rates of employees with emp_no less than 123460 whose rate of pay is more than the rate of payoff atleast one em- ployee with emp_no greater than or equal to 123460. B) Consider the following relational schema. L3,L5/ 6 Weather (city, temperature, humidity, condition) CO2 Location (city, country) Write the following queries in SQL: (i) Find all the tuples having temperature greater than that of Paris. (ii) Find the names of those cities with temperature and condition whose condition is neither Sunny nor Cloudy but temperature must be greater than 70. (iii) Find all the cities with temperature, condition and humidity, whose hu- midity is in the range of 63 to 79. C) What is view? What are its advantages? Explain views in SQL with suitable L2/ CO2 6 example. Q.4 Solve Any Two of the following. 12  A) What is Normalization? Explain the importance of normalization.What is L2/ CO3 6 the criteria for good relation design? B) Explain BCNF with suitable example and distinguish between BCNF and L2,L3,L4/ 6 3NF. CO3 Is relation R(student_no, course_no,instr_no) with F = { {student_no, course_no} instr_no, instr_no course_no} in BCNF and 3NF? Justify your answer. C) Consider the relation schema R=(A,B,C,G,H,I) and Set of functional de- L3,L4/ 6 pendencies: CO3 A→B A→C CG → H CG → I B→H Compute (AG)+.Is it candidate key? Justify your answer. Q. 5 Solve Any One of the following. 12 A) Explain ACID properties in detail L2/CO5 6 B) What are ordered indices? Explain with suitable example. Distinguish L2,L4/ 6 between dense index and sparse index. CO4 C) Construct a B+-tree for the following set of key values: L3/ CO4 6 (2, 3, 5, 7, 11, 17, 19, 23, 29, 31) Assume that the tree is initially empty and values are added in ascending order. Construct B+-trees for four pointers that will fit in one node *** End *** The grid and the borders of the table will be hidden before final printing.  DR. BABASAHEB AMBEDKAR TECHNOLOGICAL UNIVERSITY, LONERE Q. 3 Solve Any Two of the following. 12 Summer Examination – 2023 A) What is Normalization? Explain various anomalies with suitable example. 02/CO3 6 Course: B. Tech. Branch : Electronics & Computer Engineering Semester :IV B) A company has following entities and respective attributes, where EmpPhone is Subject Code & Name: Database Management System (BTECPC402) a multivalued and Address is a composite attribute. Draw the ER-Diagram for Max Marks: 60 Date: 15.07.2023 Duration: 3 Hr. the same; Instructions to the Students: 1. All the questions are compulsory. Entity Attributes 03/CO3 6 2. The level of question/expected answer as per OBE or the Course Outcome (CO) Employee EmpId, EmpName, Gender, DoJ, Designation, EmpSal on which the question is based is mentioned in ( ) in front of the question. 3. Use of non-programmable scientific calculators is allowed. Contact Details EmpPhone, EmpMailID, Address 4. Assume suitable data wherever necessary and mention it clearly. Department DepId, DepName, DepLocation, DepHead. (Level / Marks Project ProjId, ProjName, ProjLocation,ProjDuration CO) Q. 1 Solve Any Two of the following. 12 C) Solve the following A) With help of a diagram, explain the difference between 2-tier and 3-tier 02/CO1 6 a. Find the Primary & Non-Primary Attributes of architecture. Justify which architecture is better suited for web applications? 03/CO3 6 R (A,B,C,D,E) FD: {A BCDE, BC ACE, D E} B) Describe with a diagram the level of abstractions available in database systems. 02/CO1 6 b. Identity whether the given equations is in 2NF or not R (A,B,C,D) FD: {AB CD, C A, D B} C) Describe the components of a a) Query Processor b) Storage Processor 02/CO1 6 Q.4 Solve Any Two of the following. 12 A) Draw a state diagram and discuss the typical states that a transaction goes 03/CO4 6 Q.2 Solve Any Two of the following. 12 through during execution. A) Write SQL commands, B) What is concurrency control? Explain lock based protocols for concurrency 02/CO4 6 i. To Create table for following schema: Student {Roll-No (PK), Name, control. Class, DOB} 03/CO2 6 C) Describe shortly ii. To add new column percentage to student table. 02/CO4 6 a) Database Recovery Techniques iii. To drop column DOB from student table. b) Causes of Database failures Q. 5 Solve Any Two of the following. 12 B) Consider an EMPLOYEE table having Eid, Name, Age, Address, and Salary. A) State the CAP theorem. Explain each term of CAP with suitable example. 01/CO5 6 Using subqueries, write a query to B) Describe the concept of Document and Collection in MongoDB. 02/CO5 6 i) Display all details where Salary>45000 03/CO2 6 C) For MongoDB, write a query to 03/CO5 6 ii) Delete all the records of employee whose age25000 and Br_name=”Dharashiv) d) To display Acno, AcName, Amount From Account where the amount>50000 C) Explain Equijoin, left join and right join with suitable example and write proper CO2/2 6 relational algebraic query for each of them. Q. 3 Solve Any Two of the following. 12 A) Mention different SQL languages. Explain their purpose with suitable SQL CO3/1,2 6 commands. B) Write the SQL Queries for the following. CO3/3 6  a) Creating, Using, showing and deleting databases. b) To create BOOK table with Bkid, Title, Author, branch, cost and date of publishing. (Assume proper data type for each column). c) Write query to insert 2 valid rows of values into the BOOK table. d) Write query to increase the length of Title and Author. e) Write query to list books with cost less than 100. f) Write query to list all books with title “RDBMS”. C) Consider the following schema: CO3/3 6 Suppliers (sid : integer, sname : string, address : string) Parts (pid : integer, pname : string, color : string) Catalog (sid : integer, pid : integer, cost : real) Write query for the following a) To create above tables b) Find the name of suppliers who supply some red parts c) Find the sids of suppliers who supply some red or green parts d) Find the sids of suppliers who supply some red part or are at 221 packer Ave e) Find the sids of suppliers who supply some red part and some green part Q.4 Solve Any Two of the following. 12 A) What is normalization? Why one need to normalize the database tables? CO4/2 6 st nd rd B) Explain 1 , 2 and 3 normal form with the help of suitable example. CO4/2 6 C) Why we need to organize database files? Mention the type of organisations, and CO3/2 6 explain any one with suitable example. Q. 5 Solve Any Two of the following. 12 A) Define Transaction and their properties with suitable example. CO5/2 6 B) Define serial and non-serial schedules. Explain serilisabilty with suitable example. CO5/2 6 C) Explain the need of concurrency control. Mention the concurrency control CO5/2 6 methods. Explain any one with suitable example. *** End ***  DR. BABASAHEB AMBEDKAR TECHNOLOGICAL UNIVERSITY, LONERE Supplementary Summer Examination – 2024 Course: B. Tech. Branch : Computer Engineering/Computer Science and Engineering Subject Code & Name: Database Systems (BTCOC501) Semester :V Max Marks: 60 Date:01/07/2024 Duration: 3 Hr. Instructions to the Students: 1. All the questions are compulsory. 2. The level of question/expected answer as per OBE or the Course Outcome (CO) on which the question is based is mentioned in ( ) in front of the question. 3. Use of non-programmable scientific calculators is allowed. 4. Assume suitable data wherever necessary and mention it clearly. (Level/CO) Marks Q. 1 Solve Any Two of the following. 12 A) Draw and explain the detailed system architecture of DBMS. Understand 6 B) Explain in detail about various key constraints used in database system. Understand 6 C) Discuss the main characteristics of the database approach and specify how Analysis 6 it differs from traditional file system? Q.2 Solve Any Two of the following. 12 A) Write a short notes on Understand 6 i) Foreign Key ii) Relation state iii) Database schema. B) account(account_number, branch_name, balance) Apply 6 branch (branch_name, branch_city, assets) customer (customer_name ,customer_street, customer_city) loan (loan_number, branch_name, amount) depositor((customer_name, account_number) borrower(customer_name, loan_number) Write the following queries in SQL: 1)For all customers who have a loan from the bank, find their names, loan numbers, and loan amount. 2) Find the customer names, loan numbers, and loan amounts, for all loans at the Panvel branch. 3)Find the names of all branches that have assets greater than those of at least one branch located in Mumbai. 4)Find the average account balance of those branches where the account balance is greater than Rs. 1500. 5) Find the maximum across all branches of the total balance at each branch. C) Write the SQL syntax for the following with example: Understand 6  1)SELECT 2) ALTER 3)UPDATE Q. 3 Solve Any Two of the following. 12 A) Write an SQL query for the following: Apply 6 a)To create a table of Hospital database with minimum 4 fields b) To insert two records c) To add new field d)To display all records B) What are JOINS? Explain INNER JOIN and OUTER JOIN. Understand 6 C) Explain different types of trigger. Understand 6 Q.4 Solve Any Two of the following. 12 A) State BCNF. How does it differ from 3NF? Analysis 6 B) Explain about dynamic multilevel indexing using B+ trees. Understand 6 C) Define Multi-valued dependency. Explain the Fourth normal form with an Understand 6 example. Q. 5 Solve Any Two of the following. 12 A) Explain in detail about timestamp based concurrency control techniques. Understand 6 B) Explain ACID properties of a transaction. Understand 6 C) Explain how Concurrency control can be achieved with locking methods? Understand 6 *** End ***