drop table sc;
drop table course;
drop table student;
drop table teacher;
drop table dep;

create table dep
(
dno number(2),
dname varchar2(30),
director number(4),
tel varchar2(8)
);

create table teacher
(
tno number(4),
tname varchar2(10),
title varchar2(20),
hiredate date,
sal number(7,2),
bonus number(7,3),
mgr number(4),
deptno number(2)
);
create table student
(
sno number(6),
snmae varchar2(8),
sex varchar2(2),
birth date,
passwd varchar2(8),
dno number(2)
);
create table course(
cno varchar2(8),
cname varchar2(20),
credit number(1),
ctime number(2),
quota1 number(3)
);

create table sc
(sno number(6),
cno varchar2(8),
grade number(3));
alter table dep add (constraint pk_deptno primary key(dno));
alter table dep add (constraint dno_number_check check(dno>=10 and dno<=50));
alter table dep modify(tel default 62795032);
alter table student add (constraint pk_sno primary key(sno ));
alter table student add (constraint sex_check check(sex='男' or sex='女'));
alter table student modify(birth default sysdate);
alter table course add (constraint pk_con primary key(cno));
alter table sc add (constraint pk_key primary key(cno,sno));
alter table teacher add (constraint pk_tno primary key(tno));
alter table sc add (foreign key(cno) references course(cno));
alter table sc add (foreign key(sno) references student(sno));
alter table student add (foreign key(dno) references dep(dno));
alter table teacher add (foreign key(deptno) references dep(dno));

insert into dep values(10,'计算机系',9469,'12345678');
insert into dep values(20,'自动化系',9581,'12345678');
insert into dep values(30,'无线电系',9791,'12345678');
insert into dep values(40,'信息管理系',9611,'12345678');
insert into dep values(50,'微纳电子系',2031,'12345678');

insert into teacher values(9468,'charles','professor','17-12月-2004',8000,1000,null,10);
insert into teacher values(9469,'smith','professor','17-12月-2004',5000,1000,9468,10);
insert into teacher values(9470,'allen','associate professor','20-2月-2003',4200,500,9469,10);
insert into teacher values(9471,'word','professor','17-12月-2004',8000,1000,9469,20);
insert into teacher values(9581,'jones','professor','2-4月-2003',6500,1000,9468,20);
insert into teacher values(9582,'martin','associate professor','28-9月-2005',4000,800,9581,20);
insert into teacher values(9583,'blake','lecture','1-5月-2006',3000,300,9581,30);
insert into teacher values(9791,'clark','professo','9-6月-2003',5500,null,9468,30);
insert into teacher values(9792,'scott','assciate professor','09-12月-2004',4500,null,9791,30);
insert into teacher values(9793,'baggy','lecturer','17-11月-2004',3000,null,9791,30);
insert into teacher values(9611,'turner','professor','8-9月-2005',6000,1000,9468,40);
insert into teacher values(9612,'adams','assciate professor','12-1月-2004',4800,80,9611,40);
insert into teacher values(9613,'james','lecturer','3-12月-2006',2800,200,9611,40);
insert into teacher values(2031,'ford','rofessor','3-12月-2005',5500,null,9468,50);
insert into teacher values(2032,'milier','assciate professor','12-1月-2005',4300,null,2031,40);
insert into teacher values(2033,'migeal','lecturer','23-1月-2006',2900,null,2031,50);
insert into teacher values(2034,'peggy','lecturer','23-1月-2007',2500,null,2031,50);