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]
***************
No comments:
Post a Comment