Question

1. Explain the following primary file organizations: (1) a heap file (2) a sorted file.

2. Why is choosing a file organization important for a database?

3. If the primary index file contains b_i blocks, how many of block accesses to get the block containing a particular record?

4. Which indexes are based on sequentially ordered files (a) primary index (b) clustering index (c) secondary index (d) B-tree index (e) B+-tree (have more than one correct answer)?

5. Explain the circumstances you should not create indexes?

Answer 1

Q1)

1. Heap File organization: It is the simplest and basic file organization method. In the heap file organization, the records get inserted at the end of the file. It won't sort or order the record during insertion. If the data block is full, new records are added to some other block, which need not to be the very next data block, but it can select any data block in the memory to store new records. This kind of fille organization is also known as an un-ordered file organization (refer figure. heap)

Pros and cons:

1) Fetching and retrieval of records from smaller database is faster and efficient when compared to sorted/ ordered file organization.

2)It is a very good method of file organization for bulk insertion.

3) This file organization is not suitable for large databases

2) Sorted file organization :In this method, the files are sorted sequentially. This can be categorized into two.

   pile file method: Where the record are sorted one after another as they get inserted. In case of any modification or deletion, the record will be searched in the memory blocks and then processed.

sorted file method.: The records are sorted (either ascending or descending) each time they are inserted into the system. The sorting of the record is done in terms of primary key or any other columns.

Q2)   Database consists of tables, index, procedures, functions etc. Databases stores huge volume of data, since it will be in physical storage devices. In the physical memory, the data items are not stored as such, it is converted into binary format. Any user who wants to view these data or modify these data, simply fires SQL query and gets the result on the screen. For the past processing of query, it is important to choose simple data organization methods.

Q4)  indexes are based on sequentially ordered files are

1. primary index
2. Clustering Index

Q5. Explain the circumstances you should not create indexes?

1. If the tables are very small no need yo use indexing
2. It is bettter not to use indexes on columns that return a high percentage of data rows. For example, you would not have an entry for the word "the" or "and" in the index of a book.
3. Should not use indexes if the column is having large number of NULL values