汽车站数据库设计

汽车站数据库设计

一、系统简要介绍

主要实现的功能是汽车站驾驶人员和路线及汽车等基本信息进行管理，包括浏览驾驶员信息、路线信息，查询和统计一些驾驶员、汽车等数据，修改驾驶员行走的路线信息等。

二、本系统有四表组成，表结构如下：

驾驶员表：（驾驶员编号，汽车编号，，生日，性别，工作年限）

汽车表：（汽车编号，型号，载客量，票价）

路线表：（路线编号，目的地，行程时间，出发地）

汽车路线表：（汽车编号，路线编号）

用户表：（用户名，密码）

三、表间关系说明

一个驾驶员属于一辆车，一辆车可以有多个驾驶员，一辆车可以行走多条路线，一条路线也可有多辆车行走。

四、E—R图如下：

五.各表的简单介绍和创建代码：

1.驾驶员表driver

创建驾驶员表：

CREATE TABLE driver

(

driverID char(20) primary key, car_ID char(20) NOT NULL ,

name char(20)unique,

brithday char(20),

sex char(2),

standing char(20),

foreign key (car_ID) references car(car_ID ) );

2.汽车表car：

创建汽车表表：

CREATE TABLE car

(

car_ID char(20)primary key,

type char(20) ,

capacity char(20) ,

);

3.路线表line

创建路线表：

CREATE TABLE line

(

line_ID char(20)primary key, destination char(20) not null,

take_time char(20) ,

start_address char(20) not null，

ticket_price char(20) not null

);

4.汽车路线表car_line

创建汽车路线表：

CREATE TABLE car_line

(

carID char(20),

lineID char(20),

primary key(carID,lineID)

);

5.用户表user_ID.

创建用户表表：

CREATE TABLE user_ID

(

username varchar(20),

password varchar(20),

primary key(username,password)

);

六.插入数据insert

1.汽车表数据

insert into car values('091201','飞鹤','45');

insert into car values('091202','快鹿','40');

insert into car values('091203','鸵鸟','42');

insert into car values('091204','风火轮','45');

insert into car values('091205','筋斗云','38');

insert into car values('091206','白龙马','42');

insert into car values('091207','毛驴','36');

insert into car values('091208','青牛','50');

2.驾驶员数据

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491001','091201','吴士','1981','1','6');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491002','091204','悟','1974','1','15');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491003','091203','娇','1979','0','9');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491004','091202','夏雨荷','1983','0','6');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491005','091205','备','1977','1','16');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491006','091208','诸亮','1985','1','4');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491007','091206','娜','1975','0','17');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491008','091207','海风','1978','1','10');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491009','091201','朱翠翠','1980','0','8');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491010','091204','薇','1988','0','1');

insert into driver (driverID,car_ID,name,brithday,sex,standing) values ('19491011','091208','鸣','1978','1','12');

insert into driver (driverID,car_ID,name,brithday,sex,standing)

values ('19491012','091203','周杰磊','1981','1','4');

3.路线表数据

insert into line (line_ID,destination,take_time,start_address,ticket_price) values ('201001','','3小时','','123');

insert into line (line_ID,destination,take_time,start_address,ticket_price) values ('201002','','4小时','','233');

insert into line (line_ID,destination,take_time,start_address,ticket_price) values ('201003','','3个半小时','','256');

insert into line (line_ID,destination,take_time,start_address,ticket_price) values ('201004','','3小时20分','','330');

insert into line (line_ID,destination,take_time,start_address,ticket_price) values ('201005','','2个半小时','','450');

insert into line (line_ID,destination,take_time,start_address,ticket_price) values ('201006','','2小时','','326');

insert into line (line_ID,destination,take_time,start_address,ticket_price) values