Database Fundamentals

Organizing Information

At its heart, a database is just an organized collection of data. Think of it like a digital filing cabinet. Instead of stuffing papers randomly into a drawer, you sort them into folders, making it easy to find what you need later. A database does the same thing for information, but on a massive scale.

database

noun

A structured set of data held in a computer, especially one that is accessible in various ways.

The most common type is a relational database. It organizes data into tables, which are similar to spreadsheets. A Relational Database Management System (RDBMS) is the software that allows us to create, update, and manage these databases. Examples you might have heard of include MySQL, PostgreSQL, and SQL Server.

Relational databases use tables to store data, and these tables can be linked to each other based on shared information.

The Building Blocks

Every relational database is built from a few simple components: tables, columns, and rows.

A table is a collection of related data, like a list of all your customers. Each table has columns, which define the type of information you're storing (like FirstName, LastName, and Email), and rows, which represent individual records (each specific customer).

CustomerID	FirstName	LastName	Email
1	Maria	Anders	maria.a@email.com
2	Ana	Trujillo	ana.t@email.com
3	Antonio	Moreno	antonio.m@email.com

In this Customers table:

The entire grid is the table.
CustomerID, FirstName, LastName, and Email are the columns.
Each line of customer information is a row.

Connecting the Dots

You might wonder why we don't just put all information into one giant table. Imagine adding every order a customer makes to our Customers table. The table would get messy and full of repeated information. Instead, we create separate tables for different things, like customers and orders, and then create relationships between them.

This is where keys come in.

primary key

noun

A column (or set of columns) in a table that uniquely identifies each row. It cannot contain NULL values, and each value must be unique.

A foreign key is a column in one table that refers to the primary key in another table. It's the glue that links your tables together. For example, we can create an Orders table and use a CustomerID column to link each order back to the customer who made it.

This structure keeps data organized and prevents duplication. If a customer changes their name, we only need to update it in one place: the Customers table.

Speaking the Language of Data

To interact with a relational database, we use a language called SQL (Structured Query Language). It's the standard for managing data in an RDBMS. With SQL, you can ask the database to retrieve information, add new records, update existing ones, or delete them.

These four basic operations are often called CRUD:

Create (INSERT)
Read (SELECT)
Update (UPDATE)
Delete (DELETE)

Let's look at a simple command for each.

To read data, we use SELECT. This command retrieves data from one or more tables. To get the first and last names of all customers, you'd write:

SELECT FirstName, LastName
FROM Customers;

To create a new record, we use INSERT. This adds a new row to a table. Here’s how to add a new customer:

INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('John', 'Smith', 'john.s@email.com');

To update existing data, we use the UPDATE command. If Ana Trujillo gets a new email address, we can change it like this:

UPDATE Customers
SET Email = 'new.ana.t@email.com'
WHERE CustomerID = 2;

The WHERE clause is crucial. It specifies which row to update. Without it, you would update the email for every single customer in the table.

Finally, to delete data, we use DELETE. This removes a row from a table. If we need to remove Antonio Moreno's record, we would write:

DELETE FROM Customers
WHERE CustomerID = 3;

Again, the WHERE clause is essential to make sure you only delete the intended record.

These four commands are the foundation for almost everything you do with a relational database.

Quiz Questions 1/6

What is the primary purpose of a database?

Quiz Questions 2/6

In a relational database table called Products, what does a single row represent?

SQL Database Administration Essentials