DBMS Question Bank PDF

Question Bank Subject:DBS Unit 1: 1. Define DBMS.Explain Applications of DBMS. 2. What is DBMS? Explain the need of DBMS. Write any 3 application of DBMS 3. llustrate the database characteristics. How they are different from File systems OR Write Difference between DBMS an...

Question Bank Subject:DBS Unit 1: 1. Define DBMS.Explain Applications of DBMS. 2. What is DBMS? Explain the need of DBMS. Write any 3 application of DBMS 3. llustrate the database characteristics. How they are different from File systems OR Write Difference between DBMS and File Processing 4. Write Benefits of DBMS 5. What are disadvantage of File processing system over DBMS 6. What are the problems in file system data management? Explain in detail with relevant example. 7. Why would you choose a database system instead of simple storing data in file processing system? Compare file processing system and DBMS 8. Explain the importance of Null values in Relational Model. 9. Illustrate about integrity and key constraints with suitable examples? 10. Write Difference between Two tier and Three Tier Architecture. 11. State different notations of ER Diagram 12. What is ER model? Explain various symbols/components used in ER diagram 13. Define an Entity and Attribute. Explain the different types of attributes that occur in an ER diagram model, with an example 14. Draw an E-R diagram of Banking system taking into account at least five entities, indicate all keys, constraints and assumptions that are made. 15. What is role of Database Administrator? 16. Explain in detail the Data Model and its types. 17. Describe about various keys in relational model. Explain in detail 18. Explain data abstraction levels of database. 19. With the help of block diagram, describe the basic architecture of a database management system. 20. Construct an E-R diagram for a car insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents. Each insurance policy covers one or more cars, and has one or more premium payments associated with it. Each payment is for a particular period of time, and has an associated due date, and the date when the payment was received Unit 2 and 3: 1. Explain in detail, the form of a basic SQL query with a suitable example 2. What is relational algebra?Explain different operations of algebra? 3. Difference between algebra and calculus 4. List out various SET comparison operator in SQL and also write about its use in writing SQL queries. 5. Define and differentiate the following relational algebra operators with suitable example: (i) Cartesian product (ii) Natural join 6. Write SQL Queries for the following set of tables: 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. 7. Write SQL Queries for the following set of tables: 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 8. By considering an example describe various data update operations in SQL. a. Let the following relation schemas be given R = (A, B, C), S= (A, D, E ) Let relations r (R) and s ( S) be given. Give an expression in SQL that is equivalent to each of the following queries. i. ∏A,E( σC=D (r X s) ii. r ⋈ s iii. ∏A (r) ∩ ∏A (s) 9. Consider the following relational schema. Suppliers(sid, sname, address) 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 10. Consider the following employee database.  Give expression in tuple relational calculus for each of the following queries. 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 company for which the work is located. 11. Consider the following schema and solve following queries using SQL. 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 employee with emp_no greater than or equal to 1234 12. Consider the following relational schema. Weather (city, temperature, humidity, condition) 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 humidity is in the range of 63 to 79. 13. Consider the following database Student(name, s_no, class, major) Course(c-name, c_no, credit_hours, department) Write SQL statements to do the following update on the database schema (1) Insert a new student,,in the database. (2) Change the credit_hours of course ‘Data Science’ to 4.  (3) Delete the record for the student whose name is ‘Smith’ and whose student number is 17. 14. What is view? What are its advantages? Explain views in SQL with suitable example. 15. Explain various Data Definition Commands in details with syntax. 16. Explain Data Manipulation Commands with syntax and examples. 17. Explain SELECT query using various clauses with syntax and examples. 18. Discuss about different advanced Data Definition Commands. 19. Explain advanced SELECT Queries with examples. 20. Write commands to create virtual tables and to show rows from virtual tables. 21. Write queries using Relational Set operators and SQL Join operators. 22. Write queries using Sub queries and correlated queries. 23. Classify SQL Functions. Explain numeric functions with explanations. 24. Explain Group By feature with HAVING Clause with example 25. Explain the following: a. Logical view of data b. Table & their Characteristics 26. What the things we have to follow while selecting primary key? 27. Explain the following briefly: a. Entity integrity b. Referential Integrity Unit 4: 1. State BCNF. How does it differ from 3NF? 2. Define Functional Dependencies. Explain First, Second normal forms with relevant table 3. Explain insertion, deletion and modification anomalies with suitable examples. 4. Explain in detail about internal hashing Techniques. 5. Discuss in detail about cluster and Multilevel indexes. 6. What is Normalization? Explain the importance of normalization.What is the criteria for good relation design? 7. Explain the following terms: a. Functional dependencies b. Fully functional dependencies c. Transitive dependencies 8. Explain BCNF with suitable example and distinguish between BCNF and 3NF. 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.  9. Consider the relation schema R=(A,B,C,G,H,I) and Set of functional dependencies: A→B A→C CG → H CG → I B→H Compute (AG)+. Is it candidate key? Justify your answer. 10. What are ordered indices? Explain with suitable example. Distinguish between dense index and sparse index. 11. Construct a B+-tree for the following set of key values: (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 pointers that will fit in one node as follows i)Six ii)Four 12. Compute the closure of the following set F of functional dependencies for relation schema r (A, B, C, D, E). A→BC CD→E B→D E→A List the candidate keys for R. Unit 5: 1. Explain ACID properties in detail 2. Explain Concurrency control with locking methods. 3. What is transaction? Explain State of Transaction. 4. What is transaction? Explain the ACID Properties. 5. Explain various locking methods with examples. 6. Define ACID. Explain about scheduling in transaction management method. 7. Define Concurrency control. Explain different concurrency control. 8. What are the different types locking? 9. Explain Lock-based Concurrency control with diagram. 10. Explain about concurrency control based on time-stamp ordering. 11. Explain how to implement atomicity and durability. 12. Define deadlock. Explain the techniques to control deadlocks. 13. Explain concurrency control with optimistic methods. 14. Explain the terms: a. Shared lock b. Exclusive lock