MODEL PAPER

B. Tech (CSE) Degree Examination

Third Year - Second Semester

DATABASE MANAGEMENT SYSTEMS

Effective from the admitted batch of 2004-2005

Time: 3 hrs
Max Marks: 70

First Question is Compulsory

Answer any four from the remaining questions

All Questions carry equal marks

Answer all parts of any question at one place

1. Define the following terms
a) Database
b) DBA tasks
c) Entity
d) Domain
e) Attribute
f) Relation
g) Logical Design

2. a) In what sense does relational calculus differ from relational algebra, and in what sense they are similar?

b) How does tuple relational calculus differ from domain relational calculus?

c) Discuss the meanings of existential quantifier and the universal quantifier.

3. Write the SQL Queries for the following: The tables are EMP(Emp no. ename. job, mgr, hiredate, sal, comm., dept no)
Dept (Dept no, dname, loc)
Salgrade(grade, losal, hisal)

a) Find the employees who earn the highest salary in each job type. Sort in descending salary order.
b) Find the most recently hired employees in each department order by hire date.
c) Show (ename, sal, dept no) details of any employee who earns a salary greater than the average salary for their dept. sort in dept no.
d) Display the 5th row of the emp. Table.

4. a) Construct an E-R diagram for University Registrar’s Office. The office maintains data about each class, including the instructor, the enrollment and the time and place of the class meetings. For each student class pair, a grade is recorded. Determine the entities and relationships that exist between the entities. Also construct the tabular representation of the entities and relationships.

b) What is an entity type? What is an entityset? Explain the difference between the entity, entity type and entityset?

5. What is normalization? Explain about 3rd, BCNF, 4th normal forms in detail by taking an example. Highlight the concept of various dependencies.

6. A table with the following fields was created for billing of customer in a hotel.
Customer’s name, address, date of arrival, room number, room type, room rent, number of local calls made, date of call, rate per call (for each date), clothes give for laundry, date, charge per cloth (for each date). Is it good design? If it is not. identify errors and correct the design using normal forms.

7. (a) What is a system crash? Write the various causes for it.

(b) Write about log-based recovery and shadow paging.

8. a) What is meant by the concurrent execution of database transactions in a multi-user system?

b) Discuss why concurrency control is needed. Give informal example.