| 1. |
(a) |
Consider the following 3
tables S, P and SP in which S# is supplier code, QTY is quantity and
others carry their respective meanings. |
| |
|
S Table
| S# |
SNAME |
SCITY |
TURNOVER |
| 20 |
KRISHNA |
VIJAYAWADA |
100 |
| 21 |
GODAVARI |
RAJAMUNDRY |
50 |
| 22 |
YAMUNA |
DELHI |
80 |
| 23 |
PENNA |
NELLORE |
20 | |
| |
|
P Table
| P# |
WEIGHT |
COLOUR |
COST |
SELLING
PRICE |
| 1 |
25 |
RED |
10 |
30 |
| 2 |
30 |
BLUE |
15 |
45 |
| 3 |
35 |
RED |
20 |
45 | |
| |
|
SP Table
| S# |
P# |
QTY |
| 20 |
1 |
100 |
| 21 |
1 |
5 |
| 20 |
2 |
50 |
| 21 |
2 |
30 |
| 20 |
3 |
10 |
| 22 |
3 |
100 |
| 23 |
1 |
20 | |
| |
|
Write appropriate SQL
statements for the following :
(i) Get all the details of
suppliers who operate from DELHI with TURNOVER = 80
(ii)
Get part numbers weighing between 25 and 35.
(iii) Get the name
of suppliers whose name begins with V.
(iv) For each part
supplied, get part number and names of all cities supplying the
part.
(v) Get the names of suppliers who supply part
2. |
| |
(b) |
Define 2NF and 3NF.
Produce 3NF table structures from the table below. |
| |
|
CINEMAS
| F# |
FNAME |
CCODE |
CNAME |
TOWN |
POPULATION |
MAN
NO |
MAN
NAME |
TAKINGS |
| 25 |
Star Wars |
BX |
Rex |
Cardiff |
300000 |
01 |
Jones |
900 |
| 25 |
Star Wars |
KT |
Rialto |
Swansea |
200000 |
03 |
Thomas |
350 |
| 50 |
Jaws |
BX |
Rex |
Cardiff |
300000 |
01 |
Jones |
1200 |
| 50 |
Jaws |
DJ |
Odeon |
Newport |
250000 |
01 |
Davies |
400 |
| 50 |
Jaws |
TL |
Rex |
Bridgend |
150000 |
02 |
Smith |
300 |
| 30 |
Star Trek |
BX |
Rex |
Cardiff |
300000 |
01 |
Jones |
850 |
| 30 |
Star Trek |
TL |
Rex |
Bridgend |
150000 |
02 |
Davies |
500 |
| 40 |
ET |
KT |
Rialto |
Swansea |
200000 |
03 |
Davies |
1200 | |
| 2. |
|
Explain the following
terms:
(i) Inverted list
(ii) Referential
integrity
(iii) Foreign key
(iv) Candidate key
(v)
B-Tree |
| 3. |
(a) |
What is normalization
? What are the properties of normalization ?
Explain |
|
(b) |
Discuss the three level
architecture of DBMS. |
| 4. |
(a) |
Compare the hierarchical
model with the relational model in terms of ease of use.
Explain with one example. |
|
(b) |
Explain the types of
anomalies found in RDBMS. Justify with examples. |
| 5. |
(a) |
Discuss the difference
between the following file organisation techniques through a proper
example:
(i) Sequential file organisation
(ii)
Indexed sequential file organisation
(iii) Direct file
organisation
(iv) Multikey file organisation |
|
(b) |
Given the E-R diagram below,
give the most suitable relational database scheme to implement this
database. For each relation, choose a suitable name and list
corresponding attributes, underlying the primary key. For each
relation also identify the foreign keys. |
|
|
 |
| 6. |
(a) |
Explain the trade-offs in
distributing the database. |
|
(b) |
Describe HSAM, HISAM as the
internal access methods for a hierarchical database illustrating
their features and implications thereof in database
performance. |
