MySQL系列（八）：数据库系统期中/期末复习试题

青旅半醒 2022-12-28 11:21 129阅读 0赞

一、 选择题

1．在DBS中，DBMS和OS之间关系是（ D ）

A．并发运行 B．相互调用 C．OS调用DBMS D．DBMS调用OS

2．DBS具有“数据独立性”特点的原因是因为在DBS中（ B ）

A.采用磁盘作为外存 B.采用三级模式结构 C.使用OS来访问数据 D.用宿主语言编写应用程序

3.关系代数的完备集运算是（ B ）

(A)∪ － × σ π ρ

(B)∪ － σ π × ∩

(C)∪ ∩ × σ π ÷

(D)∪ ∩ σ π × ρ

4．设关系R、S、W各有10个元组，那么这三个关系的自然联接的元组个数为（ D）

A．10     B．30     C．1000     D．不确定（与计算结果有关）

5．如果两个关系没有公共属性，那么其自然联接操作（ A）

A．转化为笛卡尔积操作 B．转化为联接操作 C．转化为外部并操作 D．结果为空关系

6．下列式子中，不正确的是（ D）

A．R－S=R－（R∩S） B．R=（R－S）∪（R∩S） C．R∩S=S－（S－R） D．R∩S=S－（R－S）

7．SQL中，与“NOT IN”等价的操作符是（ D）

A．=SOME     B．<>SOME     C．=ALL     D．<>ALL

8．对于基本表EMP（ENO，ENAME，SALARY，DNO），其属性表示职工的工号、姓名、工资和所在部门的编号。基本表DEPT（DNO，DNAME），其属性表示部门的编号和部门名。有一SQL语句：SELECT  COUNT（DISTINCT DNO）　FROM  EMP；其等价的查询语句是（C ）

A．统计职工的总人数 B．统计每一部门的职工人数 C．统计职工服务的部门数目 D．统计每一职工服务的部门数目

9.事务的并发执行不会破坏DB的完整性，这个性质称为事务的（ B）

A．持久化     B．隔离性     C．一致性     D．原子性

10．在DB恢复时，对已经COMMIT但更新未写入磁盘的事务执行（ A）

A．REDO处理     B．UNDO处理     C．ABORT处理   D．ROLLBACK处理

**一个工程公司的数据库中有如下4张表：**

1) 员工表Employee(emp\_id，name，sex, birth\_day，title，phone, salary，dept\_id)，其属性分别表示员工工号、姓名、性别，出生日期、岗位，联系电话，工资，所属部门的编号；

2)部门表Department(dept\_id，name，phone，manager\_id), 其属性分别表示部门编号、部门名称，部门电话，部门经理的员工工号；

3)工程项目表project(proj\_id，name，location，budget, dept\_id), 其属性分别表示项目编号、项目名称，项目地点，项目金额，项目承担部门的编号。假定一个项目只由一个部门承担。一个部门可承担多个项目；

4)职工参加工程项目年度情况表Work(emp\_id, proj\_id，year，start\_date, end\_date, duty，workdays), 其属性分别表示员工工号，项目编号，年度，开始日期，结束日期，员工在项目中的职责，员工实际参与项目的天数。

注意：Work表记录了每个员工的年度工作情况。一个项目可能要多年完成，因此一个员工可能在多年中都参加了同一个项目，此时，每年都会有一行记录。在一年中，一个员工也可参加多个项目。duty取值有PROJ\_MANAGER，GROUP\_LEADER, MEMBER三种。对于一个项目，除了其承担部门的员工可参加外，其它部门的员工也可能参加。

回答如下问题：

**二、分别表示4个表的主键，如果有外键也请标识出来。**

*  Employee 主键为 emp\_id

外键为dept\_id，引用Department主键；

*  Department 主键为 deptid

外键为manager\_id，引用Employee主键；

*  Project 主键为proj\_id

外键为dept\_id，引用Department主键；

*  Work 主键为emp\_id、proj\_id与year；

外键1：emp\_id，引用Employee主键；

外键2：proj\_id，引用project主键。

**三、用关系代数式表达：**

1) 查找出(名称为‘工程1队’)的部门的女职工名单，输出姓名、出生日期、工资。

*Π**name,birth\_day,salary**(**σ**sex='**女**'**(Employment)⋈**σ**name='**工程**1**队**'(Department))*

![20201216194423366.png][]

2) 求出在2017年根本就没有参加任何项目的员工名单，输出所在部门名称、姓名、员工工号。

*Π**department.name,employee.name,emp\_id**((**Π**emp\_id**Employment**-**Π**emp\_id**Work**⋈Employment)⋈Department)*

![20201216194423363.png][]

**四、使用SQL的DDL写出Work表的定义，要定义主键、外键等数据库的完整性约束。**

CREATE TABLE Work(

emp\_id CHAR(5) NOT NULL,

proj\_id CHAR(5) NOT NULL,

year CHAR(4) NOT NULL,

start\_date DATE,

end\_date DATE,

duty VARCHAR(20) (CHECK duty IN(‘PROJ\_MANAGER’,‘GROUP\_LEADER’,‘MEMBER’)),

workdays INTEGER

PRIMARY KEY(emp\_id, proj\_id, year)

FOREIGN KEY(emp\_id) REFERENCES employee(emp\_id)

Foreign KEY(proj\_id) REFERENCES project(proj\_id)

);

**五、使用SQL回答下列十四个问题：**

(1) 新进一个工程项目：名称：望城电力大楼, 地点：雷锋大道428号，项目金额850万元, 由工程5队(部门编号为‘07’)来负责承担，公司给该工程分配编号‘2018101’。试用SQL 语句将该工程项目信息加到数据库中；

INSERT INTO project (proj\_id,name,location,budget,dept\_id) VALUES('2018101','望城电力大楼','雷锋大道428号',850,'07');

(2) 土建部的员工张开意(工号为‘2011045’)已经从公司调走，试用SQL 语句将该员工的信息从数据库中删除；

DELETE FROM employee WHERE emp\_id ='2011045';

(3) 员工李欣艺(工号：‘2008013’)从工程1队（部门编号‘03’）调任工程2队(部门编号‘04’)任部门经理，试用SQL 语句修改数据库，反映此调动和任命；

UPDATE employee SET dept\_id='04' WHERE emp\_id='2008013';

UPDATE department SET manager\_id='2008013' WHERE dept\_id='04';

(4) 求出2017年公司的劳动模范。其定义是：凡是全年参加项目的实际总计天数大于等于300天的员工就是劳动模范。要求输出姓名、工号、所属部门名称，工作天数，要求对输出结果按照工作量递减原则排序；

SELECT E.name,E.emp\_id,D.name,workdays FROM (SELECT emp\_id, SUM(workdays) AS workdays FROM work WHERE year ='2017' GROUP BY emp\_id HAVING workdays >=300) AS w, employee AS e, department AS d WHERE e. emp\_id =w. emp\_id AND e. dept\_id =d. dept\_id ORDER BY workdays DESC;

(5) 求出在2017年全年根本就没有参加任何项目的员工名单，输出所在部门编号，姓名，员工工号和年龄；

SELECT dept\_id,name,emp\_id,YEAR(now())-YEAR(birth\_day) AS age FROM employee WHERE emp\_id NOT IN (SELECT DISTINCT emp\_id FROM work WHERE year='2017');

(6) 求出工资低于职工所在部门平均工资的职工工号和姓名；

SELECT emp\_id, name FROM employee AS e,(SELECT dept\_id, AVG(salary) AS avg\_salary FROM employee GROUP BY dept\_id) AS d WHERE e.dept\_id = d.dept\_id AND e.salary < d.avg\_salary;

(7) 求出工资最高的员工的职工工号、姓名和工资；

SELECT emp\_id,name,salary FROM employee WHERE salary =(SELECT MAX(salary) FROM employee);

(8) 求出参加了所有工程项目的职工工号和姓名；

SELECT e.emp\_id, name FROM employee AS e,(SELECT emp\_id,COUNT(DISTINCT proj\_id) AS work\_num FROM work GROUP BY emp\_id HAVING work\_num=(SELECT COUNT(\*) FROM project)) AS w WHERE e. emp\_id=w. emp\_id;

(9) 创建一个视图dept\_info(name, code, manager, clerk\_num)，其内容为部门概况：部门名称，部门编号，部门经理姓名，部门员工人数）；

CREATE VIEW dept\_info(name, code, manager, clerk\_num) AS SELECT d.name,d.dept\_id,e.name, COUNT(\*) FROM(SELECT dept\_id, COUNT(\*) AS clerk\_num FROM employee GROUP BY dept\_id) AS d1, department AS d, employee AS e WHERE d.dept\_id=d1.dept\_id AND d.manager\_id=e. emp\_id;

(10) 创建一个存储过程my\_work，列出某个员工，在某年中参加项目的情况，输出包括起始日期，结束日期，项目名称proj\_name，项目承担部门的名称dept\_name，工作天数。要求按照起始日期升序排列；

CREATE PROCEDURE my\_work(@ emp\_id IN VARCHAR,@ year IN VARCHAR) AS BEGIN

SELECT start\_date,end\_date,p.name AS proj\_name,d.name AS dept\_name, workdays FROM work AS w, project AS p, department AS d WHERE w.proj\_id=p.proj\_id AND p.dept\_id=d.dept\_id AND w.emp\_id=@emp\_id AND w.year=@year ORDER BY start\_date ASC;

END;

(11) 创建一个触发器add\_work\_rule，控制往Work表中添加记录时，表中不会出现一个员工在一年中参加多个项目时，其时间上有重叠现象。即在Work表中当出现两行记录的emp\_id, year相同时，不允许其（start\_date, end\_date）时间段上有重叠；

CREATE TRIGGER add\_work\_rule BEFORE INSERT ON work

REFERENCING NEW ROW AS new

FOR EACH ROW

BEGIN

WHEN(EXIST SELECT \* FROM Work WHERE emp\_id=@new.em\_id AND proj\_id=@new.proj\_id AND end\_date >=@new.start\_date AND start\_date<=@new.end\_date)

raise\_application\_error(20000，'这个记录与已有记录在时间上有重叠')；

END；

(12) 创建一个角色group\_leader和一个用户ZhangDF, 给group\_leader赋予对work表中记录的end\_date和workdays进行修改的权限。然后把角色group\_leader指派给用户ZhangDF;

CREATE ROLE 'group\_leader';

CREATE USER ZhangDF IDENTIFIED BY '123';

GRANT UPDATE(end \_date,workdays) ON work TO group\_leader;

GRANT group\_leader To ZhangDF;

(13) 就Work表的(year, proj\_id)字段创建一个索引；

CREATE INDEX work\_year\_proj ON work(year,proj\_id);

(14）写出一个业务规范的约束。

一个员工每年至多参与5个项目。

[20201216194423366.png]: /images/20221120/19845fd4d0264a44babf375877680a44.png
[20201216194423363.png]: /images/20221120/71c41c718210426a8b115330a83b9a28.png