This is the question set along with answers of Database Management System Spring 2016, which was taken by the Pokhara University.

Pokhara University – Database Management System, Spring 2016

Level:  Bachelor	Semester –Spring	Year: 2016
Program: BCIS		Full Marks: 100
Course: Database Management System		Pass Marks: 45
		Time:  3hrs.

 

            Section “A”

Very Short Answer Questions

Attempt all the questions.                                                           10×2

1. Describe any two tables that might be used to store information in a social networking system such as Facebook.
2. What are the four main functions of a database administrator?
3. Define the terms: primary keys, candidate key, and super key.
4. Draw a relation showing the basic data structure model in it.
5. Write the function of ‘GRANT’ and ‘REVOKE’ in MySQL.
6. What is a Materialized View?
7. Enlist Basic data types used in DBMS.
8. Enlist the type of functional dependency.
9. How Lock-Based protocol works?
10. What are the various types of transaction failures?

            Section “B”

Descriptive Answer Questions

Attempt any six questions                                                      6×10

1. Illustrate mapping cardinality with suitable examples of each.
2. A database of a Library System consists of three minimum relations(a) Member, (b) book, and (c) issue. Draw a database schema along with the primary key, foreign key dependencies, relation name, and minimum of four attributes in each relation.    
3. Consider the following relations:

Course (CID, title, dept_name, credits)

Student (SID, name, dept_name,tot_credits)

Teacher (TID, name, dept_name, salary)

Write SQL statements to:

1. Create relation student.
2. Find the titles of courses in the ‘Computer Science’ department that have 3 credits.
3. Find the highest salary of any Teacher.
4. Find all the teachers earning the highest salary (hint: there may be more than one with the same salary)

If you need to create a relation Teaching, what will be the attributes?

1. Illustrate the terms UNION, INTERSECT, DIFFERENCE, EXCEPT, and COMMIT with an example of each.
2. Check for redundancy of Functional Dependency in R (W X Y Z) with X →W, WZ →XY, Y →WXZ. If exists convert it into canonical form.
3. What is ACID property? Explain serializability in Transaction.
4. How do you prevent deadlock? In data recovery, explain log-based recovery and checkpoint

 

            Section “C”

Attempt all questions

18. Read the case situation given below and answer the questions that follow.

Moon Set Colony has 5 blocks of Towers (buildings). Each block has 15 floors of apartments. Each apartment has one guest room, one living room, one dining room, and two bedrooms. In the basement, every family gets a parking slot for one car and one motorcycle. A separate parking system is made for guests/visitors. The management wants to install DBMS on the gate with the features:

• Any visitor is asked to tell the name of the resident of the colony s/he wants to meet and it is verified in the database before entry.
• The visitor’s detailed information is recorded.
• If the visitor is in the vehicle, its information also be recorded.
• The database also provides family details, location, the vehicular status of the people who reside in this colony.

1. Draw an E-R diagram of the system.
2. Enlist the max number of tables you require in the system.

• Show all possible and relevant attributes of at least two tables.

1. Offer some Views (logical) of the system.