Schema:
Course (c-name, room, instructor)
Enrollment (student-name, c-name, grade)
Ans
(a) Database file store in the disk in one of the following forms
1.Sequential File Organization or Ordered Index File ( Index SequentialAccess method )
2.B+tree file,
3. Hash file,
4. Flat File structure
The most common methods are the B+ tree file structure and Index SequentialAccess method (ISAM). Hash file structure is most efficient among them. Hash file generally takes O(1) to find an item. I suggest you use Sequential File Organization or Ordered Index File for the given schema. Because it will search efficiently between two given relations. Course name and student names attributes belongs to different tables(relation). To search run this type of query you have to link between two tables using the primary key and foreign key. Sequential File Organization or Ordered Index File structure use two types of indexing
1. Dense Index: One index for one file.
2.Sparse Index: One index for more than one file.
(b) There are three main three types of indexing
1.Cluster Indexing
2. Secondary Indexing (Non -Cluster )
3.Multilevel.
Most efficient (faster) is cluster indexing. It will decrease retrieval cost. because of mone than one record related to the same key. One thing you have to remember that it must be an ordered datafile. Here Instructors and students are in a different relation so before making a query it is necessary to join both relations
Secondary indexing is not faster as cluster indexing. Multilevel indexing used on a large amount of data.