数据库作业题目.doc

作业一：ER设计题目一：用ER图可以表达下列哪些数据完整性约束，不能表达哪些约束？能表达的给出ER图。1. 每门课选课人数不能低于10个，不能高于100个答：不能表达约束课程名2. 课程名是唯一的课程3. 不能供应不存在的零件零件号零件名供应商名零件号供应零件供应商4. 性别只能为男或女答：不能表达约束课程号姓名课程名课程号5. 每个学生都必须得选课学号选课零件学生6. 学生可以参加多个社团，但所参加的社团的活动时间必须不同答：不能表达约束7. 学生可以参加多个项目，参加不同的项目其指导老师也不同指导参加老师项目学生题目二：解答以下问题1. 列举聚集、弱实体、细化/泛化的实用例子，并用ER图表示出来。聚集：客户签订合同与采购产品之间是聚集关系弱实体：下图中教科书属于弱实体细化 /泛化：家俱与（桌子、椅子）属于细化/泛化关系厂家名称家俱ISA椅子桌子编号编号2. 已知有如下关系模式：E1(a1, a2, a3)，E2(a3, a4)， E3(a5, a6)， E4(a3, a5, a7)，其中带下划线的属性标识为所在关系模式的主码。试画出相应的E-R图，使得可以从该E-R图推导出上述关系模式。 E-R图如下：题目三：考虑设计一个关系数据库，它要存储以下信息：l 教师有教工号、教工名、职称；项目有项目号、项目名称、项目类型、起始年份、截至时间、资助额；学生有学号、学生名、年龄、学位。l 学生分为本科生和研究生，老师按职称可以分为讲师、副教授、教授，副教授以上职称的可以作为研究生的导师。l 一个教工可以负责多个项目；每个项目只能有一个负责人；一个老师可以参与多个项目；一个本科生只能参与一个项目，一个研究生学生可以参与多个项目；一个项目可以有多个学生和老师参与；学生参与项目时必须（如果改为可以呢？）有一个老师作为他的指导老师。E-R图如下：题目四：下面是一张采购订单的票据，根据上面列出的信息，给出其实体联系模型。日期编号业务员名称提交供应商订单采购货品货品名称状态作业二：关系代数题目一：S(SNO, SNAME, STATUS, CITY)P(PNO, PNAME, COLOR, WEIGHT, CITY)J(JNO, JNAME,CITY)SPJ(SNO, PNO, JNO, QTY)S表示供应商，各属性依次为供应商号，供应商名，供应商状态值，供应商所在城市；P表示零件，各属性依次为零件号，零件名，零件颜色，零件重量，零件存放的城市；J表示工程，各属性依次为工程号，工程名，工程所在城市；SPJ表示供货关系，各属性依次为供应商号，零件号，工程号，供货数量。基于以上SPJ关系模式用关系代数表达查询：1. 求向北京的工程供应了红色零件的供应商姓名。S.SNAME(sS.SNO=SPJ.SNOP.COLOR= 红色J.CITY=北京 ( SPJSPJ)2. 求只向北京的工程供应零件的供应商姓名。S.SNAME(sS.SNO=SPJ.SNOJ.CITY=北京 ( SJSPJ)3. 求至少供应了两种不同零件的供应商姓名。S.SNAME(S (sX.SNO=Y.SNOX.PNO Y.PNO ( X (SPJ) Y (SPJ) ) )4. 求没有供应任何零件的供应商姓名。S.SNAME(S (S.SNO(S)- S.SNO(SPJ) )5. 求供应了所有零件的供应商姓名。S.SNAME(S (SPJ.SNO,SPJ.PNO( SPJ) P.PNO (P) )6. 求供应了所有红色零件的供应商姓名。S.SNAME(S (SPJ.SNO,SPJ.PNO( SPJ) P.PNO (sP.COLOR=红色 (P) )7. 求供应了s1号供应商所供应的所有零件的供应商的供应商号。SNO, PNO (SPJ) PNO (s SNO=s1( SPJ)8. 求和s1号供应商所供应的零件完全相同的供应商的供应商号。SNO (SPJ) SNO ( SNO (SPJ) PNO (s SNO=s1 (SPJ) SNO, PNO (SPJ) ) SNO(SNO, PNO (SPJ) SNO(SPJ) PNO (s SNO=s1(SPJ) )题目二：对于关系R(A, B, C)，用关系代数来检验A是否取值唯一。关系R 并且R 中元组 a, b, c 是唯一的，则：sX.A=Y.A (X.B Y.B X.C Y.C) ( X (R) Y(R) 结果如果恒定为1 ，说明A 的取值是唯一的。作业三：基本SQLS(SNO, SNAME, STATUS, CITY)P(PNO, PNAME, COLOR, WEIGHT, CITY)J(JNO, JNAME,CITY)SPJ(SNO, PNO, JNO, QTY)S表示供应商，各属性依次为供应商号，供应商名，供应商状态值，供应商所在城市；P表示零件，各属性依次为零件号，零件名，零件颜色，零件重量，零件存放的城市；J表示工程，各属性依次为工程号，工程名，工程所在城市；SPJ表示供货关系，各属性依次为供应商号，零件号，工程号，供货数量。基于以上SPJ关系模式用SQL完成下面的操作：1. 求没有供应p1和p2两种零件的供应商姓名。select SNAMEfrom Swhere not exists ( select SPJ.SNOfrom SPJwhere S.SNO = SPJ.SNO and ( PNO=p1 or PNO= p 2) 2. 列出所有供应商的信息，包括供应商姓名、所供应的零件名（没有供应零件的供应商也要列出，最后结果中不要出现重复元组）select distinct SNAME,T1.PNAMEfrom S right outer join ( select SPJ.SNO,P.PNAMEfrom SPJ,Pwhere SPJ.PNO=P.PNO) as T1 on S.SNO=T1.SNO3. 求只向与自己位于不同城市的工程供应零件的供应商姓名。select distinct T1.SNO,T1.SNAMEfrom S T1, SPJ T2,J T3where T1.SNO=T2.SNO and T2.JNO=T3.JNO and T1.CITY=T3.CITYexceptselect distinct T4.SNO,T4.SNAMEfrom S T4, SPJ T5,J T6where T4.SNO=T5.SNO and T5.JNO=T6.JNO and T4.CITY=T6.CITY4. 求只向与自己位于相同城市的工程供应零件的供应商姓名。 select distinct T4.SNO,T4.SNAMEfrom S T4, SPJ T5,J T6where T4.SNO=T5.SNO and T5.JNO=T6.JNO and T4.CITY=T6.CITYexceptselect distinct T1.SNO,T1.SNAMEfrom S T1, SPJ T2,J T3where T1.SNO=T2.SNO and T2.JNO=T3.JNO and T1.CITY!=T3.CITY5. 求供应了所有零件的供应商姓名。select S.SNAMEfrom Swhere not exists (select P.PNOfrom Pwhere not exists ( select *from SPJwhere SPJ.SNO=S.SNO and SPJ.PNO=P.PNO) )6. 求供应了所有红色零件的供应商姓名。select S.SNAMEfrom Swhere not exists (select P.PNOfrom Pwhere not exists ( select *from SPJwhere SPJ.SNO=S.SNO and SPJ.PNO=P.PNOand P.COLOR=红)7. 列出每个城市的工程所使用的零件总的数量。select sum(J.QTY)from SPJ,Jwhere SPJ.JNO=J.JNOgroup by J.CITY8. 按零件数量总和的降序列出每项工程所使用的每种红色零件的总的金额（工程可以向不同的供应商购买同一零件，总金额单价*供货数量）。 select J.JNO , sum(SPJ.QTY)*J.PRICEfrom SPJ , Jwhere SPJ.JNO=J.JNO and J.COLOR= 红色group by J.JNOorder by sum(SPJ.QTY)9. 求供应零件数量最多的供应商姓名。select T1.SNAMEfrom S T1, ( select SPJ.SNOfrom SPJgroup by SPJ.SNOhaving sum(SPJ.QTY)=all (select sum(T1.QTY)from SPJ T1group by T1.SNO) T2where T1.SNO=T2.SNO10. 求每个城市中供应零件数量最多的供应商姓名。 select T3.SNAME,T2.MAXSUMQTYfrom(select S.SNO,sum(SPJ.QTY) as SUMQTYfrom SPJ,Swhere SPJ.SNO=S.SNOgroup by S.SNO) T1,(select max(T4.SUMQTY) AS MAXSUMQTYfrom(select S.CITY,S.SNO,sum(SPJ.QTY) as SUMQTYfrom SPJ,Swhere SPJ.SNO=S.SNOgroup by S.CITY, S.SNO) T4group by T4.CITY) T2,S T3where T1.SNO=T3.SNO and T2.MAXSUMQTY=T1.SUMQTY（或者使用with 子句， Oracle9i 版本后支持， sqlserver 不支持）with T1(CITY,SNO,SUMQTY) asselect S.CITY,S.SNO,sum(SPJ.QTY)from SPJ,Swhere SPJ.SNO=S.SNOgroup by S.CITY, S.SNOwith T2(MAXSUMQTY) asselect max(T1.SUMQTY)from T1group by T1.CITYselect T3.SNAMEfrom T1,T2,S T3where T1.SNO=T3.SNO and T2.MAXSUMQTY=T1.SUMQTY作业四：关系规范化题目一：分别写出关系代数和SQL语句，验证关系R(ABC)上AB和AB是否成立。解：验证AB关系代数：如果sX.A=Y.A X.B Y.B( X (R) Y(R)结果如果恒定为0 ，则A B 成立；否则不成立。SQL验证：create assertion AtoB check(not exists(select Afrom Rgroup by Ahaving count(distinct B) 1)题目二：R(ABCDE), F = ABC, BD, CE, CEB, ACB。 给出其候选码 判断范式级别 分别给出保持无损连接和函数依赖的分解 答：1. 出现在左边的属性： A,B,C,E出现在右边的属性： B,C,D,E左部属性： A右部属性： D双部属性： B,C,E所有候选码为： AB,AC 2. 由于无集合属性，所以属于1NF;由于有B D, C E，故存在非主属性对码的部分依赖，所以不属于2NF 3. 先求F 的最小覆盖无冗余化：检查AC B， G=F AC B= AB C, B D, C E, CE B(AC) G+=A,C,E,B,D B A,C,E,B,D=(AC) G+ ，所以可以从F 删除依赖A C B题目三：已知R(ABCD)的封闭属性集是，AB和ABCD,给出R的函数依赖集题目四：R(ABCDE), 给出下面函数依赖集在S(ABCD)上的投影F=AB D,AC E,BC D,D A, E B解：由AC E，E B 得出AC B由AC D，D A 得出AC A 属于平凡依赖由BC D ，D A 得出BC A所以 BC A, AC B 为F 在S(ABCD) 上的投影作业五：数据库综合报告报告一：要求：写一篇综述报告，可以是关于某项数据库实用技术的，也可以是数据库在所从事行业中的应用情况，或者是个人关于大数据产业的认知。个人对大数据产业的认知近两年最热门的技术话题就是“大数据”和“互联网+”，以前总以为大数据就是对数据库信息的深入挖掘，再加以利用。最近在北京大学进修了数据库的课程，其中有一课主门讲大数据的。课外我又查阅了互联网和图书，下了些功夫了解了一下大数据，使我对于大数据又有了新的认识。最早提出大数据时代到来的是麦肯锡： “数据，已经渗透到当今每一个行业和业务职能领域， 成为重要的生产因素。人们对于海量数据的挖掘和运用， 预示着新一波生产率增长和消费者盈余浪潮的到来。”业界（ IBM 最早定义）将大数据的特征归纳为4 个“V”（量Volume，多样Variety，价值Value，速Velocity），或者说特点有四个层面：第一，数据体量巨大。大数据的起始计量单位至少是P（1000 个T）、E（100 万个T）或Z（10 亿个T）；第二，数据类型繁多。比如，网络日志、视频、图片、地理位置信息等等。第三，价值密度低，商业价值高。第四，处理速度快。最后这一点也是和传统的数据挖掘技术有着本质的不同。大数据的核心就是“数据挖掘”吗？这也是我原来这么认为的。大数据的核心在于预测，可以发发现很多依靠人的常识和常规信息处理手段无法发现的一些规律性，从而增加决策的科学性和准确性程度。大规模数据放在一起，确实能达到这种效果。可是，这些不是数据挖掘技术做的事情吗，为什么还要多此一举的加上一个“大”呢？我的理解这个大，除了表示数据量很大之外，也表示这是一个经过整合以后形成的一个庞然大物。”整合“是大数据的命脉，数据不整合在一起，即使再多，也无法做关联分析。但是，”整合“真的就知识为了给数据挖掘提供原料吗？如果整个社会的数据整合在了一起，这就是意味着所有的机构和个人可以通过这个数据平台互联互通，整个人类社会的运行规则将发生天翻地覆的变化。政府可以清晰地知道管理对象的所有细节信息，现在的层层审批都可以不需要，很多社会问题刚一露头就会被发现，从而消除在萌芽状态；公民也可以监督政府运作的每一个细节，政府在民众面前基本裸体，也不敢玩太多的把戏，商家也可以了解顾客的细节需求，所以投其所好，消费者也可以了解商家的细节信息，从而避免上当受骗。总之，真正的”大数据时代“是一个数据驱动一切的社会，也是一个所有人都在聚光灯下的社会，因此人类社会发展过程中由于信息的不对称导致的很多问题都可能得到部分地解决，。“大数据时代”是人类生存方式将要发生巨大变革的时代，而不仅仅是一个会使用数据挖掘技术，实现信息资源二次利用的时代。报告二：数据库实践，自己设计表结构和查询，展示组合索引或覆盖索引对查询性能的提升。根据我公司客户服务系统需求，设计了如下客户信息表结构，表名称（gerenkehu）名称代码数据类型键必填项注解居民客户idgr_idvarchar(20)主键TRUE主键集团统一编号gr_jt_bhvarchar(20)姓名gr_xmnvarchar(50)索引TRUE性别gr_xb_idvarchar(6)TRUE身份证号gr_sfzvarchar(18)索引证件类型gr_zjlx_idvarchar(6)其他证件号gr_zj_idvarchar(30)地址gr_dznvarchar(70)索引邮政编码gr_yzbmvarchar(6)家庭电话gr_jtdhvarchar(20)索引手机gr_sjvarchar(20)索引手机2gr_sj2varchar(20)索引华润项目增加小灵通gr_xiaolingtongvarchar(20)索引电子邮箱gr_dzyxvarchar(60)工作单位gr_gzdwnvarchar(60)职务gr_zwva