BITM 4th Semester
Database Management System Board Question Paper 2023

TRIBHUVAN UNIVERSITY
FACULTY OF MANAGEMENT
Office of the Dean
March - April 2023
Full Marks:40 Pass Marks:20 Time:2 hrs.
BIM /
Fourth Semester /
IT 220:
Database Management System

Candidates are required to give their answers in their own words as for as practicable.
The figures in the margin indicate full marks

Long Answer Questions
Section "A"

Brief Answer Questions:

[10 × 1 = 10]
1.

Define Data abstraction.

2.

Define Candidate key.

3.

Write the advantages of object relational data model.

4.

Write a syntax to rename a table name in SQL.

5.

Write a syntax for ORDER BY clause.

6.

Write down the use of GROUP BY clause.

7.

What are the different types of outer join operation in SQL?

8.

Define loss less - Join decomposition.

9.

What is the use of mapping cardinalities?

10.

Define the durability property of transaction.

Section "B"

Exercise Problems (Any Five):

[5 × 4 = 20]
11.

Explain the need of aggregation with example.

12.

Draw an ER diagram for database showing bank. Each bank can have multiple branches, and each branch can have multiple accounts and loans.

13.

What is participation constraint? Explain the different type of participation constraints.

14.

Determine the normal form of following student table. If it is not in 3NF then normalize to 3NF.

StudentIDStudentNameCourseIDCourse NameCreditContact_No
101RamIT220DBMS39841XXXXXX,5573XXX
102SitaIT220DBMS39950XXXXXX
103JohnIT218DSA with JAVA39371XXXXXX, 2365XXXX
104JennyECO201Micro Econom ics39985XXXXXX, 4395XXX
Given functional dependencies:
StudentID →StudentName
CourseID →CourseName
CourseID →Credit

15.

Why concurrency control is needed? Discuss with suitable examples.

16.

Explain time stamp based locking protocol with example.

Section "C"

Comprehensive Answer Questions (Any Two):

[2 × 5 = 10]
17.

Explain the types of database architecture with example.

18.

If a multinational company consult you to design a database architecture of their company, as being Database Consultant, which architecture will you suggest among centralized and distributed, and explain why?

19.

Assume a HR database of a Company. Where primary keys are underlined:
employees (EmpID, FirstName, LastName, Salary, DeptID)
departments (DeptID, DeptName, LocationID)
locations (LocationID, StreetAddress, PostalCode, City, ProvinceNo)
Write the SQL queries for each of the following cases.
a) The HR department needs a report to display the EmpID, first name, salary, for each employee whose salary is greater than 25,000 and less than 50,000.
b) Write a query to display the last name, salary, department name of all employees whose department id is 26.
c) Write a query to display the first name, department ID, department name, city for all employees who works in Lalitpur.
d) Update the salary of employee with 50000 whose EmpID is 220.
e) Create a view for employees table named as EmpView with attributes EmpID, FirstName and Salary.