| 1. |
(a) |
List all function dependencies and
multivalued dependencies satisfued by the realtion of the figure
below: |
|
|
| A |
B |
C |
D |
| a1 |
b1 |
c1 |
d1 |
| a1 |
b2 |
c1 |
d2 |
| a2 |
b2 |
c2 |
d2 |
| a2 |
b3 |
c2 |
d3 |
| a3 |
b3 |
c2 |
d4 | |
|
(b) |
When designing an ER diagram for a
particular enterprise, there exist several alternative
designs.
(i) what criteria should you consider in deciding
on the appropriate choice ?
(ii) Come up with several
alternative E-R diagrams to represent an enterprise. List the
merits of each alternative and argue in favour of one of the
alternatives. |
|
(c) |
Given the EMPLOYEE and SALARY
relations. |
|
|
Find the Natural Join (EMPLOYEE
SALARY)
| EMPLOYEE |
SALARY |
| ENO |
ENAME |
| 600 |
ALEXANDER |
| 607 |
BRITES |
| 610 |
CHARLES |
| 612 |
DAVID | |
| ENO |
ESALARY |
| 600 |
1400 |
| 607 |
1600 |
| 610 |
1300 |
| 612 |
1800 | | |
| 2. |
(a) |
Explain how each view is defined by a
schema. Discuss the different views and levels of architecture
for a DBMS. |
|
(b) |
Design a Relational Database
corresponding to the E-R diagram given below : |
|
|
|
| 3. |
(a) |
Compare and contrast Sequential and
Indexed sequential file organisation. |
|
(b) |
Why is a B+ tree a better structure than
B-tree for implementation of an idexed |
|
|
sequential file ?
Discuss. |
| 4. |
|
Consider the following tables A, B and
AB in which S# is supplier code, P# is product |
|
|
code and QTY is quantity and other carry
their respective meaning.
A
| S# |
SNAME |
SCITY |
TURNOVER |
| 110 |
XXX |
BHOPAL |
50 |
| 111 |
YYY |
MUMBAI |
100 |
| 112 |
ZZZ |
DEHRADUN |
70 |
| 113 |
SSS |
HYDERABAD |
20 | |
|
|
B
| P# |
WEIGHT |
COLOUR |
COST |
SELLING PRICE |
| 1 |
25 |
VIOLET |
10 |
30 |
| 2 |
30 |
BROWN |
15 |
55 |
| 3 |
35 |
VIOLET |
17 |
55 |
SP
| S# |
P# |
QTY |
| 110 |
1 |
100 |
| 111 |
1 |
5 |
| 110 |
2 |
50 |
| 111 |
2 |
30 |
| 110 |
3 |
10 |
| 112 |
3 |
100 |
| 113 |
1 |
20 |
Write appropriate SQL statements for the
following :
(i) Get the part number of all the
parts being supplied with no duplicates
(ii) Get the supplier
number and part number in ascending order of supplier number and
descending order for the part supplied for each
supplier.
(iii) Get the details of suppliers who operate
from MUMBAI with turnover > 50.
(iv) Get the part
numbers weighing between 25 and 35.
(v) Get the name and
cities of suppliers whose names being with XXX |
| 5 |
(a) |
Describe HSAM and HISAM as the internal
access methods for a hierarchical |
|
|
database illustrating their features and
implications thereof in database performance. |
|
(b) |
discuss the 3 most important Codd's
commandments through examples. |
| 6 |
(a) |
Compare and contrast knowledge database
and DBMS. Give any two examples of knowledge database
applications and explain. |
|
(b) |
Compare and contrast production rule
system and frame based representation with appropriate
examples. |
