GO

CREATE TABLE [dbo].[P_PasswordType](
	[P_Id] [int] IDENTITY(1,1) NOT NULL,
	[U_Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
delete from P_PasswordType 
insert into P_PasswordType values('我最喜欢的动物')
insert into P_PasswordType values('我的小学班主任')
insert into P_PasswordType values('我的中学班主任')
insert into P_PasswordType values('我最爱吃什么')
insert into P_PasswordType values('我最爱吃什么')
insert into P_PasswordType values('我母亲叫什么')
insert into P_PasswordType values('我父亲叫什么')
GO
create table U_UserInfo 
(
    U_Id	int			identity(1,1) PRIMARY KEY		not null,--用户编号
    U_Name				varchar(50)			null,--用户名,
    U_RelName			varchar(50)			null,--用户真实姓名,
    U_Sex				bit					null,--用户性别,
    U_PassWord			varchar(50)			null,--用户密码,
    U_Birthday			datetime			null,--出生日期
    U_PsdType			int					null,--密保类型(管理员维护)
    U_PsdAnswer			varchar(50)			null, --密保回答问题
    U_IdCard			varchar(20)			null, --身份证号
    U_NativePlace		varchar(20)			null, --籍贯
    U_Address			varchar(50)			null, --家庭住址
    U_Telephone			varchar(50)			null, --电话号码
    U_Email				varchar(50)			null, --邮箱号码
    U_PostalId			varchar(20)			null, --邮编号码
    U_QQ				varchar(20)			null, --QQ
    U_Position			varchar(20)			null, --职位
    U_Reamrk			varchar(50)			null, --备注
    U_LoginTime         datetime            null, --注册时间
    U_RoleType			int					null,--用户权限类型(XML记录)
    U_Image				image				null,--用户生活照
)
GO
delete from U_UserInfo 
insert into U_UserInfo(U_Name,U_Sex, U_PassWord,U_Birthday,U_LoginTime,U_RoleType)
values('admin',1,'21232F297A57A5A743894A0E4A801FC3',GETDATE(),GETDATE(),2)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CollegeInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CollegeInfo]
GO

CREATE TABLE [dbo].[CollegeInfo] (
	[collegeNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY ,
	[collegeName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[collegeBirthDate] [datetime] NULL , 
	[collegeMan] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[collegeTelephone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[collegeMemo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpecialFieldInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SpecialFieldInfo]
GO

CREATE TABLE [dbo].[SpecialFieldInfo] (
	[specialFieldNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY ,
	[specialFieldName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[specialCollegeNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[specialBirthDate] [datetime] NULL , 
	[specialMan] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[specialTelephone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[specialMemo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClassInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ClassInfo]
GO

CREATE TABLE [dbo].[ClassInfo] (
	[classNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY ,
	[className] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[classSpecialFieldNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[classBirthDate] [datetime] NULL , 
	[classTeacherCharge] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
	[classTelephone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[classMemo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student_QQ287307421]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Student_QQ287307421]
GO

CREATE TABLE [dbo].[Student_QQ287307421] (
	[studentNumber] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY ,
	[studentName] [varchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[studentPassword] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[studentSex] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[studentClassNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[studentBirthday] [datetime] NULL , 
	[studentState] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[studentPhoto] [image] NULL ,
	[studentTelephone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[studentEmail] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[studentQQ] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[studentAddress] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[studentMemo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Teacher_QQ287307421]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Teacher_QQ287307421]
GO

CREATE TABLE [dbo].[Teacher_QQ287307421] (
	[teacherNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY ,
	[teacherName] [varchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[teacherPassword] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[teacherSex] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[teacherBirthday] [datetime] NULL , 
	[teacherArriveDate] [datetime] NULL , 
	[teacherCardNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[teacherPhone] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[teacherPhoto] [image] NULL ,
	[teacherAddress] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[teacherMemo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseInfo_QQ254540457]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CourseInfo_QQ254540457]
GO

CREATE TABLE [dbo].[CourseInfo_QQ254540457] (
	[courseNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL PRIMARY KEY ,
	[courseName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[courseTeacher] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[courseTime] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[coursePlace] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[courseScore] [float] NOT NULL ,
	[courseMemo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentSelectCourseInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[StudentSelectCourseInfo]
GO

CREATE TABLE [dbo].[StudentSelectCourseInfo] (
	[selectId] [int] IDENTITY (1, 1) NOT NULL  PRIMARY KEY,
	[studentNumber] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[courseNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,

) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ScoreInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ScoreInfo]
GO

CREATE TABLE [dbo].[ScoreInfo] (
	[scoreId] [int] IDENTITY (1, 1) NOT NULL  PRIMARY KEY,
	[studentNumber] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[courseNumber] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[scoreValue] [float] NOT NULL ,
	[studentEvaluate] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,

) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[News]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[News]
GO

CREATE TABLE [dbo].[News] (
	[newsId] [int] IDENTITY (1, 1) NOT NULL  PRIMARY KEY,
	[newsTitle] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[newsContent] [varchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[newsDate] [datetime] NULL , 
	[newsPhoto] [image] NULL ,

) ON [PRIMARY]
GO



ALTER TABLE SpecialFieldInfo ADD FOREIGN KEY (specialCollegeNumber) REFERENCES CollegeInfo(collegeNumber)
ALTER TABLE ClassInfo ADD FOREIGN KEY (classSpecialFieldNumber) REFERENCES SpecialFieldInfo(specialFieldNumber)
ALTER TABLE Student_QQ287307421 ADD FOREIGN KEY (studentClassNumber) REFERENCES ClassInfo(classNumber)
ALTER TABLE CourseInfo_QQ254540457 ADD FOREIGN KEY (courseTeacher) REFERENCES Teacher_QQ287307421(teacherNumber)
ALTER TABLE StudentSelectCourseInfo ADD FOREIGN KEY (studentNumber) REFERENCES Student_QQ287307421(studentNumber)
ALTER TABLE StudentSelectCourseInfo ADD FOREIGN KEY (courseNumber) REFERENCES CourseInfo_QQ254540457(courseNumber)
ALTER TABLE ScoreInfo ADD FOREIGN KEY (studentNumber) REFERENCES Student_QQ287307421(studentNumber)
ALTER TABLE ScoreInfo ADD FOREIGN KEY (courseNumber) REFERENCES CourseInfo_QQ254540457(courseNumber)
