SQLite Schema Design and Best Practices Quiz Quiz

Explore key concepts of SQLite schema design, including data types, normalization, primary keys, and indexing strategies. This quiz helps users evaluate their understanding of efficient schema creation and best practices for optimizing SQLite database structures.

1. Choosing the Right Primary Key

   Why is it recommended to use an INTEGER PRIMARY KEY for the main identifier in an SQLite table?

   1. It automatically creates a unique row id and improves indexing performance.
   2. It limits the table to only positive numbers.
   3. It reduces storage size by half compared to TEXT.
   4. It is necessary for foreign key support.

   Explanation: Using an INTEGER PRIMARY KEY in SQLite automatically assigns a unique row id to each record and makes indexing more efficient. Limiting the table to positive numbers or reducing storage size by half compared to TEXT is incorrect; these are not direct effects of this key choice. Having an INTEGER PRIMARY KEY is not required for foreign key support but can simplify relationships. The benefit lies mainly in performance and uniqueness.

2. Understanding Data Types

   In SQLite, which storage class is recommended for storing true or false values?

   1. DATE
   2. TEXT
   3. INTEGER
   4. BOOLEAN

   Explanation: Storing true or false values as INTEGER (typically 0 and 1) is the recommended approach in SQLite. SQLite does not have a built-in BOOLEAN storage class, even though 'BOOLEAN' may be accepted as an alias. TEXT and DATE are not appropriate for representing boolean logic as they imply either string data or specific date formatting.

3. Using NOT NULL Constraints

   What is the main advantage of adding NOT NULL constraints to columns in an SQLite table?

   1. It speeds up SELECT queries automatically.
   2. It makes the database read-only.
   3. It prevents duplicate values in the column.
   4. It ensures that every row has a value for the column and can enforce more reliable data quality.

   Explanation: The NOT NULL constraint ensures that each entry in the column contains a value, supporting data reliability. It does not speed up SELECT queries directly, make the database read-only, or prevent duplicates (which requires a UNIQUE constraint). Its primary purpose is to maintain data completeness and consistency.

4. Default Values in Table Schemas

   When defining a table, which best practice should you follow for default values in columns?

   1. Use NULL as default for all columns.
   2. Set a default value for every column.
   3. Avoid default values to reduce storage.
   4. Use defaults for columns where a missing value could disrupt queries or logic.

   Explanation: Defaults help prevent errors when certain columns are omitted in INSERT statements, especially where missing values could cause issues. Setting defaults for every column or always using NULL is unnecessary and can introduce ambiguity. Avoiding default values just to save storage disregards data integrity and application logic.

5. Benefits of Normalization

   Why is normalizing your database schema important in SQLite design?

   1. It removes the need for indexes.
   2. It automatically makes queries run faster.
   3. It restricts the use of foreign keys.
   4. It removes redundant data, minimizing inconsistencies and saving space.

   Explanation: Normalization focuses on organizing tables to eliminate redundancy, which reduces inconsistencies and storage requirements. It does not inherently make queries faster, eliminate the need for indexing, or restrict foreign key use. Proper normalization supports efficient, reliable databases.

6. Indexing Foreign Keys

   What is a best practice regarding indexes on foreign key columns in SQLite?

   1. Create indexes on foreign key columns to speed up JOIN and lookup operations.
   2. Only index columns that are the primary key.
   3. Avoid indexing foreign key columns as it uses extra space.
   4. Drop indexes from all reference columns.

   Explanation: Indexing foreign key columns improves JOIN and lookup performance, which is important for relational database efficiency. Avoiding indexes can slow down these operations. While primary keys are indexed by default, foreign keys benefit from manual indexing. Removing all reference column indexes is not advised for performance.

7. Using AUTOINCREMENT

   When should you use the AUTOINCREMENT keyword with INTEGER PRIMARY KEY in SQLite?

   1. Only when you must guarantee that row ids are never reused after deletion.
   2. Every time you use INTEGER PRIMARY KEY.
   3. When you want random values for primary keys.
   4. To keep the table sorted automatically.

   Explanation: AUTOINCREMENT ensures that row ids are not reused, useful in certain scenarios requiring strict sequence guarantees. Using it all the time can lead to slower inserts and larger database files. It does not provide random values or automatic table sorting. It should be reserved for specific needs.

8. Naming Conventions for Tables

   Which SQLite table naming convention is typically considered best practice for clarity and consistency?

   1. Use singular, lowercase names such as 'customer'.
   2. Use all uppercase letters for visibility.
   3. Name tables with special characters for uniqueness.
   4. Include spaces in table names.

   Explanation: Singular, lowercase table names increase clarity and consistency, making schema management easier. Using uppercase letters, spaces, or special characters can complicate queries and reduce readability. While variations are possible, simple lowercase names are widely preferred.

9. Composite Primary Keys

   What is a composite primary key in the context of SQLite schema design?

   1. A key that combines unique and NOT NULL constraints automatically.
   2. A primary key created using only numeric columns.
   3. A foreign key that references two tables.
   4. A primary key made up of two or more columns together to ensure uniqueness.

   Explanation: A composite primary key uses two or more columns in combination to make each row unique. It is not a foreign key referencing two tables and does not automatically combine unique and NOT NULL constraints. Composite keys do not require numeric columns exclusively; any appropriate columns can be used.

10. Dropping Columns in SQLite

    If you need to remove a column from an existing SQLite table, what is considered the recommended procedure?

    1. Restart the database server.
    2. Use the DROP COLUMN statement directly.
    3. Delete all rows with that column’s value.
    4. Create a new table without the column, copy data, and rename it.

    Explanation: Because SQLite does not support the DROP COLUMN statement in most versions, the best practice is to create a new table without the column, transfer the data, and rename the table. There is no direct DROP COLUMN support as in other databases. Deleting rows or restarting the server does not affect the schema structure.