Code: A-20

Code: DC-10 Subject: DATABASE MANAGEMENT SYSTEMS

Time: 3 Hours Max. Marks: 100

NOTE: There are 9 Questions in all.

· Question 1 is compulsory and carries 20 marks. Answer to Q. 1. must be written in the space provided for it in the answer book supplied and nowhere else.

· Out of the remaining EIGHT Questions answer any FIVE Questions. Each question carries 16 marks.

· Any required data not explicitly given, may be suitably assumed and stated.

Q.1 Choose the correct or best alternative in the following: (2x10)

a. The _____________ of a relationship indicates the number of associated entities.

(A) Degree (B) Cardinality

(C) Connectivity (D) Dependency

b. If two relations have 5 and 10 tuples respectively, then what will be the number of tuples in the Cartesian product?

(A) 5 (B) 10

(C) 15 (D) 50

c. Who developed QBE?

(A) C.J. Date (B) E.F. Codd

(C) M.M. Zloof (D) None of the above

d. Which of the following is an attribute that holds a single value for a single entity?

(A) Simple (B) Composite

(C) Derived (D) Single-valued

e. The ‘B’ in B-tree stands for

(A) Binary (B) Blocked

(C) Balanced (D) Breadth-first

f. What is the expansion of ISAM?

(A) Internal Storage Access Mechanism

(B) Index Sequential Access Method

(C) Integrated Storage And Management

(D) None of the above

g. What will be the result of the following SQL statement SELECT * FROM BOOK WHERE YEAR IN (1998, 2000)?

(A) All books published in the years 1998 and 2000

(B) All books published between the years 1998 and 2000

(C) All books that are not published in the years 1998 and 2000

(D) None of the above.

h. In the case of ___________ nulls are handled like normal values in the functions of SQL

(A) SUM (B) AVG

(C) MAX (D) COUNT(*)

i. Which rule guarantees that every primary attribute key is non-null?

(A) Operational constraints (B) Domain constraint

(C) Entity integrity constraint (D) Referential integrity constraint

j. Which of the following algebraic operation is not from the set theory?

(A) UNION (B) INTERSECTION

(C) CARTESIAN PRODUCT (D) SELECT

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

Q.2 a. Explain any four DBMS interfaces that make DBMS user friendly. (8)

b. Explain the three schema architecture and show how it achieves logical and physical data independence. (8)

Q.3 a. Define the following:-

(i) Weak entity. (ii) UoD.

(iii) DBA. (iv) DDL. (8)

b. Discuss any four database utilities and their functions. (8)

Q.4 a. Define a relationship type in an ER model and provide examples of binary and ternary relationships. (6)

b. Give an example of a relationship type with attributes. (2)

c. Create an ER diagram, complete with attributes, keys and constraints, for the following description of albums:

Each musician has a name, address and a phone number. Each instrument that is used in songs has a name (e.g. guitar, flute). Each album has a title, a copyright date, a format (e.g. CD or MC) and an album identifier. Each song 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 several songs on it but no song can appear on more than one album. Each song is performed by one or more musicians and a musician may perform a number of songs. (8)

Q.5 a. Define the following terms with respect to the relational model

(i) degree of a relation (ii) n-tuple

(iii) candidate key (iv) valid state (8)

b. Consider the following relations where Enrolled gives the students (sid) and the courses they are registered in (cid). sid and (cid,sid) are the primary keys of Student and Enrolled respectively. sid in Enrolled references sid of Student.

Student

name	Sid	age	gpa
Hari	50000	19	9.0
John	50366	20	6.2
Jane	53650	20	8.5
Guldu	54000	19	9.2

Enrolled

cid	name	sid
DC10	DBMS	50366
DC11	C++	53650
DC10	DBMS	54000

What are the constraints that are violated, if any, when the following operations are performed?

(i) Update the sid of Guldu to 50000.

(ii) Insert < Joe, null, 19, 9.0> into Student.

(iii) Insert a tuple <DC11, C++, 52500> in Enrolled.

(iv) Delete the tuple of John in Student. (8)

Q.6 a. Define the following terms with respect to SQL

(i) Create Assertion statement

(ii) EXCEPT operator

(iii) Nested query

(iv) Foreign key

(v) DISTINCT (10)

b. Consider the following relations with keys underlined

Sailors (sid, sname, rating, age)

Boats (bid, bname, colour)

Reserves (sid, bid, day)

Define the above relations as tables in SQL making real world assumptions about the type of the fields. Define the primary keys. (6)

Q.7 a. For the relations of Q6 (b) answer the following queries in relational algebra.

(i) Find the names of sailors who have reserved boat 103.

(ii) Find the sids of sailors who have not reserved any boat. (7)

b. For the relations of Q6 (b) answer the following queries in SQL

(i) Find the sailors who have reserved a red boat.

(ii) Find the sailors with the highest rating.

(iii) Find the average age of sailors with a rating of 10. (9)

Q.8 Explain the following terms

(i) heap file. (ii) seek time and rotational delay.

(iii) interblock gap. (iv) hardware address of a block.

(v) mirroring in RAID technology (vi) repeating field and repeating group

(vii) blocking factor (viii) static hashing. (16)

Q.9 a. Describe the four types of segments used in Oracle. (7)

b. Consider the relations given in question Q6(b). Answer the following queries in QBE

(i) Find the names of sailors who are older than 20 but younger than 30 years.

(ii) Delete all the boats which are coloured red.

(iii) Find the colours of boats reserved on ‘24/08/2005’. (9)