2022年数据库大作业.doc

《2022年数据库大作业.doc》由会员分享，可在线阅读，更多相关《2022年数据库大作业.doc（22页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验内容及要求一、实验目的：本次作业，是为解决实际问题而进行数据库设计。完成设计任务，将对设计实际有用的数据库应用程序，有更深入的理解。二、设计环境： 操作系统： Windows 10。数据库管理系统：Microsoft 公司的SQL Server 2008 R2。三、实验内容（一）、题目：设某汽车运输公司数据库中有三个实体集。一是“车队”实体集，属性有车队号、车队名等；二是“车辆”实体集，属性有牌照号、厂家、出厂日期等；三是“司机”实体集，属性有司机编号、姓名、电话等。设车队与司机之间存在“聘用”联系，每个车队可聘用若干司机，但每个司机只能应聘于一个车队，车队聘用司机有个聘期；车队与车辆之间

2、存在“拥有”联系，每个车队可拥有若干车辆，但每辆车只能属于一个车队；司机与车辆之间存在着“使用”联系，司机使用车辆有使用日期和公里数两个属性，每个司机可使用多辆汽车，每辆汽车可被多个司机使用。（二）、要求：1、概念结构设计：根据题目要求，设计E-R模型，详细描述实体的属性和实体之间的联系，消除不必要的冗余。并在图上注明属性、联系类型、实体标识符；出厂日期厂家牌照号车队号车队名 车辆拥有车队 1 n 1 n公里数聘期聘用使用使用日期 m m司机 姓名电话司机编号2、逻辑结构设计：实现E-R图向关系模型的转换，特别注意实体的1:n联系，优化数据模型，并标明主键和外键。 车队（车队号，车队名） 车辆

3、（牌照号，厂家，出厂日期，车队号） 司机（司机编号，姓名，电话，） 聘用（司机编号，车队号，聘期） 使用（司机编号，牌照号，公里数，使用日期） (注：直线下标标注的是主键，波浪线标注的是外键)3、关系规范化：3.1写出所有关系模式的基本函数依赖和关键码；3.2说明各关系模式最高已经达到第几范式？为什么？3.3如果各关系模式不属于3NF，请说明理由，并将各关系模式分解成第三范式。车队名车队号(1) 车队关系： 关键码是：车队号 该关系最高已达到了第三范式（3NF）因为车队号和车队名只包含单一的值，满足第一范式，车队名是非主属性完全依赖于主属性车队号满足第二范式，不存在传递依赖满足第三范式。 厂家

4、(2) 车辆关系： 车队号牌照号出厂日期 关键码是：牌照号 车辆关系中只存在完全依赖，不存在部分依赖和传递依赖，满足第三范式。 姓名 (3)司机关系：司机编号电话 关键码是：司机编号 司机关系中只存在完全依赖，不存在部分依赖和传递依赖，满足第三范式。聘期 (4)聘用关系：司机编号车队号 关键码是：司机编号 聘用关系中只存在完全依赖，不存在部分依赖和传递依赖，满足第三范式。 (5)使用关系： 司机编号使用日期公里数牌照号 关键码是：司机编号和牌照号 使用关系中只存在完全依赖，不存在部分依赖和传递依赖，满足第三范式。4、写出表关系的详细说明。4.1车队关系的详细说明字段名数据类型含义说明空值情况M

5、otorcadeIDChar(4)车队号主关键字MotorcadeNameVarchar(30)车队名不可为空4.2车辆关系的详细说明字段名数据类型含义说明空值情况MotorcadeIDChar(4)车队号主关键字VehicleIDChar(6)牌照号主关键字ProducerChar(8)厂家可为空DateDatetime出厂日期可为空4.3司机关系的详细说明字段名数据类型含义说明空值情况DriverIDChar(6)司机编号主关键字NameVarchar(30)姓名不可为空PhoneChar(11)电话可为空4.4聘用关系详细说明字段名数据类型含义说明空值情况DriverIDChar(6)司

6、机编号主关键字MotorcadeIDChar(4)车队号主关键字EmploymentTermtinyint聘期可为空4.5使用关系详细说明字段名数据类型含义说明空值情况DriverIDChar(6)司机编号主关键字VehicleIDChar(6)牌照号主关键字Kilometresint公里数可为空DataTimeDatetime使用日期可为空5、用T-Sql语言创建数据库；create database TruckLine use TruckLinecreate table Motorcade( MotorcadeID char(4) primary key, MotorcadeName va

7、rchar(30) not null) create table Cars ( VehicleID char(6) primary key, Producer char(8), Date datetime, MotorcadeID char(4) references Motorcade(MotorcadeID) ) create table Driver ( DriverID Char(6) primary key, Name Varchar(30) not null, Phone Char(11) ) create table Employment ( DriverID Char(6) p

8、rimary key references Driver(DriverID), MotorcadeID char(4) references Motorcade(MotorcadeID), EmploymentTerm tinyint)create table usage( DriverID Char(6) references Driver(DriverID), VehicleID char(6) references Cars(VehicleID), Kilometres int, DataTime Datetime, primary key(DriverID,VehicleID) ) i

9、nsert into Motorcade values (1101,北京时代物流运输队) insert into Motorcade values (1102,北京超长运输队) insert into Motorcade values (1103,北京散货运输队) insert into Motorcade values (1104,天津机械运输队) insert into Motorcade values (1105,燕郊顺丰运输队) insert into Cars values (A12345,中国重汽,2005-01-02,1101) insert into Cars values (

10、A12346,中国重汽,2005-01-02,1102) insert into Cars values (A12347,中国重汽,2005-01-02,1102) insert into Cars values (A12348,中国重汽,2005-01-03,1102) insert into Cars values (A12349,中国重汽,2005-01-03,1101) insert into Cars values (A12344,中国重汽,2005-01-04,1101) insert into Cars values (B11201,东风汽车,2004-04-25,1101) i

11、nsert into Cars values (B11202,东风汽车,2004-04-25,1102) insert into Cars values (B11203,东风汽车,2004-04-26,1101) insert into Cars values (B11204,东风汽车,2004-04-26,1103) insert into Cars values (B11207,东风汽车,2004-04-26,1103) insert into Cars values (B11205,东风汽车,2004-04-26,1103) insert into Cars values (C10054

12、,一汽解放,2008-09-14,1103) insert into Cars values (B11206,东风汽车,2004-04-27,1104) insert into Cars values (C10051,一汽解放,2008-09-12,1104) insert into Cars values (C10052,一汽解放,2008-09-13,1104) insert into Cars values (C10053,一汽解放,2008-09-13,1104) insert into Cars values (C10055,一汽解放,2008-09-15,1104) insert

13、into Cars values (C10056,一汽解放,2008-09-15,1105) insert into Cars values (D66121,陕汽卡车,2011-12-06,1105) insert into Cars values (D66122,陕汽卡车,2011-12-06,1105) insert into Cars values (D66123,陕汽卡车,2011-12-06,1105) insert into Cars values (D66124,陕汽卡车,2011-12-07,1105) insert into Driver values (110101,范冰冰

14、,) insert into Driver values (110102,李晨,) insert into Driver values (110103,张馨予,) insert into Driver values (110201,angelababy,) insert into Driver values (110202,黄晓明,) insert into Driver values (110301,杨幂,) insert into Driver values (110302,刘恺威,) insert into Driver values (110401,张柏芝,) insert into

15、Driver values (110402,王菲,) insert into Driver values (110403,谢霆锋,) insert into Driver values (110501,刘诗诗,) insert into Driver values (110502,吴奇隆,) insert into Driver values (110503,刘强东,) insert into Driver values (110504,章泽天,) insert into Employment values (110101,1101,50) insert into Employment val

16、ues (110102,1101,30) insert into Employment values (110103,1101,30) insert into Employment values (110201,1102,13) insert into Employment values (110202,1102,26) insert into Employment values (110301,1103,31) insert into Employment values (110302,1103,32) insert into Employment values (110401,1104,5

17、) insert into Employment values (110402,1104,8) insert into Employment values (110403,1104,12) insert into Employment values (110501,1105,5) insert into Employment values (110502,1105,7) insert into Employment values (110503,1105,98) insert into Employment values (110504,1105,23) insert into usage v

18、alues (110101,A12345,700000,2011-11-12) insert into usage values (110101,A12344,600000,2011-11-13) insert into usage values (110101,B11201,400000,2011-11-20) insert into usage values (110102,B11203,900000,2011-11-12) insert into usage values (110102,A12344,400000,2011-11-12) insert into usage values

19、(110103,A12349,720000,2011-12-10) insert into usage values (110201,B11202,120000,2012-01-01) insert into usage values (110201,A12348,223000,2012-01-04) insert into usage values (110202,A12346,250000,2012-02-01) insert into usage values (110202,A12347,274000,2012-02-07) insert into usage values (1103

20、01,B11204,310000,2012-07-05) insert into usage values (110301,B11207,350000,2012-07-12) insert into usage values (110302,C10054,360000,2012-07-05) insert into usage values (110302,B11205,300000,2012-07-23) insert into usage values (110401,B11206,70000,2013-02-06) insert into usage values (110401,C10

21、051,60000,2013-02-06) insert into usage values (110401,C10055,120000,2013-02-06) insert into usage values (110402,C10051,95000,2013-03-04) insert into usage values (110402,C10052,63000,2013-04-03) insert into usage values (110403,C10053,130000,2013-04-09) insert into usage values (110403,C10055,1100

22、00,2013-05-18)insert into usage values (110501,D66121,30000,2014-09-13)insert into usage values (110501,D66123,45000,2014-09-13)insert into usage values (110501,C10056,10000,2014-09-13) insert into usage values (110502,C10056,175000,2014-09-13)insert into usage values (110503,D66122,1125000,2014-07-

23、16)insert into usage values (110503,D66123,934000,2014-12-11)insert into usage values (110504,D66124,470000,2014-8-27) 8.1:三个单表查询(1)按出厂日期降序输出所有车辆情况select * from Cars order by date desc(2)查询出牌照号第五位数为4的司机编号,牌照号,公里数,并用司机编号,牌照号,公里数表示. select driverID 司机编号,VehicleID 牌照号, Kilometres 公里数 from usage where V

24、ehicleID like _4_ (3)查询范冰冰的编号.并用姓名和司机编号表示.select Name 姓名,DriverID 司机编号 from Driver where Name=范冰冰 8.2:三个应用集合函数的查询. (1)计算该公司的司机数量. select count(DriverID) 司机数量 from Driver (2)查询北京时代物流运输队司机的平均聘期(月). select avg(EmploymentTerm) 平均聘期（月） from Motorcade,Employment where Motorcade.MotorcadeID=Employment.Moto

25、rcadeID and MotorcadeName=北京时代物流运输队 (3)查询车牌号为D66122车辆所在的车队的司机数量. select count(DriverID) 司机数量 from Employment where MotorcadeID=(select MotorcadeID from Cars where VehicleID=D66122 ) 8.3:三个多表连接的查询. (1)查询张馨予所在车队名. select name 姓名, MotorcadeName 车队名 from Motorcade,Employment,Driver where Motorcade.Motor

26、cadeID=Employment.MotorcadeID and Employment.DriverID=Driver.DriverID and Name=张馨予 (2) 查询刘强东的聘期. select Name 姓名,EmploymentTerm 聘期(月) from Driver ,Employment where Driver.DriverID=Employment.DriverID and Name=刘强东 (3)查询章泽天使用车辆的使用日期 select name 姓名,DataTime 使用日期 from usage, Driver where Driver.DriverID=

27、usage.DriverID and Name=章泽天 8.4:三个子查询.(1)查询杨幂和谁在同一车队. select Name 姓名 from Driver where DriverID in (select DriverID from Employment where MotorcadeID=(select MotorcadeID from Employment where DriverID in (select driverID from Driver where Name=杨幂) (2) 查询牌照号为A12344司机的姓名和电话. select Name 姓名,phone 电话 fr

28、om Driver where DriverID in (select DriverID from usage where VehicleID=A12344 ) (3)查询燕郊顺丰运输队车辆的平均公里数. select avg(kilometres) 平均公里数 from Cars,Employment ,Motorcade ,usage where usage.DriverID=Employment.DriverID and Employment.DriverID in (select DriverID from Motorcade,Employment where Motorcade.Mo

29、torcadeID=Employment.MotorcadeID and MotorcadeName=燕郊顺丰运输队) and usage.VehicleID=Cars.VehicleID and Cars.VehicleID in (select VehicleID from Cars, Motorcade where Cars.MotorcadeID=Motorcade.MotorcadeID and MotorcadeName=燕郊顺丰运输队) 8.5: 三个分组查询. (1)查询该公司的车辆数,并按厂家分组. select Producer 厂家, count(VehicleID) 车

30、辆数 from Cars group by Producer (2) 查询每一个车队司机的平均聘期,并按车队名分组. select MotorcadeName 车队名,AVG(EmploymentTerm) 平均聘期 from Motorcade ,Employment where Motorcade.MotorcadeID=Employment.MotorcadeID group by MotorcadeName (3)查询该公司的司机数,并按车队号分组. select MotorcadeID 车队编号, COUNT(DriverID) 司机数 from Employment group b

31、y MotorcadeID 8.6:五个数据更新. (1)添加所有字段的数据 (向数据库TruckLine中的数据表motorcade中添加数据) use TruckLine insert into Motorcade values (1107,北京现代) (2)添加部分字段的数据 (向数据库TruckLine中的数据表Cars中添加部分数据) insert into Cars (VehicleID,Date ) values (D12567,) (3)更改数据 (更改黄晓明的电话号码为) update Driver set Phone= where Name=黄晓明 (4)输入多个元组 (建

32、立数据库表Truck_bak,字段定义同数据表motorcade. 将数据库表motorcade中motorcadeID和motorcadeName字段的数据添加到数据表trucked中) create table Trucked ( MotorcadeID char(4) primary key, MotorcadeName varchar(30) not null, TruckTel varchar(7) insert Trucked (MotorcadeID ,MotorcadeName ) select MotorcadeID ,MotorcadeName from Motorcade

33、 (5)删除数据 (删除数据表Truck_bak中的记录) Use TruckLine delete from Truck_bak 课程学习及作业心得结构化查询语言SQL（Structured query language）是一种非过程化、面向集合的数据库语言。由其完整英文名Structured query language翻译成中文就是结构化查询语言，使得我们容易理解和记住SQL这三个字母的简称和含义。SQL最大的特点就是非过程化，只要输入正确的命令就可以查找所需的数据，不像C语言要查询数据必须设计完整的数学算法，往往会因为数学算法太过复杂难以查找所需数据，然而SQL就可以很简单的实现查找，

34、使得SQL简易的查询大型数据库的数据得以实现。作为自动化专业的学生掌握如此方便又实用的工具是必须的，可以为后来的深入学习打下基础，又能作为一种本领加强自己的实力，以更好的适应社会的需求。刚开始学习这门课程学习的时候感觉SQL很新颖，相当的陌生，以至于自己学习的心里压力打败了自己，以为SQL会很难。然后自己学习吸收能力又慢，刚开始学习数据库就落下了，跟不上老师的节奏。由于课程学习是环环相连，层层递进的，落下部分课程后无法理解老师新教授的知识，拉下的也就越来越多。半个学期过去了，看着自己落下如此多的课程，自己越来越焦虑，惶恐担心自己不能通过考试、挂科、影响专业课的学习等等，对数据库的忧虑导致了自己

35、几晚失眠。然后自己终于下定决心奋起直追，竟然通过一周的努力就赶上了老师的课程，也不再忧虑了。正如sql所定义的那样，非过程化查询语言表明了它的简易与使用，自己认真学起来也很轻松。最终第11周我就跟上了老师的节奏，再也没有落下。这告诉了我们没有过不去的坎儿，不是课程很难，难的是自己心里的压力，主要还是得靠认真对待，认真学习的态度。自以为数据库很简单，但通过做作业后发现自己理解的SQL仅仅停留在能看懂的层面上，不知道怎样用。通过做作业是自己加深了对SQL的理解，对每一个命令使用原因的理解，也使得了自己能够使用课程要求的查询。然而写作业时有时自己想出来的查找很难，设计过程慢，耗时久，设计出来不是过程复杂就是没法设计。所以要想完全掌握SQL还得更深入的学习，这就是我做作业的心得与收获。通过课程的学习和做作业，我相信自己达到了这门课程学习的要求，更深入的掌握还的靠自己的努力，教材上的内容是不够的，应该自己查阅参考教材。