例1

| 给定关系模式EMP_DEPT
1.“EmployeeNo”, “EmployeeName”, “BornDate” and “EmployeeAddress” represent the ID number, name, date of birth, and address of an employee.
2.“DepartmentNo”, “DepartmentName”, and “DepartmentAddress” represent the ID number, name, and address of a department.
3.“ProjectNo” and “ProjectName” represent the ID number and name of a project.
4.“WorkHours” represents the working hours of an employee in a project. | | —- | | 一些设定:
A department has more than one employee;
Two employees maybe have the same name;
An employee works in a unique department;
An employee can take part in several projects;
Two or more employees from different departments can take part in a same project;
An employee can take part in more than one project;
The working hours of an employee in different project may be different. |

  1. Identify functional dependencies. | 有以下函数依赖:
    image.png
    易错点:
    认为employeeNo->projectNo,认真读题,”一个人一个部门,一个人多个项目,不同部门得多个人可以参加一个项目”,则有一定有人不属于任一个部门,才能参加多个项目,因此二者没有必然依赖关系,只有employeeNo, departmentNo->projectNo | | | —- | —- |

  2. Determine all candidate keys of the relational schema.

image.png

  1. Is the realtional schema in BCNF? Why? If not, bring it to a set of BCNF schemas, and identify their candidate keys and foreign keys

image.png

例2

| 考虑关系模式R = {A, B, C, D, E}, 函数依赖集FD = { A→C, B → D, AB → CD, E → B}
（1）求R的候选码。
（2）下列函数依赖哪些成立？ a. E → D b. A → B c. AB → CE d. AE → CB
（3）R是否满足BCNF？请说明理由。若不满足，请将R规范化为一组BCNF关系模式。 | | —- |

  1. 参考:5.7 候选码求法—LRN法
    1.    首先排除R类属性BCD<br />
    2.    A+≠R且E+≠R<br />
    3.    (AE)+=R,则候选码是AE
  2. 参考:5.2 属性闭包算法作用

image.png

  1. 参考: 6.2 BCNF分解算法 | image.png | 易错点:
    1. 使用AB->CD分解R:
    分解算法是要在分解后产生一个无损BCNF关系,另一个不满足BCNF的时候在此继续分解;而不是为了分解而分解.使用AB->CD,第一步得到的R1和R2都是非BCNF
    2. 关于判定Ri中未显式表明的依赖是否满足BCNF:
    如R4(ABE)中E->B成立,但不好直接说明是否符合BCNF,通过改进后的判定方法,R4有3个元素,对于8个子集,其中E+= BDE, 不满足”要么闭包不含有除本身外的其余任何属性,要么包含R中所有属性”这一调节,则E->((E+-E)∩Ri)即E->B不满足BCNF,需要分解 | | —- | —- |