学生管理系统 数据库期末综合设计作业(6)

2.教务管理员

CREATE login jadmin with password='jadmin' use school

create user jadmin for login jadmin WITH DEFAULT_SCHEMA=\;

c.财务管理员

CREATE login cadmin with password='cadmin' use school

create user cadmin for login cadmin WITH DEFAULT_SCHEMA=\;

d.奖惩管理员

CREATE login padmin with password='padmin' use school

create user padmin for login padmin WITH DEFAULT_SCHEMA=\;

e.教工用户

CREATE login jg with password='jg' use school

create user jg for login jg WITH DEFAULT_SCHEMA=\;

f. 学生用户

CREATE login st with password='st' use school

create user st for login st WITH DEFAULT_SCHEMA=\;

23

三、 基本表创建

1.教工信息表

create table \.Teacher (

TNum varchar (20) primary key, TName varchar (20) unique, TSex varchar (2),

BornYear varchar (20), Position varchar (15),

RelationNumber varchar(20), Wage varchar(10) );

2.院系信息表

create table \.Major (

Department varchar(20) ,

Major varchar(20)primary key );

3.班级信息表

create table \.Class (

Grade varchar (10) unique, Class varchar (10)unique, Num varchar (4),

24

)

TName varchar(20), Section varchar (20),

primary key(Grade,Class),

foreign key(TName)references \.Teacher(TName)

4.学生信息表

create table \.Student (

StuNum varchar (20) primary key, StuName varchar (20)unique, StuSex varchar (2), Class varchar(10), Grade varchar (10), BornYear varchar (20),

RelationAddress varchar(50), RelationNumber varchar(20), ParentNumber varchar(20),

foreign key(Class)references \.Class(Class), foreign key(Grade)references \.Class(Grade) );

5.课程信息表

create table \.Course (

Grade varchar (10), Class varchar (10), Course varchar (20), Term varchar(20),

TeachingMaterial varchar (20), CourseStyle varchar (20), ClassHour varchar(4),

ClassAddress varchar(20), ExamStyle varchar(10),

primary key(Grade,Class,Course),

foreign key(Class)references \.Class(Class), foreign key(Grade)references \.Class(Grade) );

6.成绩信息表

create table \.Result (

25

Grade varchar(10) , Class varchar(10) , Term varchar(20), Result varchar (8), StuNum varchar (20),

Course varchar (20) primary key,

foreign key(Class)references \.Class(Class), foreign key(Grade)references \.Class(Grade), foreign key(StuNum)references \.Student(StuNum) );

7.奖惩信息表

create table \.RewardsPunish (

StuNum varchar(20) primary key, StuName varchar(20), StuSex varchar(2),

RewardTime varchar (20), RewardStyle varchar (20), RewardReason varchar (20), PunishTime varchar (20), PunishStyle varchar (20), PunishReason varchar (20),

foreign key(StuName)references \.Student(StuName), foreign key(StuNum)references \.Student(StuNum) );

8.奖学金信息表

create table \.Scholarship (

StuNum varchar(20) primary key, StuName varchar(20), StuSex varchar(2), Class varchar (10),

ScholarshipStyle varchar (20), Smoney varchar (20), Section varchar (20),

foreign key(StuName)references \.Student(StuName), foreign key(StuNum)references \.Student(StuNum), foreign key(Class)references \.Class(Class) );

26

9.贷款信息表

create table \.Loan (

StuNum varchar(20) primary key, StuName varchar(20), StuSex varchar(2), Class varchar (10), LoanTime varchar (20), Smoney varchar (20), Section varchar (20),

foreign key(StuName)references \.Student(StuName), foreign key(StuNum)references \.Student(StuNum), foreign key(Class)references \.Class(Class) );

10.用户信息表

create table \.UserTable (

UserID varchar (10) primary key, UserPassword varchar (10), UserStyle varchar (10) );

27