School Management System Database

Welcome to the School Management System Database repository! This repository contains the SQL scripts and database schema for implementing the database component of a school management system. The database is designed to store and manage various data related to students, teachers, courses, subjects, countries, and other aspects of school operations.

Database Schema

Figure 1 SMS Database Diagram

The database schema consists of the following tables:

• Students: Stores information about students, including their unique IDs, names, contact details, and other relevant details.
• Teachers: Contains information about teachers, such as their IDs, names, contact information, and other related data.
• Courses: Stores details about the courses offered by the school, including course IDs, names, descriptions, and other pertinent information.
• Subjects: Stores details about the individual subjects offered by the courses in school, including course IDs, names, descriptions, and other pertinent information.
• Countries: Stores the countries of students, along with other relevant data.
• Users: Stores information about Users of the website or app, including their unique IDs, names, contact details, and other relevant details.

And tables that connect two or more tables: StudentsCourses, CoursesSubjects, TeacherCourses.

Setup

To set up the School Management System Database on your MS SQL Server, follow these steps:

1. Connect to your MS SQL Server using your preferred client or SQL Server Management Studio (SSMS).
2. Create a new database using the following SQL commands:

CREATE DATABASE SchoolManagementSystem;

CREATE TABLE [dbo].[Countries](
	[CountriesID] [int] IDENTITY(1,1) NOT NULL,
	[ISO] [nvarchar](10) NOT NULL,
	[CountryName] [nvarchar](100) NOT NULL,
	[PhoneCode] [int] NOT NULL,
	[ISO3] [nchar](10) NOT NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED 
(
	[CountriesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[CouresSubjects]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CouresSubjects](
	[CoursesSubjectsID] [int] IDENTITY(1,1) NOT NULL,
	[CoursesID] [int] NOT NULL,
	[SubjectsID] [int] NOT NULL,
 CONSTRAINT [PK_CouresSubjects] PRIMARY KEY CLUSTERED 
(
	[CoursesSubjectsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Courses]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Courses](
	[CoursesID] [int] IDENTITY(1,1) NOT NULL,
	[CoursesName] [nvarchar](50) NOT NULL,
	[CoursesDescription] [nvarchar](500) NOT NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED 
(
	[CoursesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Students]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Students](
	[StudentsID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](20) NOT NULL,
	[LastName] [nvarchar](20) NULL,
	[Email] [nvarchar](50) NULL,
	[Gender] [nchar](10) NULL,
	[DateOfBirth] [datetime] NULL,
	[Adress] [nvarchar](100) NULL,
	[CountriesID] [int] NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
	[StudentsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StudentsCourses]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentsCourses](
	[StudentsCoursesID] [int] IDENTITY(1,1) NOT NULL,
	[StudentsID] [int] NOT NULL,
	[CoursesID] [int] NOT NULL,
	[Passed] [bit] NULL,
	[StartDate] [datetime] NOT NULL,
 CONSTRAINT [PK_StudentsCourses] PRIMARY KEY CLUSTERED 
(
	[StudentsCoursesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Subjects]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Subjects](
	[SubjectID] [int] IDENTITY(1,1) NOT NULL,
	[SubjectName] [nvarchar](50) NOT NULL,
	[SubjectDescription] [nvarchar](500) NOT NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Subjects] PRIMARY KEY CLUSTERED 
(
	[SubjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Teachers]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Teachers](
	[TeachersID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](20) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[Email] [nvarchar](20) NOT NULL,
	[Gender] [nvarchar](10) NOT NULL,
	[DateOfBirth] [datetime] NOT NULL,
	[Adress] [nvarchar](100) NOT NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Teachers] PRIMARY KEY CLUSTERED 
(
	[TeachersID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TeachersCourses]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TeachersCourses](
	[TeachersCoursesID] [int] IDENTITY(1,1) NOT NULL,
	[TeachersID] [int] NOT NULL,
	[CoursesID] [int] NOT NULL,
 CONSTRAINT [PK_TeachersCourses] PRIMARY KEY CLUSTERED 
(
	[TeachersCoursesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
	[UsersID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[UserPassword] [nvarchar](50) NOT NULL,
	[Administrator] [bit] NULL,
	[UserEmail] [nvarchar](50) NOT NULL,
	[Deleted] [bit] NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UsersID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Countries] ADD  CONSTRAINT [DF_Countries_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO
ALTER TABLE [dbo].[Courses] ADD  CONSTRAINT [DF_Courses_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO
ALTER TABLE [dbo].[Students] ADD  CONSTRAINT [DF_Students_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO
ALTER TABLE [dbo].[Subjects] ADD  CONSTRAINT [DF_Subjects_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO
ALTER TABLE [dbo].[Teachers] ADD  CONSTRAINT [DF_Teachers_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO
ALTER TABLE [dbo].[CouresSubjects]  WITH CHECK ADD  CONSTRAINT [FK_CouresSubjects_Courses] FOREIGN KEY([CoursesID])
REFERENCES [dbo].[Courses] ([CoursesID])
GO
ALTER TABLE [dbo].[CouresSubjects] CHECK CONSTRAINT [FK_CouresSubjects_Courses]
GO
ALTER TABLE [dbo].[CouresSubjects]  WITH CHECK ADD  CONSTRAINT [FK_CouresSubjects_Subjects] FOREIGN KEY([SubjectsID])
REFERENCES [dbo].[Subjects] ([SubjectID])
GO
ALTER TABLE [dbo].[CouresSubjects] CHECK CONSTRAINT [FK_CouresSubjects_Subjects]
GO
ALTER TABLE [dbo].[Students]  WITH CHECK ADD  CONSTRAINT [FK_Students_Countries] FOREIGN KEY([CountriesID])
REFERENCES [dbo].[Countries] ([CountriesID])
GO
ALTER TABLE [dbo].[Students] CHECK CONSTRAINT [FK_Students_Countries]
GO
ALTER TABLE [dbo].[StudentsCourses]  WITH CHECK ADD  CONSTRAINT [FK_StudentsCourses_Courses] FOREIGN KEY([CoursesID])
REFERENCES [dbo].[Courses] ([CoursesID])
GO
ALTER TABLE [dbo].[StudentsCourses] CHECK CONSTRAINT [FK_StudentsCourses_Courses]
GO
ALTER TABLE [dbo].[StudentsCourses]  WITH CHECK ADD  CONSTRAINT [FK_StudentsCourses_Students] FOREIGN KEY([StudentsID])
REFERENCES [dbo].[Students] ([StudentsID])
GO
ALTER TABLE [dbo].[StudentsCourses] CHECK CONSTRAINT [FK_StudentsCourses_Students]
GO
ALTER TABLE [dbo].[TeachersCourses]  WITH CHECK ADD  CONSTRAINT [FK_TeachersCourses_Courses] FOREIGN KEY([CoursesID])
REFERENCES [dbo].[Courses] ([CoursesID])
GO
ALTER TABLE [dbo].[TeachersCourses] CHECK CONSTRAINT [FK_TeachersCourses_Courses]
GO
ALTER TABLE [dbo].[TeachersCourses]  WITH CHECK ADD  CONSTRAINT [FK_TeachersCourses_Teachers1] FOREIGN KEY([TeachersID])
REFERENCES [dbo].[Teachers] ([TeachersID])
GO
ALTER TABLE [dbo].[TeachersCourses] CHECK CONSTRAINT [FK_TeachersCourses_Teachers1]
GO
/****** Object:  StoredProcedure [dbo].[AddCourseSubject]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AddCourseSubject] (@CoursesID int,@SubjectsID int)
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO CouresSubjects(CoursesID,SubjectsID) 
	VALUES(@CoursesID,@SubjectsID)
END
GO
/****** Object:  StoredProcedure [dbo].[AddStudentCourse]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE  [dbo].[AddStudentCourse] (@StudentId int,@CourseId int,@Startdate datetime)
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO StudentsCourses(StudentsID,CoursesID,StartDate)
	VALUES(@StudentId,@CourseId,@Startdate)
END
GO
/****** Object:  StoredProcedure [dbo].[AddTeacherCourses]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE  [dbo].[AddTeacherCourses](@TeachersID int,@CoursesID int)
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	insert into TeachersCourses(TeachersID,CoursesID) values(@TeachersID,@CoursesID)
END
GO
/****** Object:  StoredProcedure [dbo].[DeleteCountry]    Script Date: 29.1.2022. 01:47:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Boris	>
--