mysql作业

必做题

1. 编写与图3.17中模式对应的 SQL DDL。对数据类型做任何合理的假设，并确保声明主键和外键。

范例：

1 create table person(
2     driver_id varchar( 50 ),
3 	  name varchar( 50 ),
4     address varchar( 50 ),
5     primary key (driver_id)
6 );

create table person ( 
	driver_id int, name varchar ( 50 ), 
    name varchar( 50 ),
	address varchar ( 50 ), 
	primary key ( driver_id ) 
);

create table car (
	license_plate varchar ( 10 ),
	model varchar ( 20 ),
	year date, 
	primary key ( license_plate )
);

create table accident ( 
	report_number int,
	year date, 
	location varchar ( 30 ), 
	primary key ( report_number ) 
);

create table owns (
	driver_id int,
	license_plate varchar ( 10 ),
	primary key ( driver_id ),
	foreign key ( driver_id ) references person(driver_id),
	foreign key ( license_plate ) references car ( license_plate ) 
);

create table participated (
	report_number int,
	license_plate varchar ( 10 ),
	driver_id int,
	damage_amount int,
	primary key ( report_number, license_plate ),
	foreign key ( report_number ) references accident( report_number ),
	foreign key ( license_plate ) references car ( license_plate )
);

2. 考虑图3.19中的员工数据库，其中主键被下划线标示。用 SQL 为下面的每个查询给出一个表达式

a. 查找与所在公司所在城市相同的每个员工的 ID 和姓名。
b. 找到与经理住在同一个城市和同一条街上的每个员工的身份证和姓名。
c. 查找每个工资高于公司所有员工平均工资的员工的 ID 和姓名。
d. 找出工资最少的公司。

a. 查找与所在公司所在城市相同的每个员工的 ID 和姓名。
select employee.ID,person_name
from employee,company,work
where employee.ID=work.ID and
employ.city=company.city and
company.company_name=work.company_name;

b. 找到与经理住在同一个城市和同一条街上的每个员工的身份证和姓名。
select employee.ID,person_name
from employ,manages,works,company
where employ.ID=manages.ID and
manages.street=employ.street and
manages.city=employ.city;

c. 查找每个工资高于公司所有员工平均工资的员工的 ID 和姓名。
select employee.ID,person_name
from employee as ep,works as w
where ey.ID=w.ID and
w.salary<(select AVG(w.salary) from emploee as e where e.salary = er.salary);

d. 找出工资最少的公司。
select c.company_name 
from works as w, company as c 
where c.company_name = w.company_name and 
w.salary <= (select min(wr.salary) from wr as works where wr.salary = w .salary);

3.

1 classroom(building, roomnumber, capacity)
2 department(deptname, building, budget)
3 course(courseid, title, deptname, credits)
4 instructor(ID, name, deptname, salary)
5 section(courseid, secid, semester, year, building, roomnumber, timesl
otid)
6 teaches(ID, courseid, secid, semester, year)
7 student(ID, name, deptname, totcred)
8 takes(ID, courseid, secid, semester, year, grade)
9 advisor(sID, iID)
10 time slot(time slotid, day, starttime, endtime)
11 prereq(courseid, prereqid)

考虑到上面的数据库，使用SQL语言编写以下查询。

1. 找到所有讲师的部门名称；
2. 找到计算机科学系所有年薪超过70,000美元的讲师的名字；
3. 找到所有讲师的名字，连同他们的部门名称和部门大楼名称；
4. 找出所有在生物系中薪水超过至少一名教员的教员的名字；
5. 查找其大楼名称包含子字符串‘Watson’的所有部门的名称；
6. 使用 union 属性，找到 2009 年秋季或 2010 年春季或两者授课的所有课程集；
7. 找到所有出现在教员关系中且薪水为空值的教员；

1 select distinct deptname from instructor;

2. 找到计算机科学系所有年薪超过70,000美元的讲师的名字；
select distinct i.deptname 
from instructor as i 
where i.instructor like '%计算机科学%' and 
i.salary >=70000;

3. 找到所有讲师的名字，连同他们的部门名称和部门大楼名称；
select distinct i.name, i.dept_name, d.building 
from instructor as i,department as d, d join i on deptname 
where d.dept_name = i.dept_name;

4. 找出所有在生物系中薪水超过至少一名教员的教员的名字；
select distinct i.name
from instructor as i, instructor as t
where i.salary > t.salary and t.deptname =  '生物系'

5. 查找其大楼名称包含子字符串‘Watson’的所有部门的名称；
select d.deptname 
from department as d 
where d.building = '%Watson%'

6. 使用 union 属性，找到 2009 年秋季或 2010 年春季或两者授课的所有课程集；
(select courseid
    from  section
    where semester = 'Fall' and year = 2009)
union
(select courseid
    from  section
    where semester = 'Spring' and year = 2010);

7. 找到所有出现在教员关系中且薪水为空值的教员；
select i.name from instructor as i where i.salary is null;

选做题

1.

（ 1 ）检查下表是否符合 1NF 规范，如果不是请改进，

EMP_PHONE中包含了两个信息，可以改为EMP_PHONE1和EMP_PHONE2

（ 2 ）检查下表是否符合 2NF 规范，如果不是请改进，

对于SUBJECT可以通过TEACHER_ID来确定，因此在这里#TEACHER_AGE显得多余

可以将SUBJECT表进行拆分消除依赖

（ 3 ）检查下表是否符合 3NF 规范，如果不是请改进，

EMP_STATE与EMP_CITY之间存在依赖关系

删除EMP_STATE,将EMP_STATE与EMP_CITY单独拆分成独立表

2. 构造一个B+树，包含以下键值：﴾2、 3 、 5 、 7 、 11 、 17 、 19 、 23 、 29 、31﴿，假设树最初是空的，值是按升序添加的，不会告诉你阶层为 4 比较合适的 ，画图！需要画出每一步！。