一、 选择题
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))
2) 求出在2017年根本就没有参加任何项目的员工名单,输出所在部门名称、姓名、员工工号。
Πdepartment.name,employee.name,emp_id((Πemp_idEmployment-Πemp_idWork⋈Employment)⋈Department)
四、使用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个项目。