实验室管理系统设计与实施

课程设计课题：实验室管理系统LAB院系：计算机科学与技术学院专业:网络工程班级：104221课程：数据库原理与应用教师:陈佛敏小组组长学号:1042210姓名:2023至2023学年度下学期工程分工及成绩表学号姓名在本工程中完成的任务成绩1042210E-R图讨论，数据库设计931042210E-R图讨论，设计E-R图891042210E-R图讨论，后期工作861042210E-R图讨论，word文档制作871042210E-R图讨论，数据库编程88目录实验室管理数据库LAB设计与实施31.1实验室管理数据库设计3现实问题31.1.2概念结构设计41.1.3逻辑结构设计41.1.4完整性约束条件规划设计51.1.5物理结构设计51.2数据库实施--建立库和表61.2.1表结构设计61.2.2建立数据库ST源代码71.2.3建立表结构源代码71.2.4向表中插入例如数据源代码91.2.5数据库关系图121.3查询与更新131.3.1单表查询131.3.2连接查询131.3.3嵌套查询131.3.4集合查询141.3.5插入数据141.3.6修改数据151.3.7删除数据151.4索引与视图161.4.1建立索引源代码161.4.2建立视图源代码161.5存储过程171.5.1建立存储过程语句171.5.2无参存储过程171.6内嵌表值函数181.7数据库平安性20实验室管理系统数据库LAB设计与实施1.1实验室管理系统数据库设计现实问题我校有假设干实验室，每个实验室有假设干学生；一个学生可以参加多个实验室；每个实验室有且只有一个指导老师；每个实验室有假设干器材；实验室给每个学生分配任务，有且只有一个任务；所有实验室的器材有实验室自己派学生管理，并有一个管理员；概念结构设计为我校设计实验室管理系统数据库E-R模型，自行为每个实体和联系给出一些适当的属性，对于键属性用下划线或在旁边用竖线标明。解：学生课程数据库E-R模型如图1所示。图1实验室管理系统数据库E-R模型逻辑结构设计对关系型数据库来说，逻辑结构设计就是应用E-R图转换为关系模型的规那么将E-R模型转换为等价的关系模型。下面以学生课程数据库为例给出关系型数据库逻辑结构设计的过程。【例2】将图1实验室管理系统数据库E-R图转换为等价的关系模型，并标明主键和外键。〔主键之下用下划线“__〞表示，外键之下用双下划线“〞表示〕解：根据E-R图向关系模型的转换规那么，将E-R模型转换为如下关系模式：(a)学生〔学生学号，学生姓名，性别,年龄〕(b)老师〔老师编号，老师姓名，学历，工程，老师工资，老师〕(c)实验室〔实验室名称，实验室编号，老师姓名，成员数量，实验室地址〕(d)器材〔器材名称，器材编号，器材数量，实验室名称，sname(管理员)〕(e)工程〔实验室编号，学生学号，所研究任务〕以上5个关系模式构成实验室管理系统关系数据库模式。完整性约束条件规划设计表名完整性约束条件约束类型学生(1)学生学号取值唯一且不能为空值主健(2)学生姓名不能为空值Notnull(3)性别只能为“男〞或“女〞。默认值为’男’check(4)年龄不能为空Notnull老师(4)老师编号取值唯一且不能为空值主健(5)老师姓名取值不能为空Notnull学历取值不能为空Notnull工程取值不能为空Notnull老师工资取值不能为空Notnull老师可有可没有实验室(7)实验室名称取值不能为空Notnull(8)实验室编号取值唯一且不能为空主键(9)老师姓名为老师表中老师已有的姓名Notnull(10)成员数量取值不能为空Notnull实验室地址取值不能为空Notnull器材(12)器材名称取值不为空Notnull(13)器材编号取值唯一且不能为空主键(14)器材数量取值不为空Notnull(15)实验室名称取值为实验表中已有的名称Notnull(16)管理员取值不为空Notnull工程(17)实验室编号取值唯一且不能空，是实验室表中已有的实验室编号外键(18)学生学号取值唯一且不能为空，是学生表中已有的学生学号外键(19)所研究任务取值不能为空Notnull物理结构设计【例3】创立实验室管理系统数据库“LAB〞，将数据文件和日志文件均存入文件夹：F:\SQL2023DB。具体设置见表1。表1ST数据库属性设置表文件类型逻辑文件名物理文件名初始大小自动增量最大容量主数据文件LAB_DataF:\SQL2023DB\;LAB_Data.mdf3M1M不限制增长日志文件LAB_logF:\SQL2023DB\LAB_log.ldf1M10%不限制增长1.2数据库实施--建立库和表表结构设计学生课程数据库JXDB中各个表结构设置如下：表1“student〞表结构列名数据类型允许Null值约束条件学生学号Char(10)primarykeysnameChar(20)性别Char(2)check(性别in('男','女'))default'男',表2“teacher〞表结构列名数据类型允许Null值约束条件老师编号Char(10)primarykey老师姓名Char(20)学历Char(10)工程Char(20)老师工资Char(20)老师Char(11)实验室编号Char(10)为实验表中已有的编号表3“laboratory〞表结构列名数据类型允许Null值约束条件实验室名称Char(20)实验室编号Char(10)primarykey老师姓名Char(10)为老师表中已有的姓名成员数量Char(10)实验室地址Char(20)表4“equipment〞表结构列名数据类型允许Null值约束条件器材名称Char(20)器材编号Char(10)primarykey器材数量Char(5)实验室名称Char(20)为实验表中已有的名称实验室编号Char(10)为实验表中已有的编号SnameChar(10)为学生表中已有的名称表5“tesk〞表结构列名数据类型允许Null值约束条件学生学号Char(10)Foreignkey实验室编号Char(10)Foreignkey所研究任务Char(10)建立数据库ST源代码【例4】创立实验室管理系统数据库“ST〞，将数据文件和日志文件均存入文件夹：F:\SQL2023DB。--建立学生-课程数据库lab.sqlUSEmasterIFDB_ID(N'LAB')ISNOTNULL--假设数据库ST已存在, DROPDATABASELAB--那么删除。CREATEDATABASELABON(NAME=LAB_Data,--主数据文件FILENAME='F:\SQL2023DB\LAb_Data.mdf',SIZE=3MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)LOGON(NAME=LAB_log,--日志文件FILENAME='F:\SQL2023DB\LAB_log.ldf',SIZE=1MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)建立表结构源代码useLABifexists(selectnamefromsys.objectswherename='TESK')droptableTESKifexists(selectnamefromsys.objectswherename='laboratory')droptablelaboratoryifexists(selectnamefromsys.objectswherename='teacher')droptableteacherifexists(selectnamefromsys.objectswherename='student')droptablestudentifexists(selectnamefromsys.objectswherename='equipment')droptableequipment--建立学生表结构createtablestudent(学生学号char(10)primarykey,snamechar(20),--学生姓名性别char(2),年龄char(10),)--建立老师表结构createtableteacher(老师编号char(10)primarykey,老师姓名char(20),学历char(10),工程char(20),工资char(20),老师char(11),实验室编号char(10),)--建立实验室表结构createtablelaboratory(实验室名称char(20),实验室编号char(10)primarykey,老师编号char(10),成员数量char(10),--成员数量实验室地址char(20),)--建立器材表结构createtableequipment(器材名称char(20),器材编号char(10)primarykey,器材数量char(5),实验室编号char(10),实验室名称char(20),--属于哪个实验室snamechar(10),--哪个学生管理)--建立工程室表结构createTABLEtesk( 学生学号CHAR(10), 实验室编号CHAR(10), 所研究任务CHAR(20), PRIMARYKEY(学生学号,实验室编号), FOREIGNKEY(学生学号)REFERENCESSTUDENT(学生学号), FOREIGNKEY(实验室编号)REFERENCESLABORATORY(实验室编号),)向表中插入例如数据源代码--向学生表中插入数据insertintostudentvalues('104221028','杨礼高','男','20');insertintostudentvalues('104221032','桂畅','男','21');insertintostudentvalues('104221009','胡康','男','20');insertintostudentvalues('104221013','欧阳红','女','21');insertintostudentvalues('104221024','丁阳','男','22');insertintostudentvalues('104221025','李开','男','22');insertintostudentvalues('104221033','冯晓剑','男','21');insertintostudentvalues('104221014','张志林','男','23');insertintostudentvalues('104221012','陈佩','女','21');insertintostudentvalues('104221023','徐桂荣','女','22');--向老师表中插入数据insertintoteachervalues('38001','王忠友','博士','单片机,’1001’insertintoteachervalues('38002',,’1002’insertintoteachervalues('38003',,’1003’insertintoteachervalues('38004',,’1004’insertintoteachervalues('38005',,’1005’insertintoteachervalues('38006',,’1006’--向实验室表中插入数据insertintolaboratoryvalues('单片机工程','1001','38001','2','LG1F');insertintolaboratoryvalues('音视频工程','1002','38002','2','LG2F');insertintolaboratoryvalues('数据库工程','1003','38003','2','LG3F');insertintolaboratoryvalues('机器人工程','1004','38004','2','LG4F');insertintolaboratoryvalues('网站建设工程','1005','38005','1','TS1F');insertintolaboratoryvalues('C#工程','1006','38006','1','TS2F');--向器材表中插入数据insertintoequipmentvalues('投影仪','1002001','1',’1002’,insertintoequipmentvalues('pc机','1002002','3',’1002’,insertintoequipmentvalues('单片机板子','1001001','4',’1001’,insertintoequipmentvalues('pc机','1001002','2',’1001’,insertintoequipmentvalues('小型机器人','1004001',’1004’insertintoequipmentvalues('pc机','1004002','4',’1004’insertintoequipmentvalues('芯片','1004003','7',1004’’--向工程表中插入数据insertintoteskvalues('104221028','1001','单片机学习');insertintoteskvalues('104221032','1001','单片机学习');insertintoteskvalues('104221028','1002','C语言学习');insertintoteskvalues('104221013','1002','C语言学习');insertintoteskvalues('104221014','1004','java学习');insertintoteskvalues('104221024','1003','数据库学习');insertintoteskvalues('104221012','1005','网站建设学习');studentteacherlaboratoryEquipmenttesk数据库ST关系图1.3查询与更新单表查询查询参与实验室学生的信息Select*Fromstudent连接查询--C查询实验室成员信息selectstudent.学生学号,sname学生姓名,性别,年龄,tesk.实验室编号,所研究任务fromstudent,teskwherestudent.学生学号=tesk.学生学号嵌套查询查询管理员的信息selectstudent.学生学号,student.sname管理员姓名,性别,年龄,tesk.实验室编号所处实验室编号fromstudent,equipmenty,teskWherestudent.snamein(selecty.snameFromequipmenty)andstudent.学生学号=tesk.学生学号groupbystudent.学生学号,student.sname,性别,年龄,tesk.实验室编号集合查询//union表示去并集，intersect表示取交集except表示差集查询既参加了实验室编号为1001和参加了实验室编号为1002的学生信息Selecty.学生学号,student.sname学生姓名,student.年龄,student.性别,y.实验室编号,y.所研究任务Fromtesky,studentWherey.实验室编号='1001'andy.学生学号=student.学生学号UnionSelectx.学生学号,student.sname学生姓名,student.年龄,student.性别,x.实验室编号,x.所研究任务Fromteskx,studentWherex.实验室编号='1002'andx.学生学号=student.学生学号插入数据比方说要在学生表中插入一个新的数据格式如下insertinto表名values需要插入的值Insertintostudentvalues('104221001','小红','女','20');修改数据比方说陈佛格式如下update表名set你要设置的数据UpdateteacherSet老师Where老师编号='38003';删除数据比方说小红退出实验室编号为1001的实验室，所以要在相应表中把小红的记录删掉格式如下deletefrom表名【where<条件>】DeleteFromstudentWhere学生学号='104221001'and实验室编号='1001';1.4索引与视图建立索引源代码制定索引的缺省值是升序排列比方说现在要在teacher表上按老师编号升序建立索引Createuniqueindexteacheronteacher(老师编号ASC)删除索引deleteindex索引名建立视图源代码比方说现在在学生表上建立1001实验室视图CreateviewIS_equipmentASSelect*FromequipmentWhere实验室编号='1001';1.5存储过程建立存储过程语句比方说现在建立一个存储过程，调用存储可以查询实验室成员信息Ifexists(selectnamefromsysobjectswherename='p_out'andtype='p')Dropprocedurep_outGoCreateprocedurep_out@codechar(10)AsSelectstudent.学生学号,sname成员姓名,性别,tesk.实验室编号,tesk.所研究任务,laboratory.实验室名称Fromstudent,tesk,laboratoryWherestudent.学生学号=@codeandtesk.学生学号=@codeandlaboratory.实验室编号=tesk.实验室编号Groupbystudent.学生学号,sname,性别,tesk.实验室编号,tesk.所研究任务,laboratory.实验室名称Go调用如下：UselabExecutep_out104221028无参存储过程useLABIfexists(selectnamefromsys.objectswherename='p_out1'andtype='p')Dropprocedurep_out1GoCreateprocedurep_out1asselectlaboratory.老师编号,老师姓名,学历,工程,工资,老师,laboratory.实验室名称所带实验室,laboratory.实验室编号所带实验室编号fromlaboratory,teacherwherelaboratory.老师编号=teacher.老师编号go调用如下：UselabExecutep_out11.6内嵌表值函数返回由一个SELECT语句得到的结果表值的函数称为内嵌表值函数(InlineTable-ValuedFunctions)。uselabifexists(selectnamefromsysobjectswherename='f_manage')dropfunctionf_managegocreatefunctionf_manage(@lcodechar(10))returnstableasreturn(selectstudent.学生学号,sname成员姓名,性别,年龄,tesk.实验室编号,所研究任务 fromstudent,tesk wherestudent.学生学号=@lcodeandtesk.学生学号=@lcode groupbystudent.学生学号,sname,性别,年龄,tesk.实验室编号,所研究任务)s