GO
/****** Object: Database [schoolm] Script Date: 9/28/2017 7:55:55 PM ******/
CREATE DATABASE [schoolm]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'schoolm', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\schoolm.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'schoolm_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\schoolm_log.ldf' , SIZE = 832KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [schoolm] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [schoolm].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [schoolm] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [schoolm] SET ANSI_NULLS OFF
GO
ALTER DATABASE [schoolm] SET ANSI_PADDING OFF
GO
ALTER DATABASE [schoolm] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [schoolm] SET ARITHABORT OFF
GO
ALTER DATABASE [schoolm] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [schoolm] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [schoolm] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [schoolm] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [schoolm] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [schoolm] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [schoolm] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [schoolm] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [schoolm] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [schoolm] SET ENABLE_BROKER
GO
ALTER DATABASE [schoolm] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [schoolm] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [schoolm] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [schoolm] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [schoolm] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [schoolm] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [schoolm] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [schoolm] SET RECOVERY FULL
GO
ALTER DATABASE [schoolm] SET MULTI_USER
GO
ALTER DATABASE [schoolm] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [schoolm] SET DB_CHAINING OFF
GO
ALTER DATABASE [schoolm] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [schoolm] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [schoolm]
GO
/****** Object: Table [dbo].[administator] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[administator](
[ad_id] [int] IDENTITY(1,1) NOT NULL,
[ad_name] [nvarchar](20) NOT NULL,
[ad_password] [nvarchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ad_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].[classes] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[classes](
[class_id] [int] IDENTITY(1,1) NOT NULL,
[classname] [nvarchar](20) NOT NULL,
[fees] [float] NULL,
PRIMARY KEY CLUSTERED
(
[class_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].[fees] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[fees](
[fee_id] [int] IDENTITY(1,1) NOT NULL,
[fee_amount] [float] NOT NULL,
[fee_fk_st_id] [int] NULL,
[SA_FK_ID] [int] NULL,
[dayx] [nvarchar](2) NULL,
[monthx] [nvarchar](2) NULL,
PRIMARY KEY CLUSTERED
(
[fee_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].[student] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
[std_id] [int] IDENTITY(1,1) NOT NULL,
[std_name] [nvarchar](20) NOT NULL,
[std_fname] [nvarchar](20) NOT NULL,
[std_gender] [nvarchar](6) NOT NULL,
[std_address] [nvarchar](100) NOT NULL,
[std_admissiondate] [nvarchar](20) NOT NULL,
[std_ad_fk_id] [int] NULL,
PRIMARY KEY CLUSTERED
(
[std_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].[student_img] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student_img](
[img_id] [int] IDENTITY(1,1) NOT NULL,
[img_path] [nvarchar](max) NOT NULL,
[img_fk] [int] NULL,
PRIMARY KEY CLUSTERED
(
[img_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
/****** Object: Table [dbo].[STUDENT_STATUS] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[STUDENT_STATUS](
[SA_ID] [int] IDENTITY(1,1) NOT NULL,
[SA_ST_ID] [int] NULL,
[SA_Class_id] [int] NULL,
[sa_year] [nvarchar](5) NULL,
PRIMARY KEY CLUSTERED
(
[SA_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 IDENTITY_INSERT [dbo].[administator] ON
INSERT [dbo].[administator] ([ad_id], [ad_name], [ad_password]) VALUES (1, N'salmnan', N'password123')
INSERT [dbo].[administator] ([ad_id], [ad_name], [ad_password]) VALUES (2, N'raza', N'password113')
SET IDENTITY_INSERT [dbo].[administator] OFF
SET IDENTITY_INSERT [dbo].[classes] ON
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (1, N'Montessori', 5000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (2, N'I', 4000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (3, N'II', 4000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (4, N'III', 4000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (5, N'IV', 4500)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (6, N'V', 4500)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (7, N'VI', 5000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (8, N'VII', 5000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (9, N'VIII', 5500)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (10, N'IX', 6000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (11, N'X', 6000)
SET IDENTITY_INSERT [dbo].[classes] OFF
SET IDENTITY_INSERT [dbo].[fees] ON
INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_fk_st_id], [SA_FK_ID], [dayx], [monthx]) VALUES (1007, 4000, 2, 1, N'28', N'9')
INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_fk_st_id], [SA_FK_ID], [dayx], [monthx]) VALUES (1008, 4000, 3, 4, N'28', N'9')
INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_fk_st_id], [SA_FK_ID], [dayx], [monthx]) VALUES (1009, 5000, 2005, 5, N'28', N'9')
SET IDENTITY_INSERT [dbo].[fees] OFF
SET IDENTITY_INSERT [dbo].[student] ON
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2, N'ahsan', N'ahmed', N'male', N't-3t2 north khi', N'8/26/2017', 1)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3, N'sunny', N'ahmed', N'male', N'e-3q2 north khi', N'8/26/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2004, N'jen', N'peter', N'male', N'i-32 street 5', N'9/12/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2005, N'sami', N'ali', N'male', N'y-434', N'9/12/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3004, N'ali ahmed', N'ahmed', N'male', N'y-3232', N'9/14/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3006, N'tony', N'james', N'male', N'u-454', N'9/19/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3007, N'sami', N'ahmed', N'male', N'y-565', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3008, N'raza ahmed', N'masood', N'male', N'y-6546', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3009, N'sana', N'ali', N'female', N'ds-6654', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3010, N'sana', N'ali', N'female', N'ds-6654', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3011, N'moosa', N'ali', N'male', N't-545', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3012, N'test', N'test2', N'male', N'i-4324', N'9/21/2017', 1)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (4007, N'ali', N'ahmed raza', N'male', N'r-654', N'9/23/2017', 2)
SET IDENTITY_INSERT [dbo].[student] OFF
SET IDENTITY_INSERT [dbo].[student_img] ON
INSERT [dbo].[student_img] ([img_id], [img_path], [img_fk]) VALUES (6, N'C:\Users\salman\Documents\Visual Studio 2013\Projects\WindowsFormsApplication4\WindowsFormsApplication4\Resources\2005.jpg', 2005)
SET IDENTITY_INSERT [dbo].[student_img] OFF
SET IDENTITY_INSERT [dbo].[STUDENT_STATUS] ON
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (1, 2, 2, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (2, 2004, 2, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (4, 3, 4, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (5, 2005, 1, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (6, 3004, 6, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (7, 3006, 6, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (8, 2, 1, N'2016')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (9, 3, 3, N'2016')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (10, 3009, 7, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (11, 3011, 11, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (12, 3012, 9, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (1010, 4007, 4, N'2017')
SET IDENTITY_INSERT [dbo].[STUDENT_STATUS] OFF
SET ANSI_PADDING ON
GO
/****** Object: Index [UQ__administ__84D9FB7AC548E39F] Script Date: 9/28/2017 7:55:55 PM ******/
ALTER TABLE [dbo].[administator] ADD UNIQUE NONCLUSTERED
(
[ad_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[fees] WITH CHECK ADD FOREIGN KEY([fee_fk_st_id])
REFERENCES [dbo].[student] ([std_id])
GO
ALTER TABLE [dbo].[fees] WITH CHECK ADD FOREIGN KEY([SA_FK_ID])
REFERENCES [dbo].[STUDENT_STATUS] ([SA_ID])
GO
ALTER TABLE [dbo].[student] WITH CHECK ADD FOREIGN KEY([std_ad_fk_id])
REFERENCES [dbo].[administator] ([ad_id])
GO
ALTER TABLE [dbo].[student_img] WITH CHECK ADD FOREIGN KEY([img_fk])
REFERENCES [dbo].[student] ([std_id])
GO
ALTER TABLE [dbo].[STUDENT_STATUS] WITH CHECK ADD FOREIGN KEY([SA_Class_id])
REFERENCES [dbo].[classes] ([class_id])
GO
ALTER TABLE [dbo].[STUDENT_STATUS] WITH CHECK ADD FOREIGN KEY([SA_ST_ID])
REFERENCES [dbo].[student] ([std_id])
GO
/****** Object: StoredProcedure [dbo].[delete_student] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[delete_student]
(
@std_id int
)
as
begin
delete from student_img where img_fk=@std_id
delete from student where std_id=@std_id
end
GO
/****** Object: StoredProcedure [dbo].[insert_student] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_student]
(
@std_name nvarchar(20),
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100),
@std_admissiondate nvarchar(20),
@std_ad_fk_id int
)
as
begin
insert into student(std_name,std_fname,std_gender,std_address,std_admissiondate,std_ad_fk_id)
values(@std_name,@std_fname,@std_gender,@std_address,@std_admissiondate,@std_ad_fk_id)
end
GO
/****** Object: StoredProcedure [dbo].[insert_student_img] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_student_img]
(
@img_path nvarchar(max),
@img_fk int
)
as
begin
insert into student_img(img_path,img_fk)
values(@img_path,@img_fk)
end
GO
/****** Object: StoredProcedure [dbo].[insert_STUDENT_STATUS] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_STUDENT_STATUS]
(
@SA_ST_ID INT ,
@SA_Class_id INT ,
@sa_year nvarchar(5)
)
as
begin
insert into STUDENT_STATUS(SA_ST_ID,SA_Class_id,sa_year)
VALUES(@SA_ST_ID,@SA_Class_id,@sa_year)
end
GO
/****** Object: StoredProcedure [dbo].[insert_tbladmin] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_tbladmin]
(
@ad_name nvarchar(20) ,
@ad_password nvarchar(20)
)
as
begin
insert into administator(ad_name,ad_password)
values(@ad_name,@ad_password)
end
GO
/****** Object: StoredProcedure [dbo].[insert_tblfees] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[insert_tblfees]
(
@fee_amount float ,
@dayx nvarchar(2),
@monthx nvarchar(2),
@fee_fk_st_id int,
@SA_FK_ID INT
)
as
begin
insert into fees(fee_amount,dayx,monthx,fee_fk_st_id,SA_FK_ID)
values(@fee_amount,@dayx,@monthx,@fee_fk_st_id,@SA_FK_ID)
end
GO
/****** Object: StoredProcedure [dbo].[insert_tblstudent] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_tblstudent]
(
@std_name nvarchar(20) ,
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100) ,
@std_admissiondate nvarchar(20),
@std_ad_fk_id int
)
as
begin
insert into student(std_name,std_fname,std_gender,std_address,std_admissiondate,std_ad_fk_id)
values(@std_name,@std_fname,@std_gender,@std_address,@std_admissiondate,@std_ad_fk_id)
end
GO
/****** Object: StoredProcedure [dbo].[UPDATE_STUDENT] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[UPDATE_STUDENT]
(
@std_id int,
@std_name nvarchar(20),
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100)
)
as
begin
update student
set std_name=@std_name,
std_fname=@std_fname,
std_gender=@std_gender,
std_address=@std_address
where std_id=@std_id
end
GO
USE [master]
GO
ALTER DATABASE [schoolm] SET READ_WRITE
GO
