Foundational Concepts of Databases and RDBMS

TL;DR

Databases are organized collections of data, making it easy to store, manage, and retrieve information. A Relational Database Management System (RDBMS) uses tables to organize data, connecting them through relationships. SQL is the standard language you'll use to interact with and manipulate data within an RDBMS.

1. The Mental Model

Think of a database like a super-organized digital filing cabinet for your data. An RDBMS is the specific type of cabinet that uses folders (tables) with columns and rows, and it knows how to link related folders together.

2. The Core Material

At its heart, a database is just a structured collection of information. Imagine all your contacts, photos, or financial transactions. Without a database, this data would be chaotic and hard to use. Databases provide a systematic way to store and manage it.

A Database Management System (DBMS) is the software that allows you to interact with a database. It handles storing, retrieving, defining, and managing data. There are various types of DBMSs, but in this course, we're focusing on one specific, very popular kind: the Relational Database Management System (RDBMS).

What is an RDBMS?

Photo by Ann H on Pexels

An RDBMS organizes data into one or more tables, which are also called relations. Each table has a defined structure of columns and rows.

• Tables: Like a spreadsheet, a table consists of rows and columns.
• Rows (Records/Tuples): Each row represents a single, complete entry or record in the table. For example, in a Customers table, one row would be for a single customer.
• Columns (Fields/Attributes): Each column represents a specific piece of information or attribute for each row. In the Customers table, columns might be CustomerID, FirstName, LastName, Email.

The "relational" part comes from how these tables can be linked together using common columns. This prevents data duplication and keeps your data consistent.

graph TD
    A["Database (e.g., OnlineStoreDB)"] --> B["RDBMS (e.g., MySQL Server)"]
    B --> C["Table: Customers"]
    B --> D["Table: Products"]
    B --> E["Table: Orders"]

    C --> C1["CustomerID (PK)"]
    C --> C2["FirstName"]
    C --> C3["LastName"]
    C --> C4["Email"]

    D --> D1["ProductID (PK)"]
    D --> D2["ProductName"]
    D --> D3["Price"]

    E --> E1["OrderID (PK)"]
    E --> E2["CustomerID (FK)"]
    E --> E3["OrderDate"]
    E --> E4["TotalAmount"]

    C1 --> E2["Links Customers to Orders"]

Primary Keys and Foreign Keys

Photo by Jan van der Wolf on Pexels

These are crucial for establishing relationships between tables:

• Primary Key (PK): A column (or set of columns) that uniquely identifies each row in a table. No two rows can have the same primary key value, and it cannot be empty (NULL). Think of it like a unique ID card for each record. In our Customers table, CustomerID would be the primary key.
• Foreign Key (FK): A column (or set of columns) in one table that refers to the primary key in another table. Foreign keys establish the link or relationship between tables. For instance, CustomerID in the Orders table would be a foreign key, referencing the CustomerID (PK) in the Customers table. This tells us which customer placed a specific order.

SQL: The Language of Databases

Photo by Myburgh Roux on Pexels

Structured Query Language (SQL) is the standard language used to communicate with and manage relational databases. You'll use SQL to:

• Create databases and tables.
• Insert new data into tables.
• Select (retrieve) data from tables.
• Update existing data.
• Delete data.

It's a powerful declarative language, meaning you tell the database what you want to achieve, not necessarily how to achieve it.

3. Worked Example

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

First, you'd define two tables: Books and Borrowers.

Table: Books
| BookID (PK) | Title | Author |
| :---------- | :---------------- | :----------- |
| 101 | The Hitchhiker's Guide | Douglas Adams |
| 102 | 1984 | George Orwell |
| 103 | Pride and Prejudice | Jane Austen |

Table: Borrowers
| BorrowerID (PK) | FirstName | LastName |
| :-------------- | :-------- | :------- |
| 1 | Alice | Smith |
| 2 | Bob | Johnson |

Now, to track who borrowed which book, you'd create a third table, Loans, which links Books and Borrowers using foreign keys:

Table: Loans
| LoanID (PK) | BookID (FK) | BorrowerID (FK) | LoanDate |
| :---------- | :---------- | :-------------- | :--------- |
| 1001 | 101 | 1 | 2023-10-26 |
| 1002 | 103 | 2 | 2023-10-27 |
| 1003 | 101 | 2 | 2023-10-28 |

Here:
* BookID in Loans is a foreign key referencing BookID in the Books table.
* BorrowerID in Loans is a foreign key referencing BorrowerID in the Borrowers table.

This structure allows you to know that Alice Smith (BorrowerID 1) borrowed "The Hitchhiker's Guide" (BookID 101) on 2023-10-26.

4. Key Takeaways

• Databases are structured collections of information, while an RDBMS is software that organizes this data into related tables.
• Tables consist of rows (records) and columns (attributes) to store specific pieces of data.
• Primary keys uniquely identify each row in a table and are crucial for data integrity.
• Foreign keys link tables together by referencing primary keys in other tables, creating relationships.
• SQL is the universal language you'll use to interact with, query, and modify data in an RDBMS.
• Relational databases avoid data redundancy by storing related information in separate tables and linking them.

Common Mistakes to Avoid:
* Ignoring Primary Keys: Not defining primary keys makes it impossible to uniquely identify records and establish reliable relationships.
* Duplicating Data: Storing the same information in multiple places across different tables (e.g., storing a customer's address in both Customers and Orders) leads to inconsistency.
* Poorly Chosen Column Names: Using vague or inconsistent column names makes your database hard to understand and work with.
* Forgetting Relationships: Creating tables without establishing appropriate foreign key relationships means your data is isolated and hard to query meaningfully.

5. Now Try It

Think about a personal collection you have, like movies, music, or video games. On a piece of paper, design three related tables for this collection. For each table, list at least three columns, clearly marking which column would be its primary key and identifying any foreign keys you'd need to link them. For instance, if you chose movies, you might have Movies, Actors, and MovieCast tables. What would their columns be?