Table Script
USE [TrainingManagement]
GO
CREATE TABLE [dbo].[Attendance](
[AttendanceId] [int] IDENTITY(1,1) NOT NULL,
[BatchId] [int] NULL,
[FacultyId] [int] NULL,
[Date] [date] NULL,
[HoursTaken] [int] NULL,
[CoveredModules] [varchar](200) NULL,
[Remarks] [varchar](200) NULL,
CONSTRAINT [PK_BatchSchedule] PRIMARY KEY CLUSTERED
(
[AttendanceId] 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].[Batch] Script Date: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Batch](
[BatchId] [int] IDENTITY(1,1) NOT NULL,
[CourseId] [int] NULL,
[BatchName] [varchar](40) NULL,
[StartDate] [date] NULL,
[TentativeEndDate] [date] NULL,
[BatchTiming] [varchar](100) NULL,
[Hours] [int] NULL,
[HoursTaken] [int] NULL,
[Fees] [int] NULL,
[FeesPaid] [int] NULL,
[Status] [varchar](15) NULL,
[Remarks] [varchar](300) NULL,
CONSTRAINT [PK__Batch__5D55CE58980177C0] PRIMARY KEY CLUSTERED
(
[BatchId] 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].[Course] Script Date: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Course](
[CourseId] [int] IDENTITY(1,1) NOT NULL,
[CourseName] [varchar](40) NULL,
[DurationInHours] [int] NULL,
[Fees] [int] NULL,
[Remarks] [varchar](200) NULL,
PRIMARY KEY CLUSTERED
(
[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 ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Faculty] Script Date: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Faculty](
[FacultyId] [int] IDENTITY(1,1) NOT NULL,
[FacultyName] [varchar](40) NULL,
[Specialization] [varchar](40) NULL,
[Phone] [varchar](15) NULL,
[Email] [varchar](40) NULL,
PRIMARY KEY CLUSTERED
(
[FacultyId] 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].[Fees] Script Date: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Fees](
[FeesId] [int] IDENTITY(1,1) NOT NULL,
[BatchId] [int] NULL,
[Date] [date] NULL,
[Amout] [numeric](8, 2) NULL,
[Description] [varchar](200) NULL,
CONSTRAINT [PK_Fees] PRIMARY KEY CLUSTERED
(
[FeesId] 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: 5/29/2017 8:23:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
[StudentId] [int] IDENTITY(1,1) NOT NULL,
[BatchId] [int] NULL,
[StudentName] [varchar](50) NULL,
[YearsOfExperience] [int] NULL,
[TechnologiesKnown] [varchar](100) NULL,
[Location] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[Email] [varchar](200) NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[StudentId] 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
SET IDENTITY_INSERT [dbo].[Attendance] ON
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (1, 1, 2, NULL, 2, N'JQuery Ajax', NULL)
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (2, 1, 1, NULL, 5, N'Angular Service', N'Given enough assignments for two weeks.')
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (3, 1, 1, NULL, 5, NULL, NULL)
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (4, 2, 1, NULL, 3, NULL, NULL)
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (5, 2, 1, NULL, 3, NULL, NULL)
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (6, 2, 1, CAST(0xB03C0B00 AS Date), 5, N'Entity Framework', N'Entity Framework Database First')
INSERT [dbo].[Attendance] ([AttendanceId], [BatchId], [FacultyId], [Date], [HoursTaken], [CoveredModules], [Remarks]) VALUES (7, 5, 1, NULL, 5, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Attendance] OFF
SET IDENTITY_INSERT [dbo].[Batch] ON
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (1, 2, N'Gemini Softwares ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (2, 2, N'.Net Morning', CAST(0x843C0B00 AS Date), CAST(0x46BD0B00 AS Date), N'Mornings 6.30 - 8.30 am and Sundays', 60, 38, 15000, 4, N'Ongoing', N'Slow batch')
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (3, 3, N'State Syllabus', NULL, NULL, NULL, 60, NULL, 6000, NULL, NULL, NULL)
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (4, 2, N'Technobay ', CAST(0x6C3C0B00 AS Date), NULL, N'Sundays', 60, 30, 15000, 4000, N'Ongoing', NULL)
INSERT [dbo].[Batch] ([BatchId], [CourseId], [BatchName], [StartDate], [TentativeEndDate], [BatchTiming], [Hours], [HoursTaken], [Fees], [FeesPaid], [Status], [Remarks]) VALUES (5, 1, N'Palnar Transmedia', NULL, NULL, NULL, 60, 0, 70000, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Batch] OFF
SET IDENTITY_INSERT [dbo].[Course] ON
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (1, N'Asp .Net MVC', 60, 10000, N'Asp .Net MVC 5')
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (2, N'Asp .Net MVC and Angular', 100, 15000, N'Asp .Net MVC 5, Angular and TypeScript')
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (3, N'State Plus 2', 60, 60000, N'State Syllabus Plus 2')
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (5, N'Asp .Net Core', 60, 10000, N'If the candidates already know asp .Net MVC, then only 40 hours is required')
INSERT [dbo].[Course] ([CourseId], [CourseName], [DurationInHours], [Fees], [Remarks]) VALUES (6, NULL, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[Course] OFF
SET IDENTITY_INSERT [dbo].[Faculty] ON
INSERT [dbo].[Faculty] ([FacultyId], [FacultyName], [Specialization], [Phone], [Email]) VALUES (1, N'Shalvin', N'MVC, Angular, TypeScript, C#', N'974580051w', N'shalvin@gmail.com')
INSERT [dbo].[Faculty] ([FacultyId], [FacultyName], [Specialization], [Phone], [Email]) VALUES (2, N'Mahin', N'MVC, Angular, TypeScript, C#, JQuery', N'78787878787', N'mahinna@gmail.com')
SET IDENTITY_INSERT [dbo].[Faculty] OFF
SET IDENTITY_INSERT [dbo].[Fees] ON
INSERT [dbo].[Fees] ([FeesId], [BatchId], [Date], [Amout], [Description]) VALUES (1, 1, CAST(0xDD3C0B00 AS Date), CAST(20000.00 AS Numeric(8, 2)), N'Fees paid')
SET IDENTITY_INSERT [dbo].[Fees] OFF
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT [dbo].[Student] ([StudentId], [BatchId], [StudentName], [YearsOfExperience], [TechnologiesKnown], [Location], [Phone], [Email]) VALUES (1, 2, N'arun', 3, N'asp.net,sql,html', N'alappuzha', N'8943781437', NULL)
SET IDENTITY_INSERT [dbo].[Student] OFF
ALTER TABLE [dbo].[Attendance] WITH CHECK ADD CONSTRAINT [FK_BatchSchedule_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([BatchId])
GO
ALTER TABLE [dbo].[Attendance] CHECK CONSTRAINT [FK_BatchSchedule_Batch]
GO
ALTER TABLE [dbo].[Attendance] WITH CHECK ADD CONSTRAINT [FK_BatchSchedule_Faculty] FOREIGN KEY([FacultyId])
REFERENCES [dbo].[Faculty] ([FacultyId])
GO
ALTER TABLE [dbo].[Attendance] CHECK CONSTRAINT [FK_BatchSchedule_Faculty]
GO
ALTER TABLE [dbo].[Batch] WITH CHECK ADD CONSTRAINT [FK_Batch_Course] FOREIGN KEY([CourseId])
REFERENCES [dbo].[Course] ([CourseId])
GO
ALTER TABLE [dbo].[Batch] CHECK CONSTRAINT [FK_Batch_Course]
GO
ALTER TABLE [dbo].[Fees] WITH CHECK ADD CONSTRAINT [FK_Fees_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([BatchId])
GO
ALTER TABLE [dbo].[Fees] CHECK CONSTRAINT [FK_Fees_Batch]
GO
ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([BatchId])
GO
ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Batch]
GO