汽车站数据库设计

汽车站数据库设计一、系统简要介绍主要实现的功能是汽车站驾驶人员和路线及汽车等基本信息进行管理，包括浏览驾驶员信息、路线信息，查询和统计一些驾驶员、汽车等数据，修改驾驶员行走的路线信息等。二、本系统有四张表组成，表结构如下：驾驶员表：（驾驶员编号，汽车编号，姓名，生日，性别，工作年限）汽车表：（汽车编号，型号，载客量，票价）路线表：（路线编号，目的地，行程时间，出发地）汽车路线表：（汽车编号，路线编号）用户表：（用户名，密码）三、表间关系说明一个驾驶员属于一辆车，一辆车可以有多个驾驶员，一辆车可以行走多条路线，一条路线也可有多辆车行走。四、E—R图如下：五.各表的简单介绍和创建代码：1.驾驶员表driver字段含义字段名称字段类型驾驶员编号driverIDChar(20)primarykey汽车编号car_IDChar(20)notnull驾驶员表属于汽车表路线表汽车路线表驾驶员表属于汽车表路线表汽车路线表2姓名namechar(20)unique生日birthdayChar(20)性别sexChar(20)工作年限standingChar(20)创建驾驶员表：CREATETABLEdriver(driverIDchar(20)primarykey,car_IDchar(20)NOTNULL,namechar(20)unique,brithdaychar(20),sexchar(2),standingchar(20),foreignkey(car_ID)referencescar(car_ID));2.汽车表car：字段含义字段名称字段类型汽车编号car_IDChar(20)primarykey类型typechar(20)载客量capacityChar(20)创建汽车表表：CREATETABLEcar(car_IDchar(20)primarykey,typechar(20),capacitychar(20),);3.路线表line字段含义字段名称字段类型路线编号Line_IDChar(20)primarykey目的地destinationchar(20)notnull所需时间take_timechar(20)出发地start_addresschar(20)notnull票价ticket_priceChar(20)notnull创建路线表：CREATETABLEline(line_IDchar(20)primarykey,destinationchar(20)notnull,take_timechar(20),3start_addresschar(20)notnull，ticket_pricechar(20)notnull);4.汽车路线表car_line字段含义字段名称字段类型汽车编号carIDChar(20)路线编号lineIDchar(20)创建汽车路线表：CREATETABLEcar_line(carIDchar(20),lineIDchar(20),primarykey(carID,lineID));5.用户表user_ID.字段含义字段名称字段类型汽车编号usernameChar(20)路线编号passwordchar(20)创建用户表表：CREATETABLEuser_ID(usernamevarchar(20),passwordvarchar(20),primarykey(username,password));六.插入数据insert1.汽车表数据insertintocarvalues('091201','飞鹤','45');insertintocarvalues('091202','快鹿','40');insertintocarvalues('091203','鸵鸟','42');insertintocarvalues('091204','风火轮','45');insertintocarvalues('091205','筋斗云','38');insertintocarvalues('091206','白龙马','42');insertintocarvalues('091207','毛驴','36');insertintocarvalues('091208','青牛','50');2.驾驶员数据insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491001','091201','吴士','1981','1','6');4insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491002','091204','孙悟','1974','1','15');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491003','091203','李娇','1979','0','9');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491004','091202','夏雨荷','1983','0','6');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491005','091205','刘备','1977','1','16');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491006','091208','诸葛亮','1985','1','4');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491007','091206','陈娜','1975','0','17');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491008','091207','张海风','1978','1','10');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491009','091201','朱翠翠','1980','0','8');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491010','091204','赵薇','1988','0','1');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491011','091208','李鸣','1978','1','12');insertintodriver(driverID,car_ID,name,brithday,sex,standing)values('19491012','091203','周杰磊','1981','1','4');3.路线表数据insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201001','上海','3小时','南京','123');insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201002','南通','4小时','南京','233');insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201003','苏州','3个半小时','南京','256');insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201004','盐城','3小时20分','南京','330');insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201005','泰州','2个半小时','南京','450');insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201006','扬州','2小时','南京','326');insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201007','上海','3小时','南京','521');insertintoline(line_ID,destination,take_time,start_address,ticket_price)values('201008','苏州','3小时','南京','430');4.汽车路线表数据insertintocar_line(carID,lineID)values('91202','201001');5insertintocar_line(carID,lineID)values('91207','201002');insertintocar_line(carID,lineID)values('91208','201007');insertintocar_line(carID,lineID)values('91206','201003');insertintocar_line(carID,lineID)values('91205','201008');insertintocar_line(carID,lineID)values('91204','201005');insertintocar_line(carID,lineID)values('91201','201004');insertintocar_line(carID,lineID)values('91203','201006');insertintocar_line(carID,lineID)values('91202','201003');insertintocar_line(carID,lineID)values('91206','201006');5.用户表数据insertintouser_IDvalues('邱冬','123456');七.索引：建立索引遵循的规律：1.建立在where子句经常引用的列上，2.经常需要排序的列上，3.连接属性列上等createindexcar_indexoncar(car_ID);--汽车表在汽车编号字段上建立索引createindexdriver_indexondriver(driverID);--驾驶员表在驾驶员编号字段上建立索引createindexline_indexonline(lineID);--路线表在路线编号字段上建立索引createindextake_time_indexonline(take_time);--路线表在路线所需时间上建立索引，以备查询各路线所需时间八．视图1.创建过程take_time_list，查询各路线所花时间：createviewtake_time_listasselectline_ID,take_tim