Database Management System 2067

Attempt all the questions.

1. Answer the following questions in short:(5 x 2 = 10)

    a) Differentiate between logical data independence and physical data independence.

    b) Three-schema architectures.

    c) Differentiate between database schema and a database state.

    d) Different type of data attributes.

    e) The difference among a relationship instance, a relationship type, and relationship set.

2. a) Draw an ER diagram for database showing Bank. Each Bank can have multiple branches, and each branch can have multiple accounts and loans. (6)

    b) In what sense does a relational calculus differ from relational algebra, and in what sense are they similar? (4)

3. a) Assume a database about Company.(5)

     EMPLOYEE (ss#, name)

     COMPANY (cname, address)

     WORKS (ss#, cname)

     SUPERVISE ( superviser_ss#, employee_ss#)

     Write relational algebra and SQL queries for each of the following cases.

       i) Find the names of all supervisors that work in companies whose address equals ‘pokhara’.

       ii) Find the name of all the companies who have more than 4 supervisors.

       iii) Find the name of supervisor who has the largest number of employees.

    b) What is a view in SQL and how it is defined? Explain how views are typically implemented. (5)

4. a) Define a first, second, and third normal forms with suitable examples (5)

    b) What is a functional dependency? When are two sets of functional dependencies equivalent? How can we determine their equivalence(1+2+2)

5. a) Discuss the ACID properties of a database transaction with suitable example.(5)

    b) Describe the serial and serializable schedule? Why serializable schedule is consider correct?(5)

6.a) How does the granularity of data items affects the performance of concurrency control? What factors affect selections of granularity size for data items?(5)

    b) Describe the two-phase commit protocol for database transaction. (5)