Relational database concepts in MySQL

Introduction

A relational database helps you store and organize data in tables that connect to each other. This makes it easy to find and use information quickly.

You want to keep track of customers and their orders in a store.

You need to manage employee details and their departments in a company.

You want to store book information and authors in a library system.

You need to organize student records and their classes in a school.

You want to keep product details and sales data for a business.

Syntax

MySQL

CREATE TABLE table_name (
  column1 datatype PRIMARY KEY,
  column2 datatype,
  column3 datatype,
  FOREIGN KEY (column_name) REFERENCES other_table(column_name)
);

Tables store data in rows and columns, like a spreadsheet.

Primary keys uniquely identify each row in a table.

Foreign keys link one table to another to show relationships.

Examples

This creates a table named Customers with a unique ID, name, and email.

MySQL

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name VARCHAR(100),
  Email VARCHAR(100)
);

This creates an Orders table linked to Customers by CustomerID.

MySQL

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  OrderDate DATE,
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Sample Program

This example creates three tables: Students, Classes, and Enrollments. It links students to classes they take. The final query shows which student is in which class.

MySQL

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  StudentName VARCHAR(50),
  Age INT
);

CREATE TABLE Classes (
  ClassID INT PRIMARY KEY,
  ClassName VARCHAR(50)
);

CREATE TABLE Enrollments (
  EnrollmentID INT PRIMARY KEY,
  StudentID INT,
  ClassID INT,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
  FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
);

INSERT INTO Students VALUES (1, 'Alice', 20), (2, 'Bob', 22);
INSERT INTO Classes VALUES (101, 'Math'), (102, 'History');
INSERT INTO Enrollments VALUES (1001, 1, 101), (1002, 2, 102);

SELECT Students.StudentName, Classes.ClassName
FROM Enrollments
JOIN Students ON Enrollments.StudentID = Students.StudentID
JOIN Classes ON Enrollments.ClassID = Classes.ClassID;

Important Notes

Relational databases use keys to connect data across tables.

Using JOIN lets you combine data from multiple tables easily.

Always define primary keys to keep data unique and organized.

Summary

Relational databases organize data in tables with rows and columns.

Primary keys uniquely identify each record.

Foreign keys create links between tables to show relationships.