Skip to content

Week 14: SQLite3 V

Objective
This assignment is designed to introduce you to the concepts of indexes and views in a database. You will learn how to use indexes to optimize query performance and how to use views for data abstraction and simplifying complex queries.

Background
Indexes and views are powerful tools in database management. Indexes can significantly improve the performance of data retrieval operations, especially in large databases. Views, on the other hand, provide a way to simplify complex queries and present data in a more understandable format, without altering the underlying data.

Tasks
  1. Creating and Using Indexes:
    • Identify columns in the CommunityLibraryDB that are frequently used in search conditions (WHERE clauses) or join conditions.
    • Create appropriate indexes on these columns to optimize query performance.
    • Test and compare the performance of queries before and after applying indexes.
  2. Introduction to Views:
    • Create a view to simplify a complex query. For example, a view that shows all books currently borrowed by each member, including member names and book titles.
    • Create a view that presents data in a specific format, such as a summary report showing the number of books borrowed by each member.
  3. Performance and Utility Analysis:
    • Analyze the performance improvement gained by using indexes.
    • Discuss the utility of views in simplifying data retrieval and presentation.

Deliverables
  • A report detailing the process of creating and implementing indexes, including the rationale behind choosing specific columns for indexing.
  • The SQL scripts used to create the indexes and views.
  • A performance analysis report showing the query execution times before and after indexing.
  • A discussion on the utility of views in database management, including examples of the created views.

Evaluation Criteria
  • Effectiveness of the chosen indexes in improving query performance.
  • Correctness and practical utility of the created views.
  • Depth of analysis in the performance comparison and utility of views.
By completing this assignment, you will gain practical experience in optimizing database queries using indexes and in using views for data abstraction and simplification of queries. These skills are essential for efficient database management and for presenting data in a user-friendly manner.