How to create University Course & Result Management System in SQL Database :
USE [master]
GO
/****** Object: Database [UniDb] Script Date: 2/14/2016 1:51:59 AM ******/
CREATE DATABASE [UniDb]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'UniDb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\UniDb.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'UniDb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\UniDb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [UniDb] SET COMPATIBILITY_LEVEL = 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [UniDb].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [UniDb] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [UniDb] SET ANSI_NULLS OFF
GO
ALTER DATABASE [UniDb] SET ANSI_PADDING OFF
GO
ALTER DATABASE [UniDb] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [UniDb] SET ARITHABORT OFF
GO
ALTER DATABASE [UniDb] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [UniDb] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [UniDb] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [UniDb] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [UniDb] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [UniDb] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [UniDb] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [UniDb] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [UniDb] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [UniDb] SET DISABLE_BROKER
GO
ALTER DATABASE [UniDb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [UniDb] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [UniDb] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [UniDb] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [UniDb] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [UniDb] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [UniDb] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [UniDb] SET RECOVERY SIMPLE
GO
ALTER DATABASE [UniDb] SET MULTI_USER
GO
ALTER DATABASE [UniDb] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [UniDb] SET DB_CHAINING OFF
GO
ALTER DATABASE [UniDb] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [UniDb] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [UniDb] SET DELAYED_DURABILITY = DISABLED
GO
USE [UniDb]
GO
/****** Object: Table [dbo].[AllocationRoom] Script Date: 2/14/2016 1:51:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AllocationRoom](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentId] [int] NULL,
[CourseId] [int] NULL,
[DayId] [int] NULL,
[RoomId] [int] NULL,
[StartTime] [time](7) NULL,
[EndTime] [time](7) NULL,
CONSTRAINT [PK_AllocationRoom] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Course] Script Date: 2/14/2016 1:51:59 AM ******/
SET ANSI_NULLS ON
/****** Object: Table [dbo].[Day] Script Date: 2/14/2016 1:51:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Day](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DayName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Day] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Department] Script Date: 2/14/2016 1:51:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Department](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[GradeTable] Script Date: 2/14/2016 1:51:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GradeTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Grade] [varchar](50) NULL,
CONSTRAINT [PK_GradeTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[ResultTable] Script Date: 2/14/2016 1:51:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ResultTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StudentId] [int] NOT NULL,
[CourseId] [int] NOT NULL,
[Grade] [varchar](50) NOT NULL,
CONSTRAINT [PK_ResultTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Room] Script Date: 2/14/2016 1:51:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Room](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RoomName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Student] Script Date: 2/14/2016 1:51:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StudentName] [varchar](50) NOT NULL,
[StudentEmail] [varchar](50) NOT NULL,
[StudentContactNo] [varchar](50) NOT NULL,
[Date] [date] NOT NULL,
[StudentAddress] [varchar](max) NOT NULL,
[DepartmentId] [int] NOT NULL,
[RegNo] [varchar](50) NOT NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[StudentCourse] Script Date: 2/14/2016 1:51:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentCourse](
[StudentId] [int] NOT NULL,
[CourseId] [int] NOT NULL,
[Date] [date] NOT NULL,
CONSTRAINT [PK_StudentCourse] PRIMARY KEY CLUSTERED
(
[StudentId] ASC,
[CourseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[AllocationRoom] ON
INSERT [dbo].[AllocationRoom] ([Id], [DepartmentId], [CourseId], [DayId], [RoomId], [StartTime], [EndTime]) VALUES (3, 6, 5, 1004, 3, CAST(N'08:00:00' AS Time), CAST(N'09:00:00' AS Time))
INSERT [dbo].[AllocationRoom] ([Id], [DepartmentId], [CourseId], [DayId], [RoomId], [StartTime], [EndTime]) VALUES (4, 7, 8, 1006, 5, CAST(N'10:00:00' AS Time), CAST(N'11:00:00' AS Time))
SET IDENTITY_INSERT [dbo].[AllocationRoom] OFF
SET IDENTITY_INSERT [dbo].[Course] ON
INSERT [dbo].[Course] ([Id], [CourseCode], [CourseName], [DepartmentId]) VALUES (5, N'CSE-101', N'computer Programming', 6)
INSERT [dbo].[Course] ([Id], [CourseCode], [CourseName], [DepartmentId]) VALUES (6, N'CSE-102', N'Fundamentals', 6)
INSERT [dbo].[Course] ([Id], [CourseCode], [CourseName], [DepartmentId]) VALUES (7, N'EEE-101', N'Electronic Devices', 7)
INSERT [dbo].[Course] ([Id], [CourseCode], [CourseName], [DepartmentId]) VALUES (8, N'EEE-102', N'Electric Circuits', 7)
SET IDENTITY_INSERT [dbo].[Course] OFF
SET IDENTITY_INSERT [dbo].[Day] ON
INSERT [dbo].[Day] ([Id], [DayName]) VALUES (1004, N'Saturday')
INSERT [dbo].[Day] ([Id], [DayName]) VALUES (1005, N'Sunday')
INSERT [dbo].[Day] ([Id], [DayName]) VALUES (1006, N'Monday')
INSERT [dbo].[Day] ([Id], [DayName]) VALUES (1007, N'Tuesday')
INSERT [dbo].[Day] ([Id], [DayName]) VALUES (1008, N'Wednesday')
INSERT [dbo].[Day] ([Id], [DayName]) VALUES (1009, N'Thusday')
INSERT [dbo].[Day] ([Id], [DayName]) VALUES (1010, N'Friday')
SET IDENTITY_INSERT [dbo].[Day] OFF
SET IDENTITY_INSERT [dbo].[Department] ON
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (6, N'CSE')
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (7, N'EEE')
SET IDENTITY_INSERT [dbo].[Department] OFF
SET IDENTITY_INSERT [dbo].[GradeTable] ON
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (1, N'A+')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (2, N'A')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (3, N'A-')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (4, N'B+')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (5, N'B')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (6, N'B-')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (7, N'C+')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (8, N'C')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (9, N'C-')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (10, N'D+')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (11, N'D')
INSERT [dbo].[GradeTable] ([Id], [Grade]) VALUES (12, N'D-')
SET IDENTITY_INSERT [dbo].[GradeTable] OFF
SET IDENTITY_INSERT [dbo].[ResultTable] ON
INSERT [dbo].[ResultTable] ([Id], [StudentId], [CourseId], [Grade]) VALUES (1, 1, 6, N'A')
INSERT [dbo].[ResultTable] ([Id], [StudentId], [CourseId], [Grade]) VALUES (2, 1, 5, N'A-')
INSERT [dbo].[ResultTable] ([Id], [StudentId], [CourseId], [Grade]) VALUES (3, 2, 7, N'B+')
INSERT [dbo].[ResultTable] ([Id], [StudentId], [CourseId], [Grade]) VALUES (4, 2, 8, N'A+')
SET IDENTITY_INSERT [dbo].[ResultTable] OFF
SET IDENTITY_INSERT [dbo].[Room] ON
INSERT [dbo].[Room] ([Id], [RoomName]) VALUES (3, N'R-101')
INSERT [dbo].[Room] ([Id], [RoomName]) VALUES (4, N'R-102')
INSERT [dbo].[Room] ([Id], [RoomName]) VALUES (5, N'R-103')
SET IDENTITY_INSERT [dbo].[Room] OFF
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT [dbo].[Student] ([Id], [StudentName], [StudentEmail], [StudentContactNo], [Date], [StudentAddress], [DepartmentId], [RegNo]) VALUES (1, N'Abul', N'abul@gmail.com', N'123456', CAST(N'2016-02-10' AS Date), N'dhaka', 6, N'CSE-2016-001')
INSERT [dbo].[Student] ([Id], [StudentName], [StudentEmail], [StudentContactNo], [Date], [StudentAddress], [DepartmentId], [RegNo]) VALUES (2, N'Babul', N'babul@gmail.com', N'987655', CAST(N'2016-01-30' AS Date), N'sylhet', 7, N'EEE-2016-001')
SET IDENTITY_INSERT [dbo].[Student] OFF
INSERT [dbo].[StudentCourse] ([StudentId], [CourseId], [Date]) VALUES (1, 5, CAST(N'2016-02-10' AS Date))
INSERT [dbo].[StudentCourse] ([StudentId], [CourseId], [Date]) VALUES (1, 6, CAST(N'2016-02-09' AS Date))
INSERT [dbo].[StudentCourse] ([StudentId], [CourseId], [Date]) VALUES (2, 7, CAST(N'2016-02-11' AS Date))
INSERT [dbo].[StudentCourse] ([StudentId], [CourseId], [Date]) VALUES (2, 8, CAST(N'2016-02-22' AS Date))
/****** Object: Index [IX_AllocationRoom] Script Date: 2/14/2016 1:51:59 AM ******/
CREATE NONCLUSTERED INDEX [IX_AllocationRoom] ON [dbo].[AllocationRoom]
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [master]
GO
ALTER DATABASE [UniDb] SET READ_WRITE
GO