【数据库】【实验】建立school数据库

1. 为某学校教务管理系统数据库建立school数据库。
2. 在school下建立如下数据库表，根据表中数据选取合适的数据类型及宽度，设置各表的主键及表间外键联系；

 

以下是sqlserver语句

（注意：所有类型我都设为了字符，可自行修改）：

CREATE TABLE D (
    Yxh char(50) NOT NULL,
    mc char(50) NOT NULL,
    dz char(50) NOT NULL,
    lxdh char(50) NOT NULL,
    PRIMARY KEY (Yxh));

CREATE TABLE S (
    Xh char(50) NOT NULL,
    xm char(50) NOT NULL,
    xb char(10) NOT NULL,
    csrq char(50) NOT NULL,
    jg char(50) NOT NULL,
    sjhm char(50) NOT NULL,
    yxh char(50) NOT NULL,
    Zt char(50) NULL,
    PRIMARY KEY (Xh),
    Foreign key (yxh) references D(Yxh));
    
CREATE TABLE T (
    Gh char(50) NOT NULL,
    xm char(50) NOT NULL,
    xb char(10) NOT NULL,
    csrq char(50) NOT NULL,
    Zc char(50) NOT NULL,
    yxh char(50) NOT NULL,
    PRIMARY KEY (Gh),
    Foreign key (yxh) references D(Yxh));

CREATE TABLE C (
    kh char(50) NOT NULL,
    km char(50) NOT NULL,
    xf char(10) NOT NULL,
    xs char(10) NOT NULL,
    yxh char(50) NOT NULL,
    PRIMARY KEY (kh),
    Foreign key (yxh) references D(Yxh));


CREATE TABLE O (
    xq char(30) NOT NULL,
    kh char(50) NOT NULL,
    gh char(50) NOT NULL,
    sksj char(50) NOT NULL,
    PRIMARY KEY (xq,kh,gh),
    Foreign key (kh) references C(kh),
    Foreign key (gh) references T(Gh));
    

CREATE TABLE E (
    Xh char(50) NOT NULL,
    xq char(30) NOT NULL,
    kh char(50) NOT NULL,
    gh char(50) NOT NULL,
    cj char(10) NULL,
    PRIMARY KEY (Xh,xq,kh,gh),
    Foreign key (Xh) references S(Xh),
    Foreign key (xq,kh,gh) references O(xq,kh,gh));


//---分割线---
insert into D(Yxh,mc,dz,lxdh)
values
('01','计算机学院','上大东校区三号楼','65347567'),
('02','通讯学院','上大东校区二号楼','65341234'),
('03','材料学院','上大东校区四号楼','65347890');

insert into S(Xh,xm,xb,csrq,jg,sjhm,yxh)
values
('1101','李明','男','1993-03-06','上海','13613005486','02'),
('1102','刘晓明','男','1992-12-08','安徽','18913457890','01'),
('1103','张颖','女','1993-01-05','江苏','18826490423','01'),
('1104','刘晶晶','女','1994-11-06','上海','13331934111','01'),
('1105','刘成刚','男','1991-06-07','上海','18015872567','01'),
('1106','李二丽','女','1993-05-04','江苏','18107620945','01'),
('1107','张晓峰','男','1992-08-16','浙江','13912341078','01')
;

insert into T(Gh,xm,xb,csrq,Zc,yxh)
values
('0101','陈迪茂','男','1973-03-06','副教授','01'),
('0102','马小红','女','1972-12-08','讲师','01'),
('0201','张心颖','女','1960-01-05','教授','02'),
('0103','吴宝钢','男','1980-11-06','讲师','01')
;

insert into C(kh,km,xf,xs,yxh)
values
('08305001','离散数学','4','40','01'),
('08305002','数据库原理','4','50','01'),
('08305003','数据结构','4','50','01'),
('08305004','系统结构','6','60','01'),
('08301001','分子物理学','4','40','03'),
('08302001','通信学','3','30','02')
;

insert into O(xq,kh,gh,sksj)
values
('201201','08305001','0103','星期三5-8'),
('201202','08305002','0101','星期三1-4'),
('201202','08305002','0102','星期三1-4'),
('201202','08305002','0103','星期三1-4'),
('201202','08305003','0102','星期五5-8'),
('201301','08305004','0101','星期二1-4'),
('201301','08305001','0102','星期一5-8'),
('201302','08302001','0201','星期一5-8')
;

insert into E(Xh,xq,kh,gh,cj)
values
('1101','201201','08305001','0103','60'),
('1102','201201','08305001','0103','87'),
('1102','201202','08305002','0101','82'),
('1102','201301','08305004','0101',null),
('1103','201201','08305001','0103','56'),
('1103','201202','08305002','0102','75'),
('1103','201202','08305003','0102','84'),
('1103','201301','08305001','0102',null),
('1103','201301','08305004','0101',null),
('1104','201201','08305001','0103','74'),
('1104','201302','08302001','0201',null),
('1106','201201','08305001','0103','85'),
('1106','201202','08305002','0103','66'),
('1107','201201','08305001','0103','90'),
('1107','201202','08305003','0102','79'),
('1107','201301','08305004','0101',null)
;