(BBno char (10) primary key not null, Bno char (10) not null, BBamount int(10) not null, Sno char (10) not null,
foreign key Sno references Student(Sno), foreign key Bno references Book(Bno) )
(3)教材库表：图书管理员，库号，书号，库存量 create table StackRoom (SRmanager char (10) not null, SRno char (10) not null, Bno char (10) not null, SRamount int(10) not null, primary key (SRno, SRmanager), foreign key Bno references Book(Bno) )
（4）教材表：书号，书名，单价，出版社 create table Book
(Bno char (10) primary key not null, Bname char (30) not null, Bprice char (10) not null, Pno char (10) not null,
foreign key Pno references Public(Pno) )
(5)出版社表： create table Public
(Pno char (10) primary key not null, Pname char (30) not null, Pphone char (30) not null, Padd char (30) not null,
9
)
3.2 数据载入
图4-1 学生表数据
图4-2 购书单表数据
图4-3教材库表数据
图4-4教材表数据
10
图4-5出版社表数据
．运行
(1)各班学生用书表：班级、书号、书名、购买数量 select distinct Sclass,Book.Bno,Bname,BBamount from Student,BuyBook,Book where BuyBook.Sno=Student.Sno and BuyBook.Bno=Book.Bno
图5-1各班学生用书表
(2)售书登记表：学号、姓名、书号、购买数量 select Student.Sno,Sname,Bno,BBamount from Student,BuyBook
where BuyBook.Sno=Student.Sno
图5-2售书登记表
(3)缺书登记表：学号、姓名、书号、缺书数量
select Student.Sno,Student.Sname,BuyBook.Bno,BBamount-SRamount amount from Student,BuyBook,StackRoom,Book
11
4
where BuyBook.Sno=Student.Sno and BuyBook.Bno=StackRoom.Bno and Book.Bno=BuyBook.Bno and
BuyBook.BBamount>StackRoom.SRamount
图5-2缺书登记表
教材存量表：书号、单价、库存量 select Book.Bno,Bprice,SRamount from Book,StackRoom,BuyBook where Book.Bno=StackRoom.Bno and Book.Bno=BuyBook.Bno
图5-4教材存量表
待购教材表：书号、缺书数量
select BuyBook.Bno,BBamount-SRamount amount from BuyBook,StackRoom
where BuyBook.Bno=StackRoom.Bno and BuyBook.BBamount >StackRoom.SRamount
图5-5待购教材表
12
(4)(5)
