Database Management Systems Fundamentals

TL;DR

You'll learn about what a DBMS is, why we use it, and how to organize your data. We'll cover key concepts like data models, SQL basics, and ensuring your data is reliable. Understanding these fundamentals is crucial for any data-driven application you'll build.

1. The Mental Model

Think of a DBMS as a super-organized digital filing cabinet for your important information. It doesn't just store files; it manages them, protects them, and helps you find exactly what you need quickly, preventing chaos.

2. The Core Material

A Database Management System (DBMS) is software that interacts with an end-user, applications, and the database itself to capture and analyze data. It allows you to define, create, query, update, and administer a database.

Why Use a DBMS?

Photo by Ann H on Pexels

Using a DBMS offers several advantages over simple file systems:

• Data Redundancy Control: Avoids storing the same data multiple times, saving space and preventing inconsistencies.
• Data Consistency: Ensures data is accurate and reliable across the database.
• Data Sharing: Multiple users can access the same data concurrently.
• Data Security: Provides mechanisms to protect sensitive information from unauthorized access.
• Data Integrity: Enforces rules to maintain data quality.
• Backup and Recovery: Allows for recovery of data in case of system failures.

Database Models

Photo by Markus Winkler on Pexels

Database models define the logical structure of a database, explaining how data is stored, organized, and manipulated. The most common model you'll encounter is the Relational Model.

• Relational Model: Data is organized into tables (relations), which consist of rows (records/tuples) and columns (attributes/fields). Each table has a unique name, and relationships between tables are established using common columns.

graph TD
    A["User interacts (Application)"] --> B["DBMS (Software)"];
    B --> C["Database (Stored Data)"];
    C --> B;
    B --> A;
    subgraph Components
        D["Data Definition Language (DDL)"] --> B;
        E["Data Manipulation Language (DML)"] --> B;
        F["Data Control Language (DCL)"] --> B;
    end
    D -- "Create, Alter, Drop schemas" --> C;
    E -- "Insert, Update, Delete, Select data" --> C;
    F -- "Grant, Revoke permissions" --> C;

Key Concepts

Photo by Zulfugar Karimov on Pexels

• Schema: The logical design of the database (its structure), defining tables, columns, and relationships.
• Instance: The actual data stored in the database at a particular moment.
• Primary Key (PK): A column (or set of columns) that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.
• Foreign Key (FK): A column (or set of columns) in one table that refers to the Primary Key in another table, establishing a link or relationship between them.
• Normalization: A process of organizing columns and tables in a relational database to minimize data redundancy and improve data integrity. You'll learn about different Normal Forms (1NF, 2NF, 3NF, BCNF).
• SQL (Structured Query Language): The standard language used to communicate with and manipulate relational databases.

SQL Basics (Data Definition Language - DDL)

Photo by Myburgh Roux on Pexels

You'll use DDL commands to define and manage database structures.

-- Create a new database
CREATE DATABASE YourUniversityDB;

-- Use the new database
USE YourUniversityDB;

-- Create a table named 'Students'
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATE,
    Email VARCHAR(100) UNIQUE
);

-- Create another table named 'Courses'
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100),
    Credits INT
);

-- Alter a table to add a new column
ALTER TABLE Students ADD COLUMN Major VARCHAR(75);

-- Drop a table
-- DROP TABLE Courses;

3. Worked Example

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

First, you'd define the structure:

-- Create the Library database
CREATE DATABASE LibraryDB;
USE LibraryDB;

-- Create the Books table
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    ISBN VARCHAR(13) UNIQUE NOT NULL,
    PublicationYear INT
);

-- Create the Borrowers table
CREATE TABLE Borrowers (
    BorrowerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

-- Create a table to track loans, linking Books and Borrowers
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY AUTO_INCREMENT, -- AUTO_INCREMENT generates unique IDs
    BookID INT,
    BorrowerID INT,
    LoanDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Books(BookID),
    FOREIGN KEY (BorrowerID) REFERENCES Borrowers(BorrowerID)
);

Here, BookID is the Primary Key in Books, BorrowerID is the Primary Key in Borrowers. In the Loans table, BookID and BorrowerID are Foreign Keys, creating relationships between Loans, Books, and Borrowers. This structure ensures that a loan can only refer to existing books and borrowers.

Now, let's add some data (Data Manipulation Language - DML):

-- Insert data into Books
INSERT INTO Books (BookID, Title, Author, ISBN, PublicationYear) VALUES
(101, 'The Great Gatsby', 'F. Scott Fitzgerald', '9780743273565', 1925),
(102, '1984', 'George Orwell', '9780451524935', 1949);

-- Insert data into Borrowers
INSERT INTO Borrowers (BorrowerID, FirstName, LastName, Email) VALUES
(1, 'Alice', 'Smith', 'alice.smith@example.com'),
(2, 'Bob', 'Johnson', 'bob.johnson@example.com');

-- Record a loan
INSERT INTO Loans (BookID, BorrowerID, LoanDate) VALUES
(101, 1, '2023-10-26');

-- Alice returns the book
UPDATE Loans SET ReturnDate = '2023-11-10' WHERE LoanID = 1;

-- Find all books borrowed by Alice
SELECT B.Title, B.Author, L.LoanDate, L.ReturnDate
FROM Books B
JOIN Loans L ON B.BookID = L.BookID
JOIN Borrowers Bo ON L.BorrowerID = Bo.BorrowerID
WHERE Bo.FirstName = 'Alice';

4. Key Takeaways

• A DBMS is software that manages, stores, and organizes data efficiently and securely.
• The relational model organizes data into tables with rows and columns, linked by common values.
• Primary Keys uniquely identify records, while Foreign Keys link related tables.
• SQL is the standard language for interacting with relational databases.
• DDL (Data Definition Language) commands like CREATE, ALTER, DROP define database structures.

• DML (Data Manipulation Language) commands like INSERT, UPDATE, DELETE, SELECT handle data within tables.

• Common Mistakes:

  • Not defining Primary Keys, making it hard to uniquely identify records.
  • Ignoring Foreign Keys, leading to inconsistent or incorrect data relationships.
  • Forgetting the USE statement, leading to operations on the wrong database.
  • Not understanding the difference between DDL and DML commands.

5. Now Try It

Spend 15 minutes. Design a simple database schema for an online store. Think about what tables you would need (e.g., Customers, Products, Orders), what columns each table would have, and which columns would act as Primary Keys and Foreign Keys to link them. Write down the CREATE TABLE statements for these tables.

Success looks like: You have at least three CREATE TABLE statements, with appropriate columns, PRIMARY KEY definitions, and at least one FOREIGN KEY linking two of your tables.