Database Management Systems University Question 8

Database Management Systems (DBMS) University Question 8

Database Management Systems

Time : Three Hours                                                                          Max.Marks:100

Answer ALL Questions

(10 X 10 = 100 Marks)

1. a) What are the responsibilities of the DBA and the database designers? [3]

b) What are the different types of database end users? Discuss the main activities of each [4]

c) Discuss the capabilities that should be provided by a DBMS. [3]

2. a) Describe the three-schema architecture. Why do we need mappings between schema levels?  How do different schema definition languages support this architecture? [4]

b) Write a short note on component modules of an DBMS with neat diagram. [6]

3. a) Define the following terms: composite attribute, multivalued attribute, derived attribute, complex attribute, key attribute, value set (domain). [6]

b) Discuss the entity integrity and referential integrity constraints. Why is each considered important? [4]

4. a) List the operations of relational algebra and the purpose of each. [4]

b) Consider the following schema: [6]

Suppliers(sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

Write the following queries in relational algebra

1. Find the pnames of parts for which there is some supplier.

2. Find the snames of suppliers who supply every part.

3. Find the snames of suppliers who supply every red part

5. Notown Records has decided to store information about musicians who perform on its albums (as well as other company data) in a database. The company has wisely chosen to hire you as a database designer (at your usual consulting fee of $2500/day). Each musician that records at Notown has an SSN, a name, an address, and a phone number. Poorly paid musicians often share the same address, and no address has more than one phone. Each instrument used in songs recorded at Notown has a unique identification number, a name (e.g., guitar, synthesizer, flute) and a musical key (e.g., C, B-flat, E-flat).Each album recorded on the Notown label has a unique identification number, a title, a copyright date, a format (e.g., CD or MC), and an album identifier. Each song recorded at Notown has a title and an author. Each musician may play several instruments, and a given instrument may be played by several musicians. Each album has a number of songs on it, but no song may appear on more than one album. Each song is performed by one or more musicians, and a musician may perform a number of songs. Each album has exactly one musician who acts as its producer. A musician may produce several albums, of course. Design a conceptual schema for Notown and draw an ER diagram for your schema. Be sure to indicate all key and cardinality constraints and any assumptions you make. Identify any constraints you are unable to capture in the ER diagram and briefly explain why you could not express them.

6. a) What is Normalization? Explain Normalization techniques using functional dependencies with relevant examples. [7]

b) Consider the relation schema R(A,B,C,D,E, F,G,H) with functional dependencies

F = {BE → GH, G → FA, D → C, F → B}.

(a) Find a (minimal) key for R. [3]

7. Write a short note on the following

1. Storage and file structure

2. Indexed files Vs Hashed files;

3. B-trees;

8. a) Differentiate between the following: with example In SQL queries [5]

(i) Theta Join. (ii) Equi Join. (iii) Natural Join (iv) Outer Join.

b) Discuss the entity integrity and referential integrity constraints. Explain constraint Violations during update operations? [5]

9. Explain the following protocol to ensures conflict serializability

a) locking protocols,

b) timestamp ordering protocol

10. a) State the types of failures that may occur in a system. [3]

b) Explain log based recovery with respect to Deferred Database Modification and Immediate Database modifications. [7]

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