SQL数据库系统

数据库系统概论

一. 建表

设有一个SPJ数据库，包括：S，P，J，SPJ4个关系模式：
S(SNO，SNAME，STATUS，CITY)    SNO为主关键字
P(PNO，PNAME，COLOR，WEIGHT)   PNO为主关键字
J(JNO，JNAME，CITY)            JNO为主关键字
SPJ(SNO，PNO，JNO，QTY)        SNO，PNO，JNO三者组合为主关键字;并且SNO,PNO,JNO分别为S，P，J表的外键

供应商表S由供应商代码(SNO)，供应商姓名(SNAME)，供应商状态(STATUS)，供应商所在城市(CITY)组成。
零件表P由零件代码(PNO)，零件名(PNAME)，颜色(COLOR)，重量(WEIGHT)组成。
工程项目表J由工程项目代码(JNNO)，工程项目名(JNAME)，工程项目所在城市(CITY)组成。
供应情况表SPJ由供应商代码(SNO)，零件代码(PNO)，工程项目代码(JNO)，供应数量(QTY)组成，表示某供应商供应某种零件给某工程项目的数量为QTY。

create database spj
use spj

create table s
 (
    sno char(3),
    sname char(10),
    status char(2),
    city char(10),
    primary key(sno)
  )


create table p
 (
    pno char(3),
    pname char(10),
    color char(4),
    weight int,
    primary key(pno)
  )

create table j
 (
    jno char(3),
    jname char(10),
    city char(10),
    primary key(jno)
  )

create table spj
 (
    sno char(3),
    pno char(3),
    jno char(3),
    qty int,
    primary key(sno,pno,jno),
    foreign key(sno) references s(sno),
    foreign key(pno) references p(pno),
    foreign key(jno) references j(jno)
  )



INSERT INTO s VALUES('s1','精益','20','天津')
INSERT INTO s VALUES('s2','盛锡','10','北京')
INSERT INTO s VALUES('s3','东方红','30','北京')
INSERT INTO s VALUES('s4','丰泰盛','20','天津')
INSERT INTO s VALUES('s5','为民','30','上海')

INSERT INTO p VALUES('p1','螺母','红',12)
INSERT INTO p VALUES('p2','螺栓','绿',17)
INSERT INTO p VALUES('p3','螺丝刀','蓝',14)
INSERT INTO p VALUES('p4','螺丝刀','红',14)
INSERT INTO p VALUES('p5','凸轮','蓝',40)
INSERT INTO p VALUES('p6','齿轮','红',30)

INSERT INTO j VALUES('j1','三建','北京')
INSERT INTO j VALUES('j2','一汽','长春')
INSERT INTO j VALUES('j3','弹簧厂','天津')
INSERT INTO j VALUES('j4','造般厂','天津')
INSERT INTO j VALUES('j5','机车厂','唐山')
INSERT INTO j VALUES('j6','无线电厂','常州')
INSERT INTO j VALUES('j7','半导体厂','南京')

INSERT INTO spj VALUES('s1','p1','j1',200)
INSERT INTO spj VALUES('s1','p1','j3',100)
INSERT INTO spj VALUES('s1','p1','j4',700)
INSERT INTO spj VALUES('s1','p2','j2',100)

INSERT INTO spj VALUES('s2','p3','j1',400)
INSERT INTO spj VALUES('s2','p3','j2',200)
INSERT INTO spj VALUES('s2','p3','j4',500)
INSERT INTO spj VALUES('s2','p3','j5',400)
INSERT INTO spj VALUES('s2','p5','j1',400)
INSERT INTO spj VALUES('s2','p5','j2',100)

INSERT INTO spj VALUES('s3','p1','j1',200)
INSERT INTO spj VALUES('s3','p3','j1',200)

INSERT INTO spj VALUES('s4','p5','j1',100)
INSERT INTO spj VALUES('s4','p6','j3',300)
INSERT INTO spj VALUES('s4','p6','j4',200)

INSERT INTO spj VALUES('s5','p2','j4',100)
INSERT INTO spj VALUES('s5','p3','j1',200)
INSERT INTO spj VALUES('s5','p6','j2',200)
INSERT INTO spj VALUES('s5','p6','j4',50)