THIRD SEMESTER DIPLOMA EXAMINATION IN ENGINEERING/TECHNOLOGY-OCTOBER, 2007
DATABASE MANAGEMENT SYSTEMS
PART-A
(Maximum marks:15)
v Answer the following questions in one or two sentences:
1.Define data independence.
2.What is DML?
3.Define domain.
4.What is entity integrity ?
5.List the data types in SQL.
6.What is the use of SAVEPOINT statement and give its syntax?
7.What is embedded SQL?
8.What is a stored procedure?
9.What is RAID level 0?
10.What is a distributed database?
PART-B
(Maximum marks:60)
(Answer one full question from each unit)
Unit-1
v (a)Explain the centralized DBMS architecture.(6)
(b)Explain the use and parts of UMI.(6)
OR
v (a)Explain the three tier client architecture with a figure.(6)
(b)Explain the generalization feature of EER model with a suitable example.(6)
Unit-2
v (a)Explain the use and how to write embedded SQL with an example(8)
(b)Explain about multivalued dependencies.(4)
OR
v (a)Explain about ODBC.(6)
(b)Explain the lossless-join decomposition with an example.(6)
UNIT-3
v (a)Explain SELECT statements with WHERE and ORDER BY clause with examples.(6)
(b)Explain the use,creation of simple and composite indexes,creation of unique index and dropping of indexes with syntax.(6)
OR
v There is a sales order detail table maintained in an inventory management storing details such as order No.,product code,quantity ordered,quantity sold and order date.Another table product master contains details such as product code,description and balance quantity.Write SQL statement to perform the following operations:
(i)Retrieve all the products that are not being sold from the product master file.
(ii)Retrieve the contents of transaction file and find the total quantity sold for each item.
(iii)Retrieve the contents of transaction file in the ascending order of order number and order date.The output should contain det order No.,product code,description,order date in DD/MM/YY format.
(iv)Retrieve the contents of transaction file having difference in quantity ordered and quantity sold.(12)
UNIT-4
v Write a PL/SQL block of code to accept item code,transaction type and then update the ITEM MASTER table based on transaction type which may be’p’for purchase or’I’for issue.If an item is at reorder level then that item should not be issued and an appropriate message should be generate.The ITEM MASTER table have the following fields:Item code,description,balance stock,reorder level and unit price.The contents of ITEM MASTER before and after updation should also be displayed.(12)
OR
v Write a PL/SQL block of code to generate rank list of students studying in a class.The student details are stored in a STUDENT table having fields-Register No.,name,marks obtained in 6 subjects.A minimum of 40 is required for a pass in each subject.The maximum mark for each subject is 100.the rank list should be formatted and should contain rank,register no.,name,marks in individual subjects,total mark and grade.the grade is assigned as follows:
D-Distinction if%>=80
F-First class if%>=60 and<80
S-Second class if%>=50 and<60
T-Third class if%<50 and>=40
The rank list should not contain failed candidates.(12)
UNIT-5
v (a)Explain the basic parts of a trigger.(6)
(b)Explain the components of a procedure of function.(6)
OR
v (a)Explain any 3 types of triggers.(6)
(b)Write any 4 advantages of using procedures and functions.(6)