Database Basics

Before learning SQL, you need to understand basic concepts of databases. This chapter introduces core concepts, relational model, and basic terminology of databases, laying a solid foundation for subsequent learning.

What is a Database?

Definition

A database is an organized collection of data, usually stored in a computer system. It allows users to efficiently store, retrieve, and manage data.

Database Management System (DBMS)

A Database Management System (DBMS) is software used to create, manage, and operate databases. Common DBMS include:

• MySQL
• PostgreSQL
• SQL Server
• Oracle
• SQLite

Relationship

Database ← Place where data is stored DBMS ← Software that manages the database SQL ← Language for interacting with DBMS

Relational Databases

Core Concepts

A relational database is a database system based on the relational model, where data is stored in tabular format (tables).

Table Example

id	name	age	city
1	Zhang San	25	Beijing
2	Li Si	30	Shanghai
3	Wang Wu	28	Guangzhou

This is a typical database table containing rows and columns.

Basic Database Terminology

1. Table

A table is the basic unit for storing data in a database, similar to an Excel spreadsheet.

Example: Users Table

-- SQL create table
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

2. Column

A column defines the type and attributes of data in a table, also called a field.

Column Features

• Data Type: Defines the type of data in the column (integer, text, date, etc.)
• Constraint: Rules that limit data in the column (cannot be empty, must be unique, etc.)
• Default Value: Value used by default if no value is provided

Example: Column Definition

CREATE TABLE products (
    id INT,                    -- Integer column
    name VARCHAR(100),         -- Text column
    price DECIMAL(10,2),       -- Decimal column
    stock INT DEFAULT 0,       -- Column with default value
    created_at DATE            -- Date column
);

3. Row

A row, also called a record, is a complete piece of data in a table.

Example: Row Data

-- Insert row data
INSERT INTO users (id, name, age, city)
VALUES (1, 'Zhang San', 25, 'Beijing');

4. Primary Key

A primary key is a column in a table that uniquely identifies each row. Primary key values cannot be duplicated and cannot be null.

Primary Key Features

• Uniqueness: Each row's primary key value must be unique
• Non-null: Primary key values cannot be NULL
• Immutability: Primary key values typically do not change

Example: Defining Primary Key

CREATE TABLE users (
    id INT PRIMARY KEY,        -- id column is the primary key
    name VARCHAR(50),
    age INT
);

5. Foreign Key

A foreign key is a column in one table that references the primary key of another table, used to establish relationships between tables.

Foreign Key Functions

• Ensure data consistency
• Establish relationships between tables
• Prevent insertion of invalid data

Example: Foreign Key Relationship

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Orders table (references users table)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

6. Index

An index is a data structure that improves query performance, similar to a table of contents in a book.

Index Functions

• Speed up data retrieval
• Optimize query performance
• But occupies additional storage space

Example: Creating Index

-- Create index for name column
CREATE INDEX idx_name ON users(name);

Database Structure

Database → Table → Rows/Columns

Database
  ├── Users Table (Table: users)
  │     ├── Columns: id, name, age, city
  │     └── Rows: (1, 'Zhang San', 25, 'Beijing')
  └── Orders Table (Table: orders)
        ├── Columns: id, user_id, amount
        └── Rows: (1, 1, 99.99)

Hierarchy

1. Database Server: Computer running DBMS
2. Database: Collection of related tables
3. Table: Structure storing related data
4. Row/Column: Actual data units

Relational Model

One-to-One Relationship (1:1)

One record in a table corresponds to one record in another table.

Example: Users and User Details

users table          user_details table
id (PK)   →      user_id (FK, PK)
name             address

-- One user corresponds to one detail
SELECT u.name, d.address
FROM users u
JOIN user_details d ON u.id = d.user_id;

One-to-Many Relationship (1:N)

One record in a table corresponds to multiple records in another table.

Example: Users and Orders

users table          orders table
id (PK)   →      user_id (FK)
name             order_id
                  amount

-- One user can have multiple orders
SELECT u.name, o.order_id, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

Many-to-Many Relationship (M:N)

Records in two tables can correspond to multiple records in each other.

Example: Students and Courses

students table      enrollments table      courses table
id (PK)    →     student_id (FK)    id (PK)
name             course_id (FK)      name

-- One student can take multiple courses
-- One course can have multiple students
SELECT s.name, c.name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

Common Data Types

Numeric Types

Type	Description	Example
INT	Integer	1, 2, 100
DECIMAL(M,D)	Decimal	123.45, 99.99
FLOAT	Floating point	3.14, 2.718
BIGINT	Large integer	9223372036854775807

String Types

Type	Description	Max Length
CHAR(N)	Fixed-length string	255
VARCHAR(N)	Variable-length string	65535
TEXT	Long text	65535+

Date/Time Types

Type	Description	Format
DATE	Date	YYYY-MM-DD
TIME	Time	HH:MM:SS
DATETIME	Date and time	YYYY-MM-DD HH:MM:SS
TIMESTAMP	Timestamp	YYYY-MM-DD HH:MM:SS

Boolean Type

Type	Description	Value
BOOLEAN	Boolean value	TRUE, FALSE

Database Design Principles

1. Data Normalization

Normalization is the process of organizing database tables to reduce data redundancy and improve data integrity.

2. Primary Key Design

• Use a single column as the primary key
• Use auto-incrementing integers
• Avoid using columns with business meaning

3. Naming Conventions

• Use meaningful names
• Use lowercase letters and underscores
• Avoid using reserved words

Real Example: E-commerce Platform

Database Structure

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at DATETIME
);

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2),
    order_date DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Order items table
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Data Relationships

User (1) ←→ (N) Order (1) ←→ (N) Order Items (N) ←→ (1) Product

Basic Database Operation Flow

1. Connect to Database

-- Connect to database server
mysql -u username -p

2. Select Database

-- Use specific database
USE database_name;

3. Execute Query

-- Execute SQL statement
SELECT * FROM users;

4. Disconnect

-- Disconnect from database
EXIT;

Summary

This chapter introduced basic database concepts:

• Database Definition: Organized collection of data
• Relational Database: Database based on relational model
• Basic Terminology: Table, column, row, primary key, foreign key, index
• Data Types: Numeric, string, date/time, boolean
• Relationship Types: One-to-one, one-to-many, many-to-many
• Design Principles: Normalization, primary key design, naming conventions

Understanding these basic concepts is the foundation for learning SQL. In the following chapters, we will learn how to use SQL to actually operate databases.

Next Step: Learn SQL Basic Syntax and start writing your first SQL statement.