数据库设计大作业2

数据库大作业1.要求：1)需求分析。

2)数据库设计。

2．1概念结构设计（E-R图）2．2逻辑结构设计（E-R模型转换为关系模式，指明所满足的范式并给出理由）。

2．3物理结构设计3)数据库实现。

（录入数据，每个表应不少于10条数据）4)数据库运行。

（不少于30条sql语句，其中包括查询、更新、删除、插入、创建触发器等sql语句）5)18周之前交。

2.可选题目：1.机票预定信息系统系统功能的基本要求：航班基本信息的录入，包括航班的编号、飞机名称、机舱等级等。

机票信息，包括票价、折扣、当前预售状态及经手业务员等。

客户基本信息，包括姓名、联系方式、证件及号码、付款情况等。

按照一定条件查询、统计符合条件的航班、机票等；对结果打印输出。

（1）、.机票预定信息系统通过此系统可以实现如下功能：1) 机票信息信息包括航班号、飞机名、目的地、起航时间、票价、折扣。

假设现在有3条航线, 目的地分别是北京, 上海, 广州, 飞机上可乘坐100人( 即初始订票数为0, 余票数为100) , 将这3条航线信息存入表“airline”中。

2) 客户基本信息客户信息包括姓名,性别，订票数，联系方式, 证件及号码, 假设已有3个客户信息存入表“customer”中。

有新客户订票时, 先输入客户的姓名和他提出的航班号, 查询该航线的订票情况, 若有余票, 则为客户办理订票手续,分配给客户一个座位号, 然后将新客户的信息添加到文件“customer.dat”中, 并修改文件“airline.dat”中该航线的订票数和余票数。

若无余票, 则输出客满信息。

进一步可实现如果该航班已经无票，可以提供相关可选择航班信息。

3）航班航班号, 飞机名称，机舱等级, 座位号(初始为0）3) 退票业务根据客户提出的航班号, 办理退票, 从表“customer”中删除该客户的信息, 并修改表“airline”中相应航线的订票数和余票数。

4) 修改航班信息：当航班信息改变可以修改航班数据文件。

5) 输出全部航线信息和全部客户信息。

6) 退出系统。

1）、航空订票系统：通过此系统可以实现如下功能：1) 录入航线信息每条航线信息包括航班号、飞机号、目的地、订票数、余票数共5项。

假设现在有3条航线, 目的地分别是北京, 上海, 广州, 飞机上可乘坐100人( 即初始订票数为0, 余票数为100) , 将这3条航线信息存入文件“airline.dat”中。

2) 订票业务客户信息包括姓名, 航班号, 座位号(初始为0), 假设已有3个客户信息存入文件“customer.dat”中。

有新客户订票时, 先输入客户的姓名和他提出的航班号, 查询该航线的订票情况, 若有余票, 则为客户办理订票手续,分配给客户一个座位号, 然后将新客户的信息添加到文件“customer.dat”中, 并修改文件“airline.dat”中该航线的订票数和余票数。

若无余票, 则输出客满信息。

进一步可实现如果该航班已经无票，可以提供相关可选择航班信息。

3) 退票业务根据客户提出的航班号, 办理退票, 从文件“customer.dat”中删除该客户的信息, 并修改文件“airline.dat”中相应航线的订票数和余票数。

4) 修改航班信息：当航班信息改变可以修改航班数据文件。

5) 输出全部航线信息和全部客户信息。

6) 退出系统。

3.报告格式模版：机票预定信息系统1．需求分析人们在网上预订机票越来越普遍，能提供网上预订服务的网站也越来越多，如：携程旅行网等，但这些网站提供的服务并非实时的，而是在人工干预的基础上完成的。

为了提升企业效益，特开发此网上预订机票系统。

系统的功能要求：机票订购子系统（1）审查旅客信息的有效性旅客将自己的个人信息提供给业务员，业务员通过计算机验证是否真实并允许该乘客购买机票。

（2）开发票如果允许旅客买机票，则业务员将查阅机票储量，如果有票足够旅客购买，则开交款发票，否则不开发票。

(3) 签名并交款当旅客拿到发票后查看无错，则签名并交款（可以现金，刷卡等形式交款），然后旅客就可以拿到机票了。

机票退票子系统（1）退票业务根据旅客提出的航班号, 办理退票,并从表“customer”中删除该客户的信息, 并修改表“airline”中相应航线的订票数和余票数。

（2）修改航班信息：当航班信息改变可以修改航班数据文件，然后输出全部航线信息和全部客户信息。

2系统设计2.1 概念结构设计E-R图是分为两部分实体和属性，每个实体可以有多个属性，这些属性用来表示实体的性质。

不同实体之间可以用关系进行连接，表明各个实体之间的内在联系。

实体和实体之间的关系有一对一的关系（1：1），一对多的关系（1：N）和多对多的关系（N：M）。

各个实体分E-R图如下图所示实体：航班，航线，机票，旅客总体E-R图及各个实体之间的关系如下图所示2.2 逻辑结构设计E-R图向关系模型的转换关系模型的逻辑结构是一组关系模式的集合。

E-R图则是由实体，实体的属性和实体间的联系三个要素组成。

所以将E-R图转换为关系模型实际上就是要将实体，实体的属性和实体间的联系转换为关系模式。

转换原则如下。

1. 实体类型的转换:一个实体型转换成一个关系模式。

实体的属性就是关系的属性，实体的码就是关系的码。

2．联系类型的转换，根据不同的情况做不同的处理。

⑴一个1：1的联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。

如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，每个实体的码均是该关系的候选码。

如果与某一端实体对应的关系模式合并，则需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。

⑵一个１：N的联系可以转换为一个独立的关系模式，也可以与N端对应的关系模式合并。

如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为N端实体的码。

⑶一个M：N联系转换为一个关系模式。

与该联系相连的各实体的码为各实体码的组合。

⑷三个或三个以上实体间的一个多元联系可以转换为一个关系模式。

与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。

⑸具有相同码的关系模式可合并。

3. 根据销售信息管理系统的E-R图转换为关系模型如下。

将每一个实体转换成一个关系（关系就是给出关系名，属性就是实体属性，并标明该关系的主键用下划线来表示）关系模式：航班（航班编号，飞机名，机舱数量，容客量），主码为航班号，符合三范式。

航线（航班号，起点，目的地，票价），主码航班号符合三范式。

机票（航班号，起飞时间，机舱等级，座位号，票价，折扣，预售状态，业务员），主码座位号，符合三范式。

旅客（姓名，性别，身份证号，付款情况，联系方式），主码为身份证号，符合三范式。

2.3 物理结构设计表3-1 旅客表3数据库实现3.1建库、建表创建数据库:create database 机票购销创建基本表:（1）旅客表：（姓名，性别，身份证号，付款情况，联系方式）create table 旅客(姓名 char (10) not null,性别 char (2) not null,身份证号 char (10) primary key not null,付款情况 char (10) not null,联系方式 char (10) not null)在旅客表中输入数据：insert into 旅客 values('黄汉杰','男','46001','付','26510310') insert into 旅客 values('李浩','男','46002','付','26601101') insert into 旅客 values('李思','女','46003','付','26665544') insert into 旅客 values('赵刚','男','46005','付','25510315')insert into 旅客 values('王可嘉','女','46006','付','26510317') insert into 旅客 values('王霞','女','46007','付','26689988') insert into 旅客 values('张平','男','47001','待付','26668888') insert into 旅客 values('吴晗','女','47002','待付','26667474') insert into 旅客 values('高飞','男','47003','付','25558989') insert into 旅客 values('黄莉','女','47004','付','26060303')（2）航班表：（航班号，飞机名，机舱等级,容客量）create table 航班( 航班号 char(10) primary key not null,飞机名 char(10) not null,机舱等级 char(10) not null,容客量 int not null)在航班表输入数据：insert into 航班 values('','','','')insert into 航班 values('0001','飞龙号','二等','100')insert into 航班 values('0002','飞鹰号','一等','120')insert into 航班 values('0003','和谐号','三等','110')insert into 航班 values('0004','海南号','一等','120')insert into 航班 values('0005','创新号','二等','100')insert into 航班 values('0006','北京号','一等','110')insert into 航班 values('0007','南京号','二等','110')insert into 航班 values('0008','上海号','一等','120')insert into 航班 values('0009','世界号','一等','100')insert into 航班 values('0010','美丽号','二等','150')insert into 航班 values('0011','追梦号','三等','150')(3)航线表：(航班号，起点，目的地，票价)create table 航线(航班号 char (10) primary key not null,起点 char (10) not null,目的地 char (10) not null,票价 int not null,foreign key (航班号) references 航班(航班号))在航线表中输入数据：insert into 航线 values('0004','海南','北京','1200')insert into 航线 values('0006','北京','海南','1200')insert into 航线 values('0001','广州','上海','1000')insert into 航线 values('0002','南京','上海','500')insert into 航线 values('0005','北京','上海','800')insert into 航线 values('0007','海南','上海','1000')insert into 航线 values('0009','成都','广州','900')insert into 航线 values('0003','杭州','北京','600')insert into 航线 values('0008','上海','北京','700')insert into 航线 values('0010','海南','广州','600')（4）机票表：（航班号，起飞时间，机舱等级，座位号，票价，折扣，预票状态，业务员）create table 机票(航班号char (10) not null,起飞时间char (10) not null,机舱等级char (10) not null,座位号int not null,票价int not null,折扣char(10) not null,预票状态char(10) not null,业务员char(10 not null,)primary key (航班号,座位号),foreign key (航班号) references 航班(航班号) );3.2 数据载入图4-1 旅客表数据图4-2 航班表数据图4-3航线表数据图4-4教材表数据图4-5出版社表数据4．运行(1)各班学生用书表：班级、书号、书名、购买数量select distinct Sclass,Book.Bno,Bname,BBamountfrom Student,BuyBook,Bookwhere BuyBook.Sno=Student.Sno andBuyBook.Bno=Book.Bno图5-1各班学生用书表(2)售书登记表：学号、姓名、书号、购买数量select Student.Sno,Sname,Bno,BBamountfrom Student,BuyBookwhere BuyBook.Sno=Student.Sno图5-2售书登记表(3)缺书登记表：学号、姓名、书号、缺书数量select Student.Sno,Student.Sname,BuyBook.Bno,BBamount-SRamount amount from Student,BuyBook,StackRoom,Bookwhere BuyBook.Sno=Student.Sno andBuyBook.Bno=StackRoom.Bno andBook.Bno=BuyBook.Bno andBuyBook.BBamount>StackRoom.SRamount图5-2缺书登记表(4)教材存量表：书号、单价、库存量select Book.Bno,Bprice,SRamountfrom Book,StackRoom,BuyBookwhere Book.Bno=StackRoom.Bno andBook.Bno=BuyBook.Bno图5-4教材存量表(5)待购教材表：书号、缺书数量select BuyBook.Bno,BBamount-SRamount amount from BuyBook,StackRoomwhere BuyBook.Bno=StackRoom.Bno and BuyBook.BBamount >StackRoom.SRamount图5-5待购教材表。