Introduction to Database Systems

TL;DR

Databases help you store, organize, and quickly find large amounts of information. They're built on structured models, mainly relational, which use tables to link data together. Understanding how they work is key to managing data effectively in almost any application.

1. The Mental Model

Think of a database like a super-organized digital filing cabinet for all your important stuff. Instead of just throwing files in, you have specific drawers (tables) and folders (columns) for every piece of information, making it easy to find exactly what you need whenever you want.

2. The Core Material

A database system is a way to store, manage, and retrieve data efficiently. It's much more than just a collection of files; it includes software (the Database Management System, or DBMS) that handles all the heavy lifting.

Why Use a Database?

Photo by Markus Winkler on Pexels

• Data Persistence: Information stays stored even after a program closes.
• Data Integrity: Rules ensure data is accurate and consistent (e.g., no negative ages).
• Data Security: Controls who can access or change information.
• Concurrency: Multiple users can access and modify data at the same time without messing things up.
• Scalability: Can handle growing amounts of data and users.
• Efficiency: Optimized for quick data retrieval and updates.

Key Components of a Database System

Photo by Sergei Starostin on Pexels

1. Data: The actual information stored.
2. Schema: The structure or blueprint of the data (like a table definition).
3. DBMS: The software that lets you interact with the data (e.g., MySQL, PostgreSQL, Oracle).
4. Database Language: A way to talk to the DBMS, most commonly SQL (Structured Query Language).

Database Models: How Data is Organized

Photo by Markus Winkler on Pexels

There are different ways to organize data. The most common in business is the Relational Model.

Relational Model

In the relational model, data is stored in tables (also called relations). Each table has rows (records) and columns (attributes). Tables are linked together using common columns called keys.

• Table: A collection of related data organized into rows and columns.
• Row (Tuple/Record): A single entry in a table, representing one complete set of information.
• Column (Attribute/Field): A specific piece of information for each row, with a defined data type (e.g., name is text, age is a number).
• Primary Key: A column (or set of columns) that uniquely identifies each row in a table. You can't have duplicates, and it can't be empty (NULL).
• Foreign Key: A column in one table that refers to the Primary Key in another table. This is how you link tables and establish relationships.

Here's a simple example of how entities and relationships connect:

erDiagram
    "CUSTOMER" {
        "CustomerID" PK
        "Name"
        "Email"
    }
    "ORDER" {
        "OrderID" PK
        "CustomerID" FK
        "OrderDate"
        "TotalAmount"
    }
    "PRODUCT" {
        "ProductID" PK
        "Name"
        "Price"
    }
    "ORDER_ITEM" {
        "OrderItemID" PK
        "OrderID" FK
        "ProductID" FK
        "Quantity"
    }

    "CUSTOMER" ||--o{ "ORDER" : places
    "ORDER" ||--o{ "ORDER_ITEM" : contains
    "PRODUCT" ||--o{ "ORDER_ITEM" : includes

Data Independence

Photo by Valentin Ivantsov on Pexels

A major benefit of database systems is data independence. This means changes to how data is stored physically (e.g., changing hard drives) don't require changes to how applications access the data. Similarly, changes to the logical structure (e.g., adding a new column) ideally don't break existing applications. This is achieved through a multi-level architecture:

• Physical Level: How data is actually stored on disk.
• Conceptual/Logical Level: The overall structure of the database, describing what data is stored and the relationships among the data.
• External/View Level: How specific users or applications see a subset of the database.

3. Worked Example

Let's say you're building a simple system for a library to track books and borrowers.

Problem: You need to store information about books (title, author, ISBN) and borrowers (name, ID, email) and also track which books are currently borrowed by whom.

Solution using a Relational Database:

1. Books Table:

   • ISBN (Primary Key - unique identifier for a book)
   • Title
   • Author
   • PublicationYear

2. Borrowers Table:

   • BorrowerID (Primary Key - unique identifier for a borrower)
   • Name
   • Email

3. Loans Table: (This links Books and Borrowers)

   • LoanID (Primary Key - unique identifier for a loan)
   • ISBN (Foreign Key, referencing Books.ISBN)
   • BorrowerID (Foreign Key, referencing Borrowers.BorrowerID)
   • LoanDate
   • ReturnDate (can be NULL if not yet returned)

Example Data:

Books Table:

Borrowers Table:

Loans Table:

Here, ISBN in Loans links to Books, and BorrowerID in Loans links to Borrowers. If you want to know what books Alice has borrowed, you'd look up Alice's BorrowerID (101), then find all entries in the Loans table with BorrowerID = 101, and then use the ISBN from those loans to find the book titles in the Books table.

4. Key Takeaways

• Databases are structured systems for storing, organizing, and retrieving data reliably.
• The most common model, relational, uses tables with rows and columns, linked by keys.
• A Primary Key uniquely identifies a row, while a Foreign Key links tables together.
• Database Management Systems (DBMS) are the software that handle all interactions with the data.
• SQL is the standard language for querying and managing relational databases.
• Data independence means you can change how data is stored without breaking applications.

Common mistakes to avoid:
- Storing all data in one giant table without thinking about relationships.
- Not defining Primary Keys, which makes it hard to uniquely identify records.
- Not using Foreign Keys, which means you can't properly link related data between tables.
- Trying to manage large amounts of structured data with simple files or spreadsheets instead of a proper database.

5. Now Try It

Think about a simple system you use daily, like your phone's contact list or a music playlist. Sketch out what tables you'd need, what columns would be in each table, and how those tables would relate to each other if you were to design a simple relational database for it. Define at least one Primary Key for each table. Success looks like having a clear set of related tables, each with a primary key, and an idea of how foreign keys would connect them.