Database Management System – 15CS53 Question Bank 3
Advances Queries
1. Consider the following relational database schema
Student ( Student-id, Sname, major, GPA)
Faculty (Faculty-id, fname, dept,designation,salary)
Course (Course-id,Cname,Faculty-id) Enrol (Course-id,Student-id,grade)
Write the following queries in SQL:
List the names of all students enrolled for the course “15CS53” and have received “A” grade.
List all the departments having an average salary of above Rs. 10,000.
Give a 20% raise to a salary of all faculty.
Add ‘John’ as an employee with id = 99, and Major =’Maths’ GPA=’B’.
Retrieve the faculty who gets the second highest salary.
2. Consider the following relations for a database.
Supplier (Sno, Sname, Status, City)
Product (EnQ. Pname, Color. Weight, City) Shipments (Sno, Pno, Qty)
3. What are the aggregate functions? And list the aggregate functions supported by SQL?
4. What is view in SQL? How is it defined?
5. List and explain the statements associated with a database transaction?
6. Describe queries with complex types?
7. What are the types of reference? Explain it with suitable example.
8. Give the syntax of assertion & triggers?
DBMS QUESTION BANK – Module 4
Normalization: Database Design Theory
1. What is the need for normalization? Explain the 1 NF, 2 NF, and NF with examples.
2. Explain informal design guidelines for relation schemas.
3. What is the need for normalization? Explain the second normal form.
4. Which normal form is based on the concept of transitive dependency? Explain with an example the decomposition into 3NF.
5. Explain multivalued dependency. Explain 4NF with an example.
6. Explain Inclusion dependency, Domain Key Normal Form.
7. Explain lossless join property.
8. What are the ACID Properties? Explain any One?
9. What is serializability? How can serializability? Justify your answer?
MODULE 5- Transaction Management
1. What are the ACID properties? Explain.
2. Explain the properties of a transaction.
3. With a neat state transition diagram, discuss the different states of the transaction.
4. What is a schedule? Explain with an example conflict serializable schedule.
5. What are the anomalies occur due to interleaving execution (concurrent transactions)? Explain them with an example.
6. Explain transaction support in SQL.
7. Briefly discuss the two-phase locking (2PL) protocol used in concurrency control. How does it guarantee serializability? Discuss its advantages. How does it vary from strict 2PL?
8. Write a note on Timestamp-based concurrency control, Write ahead log protocol, Shadow paging
9. Explain Multiversion concurrency control with an example.
10. Explain Multiple granularity locking with suitable examples.
11. Explain all the phases involved in the ARIES algorithm with an example/ describe the three steps in crash recovery in Aries. What is the goal of each phase?
For regular updates please like the facebook page