数据库设计例题解析

问题1需要评分 有关系如下表所示。EMPNOEMPNEAGSEXDEPTNODETAMEZhao0FDCCCian25D1A3Su8MD3CC4Li25FDCC其中,EMPO为员工号,PE为员工姓名,GE为年龄，SEX为员工性别，DEPNO为部门编号,EPNAE为部门名称。1）R是第几范式,为什么? 2)中与否存在异常？若存在，则阐明什么状况下发生。3） 将它分解为高一级范式，分解后的关系能否解决操作异常问题？ 对的答案:1）R关系属于2NF。从表中的数据看,该关系的主键为：员工号EMPNO，或员工名EPAME，由于只有这两列数据不反复。又由于存在着EMPO DEPTNO, DPTNODETNAME,即存在 着非主属性(部门名称）对码(员工号）的传递依赖。2）存在数据冗余、修改异常、插入异常和删除异常,如，部门名称数据冗余,如果没有员工信息，则部门信息无法添加;如果删除一种部门所有员工信息,则也删除了该部门信息;更新一种部门名称复杂。3）分解为R1和RR:（EMPNO， EMPNAM, AGE， SX， DEPNO), 候选码为EMN ，或EMPNAME，选MPN为主键。外部键:TNO R2:(DEPTNO, DEPTNE),候选码为DEPTN，即为主键，外部键：无R1，2均达到了BF范式(决定因素涉及候选码)，消除了上述的多种异常。 问题 2需要评分 有一种应用涉及三个实体集。 实体“商店”的属性有：商店编号,店名，店址，店经理。实体“会员”的属性有：会员编号,会员名，地址。 实体“职工”的属性有:职工编号，职工名,性别，工资。每个商店有若干职工，但每个职工只能服务于一种商店。每个商店有若干会员，每个会员可以属于多种商店。职工参与某商店工作后记录开始工作时间；会员成为商店会员时记录加入时间。(1)试画出反映应用的E图； (2)将ER图转换成关系模式,并指出每个表的主键和外键 对的答案：（1）ER图图省略。（2） 实体转换为关系表商店（商店编号,店名，店址,店经理）会员（会员编号,会员名,地址）职工(职工编号,职工名，性别,工资)联系转换为关系表商店-会员（商店编号,会员编号，会员的加入时间)商店-职工（商店编号,职工编号,开始工作的时间) 主码相似的表可以合并,合并得到:职工(职工编号,职工名，性别,工资，商店编号，开始工作的时间) 最后得到关系模型：商店（商店编号,店名,店址,店经理） 主键：商店编号,外部键:无会员(会员编号,会员名，地址） 主键:会员编号,外部键：无职工（职工编号,职工名，性别，工资，商店编号，开始工作的时间）主键：职工编号,外部键:商店编号商店会员（商店编号，会员编号，会员的加入时间) 主键:（商店编号,会员编号）外部键:商店编号,外部键:会员编号问题 3需要评分 某医院病房的管理如下：有若干科室，一种科室有多种病房,多种医生，一种病房只能属于一种科室;一种病人可以有多种医生治疗,但主管医生只有一种；一种医生只属于一种科室，可负责多种病人的诊治。科室属性有:科室名,科地址，科电话病房属性有：病房号,床位号医生属性有:工作证号，姓名,职称,年龄病人属性有：病历号，姓名，性别,出生日期根据上述描述,完毕如下设计:1)画出E-R,并在图上注明联系类型；2)将实体及联系转换为关系模型,并注明主键和外部键。对的答案: 1)找出所有的实体:科室，病房,医生,病人。根据题意,画出E-R图:省略。实体转换成关系表科室（科室名,科地址，科电话）,主键:科室名病房(病房号,床位号),主键:(病房号,床位号)医生（工作证号，姓名，职称,年龄），主键:工作证号病人（病历号,姓名，性别,出生日期)，主键：病历号实体联系转换成关系表科室-医生(科室名,工作证号），主键:工作证号科室病房(科室名,病房号，床位号),主键:病房号,床位号病房病人(病房号,病历号)，主键：病历号主治（病历号,工作证号），主键：病历号。由于是:联系，也可以是工作证号诊治(病历号,工作证号),主键:（病历号，工作证号）合并主键相似的表，最后得到:科室（科室名,科地址,科电话），主键：科室名病房(病房号，床位号,科室名），主键:（病房号，床位号)，外键：科室名医生（工作证号，姓名，职称，年龄,科室名) ，主键：工作证号，外键:科室名病人（病历号，姓名,性别，病房号,工作证号),主键:病历号，外键：病房号，外键：工作证号诊治(病历号，工作证号）,主键:(病历号，工作证号）,外键:病历号，外键：工作证号问题4 需要评分 图书馆有多种读者和多种图书，一种读者可以借阅多种图书,一种图书可以供多种读者借阅。读者借书后有借出日期和应还日期。读者的属性涉及：读者号、姓名、地址、性别、年龄、单位;图书的属性涉及：书号、书名、作者、出版社、SN、价格。根据上述描述,规定:）画出E-R,并在图上注明联系类型;2)将实体及联系转换为关系模型，并注明主键和外部键。对的答案:)图：省略 2)关系模型为： 读者（读者号,姓名，地址，性别，年龄,单位） 主键：读者号 ， 外部键：无图书（书号,书名,作者,出版社，ISN，价格） 主键:书号 ， 外部键:无借书(读者号,书号，借出日期，应还日期) 主键:(读者号，书号)，外部键:读者号 ；外部键：书号 问题 5 需要评分 既有有关学会、学生、课程、教师的信息如下：学生的属性有：学号、姓名、性别、年龄；课程的属性有:课程号、课程名、学分；教师的属性有：教师号、姓名、职称、专业;学会的属性有：学会名称、成立时间、负责人姓名、会费。假设每门课程可由多位教师讲授,每位教师只讲授一门课程；每个学生可加入多种学会，学生进入学会有一种入会年份。学生可选修多门课程,每个学生选修的每门课程有一种成绩记载。）根据语义画出ER图,并在图上注明联系类型;2）将E图转换成关系模式，并根据属性的现实意义指明主键和外键。所选答案：学生（学号，姓名，性别，年龄)课程（课程号，课程名，学分)教师（教师号,姓名,职称，专业）学会（学会名称,成立时间，负责人姓名，会费 ）选课(学号,课程号，成绩)入会（学号,学会名称，入会年份）对的答案： 1）E-图省略2）关系模式：学生(学号、姓名、性别、年龄) 学号为主键课程（课程号、课程名、学分、课程号) 课程号为主键,，课程号为外键教师(教师号、姓名、职称、专业）教师号为主键学会(学会名称、成立时间、负责人姓名、会费) 学会名称为主键选修(学号、课程号、成绩) 学号和课程号联合为主键,学号和课程号均为外键入会(学号、学会名称、入会年份） 学号和学会名称联合为主键,学号和学会名称为外键问题 需要评分 设有商店和顾客两个实体,“商店”有属性商店编号、商店名、地址、电话,“顾客”有属性顾客编号、姓名、地址、年龄、性别。假设一种商店有多种顾客购物,一种顾客可以到多种商店购物，顾客每次去商店购物有一种消费金额和日期,并且规定每个顾客在每个商店里每天最多消费一次。试画出E-R图,注明属性和联系类型,并将E-R模型转换成关系模式，并规定指出关系模式主码、外码。所选答案：商店(商店编号,商店名,地址,电话）顾客(顾客编号，姓名，地址,年龄，性别)购物（商店编号,顾客编号,消费金额，日期)对的答案:关系R图（略）。关系模型顾客(顾客编号,姓名，地址，年龄,性别),主键：顾客编号商店（商店编号，商店名,地址，电话),主键：商店编号)购物(顾客编号,商店编号,消费日期，消费金额),主键：（顾客编号,商店编号，消费日期），这里考虑到每天消费一次的限制。 外部键：顾客编号,商店编号问题 7 需要评分某公司集团有若干工厂，每个工厂生产多种产品,且每一种产品可以在多种工厂生产,每个工厂按照固定的筹划数量生产产品;每个工厂聘任多名职工,且每名职工只能在一种工厂工作，工厂聘任职工有聘期和工资。工厂的属性有工厂编号、厂名、地址,产品的属性有产品编号、产品名、规格,职工的属性有职工号、姓名。(1)根据上述语义画出E-图;（2)将该E-R模型转换为关系模型；（规定:1和1:n的联系进行合并）（3)指出转换成果中每个关系模式的主码和外码。所选答案: 工厂(工厂编号，厂名，地址)产品(产品编号，产品名,规格)职工（职工号，姓名)聘任（职工号，工厂编号，聘期，工资)生产(工厂编号,产品编号,产品数）对的答案： （1）ER图略 (2）转化后的关系模式如下:工厂(工厂编号,厂名,地址)产品（产品编号,产品名，规格）职工（职工号,姓名 )聘任（职工号，工厂编号，聘期,工资） ,由于工厂与职工是：n的联系，因此主码是职工号生产(工厂编号，产品编号，筹划数量) ,由于工厂与产品是:的联系,因此主码是（工厂编号,产品编号）（3）每个关系模式的主码、外码如下：工厂（工厂编号,厂名，地址）:主码: 工厂编号,外码:无;产品（产品编号,产品名,规格）:主码：产品编号,外码：无；职工（职工号，姓名，工厂编号,聘期,工资)主码：职工号,外码：工厂编号；生产(工厂编号,产品编号，筹划数量） 主码(工厂编号，产品编号）,外码:工厂编号，外码：产品编号 问题8需要评分 假设某商业集团数据库中有一关系模式R如下:R（商店编号，商品编号,商品库存数量,部门编号,负责人)如果规定：(1)每个商店的每种商品只在该商店的一种部门销售；()每个商店的每个部门只有一种负责人;（3)每个商店的每种商品只有一种库存数量。试回答问题（1）根据上述规定，写出关系模式R的基本函数依赖；（2）找出关系模式R的候选码；（3)试问关系模式R最高已经达到第几范式？为什么?(4)如果R不属于N,请将分解成3NF模式集。对的答案: 1)根据题意，F=（商店编号,商品编号)部门编号，(商店编号，部门编号）负责人,（商店编号,商品编号)商品库存数量2）根据F， 求(商店编号，商品编号) 商店编号，商品编号，部门编号,负责人，商品库存数量,因此,R的唯一候选码是(商店编号,商品编号）3)根据F:(商店编号，商品编号)部门编号,而（商店编号,商品编号）商店编号(这是平凡函数依赖)，得到：（商店编号，商品编号）（商店编号,部门编号） (这是根据Armarong的合并原则）根据:(商店编号,商品编号）（商店编号，部门编号),(商店编号，部门编号）负责人得到:(商店编号，商品编号)负责人, 即非主属性“负责人”对候选码(商店编号、商品编号)传递函数依赖。因此R属于2F,不属于3NF。）将R分解成：R1(商店编号，商品编号，商品库存数量，部门编号),候选码：(商店编号，商品编号),R13NR(商店编号,部门编号,负责人），候选码：（商店编号，部门编号)，R2F问题 需要评分 既有如下关系模式：借阅（图书编号,书名，作者名，出版社,读者编号,读者姓名,借阅日期,归还日期)，基本函数依赖集=图书编号(书名,作者名，出版社），读者编号读者姓名，（图书编号,读者编号，借阅日期）归还日期（1)读者编号是候选码吗？（2）写出该关系模式的主码。（3）该关系模式中与否存在非主属性对码的部分函数依赖?如果存在，请写出一种。()该关系模式满足第几范式？并阐明理由。 对的答案： （1)不是。由于它不能决定关系中的每个属性。(2)求(图书编号,读者编号,借阅日期)的闭包，闭包涉及了关系中的每个属性，且任何(图书编号，读者编号，借阅日期) 的子集的闭包都不能涉及关系中的所有属性,因此，（图书编号，读者编号，借阅日期)的关系的一种候选码。()存在。(图书编号,读者编号,借阅日期)书名、（图书编号,读者编号,借阅日期）作者名、(图书编号，读者编号,借阅日期)出版社、(图书编号，读者编号,借阅日期）读者姓名（四个部分函数依赖任选一种即可)（)1NF。由于存在非主属性对码的部分函数依赖。 问题10需要评分 某工厂生产若干产品,每种产品由不同的零件构成，有的零件可用在不同的产品上。这些零件由不同的原材料制成，不同零件所用的原材料可以相似。这些零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中。产品属性：产品号,产品名;零件属性：零件号，零件名；原材料属性:原材料号,原材料名,类别;仓库属性：仓库号，仓库名。请完毕如下设计:1)用-图画出概念模型，并注明联系的类型。2)将该模型转换为关系模型，并指出每个关系模式的主码。对的答案： 1)找出实体:产品，零件,原材料,仓库,根据题意画出E-R图:略。2)实体转换为关系表：产品（产品号，产品名)，主键:产品号零件（零件号,零件名),主键：零件号原材料(原材料号,原材料名，类别），主键：原材料号仓库（仓库号,仓库名），主键：仓库号各联系转换为关系表：(注：根据题意推断,联系均为多对多联系，联系产生了数量属性)产品-零件（产品号,零件号,数量),主键：(产品号,零件号),外键：产品号, 外键：零件号零件-原材料（零件号,原材料号, 数量），主键：(零件号，原材料号),外键:零件号,外键：原材料号零件-仓库(零件号,仓库号， 数量），主键:(产品号，仓库号），外键：产品号,外键:仓库号材料-仓库（原材料号,仓库号, 数量）,主键:(产品号,仓库号）,外键:产品号，外键：仓库号关系模型中没有 主码相似的关系，即没有需要合并的表。