Database Management System : End Sem Examination ( 4th Sem )

Database Management System

End Semester Examination, 2021-22
B. Tech - Semester : 04
Time : 03 hrs. - Max. Marks : 100

Instructions:

1. All questions are Compulsory
2. Assume missing data suitably, if any.

Section : A ( 10 x 4 = 40 Marks )

All questions are compulsory

1. Compare Database System and File System. Discuss the advantage of DBMS.
2. Define Database schema and Data Independence in DBMS.
3. Explain the different symbols of ER diagram. Differentiate between single valued and multi valued attributes.
4. Explain the terms DDL, DML and DCL with suitable examples.
5. Discuss the six basis operators used in Relational Algebra.
6. How can we differentiate all possible join operations? Show it by example.
7. Explain Procedural and Declarative Paradigms used in Relational Query.
8. Explain the concept of normalizations by explaining 4th normal Normal Form with suitable example.
9. Explain ACID properties of transaction processing system.
10. Discuss Lock-Based and Timestamp-Based Protocols used in Concurrency Control.

Section : B ( 3 x 6 = 18 Marks )

All questions are compulsory

1. Explain the following with example:
   a. Relational Data Model Concepts
   b. Mapping Constraints
   c. Relational Algebra Operations
   
   OR
   Look in the relational schemas as follows:
   Teacher ( TeacherId, Tname, Department )
   Student ( RollNo, Sname, Branch )
   Teaches ( TeacherId, RollNo, Subject )
   Write the following queries in SQL:
   a. Write the SQL statement to create & insert above database.
   b. List the name of teachers and their concerned department who are offering either 'DBMS' or 'Operating System'.
2. Explain Multi valued Dependency. Compare between MVD and FD.
   
   OR
   Define Different anomalies in designing a Database.
3. Explain Schedule and Serializability? Explain conflict serializability with suitable example.
   
   OR
   List out different type of failure. Explain the use of cascading rollback and recoverable scheduling through example.

Section : C ( 3 x 10 = 30 Marks )

All questions are compulsory

1. Draw ER Diagram of University Examination System which demonstrate the following entities, single value attributes, multi-valued attribute and all possible cardinalities.
   
   OR
   Explain the concept of Primary Key, Super Key, Foreign Key and Candidate Key with help of use case in University Management.
2. Consider the following relation for published books :
   BOOK (Book_title, Author_name, Book_type, List_price, Author_affil, Publisher)
   Author_affil refers to the affiliation of author. Suppose the following dependencies exist :
   Book_title -> Publisher,
   Book_type Book_type -> List_price
   Author_namr -> Author_affil
   a. What normal form is the relation in? Explain your answers.
   b. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.
   
   OR
   Write short notes on the following.
   a. Trivial and Partial Dependency
   b. Boyce Codd Normal Form
   c. Dependency Preservation
   Anomalies in designing a Database.
3. What is a precedence graph? What is a conflict serializable schedule? Can precedence graph be used to detect a conflict serializable schedule? Explain the process.
   
   OR
   What do you understand by Concurrency Control? Explain in brief working of two-phase locking protocol with example.

Section : D ( 1 x 12 = 12Marks )

All questions are compulsory

1. Consider the following schema for institute library :
   Student (rollno, name, father-name, branch)
   Book (ISBN, title, author, publisher)
   Issue (rollno, ISBN, date-of-issue)
   Write the following queries in relational algebra:
   a. List roll number and name of all students of the branch 'CSE'.
   b. Find the name of students who have issued a book published by 'ABC' publisher.
   c. List title of all books and their authors issued by a student 'XYZ,.
   d. List all books published by publisher 'ABC'.
   
   OR
   Consider the following schedules, the actions are listed along with transaction name in the order in which these are scheduled:
   S1: T1:R(X), T2:R(X), T1:W(Y), T2:W(Y), T1:R(Y), T2:R(Y)
   S2: T3:W(X). T1:R(X), T1:W(Y), T2:R(Z), T2:W(Z), T3:R(Z)
   a. Draw the precedence graph of the schedules.
   b. Is the schedule conflict serializable? If so, then what is conflict equivalent serial schedule?
   c. Re-write the above schedule S1 and S2:
   i. Recoverable schedules using no dirty read
   ii. Recoverable schedule using dirty read
   iii. Irrecoverable schedule.
   
   ******************