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:
- Visit the MySQL Downloads page.
- Download the MySQL Installer.
- Follow the installation wizard to install MySQL Server.
macOS:
- Install Homebrew (if not already installed) by running
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
. - Install MySQL using Homebrew by running
brew install mysql
.
Linux (Ubuntu):
- Open a terminal and run
sudo apt update
. - Install MySQL Server by running
sudo apt install mysql-server
.
1.2.2 Configuration
- During the installation, set a secure root password.
- 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:
- Create a MySQL database named “company” to store employee information.
- 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:
StudentID | FirstName | LastName | Age |
---|---|---|---|
1 | John | Doe | 20 |
2 | Jane | Smith | 22 |
3 | Alex | Johnson | 21 |
Courses Table:
CourseID | CourseName | Instructor |
---|---|---|
101 | Math 101 | Prof. Brown |
102 | English 201 | Prof. Williams |
103 | Chemistry 301 | Prof. 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:
EnrollmentID | StudentID | CourseID | Grade |
---|---|---|---|
1 | 1 | 101 | A |
2 | 1 | 102 | B |
3 | 2 | 101 | A |
4 | 3 | 103 | B+ |
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
andCourseID
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:
- Create a MySQL database named “library” to store information about books and authors.
- Define two tables:
authors
andbooks
. - Establish a relationship between the
authors
andbooks
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:
- 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:
- What is the primary key in the
authors
table? - What is the foreign key in the
books
table? - How many books are in the library database?
Tasks for Learners:
- Retrieve the list of authors from the
authors
table. - Retrieve the title and publication year of each book along with the author’s name.
Answers:
- What is the primary key in the
authors
table?- The primary key in the
authors
table isauthor_id
.
- The primary key in the
- What is the foreign key in the
books
table?- The foreign key in the
books
table isauthor_id
, which references theauthor_id
in theauthors
table.
- The foreign key in the
- 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.