2020-数据库五次课堂作业-BUPT 信通院 -- 十安辰

数据库大二下五次作业合集

第一次作业：

---

从自己喜欢的app中选一个数据库应用产品，阐述所选定的数据库产品存储的数据有哪些，属于什么数据模型，在这个app中常见的数据处理功能有哪些。

答：
产品：网易云音乐

1. 网易云音乐产品存储的数据有：歌手信息、歌手、专辑、专辑信息、歌曲，评论信息等
   
2. 这个数据库产品属于关系数据库模型
   
3. 这个app中常见的数据处理功能有：播放音乐-查询，搜索歌曲-查询，评论-插入、歌曲信息录入-插入、歌曲修改信息-修改
   

---

第二次作业：

---

课本第二章课后题第6题；
代码如下：

-- 如果是在navicat中运行，请将注释中的‘--’更改为‘#’，否则程序无法识别注释
CREATE DATABASE SPJ;
USE SPJ;
-- 创建供应商S表
CREATE TABLE S
     (
     SNO CHAR(2) PRIMARY KEY,
     SNAME VARCHAR(30) NOT NULL,
     STATUS SMALLINT NOT NULL,
     CITY VARCHAR(20) NOT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据   
INSERT INTO S(SNO,SNAME,STATUS,CITY)
VALUES ('S1','精益',20,'天津'),
('S2','盛锡',10,'北京'),
('S3','东方红',30,'北京'),
('S4','丰泰盛',20,'天津'),
('S5','为名',30,'上海');
 
-- 创建零件P表
CREATE TABLE P
     (
     PNO CHAR(2) PRIMARY KEY,
     PNAME VARCHAR(10) NOT NULL,
     COLOR CHAR(2) NOT NULL,
     WEIGHT SMALLINT NOT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据       
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)
VALUES ('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);
 
-- 创建工程项目G表
CREATE TABLE J
     (
     JNO CHAR(2) PRIMARY KEY,
     JNAME VARCHAR(20) NOT NULL,
     CITY VARCHAR(20) NOT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据       
INSERT INTO J(JNO,JNAME,CITY)
VALUES ('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京');
 
-- 创建供应情况SPJ表
CREATE TABLE SPJ
     (
     SNO CHAR(2) ,
     PNO CHAR(2) ,
     JNO CHAR(2) ,
     QTY SMALLINT NULL,
     PRIMARY KEY (SNO,PNO,JNO),
     CONSTRAINT fk_spj_s FOREIGN KEY(SNO) REFERENCES S(SNO),
     CONSTRAINT fk_spj_p FOREIGN KEY(PNO) REFERENCES P(PNO),
     CONSTRAINT fk_spj_j FOREIGN KEY(JNO) REFERENCES J(JNO)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#插入数据       
INSERT INTO SPJ(SNO,PNO,JNO,QTY)
VALUES ('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500);

第三次作业：

---

在第一讲结束的时候请同学们选择了一个感兴趣的数据库应用，请对自己选择的数据库应用进行关系模型建模，并用关系代数表达一项常用业务

一项业务：查询(搜索)歌手的所有曲目
专辑曲目歌手歌手姓名歌手

第四次作业：

---

第一次课作业请大家写出了一个常见的数据库应用产品。请在第一次课作业的基础上写出这个产品的完整性和安全性约束。

答：完整性约束如下：

CREATE TABLE `歌手`  (
......
  #歌手姓名作为主码
  PRIMARY KEY (`歌手姓名`)
);
CREATE TABLE `歌手个人信息`  (
......
  #歌手姓名作为主码
  PRIMARY KEY (`姓名`)
);
CREATE TABLE `评论`  (
......
  #评论以每条评论id序号作为主码
  PRIMARY KEY (`序号`)
);
CREATE TABLE `专辑曲目`  (
......
  #歌曲曲目以曲名作为组码
  PRIMARY KEY (`曲名`)
);
CREATE TABLE `专辑信息`  (
......
  #专辑信息以专辑名称作为主码
  PRIMARY KEY (`名称`)
);
#歌手个人信息的“姓名”属性关联歌手属性的“歌手姓名”
ALTER TABLE `歌手个人信息` ADD CONSTRAINT `歌手_信息` FOREIGN KEY (`姓名`) REFERENCES `歌手` (`歌手姓名`);
#评论的“曲名”属性关联专辑曲目的“曲名”属性
ALTER TABLE `评论` ADD CONSTRAINT `评价_曲目` FOREIGN KEY (`曲名`) REFERENCES `专辑曲目` (`曲名`);
#专辑曲目的“所属专辑”属性关联专辑信息的"名称"属性
ALTER TABLE `专辑曲目` ADD CONSTRAINT `专辑_曲目` FOREIGN KEY (`所属专辑`) REFERENCES `专辑信息` (`名称`);
#专辑曲目的“歌手”属性关联歌手的"歌手姓名"属性
ALTER TABLE `专辑曲目` ADD CONSTRAINT `歌手_曲目` FOREIGN KEY (`歌手`) REFERENCES `歌手` (`歌手姓名`);

数据库的安全性约束：
假定现有普通以及歌手两种用户（数据库管理员拥有最高权限，不予考虑）

#所有用户可查询以上表中的所有信息
GRANT SELECT ON TABLE `歌手` `歌手个人信息` `评论` `专辑曲目` `专辑信息`TO PUBLIC;

对于普通用户：

#授予普通用户新增加评论、修改编辑评论的权限
GRANT UPDATE(`曲名`,`详细信息`) INSERT(`曲名`,`详细信息`) ON TABLE `评论` TO common;

对于歌手用户：

#授予歌手用户对歌手个人信息表的所有权限
GRANT ALL PRIVILEGES ON TABLE `歌手个人信息` TO singer;
#授予歌手用户对专辑曲目表的所有权限
GRANT ALL PRIVILEGES ON TABLE `专辑曲目` TO singer;

第五次作业：

---

1、请为选定的数据产品设计E-R模型图，再将转化为关系模型，并写出关系模型所属的范式及原因，讨论当前范式存在的问题以及改进的方法

此产品的E-R模型图如下

关系模型如下

此模型为第二范式，原因是各关系中每一个分量都是不可分割的数据项，非主属性完全依赖于主码
存在的问题 ：
1、专辑曲目关系中：所属专辑函数依赖于曲名，歌手函数依赖于所属专辑，故歌手传递依赖于曲名
改进办法，将歌手和所属专辑单独抽象出一个关系

---

2、课本241页，第7题

概念模型如图