Introduction to Databases

What is a Database?

A database is a structured collection of data that is organized for efficient access, management, and storage. Its purpose is to store and retrieve data in a systematic manner.

Database Concepts

• Table: A collection of related data records organized into rows and columns.
• Record: A single row in a table that represents an object or entity.
• Field: A specific piece of data within a record, such as name or address.
• Key: A field that uniquely identifies each record in a table.
• Index: A data structure that speeds up data retrieval.

SQL Basics

Structured Query Language (SQL) is the standard language used to interact with databases. It allows you to:

• Create and modify databases and tables.
• Insert, update, and delete data.
• Retrieve data using queries.

Relational Databases

Relational databases organize data into multiple related tables. They utilize keys to establish relationships between tables, ensuring data integrity and consistency.

Practical Steps

• Create a database: CREATE DATABASE my_database;
• Create a table: CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
• Insert data: INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
• Retrieve data: SELECT * FROM users;
• Update data: UPDATE users SET name = 'John Smith' WHERE id = 1;
• Delete data: DELETE FROM users WHERE id = 1;

Python Example

import sqlite3

# Connect to the database
conn = sqlite3.connect('my_database.db')
# Create a cursor
c = conn.cursor()

# Create a table
c.execute('''CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)''')

# Insert data
c.execute('''INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')''')

# Commit the changes
conn.commit()

# Retrieve data
for row in c.execute('SELECT * FROM users'):
    print(row)

# Close the connection
conn.close()