数据库设计是开发管理系统的一个重要步骤。如果数据库设计不合理，会给后续的系统开发带来很大的麻烦。本节为读者介绍驾校管理系统的数据库的设计过程。

数据库设计时要确定创建哪些表、表中有哪些字段、字段的数据类型和长度。本章介绍的驾校学员管理系统选择MySQL数据库。因为本书主要是介绍MySQL数据库的知识， 所以在设计数据库时会尽量用到书中介绍过的MySQL数据库的知识点。这样可以让读者对MySQL数据库有一个全面的认识。

22.3.1  设计表

本系统所有的表都放在drivingschool数据库下。创建drivingschool数据库的SQL代码如下：

 create database drivingschool;

在这个数据库下一共存放6张表，分别是user表、studentInfo表、healthInfo表、courseInfo 表、gradeInfo表和licenseInfo表。其中，user表存储管理员的用户名和密码；studentInfo 表存储学员的学籍信息；healthInfo表存储学员的体检信息；courseInfo表存储学员的课程信息；gradeInfo表存储学员各科考试信息；licenseInfo表存储领取驾驶证的信息。

1. user表

user表中存储用户名和密码，所以将user表设计为只有两个字段。usemame字段表示用户名，password字段表示密码。因为用户名和密码都是字符串，所以这两个字段都使用 varchar类型。而且将这两个字段的长度都设置为20。而且用户名必须唯一。user表的每个字段的信息如表22.1所示。

根据表22.1的内容创建user表。创建user表的SQL语句如下：

CREATE TABLE user(usemame VARCHAR(20) PRIMARY KEY UNIQUE NOT NULL , password VARCHAR(20) NOT NULL);

创建完成后，可以使用DESC语句或者SHOW CREATE TABLE语句查看user表的结构。

2.studentInfo表

studentInfo表中主要存储学员的学籍信息，包括学号、姓名、性别、年龄和身份证号码等信息。用sno字段表示学号，因为学号是studentInfo表的主键，所以sno字段是不能为空值的，而且值必须是唯一的。identify字段表示学员的身份证，而每个学员的身份证必须是唯一的。因为有些身份证以字母x结束，所以identify字段设计为VARCHAR类型。

sex字段表示学员的性别，该字段只有“男”和“女”这两个取值。因此sex字段使用ENUM类型。scondition字段表示学员的学业状态，每个学员只有3种状态，分别为“学习”、“结业”和“退学”。因此，scondition字段也使用ENUM类型。入学时间和毕业时间都是日期，因此选择DATE类型。s_text字段用于存储备注信息，所以选择TEXT类型比较合适。studentInfo表的每个字段的信息如表22.2所示。

创建studentInfo表的SQL代码如下：

CREATE TABLE studentlnfo(
sno INT(8) PRIMARY KEY UNIQUE NOT NULL,
sname VARCHAR(20) NOT NULL,
sex ENUM('男','女',）NOT NULL,
age INT(3),
identify VARCHAR(18) UNIQUE NOT NULL,
telVARCHAR(15),
car_type VARCHAR(4) NOT NULL,
enroll_time DATE NOT NULL,
leave_time DATE,
scond7tion ENUM('学习','结','退学'）NOT NULL,
s_text TEXT
);

studentInfo表创建成功后，读者可以通过DESC语句查看studentInfo表的基本结构，也可以通过SHOW CREATE TABLE语句查看studentInfo表的详细信息。

(3)healthInfo表

因为驾校体检主要检查身高、体重、视力、听力、辨色能力、腿长和血压信息。所以healthInfo表中必须包含这些信息。身高、体重、左眼视力和右眼视力分别用height字段weight字段、left_sight字段和right_sight字段表示。因为这些字段的值有小数，所以这些 字段都定义成FLOAT类型。辨色能力、左耳听力、右耳听力、腿长和血压分别用differentiate字段、left_ear字段、right_ear字段、legs字段和pressure字段表示。这些字段的取值都是 在特定几个取值中取一个，因此定义成ENUM类型。

id字段是记录的编号，而且该字段为自增类型。每插入一条新记录，id字段的值会自动加1。healthInfo表中需要一个字段与studentInfo表建立连接关系。这就可以设计sno 段是外键，其依赖于studentInfo表的sno字段。healthInfo表中设计一个学员姓名的字段，用sname子段表示。特别值得注意的是sname字段与studentInfo表中sname字段的值是一样的。这个字段healthInfo表不能满足三范式的要求。但是，査询healthInfo表时需要使用这个字段。为了提高查询速度，特意在healthInfo也表中增加了sanme字段。heahhInfo的每个字段的信息如表22.3所示。

创建healthInfo表的SQL语句如下：

CREATE TABLE healthlnfo(
id INT(8) PRIMARY KEY UNIQUE NOT NULL AUTO_INCREMENT,
sno INT(8) UNIQUE NOT NULL,
sname VARCHAR(20) NOT NULL,
height FLOAT,
weight FLOAT,
differentiate ENUM('正常' '色弱','色盲'),
left_sight FLOAT,
righI_sight FLOAT,
left_ear ENUM('正常','偏弱'),
rigW_ear EENUM('正常','偏弱'),
tegs ENUM('相等'，‘不相等')，
pressure ENUM('正常','偏高','偏低'),
history VARCHAR(50),
h_text TEXT,
CONSTRAINT health_fk FOREIGN KEY (sno) REFERENCES studentlnfo(sno)

创建healthInfo表时将sno字段设置为外键，而且外键的别名为health_fk。而且，id字段加上了AUTO_INCREMENT属性，这样就可以将id字段设置为自增字段。healthInfo 表创建完成后，读者可以使用DESC语句或者SHOW CREATE TABLE语句査看healthInfo 表的结构。

(4)courselnfo表

courseInfo表用于存储考试科目的信息，每个科目都必须有科目号、科目名称。有些科目必须在某个科目考试完成之后才能学习，因此，每个科目都要有个先行考试科目。这个表只需要3个字段就可以了，cno字段表示科目号,cname字段表示科目名称,before_cour 字段表示先行考试科目的科目号。每条记录中，只有before_cour字段中存储的科目考试通过后，学员才可以报考cno表示的科目。由于第一个科目没有先行考试科目，因此，第-一个科目的先行考试科目号的默认值为0。courseInfo表的每个字段的信息如表22.4所示。

创建courseInfo表的SQL代码如下：

CREATE TABLE courselnfo(
cno INT(4) PRIMARY KEY NOT NULL UNIQUE,
cname VARCHAR(20) NOT NULL UNIQUE,
before_cour INT(4) NOT NULL DEFAULT 0
);

从上面的SQL代码可以看到，使用DEFAULT关键字为before_cour字段设置默认值。courseInfo表创建完成后，读者可以使用DESC语句或者SHOW CREATE TABLE语句查看courseInfo表的结构。

(5)gradeInfo表

gradeInfo表用于存储学员的成绩信息。

这个表必须与studentInfo表和course表建立联系。因此设计sno字段和cno字段。sno字段和cno字段作为外键。sno字段依赖于studentInfo 表的sno字段，cno字段依赖于courseInfo表的cno字段。这里一个学员可能需要参加多个科目，而且同一个科目可能需要考多次。因此，sno字段和cno字段都不是唯一字段，表中可以出现重复的值。而且，需要记录每科考试的时间和考试的次数。这里用last_time字段表示考试时间,times字段表示某一个科目的考试次数。默认值情况下是第一次参加考试， 因此times字段的默认值为1。分数用gmde字段表示，默认分数为0分。gradeInfo表的每个字段的信息如表22.5所示。

创建gradeInfo表的SQL代码如下:

CREATE TABLE gradelnfo(
id INT(8) PRIMARY KEY UNIQUE NOT NULL AUTOJNCREMENT,
sno INT(8) NOT NULL,
cno INT(4) NOT NULL,
last_time DATE,
times INT(4) DEFAULT 1,
grade FLOAT DEFAULT 0,
CONSTRAINT grade_sno_fk FOREIGN KEY (sno) REFERENCES studentlnfo(sno),
CONSTRAINT grade_cno_fk FOREIGN KEY (cno) REFERENCES courselnfo(cno)
);

(6)licenseInfo表

licenseInfo表用于存储学员领取驾驶证的信息。这个表中需要记录学员的学号、姓名、驾驶证号码、领取时间、领取人等信息。而且licenseInfo表需要与studentInfo表建立联系，这可以通过学号来完成。在该表中设计sno字段为外键，其依赖于studentInfo表的sno字段。姓名用sanem字段表示，sname字段是冗余字段，设置这个字段是为了提高查询速度。

驾驶证号码用Ino字段表示，每个人的驾驶证号都是唯一的。领取时间用receive_time字段表示，该字段设置为DATE类型。领取人的姓名用receive_name字段表示。表中需要一个字段来存储备注信息，这里设计L_text字段来存储备注信1，而且其应该为TEXT类 型。licenseInfo表的每个字段的信息如表22.6所示。

创建licenseInfo表的SQL代码如下:

CREATE TABLE licenselnfo(
id INT(8) PRIMARY KEY UNIQUE NOT NULL AUTOJNCREMENT,
sno INT(8) UNIQUE NOT NULL,
sname VARCHAR(20) NOT NULL,
lno VARCHAR(18) UNIQUE NOT NULL,
receive_time DATE,
receive_name VARCHAR(20),
l_text TEXT,
CONSTRAINT license_fk FOREIGN KEY (sno)
REFERENCES studentlnfo(sno)
);

sno字段被设置成外键，该外键的别名为license_fk。licenseInfo表创建完成后，读者可以使用DESC语句或者SHOW CREATE TABLE语句查licenseInfo表的结构。

22.3.2设计索引

索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。驾校学员管理系统需要査询学员的信息，这就需要在某些特定字段上建立索引，以便提高查询速度。

1. 在studentInfo表上建立索引

驾校学员管理系统中需要按照snaem字段、car_type字段、scondition字段查询学籍信息。因此，需要在这3个字段上创建索引。在第7章中介绍了创建索引的3种方法。本小节将使用CREATE INDEX语句和ALTER TABLE语句创建索引。

下面使用CREATE INDEX语句在sname字段上创建名为index_stu_name的索引。SQL语句如下：

CREATE INDEX index_stu_name ON studentlnfo(sname);

然后，再使用CREATE INDEX语句在car_type字段上创建名为index_car的索引。SQL语句如下：

CREATE INDEX index_car ON studentlnfo(car_type);

最后，使用ALTER TABLE语句在scondition字段上创建名为index_con的索引。SQL 语句如下：<.p>

ALTER TABLE studentInfo ADD INDEX index_con(scondition);

代码执行完后，读者可以使用SHOW CREATE TABLE语句查看studentInfo表的结构》,查看结果中如果显示了index_stu_name、index_car和index_con这3个索引，这表示索引创建成功。

2. 在healtnInfo表上建立索引

管理系统中需要通过sname字段查询healthInfo表中的记录，因此需要在这些字段上创建索引。创建索引的语句如下:

CREATE INDEX index_h_name ON healthlnfo(sname);

代码执行完后，读者可以使用SHOW CREATE TABLE语句査看healthInfo表的结构。

3. 在licenseInfo表上建立索引

管理系统需要通过sname字段和receive_name字段査询licenseInfo表中的信息，因此 可以在这两个字段上创建索引。创建索引的语句如下：

ALTER TABLE licenseInfo ADD INDEX index_license_name(sname);
ALTER TABLE licenseInfo ADD INDEX index_receive_name(receive_name);

上面的代码都是使用ALTER TABLE语句来创建索引。第一个语句在sname字段上创 建名为index_license name的索引；第二个语句在receive_name字段上创建名为 index_license_name的索引。代码执行完后，读者可以使用SHOW CREATE TABLE语句查 看licenseInfo表的结构。

22.3.3设计视图

视图由数据库中的一个表或多个表导出的虚拟表。其作用是方便用户对数据的操作。在这个管理系统中，也设计了一个视图改善查询操作。

在驾校学员管理系统中，如果直接査询gradeInfo表，显示信息时会显示学员的学号和考试的科目号。这种显示并不直观，为了以后查询方便，可以创建一个视图grade_view。 这个视图显示编号、学号、姓名、课程名、last_time字段、times字段和grade字段。创建视图grade_view的SQL代码如下：

CREATE VIEW grade_viewAS SELECT g.id,g.sno,s.sname,c.cname,last_time,times,grade FROM studentInfo s,courselnfo c,gradelnfo g WHERE g.sno=s.sno AND g.cno=c.cno;

上述SQL语句中给每个表都取了一个别名，studentInfo表的别名为s; courseInfo表的别名为c;gradeInfo表的别名为s。这个视图从这3个表中取出了相应的字段。视图创建完成后，可以使用SHOW CREATE VIEW语句创建视图。如果想了解更多关于视图的内容，请参照第8章。

22.3.4设计触发器

触发器是由mSERT、UPDATE和DELETE等事件来触发某种特定操作。满足触发器的触发条件时，数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。为了使驾校学员管理系统的数据更新更加快速、合理，可以在数据库中计几个触发器。

(1)设计INSERT触发器

如果向licenseInfo表中插入记录，说明这个学员已经结业。那么studentInfo表中的 studentInfo字段的值应该更新为“结业”。这可以通过触发器来完成。在licenseInfo表上创建名为license_stu的触发器,其SQL语句如下:

DELIMITER &&
CREATE TRIGGER license_stu AFTER INSERT ON licenseInfo FOR EACH ROW BEGIN
UPDATE studentInfo SET leave_time=NEW.receive_time,scondition='结业'
WHERE sno=NEW.sno;
END
&&
DELIMITER ;

如果向licenseInfo表中执行INSERT操作，那么系统会自动将学员的离校时间(leave_time)设置为领证时间。NEW.receive_time表示新插入的记录的receive_time字段的 值。同时，该触发器会将scondition字段的值更新为“结业” .

2. 设计UPDATE触发器

在设计表时,healthInfo表和licenseInfo表中的sanem字段的值与studentInfo表中sname字段的值是一样的。如果studentInfo表中sname字段的值更新了，那么healthInfo表和 licenseInfo表中的sname字段的值也必须同时更新。这可以通过一个UPDATE触发器来实现。创建UPDATE触发器update_sname的SQL代码如下：

DELIMITER &&
REATE TRIGGER update_sname AFTER UPDATE ON studentInfo FOR EACH ROW BEGIN
UPDATE healthInfo SET sname=NEW.sname WHERE sno=NEW.sno;
UPDATE licenseInfo SET sname=NEW.sname WHERE sno=NEW.sno;
END
&&
DELIMITER;

其中，NEW.sno表示studentInfo表中更新的记录的sno值。如果studentInfo表中的一个学员的姓名改变了，healthInfo表和licenseInfo值相同的记录也会同时更新sname字段的值。

3. 设计DELETE触发器

如果从studentInfo表中删除一个学员的学籍信息，那么这个学员在healthInfo表、 gradeInfo表和licenseInfo表中的信息也必须同时删除。这也可以通过触发器来实现。在studentInfo表上创建delete_stu触发器，只要执行DELETE操作，那么就删除healthInfo表、 gradeInfo表和licenseInfo表中相应的记录。创建delete_stu触发器的SQL语句如下:

DELIMITER &&
CREATE TRIGGER delete_stu AFTER DELETE ON studentInfo FOR EACH ROW BEGIN
DELETE FROM gradeInfo WHERE sno=OLD.sno;
DELETE FROM healthInfo WHERE sno=OLD.sno;
DELETE FROM licenseInfo WHERE sno=OLD.sno;
END
&&
DELIMITER ;

其中，OLD.sno表示新删除的记录的sno值。如果一次性删除studentInfo表中的所有记录时，这个触发器只能获取第一条记录的sno值。但是在管理系统中都是一次删除一条信息，因此这个触发器可以达到预期效果。