The primary purpose of normalization in a relational database is to reduce data redundancy and improve data integrity by organizing data within the database. 1NF eliminates repeating groups by ensuring that each column contains atomic values and each record is unique . 2NF removes partial dependencies by ensuring that all columns are fully functionally dependent on the primary key . 3NF removes transitive dependencies, which means that non-key attributes are not dependent on other non-key attributes . This structuring reduces redundancy and ensures data consistency.

A Composite Index, which is a multi-column index, can improve query performance by allowing lookups, joins, and filters that use several columns in a where clause to be processed more efficiently . Queries that use all of these columns can retrieve results quickly because the index provides quick access paths. However, one limitation is that a Composite Index is most efficient when used with all indexed columns or the leading subset of columns, as it follows the leftmost prefix principle. It's less efficient for queries that do not utilize the leading column. An example usage could be indexing on columns ‘last_name’ and ‘first_name’ for personnel lookups where both names are queried .

A Bitmap Index is more efficient than a B-Tree Index in scenarios where the indexed column has low distinct values, such as gender or boolean fields . This is because Bitmap Indexes store bitmaps for each distinct key and are particularly useful for columns with low cardinality. They improve performance significantly in data warehousing applications where complex queries access a large number of records but output a small number of records . In contrast, B-Tree Indexes are generally better for high-cardinality columns .

INNER JOIN returns only the rows that have matching values in both tables involved in the join. For example, if you need to find all employees who belong to a department, you would use an INNER JOIN between employees and departments tables . LEFT JOIN returns all rows from the left table (e.g., employees), and the matched rows from the right table (e.g., departments). If there is no match, NULLs are returned for columns from the right table. This is useful if you want to list all employees and see the departments they belong to, including those who may not yet be assigned to one .

DELETE removes selected rows from a table and the changes can be rolled back if they are encapsulated in a transaction . TRUNCATE deletes all rows in a table, but it cannot be rolled back, as it does not log individual row deletions . DROP deletes the entire table structure along with its data, and once executed, the table and its contents are permanently lost and not recoverable through rollback .

A subquery is a query within another SQL query and is independent of the outer query; it executes once and returns a value to be used by the outer query . It is suitable for operations where you need a separate dataset to be used in a main query, like getting maximum salary per department. A correlated subquery, on the other hand, depends on the outer query and runs once for each row processed by the outer query, which means it can reference columns of the outer query . This type of subquery can be used in filtering based on the dynamic criteria derived from the outer query's current row.

Aggregate functions in SQL are used to perform calculations on multiple rows of a dataset, returning a single result, which is useful for generating summaries and reports from extensive data sets. Examples include SUM() for calculating the total of a numerical column, AVG() for determining the average value, COUNT() for counting rows, and MAX() and MIN() for finding the highest and lowest values, respectively . For instance, using COUNT(), a SQL statement might be used to count the number of employees in each department: SELECT department, COUNT(*) FROM employees GROUP BY department . These aggregate operations enable analysts to quickly glean insights and patterns within large datasets.

Constraints such as PRIMARY KEY, FOREIGN KEY, and CHECK enhance data integrity by enforcing rules at the database level. A PRIMARY KEY ensures each record in a table is unique and not null, which helps in uniquely identifying rows . A FOREIGN KEY enforces referential integrity by ensuring that a value in a column corresponds to a value in another table's primary key, thus maintaining consistent relationships between tables . CHECK constraints enforce specific conditions on data, ensuring that only valid or expected data is entered into a column, as per defined business rules, such as age >= 18 .

The advantage of using DENSE_RANK over RANK or ROW_NUMBER is its handling of duplicate values. DENSE_RANK assigns the same rank to identical values without any gap, unlike RANK which skips numbers after a tie. ROW_NUMBER, on the other hand, would assign unique numbers, regardless of ties . This behavior makes DENSE_RANK particularly suitable when determining the Nth highest salary because it ensures the ranking accounts for ties without leaving a gap, thus accurately reflecting the actual rank of each salary .

Views offer several benefits, including security (by abstracting sensitive data), simplified query processing (complex queries can be encapsulated in views), and logical data independence (allowing database schema changes without affecting user queries). However, their potential drawbacks include performance overhead, as views can sometimes result in complex query compilation, and any updates on views relying on multiple tables can potentially be complex or even impossible, depending on the view's definition .