Database Management Systems University Question 1

Database Management Systems University Question 1

[Click on the links to visit the answer page]

Database Management Systems

Time : Three Hours                                                                          Max.Marks:100

PART – A (8 X 5 = 40 Marks)

Answer ALL Questions

1. Describe the three levels of data abstraction with a neat diagram.

2. Consider the following relation schema

Depositor(customername, accountnumber)

Borrower(customername, loanno)

Loan(loanno, branchname, amount)

Write the following queries in relational algebra:

a) Find the customers that have both account and loan. [2.5]

b) Find the names of the customers who have loan at Chennai branch. [2.5]

3. How do you map a binary 1:1, 1: Nand M: N relationship type into a relation database schema. Describe with asuitable example.

4. For a relation R(A,B,C,D,E,H) having the set of functional dependencies

F={A->BC, CD->E, E->C, D->AEH, ABH->BD, DH->BC}

Find the key for the relation R.

5. Since indices speed query processing, why might they not be kept on several search keys? Give reasons.

6. Briefly describe the stepsinvolved in query processing.

7. Database-system implementers have paid much more attention to the ACID properties than have file-system implementers. Why might this be the case?

8. Discuss the need for object oriented Databases. Explain with example.

PART – B (6 X 10 = 60 Marks)

Answer any SIX Questions

9. a) Differentiate between traditional file processing systems and database systems. [5]

b) Discuss, briefly, the features of different data models. [5]

10. A university registrar’s office maintains data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title.

Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints.

11. Prove that

a) If X ⊇ Y then X → Y                                            [5]

b) If X → Y and Y → Z then X → Z                                    [5]

12. Write an equivalent relational algebra query for the following SQL query and show the steps of Query Optimization from initial query tree to final query tree.

SELECT P.PNUMBER,P.DNUM,E.ENAME, E.ADDRESS, E.BDATE

FROM PROJECT AS P,DEPARTMENT AS D, EMPLOYEE AS E WHERE

P.DNUM=D.DNUMBER AND D.MGRSSN=E.SSN AND

P.PLOCATION=‘BANGALORE’;

13. Differentiate between different single level indexing methods with suitable examples.

14. a) List the ACID properties. Explain the usefulness of each with example. [5]

b) What benefit does strict two-phase locking and rigorous two phase locking provide. Discuss if they provide any disadvantages. [5]

15. Compare the deferred- and immediate-modification versions of the log-based recovery scheme in terms of ease of implementation and overhead cost.

16. a) Differentiate between OLAP and OLTP. [5]

b) Explain various types of OLAP operations. [5]

*****************