问题描述

下表是一张交通违规处罚通知书样稿，根据该样稿提供的信息。

(1)找出实体及其属性.

(2)设计并画出E-R模型。

(3)将E-R模型转换成关系模式。

(4)根据上述关系模式，建立相关的数据库表。

(5)附加题:根据上述数据库表，利用标准SQL写出以下查询语句:

I)查询“车牌号”为“浙A000DM”的所有违章记录。

II)查询“车牌号”为“浙A000DM”的违章次数。

III)查询“驾驶执照号”为“330104XXXXD”的“罚款”总额。

解决方案

(1)找出实体及其属性.

驾照信息（驾照执照号码，姓名，登记地址，邮政编码，手机号码）

车辆信息（机动车牌照号码，机动车型号，机动车制造厂，机动车生产日期）

警察（警察编号，姓名）

交通违规处罚（违规处罚编号，违章日期，查抄时间，查抄地点，违章记载，处罚方式，处罚金额，驾驶执照号码，机动车牌照号码，警察编号，警察签字，被处罚人签字） 

(2)设计并画出E-R模型。

(3)将E-R模型转换成关系模式。

驾照信息（驾照执照号码，姓名，登记地址，邮政编码，手机号码） PK=驾照执照号码

车辆信息（机动车牌照号码，机动车型号，机动车制造厂，机动车生产日期）PK=机动车牌照号码

警察（警察编号，姓名）PK=警察编号 

交通违规处罚（违规处罚编号，违章日期，查抄时间，查抄地点，违章记载，处罚方式，处罚金额，驾驶执照号码，机动车牌照号码，警察编号，警察签字，被处罚人签字） PK=违规处罚编号 FK1=驾驶执照号码，FK2=机动车牌照号码，FK3=警察编号 

(4)根据上述关系模式，建立相关的数据库表。

CREATE TABLE `交通违规处罚`  (
  `违规处罚编号` varchar(255) NOT NULL,
  `驾驶执照号码` varchar(255) NULL,
  `机动车牌照号码` varchar(255) NULL,
  `违章日期` varchar(255) NULL,
  `查抄时间` varchar(255) NULL,
  `查抄地点` varchar(255) NULL,
  `违章记载` varchar(255) NULL,
  `处罚方式` varchar(255) NULL,
  `处罚金额` decimal(64, 0) NULL,
  `警察编号` varchar(255) NULL,
  `警察签字` varchar(255) NULL,
  `被处罚人签字` varchar(255) NULLPRIMARY KEY (`驾照执照号码`)
PRIMARY KEY (`违规处罚编号`)
);
CREATE TABLE `驾照信息`  (
  `姓名` varchar(255) NULL,
  `驾照执照号码` varchar(255) NOT NULL,
  `登记地址` varchar(255) NULL,
  `邮政编码` varchar(255) NULL,
  `手机号码` varchar(255) NULL,
  PRIMARY KEY (`驾照执照号码`)
);
CREATE TABLE `车辆信息`  (
  `机动车牌照号码` varchar(255) NOT NULL,
  `机动车型号` varchar(255) NULL,
  `机动车制造厂` varchar(255) NULL,
  `机动车生产日期` varchar(255) NULL,
  PRIMARY KEY (`机动车牌照号码`)
);
CREATE TABLE `警察信息`  (
  `警察编号` varchar(255) NOT NULL,
  `警察姓名` varchar(255) NULL,
  PRIMARY KEY (`警察编号`)
);

(5)附加题:根据上述数据库表，利用标准SQL写出以下查询语句:

I)查询“车牌号”为“浙A000DM”的所有违章记录。

SELECT * FROM 交通违规处罚 WHERE 机动车牌照号码="浙A000DM"

II)查询“车牌号”为“浙A000DM”的违章次数。

SELECT COUNT(*) FROM 交通违规处罚 WHERE 机动车牌照号码="浙A000DM"

III)查询“驾驶执照号”为“330104XXXXD”的“罚款”总额。

SELECT SUM(处罚金额) FROM 交通违规处罚 WHERE 驾驶执照号码="330104XXXXD" and 处罚方式="罚款"

参考文章 

数据库系统原理,第二章课后答案

下图显示一张交通违章处罚通知书