VIEWs, Indexing & Advanced Searching

Question 1

VIEW

Answer 1

is a virtual table created by a query

It does not store data itself but presents data from these tables

can simplify complex queries

a specific data perspective

enhance data security by restricting access to certain data.

Question 2

Advantages of Using VIEWs:

Answer 2

Data Abstraction: Views can simplify the complexity of data by providing a specific

Security: Views can act as a layer of security to restrict user access to specific rows and columns of data

Query Simplification: Views allow users to save complex queries as virtual tables,

Question 3

Disadvantages of Using VIEWs:

Answer 3

Performance: Views can sometimes lead to performance issues,

Update Restrictions: Some views are not updatable or insertable,

Maintenance: If the underlying table structures change, the views may need to be updated or recreated.

Question 4

INDEX

Answer 4

a database is a data structure that improves the speed of data retrieval operations on a table.

find specific information

a database index allows
the database engine to find and retrieve specific rows much faster than it could by scanning the entire table.

are typically used on columns that are frequently searched

Question 5

Advantages of INDEX-es:

Answer 5

Improved Query Performance: Indexes provide a way to quickly locate and retrieve specific rows from a table,

Faster Joins: When multiple tables are joined in a query, indexes on the join columns help optimize the join operation, r

Efficient Data Retrieval: Indexes allow for efficient data retrieval without the need to scan the entire table, m

Facilitates Unique Constraints: INDEX-es can enforce the uniqueness of values in one or more columns, ensuring that duplicate data is not allowed in those columns.

Question 6

Disadvantages of INDEX-es

Answer 6

Increased Storage Overhead: Indexes require additional storage space to store the index data structure.

Slower Data Modification: While indexes improve data retrieval speed, they can slow down INSERT, UPDATE, and DELETE operations,

Maintenance Overhead: Indexes need to be maintained, which means that as data is modified, indexes must be updated to reflect these changes.

Index Selection: Choosing the right columns to index is crucial. Creating too many indexes or indexing the wrong columns can lead to unnecessary overhead