场景描述

某汽车运输公司数据库中有三个实体，分别是车队、车辆、司机，车队属性有车队号、车队名；车辆属性有牌照号、生产厂家和出厂日期；司机属性有司机编号、姓名、联系电话。每个车队可以聘用若干个司机，但每个司机只能应聘于一个车队，车队聘用司机有个聘期；每个车队可以拥有若干个车辆，但每辆车只能属于一个车队；每个司机可以使多辆汽车，每辆汽车可以被多个司机使用，司机使用车辆有使用日期和公里数两个属性。

ER 图

将 ER 图转换为关系模式

drop table if exists drive;
drop table if exists driver;
drop table if exists vehicle;
drop table if exists fleet;

create table
  fleet (
    fleet_id int primary key auto_increment,
    fleet_name varchar(20)
  );

create table
  vehicle (
    plate_number char(7) primary key,
    producer varchar(20) not null,
    produce_date date not null,
    fleet_id int,
    foreign key (fleet_id) references fleet (fleet_id)
  );

create table
  driver (
    driver_id int primary key auto_increment,
    driver_name varchar(20) not null,
    driver_phone char(11) not null,
    fleet_id int,
    period int,
    foreign key (fleet_id) references fleet (fleet_id)
  );

create table
  drive (
    record_id bigint primary key auto_increment,
    plate_number char(7) not null,
    driver_id int not null,
    drive_date date not null,
    kilometers decimal(7, 3) not null,
    foreign key (plate_number) references vehicle (plate_number),
    foreign key (driver_id) references driver (driver_id)
  );

建立数据库

通过 华为云数据库 GaussDB(for MySQL) 平台进行数据库搭建。首先，进入官网，点击右上方控制台，进入控制台页面，在三明治菜单中选择 数据库 > 云数据库 GaussDB(for MySQL)：

购买实例后，等待初始化完成，然后登录进入数据库。

使用 华为云数据库 GaussDB(for MySQL)，在其中建立物理表及表之间的关系等，在表中增加一定数量的测试数据。完成后，使用“库管理”->“数据字典”->“导出PDF”。

分析应用场景，设计三个业务需求

需求1: 获取详细的车队信息，包括车队编号、名字、拥有的车辆数、拥有的司机数。

create or replace view
  v_fleet (
    fleet_id,
    fleet_name,
    n_vehicle,
    n_driver
  ) as
  select
    fleet_id,
    fleet_name,
    coalesce(n_vehicle, 0),
    coalesce(n_driver, 0)
  from
    fleet natural left join (
      select
        fleet_id,
        count(*) n_vehicle
      from
        vehicle
      group by
        fleet_id
    ) s natural left join (
      select
        fleet_id,
        count(*) n_driver
      from
        driver
      group by
        fleet_id
    ) t;

华为云数据库 GaussDB(for MySQL) 执行结果：

需求2：获取每辆车总公里数。

create or replace view
  v_vehicle_km (
    plate_number,
    kilometers
  ) as
  select
    plate_number,
    coalesce(km, 0)
  from (
    select
      plate_number
    from
      vehicle
    ) s natural left join (
      select
        plate_number,
        sum(kilometers) km
      from
        drive
      group by
        plate_number
    ) t;

华为云数据库 GaussDB(for MySQL) 执行结果：

需求3：当司机不被任何车队聘用时，将任期设为null

drop trigger if exists tb_driver_ins;

drop trigger if exists tb_driver_upd;

delimiter $$

create trigger
  tb_driver_ins
before insert on driver
for each row
begin
  if (new.fleet_id is null) then
    set new.period = null;
  end if;
end$$

create trigger
  tb_driver_upd
before update on driver
for each row
begin
  if (new.fleet_id is null) then
    set new.period = null;
  end if;
end$$

delimiter ;

华为云数据库 GaussDB(for MySQL) 执行结果：