学生信息学号姓名年龄性别班级院系.doc

设要建立全校某次学生下乡社会调查活动的信息管理系统。已知这次活动的调查对象为乡镇，每个同学独立行动，可调查一到几个乡镇，同一乡镇也可由多人调查，但每个同学必须对自己调查过的每一个乡镇写出一篇调查报告（文本文件格式），拍摄一张照片。信息管理系统中应包含的数据有： 学生信息：学号、姓名、年龄、性别、班级、院系 乡镇信息：乡镇名、位置、所在县（市）、所在省（市）、人口、人均纯收入(设乡镇可有重名，县市无重名) 调查信息：调查开始日期、调查持续时间（天数）、访问人数、调查报告、照片 要求： 1.试绘出有关实体的E-R图；（6分） 2.将E-R模型转化为关系模式（可添加必要的属性）；（3分） 各个实体转换为关系模型为 学生(学号,姓名,年龄,性别,班级,院系) 乡镇(乡镇编号,乡镇名,位置,县市名,省,人口,收入) 调查(学号,乡镇编号,始日,天数,访问人数,报告,照片) 3.如果建立的关系不符合3NF ，将其转化为3NF;（4分） “乡镇”关系不满足第一范式（位置可分解为所在地经度,纬度），将其转换为1 NF： 乡镇1(乡镇编号,乡镇名,经度,纬度,县市,省,人口,收入) 乡镇1中有传递函数依赖（乡镇编号→县市名，县市名→省），可分解为以下两个关系： 乡镇2(乡镇编号,乡镇名,经度,纬度，县市名,省,人口,收入) 县市（县市名,省） 学生关系不满足第三范式（学号→班级，班级→院系），可分解为以下两个关系： 学生1(学号,姓名,年龄,性别,班级,院系) 班级(班级,院系) 分解后，有下列满足3NF的五个关系： 学生1(学号,姓名,年龄,性别,班级,院系) 班级 (班级,院系) 乡镇2(乡镇编号,乡镇名,经度,纬度，县市名,省,人口,收入) 县市（县市名,省） 调查(学号,乡镇编号,始日,天数,访问人数,报告,照片) 4.写出建立的数据表中每个字段名（可用汉字、英文或拼音）及其数据类型；（3分） 表名：学生1 表名：调查 表名：乡镇2 字段名 类型 学号 C 乡镇编号 C 学号 C 乡镇编号 C 乡镇名 C 姓名 C 始日 D 位置 C 年龄 N 天数 N 县市名 C 性别 C 访问人数 N 省 C 班级 C 报告 通用 人口 N 院系 C 照片 通用 收入 N 表名：班级 表名：县市 经度 N 班级 C 县市名 C 纬度 N 院系 C 省 C 5.用选择、投影、连接符号分别表示：（6分） （1）查询刁查同学调查了2天以上的乡镇名、所在省（市）、县（市） ∏乡镇名，省，县市名(σ姓名=刁查 and 天数=2(学生1 乡镇2 县市名 调查)) （2）查询在一个乡镇访问了3人以上的同学姓名、所在院系、调查的乡镇名； ∏姓名、所在院系，乡镇名(σ访问人数=3(学生1 乡镇2 县市名 调查)) （3）查询调查过湖北省潜江市龙湾镇的同学姓名、性别、所在院系和在该镇的调查天数。∏姓名，性别，院系，天数(σ乡镇名= 龙湾镇 and县市名=潜江 (学生1 班级 乡镇2 县市名 调查)) 6.如果用VFP 6.0设计信息管理系统，试写出根据你所建立的数据表进行以下操作所用的SQL Select语言：（12分，每句各3分） (1)查询全校同学在本次调查中调查过的所有乡镇名及其所在的县（市）。 SELECT 乡镇名, 县市名 FROM乡镇2, 县市, 调查; Where乡镇2.县市名=县市.县市名 and乡镇2.乡镇编号=调查.乡镇编号; Group by调查.乡镇编号 (2)查询全校女同学调查一个乡镇所用的平均天数和在一个乡镇的平均访问人数。 SELECT AVG(持续天数),AVG(访问人数) FROM 学生1，调查; WHERE 学生1.学号=调查.学号 AND 性别=’女’ (3)查询诸葛小亮同学调查的乡镇数、调查总天数和访问的总人数； SELECT Count(*), SUM(天数),SUM(访问人数) FROM学生1, 调查; Where学生1.学号=调查.学号 and姓名=诸葛小亮 Group by 学生1.学号 (4) 查询在湖北省潜江市龙湾镇调查了3天以上的同学姓名、性别、所在院系和调查天数。 SELECT 姓名,性别,院系,天数 From学生1, 班级, 乡镇2,县市, 调查; Where学生1.班级=班级.班级 and乡镇2.县市名=县市.县市名 and; 乡镇2.乡镇编号=调查.乡镇编号 and学生1.学号=调查.学号 and; 县市.县市名=潜江 and乡镇名=龙湾镇 and天数3 第 1 页 共 2 页 乡镇 学生 调查 学号. 性别 姓名. 年龄 班级 院系 开始日期 乡镇名. 持续时间 人口 所在