数据库设计.doc

课内实验报告课 程 名： 数据库原理与应用 任课教师： 徐润森 专 业： 信息工程 学 号： 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 GRADETABLE SETABLE ETABLE STUDENTTABLE ETEACHERTABLE DUTY四、 E-R图1分E-R图学生和报名科目的部分E-R图studentEGraden1SnoSnameSsexEBTEnoECTEschoolGCGBEcourseEroom2监考老师和报名学科的E-R图EEteacher负责考nmEroomEBTEcourseEnoECTEschoolTnoEtEtname3总E-R图studentEEteacherGrade负责考n1nmSnoSnameSsexEroomEBTEcourseEnoECTEschoolGCGBTnoEtEtname监考mn五、 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) Snosname,SnoSsex码:Sno,非主属性:Sname Ssex不存在部分函数依赖不存在传递函数依赖此表满足Student 3NF2.SE(Sno,Eno) SnoEno EnoSno码:Sno或者Eno 不存在非主属性不存在部分函数依赖也不存在传递函数依赖此表满足SE 3NF3.E(Eno,Ecourse,Eroom,EBT,ECT,Eschool) ENOEcourse, ENO eroom, ENO ebt, ENO ect, ENO eschoolKey:Eno,非主属性: Ecourse,Eroom,EBT,ECT,Eschool不存在部分函数依赖也不存在传递函数依赖E3NF4.Grade(Eno,GB,GC)Eno GB, EnoGC Key:Eno,非主属性: GB,GC不存在部分函数依赖也不存在传递函数依赖Grade3NF5. Eteacher(Tno,Etname,Eroom,Et,Ecourse)TnoEtname ,TnoEroom TnoEt, TnoEcourse码:Tno,非主属性: Etname,Eroom,Et,Ecourse不存在部分函数依赖也不存在传递函数依赖Eteacher3NF6. Duty(ENO,TNO,Eroom,Ecourse)(ENO,TNO)EroomEcourse码:(ENO,TNO),非主属性:Eroom,Ecourse不存在部分函数依赖也不存在传递函数依赖Duty3NF七、 设计用户子模式对于学生，有STUDENT(SNAME,SEX),E(ENO,ECOURSE,EROOM,EBT,ECT,ESCHOOL)表的查询权利，只能看到这两个表的相关学生信息。并且只能查看GRADE(ENO, ZG)表示总成绩,看不到具体成绩对于监考老师,只能查询到自己监考的ETEACHER和查看监考的考生的准考证号和姓名,不具备修改更新、删除等操作的权利，同时对GRADE表没有查看的权利对于阅卷老师,则只能查看GRADE表,对表进行修改更新操作.阅卷老师只能看到GRADE(ENO,GB,GC)八、 建立用户视图1 建立的学生查询视图 2 学生可看到的成绩表： 3监考老师用户视图 4阅卷老师用户视图 九、 数据表格建立 1Student表2SE表格3E表格4Grade表格5Eteacher表格6Duty表格十、数据操作查询1 查找考生在的教室的监考老师TNO ETNAME2插入一组临时安排的监考老师信息并查找至少监考两门考试内容的老师姓名和监考号INSERTINTO 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 SVALUES (S1,精益,20,天津);INSERT INTO SVALUES (S2,盛锡,10,北京);INSERT INTO SVALUES (S3,东方红,30,北京);INSERT INTO SVALUES (S4,丰泰盛,20,天津);INSERT INTO SVALUES (S5,为民,30,上海);INSERT INTO PVALUES (P1,螺母,红,12);INSERT INTO PVALUES (P2,螺栓,绿,17);INSERT INTO PVALUES (P3,螺丝刀,蓝,14);INSERT INTO PVALUES (P4,螺丝刀,红,14);INSERT INTO PVALUES (P5,凸轮,蓝,40);INSERT INTO PVALUES (P6,齿轮,红,30);INSERT INTO JVALUES (J1,三建,北京);INSERT INTO JVALUES (J2,一汽,长春);INSERT INTO JVALUES (J3,弹簧厂,天津);INSERT INTO JVALUES (J4,造船厂,天津);INSERT INTO JVALUES (J5,机车厂,唐山);INSERT INTO JVALUES (J6,无线电厂,常州);INSERT INTO JVALUES (J7,半导体厂,南京);INSERT INTO SPJVALUES (S1,P1,J1,200);INSERT INTO SPJVALUES (S1,P1,J3,100);INSERT INTO SPJVALUES (S1,P1,J4,700);INSERT INTO SPJVALUES (S1,P2,J2,100);INSERT INTO SPJVALUES (S2,P3,J1,400);INSERT INTO SPJVALUES (S2,P3,J2,200);INSERT INTO SPJVALUES (S2,P3,J4,500);INSERT INTO SPJVALUES (S2,P3,J5,400);INSERT INTO SPJVALUES (S2,P5,J1,400);INSERT INTO SPJVALUES (S2,P5,J2,100);INSERT INTO SPJVALUES (S3,P1,J1,200);INSERT INTO SPJVALU