数据库第三次上机实验报告

实验九 数据库的完整性设计

一、实验目的

熟练掌握使用 Navicat 和 Transact-SQL 语言两种方法创建、修改和删除表。掌握管理表的有关系统存储过程。

二、实验内容

1、利用 Navicat 创建满足以下要求的数据库：① 数据库存在于连接 MySQL 中；② 数据库名称为 xsgl； ③ 字符集选择 utf8--UTF-8 Unicode；排序规则选择 utf8_general_ci。

CREATE DATABASE IF NOT EXISTS xsgl
CHARACTER SET utf8
COLLATE utf8_general_ci;

2、在数据库 xsgl 中，利用 Navicat 和 SQL 语句创建以下表格：

1）表格名为 xs（学生基本情况表），表格中各个属性的定义如下：

CREATE TABLE `xs` (
  `xh` int NOT NULL,
  `xm` char(8) DEFAULT NULL,
  `xb` char(2) DEFAULT NULL,
  `nl` tinyint DEFAULT NULL,
  `zy` char(16) DEFAULT NULL,
  `jtzz` char(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

2）表格名为 kc（课程情况表），表格中各个属性的定义如下：

CREATE TABLE `kc` (
  `xh` int NOT NULL,
  `kch` int NOT NULL,
  `kcm` char(20) DEFAULT NULL,
  `xss` int DEFAULT NULL,
  `xf` int DEFAULT NULL,
  `fs` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

<1>、在 xs 表中定义 xh 为主键。

ALTER TABLE xs
ADD PRIMARY KEY(xh);

<2>、在 kc 表中定义 xh 和 kch 联合构成主键。

ALTER TABLE kc
ADD PRIMARY KEY(xh, kch);

<3>、定义 kc 表中的 kcm 列满足唯一性约束。

ALTER TABLE kc
ADD UNIQUE(kcm);

<4>、定义 kc 表中的 fs 列默认值为 0。

ALTER TABLE kc
ALTER COLUMN fs SET DEFAULT 0;

<5>、定义 xs 表中的 xb 列的 CHECK 约束“男”或“女”。

ALTER TABLE xs
ADD CONSTRAINT xb
CHECK(xb in ('男', '女'));

参考：stackoverflow

<6>、在 xs 表与 kc 表之间定义外键 xh。

ALTER TABLE kc
ADD CONSTRAINT
FOREIGN KEY (xh) 
REFERENCES xs(xh);

<7>、以下将创建一个学生—课程数据库，包括学生关系 Student、课程关系 Course 和选修关系 SC：

  • ① 建立学生-课程数据库 xskc，创建 Student 表，并将 Student 表中的 Sno 属性定义为主键。

    CREATE DATABASE IF NOT EXISTS xskc
CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE `student` (
    `Sno` char(9) NOT NULL,
    `Sname` char(20) NOT NULL,
    `Ssex` smallint DEFAULT NULL,
    `Sdept` char(20) DEFAULT NULL,
    PRIMARY KEY (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

  • ② 创建 Course 表和 SC 表，并将 SC 表中的 Sno, Cno 联合构成主键。

    CREATE TABLE `course` (
    `Cno` char(4) NOT NULL,
    `Cname` char(40) NOT NULL,
    `Cpno` char(40) DEFAULT NULL,
    `Scredit` smallint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `sc` (
    `Sno` int NOT NULL,
    `Cno` char(4) NOT NULL,
    `Grade` smallint DEFAULT NULL,
    PRIMARY KEY (`Sno`,`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

  • ③ 定义 SC 表中的参照完整性

    CREATE TABLE `sc` (
    `Sno` char(9) NOT NULL,
    `Cno` char(4) NOT NULL,
    `Grade` smallint,
    PRIMARY KEY (Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno),
    FOREIGN KEY(Cno) REFERENCES Course(Cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

  • ④ 显示说明参照完整性的违约处理示例

    CREATE TABLE SC
(Sno char(9),
Cno char(4),
Grade SMALLINT,
PRIMARY KEY(Sno, Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
FOREIGN KEY(Cno) REFERENCES Course(Cno)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
);

  • ⑤ 定义 SC 表 Grade 属性不允许为空

    CREATE TABLE SC
(Sno char(9),
Cno char(4),
Grade SMALLINT NOT NULL,
PRIMARY KEY(Sno, Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
FOREIGN KEY(Cno) REFERENCES Course(Cno)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
);

三、课后练习题

1、新建名为 SPJ 的数据库。（其中数据表 S、P、J 和 SPJ 的关系模式及其含义见实验八）

见实验八。

2、在数据库 SPJ 中，合理地为库中每一个数据表定义主键和外键。

CREATE TABLE `spj` (
  `SNO` char(10) NOT NULL,
  `PNO` char(10) NOT NULL,
  `JNO` char(10) NOT NULL,
  `QTY` int(11) NOT NULL,
  PRIMARY KEY (`SNO`,`PNO`,`JNO`),
  KEY `PNO` (`PNO`),
  KEY `JNO` (`JNO`),
  CONSTRAINT `spj_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `s` (`SNO`),
  CONSTRAINT `spj_ibfk_2` FOREIGN KEY (`PNO`) REFERENCES `p` (`PNO`),
  CONSTRAINT `spj_ibfk_3` FOREIGN KEY (`JNO`) REFERENCES `j` (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、适当为各数据表及其字段添加约束（唯一性、默认值等）。

CREATE TABLE `s` (
  `SNO` char(10) NOT NULL,
  `SNAME` char(10) DEFAULT NULL,
  `STATUS` char(10) DEFAULT NULL,
  `CITY` char(10) DEFAULT NULL,
  PRIMARY KEY (`SNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `p` (
  `PNO` char(10) NOT NULL,
  `PNAME` char(10) DEFAULT NULL,
  `COLOR` char(10) DEFAULT NULL,
  `WEIGHT` int(11) DEFAULT NULL,
  PRIMARY KEY (`PNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `j` (
  `JNO` char(10) NOT NULL,
  `JNAME` char(10) DEFAULT NULL,
  `CITY` char(10) DEFAULT NULL,
  PRIMARY KEY (`JNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

四、思考题

1、使用参考语句创建好 xs 和 kc 两个表之后，语句中对于 xb 列的 CHECK 约束是否起作用？如果未起作用，思考语句通过却未起作用的原因。

是。

INSERT INTO xs(xh, xm, xb) VALUES(2021, '张飞', 'N');

output:

INSERT INTO xs(xh, xm, xb) VALUES(2021, '张飞', 'N')
> 3819 - Check constraint 'xb' is violated.
> Time: 0.001s

2、参考语句中参照完整性的违约处理示例“on delete cascade”和“on update cascade”的含义是什么？“on delete no action”又代表什么？

“on delete cascade”：当删除 REFERENCE 的表中的元组时，同时删除本表中相应的元组。

“on update cascade”：当更新 REFERENCE 的表中的元组时，同时更新删除本表中相应的元组。

“on delete no action”：当删除 REFERENCE 的表中元组造成与本表不一致时，拒绝删除。

实验五 表数据的维护

一、实验目的

加深对数据安全性的理解，并掌握 Navicat 中有关用户，角色及操作权限的管理方法。

二、实验内容

1、在 Navicat 中，管理建立用户和分配用户权限。

在这里，我们可以发现 root 是 MySQL 最高级别权限的用户，它拥有查看、修改和删除 MySQL 软件中所有数据库的权限。当需要有多个数据库，并且分配给不同的用户使用，多个用户之间只有查看自己对应数据库的权限，不相互干扰, 需要建立多个数据库和用户，给用户设置管理指定数据库的权限。

操作方法如下：
① 右键点击“MySQL”连接，选择“连接属性”，将“保存密码”取消。 ② 鼠标右键点击“MySQL”连接，选择“创建数据库”，创建名为 xunmei 的数据库，字符集选择 utf8--UTF-8 Unicode 排序规则选择 utf8_general_ci。
③ 打开数据库 xunmei 后，点击界面上方的“用户”—“新建用户”—输入用户名“xmuser”、主机“localhost”、密码“1234”—保存（服务器权限自行定义）。
④点选新建的用户 xmuser—编辑用户—权限—添加权限--选择数据库 xunmei，添加相应权限（可不全选），如图1.1所示：
⑥设置到此完成。为了验证我们的设置是否正确，我们可以用刚才建立的用户 xmuser 和密码 1234 来连接 localhost 主机地址（方法：关闭连接 MySQL，该连接属于最高权限用户 root。我们用新用户新建一个到 localhost 的连接来模拟不同用户使用数据库的过程）。连上后，可使用用户 xmuser 对数据库 xunmei 或其他数据库进行操作，看是否与自己设置的权限相吻合。（可只添加添加少量权限，然后尝试在 xunmei 里进行操作，看是否能成功）。

按：这里要注意 Server Privileges 和 Privileges 的使用，Server Privileges 的权限是很大的，一般来说，我们只需要操作 Privileges，不然，可能会出现 Privileges 被 Server Privileges 覆盖的情况。

三、课后练习题

1、使用两个不同的用户通过 Navicat 和查询分析器查看 world 数据库中 country、city 两个表的所有数据。

2、删除用户 xmuser，尝试新建另一个用户来管理数据库、表等：
添加新用户对数据库 world 的权限，比较在有无相关权限的情况之下进行操作的区别：

  • ① Create。尝试新建表
  • ② Update。尝试修改表数据或结构
  • ③ Select。尝试查询表
  • ④ Alt。尝试修改表
  • ⑤ Drop。尝试删除表

新建用户 fanyfull，密码 1234。

有权限的情况下：

没有权限：

四、思考题

1、解释 10 个数据库操作权限的含义和影响。

参考：MySQL 8 docs

数据库
#数据库
数据库第三次上机实验报告
http://fanyfull.github.io/2021/12/08/数据库第三次上机实验报告/
作者
Fany Full
发布于
2021年12月8日
许可协议