Code: DC-10 Subject: DATABASE MANAGEMENT SYSTEMS
Time: 3 Hours Max. Marks: 100
NOTE: There are 11 Questions in all.
· Question 1 is compulsory and carries 16 marks. Answer to Q. 1. must be written in the space provided for it in the answer book supplied and nowhere else.
· Answer any THREE Questions each from Part I and Part II. Each of these questions carries 14 marks.
· Any required data not explicitly given, may be suitably assumed and stated.
Q.1 Choose the correct or best alternative in the following: (2x8)
a. Which one of the following statements is false?
(A) The data dictionary is normally maintained by the database
administrator.
(B) Data elements in the database can be modified by changing the data
dictionary.
(C) The data dictionary contains the name and description of each data element.
(D) The data dictionary is a tool used exclusively by the database administrator.
b. An advantage of the database management approach is
(A) data is dependent on programs.
(B) data redundancy increases.
(C) data is integrated and can be accessed by multiple programs.
(D) none of the above.
c. A DBMS query language is designed to
(A) support end users who use English-like commands.
(B) support in the development of complex applications software.
(C) specify the structure of a database.
(D) all of the above.
d. Transaction processing is associated with everything below except
(A) producing detail, summary, or exception reports.
(B) recording a business activity.
(C) confirming an action or triggering a response.
(D) maintaining data.
e. It is possible to define a schema completely using
(A) VDL and DDL. (B) DDL and DML.
(C) SDL and DDL. (D) VDL and DML.
f. The method of access which uses key transformation is known as
(A) direct. (B) hash.
(C) random. (D) sequential.
g. Data independence means
(A) data is defined separately and not included in programs.
(B) programs are not dependent on the physical attributes of data.
(C) programs are not dependent on the logical attributes of data.
(D) both (B) and (C).
h. The statement in SQL which allows to change the definition of a table is
(A) Alter. (B) Update.
(C) Create. (D) select.
PART I
Answer any THREE Questions. Each question carries 14 marks.
Q.2 a. Describe the responsibilities of the DBA and the database designer. (7)
b. What are the four main characteristics of the database approach? (7)
Q.3 a. Differentiate between DDL and DML. (4)
b. List any two disadvantages of a database system. (3)
c. Explain the utilities that help the DBA to manage the database. (7)
Q.4 Differentiate between
(i) Logical and physical data independence.
(ii) WHERE and HAVING clause in SQL.
(iii) Strong entity set and weak entity set.
(iv) Spanned and unspanned organisation. (3.5 x 4)
Q.5 a. Discuss with examples about various types of attributes present in the ER model. (6)
b. Information about films contains information about movies, stars and studios. Movies have a title, year of production, length and the film type. Stars have a name and address. Studios have a owner and a banner. Movies are shot in studios which own them. A movie is shot in only one studio. Stars are connected to one or more studios but can act in any film which may or may not be owned by the studio.
Arrive at an E-R diagram. Clearly indicate attributes, keys, the cardinality ratios and participation constraints. (8)
Q.6 a. What is the main goal of RAID technology? Describe the levels 1 through 5. (6)
b. An employee record has the following structure
struct employee {
int eno;
char name[22];
float salary;
char dept[10];
};
(i) Calculate the record size R in bytes.
(ii) If the file has 500 records, calculate the blocking factor bfr and the number of blocks b, assuming an unspanned organization with block size B = 512 bytes.
(iii) What is the unused space in each block and in the last block? (8)
PART II
Answer any THREE Questions. Each question carries 14 marks.
Q.7 Define the following terms
(i) Hashing (ii) Specialization
(iii) Value set. (iv) DBMS.
(v) Host language. (vi) Database state.
(vii) Trigger. (2 x 7)
Q.8 a. Differentiate between natural join and outer join. (2)
b. For the relations R and S given below:
R S
|
A |
B |
C |
|
B |
C |
D |
|
1 |
2 |
3 |
|
2 |
3 |
10 |
|
4 |
5 |
6 |
|
2 |
3 |
11 |
|
7 |
8 |
9 |
|
6 |
7 |
12 |
Compute
(i) 
(ii)
(iii)
natural join (iv) outer join (12)
Q.9 a. Define union compatibility? Explain why INTERSECTION of two relations can not be performed if they are not union compatible? (4)
b. What is a view in SQL? When can views be updated? (4)
c. Using SQL create a view RS for the relations R and S of Q8. The view consists of the columns A and D renamed as X and Y respectively. Insert a tuple <10, 15> into it. Show the contents of the view. (6)
Q.10 a. Consider the relations defined below:
PHYSICIAN (regno, name, telno, city)
PATIENT (pname, street, city)
VISIT (pname, regno, date_of_visit, fee)
Where the regno and pname identify the physician and the patient uniquely respectively. Express queries (i) to (iii) in SQL.
(i) Get the name and regno of physicians who are in Delhi.
(ii) Find the name and city of patient(s) who visited a physician on 31 August 2004.
(iii) Get the name of the physician and the total number of patients who have visited her.
(iv) What does the following SQL query answer
SELECT DISTINCT name
FROM PHYSICIAN P
WHERE NOT EXISTS
( SELECT *
FROM VISIT
WHERE regno = p.regno ) (3.5 x 4) (3 x 3)
Q.11 Write short notes on
(i) Data models.
(ii) Oracle database structure.
(iii) Group By clause in SQL.
(iv) Retrieval in QBE. (3.5 x 4)