3.2.10 Create a Database

Introduction

After learning the concepts of databases, the textbook provides a practical guide to creating and working with a database using SQLite. This section walks through the entire process — from installing SQLite to creating tables, adding records, querying data, updating records, deleting records, and normalizing the database.

The textbook uses SQLite because it is lightweight, serverless, and easy to use. The examples use a sample database called `NBF` with tables for `Customer`, `Order`, `OrderDetails`, and `Laptop`.

Definition

Creating a Database in SQLite involves installing the SQLite tool, creating a database file, creating tables with defined columns, and using SQL commands to insert, query, update, and delete records.

In simple words: Creating a database means setting up the tools, making a database file, creating tables, and then adding and managing data using SQL commands.

Main Idea

The textbook demonstrates the complete workflow of working with a database:

• Install SQLite — Download and set up the SQLite tool.
• Create a Database — Use the `open` command to create a new database file.
• Create a Table — Use the `CREATE TABLE` command to define the table structure.
• Add Records — Use the `INSERT INTO` command to add data.
• Query Records — Use the `SELECT` command to retrieve data.
• Update Records — Use the `UPDATE` command to modify data.
• Sort Records — Use the `ORDER BY` clause to sort data.
• Delete Records — Use the `DELETE` command to remove data.
• Normalize the Database — Organize tables to reduce redundancy (up to 3NF).

Core Concept

• Go to the official SQLite website: `https://sqlitebrowser.org/dl/`
• Look for a "Download" button and select the right download for your system.
• Select the version that matches your Windows operating system and click the download link.
• Choose a suitable location to save the file (e.g., the 'Downloads' folder).
• The downloaded file is a compressed (ZIP) file. Right-click and select "Extract All" to create a new folder containing the SQLite files.
• To check whether SQLite was successfully installed, launch the Command Prompt by typing `cmd` in the Windows search bar.
• Use the `cd` command to navigate to the folder where you extracted the SQLite files (e.g., `cd C:\Downloads\sqlite`).
• Type `sqlite3` and press Enter. If the installation was successful, SQLite's command prompt will appear.

• Open the terminal or command prompt and type `sqlite3` and press Enter.
• Once inside the SQLite shell, use the command `open NBF.db` to create a new database named `NBF`.
• This command creates a new file, `NBF.db`, in the current directory to store your database.

3. Creating a Table Use the `CREATE TABLE` command to construct a table in the database. This command takes in the table's name, column names, and data types.

Example from the book (Fig. 3.19): ```sql CREATE TABLE Laptop ( L_ID TEXT PRIMARY KEY, L_Name TEXT NOT NULL, L_Price INTEGER NOT NULL, L_Brand TEXT NOT NULL ); ``` This creates a table called `Laptop` with four columns: - `L_ID` (text, primary key) - `L_Name` (text, not null) - `L_Price` (integer, not null) - `L_Brand` (text, not null)

Explanation: So far, only the schema (structure) is created, and it does not contain any records.

4. Adding Record(s) Use the `INSERT INTO` command to enter a single row in the table. This command requires the table name, column names, and values for the record.

Example from the book (Fig. 3.20): ```sql INSERT INTO Laptop (L_ID, L_Name, L_Price, L_Brand) VALUES ('L1', 'Dell XPS 13', 100000, 'Dell'); ``` This inserts a single record into the `Laptop` table.

Adding Multiple Records (Fig. 3.21): ```sql INSERT INTO Laptop (L_ID, L_Name, L_Price, L_Brand) VALUES ('L2', 'MacBook Air', 90000, 'Apple'), ('L3', 'HP Spectre x360', 120000, 'HP'); ``` This inserts two records at once.

5. Querying Records from Table using Select The `SELECT` command is the most-used query to retrieve records from a table. An `*` means all records in that table. Alternatively, column names can be specified to display only those columns.

Example from the book (Fig. 3.22): ```sql SELECT * FROM Customer; ``` This fetches all records from the `Customer` table.

Using WHERE clause (Fig. 3.23): ```sql SELECT * FROM Customer WHERE C_ID = 'C2'; ``` This fetches only the record where `C_ID` is `'C2'`.

6. Update Record For updating a record or a particular column, the `UPDATE` command is used. A good practice is to use a `SELECT` statement with a `WHERE` clause first to ensure the desired record is retrieved, then use `UPDATE` with the same condition.

Example from the book (Fig. 3.24): ```sql UPDATE Customer SET C_Address = 'New Address' WHERE C_ID = 'C1'; ``` This updates the address of the customer with `C<sub>I</sub>D = 'C1'`.

7. Use of Order By Clause The `ORDER BY` clause is used to display records in a sorted order. By default, the result is displayed in ascending order. The `DESC` keyword is used for descending order.

Example from the book (Fig. 3.25): ```sql SELECT * FROM Customer ORDER BY C_Name; ``` This displays records sorted by `C_Name` in ascending order.

Descending order: ```sql SELECT * FROM Customer ORDER BY C_Name DESC; ``` This displays records sorted by `C_Name` in descending order.

8. Deletion of Record(s) Use the `DELETE` command to delete a single or multiple entries from a table. The table structure remains in the database.

Example from the book (Fig. 3.26): ```sql DELETE FROM "Order" WHERE L_ID IN ('L1', 'L2', 'L3'); ``` This deletes records from the `Order` table where `L_ID` is `'L1'`, `'L2'`, or `'L3'`.

Important: Without a `WHERE` clause, all records will be deleted.

9. Normalization to 3NF The textbook explains how to normalize a database to Third Normal Form (3NF). The goal is to remove transitive dependencies.

Transitive dependency: A situation where a non-key attribute depends on another non-key attribute.

Example from the book: In the `Order` table, `L_ID` depends on `L_Name`, `L_Price`, and `L_Brand`, which are attributes of the `Laptop` table. This is a transitive dependency.

• The `Order` table should only reference `L_ID`.
• The `Laptop` table should store `L_Name`, `L_Price`, and `L_Brand`.

The 3NF Tables (from the book):

Database Tools The textbook mentions two tools:

MS Access: Part of MS Office suite, user-friendly drag-and-drop interface, commonly used by individuals and small organizations. Integrates well with other MS Office programs.

SQLite: Lightweight, serverless, single-file database. Ideal for applications needing local storage, mobile apps, and scenarios that do not require a large database server.

• Occupies small space.
• Easily integrated into applications.
• Serverless architecture.
• Complete database saved in a single file.

Intermediate Level

The textbook demonstrates a complete SQL workflow:

• `SELECT *` retrieves all columns.
• `WHERE` filters records.
• `ORDER BY` sorts records (ascending by default, `DESC` for descending).
• `DELETE` without `WHERE` deletes all records.

Important Points

• - SQLite Installation: Download from `sqlitebrowser.org`, extract, and run `sqlite3` from the command prompt.
• - Create Database: Use `open NBF.db` to create a new database.
• - Create Table: Use `CREATE TABLE` with column names and data types.
• - Add Records: Use `INSERT INTO` with values.
• - Query Records: Use `SELECT` with `*` for all columns or specific columns.
• - Update Records: Use `UPDATE` with `SET` and `WHERE`.
• - Sort Records: Use `ORDER BY` (ascending by default, `DESC` for descending).
• - Delete Records: Use `DELETE` with `WHERE` (without `WHERE`, all records are deleted).
• - Normalization to 3NF: Remove transitive dependencies by ensuring non-key attributes depend only on the primary key.
• - MS Access: User-friendly, drag-and-drop.
• - SQLite: Lightweight, serverless, single-file.

Remember

• - SQLite install: `sqlitebrowser.org`.
• - Create database: `open NBF.db`.
• - Create table: `CREATE TABLE`.
• - Add records: `INSERT INTO`.
• - Query records: `SELECT * FROM table;`.
• - Update records: `UPDATE table SET column = value WHERE condition;`.
• - Sort records: `ORDER BY column;` (or `DESC`).
• - Delete records: `DELETE FROM table WHERE condition;`.
• - Normalization: Remove transitive dependencies for 3NF.
• - SQLite = Lightweight, serverless, single file.

Common Mistakes

• 1. Forgetting the `WHERE` clause in `DELETE` — Without `WHERE`, all records are deleted.
• 2. Using `UPDATE` without `WHERE` — Without `WHERE`, all records are updated.
• 3. Not specifying column names in `INSERT` — The values must match the column order; specifying column names avoids errors.
• 4. Forgetting the semicolon at the end of SQL statements — SQL statements must end with `;`.
• 5. Not normalizing the database — This leads to redundancy and anomalies during updates/deletions.
• 6. Confusing `ORDER BY` ascending and descending — Default is ascending; use `DESC` for descending.
• 7. Trying to delete a table with `DELETE` — `DELETE` removes records, not the table. Use `DROP TABLE` to remove the table structure.

Quick Revision

• SQLite Installation: Download from `sqlitebrowser.org`, extract, navigate to the folder in command prompt, and run `sqlite3`.
• Database Creation: Use `open NBF.db` to create a new database.
• Creating a Table: Use `CREATE TABLE` with column names and data types.
• Adding Records: Use `INSERT INTO` with values.
• Querying Records: Use `SELECT * FROM table;` to retrieve all records. Use `WHERE` to filter.
• Updating Records: Use `UPDATE table SET column = value WHERE condition;`.
• Sorting Records: Use `ORDER BY column;` (ascending by default) or `ORDER BY column DESC;` (descending).
• Deleting Records: Use `DELETE FROM table WHERE condition;`. Without `WHERE`, all records are deleted.
• Normalization to 3NF: Remove transitive dependencies by ensuring non-key attributes depend only on the primary key.
• Database Tools: MS Access (user-friendly, drag-and-drop) and SQLite (lightweight, serverless, single file).

60-Second Revision

• Install SQLite → `sqlitebrowser.org`.
• Create DB → `open NBF.db`.
• Create Table → `CREATE TABLE`.
• Insert → `INSERT INTO`.
• Query → `SELECT * FROM table;`.
• Update → `UPDATE table SET column = value WHERE condition;`.
• Sort → `ORDER BY column;` (or `DESC`).
• Delete → `DELETE FROM table WHERE condition;`.
• Normalization → Remove transitive dependencies (3NF).
• Warning: Always use `WHERE` in `UPDATE` and `DELETE` to avoid affecting all records.

Frequently Asked Questions (FAQs)

• Go to `https://sqlitebrowser.org/dl/` and download the version for your Windows system.
• Extract the ZIP file to a folder (e.g., `Downloads\sqlite`).
• Open Command Prompt, navigate to the folder using `cd`, and type `sqlite3`. If the installation was successful, SQLite's command prompt will appear.

• Q2. How do you create a new database in SQLite?
• Answer: Open the SQLite command-line interface and type `open NBF.db`. This creates a new database file named `NBF.db` in the current directory.

• Q3. How do you create a table in SQLite?
• Answer: Use the `CREATE TABLE` command with the table name, column names, and data types. For example:
• ```sql
• CREATE TABLE Laptop (
• L_ID TEXT PRIMARY KEY,
• L_Name TEXT NOT NULL,
• L_Price INTEGER NOT NULL,
• L_Brand TEXT NOT NULL
• );
• ```

• Q4. How do you add a record to a table?
• Answer: Use the `INSERT INTO` command with the table name, column names, and values. For example:
• ```sql
• INSERT INTO Laptop (LID, LName, LPrice, LBrand)
• VALUES ('L1', 'Dell XPS 13', 100000, 'Dell');
• ```

• Q5. How do you retrieve records from a table?
• Answer: Use the `SELECT` command. For example:
• ```sql
• SELECT * FROM Customer;
• ```
• This retrieves all records from the `Customer` table. To filter records, use `WHERE`:
• ```sql
• SELECT * FROM Customer WHERE C_ID = 'C2';
• ```

• Q6. How do you update a record in a table?
• Answer: Use the `UPDATE` command with `SET` and `WHERE`. For example:
• ```sql
• UPDATE Customer
• SET C<sub>A</sub>ddress = 'New Address'
• WHERE C<sub>I</sub>D = 'C1';
• ```

• Q7. How do you sort records in a table?
• Answer: Use the `ORDER BY` clause. For ascending order (default):
• ```sql
• SELECT * FROM Customer ORDER BY C_Name;
• ```
• For descending order:
• ```sql
• SELECT * FROM Customer ORDER BY C_Name DESC;
• ```

• Q8. How do you delete records from a table?
• Answer: Use the `DELETE` command with `WHERE`. For example:
• ```sql
• DELETE FROM "Order" WHERE L_ID IN ('L1', 'L2', 'L3');
• ```
• Without a `WHERE` clause, all records will be deleted.

Q9. What is normalization to 3NF? Answer: Normalization to 3NF (Third Normal Form) removes transitive dependencies. A transitive dependency occurs when a non-key attribute depends on another non-key attribute. To remove it, the dependent attributes should be moved to a separate table.

• It occupies small space, making it suitable for applications with limited resources.
• It is easily integrated into applications.
• It has a serverless architecture, eliminating unnecessary dependencies.
• The complete database is saved in a single file.

Practice Questions

Short Response Questions (SRQs)

• How do you install SQLite on Windows?
• How do you create a new database in SQLite?
• Write the SQL command to create a table named `Laptop` with columns `L_ID`, `L_Name`, `L_Price`, and `L_Brand`.
• Write the SQL command to insert a record into the `Laptop` table.
• How do you retrieve all records from the `Customer` table?

Extended Response Questions (ERQs)

• Describe the complete process of creating and working with a database in SQLite, from installation to querying records.
• Explain the SQL commands for creating tables, inserting records, querying records, updating records, sorting records, and deleting records, with examples.
• Discuss the importance of normalization in database design. Explain how to normalize a database to 3NF using the example from the textbook.
• You are designing a database for a school. Describe how you would create the tables, add records, and query data using SQLite.
• Compare and contrast MS Access and SQLite as database tools. What are the advantages of each, and in what situations would you choose one over the other?

Multiple Choice Questions (MCQs)

• Q1. Which command is used to create a new database in SQLite?
• A. `CREATE DATABASE`
• B. `open`
• C. `NEW DATABASE`
• D. `CREATE TABLE`
• Correct Answer: B
• Explanation: The `open` command is used to create a new database in SQLite.

• Q2. Which SQL command is used to create a table?
• A. `CREATE TABLE`
• B. `INSERT INTO`
• C. `SELECT`
• D. `UPDATE`
• Correct Answer: A
• Explanation: `CREATE TABLE` is used to create a table.

• Q3. Which SQL command is used to add records to a table?
• A. `CREATE TABLE`
• B. `INSERT INTO`
• C. `SELECT`
• D. `UPDATE`
• Correct Answer: B
• Explanation: `INSERT INTO` is used to add records to a table.

• Q4. Which SQL command is used to retrieve records from a table?
• A. `CREATE TABLE`
• B. `INSERT INTO`
• C. `SELECT`
• D. `UPDATE`
• Correct Answer: C
• Explanation: `SELECT` is used to retrieve records from a table.

• Q5. Which SQL command is used to modify existing records?
• A. `CREATE TABLE`
• B. `INSERT INTO`
• C. `SELECT`
• D. `UPDATE`
• Correct Answer: D
• Explanation: `UPDATE` is used to modify existing records.

• Q6. Which clause is used to sort records?
• A. `WHERE`
• B. `ORDER BY`
• C. `GROUP BY`
• D. `HAVING`
• Correct Answer: B
• Explanation: The `ORDER BY` clause is used to sort records.

• Q7. Which SQL command is used to delete records from a table?
• A. `CREATE TABLE`
• B. `DELETE`
• C. `SELECT`
• D. `UPDATE`
• Correct Answer: B
• Explanation: `DELETE` is used to delete records from a table.

• Q8. What happens if you use `DELETE` without a `WHERE` clause?
• A. Only one record is deleted
• B. All records are deleted
• C. The table is deleted
• D. An error occurs
• Correct Answer: B
• Explanation: Without a `WHERE` clause, all records in the table are deleted.

• Q9. What is normalization to 3NF?
• A. Adding more tables
• B. Removing transitive dependencies
• C. Encrypting data
• D. Backing up data
• Correct Answer: B
• Explanation: Normalization to 3NF removes transitive dependencies.

• Q10. Which of the following is an advantage of SQLite?
• A. It requires a powerful server
• B. It occupies small space
• C. It cannot be integrated into applications
• D. It requires complex installation
• Correct Answer: B
• Explanation: SQLite occupies small space, making it suitable for applications with limited resources.