Creating a Simple Database
Creating a database management system (DBMS) with three tables can be demonstrated with a
simple example of a Student Management System.
The three tables can be:
1. Students – Stores information about students.
2. Courses – Stores details about the courses.
3. Enrollments – Tracks which students are enrolled in which courses (a junction table that
connects Students and Courses).
Students
1
Creating a Simple Database
Steps to Create the DBMS with Three Tables
Step 1: Create the Database
CREATE DATABASE StudentManagement;
USE StudentManagement;
Step 2: Create the Students Table
CREATE TABLE Students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
birthdate DATE,
gender ENUM('Male', 'Female') NOT NULL,
email VARCHAR(100) UNIQUE
);
Step 3: Create the Courses Table
CREATE TABLE Courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
2
Creating a Simple Database
course_name VARCHAR(100) NOT NULL,
course_code VARCHAR(10) NOT NULL UNIQUE,
credit_hours INT NOT NULL
);
Step 4: Create the Enrollments Table
This table serves as a junction table to establish the many-to-many relationship between
Students and Courses.
CREATE TABLE Enrollments (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_id INT,
enrollment_date DATE,
FOREIGN KEY (student_id) REFERENCES Students(student_id)
ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
ON DELETE CASCADE
);
Sample Data Insertion
Step 5: Insert Data into Students Table
INSERT INTO Students (first_name, last_name, birthdate, gender, email) VALUES
('John', 'Doe', '2000-01-01', 'Male', 'john.doe@example.com'),
('Jane', 'Smith', '1999-05-21', 'Female', 'jane.smith@example.com'),
('Alice', 'Brown', '2001-03-15', 'Female', 'alice.brown@example.com');
Step 6: Insert Data into Courses Table
INSERT INTO Courses (course_name, course_code, credit_hours) VALUES
('Database Systems', 'DB101', 3),
('Operating Systems', 'OS102', 4),
('Data Structures', 'DS103', 3);
Step 7: Insert Data into Enrollments Table
INSERT INTO Enrollments (student_id, course_id, enrollment_date) VALUES
(1, 1, '2024-09-20'),
(2, 2, '2024-09-22'),
(1, 3, '2024-09-23'),
(3, 1, '2024-09-24');
Query Examples
1. List All Students
SELECT * FROM Students;
3
Creating a Simple Database
2. List All Courses
SELECT * FROM Courses;
3. List Students Enrolled in Each Course
SELECT s.first_name, s.last_name, c.course_name
FROM Enrollments e
JOIN Students s ON e.student_id = s.student_id
JOIN Courses c ON e.course_id = c.course_id;
4. Find the Courses a Particular Student Is Enrolled In
SELECT c.course_name, c.course_code
FROM Enrollments e
JOIN Courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;
Schema Overview
Students: Holds information about each student.
Courses: Contains the details of the courses offered.
Enrollments: Links students and courses, showing which students are enrolled in which
courses.
