数据库设计大作业案例

1大型数据库期末大作业案例教材购销系统1.需求分析。2.数据库设计。2.1概念结构设计2．2逻辑结构设计。2.3物理结构设计。3.数据库实施。4.数据库运行。21．需求分析教材购销管理系统：教材购销管理系统是帮助管理人员、教材销售和库存的管理软件。该教材购销管理系统软件目前已有比较完善的管理与使用功能，该项目的用户是学校，开发本系统是为了教材购销管理系统更完善，为了教材购销对商品库存和销售的管理更方便，为了减轻工作人员的工作负担，以现代化的创新思维模式去工作。系统的功能要求：销售子系统（1）审查有效性学生将购书单交给教材科秘书，秘书通过查阅各班学生用书表、售书登记表检查是否允许该生买书。（2）开发票如果允许学生买书，教材科会计将按购书单查阅教材存量表，如果有书且数量够，则开交款发票，否则，将购书单中无法购买的书的信息写入缺书登记表，以备以后补售教材。（3）登记并开领书单教材科出纳员根据发票上的总金额收取现金，并进行售书登记，同时开领书单，学生将领书单交给保管员，领书。（4）补售教材采购部门进书后，教材科秘书根据进书通知和缺书登记表给没买到教材的学生发补售通知单。采购子系统（1）教材入库采购员采购教材后，将进书单交给秘书，秘书修改教材库存和待购量。（2）统计秘书每月按书号和出版社统计缺书，形成待购教材表和缺书单，供采购员采购时使用。32系统设计2.1概念结构设计E-R图是分为两部分实体和属性，每个实体可以有多个属性，这些属性用来表示实体的性质。不同实体之间可以用关系进行连接，表明各个实体之间的内在联系。实体和实体之间的关系有一对一的关系（1：1），一对多的关系（1：N）和多对多的关系（N：M）。各个实体分E-R图如下图所示学生教材保管员教材科秘书教材出纳员购买查阅数量检查是否允许购买领书单，发票写购书单交购书单是否够不够采购员4实体：教材，学生，购书单，教材库图1-1教材E-R图图1-2学生E-R图图1-3购书单E-R图图1-4教材库E-R图学生班级性别姓名学号专业购书单购买数量购书人书号购书单号教材库库存量图书管理员书号库号年龄购书单单价出版社书名书号5图1-2出版社E-R图总体E-R图及各个实体之间的关系如下图所示图2-1教材购销管理ER图学生购书单购书单细节书库对应填写nm1n书库买教材教材n1教材库交纳书费出版社电话地址出版社名shehao出版社号62.2逻辑结构设计E-R图向关系模型的转换关系模型的逻辑结构是一组关系模式的集合。E-R图则是由实体，实体的属性和实体间的联系三个要素组成。所以将E-R图转换为关系模型实际上就是要将实体，实体的属性和实体间的联系转换为关系模式。转换原则如下。1.实体类型的转换:一个实体型转换成一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。2．联系类型的转换，根据不同的情况做不同的处理。⑴一个1：1的联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，每个实体的码均是该关系的候选码。如果与某一端实体对应的关系模式合并，则需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。⑵一个１：N的联系可以转换为一个独立的关系模式，也可以与N端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为N端实体的码。⑶一个M：N联系转换为一个关系模式。与该联系相连的各实体的码为各实体码的组合。⑷三个或三个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。⑸具有相同码的关系模式可合并。3.根据销售信息管理系统的E-R图转换为关系模型如下。将每一个实体转换成一个关系（关系就是给出关系名，属性就是实体属性，并标明该关系的主键用下划线来表示）关系模式：学生（学号，姓名，性别，年龄，所在系，班级），主码为学号，符合三范式。购书单（购书单号，书号，购买数量,购书人学号），主码为购书单号，符合三范式。7教材库（图书管理员，库号，书号，库存量）主键为图书管理员，库号符，合三范式。教材（书号，书名，单价，出版社）主码为书号，符合三范式。2.3物理结构设计表3-1学生表Student字段名称中文名称类型长度取值范围是否非空主关键字备注Sno学号char10NY外码Sname姓名char30NNSsex性别char10男、女NNSge年龄char1018～28NNSdept专业char10NNSclass班级char10NN表3-2购书单表BuyBook字段名称中文名称类型长度取值范围是否非空主关键字备注BBno购书单号char100000～9999NYBno书号char100000～9999NN外码BBamount购买数量int10NNSno购书人学号char10NN外码表3-3教材库表StackRoom字段名称中文名称类型长度取值范围是否非空主关键字备注SRmanager图书管理员char100000～9999NYSRno库号char100000～9999NNBno书号char100000～9999NN外码SRamount库存量int10NN表3-4教材表Book字段名称中文名称类型长度取值范围是否非空主关键字备注Bno书号char100000～9999N外码8Bname书名char30NNBprice单价char10NNPno出版社号char100000～9999NN表3-5出版社表Publish字段名称中文名称类型长度取值范围是否非空主关键字备注Pno出版社号char100000～9999NY外码Pname出版社名char30NNPphone电话char30NNPadd地址char30NN3系统实施3.1数据库实现创建数据库:createdatabase教材购销创建基本表:（1）学生表：学号，姓名，性别，年龄，所在系，班级createtableStudent(Snochar(10)primarykeynotnull,Snamechar(30)notnull,Ssexchar(10)notnull,Sagechar(10)notnull,Sdeptchar(10)notnull,Sclasschar(10)notnull,foreignkeySnoreferencesBuyBook(Sno))（2）购书单表：购书单号，书号，购买数量,购书人学号createtableBuyBook9(BBnochar(10)primarykeynotnull,Bnochar(10)notnull,BBamountint(10)notnull,Snochar(10)notnull,foreignkeySnoreferencesStudent(Sno),foreignkeyBnoreferencesBook(Bno))(3)教材库表：图书管理员，库号，书号，库存量createtableStackRoom(SRmanagerchar(10)notnull,SRnochar(10)notnull,Bnochar(10)notnull,SRamountint(10)notnull,primarykey(SRno,SRmanager),foreignkeyBnoreferencesBook(Bno))（4）教材表：书号，书名，单价，出版社createtableBook(Bnochar(10)primarykeynotnull,Bnamechar(30)notnull,Bpricechar(10)notnull,Pnochar(10)notnull,foreignkeyPnoreferencesPublic(Pno))(5)出版社表：createtablePublic(Pnochar(10)primarykeynotnull,Pnamechar(30)notnull,Pphonechar(30)notnull,Paddchar(30)notnull,10)3.2数据载入图4-1学生表数据图4-2购书单表数据图4-3教材库表数据图4-4教材表数据11图4-5出版社表数据4．运行(1)各班学生用书表：班级、书号、书名、购买数量selectdistinctSclass,Book.Bno,Bname,BBamountfromStudent,BuyBook,BookwhereBuyBook.Sno=Student.SnoandBuyBook.Bno=Book.Bno图5-1各班学生用书表(2)售书登记表：学号、姓名、书号、购买数量selectStudent.Sno,Sname,Bno,BBamountfromStudent,BuyBookwhereBuyBook.Sno=Student.Sno图5-2售书登记表(3)缺书登记表：学号、姓名、书号、缺书数量selectStudent.Sno,Student.Sname,BuyBook.Bno,BBamount-SRamountamountfromStudent,BuyBook,StackRoom,Book12whereBuyBook.Sno=Student.SnoandBuyBook.Bno=StackRoom.BnoandBook.Bno=BuyBook.BnoandBuyBook.BBamountStackRoom.SRamount图5-2缺书登记表(4)教材存量表：书号、单价、库存量selectBook.Bno,Bprice,SRamountfromBook,StackRoom,BuyBookwhereBook.Bno=StackRoom.BnoandBook.Bno=BuyBook.Bno图5-4教材存量表(5)待购教材表：书号、缺书数量selectBuyBook.Bno,BBamount-SRamountamountfromBuyBook,StackRoomwhereBuyBook.Bno=StackRoom.BnoandBuyBook.BBamountStackRoom.SRamount图5-5待购教材表