For an instructor lead, in-depth look at learning SQL click below.
Managing School Timetable can be a cumbersome task, and SQL can make this effortless. SQL (Structured Query Language) is a standard language for storing, manipulating, and retrieving data in databases. We are going to design a School Timetable Management System using SQL, which will streamline the process.
Structuring The Database
First and foremost, we have to structure our database. This comprises creating relevant and necessary tables and relationships between these tables. In our case, we’ll have to create tables for Courses, Teachers, Students, Classrooms, and Timetable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
CREATE TABLE Teachers( TeacherID int PRIMARY KEY, FirstName varchar(255), LastName varchar(255) ); CREATE TABLE Courses( CourseID int PRIMARY KEY, CourseName varchar(255) ); CREATE TABLE Students( StudentID int PRIMARY KEY, FirstName varchar(255), LastName varchar(255) ); CREATE TABLE Classrooms( ClassroomID int PRIMARY KEY, ClassroomName varchar(255) ); CREATE TABLE Timetable( ScheduleID int PRIMARY KEY, ClassroomID int FOREIGN KEY REFERENCES Classrooms(ClassroomID), CourseID int FOREIGN KEY REFERENCES Courses(CourseID), TeacherID int FOREIGN KEY REFERENCES Teachers(TeacherID), Start_Time time, End_Time time, Day_Of_Week varchar(255) ); |
Inserting Data Into The Database
After creating our tables, the next logical step would be to populate them with relevant data. Here is how you can insert data into these tables using SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
INSERT INTO Teachers(TeacherID, FirstName, LastName) VALUES (1, 'John', 'Doe'); INSERT INTO Courses(CourseID, CourseName) VALUES (1, 'Mathematics'); INSERT INTO Students(StudentID, FirstName, LastName) VALUES (1, 'Jane', 'Smith'); INSERT INTO Classrooms(ClassroomID, ClassroomName) VALUES (1, '101'); INSERT INTO Timetable(ScheduleID, ClassroomID, CourseID, TeacherID, Start_Time, End_Time, Day_Of_Week) VALUES (1, 1, 1, 1, '09:00:00', '10:00:00', 'Monday'); |
Querying The Database
Below are some examples of how to query and retrieve data from these tables.
Finding the timetable for a particular classroom:
|
1 2 3 4 5 6 |
SELECT * FROM Timetable INNER JOIN Classrooms ON Timetable.ClassroomID=Classrooms.ClassroomID WHERE ClassroomName='101'; |
Listing all teachers teaching a particular course:
|
1 2 3 4 5 6 |
SELECT FirstName,LastName FROM Teachers INNER JOIN Timetable ON Teachers.TeacherID=Timetable.TeacherID WHERE CourseID=(SELECT CourseID from Courses WHERE CourseName='Mathematics'); |
In Conclusion
SQL is a powerful tool that can be used to manage and automate a school’s timetable system and many other data handling tasks. With a well-structured database and detailed data entries, the possibilities of SQL are endless.