数据库设计大作业案例

(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)