博客

假设每个学生选修若干门课程，且每个学生每选一门课只有一个成绩，每个教师只担任一门课的教学,一门课由若干教师任教。“学生”有属性：学号、姓名、地址、年龄、性别。“教师”有属性：职工号、教师姓名、职称，“课程”有属性：课程号、课程名。

请画出E-R图

概念模型：

物理模型：

生成对应数据库代码：

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2022/2/23 0:01:47                            */
/*==============================================================*/


drop table if exists course;

drop table if exists student;

drop table if exists student_course;

drop table if exists teacher;

/*==============================================================*/
/* Table: course                                                */
/*==============================================================*/
create table course
(
   courseid             int not null,
   cname                varchar(16),
   primary key (courseid)
);

/*==============================================================*/
/* Table: student                                               */
/*==============================================================*/
create table student
(
   studentid            int not null,
   sname                varchar(16),
   age                  int,
   address              varchar(255),
   sex                  varchar(4),
   primary key (studentid)
);

/*==============================================================*/
/* Table: student_course                                        */
/*==============================================================*/
create table student_course
(
   studentid            int not null,
   courseid             int not null,
   score                float,
   primary key (studentid, courseid)
);

/*==============================================================*/
/* Table: teacher                                               */
/*==============================================================*/
create table teacher
(
   teacherid            int not null,
   courseid             int,
   tname                varchar(16),
   post                 varchar(16),
   primary key (teacherid)
);

alter table student_course add constraint FK_student_course foreign key (studentid)
      references student (studentid) on delete restrict on update restrict;

alter table student_course add constraint FK_student_course2 foreign key (courseid)
      references course (courseid) on delete restrict on update restrict;

alter table teacher add constraint FK_course_teacher foreign key (courseid)
      references course (courseid) on delete restrict on update restrict;

随机生成5条测试数据：

insert into student (studentid, sname, age, address, sex) values (2, 'JHA7N31A8WM1D6AG', 2, 'TH TXFHBBX3D8TFTNOGUEXXOEYO9O9U3TCMP2KHERJPXW8HSUE9V3P6TKV4CR9OBQD14RPLAW2SMMADW08M9AR7R0 PMGDI5WUTP9CCGEO0M15YSFMO1F1EIW48 3NM7J8MP6WYHVU9NBQP496MMCAV59FLXJVS7SDATKFDYTAHYYPUNNUX0A0AR28XG1NJVRCSBWU2GM3VAVJ73LW4RWH0I59E45B1YCIB1 UANLIQ EI98 S Y8T4JAGG  04', '    ');

insert into student (studentid, sname, age, address, sex) values (4, '2Q6 5FD69B5KEDH9', 0, 'BGI594VW8 7JVAM5WOCC4STA050MOXO8PUBRV 7EWW XBPPPXELAGE5U7BILAU0I072R9MVYDE1O75CC6RX KD9SCAO9I0IR44BXCIMDFDSP0LY8KAYTOCARQFMNJ1GEX3T7LK36VO 5NGN40GL9HH84N1QV32UKFILOY2OHK3K7MFEFHM2097VRHCLBGHK KN0L PG3C9VCYQX8 0TVRGQDMG5GD872ESPCQLFG3QG38XQAKTLTBK67FCMX7L5', '   0');

insert into student (studentid, sname, age, address, sex) values (0, 'WTIRKT4RL9AB5LV ', 4, 'VX JHR99OR 46I02083G0R687D HJS7L689PP6LHJAE0GFE1E4UPPIHUYX6H7I8IBFLNXMXCFP8K04VH8CW DE OPMNO394KCNS45A XF PF AM0T244MXL6CY2DGJUDX2L6KVX2A6WLR3A27F4B71PJGIY8PAN5GWO2HLADEFTATCVT2PK 190S9VS3F0B0XCKEF708TSC1T9T7Q3XW7LORMPX5CSYU PP112GU6FBPKPY17WN0WL9 2MLUD52', '   1');

insert into student (studentid, sname, age, address, sex) values (1, 'WP7SIFOFXY9KOX U', 1, 'RPOS4RFV5DJQUV98EW98AKMG90SHA3G8BDOOWQ 0NVLD59 7WHEYLLU3 T0DDO15I1TVREHBTVLWOJAGIPD2H2BYTVIDME7EEVSFSS2HV1RWRWYVW13YTKRC3M6YREQY5KR6RXVG4KPTHJPLSEVW5LNW52CF4V964E9Q XWH39FUGM334AIVRSGHMQPWQ2CKR38JLW14FBDW1FO3YS4PGRKA8ME799G2R 2Q7 45 LET88TKIF9XS67RVBY0 PX', '   2');

insert into student (studentid, sname, age, address, sex) values (3, '1YW4GR8PQL DHX1I', 3, 'TWUH99DUPOFO2OCWU6BXXYQLNLXCQWR2763NYP8U3NNX O43GI2HUS5SXM2VKNR2YOL698UM17P5O7WKA6VOKT7QY9XBJI4Y2OR1I6GOGSIS0U3CG2CQS4NE827UCF8STNKLSJDBJYIROV6DD6S61LBIOQ40M0JFR4HO WYSTHPB28KIQOW3JN2TF2FTMIKOQWKGQMI81AM4EIRIJV TCFRWU1ELITS9UTBIHLIRAEB9QXC1 8V69PBAPTW56IS', '   3');

insert into course (courseid, cname) values (0, 'M0XDAO169KBIY7ED');

insert into course (courseid, cname) values (4, '0WM6SWMQJ7Y27 TX');

insert into course (courseid, cname) values (1, '85M9 009CAEW5WR1');

insert into course (courseid, cname) values (3, 'BDNLIFLM8E2L1C77');

insert into course (courseid, cname) values (2, '1QPURT5WKAW WB42');

insert into student_course (studentid, courseid, score) values (2, 0, 3);

insert into student_course (studentid, courseid, score) values (4, 4, 2);

insert into student_course (studentid, courseid, score) values (0, 1, 0);

insert into student_course (studentid, courseid, score) values (1, 3, 4);

insert into student_course (studentid, courseid, score) values (3, 2, 1);

insert into teacher (teacherid, courseid, tname, post) values (2, 2, '11EQGIXXLA5MREVJ', '6RWGLWHV606HOBR3');

insert into teacher (teacherid, courseid, tname, post) values (4, 0, 'JDBTMMJSJ3EMYRBF', 'GFDSXIL5MK2NL64M');

insert into teacher (teacherid, courseid, tname, post) values (0, 3, 'Y7ETB0T2GS9VM5VE', 'E0SLEIAHTAMR9F E');

insert into teacher (teacherid, courseid, tname, post) values (1, 3, 'PV6 EUYI96INPRM9', 'VFWD2UW9Y68EKGXX');

insert into teacher (teacherid, courseid, tname, post) values (3, 3, '5J64HEN7FESAD485', 'FWB N23XMPCFHVTR');