数据库设计例题解析.docx

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