Skip to content

Lesson 1: Introduction to MySQL

Basics of relational databases

Setting up a MySQL database and tables

Objective:

The objective of this lesson is to provide learners with an introduction to MySQL, covering the basics of relational databases and guiding them through the process of setting up a MySQL database and tables. By the end of this lesson, participants should be able to:

  • Understand the fundamentals of relational databases.
  • Install and set up MySQL on their local environment.
  • Create a MySQL database and define tables.

Description:

1.1 Basics of Relational Databases

1.1.1 Introduction

Relational databases play a crucial role in organizing and managing data. They are structured collections of data organized in tables, where each table represents an entity, and each row in the table represents an instance of that entity.

1.1.2 Key Terms

  • Database: A structured collection of data.
  • Table: A collection of data organized in rows and columns.
  • Field/Column: A single piece of data in a table.
  • Primary Key: A unique identifier for each record in a table.
  • Foreign Key: A field that refers to the primary key in another table, establishing a link between the two tables.

1.2 Installing and Setting Up MySQL

1.2.1 Download and Installation

Windows:

  1. Visit the MySQL Downloads page.
  2. Download the MySQL Installer.
  3. Follow the installation wizard to install MySQL Server.

macOS:

  1. Install Homebrew (if not already installed) by running /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)".
  2. Install MySQL using Homebrew by running brew install mysql.

Linux (Ubuntu):

  1. Open a terminal and run sudo apt update.
  2. Install MySQL Server by running sudo apt install mysql-server.

1.2.2 Configuration

  1. During the installation, set a secure root password.
  2. Configure other settings based on your preferences.

1.3 Creating a Database and Tables

1.3.1 MySQL Command-Line

Creating a Database:

CREATE DATABASE mydatabase;

Using the Database:

USE mydatabase;

Creating a Table:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255)
);

QL Workbench (Optional)

MySQL Workbench is a graphical tool for managing MySQL databases. It provides a user-friendly interface for creating databases, tables, and executing queries.

1.4 Practice Exercise

Task:

  1. Create a MySQL database named “company” to store employee information.
  2. Define a table named “employees” with fields for employee ID, name, and department.

SQL Statements:

-- Create the database
CREATE DATABASE company;

-- Use the database
USE company;

-- Create the employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department VARCHAR(255)
);

his practice exercise allows learners to apply the concepts learned in the lesson by creating a MySQL database and defining a table.

Conclusion:

In this lesson, we covered the basics of relational databases, installed and configured MySQL, and learned how to create databases and tables.

Let’s provide examples of relational databases, primary keys, and foreign keys.

Example 1: Relational Databases

Consider a scenario where we want to store information about students and their courses. We can create two tables: one for students and one for courses. The relationship between the two tables is established using a common field, such as the student ID.

Students Table:

StudentIDFirstNameLastNameAge
1JohnDoe20
2JaneSmith22
3AlexJohnson21

Courses Table:

CourseIDCourseNameInstructor
101Math 101Prof. Brown
102English 201Prof. Williams
103Chemistry 301Prof. Davis

In this example, the StudentID in the Students table serves as a primary key, and it is used as a foreign key in the Courses table to establish a relationship between the two tables.

Example 2: Primary Key and Foreign Key

Let’s extend the previous example and add a third table for student enrollments. This table will link students to the courses they are enrolled in.

Enrollments Table:

EnrollmentIDStudentIDCourseIDGrade
11101A
21102B
32101A
43103B+

In this example:

  • StudentID in the Students table is the primary key.
  • CourseID in the Courses table is the primary key.
  • In the Enrollments table, both StudentID and CourseID are foreign keys, establishing relationships with the primary keys in the Students and Courses tables.

This way, we can associate students with courses and track their grades in the Enrollments table.

These examples showcase the concept of relational databases, where tables are related through keys, and the use of primary and foreign keys to establish relationships between tables.

Let’s create a practice exercise that involves creating a simple relational database with primary and foreign keys. In this exercise, we’ll design tables for a library database that stores information about books and authors. The tables will have relationships using primary and foreign keys.

Practice Exercise: Library Database

Task:

  1. Create a MySQL database named “library” to store information about books and authors.
  2. Define two tables: authors and books.
  3. Establish a relationship between the authors and books tables using primary and foreign keys.

SQL Statements:

-- Create the library database
CREATE DATABASE library;

-- Use the library database
USE library;

-- Create the authors table
CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(255) NOT NULL
);

-- Create the books table
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    publication_year INT,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

Instructions:

  1. Insert data into the authors table:
-- Insert data into the authors table
INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'George Orwell'),
(3, 'J.K. Rowling');

Insert data into the books table:

-- Insert data into the books table
INSERT INTO books (book_id, title, publication_year, author_id) VALUES
(101, 'Pride and Prejudice', 1813, 1),
(102, '1984', 1949, 2),
(103, 'Harry Potter and the Philosopher''s Stone', 1997, 3);

Questions:

  1. What is the primary key in the authors table?
  2. What is the foreign key in the books table?
  3. How many books are in the library database?

Tasks for Learners:

  1. Retrieve the list of authors from the authors table.
  2. Retrieve the title and publication year of each book along with the author’s name.

Answers:

  1. What is the primary key in the authors table?
    • The primary key in the authors table is author_id.
  2. What is the foreign key in the books table?
    • The foreign key in the books table is author_id, which references the author_id in the authors table.
  3. How many books are in the library database?
    • There are three books in the library database.

Tasks for Learners:

Task 1: Retrieve the list of authors from the authors table.

-- Retrieve the list of authors
SELECT * FROM authors;

Task 2: Retrieve the title and publication year of each book along with the author’s name.

-- Retrieve book information with author names
SELECT books.title, books.publication_year, authors.author_name
FROM books
JOIN authors ON books.author_id = authors.author_id;

Results:

For Task 1:

+-----------+-----------------+
| author_id | author_name     |
+-----------+-----------------+
|         1 | Jane Austen     |
|         2 | George Orwell   |
|         3 | J.K. Rowling    |
+-----------+-----------------+

For Task 2:

+-----------------------------+---------------------+-----------------+
| title                       | publication_year    | author_name     |
+-----------------------------+---------------------+-----------------+
| Pride and Prejudice         | 1813                | Jane Austen     |
| 1984                        | 1949                | George Orwell   |
| Harry Potter and the Phil...| 1997                | J.K. Rowling    |
+-----------------------------+---------------------+-----------------+

These results demonstrate how to retrieve information from the tables using SQL queries and how the tables are related through primary and foreign keys.