DBMS primary index and secondary index questions

DBMS

1. Primary Index

2. Q.Supposethereisaorderedfileof30,000records
storedonadiskwithblocksareofsize1024
bytesandrecordsarefixedlengthandun
spannedoflength100bytes,ofwhichkeyfield
is9bytes,andbothblockandrecordpointers
areofsize6bytes.Supposeifweconstructa
primaryindexonthekeyfieldofthefilethen
findtheaveragenumberofblockstoaccessto
accesstherecord_________.
Practice Question

3. Clustered Index

4. Q.Supposethereisaorderedfileof30,000records
storedonadiskwithblocksareofsize1024bytes
andrecordsarefixedlengthandunspannedoflength
100bytes,ofwhichkeyfieldis9bytes,andboth
blockandrecordpointersareofsize6bytes.Suppose
ifweconstructaclusteredindexonthekeyfieldof
thefilethenfindtheaveragenumberofblocksto
accesstoaccesstherecord_________.
(Assumethatthereare1000clustersinthedatafile)
Practice Question

5. Secondary Index

6. Q.Supposethereisanorderedfileof30,000
recordsstoredonadiskwithblocksareofsize
1024bytesandrecordsarefixedlengthandun
spannedoflength100bytes,ofwhichkeyfield
is9bytes,andbothblockandrecordpointers
areofsize6bytes.Supposeifweconstructa
secondaryindexonthekeyfieldofthefile,then
findtheaveragenumberofblockstoaccessto
accesstherecord_________.
Practice Question

7. Multi level Index

8. Q.Supposethereisanorderedfileof30,000
recordsstoredonadiskwithblocksareofsize
1024bytesandrecordsarefixedlengthandun
spannedoflength100bytes,ofwhichkeyfield
is9bytes,andbothblockandrecordpointers
areofsize6bytes.Supposeifweconstructa
secondaryindexonthekeyfieldofthefile,and
thenconvertedintomultilevelindex,thenfind
thenumberofblockstoaccesstoaccessthe
record_________.
Practice Question

9. Class RoomPractice Question

10. Q.Considerafileof16384records.Eachrecordis32bytes
longanditskeyfieldisofsize6bytes.Thefileis
orderedonanon-keyfield,andthefileorganizationis
unspanned.Thefileisstoredinafilesystemwithblock
size1024bytes,andthesizeofablockpointeris10
bytes.Ifthesecondaryindexisbuiltonthekeyfieldof
thefile,andamulti-levelindexschemeisusedtostore
thesecondaryindex,thenumberoffirstlevelandsecond
levelblocksinthemulti-levelindexarerespectively
[GATE-2008][CRPQ-7]
(a)8and0 (b)128and6
(c)256and4 (d)512and5
Previous GATE Question

12. A data file consisting of 1,50,000 student-records is
stored on a hard disk with block size of 4096 bytes. The
data file is sorted on the primary key RollNo. The size
of a record pointer for this disk is 7 bytes. Each student-
record has a candidate key attribute called ANumof
size 12 bytes.
Suppose an index file with records consisting of two
fields. ANumvalue and the record pointer to the
corresponding student record is built and stored on the
same disk.
Assume that the records of data file and index file are
not split across disk blocks. The number of blocks in
the index file is ________.
[CRPQ-6](GATE-2021-Set2)
Previous GATE Question

14. GATE-2023