(Page 1 of 2)
USN 22CSU403
B. E. Degree (Autonomous) Fourth Semester End Examination (SEE)
DATABASE MANAGEMENT SYSTEMS
(Model Question Paper – III)
Time: 3 Hours] [Maximum Marks: 100
Instructions to students:
Answer FIVE FULL questions
RBT
Q.No. Questions Marks CO Cognitive
Level
Discuss how insulation between programs and data and data
a) 7 CO1 L2
abstraction is carried out in database systems.
1. Discuss schemas, instances and database state with an
b) 7 CO1 L2
example in detail.
c) Discuss the various DBMS languages in detail. 6 CO1 L2
OR
Discuss redundancy control, restricting unauthorized access
a) and providing persistent storage for program objects 6 CO1 L2
capabilities in the database systems.
2. Explain the attribute types and illustrate their use via
b) 7 CO1 L3
examples.
Explain entity types, entity sets, key attributes of an entity
c) 7 CO1 L3
type and value sets of attributes in detail.
Explain Aggregate functions and grouping by considering
a) 10 CO1 L3
suitable examples.
With examples, describe the INSERT operation by
3. b) 5 CO1 L2
considering referential integrity.
Discuss DIVISION operation by considering an example in
c) 5 CO1 L2
detail.
OR
4. Explain sequences of Operations and RENAME operation in
a) 7 CO1 L3
detail by considering suitable examples
b) Discuss the characteristics of the relation by considering an 7 CO1 L2
example.
Dr. Ambedkar Institute of Technology, Bengaluru – 560056
(An Autonomous Institution Affiliated to Visvesvaraya Technological University, Belgaum)
(Page 2 of 2)
Discuss UNION, INTERSECTION and MINUS operations
c) 6 CO1 L2
in detail by considering suitable examples.
5. Consider the below relational schema
a) 8 CO2 L3
Write the queries for below in SQL
a) Retrieve the names of all employees who do not have
supervisors.
b) Retrieve the names of employees whose salary is
greater than the salary of all the employees in
department 5.
c) Retrieve the name of each employee who has a
dependent with the same first name and is the same
sex as the employee.
Consider the relational schema given in the previous
question and write the queries for below in SQL.
a) Find the sum of the salaries of all employees of the
‘Research’ department, as well as the maximum
salary, the minimum salary, and the average salary in
this department.
b) b) Retrieve the total number of employees in the 8 CO2 L3
company and the number of employees in the
‘Research’ department.
c) Count the number of distinct salary values in the
database.
d) Retrieve the names of all employees who have two or
more dependents
c) Consider the relational schema given above and write the 4 CO2 L3
queries for below in SQL.
a) For each project, retrieve the project number, the
project name, and the number of employees from
department 5 who work on the project.
b) For each department that has more than five
Dr. Ambedkar Institute of Technology, Bengaluru – 560056
(An Autonomous Institution Affiliated to Visvesvaraya Technological University, Belgaum)
(Page 3 of 2)
employees, retrieve the department number and the
number of its employees who are making more than
$40,000.
OR
Consider the below relational schema
a) 10 CO2 L3
6.
Write the queries for below in SQL
a) For each department, retrieve the department number,
the number of employees in the department, and their
average salary.
b) For each project, retrieve the project number, the
project name, and the number of employees who
work on that project.
c) For each project on which more than two employees
work, retrieve the project number, the project name,
and the number of employees who work on the
project.
d) List the names of managers who have at least one
dependent.
b) Explain the need for triggers in SQL in detail. 10 CO2 L3
Explain relational synthesis into 3NF with dependency
a) preservation and nonadditive join property algorithm by 10 CO3 L3
considering an example.
7.
Discuss multivalued dependency and fourth normal form in
b) designing relation database systems by considering 10 CO3 L2
appropriate example.
OR
8. a) Discuss second normal form (2NF) and third normal form 10 CO3 L2
(3NF) for designing a relational schema by taking a suitable
Dr. Ambedkar Institute of Technology, Bengaluru – 560056
(An Autonomous Institution Affiliated to Visvesvaraya Technological University, Belgaum)
(Page 4 of 2)
example.
Discuss functional dependency in database systems by
b) 10 CO3 L2
considering suitable examples.
Explain the timestamp ordering algorithm for concurrency
a) 10 CO4 L3
control in database systems.
9.
Discuss desirable properties of transactions for database
b) 10 CO4 L3
systems.
OR
Explain characterizing schedules based on recoverability in
a) 10 CO4 L3
database systems.
10.
Discuss commit point of a transaction and DBMS specific
b) 10 CO4 L2
buffer replacement policies.
******
Dr. Ambedkar Institute of Technology, Bengaluru – 560056
(An Autonomous Institution Affiliated to Visvesvaraya Technological University, Belgaum)
