In this lesson, you'll build on your planning from the previous lesson by creating the database for your interactive information system and integrating it with a backend. We'll review data types and relational databases, then guide you through setting up a database, inserting data, creating backend routes, and testing everything. 

This lesson ties into the Python Web Backend module, especially the Databases unit. It should take about 180-240 minutes over a few sessions. Remember, your system should meet user needs while using abstraction to organise data efficiently.

Let's quickly review key concepts from the Data, Logic and Numbers module and the Databases unit in Python Web Backend. This will help you build a solid database.

Data Types: In databases, data types define what kind of data a field can hold. Common ones include:

• Integer: Whole numbers, e.g., for IDs or counts.
• Text/String: For names, descriptions.
• Real/Float: Decimal numbers, e.g., prices.
• Date: For timestamps.
• Boolean: True/False values, e.g., availability.

Choosing the right type ensures efficiency and prevents errors.

Relational Databases: These store data in tables with relationships. Key elements:

• Tables: Like spreadsheets, with rows (records) and columns (fields).
• Primary Key: Unique identifier for each record.
• Foreign Key: Links tables, e.g., a Loan table's UserID linking to Users table.
• Relationships: One-to-one, one-to-many, many-to-many.

Here's a simple diagram of a relational database structure:

Now create your database based on your schema. We'll use SQLite as it's lightweight and built into Python, but you can use another database if you prefer.

If you haven't already:

1. Create your project folder in VS Code i.e. ALT1Project
2. Create the virtual environment
3. Open a terminal and install Flask

In a new Python file (e.g., create_db.py), set up the database. Here's an example:

import sqlite3

# Connect to (or create) the database file
conn = sqlite3.connect('library.db')

c = conn.cursor()

# Create Users table
c.execute('''CREATE TABLE IF NOT EXISTS Users
             (UserID INTEGER PRIMARY KEY,
              Name TEXT,
              Role TEXT,
              Email TEXT)''')

# Create Books table
c.execute('''CREATE TABLE IF NOT EXISTS Books
             (BookID INTEGER PRIMARY KEY,
              Title TEXT,
              Author TEXT,
              ISBN TEXT,
              Availability BOOLEAN)''')

# Create Loans table
c.execute('''CREATE TABLE IF NOT EXISTS Loans
             (LoanID INTEGER PRIMARY KEY,
              UserID INTEGER,
              BookID INTEGER,
              LoanDate DATE,
              ReturnDate DATE,
              FOREIGN KEY (UserID) REFERENCES Users(UserID),
              FOREIGN KEY (BookID) REFERENCES Books(BookID))''')

# Save changes and close
conn.commit()
conn.close()
print('Database and tables created successfully!')

With the database created, you should insert some initial test data.

Create a new Python file (e.g., insert_test_data.py) and use a script such as the following to insert the data:

import sqlite3

# Connect to the database file
conn = sqlite3.connect('library.db')
c = conn.cursor()

# Insert test records into Users table
c.execute("INSERT INTO Users (UserID, Name, Role, Email) VALUES (1, 'Alice Smith', 'Student', 'alice@example.com')")
c.execute("INSERT INTO Users (UserID, Name, Role, Email) VALUES (2, 'Bob Johnson', 'Librarian', 'bob@example.com')")

# Insert test records into Books table
c.execute("INSERT INTO Books (BookID, Title, Author, ISBN, Availability) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', '978-0743273565', 1)")
c.execute("INSERT INTO Books (BookID, Title, Author, ISBN, Availability) VALUES (2, '1984', 'George Orwell', '978-0451524935', 0)")

# Insert test records into Loans table
c.execute("INSERT INTO Loans (LoanID, UserID, BookID, LoanDate, ReturnDate) VALUES (1, 1, 1, '2025-08-01', '2025-08-15')")
c.execute("INSERT INTO Loans (LoanID, UserID, BookID, LoanDate, ReturnDate) VALUES (2, 2, 2, '2025-07-20', '2025-08-10')")

# Save changes and close
conn.commit()
conn.close()
print('Test records inserted successfully!')

Adapt this script for your own tables. Insert data that matches your user needs, like sample books or users.

Now that you've inserted some test data into your database, it's important to verify that everything has been added correctly. You can do this by running SELECT queries to retrieve and display the data. This will help you ensure that the data matches what you expect and that there are no errors.