实验1-数据库设计与实现

《管理信息系统》实验指导15/15实验1数据库设计与实现目的和要求根据实际场景，设计E-R模型；将E-R模型转换为数据库表；在SqlServer2000上实现数据库理解SQL语言背景知识E-R模型设计1．E-R模型简介E-R模型（EntityRelationshipModel，实体联系模型）是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:N联系，工程项目与零件是M:N联系，供应商和设备是M:N联系。组合成ER图。如图1-1所示。确定属性。职工的属性有：职工编号，职工姓名，性别，出生年月，职工编号为标识符。零件的属性有：零件编号，零件名称，颜色，重量，零件编号为标识符。工程项目的属性有：项目编号，项目名称，项目开工日期，项目编号为标识符。供应商的属性有：供应商编号，供应商名称，地址，供应商编号为标识符。参与联系的属性是某职工在某工程项目中的担任职务。使用联系的属性是某项目需要某零件的数量。供应联系的属性是某供应商供应某程零件的数量。注：如果ER图比较复杂，也可以将属性单独以文字的方式加以说明。MMN使用零件供应商供应MN职工工程项目参与1N担任职务数量数量图1-1ER图实例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）是在多数数据库环境下使用的标准的第四代查询语言。SQL组成（1）SQLDDL：数据定义。用于定义SQL模式、基本表、视图和索引。（2）SQLDML：数据操纵。分为数据查询和数据更新，数据更新又分为插入、删除和修改三种操作。（3）SQLDCL：数据控制。包括对基本表和视图的授权，完整性规则的描述，事务控制语句等。（4）EmbeddedSQL：嵌入式SQL，涉及到SQL语句嵌入在宿主语言程序中的使用规则。日常使用较多的语句是数据操纵，下面介绍的也是数据查询和数据更新语句。数据查询SQL的数据查询是使用最广泛的语句，而且查询语句只有一条SELECT语句，功能大常强大。其基本语法格式为：Select字段名表From表名WHERE<条件表达式>在SELECT之后要列出待查询的字段名称，FROM之后要指明使用哪些逻辑关系，WHERE后面描述选择的条件。示例：1．查询参与工程编号为“P03”Select职工姓名，性别From职工表Where工程编号=’p03查询供应了红色零件的供应商的姓名和地址。Select供应商姓名，地址From供应商表，零件表，供应表Where供应表.供应商编号=供应表.供应商编号and零件表.零件编号=供应表.零件编号数据插入数据插入的语法结构为：insertinto表名（列名表）values（元组值）示例：向职工表中插入一条记录（’a09’,’张天一’,’男’,’1978-01-02’,’P02insertinto职工表（职工编号，职工姓名，性别，出生年月，项目编号）values（’a09’,’张天一’,’男’,’1978-01-02’,’数据删除数据删除的语法结构为：deletefrom表名where<条件表达式>示例：将所有参加项目编号为’p05’deletefrom职工表where项目编号=’p05数据更新数据更新的语法结构为：update表名set列名=值表达式[，列名=值表达式…][where条件表达式]示例：所有女性职工都参加项目编号为p10项目，也就是将所有女职工的项目编号改为p10，更新语名为：Update职工表set项目编号=’p10Where性别=’女’内容和步骤创建数据库数据库实际上就是在硬盘中为诸如表、视图及存储结构等预先分配空间的系列文件。在SQLServer2000数据库中存在主要数据文件、次要数据文件和事务日志3种数据文件。在SQLServer2000下创建数据库有两种方法：使用企业管理器直接创建数据库；用Transact-SQL语句创建数据库。下面介绍使用企业管理器直接创建数据库。打开企业管理器，在左窗格中双击要建立数据库的服务器节点。展开该节点，右键点击“数据库”节点，从弹出的快捷菜单中选择“新建数据库”命令。如图1-2所示。图1-2新建数据库弹出对话框，在“名称”文本框中输入正确的数据库名称，如图1-3所示。图1-3新建数据库对话框点击“数据文件”选项卡，可以输入数据文件的名称、位置、大小，也可以使用SQLServer2000的默认值（例如，数据文件名就是在用户输入的文件后面增加上_Data）。点击“事务日志”选项卡，可以输入日志文件的名称、位置、大小，也可以使用SQLServer2000的默认值（例如，数据文件名就是在用户输入的文件后面增加上_Log）。创建表在关系数据库中，每个关系都体现为一张表，表是数据库的一个对象之一，是用来存储数据和操作数据的逻辑结构，使用数据库时，绝大多时间都是在与表打交道。表中有两个基本的对象：字段和记录。字段也叫属性，也就是表中的列。记录是字段的有序集合。一般用记录描述一个实体，因此有的时候记录也可以定义为能完整地描述一个实体的字段集，在二维表格中就是行。在SQLServer2000下创建表有两种方法：使用企业管理器创建表；用Transact-SQL语句创建数据库。下面介绍使用企业管理器直接创建表。打开企业管理器，展开左窗格中树形结构上的数据库节点。选中创建好的数据为，右键点击数据库中的表对象，如图1-4所示。图1-4新建表弹出窗口。在这个窗口中，需要对表的内容进行定义，内容有：输入列名，列名必须唯一，列的命名规则必须以字母和下划线开头，后面跟字母、数字和下划线。选择正确的数据类型、数据长度和精度。规定该列的值是否可以为空。设置表格的主键和索引。注：创建主键工作比较简单，首先选中要建立主键的列，然后右击鼠标，从弹出的快捷菜单中选择“设置主键”命令即可。建立为主键的列的旁边会出现一个小钥匙形的标志。也可以从快捷菜单中选择“属性”命令，弹出“属性”对话框，然后打开“索引/键”选项卡。输入了“客户表”各列以及设置了“客户编号”为主键的界面如图1-5所示。图1-5新建表示例修改表从树形结构上展开要修改定义的表所在的数据库。选择该数据库节点下的表节点，则企业管理器显示该数据库下全部的表格。右键点击要修改的表名，从弹出的快捷菜单中选择“设计表”命令，弹出类似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）客户的保单信息，包括客户投保的日期，办理该保单的业务人员，投保了哪些保险产品等。客户客户保险产品业务人员投保1234图1-8保险公司数据库的ER图注：各实体及联系的属性在下面详细说明。（1）客户实体属性：客户编号，姓名，性别，出生年月，联系电话，地址（2）保险产品实体属性：险种编号，险种名称，投保金额，保险年限，缴纳金额（3）业务人员实体属性：营销员编号，营销员姓名，营管处编号，职级，出生年月，联系电话，地址（4）投保联系属性：保单编号，保单状态，到期日，核保通过日，生效日，被保人编号，投保人编号，签单的营销员编号，险种编号2．保险公司案例的数据库设计客户表（客户编号，姓名，性别，出生年月，联系电话1，联系电话2，地址，类型）注：类型值为I时代表INSURED，即被保人，类型值为P时代表POLICYOWNER，即投保人险种表（险种编号，险种名称，投保金额，保险年限，缴纳金额）注：险种编号如果表示主合同和附加合同,则为01时代表主合同,02,03都为附加合同等营销员个人信息表（营销员编号，营销员姓名，营管处编号，职级，出生年月，联系电话1，联系电话2，地址，状态）注：（1）状态为A时为有效的营销员，否则为离职的营销员。（2）职级大致可分为普通营销员业务主管业务经理区处经理营管处总监（3）营管处编号先分为业务发展一区部和业务发展二区部,业务发展一区部又可分为虹桥区部,普陀区部,长宁区部等,然后再分为长宁一处,进德四区处等,名字可根据路名等来命名保单主表（保单编号，保单状态，到期日，核保通过日，生效日，被保人编号，投保人编号，签单的营销员编号）注：（1）保单编号A100013417根据A开头可知道为意外险产品H200023418H开头为健康险产品C300028438C开头为寿险产品（2）保单状态的编码含义

10通过核保但保费未相配

12

生效后退保

13

拒保

14支票退票

15

延期

16

未生效退保

17

通过核保待体检

18已通过核保,尚