数据库模块41题作业

 

作业练习：
http://www.cnblogs.com/wupeiqi/articles/5729934.html

制表语句：

制表语句：
    班级表
               Table: class
        Create Table: CREATE TABLE `class` (
          `cid` int(11) NOT NULL AUTO_INCREMENT,
          `caption` varchar(50) DEFAULT NULL,
          PRIMARY KEY (`cid`)
        ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
        1 row in set (0.00 sec)

        
    学生表：
                       Table: student
        Create Table: CREATE TABLE `student` (
          `sid` int(11) NOT NULL AUTO_INCREMENT,
          `sname` varchar(50) DEFAULT NULL,
          `gender` char(10) DEFAULT NULL,
          `class_id` int(11) DEFAULT NULL,
          PRIMARY KEY (`sid`),
          KEY `fk_student_class` (`class_id`),
          CONSTRAINT `fk_student_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
        ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
                

    老师表：
                       Table: teacher
        Create Table: CREATE TABLE `teacher` (
          `tid` int(11) NOT NULL AUTO_INCREMENT,
          `tname` varchar(50) DEFAULT NULL,
          PRIMARY KEY (`tid`)
        ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
        1 row in set (0.00 sec)
                        
                        
    课程表：
                      Table: course
        Create Table: CREATE TABLE `course` (
          `cid` int(11) NOT NULL AUTO_INCREMENT,
          `cname` char(25) DEFAULT NULL,
          `teacher_id` int(11) DEFAULT NULL,
          PRIMARY KEY (`cid`),
          KEY `fk_course_teacher` (`teacher_id`),
          CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
        ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
        1 row in set (0.00 sec)

    成绩表：    
            
        create table score
             (sid int not null auto_increment primary key,
              student_id int not null,
             corse_id int not null,
             number int not null,
             unique uq_sc (student_id,corse_id),
             
             CONSTRAINT fk_sc_st FOREIGN key (student_id) REFERENCES student(sid),
             constraint fk_sc_co foreign key  (corse_id) references course(cid)
             ) engine=innodb default charset=utf8;