实验1 数据库设计与实现

管理信息系统

实验1 数据库设计与实现

目的和要求

1． 根据实际场景，设计E-R模型；

2． 将E-R模型转换为数据库表；

3． 在Sql Server2000上实现数据库

4． 理解SQL语言

背景知识

E-R模型设计

1．E-R模型简介

E-R模型（Entity Relationship Model，实体联系模型）是P.P.Chen于1976年提出的。这个模型直接从现实世界中抽象出实体类型及实体间联系，然后用实体联系图（E-R图）表示数据模型。

E-R图中有下面四个基本成分：

（1）矩形框，表示实体类型；

（2）菱形框，表示联系类型；

（3）椭圆形框，表示实体类型或联系类型的属性；

（4）直线，用来连接实体与属性、联系与属性，联系与其涉及的实体，并在直线端部标联系的类型（1:1，1:N或M:N）。

2．E-R图的建立过程

（1）首先确定实体类型。

（2）确定联系类型。

（3）将实体类型和联系类型组合成ER图。

（4）确定实体类型和联系类型的属性，并为实体类型确定能唯一确定实体的实体标识符（标记为在该属性下面划线）。

示例1：根据下面语义设计ER模型。

管理信息系统

某单位有多个职工，每个职工只能参加一个工程项目，但一个工程项目中可以有很多职工参与；有多个供应商为各个工程项目供应不同零件。

分析：

（1） 本问题有四个实体类型：职工，工程项目，供应商，零件。

（2） 实体之间的联系类型：工程项目与职工是1:N联系，工程项目与零件是M:N

联系，供应商和设备是M:N联系。

（3） 组合成ER图。如图1-1所示。

（4） 确定属性。职工的属性有：职工编号，职工姓名，性别，出生年月，职工

编号为标识符。零件的属性有：零件编号，零件名称，颜色，重量，零件

编号为标识符。工程项目的属性有：项目编号，项目名称，项目开工日

期，项目编号为标识符。供应商的属性有：供应商编号，供应商名称，地

址，供应商编号为标识符。

参与联系的属性是某职工在某工程项目中的担任职务。使用联系的属性是某

项目需要某零件的数量。供应联系的属性是某供应商供应某程零件的数量。

注：如果ER图比较复杂，也可以将属性单独以文字的方式加以说明。

图1-1 ER图实例

管理信息系统

ER模型转换为数据库表

ER模型中的主要成分是实体类型和联系类型，转换规则就是如何把实体类型、联系类型转换成关系模式（即表）。

1．实体类型的转换：将每个实体类型转换成一个表（关系模式），实体的属性即为表的属性，实体标识符即为表的键。

2．联系类型的转换：根据联系的类型所不同的处理。

（1）1:1的联系：在联系的两个实体类型转换成的表中的任意一个表的属性中加入另一个表的键和联系类型的属性。

（2）1:N的联系：在N端实体类型转换成的表中加入1端实体类型转换成的表的键和联系类型的属性。

（3）M:N的联系：将联系类型也转换成表，其属性为两端实体类型的键加上联系类型的属性，而联系类型生成的表的键为两端实体键的组合。

示例1的ER模型转换成数据库表时，四个实体“职工”、“零件”、“工程项

目”和“供应商“分别转换为四个表，工程项目与职工的1:N联系通过将N端的工程项目的键“项目编号”加入1端的职工表实现，“使用”与“供应”两个联系为M:N的联系，则分别转换成表。转换后的表为： 职工表（职工编号，职工姓名，性别，出生年月，项目编号） 零件表（零件编号，零件名称，颜色，重量） 工程项目表（项目编号，项目名称，项目开工日期） 供应商表（供应商编号，供应商名称，地址） 使用表（项目编号，零件编号，数量） 供应表（零件编号，供应商编号，数量）

SQL语言

结构化查询语言（SQL）是在多数数据库环境下使用的标准的第四代查询语言。

1． SQL组成

（1）SQL DDL：数据定义。用于定义SQL模式、基本表、视图和索引。

管理信息系统

（2）SQL DML：数据操纵。分为数据查询和数据更新，数据更新又分为插入、删除和修改三种操作。

（3）SQL DCL：数据控制。包括对基本表和视图的授权，完整性规则的描述，事务控制语句等。

（4）Embedded SQL：嵌入式SQL，涉及到SQL语句嵌入在宿主语言程序中的使用规则。

日常使用较多的语句是数据操纵，下面介绍的也是数据查询和数据更新语句。

2． 数据查询

SQL的数据查询是使用最广泛的语句，而且查询语句只有一条SELECT语句，功能大常强大。其基本语法格式为：

Select 字段名表

From 表名

WHERE <条件表达式>

在SELECT之后要列出待查询的字段名称，FROM之后要指明使用哪些逻辑关系，WHERE后面描述选择的条件。

示例：

1．查询参与工程编号为“P03”的职工的姓名与性别。

Select 职工姓名，性别

From 职工表

Where 工程编号=’p03’

2． 查询供应了红色零件的供应商的姓名和地址。

Select 供应商姓名，地址

From 供应商表，零件表，供应表

Where 供应表.供应商编号=供应表.供应商编号 and

零件表.零件编号=供应表.零件编号

3． 数据插入

数据插入的语法结构为：

管理信息系统

insert into 表名（列名表） values（元组值）

示例：

向职工表中插入一条记录（’a09’,’张天一’,’男’,’1978-01-02’,’P02’），则插入的语句为：

insert into 职工表（职工编号，职工姓名，性别，出生年月，项目编号）

values（’a09’,’张天一’,’男’,’1978-01-02’,’P02’）

4． 数据删除

数据删除的语法结构为：

delete from 表名 where <条件表达式>

示例：

将所有参加项目编号为’p05’的职工删除，则删除的语句为：

delete from 职工表 where 项目编号=’p05’

5． 数据更新

数据更新的语法结构为：

update 表名 set 列名=值表达式[，列名=值表达式…]

[where 条件表达式]

示例：

所有女性职工都参加项目编号为p10项目，也就是将所有女职工的项目编号改为p10，更新语名为：

Update 职工表 set 项目编号=’p10’

Where 性别=’女’

内容和步骤

创建数据库

数据库实际上就是在硬盘中为诸如表、视图及存储结构等预先分配空间的系列文件。在SQL Server 2000数据库中存在主要数据文件、次要数据文件和事务日志3种数据文件。

管理信息系统

在SQL Server 2000下创建数据库有两种方法：使用企业管理器直接创建数据库；用

Transact-SQL语句创建数据库。

下面介绍使用企业管理器直接创建数据库。

1． 打开企业管理器，在左窗格中双击要建立数据库的服务器节点。

2． 展开该节点，右键点击“数据库”节点，从弹出的快捷菜单中选择“新建数据库”命

令。如图1-2所示。

图1-2 新建数据库

3． 弹出对话框，在“名称”文本框中输入正确的数据库名称，如图1-3所示。

图1-3 新建数据库对话框

管理信息系统

4． 点击“数据文件”选项卡，可以输入数据文件的名称、位置、大小，也可以使用SQL

Server 2000的默认值（例如，数据文件名就是在用户输入的文件后面增加上

_Data）。

5． 点击“事务日志”选项卡，可以输入日志文件的名称、位置、大小，也可以使用SQL

Server 2000的默认值（例如，数据文件名就是在用户输入的文件后面增加上

_Log）。

创建表

在关系数据库中，每个关系都体现为一张表，表是数据库的一个对象之一，是用来存储数据和操作数据的逻辑结构，使用数据库时，绝大多时间都是在与表打交道。表中有两个基本的对象：字段和记录。

字段也叫属性，也就是表中的列。

记录是字段的有序集合。一般用记录描述一个实体，因此有的时候记录也可以定义为能完整地描述一个实体的字段集，在二维表格中就是行。

在SQL Server 2000下创建表有两种方法：使用企业管理器创建表；用Transact-SQL语句创建数据库。

下面介绍使用企业管理器直接创建表。

1． 打开企业管理器，展开左窗格中树形结构上的数据库节点。

2． 选中创建好的数据为，右键点击数据库中的表对象，如图1-4所示。

图1-4 新建表

3． 弹出窗口。在这个窗口中，需要对表的内容进行定义，内容有：

管理信息系统

（1） 输入列名，列名必须唯一，列的命名规则必须以字母和下划线开头，后面跟字

母、数字和下划线。

（2） 选择正确的数据类型、数据长度和精度。

（3） 规定该列的值是否可以为空。

（4） 设置表格的主键和索引。

注：创建主键工作比较简单，首先选中要建立主键的列，然后右击鼠标，从弹出的快捷菜单中选择“设置主键”命令即可。建立为主键的列的旁边会出现一个小钥匙形的标志。也可以从快捷菜单中选择“属性”命令，弹出“属性”对话框，然后打开“索引/键”选项卡。

输入了“客户表”各列以及设置了“客户编号”为主键的界面如图1-5所示。

图1-5 新建表示例

修改表

1． 从树形结构上展开要修改定义的表所在的数据库。

管理信息系统

2． 选择该数据库节点下的表节点，则企业管理器显示该数据库下全部的表格。

3． 右键点击要修改的表名，从弹出的快捷菜单中选择“设计表”命令，弹出类似1-4

的窗口。

数据更新

数据更新包括数据插入、删除与更新。可以利用企业管理器的窗口与在查询分析器中执行SQL语句来完成。三种语句的执行步骤类似，不同的只是SQL语句的构建。下面以数据插入为例来介绍在查询分析器中的操作。

创建表格的目的是为了利用表格来存储和管理业务数据。实现数据存储的前提是向表格中添加数据。下面介绍使用Transact-SQL插入数据。

1．打开“查询分析器”，如图1-6所示。

图1-6 打开查询分析器

2．弹出窗品，在查询窗口中输入插入语句，单击F5键或点击“工具”—>“执行”。如图1-7所示。

管理信息系统

图1-7 利用查询分析器执行插入语句

练习题

为保险公司案例设计E-R模型，进行设计数据库并实现。

案例 数据库技术在沃凯(Vulcan)保险公司业务中的应用

沃凯保险公司的销售主要采用三种方式，即保险公司业务网点销售、保险公司业务人员销售、通过代理机构或代理人进行销售。保险公司业务网点销售即由顾客自己上门到保险公司业务网点进行投保，这是一直以来保险销售采取的一种“坐商”方式。保险公司业务人员销售即保险公司通过自己的业务人员联系客户，进行保险产品销售，这也是保险的一种主要销售方式。通过代理机构或代理人进行销售即保险公司通过大量的代理机构和代理人将保险产品推销出去，保险公司再给代理商一笔不菲的佣金，这在竞争日趋激烈的保险行业是一种较为普遍的销售方式。

不管哪种销售方式，其流程都是相似的。而且存储在数据库的数据也是大致相同的。

管理信息系统

参考答案：

1．保险公司案例的E-R模型设计

那么我们来分析一下，有哪些信息需要存储到数据库中呢？

（1）客户的相关信息，包括客户的姓名，性别，联系方式等；

（2）保险产品信息，包括产品名称，保险金额，保险年限等；

（3）公司业务人员信息，包括业务人员的基本信息，还有所在部门等；

（4）客户的保单信息，包括客户投保的日期，办理该保单的业务人员，投保了哪些保险产品等。

图1-8 保险公司数据库的ER图

注：各实体及联系的属性在下面详细说明。

（1）客户实体属性：客户编号，姓名，性别，出生年月，联系电话，地址

（2）保险产品实体属性：险种编号，险种名称，投保金额，保险年限，缴纳金额

（3）业务人员实体属性：营销员编号，营销员姓名，营管处编号，职级，出生年月，联系电话，地址

（4）投保联系属性：保单编号，保单状态，到期日，核保通过日，生效日，被保人编号，投保人编号，签单的营销员编号，险种编号

2．保险公司案例的数据库设计

管理信息系统

客户表（客户编号，姓名，性别，出生年月，联系电话1，联系电话2，地址，类型） 注：类型值为I时代表INSURED，即被保人，类型值为P时代表POLICY OWNER，即投保人

险种表（险种编号，险种名称，投保金额，保险年限，缴纳金额）

注：险种编号如果表示主合同和附加合同,则为01时代表主合同,02,03都为附加合同等

营销员个人信息表（营销员编号，营销员姓名，营管处编号，职级，出生年月，联系电话1，联系电话2，地址，状态）

注：（1）状态为A时为有效的营销员，否则为离职的营销员。

（2）职级大致可分为普通营销员 业务主管 业务经理 区处经理 营管处总监

（3）营管处编号 先分为业务发展一区部和业务发展二区部,业务发展一区部又可分为虹桥区部,普陀区部,长宁区部等,然后再分为长宁一处,进德四区处等,名字可根据路名等来命名

保单主表（保单编号，保单状态，到期日，核保通过日，生效日，被保人编号，投保人编号，签单的营销员编号）

注：（1）保单编号

A100013417 根据A开头可知道为意外险产品

H200023418 H开头为健康险产品

C300028438 C开头为寿险产品

（2）保单状态的编码含义

10 通过核保但保费未相配

12 生效后退保

13 拒保

14 支票退票

15 延期

16 未生效退保

17 通过核保待体检

管理信息系统

18 已通过核保,尚等缴保费

19 生效

20 失效终止

21 退保终止

22 死亡终止

23 不续保

24 自动退保

保单明细表（保单编号，险种编号……）

管理信息系统

实验操作内容：

1． 创建“insurance”数据库

2． 依次创建数据库表

3． 更新数据库表中的数据

（1）依照下表内容插入“客户”表中的数据。

（2）查找所有出生在1970年前的客户信息；

（3）查找所有姓“张”的客户的信息

（4）将所有姓“张”的客户的电话号码改为“12345678”

（5）删除所有姓“杨”的客户的信息

4． 依次插入如下表中的数据。

管理信息系统

思考：如何查找当前投保了人寿保险的客户有哪些？

本文来源：https://www.bwwdw.com/article/8uvq.html