设工程-零件数据库有4个基本表

数据库问题

创建表供应商，表中有4个字段：供应商代码（字符型），性别（字符型），所在城市（字符型），联系电话（字符型）。 create table 供应商 (供应商代码 char(10), 性别 char(2), 所在城市 char(20), 联系电话 char(32)); 求供应工程J1中零件P1的供应商姓名； select a.供应商姓名 from 供应商 a, 工程 b, 零件 c, 供应零件 d where a.供应商代码=d.供应商代码 and b.工程代码=d.工程代码 and c.零件代码=d.零件代码 and b.工程名='J1' and c.零件名='P1'; 求供应工程J1零件为红色的

一道数据库习题

(1）找出没有使用天津产零件的工程号码。 select 工程代码 from 工程 where 供应商代码 in (select 供应商代码 from 供应商 where 所在城市 <>'天津') 其它的问题,没有分,免淡!

数据库由这4个表儿，想查出“取出上海供应商不提供任何零件的工程代号” 黑笔是我自己写的，红笔是正确

你要先搞清楚你要查内容的逻辑结构，看你自己写的语句翻译之后，只能查出来上海供应商参与的工程编号，实际上我觉得红字的查血语句还可以在简化，没必要再去J表里查询，直接查供应关系表里不与上海供应商合作的项目编号就可以 select distinct jn from spj where sn != (select sn from s where city="上海")；

有四个表他们都是多对多的关系，如何设计数据库

A表，a_id,a_name,a_tel三个字段。 B表,b_id,b_address,b_sol三个字段。 关联表，ref_id,a_id,b_id,存储着AB两表的id, 比如：A有 a0001,zhang3,139555533 a0002,li4,1397788888 a0003,wang5,13655566 B有 b0001,shanghai,234 b0002,shenyang,332 b0003,chongqing,123 如果想要AB表多对多的话，只需要将关联表进行增加即可，而AB两表基本数据不变， 比如： 关联表ref_table： r0001,a0001,b0002 r000

SP用两支笔干啥

一、 选择题
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个项目。