CY-405 (GS) – Database Management System

B.Tech., IV Semester
Examination, June 2022
Grading System (GS)
Max Marks: 70 | Time: 3 Hours

Note:
i) Answer any five questions.
ii) All question carries equal marks.

Previous Year Questions (June 2022)

Q.1

a) Mention the drawbacks of traditional file-based systems that make a database management system a better option. (Unit 1)

b) Define data, database, and database management system. Discuss the advantages of DBMS? (Unit 1)
Q.2

a) What is meant by an entity-relationship (E-R) model? Explain the terms Entity, Entity Type and Entity Set in DBMS. (Unit 1)

b) Draw E-R Diagram of Library Management System. (Unit 1)
Q.3

a) What is the difference between a relation and a relation schema? Discuss their relationship. (Unit 2)

b) What is a Foreign key? List the properties of a foreign key. Explain with the help of an example. (Unit 2)
Q.4

a) Write a query and explain with examples that JOIN two tables so that all rows from the first table are in the result. (Unit 2)

b) What is the difference between a LEFT JOIN and a FULL JOIN? Explain with the help of an example. (Unit 2)
Q.5

a) Explain aggregate function with syntax and give an example. (Unit 2)

b) Write the SQL query for the followings:
i) Fetch the EmpId and FullName of all the employees working under Manager with id '121' from Employee table.
ii) Fetch the different projects available from the Employee Salary table.
iii) Fetch the count of employees working in project 'P1' from Employee Salary table.
iv) Find the maximum, minimum, and average salary of the employees from Employee Salary table.
v) Find the employee id whose salary lies in the range of 9000 and 15000 from Employee Salary Table. (Unit 2)
Q.6

a) What is difference between Lossless join decomposition and lossy less join decomposition? Explain with examples. (Unit 3)

b) Given a relation $R(A,B,C,D)$ and Functional Dependency set $FD=\{AB\rightarrow CD, B\rightarrow C\}$, determine whether the given R is in 2NF? If not convert it into 2 NF. (Unit 3)
Q.7

a) What is Log? How is it maintained? Discuss the salient features of deferred database modification and immediate database modification strategies in brief. (Unit 4)

b) What is Recoverable schedule? Why is recoverability of schedules desirable? Are there any circumstances under which it would be desirable to allow non-recoverable schedules? Explain your answer (Unit 4)
Q.8

a) Why query Optimization is needed? What is the difference between query processing and query optimization? (Unit 5)

b) What are the phases of Query Processing? How are query processing and query optimization related? (Unit 5)