子项目3数据表创建及管理

子项目3数据表创建及管理数据表的设计概念模型设计－逻辑模型设计－物理模型设计需求分析概念模型设计逻辑模型设计物理模型设计现实世界信息世界计算机世界存储模式RDBMS模型E-R图任务3.1概念模型设计概念模型设计就是把现实世界中的客观对象抽象为某一种信息结构。它是整个数据库设计的关键，它通过对用户需求进行综合、归纳与抽象，形成一个不依赖某一数据管理系统（Databasemanagementsystem，DBMS）概念模型设计的产物是E－R模型E-R模型E-R模型中有以下三个基本的元素：（1）实体（Entity）所谓实体，即现实世界中存在的、可以相互区别的人或事物。实体可以具体的人、事、物或抽象的概念或联系。例如，实验室的一个学生、一位老师，或抽象的一次报销、一次借款等财务管理中所涉及到的实体有哪些？用户经费本实验室事件实验室事件明细财务处事件（2）属性(Attribute)实体所具有的某一特性称为属性。一个实体可以有若干个属性来刻画。例如学生实体可以由学号、姓名、性别、出生年月、班级等属性组成。用户信息主要包括用户ID、用户名称、用户的密码、用户类型、用户状态等，其中用户类型主要用于权限控制，取不同的值，代表不同类型的用户。它主要有实验室一般人员、财务管理人员、经费本管理人员（经费本管理人员主要指导师），以上权限递增。用户标志字段的设置主要是为了方便用户操作，如果该学生已毕业，他的用户信息应该删除，但是由于他所参与的课题还没有结题，他所有的经费操作还应该在系统中反映出来，因此可以通过设置不同的值，来体现学生是否已毕业；财务处事件主要包括财务处事件ID、事件摘要、事件的收入、事件的支出、事件发生的日期、备注、事件的标志，其中事件的标志主要标明财务处事件的类型，主要有借款、报销、项目到帐、其它收入、其它支出等项目；实验室事件和财务处事件类似，它主要管理实验室一般人员所提交的事件。它主要有事件ID、事件摘要、事件收入、事件支出、事件发生的日期、事件的备注、事件的标志、事件的状态、导师对事件的意见、是否给钱实验室事件标志包括借款、实验室内部报销、其它支出、其它收入、财务处报账等；事件的状态字段用于标识该事件是否可以修改；是否给钱字段是财务管理人员用于实时标识是否把钱给了实验室人员。实验室事件明细包括事件ID、事件摘要、事件收入、事件支出、事件发生日期、事件备注等（3）联系（Relationship）现实世界中，事物是相互联系的。所谓联系就是指实体之间的相互关系称为联系。通过找实体时主要是找名词，而找关系时主要是找动词。在财务管理项目中，保管就构成了实体“经费本管理人员”和“经费本”之间的关系。类似的还有“财务管理人员审核实验室事件”，“经费本包含财务处事件”等。联系的类型有三种：一对一联系（1：1）。如果对于实体集A中的每一个实体，实体集B中至多有一个实体与之联系。一对多联系（1：n）。如果对于实体集A中的每一个实体，实体集B中有n个实体与之联系，反之，对于实体集B中的每一个实体，实体集A中至多只有一个实体与之联系，则称实体集A与实体集B具有一对多的联系。多对多联系（m：n）。如果对于实体集A中每一个实体，实体集B中有n个实体与之联系，反之，对于实体集B中的每一个实体，实体A中也有m个实体与之联系，则称实体集A与B具有多对多的联系。在E-R模型中，实体用矩形表示，矩形框内写明实体名。属性用椭圆表示，椭圆内写上属性的名称，并用无向边将其与相应的实体连接起来。联系用菱形表示，菱形内写明联系的名称，通常用一个动词命名负责1产品产品号产品名价格1职工职工号姓名年龄负责1产品产品号产品名价格1职工职工号姓名年龄练习题画出学生管理系统E-R图包括系、学生、课程三个实体一个系有多个学生，一个系可以开设多个课程，一个学生可以选修多门课程，一个课程可以被多个学生选学练习题画出教师任课Ｅ－Ｒ图一个系部可以有多名教师，一个教师可以讲授多门课程，一门课程可以被多位老师讲授教务管理系统的初步E－R图教务管理系统的基本E－R图教务管理系统的基本E－R图画出财务管理系统的E-R图经费本管理人员财务处事件经费本财务管理人员一般人员保管、审批包含报帐审核实验室事件提交事件明细包含用户n111111nnnnn逻辑模型设计需求分析概念模型设计逻辑模型设计物理模型设计现实世界信息世界计算机世界存储模式RDBMS模型E-R图逻辑模型设计的任务就是把概念模型设计阶段设计好的E-R图转换为具体的数据库管理系统支持的数据模型，为物理模型设计阶段做好准备。3.2.1将E-R图转换为关系模型E-R模型转换成关系模型实际上就是要将实体、属性和联系转化为关系模式的集合。联系的类型有三种，转换的规则将依据联系类型的不同，将有不同的转换规则。•（1）主键•（2）外键外键从字面可以理解为在外面是关键字。也就是说它在另一个表中是关键字。(1)1：1的转换规则负责1产品产品号产品名价格1职工职工号姓名年龄负责1产品产品号产品名价格1职工职工号姓名年龄第一步：联系形成的关系独立存在：职工表（职工号，姓名，年龄）主码：职工号产品表（产品号，产品名，价格）主码：产品号负责（职工号，产品号）主码：职工号或产品号合并方案1：“负责”与“职工”两关系合并：职工（职工号，姓名，年龄，产品号）产品（产品号，产品名，价格）合并方案2：“负责”与“产品”两关系合并：职工（职工号，姓名，年龄）产品（产品号，产品名，价格，职工号）（2）1：n的转换规则步骤一：联系形成的关系独立存在。实验室事件（实验室事件编号，事件摘要，收入金额，支出金额，提交日期，备注，标识，审核状态，意见，是否给钱）主码：实验室事件编号事件明细（明细编号，事件摘要，金额，发生日期，备注）主码：明细编号包含（明细编号，实验室事件编号）主码：明细编号合并后方案：实验室事件（实验室事件编号，事件摘要，收入金额，支出金额，提交日期，备注，标识，审核状态，意见，是否给钱）主码：实验室事件编号事件明细（明细编号，实验室事件编号，事件摘要，金额，发生日期，备注）主码：明细编号实验室事件事件明细包含事件摘要收入金额支出金额实验室事件编号提交日期明细编号事件摘要金额发生日期备注1n（3）m：n的转换规则学生（学号，姓名，性别，年龄）主码：学号课程（课程号，课程名，学分）主码：课程号选课（学号，课程号，成绩）主码：学号和课程号学生课程课程号学分课程名成绩mn姓名性别学生年龄选课学生课程课程号学分课程名成绩mn姓名性别学生年龄选课根据上面的转换规则，将财务管理项目的E-R模型转换成关系模型为：用户（用户编号，用户名称，用户密码，用户类型，用户标识）实验室事件（实验室事件编号，审核人编号，提交人编号，事件摘要，收入金额，支出金额，提交日期，备注，标识，审核状态，意见，是否给钱）事件明细（明细编号，实验室事件编号，事件摘要，金额，发生日期，备注）财务处事件（财务处事件编号，经费本编号，报帐人编号，事件摘要，收入金额，支出金额，提交日期，备注，标识）经费本信息（经费本编号，保管人编号，审批人编号，项目编号，财务编号，项目类型，项目名称，支出科目代码，支出科目名称，暂付科目编号，暂付科目名称，单位编号，单位名称，经费本发放日期，经费状态）任务3.3物理模型设计物理设计的任务是将逻辑模型设计的结果在具体的数据库管理系统（DBMS）进行实现。目标就是将关系模式转换为数据表。需求分析概念模型设计逻辑模型设计物理模型设计现实世界信息世界计算机世界存储模式RDBMS模型E-R图任务3.3.1确定属性的数据类型属性、字段或列实体、记录或行销售人员表工号部门号姓名地址电话001001张明深圳138…002002李洪天津139…数据类型数据类型分类数据类型名称精确数字bigint,int,smallint,tinyint,bit,decimal,numeric,money,smallmoney近似数字Float,real日期和时间Datetime,smalldatetime字符串Char,varchar,textUnicode字符串Nchar,nvarchar,ntext二进制字符串Binary,varbinary,image其他数据类型Cursor,sql_variant,table,timestamp整数类型数据类型范围存储空间Bigint-263~263-18字节Int-231~231-14字节Smallint-215~215-12字节Tinyint0~2551字节近似数字类型数据类型存储空间备注Float取决于n的值Float(n),1n24时4字节存储，25n53时8个字节存储Real4字节Real(n)占用4个字节，最大可有7位精确位数日期和时间类型数据类型范围精确度Datetime1753.1.1~9999.12.313.33msSmalldatetime1900.1.1~2079.6.61分钟字符串类型数据类型存储空间备注Char(n)N个字节1n8000Varchar(n)N+2字节1n8000Text根据情况而定1n231-1财务管理项目各属性及所对应的数据类型任务3.3.2使用图形工具创建数据表及联系任务3.3.3使用SQL语句创建数据表及联系系统生成SQL语句通过抽象和提炼可以得到创建表命令的基本语法结构如下：CREATETABLE表名{(列名列属性列约束)}[,...]CREATETABLEstudent(stuIDVARCHAR(8),stuNamevarchar(20),stuAgetinyint,Sexchar(2))3.3.4创建完整性约束（1）默认值约束（2）增量约束（3）唯一键约束（4）外键约束（5）CHECK约束3.5数据表的管理对于数据表的管理主要体现在两方面：修改表和删除表。例如，修改表中的字段、添加新的字段、删除表中字段等。3.5.1修改表（1）使用图形工具修改表（2）使用SQL语句修改表使用SQL语句修改表，主要分为三类，分别为：添加新字段、修改字段的属性、删除字段。1、添加新字段。通过在ALTERTABLE语句中使用ADD子句，可以在表中增加一个或多个字段，其语法格式如下：ALTERTABLE表名ADD列名数据类型[（长度）][NULL|NOTNULL]例如，要向tbbookinfo中插入字段f_memo，使用SQL语句为：ALTERTABLEtbbookinfoADDf_memovarchar(100)NULL注意：向已有记录的表中添加列时，新添加的字段通常设置为允许为空，否则必须为该列指定默认值，SQLServer2005将默认值赋给现有记录中的新添字段中。否则SQLServer2005将会提示操作失败。2、修改字段的属性。通过在ALTERTABLE语句中使用ALTERCOLUNM子句，可以修改列的数据类型、长度等属性，其语法格式如下：ALTERTABLE表名ALTERCOLUMN列名数据类型[（长度）][NULL|NOTNULL]表tbfevent中的字段fe_memo的数据类型为varchar(500)和表tblevent中的字段le_memo的数据类型也为varchar(500)，但在实际的应用中发现数据的长度设置过长，太浪费存储空间，现需要将其数据类型改为varchar(100)，使用SQL语句修改fe_memo的数据类型为：ALTERTABLEtbfeventALTERCOLUMNfe_memovarchar(100)3、删除字段。通过在ALTERTABLE语句中使用DROPCOLUMN子句，可以删除表中的字段。其语法格式为：ALTERTABLE表名DROPCOLUMN列名将表tbbookinfo中新建的字段f_memo给删除，使用SQL语句为：ALTERTABLEtbbookinfo