Day 3: Creating Your First Database and Understanding Schema

Now that PostgreSQL is installed, let's dive into creating databases and understanding their structure!

What is a Database?

A database is a container that holds related data organized into tables. Think of it as a digital filing cabinet where each drawer (table) contains specific types of information.

Creating Your First Database

Using psql (Command Line)

-- Connect to PostgreSQL
psql -U postgres

-- Create a new database
CREATE DATABASE my_first_db;

-- List all databases
\l

-- Connect to the new database
\c my_first_db

Using pgAdmin

1. Open pgAdmin
2. Right-click on "Databases" under your server
3. Select "Create" → "Database"
4. Enter name: my_first_db
5. Click "Save"

Understanding Database Components

1. Schemas

A schema is a namespace within a database. Every database has a default public schema.

-- View all schemas
\dn

-- Create a new schema
CREATE SCHEMA sales;

-- Create a schema for HR
CREATE SCHEMA hr;

Why use schemas?

• Organize tables logically
• Separate development from production
• Control access permissions

2. Tables

Tables store actual data in rows and columns.

-- Create your first table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

Let's break this down:

• SERIAL: Auto-incrementing integer
• PRIMARY KEY: Unique identifier for each row
• VARCHAR(n): Variable-length text (max n characters)
• UNIQUE: Ensures no duplicates
• DATE: Calendar date
• DECIMAL(10,2): Numbers with precision (10 digits, 2 after decimal)

3. Data Types Overview

Data Type	Description	Example
INTEGER	Whole numbers	42, -10
BIGINT	Large integers	9223372036854775807
VARCHAR(n)	Variable text	'Hello'
TEXT	Unlimited text	Long paragraphs
DATE	Calendar date	'2025-10-10'
TIMESTAMP	Date + time	'2025-10-10 14:30:00'
BOOLEAN	True/False	TRUE, FALSE
DECIMAL	Exact numbers	99.99
JSON	JSON data	'{"key": "value"}'

Let's Create a Sample Database

-- Create a library database
CREATE DATABASE library_db;

-- Connect to it
\c library_db

-- Create books table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100) NOT NULL,
    isbn VARCHAR(13) UNIQUE,
    published_date DATE,
    pages INTEGER,
    available BOOLEAN DEFAULT TRUE
);

-- Create members table
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    join_date DATE DEFAULT CURRENT_DATE
);

Viewing Your Database Structure

-- List all tables
\dt

-- Describe a specific table
\d employees

-- View table with indexes and constraints
\d+ employees

Dropping (Deleting) Databases and Tables

⚠️ WARNING: These commands permanently delete data!

-- Drop a table
DROP TABLE IF EXISTS table_name;

-- Drop a database (disconnect first!)
\c postgres
DROP DATABASE IF EXISTS my_first_db;

Best Practices for Database Design

✅ Use meaningful names: customer_orders not tbl1
✅ Follow naming conventions: lowercase with underscores
✅ Always include PRIMARY KEY: Ensures unique identification
✅ Use appropriate data types: Don't store dates as text!
✅ Add NOT NULL where needed: Prevent incomplete data
✅ Document your schema: Use comments

COMMENT ON TABLE employees IS 'Stores employee information';
COMMENT ON COLUMN employees.salary IS 'Annual salary in USD';

---

Tomorrow's Preview: Day 4 - Inserting and Querying Data (Your First CRUD Operations)

Practice Exercise:
Create a database called school_db with two tables:

1. students (student_id, name, email, enrollment_date)
2. courses (course_id, course_name, credits)

Share your table creation queries in the comments! 🎓