Database Fundamentals

The terms bitmap, b-tree, and hash are commonly used types of database indices. These indices are data structures that enhance the efficiency of data retrieval operations within a database table, albeit at the cost of additional storage space and the need for extra processing to maintain the indices.

One reason to add an index to a database is to improve the performance of select statements. Indexes speed up the retrieval of rows by using pointers, which significantly reduces the amount of data the database needs to scan, thereby increasing query performance. Decreasing storage space, increasing database security, and improving the performance of insert statements are not directly achievable by adding an index.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. In the given scenario, the original table with ProductID, ProductName, Color1, Color2, and Color3 is divided into two tables: one with ProductID and ProductName, and another with ProductID and Color. This restructuring minimizes redundancy by associating each product with its colors in a separate table, ensuring better organization and consistency. Therefore, the process described is referred to as normalization.

In a relational database, when we say that one attribute is functionally dependent on another, it means that for each value of the first attribute, there is exactly one corresponding value of the second attribute. In the given table, each ProductID has exactly one ProductCategory associated with it. Thus, the term that best describes this relationship is 'Functionally dependent'.

A primary key is a field in a table that uniquely identifies each row or record in that table. It must contain unique values and cannot have NULL values. Only one primary key is allowed per table, and it can consist of one or multiple fields.