Database Fundamentals
Before diving into SQL, let's understand what databases are and why they're essential for modern applications.
What is a Database?
A database is an organized collection of structured data stored electronically. Think of it as a digital filing cabinet that can:
- Store millions of records efficiently
- Find specific information in milliseconds
- Allow multiple users to access data simultaneously
- Keep data consistent and secure
Relational Databases
A relational database organizes data into tables (also called relations). Each table consists of:
- Rows (records) - Individual entries
- Columns (fields) - Attributes of each entry
Example: Students Table
| id | first_name | last_name | gpa | |
|---|---|---|---|---|
| 1 | Alice | Brown | alice@email.com | 3.85 |
| 2 | Bob | Williams | bob@email.com | 3.45 |
| 3 | Carol | Jones | carol@email.com | 3.92 |
Key Concepts
Primary Key
A primary key uniquely identifies each row in a table. In our Students table, id is the primary key - no two students can have the same ID.
Foreign Key
A foreign key creates a relationship between tables. For example, an enrollments table might have a student_id column that references the id in the students table.
Relationships
Tables can be related in three ways:
- One-to-One - One student has one transcript
- One-to-Many - One instructor teaches many courses
- Many-to-Many - Students enroll in many courses; courses have many students
SQL: The Language of Databases
SQL (Structured Query Language) is the standard language for interacting with relational databases. With SQL you can:
- Query data -
SELECT - Insert new data -
INSERT - Update existing data -
UPDATE - Delete data -
DELETE - Create tables and structures -
CREATE
PostgreSQL
PostgreSQL (often called "Postgres") is a powerful, open-source relational database system. It's known for:
- Reliability - Used in production by millions of applications
- Standards compliance - Follows SQL standards closely
- Extensibility - Supports custom functions, types, and operators
- Advanced features - JSON support, full-text search, and more
RDBMS Features
A Relational Database Management System (RDBMS) like PostgreSQL provides:
- ACID Transactions - Atomicity, Consistency, Isolation, Durability
- Concurrency Control - Multiple users can work simultaneously
- Data Integrity - Constraints ensure valid data
- Security - User authentication and permissions
- Backup & Recovery - Protect against data loss
Summary
- Databases organize data in structured tables
- Relational databases use relationships between tables
- Primary keys uniquely identify rows
- Foreign keys create relationships between tables
- SQL is the language for working with databases
- PostgreSQL is a powerful, open-source RDBMS