Total No. of Questions : 4] SEAT No.
8
23
PC-25 [Total No. of Pages : 2
ic-
[6360]-25
tat
0s
T.E. (Computer Engg) (Artificial Intelligence and Data
2:3
Science)
02 91
0:4
DATABASE MANAGEMENT SYSTEM
0
41
1/0 13
(2019 Pattern) (310241) (Semester - I) (Insem)
0
8/2
.23 GP
Time : 1 Hour] [Max. Marks : 30
Instructions to the candidates:
E
83
8
1) Answer Q1 or Q2, Q3 or Q4.
C
23
2) Neat diagrams must be drawn wherever necessary.
ic-
3) Figures to the right side indicate full marks.
16
tat
4) Assume Suitable data, if necessary.
8.2
0s
.24
2:3
Q1) a) Compare DBMS and File processing system in terms of data isolation,
91
49
0:4
data redundancy, data inconsistency, data integrity. [4]
30
41
b) A reputed general hospital has decided to computerize their operations.
01
02
In the hospital many doctors are working. Personal information of doctors
8/2
is maintained to get them fixed salary per month. The patients are admitted
GP
to the hospital into the room. They are treated by various doctors.
1/0
Sometimes patients perform certain pathological tests which carried out
CE
83
8
23
into the labs. Identify the relationship among the entities along with the
.23
ic-
mapping cardinalities, keys in the E.R. diagram. Construct appropriate
16
tat
tables for E-R diagram designed with above requirements. [7]
8.2
0s
c) A weak entity set can always be made into a strong entity set; by adding
.24
2:3
to its attributes the primary key attributes of its identifying entity set.
91
49
0:4
Outline what sort of redundancy will result if we do so while converting
30
to tables. [4]
41
01
OR
02
8/2
Q2) a) What is Specialization and generalization in Extended E-R diagram? What
GP
1/0
is the difference between them? Why do we not display the difference in
CE
schema diagram? [7]
83
b) Draw architecture of DBMS system and explain function of following
.23
components: [8]
16
8.2
i) Storage manager
.24
ii) Query Processor
P.T.O.
49
Q3) a) Consider following schema [4]
8
23
Student_fee_details (rollno, name, fee_deposited, date)
ic-
tat
Write a trigger to preserve old values of student fee details before updating
0s
in the table.
2:3
b) What is view and how to create it? Can you update view? If yes, how? If
02 91
0:4
not, why not? [6]
0
41
c) 1/0 13
Consider following schema [5]
0
Hotels(hotel_no,hotel_name.city)
8/2
.23 GP
Rooms(Room_no,hotel_no,price,type)
E
83
8
Write a PL/SQL procedure to list the price & type of all rooms at the
C
23
hotel ‘TAJ’
ic-
16
tat
OR
8.2
0s
Q4) a) Consider the following relation schema [6]
.24
2:3
91
49
MOVIES(Mov_Id, Mov_Title,Mov_Year, Dir_Id)
0:4
30
41
DIRECTOR(Dir_Id, Dir_Name)
01
02
RATING(MOV_Id, Rev_Stars)
8/2
GP
Write the SQL queries for the following.
1/0
CE
83
i) List the title of all the movies directed by ‘RAJ KAPOOR’
8
23
.23
ii) Find the name of movies and number of stars for each movie. Sort
ic-
16
the results on movies title and from higher stars to least stars.
tat
8.2
0s
iii) Assign the rating of all movies directed by ‘Steven Spielberg’ to 9.
.24
2:3
91
b) What is the importance of creating constraints on the table? Explain with
49
0:4
example any 4 constraints that can be specified when a database table is
30
41
created. [5]
01
02
c) What is synonym? How to create and use synonym in SQL? [4]
8/2
GP
1/0
CE
83
.23
16
8.2
.24
49
[6360]-25 2
