sql课程设计报告.docVIP

课程设计报告

110054B——12010054070——张豪

创建数据库：

createdatabaseSanjiangMIS

onprimary

(

name=SanjiangMIS_data,

filename=f:\SanjiangMIS.mdf,

size=10,

maxsize=unlimited,

filegrowth=10%

)

logon

(

name=Sanjiang_log,

filename=f:\SanjiangMIS.ldf,

size=1,

maxsize=unlimited,

filegrowth=10%

)

创建表：

createtableEmployee

(

EmNointconstraintpk_EmNoprimarykey,

EmNamevarchar(20),

EmBirtdaydatetime,

EmCardIdvarchar(18),

EmPositionchar(20),

EmSexchar(2)

)

EmNo

工号

int

primarykey

EmpName

职工姓名

varchar(20)

EmBirthday

职工生日

datetime

EmCardId

职工身份证号

varchar(18)

EmPosition

职称

char(20)

EmSex

职工性别

char(2)

createtableDepartment

(

DepNointprimarykey,

DepNamevarchar(20),

)

DepNo

系别

int

primarykey

DepName

系名

varchar(20)

createtableDorm

(

DormNovarchar(8)constraintpk_DormNoprimarykeyconstraintck_DormNocheck(DormNolike[0-9][0-9]-[0-9][0-9][0-9]),

DormDepintconstraintfk_DormDepforeignkeyreferencesDepartment(DepNo),

DormNuintconstraintck_DormNucheck(DormNuin(4,6)),

DormNowNuintconstraintck_DormNowNucheck(DormNowNu=0andDormNowNu=6),

DormSexchar(2)constraintck_DormSexcheck(DormSexin(男,女))

)

DormNo

宿舍号

varchar(8)

primarykey

DormDep

系别

Int

foreignkey

DormNu

容量

Int

check

DormNowNu

剩余容量

Int

check

DormSex

性别

char(2)

check

createtableClass

(

ClassNamevarchar(20),

ClassNointconstraintpk_ClassNoprimarykey,

ClassDepNointconstraintfk_ClassDepNoforeignkeyreferencesDepartment(DepNo),

Classtypevarchar(20),

ClassScureint,

ClassTimeint

)

ClassName

课程名

varchar(20)

ClassNo

课程号

int

primarykey

ClassDepNo

系别

int

foreignkey

ClassType

课程类型

varchar(20)

ClassScure

学分

int

ClassTime

可是

int

createtableTeacher

(

TeacherNointconstraintfk_TeacherNoforeignkeyreferencesEmployee(EmNo)

constraintpk_Teacherprimarykey,

TeacherDepNointconstraintfk_TeacherDepNoforeignkeyreferencesDepartment(DepNo),

Ispartimebit

)

TeacherNo

教师工号

int

foreignkeyprimarykey

TeacherDepNo

教师系别

int

foreignkey

Ispartime

是否专职

bit

Notnull

createtableIncome

(

Employe