This topic will cover the basics of database systems, including the definition of databases, types of databases (relational, NoSQL, etc.), and the importance of managing database systems efficiently.
Introduction to Database Systems
Key concepts, components, and fundamentals for anyone starting out in data management.
1. What Is a Database?
| Aspect |
Description |
| Definition |
An organized collection of related data stored electronically for efficient retrieval, update, and management. |
| Purpose |
Preserve data integrity, eliminate redundancy, enable concurrent access, and support decision‑making. |
| Typical Uses |
Business transactions, inventory control, customer relationship management (CRM), scientific research, web applications, etc. |
2. Core Terminology
| Term |
Meaning |
| DBMS (Database Management System) |
Software that creates, maintains, and controls access to databases (e.g., MySQL, PostgreSQL, Oracle, MongoDB). |
| Schema |
The logical structure describing tables, fields, relationships, constraints, and indexes. |
| Instance |
The actual data stored in the database at a particular moment (a snapshot of the database). |
| Tuple/Row |
A single record in a table. |
| Attribute/Column |
A field within a table that holds a specific type of data. |
| Primary Key |
A unique identifier for each row in a table. |
| Foreign Key |
A column that references a primary key in another table, establishing a relationship. |
| Normalization |
Process of organizing data to reduce redundancy and improve integrity. |
| Transaction |
A logical unit of work that must be atomic, consistent, isolated, and durable (ACID). |
3. Types of Database Models
| Model |
Characteristics |
Typical Use Cases |
| Relational (RDBMS) |
Data stored in tables; uses SQL; strong ACID guarantees. |
Financial systems, ERP, CRM. |
| NoSQL |
Flexible schemas; various sub‑types (document, key‑value, column‑family, graph). |
Big data, real‑time analytics, content management. |
| Object‑Oriented |
Stores objects directly; aligns with object‑oriented programming. |
CAD/CAM, multimedia databases. |
| Hierarchical |
Tree‑like structure; parent‑child relationships. |
Legacy mainframe systems (e.g., IBM IMS). |
| Network |
Graph‑like structure; many‑to‑many relationships. |
Complex routing or bill‑of‑materials data. |
4. Relational Database Fundamentals
4.1. Tables (Relations)
- Consist of rows (records) and columns (attributes).
- Must have a primary key.
4.2. Relationships
| Cardinality |
Description |
Example |
| One‑to‑One (1:1) |
Each row in Table A matches at most one row in Table B. |
Person ↔ Passport. |
| One‑to‑Many (1:N) |
One row in Table A relates to many rows in Table B. |
Customer ↔ Orders. |
| Many‑to‑Many (M:N) |
Rows in Table A can relate to multiple rows in Table B and vice‑versa. Implemented via a junction (link) table. |
Students ↔ Courses. |
4.3. SQL – Structured Query Language
| Category |
Common Commands |
| Data Definition Language (DDL) |
CREATE, ALTER, DROP, TRUNCATE |
| Data Manipulation Language (DML) |
SELECT, INSERT, |