数据库第二次作业

1. 教师表 teacher

create table teacher(
    -> no char(4),
    -> name varchar(10) not null,
    -> prof varchar(20) not null default "assistant",
    -> sal int(2) not null,
    -> comm smallint(2),
    -> primary key(no)
    -> );

     2.学生表 student

create table student(
    -> no char(4),
    -> name varchar(10) not null,
    -> age tinyint(1) not null,
    -> dept varchar(20) not null default "computer",
    -> primary key(no)
    -> ); 

     3.课程表 course

 create table course(
    -> no char(4),
    -> name varchar(20) not null,
    -> class_hours int(2) default "45",
    -> primary key(no)
    -> );

     4.授课表 school_teaching

     create table schoolteaching(
    -> id int(4),
    -> course_no char(4) not null,
    -> teacher_no char(4) not null,
    -> week int(2) default "15",
    -> class_num varchar(10) not null,
    -> primary key(id),
    -> constraint fk_course_no foreign key (course_no) references course(no),
    -> constraint fk_teacher_no foreign key (teacher_no) references teacher(no)
    -> ); 

      5.成绩表 grade

create table grade(
    -> student_no char(4) not null,
    ->      course_no char(4) not null,
    ->     score float not null default "60",
    ->     primary key(student_no,course_no)
    ->      );