關聯式代數(Relational Algebra) ~ 實作

學生資料表(學生編號, 身分證字號, 學生姓名, 科系編號, 年級, 班級)
Student(sid, idno, sname, did, syear, sclass)

create table Student(
sid char(5) not null,
idno char(10),
sname char(20),
did char(5),
syear int,
sclass int,
primary key (sid));

insert into Student(sid, idno, sname, did, syear, sclass) values ('s0001', 'A123456789', 'John01', 'd0001', 1, 1);
insert into Student(sid, idno, sname, did, syear, sclass) values ('s0002', 'A123456780', 'John02', 'd0001', 1, 1);

insert into Student(sid, idno, sname, did, syear, sclass) values ('s0003', 'A123456799', 'John03', 'd0001', 1, 2);

insert into Student(sid, idno, sname, did, syear, sclass) values ('s0004', 'A123456700', 'John04', 'd0002', 2, 1);

insert into Student(sid, idno, sname, did, syear, sclass) values ('s0005', 'A123456777', 'John05', 'd0003', 3, 1);

insert into Student(sid, idno, sname, did, syear, sclass) values ('s0006', 'A123456797', 'John06', 'd0003', 3, 2);

insert into Student(sid, idno, sname, did, syear, sclass) values ('s0007', 'A123456791', 'John07', 'd0003', 4, 1);

insert into Student(sid, idno, sname, did, syear, sclass) values ('s0008', 'A123456792', 'John08', 'd0004', 4, 1);

insert into Student(sid, idno, sname, did, syear, sclass) values ('s0009', 'A123456793', 'John09', 'd0005', 4, 1);

insert into Student(sid, idno, sname, did, syear, sclass) values ('s0010', 'A123456794', 'John10', 'd0005', 4, 2);

老師資料表(老師編號, 身分證字號, 老師姓名, 科系編號, 老師類別)
Teacher(tid, idno, tname, did, ttype)

create table Teacher(
tid char(5) not null,
idno char(10),
tname char(20),
did char(5),
ttype int,
primary key (tid));

insert into Teacher(tid, idno, tname, did, ttype) values ('t0001', 'A123456789','Tom01','d0001',1);
insert into Teacher(tid, idno, tname, did, ttype) values ('t0002', 'B123456782','Tom02','d0002',2);
insert into Teacher(tid, idno, tname, did, ttype) values ('t0003', 'B123456783','Tom03','d0003',1);
insert into Teacher(tid, idno, tname, did, ttype) values ('t0004', 'B123456784','Tom04','d0004',3);
insert into Teacher(tid, idno, tname, did, ttype) values ('t0005', 'B123456785','Tom05','d0005',4);

課程資料表(課程編號, 課程名稱, 科系代號, 學分數, 選修別)
Course(cid, cname, did, ccredit, ctype)

create table Course(
cid char(5) not null,
cname char(10),
did char(5),
ccredit int,
ctype int,
primary key(cid));

insert into Course(cid, cname, did, ccredit, ctype) values ('c0001', 'math01', 'd0001', 3, 1);

insert into Course(cid, cname, did, ccredit, ctype) values ('c0002', 'math02', 'd0002', 2, 0);

insert into Course(cid, cname, did, ccredit, ctype) values ('c0003', 'math03', 'd0003', 2, 0);

insert into Course(cid, cname, did, ccredit, ctype) values ('c0004', 'math04', 'd0004', 3, 1);

insert into Course(cid, cname, did, ccredit, ctype) values ('c0005', 'math05', 'd0005', 3, 1);

開課資料表(開課編號, 課程編號, 老師編號, 修課年級限制, 教室編號)
OpenCourse(oid, cid, tid, yearlimit, roomno)

create table OpenCourse(
oid char(5) not null,
cid char(5),
tid char(5),
yearlimit int,
roomno char(5),
primary key (oid));

insert into OpenCourse(oid, cid, tid, yearlimit, roomno) values ('o0001', 'c0001', 't0001',1,'r0001');
insert into OpenCourse(oid, cid, tid, yearlimit, roomno) values ('o0002', 'c0002', 't0002',1,'r0002');
insert into OpenCourse(oid, cid, tid, yearlimit, roomno) values ('o0003', 'c0003', 't0003',2,'r0003');
insert into OpenCourse(oid, cid, tid, yearlimit, roomno) values ('o0004', 'c0004', 't0004',2,'r0004');
insert into OpenCourse(oid, cid, tid, yearlimit, roomno) values ('o0005', 'c0005', 't0001',2,'r0005');
insert into OpenCourse(oid, cid, tid, yearlimit, roomno) values ('o0006', 'c0001', 't0001',3,'r0001');

時間資料表(開課編號,  開課時間編號)
Schedule(oid, timeno)

create table Schedule(
oid char(5),
timeno char(2));

insert into Schedule(oid, timeno) values ('o0001', '11');
insert into Schedule(oid, timeno) values ('o0001', '12');
insert into Schedule(oid, timeno) values ('o0001', '13');
insert into Schedule(oid, timeno) values ('o0002', '22');
insert into Schedule(oid, timeno) values ('o0002', '23');
insert into Schedule(oid, timeno) values ('o0003', '43');
insert into Schedule(oid, timeno) values ('o0003', '44');
insert into Schedule(oid, timeno) values ('o0004', '51');
insert into Schedule(oid, timeno) values ('o0004', '52');
insert into Schedule(oid, timeno) values ('o0004', '53');
insert into Schedule(oid, timeno) values ('o0005', '16');
insert into Schedule(oid, timeno) values ('o0005', '17');
insert into Schedule(oid, timeno) values ('o0005', '18');
insert into Schedule(oid, timeno) values ('o0006', '12');
insert into Schedule(oid, timeno) values ('o0006', '13');
insert into Schedule(oid, timeno) values ('o0006', '14');

選課資料表(學生編號,  開課編號, 成績)
Roll(sid, oid, score)

create table Roll(
sid char(5),
oid char(5),
score int,
primary key(sid,oid));

insert into Roll(sid, oid, score) values ('s0001','o0001', 90);
insert into Roll(sid, oid, score) values ('s0001','o0002', 80);
insert into Roll(sid, oid, score) values ('s0002','o0003', 95);
insert into Roll(sid, oid, score) values ('s0003','o0004', 70);
insert into Roll(sid, oid, score) values ('s0004','o0001', 67);

insert into Roll(sid, oid, score) values ('s0005','o0003', 92);

insert into Roll(sid, oid, score) values ('s0005','o0002', 90);

科系資料表(科系編號, 科系名稱)
Dept(did, dname)

create table Dept(
did char(5) not null,
dname char(10),
primary key (did));

insert into Dept(did, dname) values ('d0001','MIS');
insert into Dept(did, dname) values ('d0002','MDE');
insert into Dept(did, dname) values ('d0003','IECS');
insert into Dept(did, dname) values ('d0004','ENG');
insert into Dept(did, dname) values ('d0005','EE');

限制(Restrict)，代表符號：σ

σ _did='d0001'(Course)

列出科系編號為'd0001'的課程資料
Select * from Course Where did = 'd0001';

投影(Project) ，代表符號：π

π_cid,cname(Course)

列出課程資料的課程編號及課程名稱
Select cid, cname from Course;

聯集(Union) ，代表符號：∪

Student ∪ Teacher

列出學生與老師資料聯集的~身分證字號，並以身分證字號由小到大
SELECT idno FROM Student UNION SELECT idno FROM Teacher ORDER BY idno;

卡氏積(Cartesian Product) ，代表符號：×

Student×Teacher

列出學生與老師資料的卡氏積
Select * from Student, Teacher;

差集(Difference) ，代表符號：─

Student─Teacher

列出沒有兼有老師身分的學生資料
Select * from Student Where idno not in (Select idno From Teacher);

交集(Intersection) ，代表符號：∩

Student∩Teacher

列出兼有老師身分與學生身分的資料
Select * from Student Where idno in (Select idno From Teacher);

合併(Join) 可以看成先把 R1 與 R2 做乘積運算(Cartesian Product) 後，再依關聯條件p做選擇運算(Select) 來篩選出合乎關聯條件的記錄。

R1⋈pR2=σp(R1×R2)

關聯運算會因為比較運算子不同而產生不同的結果。

θ-合併(Theta Join) : 

列出學生與開課資料合併，並且符合syear >= yearlimit
SELECT * FROM Student CROSS JOIN OpenCourse where syear >= yearlimit;
等於
SELECT * FROM Student s, OpenCourse o WHERE s.syear >= o.yearlimit;

對等合併(Equi-Join) :

列出學生與開課資料合併，並且符合syear = yearlimit
SELECT * FROM Student CROSS JOIN OpenCourse where syear = yearlimit;
等於
SELECT * FROM Student s, OpenCourse o WHERE s.syear  = o.yearlimit;

自然合併(Natural Join) : 自然合併(Natural Join)又稱為內部合併(Inner Join)，它必須在左右兩邊的關聯中找到對應值組才行。

列出學生的選課資料
SELECT * FROM Student s, Roll r WHERE s.sid = r.sid;

假設上圖中，左圓是老師資料表，右圓是課程資料表。 如果我們使用INNER JOIN，就是取得標示(1)的部分，語法如下:

[INNER JOIN]
SELECT * FROM tableA a INNER JOIN tableB b ON a.key=b.key;
SELECT * FROM tableA a INNER JOIN tableB b USING (key);
SELECT * FROM tableA a, tableB b WHERE a.key=b.key;

SELECT * FROM teacher t inner join opencourse c ON t.tid=c.tid;
列出有開課的老師
也可以寫成 SELECT * FROM teacher t, opencourse c WHERE t.tid=c.tid;
也可以寫成 SELECT * FROM teacher t  inner join opencourse c USING (tid);

SELECT * FROM opencourse c inner join teacher t ON t.tid=c.tid;
列出有老師開設的課程
也可以寫成 SELECT * FROM opencourse c, teacher t WHERE t.tid=c.tid;
也可以寫成 SELECT * FROM opencourse c inner join teacher t USING (tid);

使用SELECT * FROM A INNER JOIN B ON A.c=B.c 又稱為Natural JOIN (自然合併)。
使用SELECT * FROM A, B WHERE A.c=B.c 又稱為Equi JOIN (對等合併)。

如果我們使用LEFT OUTER JOIN，就是取得標示(2)的部分，語法如下:

[LEFT OUTER JOIN/LEFT JOIN]
SELECT * FROM tableA a LEFT OUTER JOIN tableB b ON a.key=b.key WHERE [conditions];
SELECT * FROM tableA a LEFT OUTER JOIN tableB b USING (key) WHERE [conditions];

取得還沒有開課的老師

SELECT * FROM teacher t LEFT OUTER JOIN opencourse c ON t.tid=c.tid
WHERE c.tid  IS NULL;

或是

SELECT * FROM teacher t LEFT JOIN opencourse c ON t.tid=c.tid
WHERE c.tid  IS NULL;

或是

SELECT * FROM teacher t LEFT JOIN opencourse c USING (tid)
WHERE c.tid  IS NULL;


如果都不使用JOIN也可以寫成 ...
select tid from teacher where tid not in (select t.tid from teacher t, opencourse c where c.tid=t.tid);

如果我們使用RIGHT OUTER JOIN，就是取得標示(3)的部分，語法如下:

[RIGHT OUTER JOIN/RIGHT JOIN]
SELECT * FROM tableA a RIGHT OUTER JOIN tableB b ON a.key=b.key WHERE [conditions];
SELECT * FROM tableA a RIGHT OUTER JOIN tableB b USING (key) WHERE [conditions];

取得還沒有老師的課程

SELECT * FROM teacher t RIGHT OUTER JOIN  opencourse  c ON t.tid=c.tid
WHERE c.tid  IS NULL;

或是

SELECT * FROM teacher t RIGHT JOIN  opencourse  c ON t.tid=c.tid
WHERE c.tid  IS NULL;

或是

SELECT * FROM teacher t RIGHT JOIN  opencourse  c USING t.tid=c.tid
WHERE c.tid  IS NULL;


如果都不使用JOIN也可以寫成 ...
select cid from  opencourse  where tid is null or tid not in (select tid from teacher);


如果我們使用FULL OUTER JOIN (因為MYSQL沒有支援)，使用RIGHT JOIN跟LEFT JOIN進行UNION，語法如下:

SELECT * FROM teacher t RIGHT JOIN  opencourse  c ON t.tid=c.tid
WHERE c.tid  IS NULL
UNION
SELECT * FROM teacher t LEFT JOIN  opencourse  c ON t.tid=c.tid
WHERE c.tid  IS NULL;

取得還沒有開課的老師, 以及還沒有老師的課程

問題 :
(1)請問如何找到學生s0001沒有衝堂的課程編號與課程名稱?
(2)請問如何找到學生s0001沒有衝堂的課程編號與老師姓名?
(3)這下面SQL是在做什麼?

select * from opencourse o,schedule s 

where o.oid=s.oid and o.oid='o0006' and concat(roomno,timeno)  in (

select concat(roomno,timeno) from opencourse o,schedule s 

where o.oid=s.oid and o.oid<>'o0006');