Skip to content

Week 14: SQLite3 V

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.

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.


  1. Build the Database:
    • Create your own new database and identify potential tables in a CommunityLibraryDB.
    • Create at least 5 tables and populate them with data. 
    • Be sure that each table has the appropriate primary keys and foreign key relationships.
  2. 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.
  3. 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.
  4. Performance and Utility Analysis:
    • Analyze the performance improvement gained by using indexes.
    • Discuss the utility of views in simplifying data retrieval and presentation.


  • A report detailing the process of creating and implementing the database and indexes, including the rationale behind choosing specific columns for indexing.
  • Three SQL scripts: 1) creating and populating the database; 2) creating indexes; and 3) creating the 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.