数据库设计

课内实验报告

课 程 名： 数据库原理与应用 任课教师： 徐润森 专 业： 信息工程 学 号： 08000101 姓 名： 刘冠丽

二○一○ 至二○一一 年度 第 1 学期

南京邮电大学 经济与管理学院

《数据库原理与应用》课程实验第 1 次实验报告 实验内容及基本要求： 实验项目名称：SQL语言的使用 实验类型： 验证 每组人数： 1 实验内容及要求： 在数据库环境SQL SERVER或ORACLE中了解SQL的概念、语法和使用方法，熟练使用SQL进行建立数据库、表、索引、视图，及进行查询和更新等操作。 实验结果： 1.建立课本P75的SPJ数据库--建立S表、P表、J表和SPJ表。 （1）P表建立 输入内容： P表显示： （2）J表的建立： 输入内容： J表显示： 视图创建： （3）S表建立： 输入内容：

S表显示： （4）SPJ表的建立： 输入内容和视图建立： SPJ表的显示： 2．课本P75（1）求供应工程J1零件的供应商号码SNO； （2）求供应工程J1零件P1的供应商号码SNO； （3）求供应工程J1零件为红色的供应商号码SNO； （4）求没有使用天津供应商生产的红色零件的工程号JNO； （5）求至少了供应商S1所供应的全部零件的工程号JNO； 3．课本P127第五题 （1）找出所有供应商的姓名和所在城市； （2）找出所有零件的名称、颜色、重量； （3）找出使用供应商S1所供应零件的工程号码； （4）找出工程项目J2使用的各种零件的名称及其数量； （5）找出上海厂商供应的所有零件号码； （6）找出使用上海产的零件的工程名称； （7）找出没有使用天津产的零件的工程号码； （8）把全部红色零件的颜色改成蓝色； （9）由S5供给J4零件P6改成由S3供应，请做必要的修改； （10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录； （11）请将（S2，J6，P4，200）插入供应情况关系。 4.课本P128第11题，针对视图完成下列查询： （1）找出三建工程项目使用的各种零件代码及其数量； （2）找出供应商S1的供应情况。 5．职工表Z（职工号zno，姓名zname，年龄zage，性别zsex） 社会团体（编号sno,名称sno,负责人sfzr,活动地点saddress） 参加（职工号zno,编号sno,参加日期DAY） 职工表Z的建立： 社会团体S表建立： 参加ZS表的建立： 题目（1）.查找没有参加唱歌队和篮球队的职工号和姓名； （2）求参加人数量最少的社会团体的名称和参加人数； （3）求每个职工参加的社团总数； （4）求成员年龄全部在40岁以上的社团名称。

《数据库原理与应用》课程实验第 2 次实验报告 实验内容及基本要求： 实验项目名称：小型数据库设计 实验类型： 设计 每组人数： 1 实验内容及要求： 按照数据库设计流程开发实现一个小型的数据库应用系统 实验结果： 一、小型数据库描述： 现有一个全国计算机考试查询、成绩管理数据库。由学生表(student)、考试项目(E)、考试成绩(Grade)、监考老师(Eteacher)组成. 学生表(student)由考生身份证号 (Sno)、姓名(Sname)、准考证号(Eno)、性别(Ssex)组成; 考试项目(E)由准考证号(Eno)、考试内容(Ecourse)、笔试考试时间(Ebt)、上机考试时间(Ect)、考试教室(Eroom)、考试校区(Eschool)组成；考试成绩(Grade)由准考证号(Eno)、笔试成绩(GB)、上机成绩(GC)组成；监考老师(Eteacher)由监考编号(Tno)、监考教室(Eroom)、监考时间(Et)、老师姓名(Etname)、监考内容(Ecourse)组成。 学生可以报名参加考试，每个学生只能报名考试一项内容的考试。一项内容的考试可以被多名学生报考。每个学生有对应的考试成绩，允许有报名但没参加考试的学生的成绩为空。监考老师可以监督多个考生，一个考生也可以被多个老师监督。 二、需求分析 1．全国计算机考试查询系统进行分析DFD图 全国计算机考试成绩查询管理系统 学生查询考试时间、地点 学生查询考试成绩 监考老师查询监考时间地点 阅卷老师登记分数 2.性能分析:分为考试前考生和监考老师对表查询相关信息和阅卷老师登记分数及考生成绩查询表两个总的功能。因此对数据的更新修改和查询操作为主要的数据操作。 三、 概念模型 1．建立每个表格的数据字典 TABLE GRADE TABLE SE TABLE E TABLE STUDENT TABLE ETEACHER TABLE DUTY 四、 E-R图 1．分E-R图 学生和报名科目的部分E-R图 Sno Ssex Sname student n GB Grade GC E 1 Eschool Eno EBT ECT Ecourse Eroom 2．监考老师和报名学科的E-R图 Tno EtnamEt Eteacher n Ecourse 负责m E Eroom Eschool Eno EBT ECT 3．总E-R图 Sno Ssex Sname Tno Etname n student n GB Grade GC 监考 m Eteacher Et n 负责1 E m Ecourse Eroom Eschool Eno EBT ECT 五、 E-R图转换关系模式 由此建立起来的关系模型： Student(Sno,Sname,Ssex) SE（Sno，Eno） Grade(Eno,GB,GC) E(Eno,Ecourse,Eroom,EBT,ECT,Eschool) Eteacher(Tno,Etname,Et,Eroom,Ecourse) Duty(ENO,TNO,Eroom,Ecourse) 六、 范式分析 1、Student(Sno,Sname,Ssex) Snosname,SnoSsex 码:Sno,非主属性:Sname Ssex 不存在部分函数依赖不存在传递函数依赖 此表满足Student ∈3NF 2.SE(Sno,Eno) SnoEno EnoSno 码:Sno或者Eno 不存在非主属性 不存在部分函数依赖也不存在传递函数依赖 此表满足SE ∈3NF 3.E(Eno,Ecourse,Eroom,EBT,ECT,Eschool) ENOEcourse, ENO eroom, ENO ebt, ENO ect, ENO eschool Key:Eno,非主属性: Ecourse,Eroom,EBT,ECT,Eschool 不存在部分函数依赖也不存在传递函数依赖 E∈3NF 4.Grade(Eno,GB,GC) Eno GB, EnoGC Key:Eno,非主属性: GB,GC 不存在部分函数依赖也不存在传递函数依赖 Grade∈3NF 5. Eteacher(Tno,Etname,Eroom,Et,Ecourse) TnoEtname ,TnoEroom TnoEt, TnoEcourse 码:Tno,非主属性: Etname,Eroom,Et,Ecourse 不存在部分函数依赖也不存在传递函数依赖 Eteacher∈3NF 6. Duty(ENO,TNO,Eroom,Ecourse) (ENO,TNO)Eroom Ecourse 码:(ENO,TNO),非主属性:Eroom,Ecourse 不存在部分函数依赖也不存在传递函数依赖 Duty∈3NF 七、 设计用户子模式 对于学生，有STUDENT(SNAME,SEX),E(ENO,ECOURSE,EROOM,EBT,ECT,ESCHOOL)表的查询权利，只能看到这两个表的相关学生信息。并且只能查看GRADE(ENO, ZG)[表示总成绩,看不到具体成绩] 对于监考老师,只能查询到自己监考的ETEACHER和查看监考的考生的准考证号和姓名,不具备修改更新、删除等操作的权利，同时对GRADE表没有查看的权利 对于阅卷老师,则只能查看GRADE表,对表进行修改更新操作.阅卷老师只能看到GRADE(ENO,GB,GC) 八、 建立用户视图 1．建立的学生查询视图 2．学生可看到的成绩表： 3．监考老师用户视图 4．阅卷老师用户视图 九、 数据表格建立 1．Student表 2．SE表格 3．E表格 4．Grade表格 5．Eteacher表格 6．Duty表格 十、数据操作查询 1．查找考生是'440602198908252122'所在的教室的监考老师TNO ETNAME 2．插入一组临时安排的监考老师信息并查找至少监考两门考试内容的老师姓名和监考号 INSERT INTO ETEACHER(TNO,ETNAME,EROOM,ET,ECOURSE) VALUES('25','卢丽丽','20030027','20100919','三级PC技术'); 3．建立显示准考证号、学生姓名和考试内容的视图，并且查询各个考试内容的人数 4.将所有考生的姓名和准考号码对应的总成绩表示(0.6*GB+0.4*GC) 5.删除姓苏的学生姓名 6.显示考试科目的GB GC的平均成绩,按照降序排 7．查找每个考试内容的最高分数 8．查找在仙林校区考试的学生人数和学生准考证号 9．建立一个角色,学生只能进行成绩表的查询,而老师对成绩表可以进行查询,删除和更新的权限.查询成绩期间过了以后，对这些权利进行收回. 成绩评定： 该生对待本次实验的态度 □认真 □良好 □一般 □比较差。 本次实验的过程情况 □很好 □较好 □一般 □比较差 对实验结果的分析 □很好 □良好 □一般 □比较差 文档书写符合规范程度 □很好 □良好 □一般 □比较差 综合意见： 成绩 指导教师签名 徐润森 日期 CREATE TABLE S

( SNO CHAR(10) PRIMARY KEY, SNAME CHAR(10), STATUS INT, CITY CHAR(10) );

CREATE TABLE P

( PNO CHAR(10) PRIMARY KEY, PNAME CHAR(10), COLOR CHAR(10), WEIGHT INT );

CREATE TABLE J

( JNO CHAR(10) PRIMARY KEY, JNAME CHAR(10), CITY CHAR(10) );

CREATE TABLE SPJ ( SNO CHAR(10), PNO CHAR(10), JNO CHAR(10), QTY INT,

PRIMARY KEY (SNO,JNO,PNO),

FOREIGN KET (SNO) REFERENCES S(SNO), FOREIGN KET (JNO) REFERENCES J(JNO), FOREIGN KET (PNO) REFERENCES P(PNO) );

INSERT INTO S

VALUES ('S1','INSERT INTO S

VALUES ('S2','INSERT INTO S

VALUES ('S3','INSERT INTO S

VALUES ('S4','INSERT INTO S

VALUES ('S5','INSERT INTO P

VALUES ('P1','INSERT INTO P

VALUES ('P2','INSERT INTO P

VALUES ('P3','INSERT INTO P

VALUES ('P4','精益',20,'天津'); 盛锡',10,'北京'); 东方红',30,'北京'); 丰泰盛',20,'天津'); 为民',30,'上海'); 螺母','红',12); 螺栓','绿',17); 螺丝刀','蓝',14); 螺丝刀','红',14);

INSERT INTO P

VALUES ('P5','凸轮','蓝',40); INSERT INTO P

VALUES ('P6','齿轮','红',30); INSERT INTO J

VALUES ('J1','INSERT INTO J

VALUES ('J2','INSERT INTO J

VALUES ('J3','INSERT INTO J

VALUES ('J4','INSERT INTO J

VALUES ('J5','INSERT INTO J

VALUES ('J6','INSERT INTO J

VALUES ('J7','INSERT INTO SPJ

VALUES ('S1','P1','J1',200); INSERT INTO SPJ

VALUES ('S1','P1','J3',100);

三建','北京'); 一汽','长春'); 弹簧厂','天津'); 造船厂','天津'); 机车厂','唐山'); 无线电厂','常州'); 半导体厂','南京'); INSERT INTO SPJ

VALUES ('S1','P1','J4',700); INSERT INTO SPJ

VALUES ('S1','P2','J2',100); INSERT INTO SPJ

VALUES ('S2','P3','J1',400); INSERT INTO SPJ

VALUES ('S2','P3','J2',200); INSERT INTO SPJ

VALUES ('S2','P3','J4',500); INSERT INTO SPJ

VALUES ('S2','P3','J5',400); INSERT INTO SPJ

VALUES ('S2','P5','J1',400); INSERT INTO SPJ

VALUES ('S2','P5','J2',100); INSERT INTO SPJ

VALUES ('S3','P1','J1',200); INSERT INTO SPJ

VALUES ('S3','P3','J1',200); INSERT INTO SPJ

VALUES ('S4','P5','J1',100);

INSERT INTO SPJ

VALUES ('S4','P6','J3',300); INSERT INTO SPJ

VALUES ('S4','P6','J4',200); INSERT INTO SPJ

VALUES ('S5','P2','J4',100); INSERT INTO SPJ

VALUES ('S5','P3','J1',200); INSERT INTO SPJ

VALUES ('S5','P6','J2',200); INSERT INTO SPJ

VALUES ('S5','P6','J4',500);