数据库 作业9

6.假设有下面两个关系模式：

       职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；

       部门（部门号，名称，经理名，电话），其中部门号为主码。

用SQL语言定义的这两个关系模式 ，要求在模式中完成以下完整性约束条件的定义：

（1）定义每个模式的主码。

职工表定义主码：

CREATE TABLE 职工 (
    职工号 INT PRIMARY KEY,
    姓名 VARCHAR(50),
    年龄 INT,
    职务 VARCHAR(50),
    工资 DECIMAL(10, 2),
    部门号 INT
);

部门表定义主键：

CREATE TABLE 部门 (
    部门号 INT PRIMARY KEY,
    名称 VARCHAR(50),
    经理名 VARCHAR(50),
    电话 VARCHAR(20)
);

（2）定义参照完整性.

职工表关系模式：

CREATE TABLE 职工 (
    职工号 INT PRIMARY KEY,
    姓名 VARCHAR(50),
    年龄 INT,
    职务 VARCHAR(50),
    工资 DECIMAL(10, 2),
    部门号 INT,
    FOREIGN KEY (部门号) REFERENCES 部门(部门号)
);

在上述SQL语句中，FOREIGN KEY (部门号) REFERENCES 部门(部门号) 表示 职工 表中的 部门号 列参照了 部门 表中的 部门号 列。这就建立了一个外键关系，确保在 职工 表中的每个 部门号 都必须在 部门 表的 部门号 列中有一个匹配的值。

部门表关系模式：

CREATE TABLE 部门 (
    部门号 INT PRIMARY KEY,
    名称 VARCHAR(50),
    经理名 VARCHAR(50),
    电话 VARCHAR(20)
);

部门表没有外键引用其他表，但是可以在其他表中使用 FOREIGN KEY 来引用 部门 表中的 部门号 列。

（3）定义职工年龄不超过60岁

CREATE TABLE 职工 (
    职工号 INT PRIMARY KEY,
    姓名 VARCHAR(50),
    年龄 INT CHECK (年龄 <= 60),  -- 添加 CHECK 约束，确保年龄不超过60岁
    职务 VARCHAR(50),
    工资 DECIMAL(10, 2),
    部门号 INT,
    FOREIGN KEY (部门号) REFERENCES 部门(部门号)
);

2.建立一个关于系、学生、班级、学会等诸信息的关系数据库。

描述学生的属性有:学号、姓名、出生年月、系名、班号、宿舍区;

描述班级的属性有:班号、专业名、系名、人数、入校年份;

描述系的属性有:系名、系号、系办公室地点、人数;

描述学会的属性有:学会名、成立年份、地点、人数。

有关语义如下:一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参加某学会有一个入会年份。

请给出关系模式，写出每个关系模式的极小函数依赖集，指出是否存在传递函数依赖,对于函数依赖左部是多属性的情况，讨论函数依赖是完全函数依赖还是部分函数依赖。

指出各关系的候选码、外部码，并说明是否全码存在。

学生关系模式：

  CREATE TABLE 学生 (
    学号 INT PRIMARY KEY,
    姓名 VARCHAR(50),
    出生年月 DATE,
    系名 VARCHAR(50),
    班号 INT,
    宿舍区 VARCHAR(50),
    FOREIGN KEY (系名) REFERENCES 系(系名),
    FOREIGN KEY (班号) REFERENCES 班级(班号)
);

班级关系模式：

    CREATE TABLE 班级 (
    班号 INT PRIMARY KEY,
    专业名 VARCHAR(50),
    系名 VARCHAR(50),
    人数 INT,
    入校年份 INT,
    FOREIGN KEY (系名) REFERENCES 系(系名)
);

系关系模式：

CREATE TABLE 系 (
    系名 VARCHAR(50) PRIMARY KEY,
    系号 INT,
    系办公室地点 VARCHAR(50),
    人数 INT
);

学会关系模式：

CREATE TABLE 学会 (
    学会名 VARCHAR(50) PRIMARY KEY,
    成立年份 INT,
    地点 VARCHAR(50),
    人数 INT
);

分析每个关系模式的极小函数依赖集、传递函数依赖等：

学生关系模式：

极小函数依赖集：{学号} → {姓名, 出生年月, 系名, 班号, 宿舍区}

不存在传递函数依赖。

没有函数依赖左部是多属性的情况。

班级关系模式：

极小函数依赖集：{班号} → {专业名, 系名, 人数, 入校年份}

不存在传递函数依赖。

没有函数依赖左部是多属性的情况。

系关系模式：

极小函数依赖集：{系名} → {系号, 系办公室地点, 人数}

不存在传递函数依赖。

没有函数依赖左部是多属性的情况。

学会关系模式：

极小函数依赖集：{学会名} → {成立年份, 地点, 人数}

不存在传递函数依赖。

没有函数依赖左部是多属性的情况。

在这里，每个关系模式的主键就是候选码，因此，主键即是候选码。外部码是指在其他关系中的属性集，与当前关系模式的主键形成外键关系。在这个简化的描述中，我们可以看到主键和外部码的定义是直观的，没有复杂的情况。

7.下面的结论哪些是正确的?哪些是错误的?对于错误的请给出一个反例说明之。

(1)任何一个二目关系是属于3NF的。

错误。 3NF（第三范式）要求关系中的所有属性都非传递依赖于关系的候选键。对于某些情况，二目关系可能不满足这一条件

(2)任何一个二目关系是属于BCNF的。

正确。 BCNF（Boyce-Codd范式）是3NF的加强形式，对于任何一个关系，如果它只有一个候选键，那么它必定满足BCNF。

(3)任何一个二目关系是属于4NF的。

错误。 4NF要求关系中不存在多值依赖。对于某些情况，二目关系可能存在多值依赖。

(4)当且仅当函数依赖A→B在R上成立，关系R(A,B,C)等于其投影R\(A, B)和 R₂(A, C)的连接。

错误。 这是错误的描述。函数依赖A→B成立，并不意味着关系R(A,B,C)等于其投影R(A, B)和 R₂(A, C)的连接。反例：考虑R(A, B, C)中的行 (1, 2, 3)，其中A→B。但是，R(A,B,C) 不等于 R(A, B) 和 R₂(A, C) 的连接。

(5)若R.A→R.B，R.B→R.C，则 RA→R.C。

错误。 这是错误的描述。反例：考虑关系R(A, B, C)中的行 (1, 2, 3)，其中A→B，B→C。但是，R.A 不能推导出 R.C。

(6) 若 R.A→R.B，R.A→R.C，则R.A→R.(B, C)。

正确。 这是正确的描述。如果R.A→R.B，R.A→R.C，那么根据合并法则，R.A→R.(B, C)。

(7)若R.B→R.A，R.C→R.A，则 R.(B, C)→R.A。

错误。 这是错误的描述。反例：考虑关系R(A, B, C)中的行 (1, 2, 3)，其中B→A，C→A。但是，R.(B, C) 不能推导出 R.A。

(8) 若 R.(B,C)→R.A，则R.B→RA,R.C→RA。

正确。 这是正确的描述。如果R.(B,C)→R.A，那么根据分解法则，R.B→R.A，R.C→R.A。

8.证明

（1）如果R是BCNF关系模式，则R是3NF关系模式，反之则不然。

定义：

BCNF (Boyce-Codd Normal Form): 一个关系R在满足BCNF，如果对于R的任何非平凡函数依赖X→Y，X都是R的超码。

3NF (Third Normal Form): 一个关系R在满足3NF，如果R中的每个非主属性都不传递依赖于R的任何候选键。

证明：

假设R是BCNF，我们要证明R是3NF。

假设BCNF的定义不满足3NF的定义。

如果R不是3NF，则存在非主属性Z和候选键K，使得Z传递依赖于K的某个子集X。

根据BCNF的定义，Z不能是任何超码的一部分。

因为BCNF要求任何非平凡函数依赖的左部都是超码。

考虑Z的传递依赖X→Z。

这意味着X是候选键K的一个子集，因为Z不能是任何超码的一部分，所以X不能包含K的真子集。

因此，Z不能传递依赖于K，与假设相矛盾。

这是因为如果Z传递依赖于K，那么X应该包含K的真子集，这违反了BCNF的定义。

由此可见，如果R是BCNF，那么它也是3NF。这是因为BCNF的定义保证了关系中的每个非平凡函数依赖都有一个超码作为左部，这防止了传递依赖的存在。

（2）如果R是3NF关系模式，则R一定是2NF关系模式。

定义：

2NF (Second Normal Form): 一个关系R在满足2NF，如果R中的每个非主属性完全函数依赖于R的任何一个候选键。

3NF (Third Normal Form): 一个关系R在满足3NF，如果R中的每个非主属性都不传递依赖于R的任何候选键。

证明：

假设R是3NF。

这意味着R中的每个非主属性都不传递依赖于R的任何候选键。

考虑R中的某个非主属性Y。

由于R是3NF，Y不能传递依赖于R的任何候选键。

考虑Y完全函数依赖于R的某个候选键X。

由于Y不能传递依赖于R的任何候选键，X必定是候选键。

因此，R中的每个非主属性都完全函数依赖于R的某个候选键。

这是因为对于每个非主属性Y，如果它完全函数依赖于候选键X，而Y不能传递依赖于R的任何候选键，那么X必定是候选键。

这证明了R是2NF。

因为R中的每个非主属性都完全函数依赖于R的某个候选键，满足2NF的定义。

因此，如果关系模式R是3NF，则R一定是2NF。这是因为3NF的定义已经包含了2NF的要求，并且更进一步地防止了传递依赖的存在。