MySQL实现关系代数运算

MySQL实现关系代数运算

笛卡尔积

• 两表的所有元组

select * from department;

select * from instructor;

• 笛卡尔积结果

select * from instructor, department;

并运算

• 两表的所有元组

select * from department

select * from department0

• 并运算结果

select * from department
union
select * from department0;

交运算

• 两表的所有元组
  
• 交运算

select * from department
inner join department0 on department.building = department0.building;

差运算

• 两表的所有元组
  

• 差运算 NOT IN

select * from department
where building not in
(select building from department0);

• 差运算 LFET JOIN

select * from department
left join department0 on department.building = department0.building
where department0.building is null;

选择

• 表
  

• 选择结果

投影

• 表
  
• 投影结果

select name from instructor;

自然连接

• 表
  

• 自然连接结果

select * from course natural join department;

初始化数据库

create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12, 2),
primary key (dept_name));

create table course(
course_id varchar(7),
title varchar(50),
credits numeric(2, 0),
primary key (course_id),
foreign key(dept_name) references department(dept_name));

create table instructor(
ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key(ID),
foreign key(dept_name) references department(dept_name));

create table section(
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),
primary key(course_id, sec_id, semester, year),
foreign key (course_id) references course(course_id));

create table teaches(
ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4, 0),
primary key(ID, course_id, sec_id, semester, year),
foreign key(course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year),
foreign key(ID) references instructor(ID));

insert into department
(dept_name, building, budget)
values
('CS', 'CS Building', 10000);

insert into department
(dept_name, building, budget)
values
('SE', 'SE Building', 10000);

insert into course
(course_id, title, dept_name, credits)
values
('10001', 'DS', 'CS', 5);

insert into course
(course_id, title, dept_name, credits)
values
('10002', 'OS', 'CS', 5);

insert into course
(course_id, title, dept_name, credits)
values
('10003', 'SE', 'SE', 5);

insert into instructor
(ID, name, dept_name, salary)
values
('10001', 'Mozart', 'CS', 10000);

insert into instructor
(ID, name, dept_name, salary)
values
('10002', 'Dennis', 'CS', 20000);

insert into instructor
(ID, name, dept_name, salary)
values
('10003', 'Kim', 'SE', 15000);

insert into section
(course_id, sec_id, semester, year, building, room_number, time_slot_id)
values
('10001', '10001', '1', 2020, 'CS Building', '101', '001');

insert into section
(course_id, sec_id, semester, year, building, room_number, time_slot_id)
values
('10002', '10002', '1', 2020, 'CS Building', '103', '002');

insert into section
(course_id, sec_id, semester, year, building, room_number, time_slot_id)
values
('10003', '10003', '1', 2020, 'SE Building', '203', '003');

insert into teaches
(ID, course_id, sec_id, semester, year)
values
('10002', '10001', '10001', '1', 2020);

insert into teaches
(ID, course_id, sec_id, semester, year)
values
('10001', '10002', '10002', '1', 2020);

insert into teaches
(ID, course_id, sec_id, semester, year)
values
('10003', '10003', '10003', '1', 2020);

鸣谢

数据库系统概论（第5版）
数据库系统概念（原书第6版）

最后

• 由于博主水平有限，不免有疏漏之处，欢迎读者随时批评指正，以免造成不必要的误解