sql实验五.docx
- 文档编号:27351093
- 上传时间:2023-06-29
- 格式:DOCX
- 页数:20
- 大小:1.72MB
sql实验五.docx
《sql实验五.docx》由会员分享,可在线阅读,更多相关《sql实验五.docx(20页珍藏版)》请在冰豆网上搜索。
sql实验五
集美大学计算机工程学院实验报告
课程名称:
数据库系统教程
班级:
实验成绩:
指导教师
姓名
实验项目名称:
学号:
上机实践日期:
实验项目编号:
实验五
组号:
上机实践时间:
学时
一、目的
二、实验内容
(1)根据以下三张图配合自已的成绩查看,完成集美大学学生成绩管理的数据库设计,包括E-R(用ERWIN或VISIO等CASE工具绘制)及数据库表设计(针对SQLSERVER),根据业务的估计频度及业务特点完成数据库物理设计,完成安全性设计(文字叙述即可).
(2)按图书管理系统课件tushu.ppt完成图书馆图书管理系统的概念模型设计(用ERWIN或VISIO或POWERDESIGNER等绘制),完成数据库表设计。
试进行物理设计。
(3)针对图书管理系统的业务完成数据库的界面设计(提高).
三、运行结果
(1)①设计图:
②sql数据库设计:
CREATETABLEcourse(
ctpyetinyintNULL,
cprieodchar(18)NULL,
ctimechar(18)NULL,
cnamechar(18)NULL,
cnotinyintNOTNULL,
tnochar(18)NULL
)
go
ALTERTABLEcourse
ADDPRIMARYKEYNONCLUSTERED(cno)
go
CREATETABLEctype(
publicbasechar(18)NULL,
specialchar(18)NULL,
specialbasechar(18)NULL
)
go
CREATETABLEexamsort(
computerchar(18)NULL,
speakchar(18)NULL,
unlookchar(18)NULL,
lookchar(18)NULL
)
go
CREATETABLEexamstatus(
absentchar(18)NULL,
unrulechar(18)NULL,
cheatchar(18)NULL,
normalchar(18)NULL
)
go
CREATETABLEgrademanage(
cnotinyintNOTNULL,
examsorttinyintNULL,
examstatustinyintNULL,
gradestatustinyintNULL,
gradesorttinyintNULL,
reportgradesorttinyintNULL
)
go
ALTERTABLEgrademanage
ADDPRIMARYKEYNONCLUSTERED(cno)
go
CREATETABLEgradesort(
sorcechar(18)NULL
)
go
CREATETABLEgradestatus(
unwritechar(18)NULL,
writechar(18)NOTNULL
)
go
CREATETABLEpass(
passchar(18)NULL,
unpasschar(18)NULL
)
go
CREATETABLErank(
Echar(18)NULL,
Dchar(18)NULL,
Cchar(18)NULL,
Bchar(18)NULL,
Achar(18)NULL
)
go
CREATETABLEreportgradesort(
theorygradetinyintNULL,
speakgradetinyintNULL,
experimentgradetinyintNULL,
examgradetinyintNULL,
ordinarygradetinyintNULL
)
go
CREATETABLEsc(
snochar(18)NOTNULL,
cnotinyintNOTNULL,
gradetinyintNULL
)
go
ALTERTABLEsc
ADDPRIMARYKEYNONCLUSTERED(sno,cno)
go
CREATETABLEstudent(
classchar(18)NULL,
snamechar(18)NULL,
snochar(18)NOTNULL,
tnochar(18)NULL,
collagechar(18)NULL
)
go
ALTERTABLEstudent
ADDPRIMARYKEYNONCLUSTERED(sno)
go
CREATETABLEteach(
classchar(18)NULL,
tnamechar(18)NULL,
tnochar(18)NOTNULL,
ttimechar(18)NULL,
tofficetinyintNULL
)
go
ALTERTABLEteach
ADDPRIMARYKEYNONCLUSTERED(tno)
go
ALTERTABLEcourse
ADDFOREIGNKEY(tno)
REFERENCESteach
go
ALTERTABLEgrademanage
ADDFOREIGNKEY(cno)
REFERENCEScourse
go
ALTERTABLEsc
ADDFOREIGNKEY(cno)
REFERENCEScourse
go
ALTERTABLEsc
ADDFOREIGNKEY(sno)
REFERENCESstudent
go
ALTERTABLEstudent
ADDFOREIGNKEY(tno)
REFERENCESteach
go
(1)①设计图:
②sql数据库设计:
CREATETABLEauther(
namechar(18)NULL,
authernochar(18)NOTNULL
)
go
ALTERTABLEauther
ADDPRIMARYKEYNONCLUSTERED(autherno)
go
CREATETABLEauther_book(
authernochar(18)NOTNULL,
booknochar(18)NOTNULL
)
go
ALTERTABLEauther_book
ADDPRIMARYKEYNONCLUSTERED(autherno,bookno)
go
CREATETABLEbook(
memorychar(18)NULL,
wrodschar(18)NULL,
versionchar(18)NULL,
pricechar(18)NULL,
publishdatechar(18)NULL,
publishaddresschar(18)NULL,
publishnamechar(18)NULL,
titlechar(18)NULL,
isbnchar(18)NULL,
booknochar(18)NOTNULL,
booktypeidchar(18)NULL,
sortnochar(18)NULL,
sortnamechar(18)NULL,
publishnochar(18)NULL
)
go
ALTERTABLEbook
ADDPRIMARYKEYNONCLUSTERED(bookno)
go
CREATETABLEbookcopy(
bookcopyidchar(18)NOTNULL,
statuschar(18)NULL,
enterdatechar(18)NULL,
librarynochar(18)NULL,
booknochar(18)NOTNULL
)
go
ALTERTABLEbookcopy
ADDPRIMARYKEYNONCLUSTERED(bookcopyid,bookno)
go
CREATETABLEbooktype(
namechar(18)NULL,
unitchar(18)NULL,
finechar(18)NULL,
booktypeidchar(18)NOTNULL
)
go
ALTERTABLEbooktype
ADDPRIMARYKEYNONCLUSTERED(booktypeid)
go
CREATETABLEborrow(
extendednumchar(18)NULL,
returndatechar(18)NULL,
borrowdatechar(18)NULL,
readernochar(18)NOTNULL,
bookcopyidchar(18)NOTNULL,
booknochar(18)NOTNULL
)
go
ALTERTABLEborrow
ADDPRIMARYKEYNONCLUSTERED(readerno,bookcopyid,bookno)
go
CREATETABLEborrowrestrict(
extendedperiodchar(18)NULL,
extendednumberchar(18)NULL,
periodchar(18)NULL,
numberchar(18)NULL,
readtypeidchar(18)NOTNULL,
booktypeidchar(18)NOTNULL
)
go
ALTERTABLEborrowrestrict
ADDPRIMARYKEYNONCLUSTERED(readtypeid,booktypeid)
go
CREATETABLEcity(
cityidchar(18)NOTNULL,
citynamechar(18)NULL,
provinceidchar(18)NOTNULL
)
go
ALTERTABLEcity
ADDPRIMARYKEYNONCLUSTERED(cityid,provinceid)
go
CREATETABLEdepartment(
namechar(18)NULL,
departmentnochar(18)NOTNULL
)
go
ALTERTABLEdepartment
ADDPRIMARYKEYNONCLUSTERED(departmentno)
go
CREATETABLElibrary(
namechar(18)NULL,
librarynochar(18)NOTNULL
)
go
ALTERTABLElibrary
ADDPRIMARYKEYNONCLUSTERED(libraryno)
go
CREATETABLEpayment(
amountchar(18)NULL,
datechar(18)NULL,
paymentnochar(18)NOTNULL,
readernochar(18)NULL
)
go
ALTERTABLEpayment
ADDPRIMARYKEYNONCLUSTERED(paymentno)
go
CREATETABLEprovince(
provincenamechar(18)NULL,
provinceidchar(18)NOTNULL
)
go
ALTERTABLEprovince
ADDPRIMARYKEYNONCLUSTERED(provinceid)
go
CREATETABLEpublish(
publishnochar(18)NOTNULL,
citynamechar(18)NULL,
provincenamechar(18)NULL,
addresschar(18)NULL,
namechar(18)NULL
)
go
ALTERTABLEpublish
ADDPRIMARYKEYNONCLUSTERED(publishno)
go
CREATETABLEreader(
departmentnochar(18)NULL,
passwordwordchar(18)NULL,
usergroupidchar(18)NULL,
genderchar(18)NULL,
emailchar(18)NULL,
statuschar(18)NULL,
readtypeidchar(18)NULL,
readernochar(18)NOTNULL
)
go
ALTERTABLEreader
ADDPRIMARYKEYNONCLUSTERED(readerno)
go
CREATETABLEreadertype(
readtypeidchar(18)NOTNULL
)
go
ALTERTABLEreadertype
ADDPRIMARYKEYNONCLUSTERED(readtypeid)
go
CREATETABLEsort(
sortnamechar(18)NOTNULL,
sortnochar(18)NOTNULL
)
go
ALTERTABLEsort
ADDPRIMARYKEYNONCLUSTERED(sortno,sortname)
go
CREATETABLEusergroup(
namechar(18)NULL,
authertynochar(18)NULL,
usergroupidchar(18)NOTNULL
)
go
ALTERTABLEusergroup
ADDPRIMARYKEYNONCLUSTERED(usergroupid)
go
ALTERTABLEauther_book
ADDFOREIGNKEY(bookno)
REFERENCESbook
go
ALTERTABLEauther_book
ADDFOREIGNKEY(autherno)
REFERENCESauther
go
ALTERTABLEbook
ADDFOREIGNKEY(publishno)
REFERENCESpublish
go
ALTERTABLEbook
ADDFOREIGNKEY(sortno,sortname)
REFERENCESsort
go
ALTERTABLEbook
ADDFOREIGNKEY(booktypeid)
REFERENCESbooktype
go
ALTERTABLEbookcopy
ADDFOREIGNKEY(bookno)
REFERENCESbook
go
ALTERTABLEbookcopy
ADDFOREIGNKEY(libraryno)
REFERENCESlibrary
go
ALTERTABLEborrow
ADDFOREIGNKEY(bookcopyid,bookno)
REFERENCESbookcopy
go
ALTERTABLEborrow
ADDFOREIGNKEY(readerno)
REFERENCESreader
go
ALTERTABLEborrowrestrict
ADDFOREIGNKEY(booktypeid)
REFERENCESbooktype
go
ALTERTABLEborrowrestrict
ADDFOREIGNKEY(readtypeid)
REFERENCESreadertype
go
ALTERTABLEcity
ADDFOREIGNKEY(provinceid)
REFERENCESprovince
go
ALTERTABLEpayment
ADDFOREIGNKEY(readerno)
REFERENCESreader
go
ALTERTABLEreader
ADDFOREIGNKEY(readtypeid)
REFERENCESreadertype
go
ALTERTABLEreader
ADDFOREIGNKEY(departmentno)
REFERENCESdepartment
go
ALTERTABLEreader
ADDFOREIGNKEY(usergroupid)
REFERENCESusergroup
go
3.数据库设计:
主页:
借书:
查询:
还书:
续借:
注:
此为读者的功能,另外管理员的功能略。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 实验