数据库模块 - 图文

【试题1】 1任 务

你作为《建设工程监管信息系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2要 求

《系统权限管理》子模块的E-R图如图2.1 所示，逻辑数据模型如图2.2 所示，物理数据模型如图2.3所示，数据表字段名定义见表2.3。请按以下设计完成数据库创建、数据表创建和数据操作任务：

所属部门用户密码用户姓名密码锁号用户ID操作员基本信息备注角色名称功能角色IDM功能名称功能ID备注M备注联系电话联系地址手机号拥有N功能角色定义一级功能权限定义N拥有 图2.1 E-R图

一级功能权限定义表功能IDCharacters (3)功能名称Variable characters (32)备注Variable characters (64)Relationship_1功能角色定义表功能角色IDCharacters (3)角色名称Variable characters (32)备注Variable characters (64)Relationship_2操作员基本信息表用户ID用户姓名用户密码所属部门联系电话联系地址手机号密码锁号备注Characters (4)Characters (16)Characters (16)Characters (3)Variable characters (16)Variable characters (32)Variable characters (16)Variable characters (64)Variable characters (64) 图2.2 逻辑数据模型

一级功能权限定义表功能ID功能名称备注char(3)varchar(32)varchar(64)功能角色定义表功能角色ID角色名称备注char(3)varchar(32)varchar(64)FK_RELATION_RELATIONS_T_FUNC_RFK_RELATION_RELATIONS_T_FUNC_IFK_RELATION_RELATIONS_T_FUNC_RRelationship_1功能ID功能角色IDchar(3)char(3)Relationship_2功能角色ID用户IDchar(3)char(4)FK_RELATION_RELATIONS_T_USER操作员基本信息表用户ID用户姓名用户密码所属部门联系电话联系地址手机号密码锁号备注char(4)char(16)char(16)char(3)varchar(16)varchar(32)varchar(16)varchar(64)varchar(64) 图2.3 物理数据原型 表2.3 字段名定义表

字段名 Func_id Func_name Func_role_id Func_role_name User_id User_name Reserve 字段说明 功能id 功能名称 功能角色id 角色名称 用户id 用户姓名 备注 字段名 User_passwd Dept_id Telephone Address Handphone Usb_no 字段说明 用户密码 所属部门 联系电话 联系地址 手机号 密码锁号 3 数据库操作 3.1 创建数据库

创建数据库ConstructionDB。 3.2创建数据表

根据图2.2和表2.3，创建数据表T_user、T_func_item、T_func_role_def。 3.3创建数据表间的关系

根据物理数据原型，创建数据关系表。 3.4数据操作

用SQL语句完成如下操作：

? 在T_user表插入数据：“id01，刘德华，123，KBB，5678900，湖南长沙，13899005678，ldh123，admin”；

? 查询出所属部门为“KBB”的操作员的基本信息； ? 查询出姓名为“刘德华”的操作员具有哪些功能权限； ? 查询出“投标责任人”角色所拥有的功能； ? 创建视图查询操作员的姓名，密码和所属部门；

? 创建存储过程，查询指定操作员所具有的功能权限。 【试题2】

1 任 务

你作为《建设工程监管信息系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建；

? 数据表相关约束的创建。 2 要 求

《流程管理》子模块的E-R图如图2.4 所示，逻辑数据模型如图2.5所示，物理数据模型如图2.6所示，数据表字段名定义见表2.4。请按设计要求完成数据库创建、数据表创建和数据表约束的创建任务：

招标范围代号招标方式代号密码锁号标段名称标段编号工程编号标段情况项目选项代号项目性质代号发包方式流程类别号评分类别号完成标志强制中断标志备注流程类别名称流程类别号流程步骤描述N时限流程步骤名称二级菜单链接流程步骤ID流程步骤定义备注N属于1流程类别招标方式代号项目选项代号备注M拥有 图2.4 E-R图 图2.5 逻辑数据模型

图2.6 物理数据原型 表2.4 字段名定义表

字段名 Step_no Step_name Step_des Limit_time URL Project_id Sub_pro_id Sub_pro_name Usb_no In_method_id In_scope_id 流程步骤ID 流程步骤名称 流程步骤描述 时限 二级菜单链接 工程编号 标段编号 标段名称（招标项目名称） 密码锁号 招标方式代号 招标范围代号 字段说明 字段名 In_choice_id Proj_type_id Engi_type_id Pack_type Grade_type_id Flag_done Flag_forcebreak Flow_type_id Serial_no Flow_type_name 字段说明 项目选项代号 项目性质代号 工程性质代号 发包方式 评分类别号 完成标志 强制中断标志 流程类别号 流程顺序号 流程类别名称 3 数据库操作 3.1 创建数据库

创建数据库ConstructionDB。 3.2 创建数据表

根据图2.5和表2.4，创建数据表T_flow_step_def、T_flow_type、T_sub_project。 3.3 创建数据表间的关系

根据物理数据原型，创建数据关系表。 3.4 约束设计

? 标段编号字段默认为 0；

? 招标方式代号只能为1或者2，其中1表示“公开”,2表示“邀请”；

? 项目选项代号只能为1、2、3之一，其中1表示“招标”，2表示“直接发包”；

3表示“违补”；

? 发包方式字段默认为0； ? 完成标志字段默认为0； ? 强制中断标志字段默认为0。

3.5 数据操作

用SQL语句完成如下操作：

? 在T_flow_step_def表中插入数据：“1，中标公示，公示中标情况，30，

http://localhost:8080/stepmenu.jsp，在规定时间内如有异议则请联系我们”； ? 查询出招标方式代号为“1”的所有标段情况信息； ? 查询出所有完成标志为“0”的标段情况信息； ? 查询出流程类别名称为“开标”的所有标段情况；

? 创建视图查询所有的流程的步骤名称、流程步骤描述及流程类别名称。 【试题3】

1任 务

你作为《银行信贷管理系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2要 求

《银行信贷管理系统》的E-R图如图2.7 所示，逻辑数据模型、物理数据模型如图2.8和图2.9所示。数据表字段名定义见表2.5。请按以下设计完成数据库创建、数据表创建和数据操作任务：

图2.7 E-R图

图2.8 逻辑数据模型

图2.9 物理数据模型 表2.5 字段名定义表

字段名 T_customer_info T_cust_credit_level T_his_cust_credit_level Cust_id Cust_name Legal_name 字段说明 客户基本信息表 客户信用等级表 客户信用等级历史表 客户编号 客户名称 法人代表 字段名 Reg_address Post_code Id Credit_level Begin_date End_date 字段说明 注册地址 邮政编码 序号 信用级别 评级有效起始日期 评级有效中止日期 3 数据库操作 3.1 创建数据库

创建数据库BankCreditLoanDB。 3.2 创建数据表

根据图2.8和表2.5，创建数据表T_customer_info、T_cust_credit_level、T_his_cust_credit_level。其中,客户信用等级历史表中的序号为自动增长字段。

3.3 创建数据表间的关系及约束

? 为表设置主键，主键命名为“pk_<表名>_<主键标识>” 。

? 根据逻辑模型，创建数据表之间的关系，关系命名为“fk_<表名>_<主表名>_<外键

标识>” 。

? 将信用级别字段默认值设置为“01”。 3.4 数据操作

用SQL语句完成如下操作：

? 根据对逻辑数据模型的理解，分别向三个表中插入一条测试数据；

? 查询客户名称为“XX公司”的信用等级（说明：“XX公司”为插入测试数据中的公司名称）；

? 创建视图V_His_cust_credit_level用于查询客户的信用级别历史记录，视图列名显示为：客户名称、信用级别、评级有效起始日期、评级有效中止日期；

? 创建带输入参数的存储过程P_ cust_credit_level，根据输入参数更新客户信用等级表，并将更新前的记录插入到客户信用等级历史表。 【试题4】

1任 务

你作为《银行信贷管理系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2要 求

《银行信贷管理系统》的E-R图如图2.10 所示，逻辑数据模型、物理数据模型如图2.11和图2.12所示。数据表字段名定义见表2.6。请按以下设计完成数据库创建、数据表创建和数据操作任务：

法人代表客户名称邮政编码客户编号客户基本信息表押物名称借据号押物数量鉴定日期注册地址1客户经理级别客户经理编号工作电话质押N质押信息台帐M客户经理信息表1经手

图2.10 E-R图

客户基本信息表客户编号Characters (10)客户名称法人代表注册地址...借据号Relationship_1质押信息台帐Characters (10)Variable characters (60)Variable characters (10)Variable characters (60)客户编号Characters (10)客户经理编号Characters (3)押物名称Variable characters (60)押物数量Integer签订日期Date & Time...Relationship_2客户经理信息表客户经理编号Characters (3)客户经理姓名客户经理级别工作电话Variable characters (10)Characters (2)Variable characters (20) 图2.11逻辑数据模型

客户基本信息表客户编号char(10)客户名称法人代表注册地址邮政编码varchar(60)varchar(10)varchar(60)char(6)FK_Relationship_1质押信息台帐借据号char(10)客户编号客户经理编号押物名称押物数量签订日期char(10)char(3)varchar(60)intdatetimeFK_Relationship_2客户经理信息表客户经理编号char(3)客户经理姓名客户经理级别工作电话varchar(10)char(2)varchar(20) 图2.12 物理数据模型 表2.6 字段名定义表

字段名 Agency_id Agency_name Agency_level Cust_id Cust_name Legal_name Agency_phone 字段说明 客户经理编号 客户经理姓名 客户经理级别 客户编号 客户名称 法人代表 工作电话 字段名 Reg_address Post_code Borrow_id Pawn_goods_name Pawn_goods_num Contract_date 字段说明 注册地址 邮政编码 借据号 押物名称 押物数量 签订日期 3数据库操作 3.1 创建数据库

创建数据库BankCreditLoanDB。 3.2 创建数据表

根据图2.11和表2.6，创建数据表T_customer_info、T_impawn_info、T_agency_info。 3.3 创建数据表间的关系及约束

? 为表设置主键，主键命名为“pk_<表名>_<主键标识> ”；

? 根据逻辑数据模型，创建数据表之间的关系，关系命名为“fk_<表名>_<主表名>_<

外键标识>”；

? 要求邮政编码由6位数字组成。 3.4 数据操作

用SQL语句完成如下操作：

? 根据你对逻辑数据模型的理解，分别向三个表中插入一条测试数据，其中客户经理编号为“001”；

? 查询 “XX公司”质押的物品及数量（说明：“XX公司”为插入测试数据中的公司名称）；

? 统计每个客户经理所经手的质押业务数，查询结果集应包含字段：客户经理姓名、质押业务数；

? 创建存储过程P_customer_info，删除指定客户编号的客户基本信息，同时也删除该客户在质押信息台帐中的所有记录。 【试题5】

1 任 务

你作为《某电子商务网站》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2 要 求

《电子商务网站》的E-R图如图2.13 所示，逻辑数据模型如图2.14 所示，物理数据模型如图2.15所示，数据表字段名定义见表2.7。请按以下设计完成数据库创建、数据表创建和数据操作任务：

价格产品名称录入日期产品编号评论编号M产品录入日期1拥有拥有类别IDM产品评论评论内容产品编号产品描述类别名称录入日期1类别 图2.13 E-R图

图2.14 逻辑数据模型图

图2.15 物理数据模型图 表2.7字段名定义表

字段名 Category_id Category_name Register_date Product_id Category_id Product_name Price 字段说明 产品类别ID 产品类别名称 默认值为当前录入时间 产品编号 产品类别ID 产品名称 产品价格 字段名 Remark Register_date Review_id Product_id Review Register_date 字段说明 产品描述 默认值为当前录入时间 评论编号 产品编号 评论内容 默认值为当前录入时间 3 数据库操作 3.1 创建数据库

创建数据库ProductDB。

3.2 创建数据表

根据图2.14和表2.7，创建数据表T_category、T_product_review、T_product，其中产品表的产品ID(Product_id)列设置为标识列，自动从1开始增长。

3.3 创建数据表之间的关系

? 创建主键（三个表均设置）；

? 产品价格列（Price）只能输入1-1000之间的数；

? 录入时间列(Register_date)默认值为当前录入时间（三个表均设置）。 3.4 数据查询

用SQL语句查询出如下数据：

? 在三个表中录入3条测试数据； ? 查询某类别下所有产品；

? 查询产品价格在300-500元之间的产品；

? 查询录入日期在2011年3月到6月之间的产品数据； ? 查询产品价格在90-200元之间的所有评论； ? 查询评论数在1-3条的所有产品。 【试题6】

1任务

你作为《学生食堂信息管理系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2要求

学生食堂信息管理系统的E-R图如图2.16 所示，逻辑数据模型如图2.17 所示，物理数据模型如图2.18所示，数据表字段名定义见表2.8。请按以下设计完成数据库创建、数据表创建和数据操作任务：

学生姓名学生学号饭卡生成时间饭卡编号充值余额1饭卡充值编号1拥有M充值充值时间饭卡余额拥有消费编号饭卡编号消费金额消费时间M消费 图2.16 E-R图

图2.17 逻辑数据模型图

图2.18 物理数据模型图

表2.8字段名定义表

字段名 Card_id Student_id Student_name Curr_money Register_date Add_id Card_id 饭卡编号 学生学号 学生姓名 饭卡余额(初始值为0) 饭卡生成时间；默认值为当前录入时间 充值编号 饭卡编号 字段说明 字段名 The_money 某次充值金额 字段说明 Register_date 充值时间；默认值为当前录入时间 Consume_id Card_id The_money 消费编号 饭卡编号 本次消费金额 Register_date 消费时间；默认值为当前录入时间 3 数据库操作 3.1 创建数据库

创建数据库CardDB。 3.2 创建数据表

根据图2.17和表2.8，创建数据表T_card、T_add_money、T_consume_money，其中将饭卡表的饭卡编号 (Card_id)列设置为标识列，自动从1开始增长。

3.3 创建数据表之间的关系

? 创建表主键（三个表均设置）；

? 充值金额列（TheMoney）只能输入50-200之间的数；

? 录入时间列(Register_date)默认值为当前录入时间（三个表均设置）。 3.4 数据查询

用SQL语句查询出如下数据：

? 在每个表中插入1条测试数据。 ? 查询所有饭卡信息；

? 查询所有饭卡的余额之和；

? 查询某日所有饭卡的充值金额之和(比如2011年6月2号总共充值金额是多少元)； ? 创建存储过程，根据某个学生的饭卡ID上的余额，判断该饭卡是否能进行一定金额的消费。（比如饭卡ID号为123的饭卡余额只有3.5元，则该饭卡不能购买5.5元的中餐）。存储过程输入参数为饭卡ID号，本次消费金额，返回值为true 或者false。 【试题7】

1 任 务 你作为《建设用地信息系统》项目基础数据设置子模块开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2 要 求

《建设用地信息系统》基础数据设置子模块的E-R图如图2.19所示，逻辑数据模型如图2.20 所示，物理数据模型如图2.21所示，数据表字段名定义见表2.9。请按以下设计完成数据库创建、数据表创建和数据操作任务：

项目类型项目编号项目名称审批单位项目ID用地基本信息行政区ID行政区编号直属部门行政区面积行政区ID从属行政基本信息备注行政区名称耕地面积项目性质农用地总面积 图2.19 E-R图

图2.20 逻辑数据模型

图2.21物理数据模型 表2.9字段名定义表

字段名 Proj_id Proj_no Proj_name Canton_no Proj_type Proj_kind Farm_tot Tilth_state Approve_unit 字段说明 项目ID 项目编号 项目名称 行政区编号 项目类型 项目性质 农用地总面积 耕地面积 审批单位 字段名 Canton_id Canton_no Canton_name Canton_tot Branch Remark 字段说明 行政区ID 行政区编号 行政区名称 行政区面积 直属部门 备注 3 数据库操作 3.1 创建数据库 创建数据库Proj。 3.2 数据表设计

根据图2.20和表2.9，创建数据表T_proj_info、T_canton_info。 3.3 数据表之间的关系设计

根据物理数据原型，创建数据关系表。。 3.4 数据操作

用SQL语句查询出以下数据：

? 查询出项目编号为C0001的建设用地基本信息；

? 查询出行政直属部门为“长沙市国土资源局”的建设用地基本信息； ? 查询出所有的建设土地基本信息并按农用地总面积升序排序； ? 删除耕地面积大于“720”的建设用地基本信息；

? 请把直属部门由“长沙市国土资源局”修改为“株洲市国土资源局”；

? 在行政区域信息表中插入数据，编号为“83100”，行政名称为“星沙”，行政面积为“142”，直属部门为“长沙市国土资源局”；

? ?

创建名为ProjInfo_view1的视图，视图的数据为编号是C0001的建设用地基本信息； 删除上题中所创建的ProjInfo_view1视图。

【试题8】

1 任 务

你作为《建设用地信息系统》报批管理子模块开发组的程序员，请按要求完成： ? 数据库的创建；

? 数据表的创建；

? 相关数据字段的操作。 2 要 求

《建设用地信息系统》报批管理子模块的E-R图如图2.22所示，逻辑数据模型如图2.23所示，物理数据模型如图2.24所示，数据表字段名定义见表2.10。请按以下设计完成数据库创建、数据表创建和数据操作任务：

审批时间地块编号项目名称审批单位项目ID用地报批基本信息图形类型地块面积从属地块基本信息地块用途地块编号地块名称报批时间报批单位报批面积图2.22 E-R图

图2.23 逻辑数据模型

图2.24物理数据模型

表2.10字段名定义表

字段名 Proj_id Proj_name Plot_id Appr_area Appr_date Appr_unit Appro_unit 字段说明 项目ID 项目名称 地块编号 报批面积 报批时间 报批单位 审批单位 字段名 Appro_date PlotInfo_id Plot_id Plot_name Total_area Purpose Shape_type 字段说明 审批时间 地块ID 地块编号 地块名称 地块面积 地块用途 图形类型 3 数据库操作

3.1 创建数据库

创建数据库NewDataSet。 3.2 数据表设计

根据图2.23和表2.10，创建数据表T_appr_area、T_plot_info。 3.3 数据表之间的关系设计

根据物理数据原型，创建数据关系表。 3.4 数据操作

用SQL语句查询出以下数据：

? 查询出地块编号为“10001”的建设用地的报批基本信息；

? 查询出地块名称为“长沙市天心花苑”的建设用地的报批基本信息； ? 查询出所有的地块基本信息并按地块面积升序排序； ? 删除地块面积大于“1100”的地块基本信息；

? 把地块名称为“长沙市天心花苑”修改为“株洲市天心花苑”；

? 在T_plot_info表中插入数据，(地块编号为“10006”，地块名称为“新型工业区”，

地块面积为“1500”，地块用途为“工业用地”，图形类型为“长方形”的信息； ? 创建名为Plot_id_view1的视图，视图的数据为地块编号是“10005”的建设用地报

批基本信息；

? 删除上题中所创建的Plot_id_view1视图。 【试题9】

1 任 务

你作为《学生成绩管理系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 相关数据的操作。 2 要 求

《学生成绩管理系统》的E-R图如图2.25所示，逻辑数据模型如图2.26所示，物理数据模型如图2.27所示, 数据表字段名定义见表2.11。请按要求完成数据库创建、数据表创建和数据操作任务：

姓 名学 号性 别入学年份出生日期学 分课程名称学 时学 生课程编号N选课N课 程成 绩 图2.25 E-R图

T_student_infoStudent_noStudent_nameSexBirthEnter_dateAddressCharactres(6)Charactres(20)Charactres(2)DateIntegerCharactres(50)T_course_infoCourse_noCourse_nameCreditClasshourCharacters(8)Characters(50)Short integerShort integerRelationship_1Relationship_2T_student_scoresCourse_noStudent_noOrdinary_scoresEnd_scoresTotal_scoresCharacters(8)Charactres(6)Decimal(7,2)Decimal(7,2)Decimal(7,2) 图2.26 逻辑数据模型

T_student_infoStudent_noStudent_nameSexBirthEnter_dateAddresschar(6)char(20)char(2)dateintegerchar(50)T_course_infoCourse_noCourse_nameCreditClasshourchar(8)char(50)intintFK_T_STUDEN_REFERENCE_T_COURSEFK_T_STUDEN_REFERENCE_T_STUDENT_student_scoresCourse_noStudent_noOrdinary_scoresEnd_scoresTotal_scoreschar(8)char(6)decimal(7,2)decimal(7,2)decimal(7,2) 图2.27物理数据原型 表2.11字段名定义表

字段名 Student_no Student_name Sex Birth Enter_date Address Course_no Course_name 学号 姓名 性别 出生年月 入学年份 家庭住址 课程编号 课程名 字段说明 Credit Classhour Student_no Course_no Ordinary_scores End_scores Total_scores 字段名 学分 学时 学号 课程编号 平时成绩 期末成绩 总评成绩 字段说明 3 数据库操作 3.1 创建数据库

创建数据库Student。 3.2 创建数据表

根据图2.26和表2.11，创建数据表T_student_info、T_course_info 、T_student_scores。

3.3 创建数据表间的关系

根据物理数据原型，创建数据关系。 3.4 数据操作

用SQL语句完成如下操作：

? 向T_student_infor表插入数据：“102011，刘德华，男，03/23/1991，2009，湖南长沙劳动东路168号”；

? 查询出1991年出生的学生信息；

? 查询选修了“数据结构”的学生姓名、平时成绩、期末成绩、总评成绩； ? 创建视图查询学生的学号、姓名、课程名、总评成绩；

? 统计每门课程的选课人数、最高分、平均分，并将统计结果保存在表T_total中。

【试题10】

1 任 务

你作为《网上商店》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2 要 求

《网上商店》的E-R图如图2.28 所示，逻辑数据模型如图2.29所示，物理数据模型如图2.30所示，数据表字段名定义见表2.12。请按以下设计完成数据库创建、数据表创建和数据操作任务：

商品价格商品名称供应商编号商品编号生产地商品编号商品订单编号订购数量总价M订购N订单供应商编号供应商名称M供应商N供应 图2.28 E-R图

T_goodsGood_idSupp_codeGoods_nameGoods_priceGoods_companyGoods_addressintegerVariable characters(20)Variable characters(50)Decimal(10,2)Variable characters(50)Variable characters(50)T_ordersRelationship_1Order_idGoods_idQuantityTotal_priceLong integerLong integerIntegerMoneyRelationship_2T_suppliersSupp_codeVariable characters(20)Supp_nameVariable characters(50) 图2.29逻辑数据模型

T_goodsGoods_idSupp_codeGoods_nameGoods_priceGoods_addressbigintvarchar(20)varchar(50)decimal(10,2)varchar(50)T_ordersOrder_idGoods_idQuantityTotal_pricebigintbigintintmoneyFK_T_ORDERS_REFERENCE_T_GOODSFK_T_GOODS_REFERENCE_T_SUPPLIT_suppliersSupp_codevarchar(20)Supp_namevarchar(50) 图2.30 物理数据模型 表2.12字段名定义表

字段名 Goods_id（标识列) Goods_Name Goods_price Supp_code Goods_adress Order_id Goods_id 商品编号 商品名称 商品价格 供应商编号 生产地 订单号码 商品编号 字段说明 Quantity Total_price Supp_code Supp_name 字段名 字段说明 订购数量 总价 供应商编号 供应商名称 表2.13 商品信息表(T_goods样本数据) Goods_id 1000 1001 1002 Goods_Name 盛唐笔记本 博士笔记本 惠普笔记本 Goods_price 5600 6700 7800 Supp_code 430102 540199 440708 Goods_adress 广东 台湾 广东 表2.14 订单信息表 (T_orders样本数据)

Order_id 11070232 11060343 11050322 1000 1002 1001 Goods_id 3 1 2 Quantity Total_price 16800 7800 13400 表2.15 供应商表(T_suppliers样本数据) Supp_code 430102 540199 440708 盛唐科技 博士科技 惠普科技 Supp_name 3 数据库操作 3.1 创建数据库 创建数据库Stores。 3.2 创建数据表

根据图2.29和表2.12，创建数据表T_goods、T_orders 、T_suppliers。 3.3 创建数据表间的关系

根据物理数据原型，创建数据表之间的关系。

3.4 数据操作

用SQL语句完成如下操作：

? 在3个表中分别插入表2.13-2.15中的样本数据； ? 将商品名为“惠普笔记本”的价格下调10%； ? 查询出商品编号为“1002”的总的订购数量；

? 查询出商品名称为“惠普笔记本”的商品的订购数量、总价；

? 创建存储过程P_stored_proc，指定供应商代码，查询该供应商的订单信息； ? 查询所有名称包含“科技”的供应商编号、供应商名称。 【试题11】

1 任 务

你作为《码头中心船货申报系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2 要 求

《危险品管理》子模块的E-R图如图2.31所示，逻辑数据模型如图2.32所示，物理数据模型如图2.33所示，数据表字段名定义见表2.16。请按以下设计完成数据库创建、数据表创建和数据操作任务：

经营单位ID数量发证地址收费单位地址收费单位编号收费单位M收费M发证部门拥有经营单位简称M经营单位编号经营单位地址是否有效证书版本危险品经营证证书ID有效日期经营地址收费单位名称收费单位电话经营单位类型经营单位N发证日期 图2.31 E-R图

（收费单位）T_fee_unit收费单位IDCharacters (8)收费单位名称Variable characters (64)收费单位电话号码Variable characters (20)收费单位地址Variable characters (50)PK_T_fee_unitRelationship_1（经营单位表）T_business_unit经营单位IDCharacters (8)经营单位简称Variable characters (32)经营单位地址Variable characters (64)经营单位类型Variable characters (50)PK_T_business_unitRelationship_2（危险品经营证表）T_danger_cargo_license证书IDCharacters (30)发证地址Variable characters (50)数量Integer经营单位IDCharacters (8)经营地址Variable characters (60)有效日期Date & Time发证部门Variable characters (50)发证日期Date & Time证书版本Long integer是否有效Characters (2)PK_T_danger_cargo_license 图2.32 逻辑数据模型

T_business_unit经营单位IDchar(8)经营单位简称varchar(32)经营单位地址varchar(64)经营单位类型varchar(50)FK_T_relation_businessunit_feeunit_T_business_unitFK_T_danger_cargo_license_T_business_unitT_relation_businessunit_feeunit经营单位IDchar(8)收费单位IDchar(8)FK_T_relation_businessunit_feeunit_T_fee_unitT_fee_unit收费单位IDchar(8)收费单位名称varchar(64)收费单位电话号码varchar(20)收费单位地址varchar(50)T_danger_cargo_license证书IDchar(30)发证地址varchar(50)数量int经营单位IDchar(8)经营地址varchar(60)有效日期datetime发证部门varchar(50)发证日期datetime证书版本bigint是否有效char(2) 图2.33物理数据模型

表2.16 字段名定义表

字段名 Businessunit_code Short_name Address Type Feeunit_id Feeunit_name Feeunit_phone Feeunit_address License_id 字段说明 经营单位ID 经营单位简称 经营单位地址 经营单位类型 收费单位ID 收费单位名称 收费单位电话号码 收费单位地址 证书ID 字段名 Location Number Address Effective_date Licens_department License_date Version Is_active 字段说明 发证地址 数量 经营地址 有效日期 发证部门 发证日期 证书版本 是否有效 3 数据库操作 3.1 创建数据库

创建数据库HarborBereau1。 3.2 创建数据表

根据图2.32和表2.16，创建数据表T_fee_unit、T_danger_cargo_license、T_business_unit。

3.3 创建数据表间的关系

根据物理数据原型，创建数据表之间的关系。 3.4 约束设计

? 经营单位类型只允许有：国营经济、私营经济、个体经济、集体经济、股份制经济

五种；

? 是否有效字段默认为：否。 3.5 数据操作

用SQL语句完成如下操作：

? 在T_business_unit表插入数据：“J2009302，振华港机，上海浦东，集体经济”； ? 查询简称为“振华港机”的经营单位所对应的收费单位名称和收费单位电话； ? 创建存储过程，查询经营类型为“集体经济”的经营单位由哪些收费单位收费，查询这些收费单位的名称、电话号码和地址。 【试题12】

1 任 务

你作为《码头中心船货申报系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2 要 求

《进出港船只申报》子模块的E-R图如图2.34 所示，逻辑数据模型如图2.35 所示，物理数据模型如图2.36所示，数据表字段名定义见表2.17。请按以下设计完成数据库创建、

数据表创建和数据操作任务：

船舶国籍建造年月适航航区船名核定载客航线代码是否引用船名船舶代码船舶航线代码航线航线名称申报人航线类别拥有经营单位地址经营单位编号N经营单位类型经营单位小型车车辆数小型车车辆数内含集箱车车辆数集箱数中型车车辆数申报时间特大型车车辆数大型车车辆数N拥有N备注拥有MMM车辆申报表进出港时间进出港方式最后更新时间申报编号航次代码经营单位编码船舶代码经营单位简称 图2.34 E-R图

T_business_unit(经营单位表)Business_unit_codeCharacters (8)Short_nameVariable characters (32)AddressVariable characters (64)Business_unit_typeVariable characters (50)PK_T_business_unit...FK_T_vehicle_declare_T_business_unitLine_codeLine_nameLine_typePK_T_lineT_line（航线表）Characters (8)Variable characters (64)Variable characters (10)FK_T_vehicle_declare_T_lineT_vehicle_declare（车辆申报表）Declare_noCharacters (8)Voyage_codeCharacters (10)Inout_portVariable characters (8)Inout_port_dateDate & TimeOutsize_vehicle_qtyIntegerLarge_vehicle_qtyIntegerMedium_vehicle_qtyIntegerSmall_vehicle_qtyIntegerEmbed_vehicle_qtyIntegerContainer_qtyIntegerDeclarerVariable characters (16)Declare_dateDate & TimeLast_update_dateDate & TimeRemarkVariable characters (64)PK_T_vehicle_declareFK_T_vehicle_declare_T_shipT_ship（船舶表）Ship_codeCharacters (8)Ship_nameVariable characters (40)Ship_nationalityVariable characters (32)Checked_capacityIntegerBuilt_dateDate & TimeSuited_areaVariable characters (20)Is_newCharacters (2)PK_T_ship 图2.35逻辑数据模型

T_business_unitBusiness_unit_codeShort_nameAddressBusiness_unit_typechar(8)varchar(32)varchar(64)varchar(50)FK_T_vehicle_declare_T_business_unitT_vehicle_declareDeclare_noBusiness_unit_codeLine_codeShip_codeVoyage_codeInout_portInout_port_dateOutsize_vehicle_qtyLarge_vehicle_qtyMedium_vehicle_qtySmall_vehicle_qtyEmbed_vehicle_qtyContainer_qtyDeclarerDeclare_dateLast_update_dateRemarkchar(8)char(8)char(8)char(8)char(10)varchar(8)datetimeintintintintintintvarchar(16)datetimedatetimevarchar(64)FK_T_vehicle_declare_T_shipFK_T_vehicle_declare_T_lineT_shipShip_codeShip_nameShip_nationalityChecked_capacityBuilt_dateSuited_areaIs_newchar(8)varchar(40)varchar(32)intdatetimevarchar(20)char(2)T_lineLine_codechar(8)Line_namevarchar(64)Line_typevarchar(10) 图2.36物理数据模型 表2.17 字段名定义表

字段名 Business_unit_code Short_name Address Business_unit_type Ship_code Ship_name Ship_nationality Checked_capacity Built_date Suited_area Is_new Line_code Line_name Line_type 字段说明 经营单位编码 经营单位简称 经营单位地址 经营单位类型 船舶代码 船名 船舶国籍 核定载客 建造年月 适航航区 是否引用船名 航线代码 航线名称 航线类别 字段名 Declare_no Voyage_code Inout_port Inout_port_date Outsize_vehicle_qty Large_vehicle_qty Medium_vehicle_qty Small_vehicle_qty Embed_vehicle_qty Container_qty Declarer Declare_date Last_update_date Remark 字段说明 申报编号 航次代码 进出港方式 进出港时间 特大型车车辆数 大型车车辆数 中型车车辆数 小型车车辆数 内含集箱车车辆数 集箱数 申报人 申报时间 最后更新时间 备注 3 数据库操作 3.1 创建数据库

创建数据库HarborBereau2。 3.2 创建数据表

根据图2.35和表2.17，创建数据表T_line、T_ship 、T_vehicle_declare、T_business_unit。

3.3 创建数据表间的关系

根据物理数据原型，创建数据表关系。 3.4 约束设计

? 进出港方式只允许有：进港和出港两种；

? 经营单位类型只允许有：国营经济、私营经济、个体经济、集体经济、股份制经

济五种；

? 是否引用船名字段只允许有：是和否两个值，默认为：否； ? 申报时间默认为：系统当前时间；

? 核定载客默认为：０，其范围在0到5000之间。 3.5 数据操作

查询所有船舶国籍为“巴拿马”的进出港车辆申报信息，包含船名、进出港方式、进出港时间及申报人字段。 【试题13】

1任 务

你作为《图书管理信息系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 相数据的操作。 2要 求

《图书管理信息系统》中借书管理子模块的E-R图如图2.37所示，逻辑数据模型如图2.38 所示，物理数据模型如图2.39所示，数据表字段名定义见表2.18。请按以下设计完成数据库创建、数据表创建和数据操作任务：

图2.37 E-R图

图2.38逻辑数据模型

图2.39物理数据原型 表2.18 字段名定义表

字段名 Book_no Book_name Author Price Qty Loan_qty Card_no 书号 书名 作者 单价 库存册数 借阅次数 卡号 字段说明 字段名 Card_name Adress Mobile Borrow_id Borrow_date Return_date 字段说明 借书人姓名 借书人地址 手机号码 借书记录编号 借书日期 还书日期

3 数据库操作 3.1 创建数据库

创建数据库BookDB。 3.2创建数据表

根据图2.38和表2.18，创建数据表T_card、T_book、T_borrow。

3.3 创建数据表间的关系

根据物理数据原型，创建数据关系。 3.4 数据操作

用SQL语句完成如下操作：

? 在T_book表中插入数据：“9787302245339，Access数据库技术与应用，陈世红，27.20，50”；

? 查询出日期为2010-10-31以后借出的图书信息； ? 查询出没有还书的借书人姓名；

? 查询出库存数量小于5册的图书信息；

? 编写视图查询借书人的姓名，手机号码和地址； ? 编写存储过程，查询指定图书名称的借阅次数。

【试题14】

1任 务

你作为《学生管理信息系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2要 求

《学生管理信息系统》中成绩管理子模块的E-R图如图2.40所示，逻辑数据模型如图2.41所示，物理数据模型如图2.42所示，数据表字段名定义见表2.19。请按以下设计完成数据库创建、数据表创建和数据操作任务：

出生日期性别籍贯姓名备注学号学生信息成绩编号学号成绩课程编号入学日期手机号码M课程名称课程编号教师姓名拥有N成绩M课程信息N拥有 图2.40 E-R图

学生表学号姓名性别出生日期入学日期手机号码籍贯备注varchar(10)varchar(20)char(2)datetimedatetimevarchar(11)varchar(30)text课程表课程号varchar(10)课程名varchar(100)教师姓名varchar(30)Relationship_1Relationship_2成绩表成绩编号bigint成绩decimal(5,2) 图2.41 逻辑数据模型

学生表学号姓名性别出生日期入学日期手机号码籍贯备注varchar(10)varchar(20)char(2)datetimedatetimevarchar(11)varchar(30)text课程表课程号varchar(10)课程名varchar(100)教师姓名varchar(30)FK_T_SCORE_REFERENCE_T_COURSEFK_T_SCORE_REFERENCE_T_STUDEN成绩表成绩编号课程号学号成绩bigintvarchar(10)varchar(10)decimal(5,2) 图2.42物理数据原型 表2.19 字段名定义表

字段名 Stud_id Stud_name Stud_sex Birth_date Entry_Date Mobile Birth_place 学号 姓名 性别 出生日期 入学日期 手机号码 籍贯 字段说明 字段名 Reserve Course_id Course_name Teacher_name Score_id Score 备注 课程编号 课程名称 教师姓名 成绩编号 成绩 字段说明 3数据库操作 3.1 创建数据库

创建数据库StudentDB。 3.2 创建数据表

根据图2.41和表2.19，创建数据表T_student、T_course、T_score。 3.3 创建数据表间的关系

根据物理数据原型，创建数据关系。 3.4 数据操作

用SQL语句完成如下操作：

? 在T_student表插入数据：“2009010001，张学友，男，1980-10-4，2009-9-1，15012345678，湖南长沙”，无；

? 查询出籍贯为“湖南长沙”的学生基本信息； ? 查询出姓名为“张学友”的学生所有课程的成绩； ? 查询出“数据库应用”这门课的平均成绩； ? 创建视图查询学生的姓名，手机号码和籍贯； ? 创建存储过程，查询指定课程名称的最高成绩。 【试题15】

1任 务

你作为《起航自学考试网》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2要 求

《起航自学考试网》的E-R图如图2.43 所示，逻辑数据模型如图2.44 所示，物理数据模型如图2.45所示，数据表字段名定义见表2.20。请按以下设计完成数据库创建、数据表创建和数据操作任务：

图2.43 E-R图

图2.51 物理数据原型 表2.28 字段名定义表

字段名 Book_id Book_name Book_isbn Book_author Book_publisher Book_price Book_rkm Order_id Order_datetime Order_status Supplier_id 字段说明 教材id 教材名称 教材ISBN编号 作者 出版社 价格 备注 订单id 订购时间 订单状态 供应商id 字段名 Supplier_name Supplier_people Supplier_address Supplier_phone Supplier_postcode Supplier_remark OrderDet_id OrderDet_status Book_datetime OrderDet_num 字段说明 供应商名称 联系人 联系地址 联系电话 邮编 备注 订单详情id 订单详情状态 出版时间 数量 3数据库操作 3.1 创建数据库

创建数据库HNIUEAM。 3.2创建数据表

根据图2.50和表2.28，创建数据表T_Supplier、T_BookInfo、T_Order。 3.3创建数据表间的关系

根据物理数据原型，创建数据关系。 3.4数据操作

? 向T_BookInfo表插入数据：“BC0001, windows程序设计,0257-9413, 刘立,电子工

业出版社代理商,2010-11-10,42,无”；

? 查询出供应商名称为“电子工业出版社代理商”的订单编号及订单状态； ? 查询教材名称为“windows程序设计”的订购日期；

? 创建视图查询供应商名为“电子工业出版社代理商”所订购的教材的详细信息(包

括名称，ISBN编号，作者，出版社，出版时间，价格，数量)；

? 创建存储过程，当订单详情表中相应订单的状态为“1”时,修改订单表的订单状态

为“1”。 【试题18】 1任 务

你作为《教务管理系统》项目开发组的程序员，请按要求完成： ? 数据库的创建； ? 数据表的创建； ? 数据的操作。 2要 求

《学生选课》子模块的E-R图如图2.52 所示，逻辑数据模型如图2.53 所示，物理数据模型如图2.54所示，数据表字段名定义见表2.29。请按以下设计完成数据库创建、数据表创建和数据操作任务：

图2.52 E-R图

图2.53 逻辑数据模型

图2.54物理数据原型 表2.29 字段名定义表

字段名 Student_id Student_name Student_sex Student_age Course_id Course_name Course_classhour Course_creditpoints Teacher_course_ evaluation 字段说明 学生学号 学生姓名 学生性别 学生年龄 课程编号 课程名称 课时 学分 评价 字段名 Teacher_id Teacher_name Teacher_professional Teacher_age Teacher _sex Student_course_hour Student_course_score Teacher_course_hour Teacher_course_place 字段说明 教师编号 教师姓名 教师职称 教师年龄 教师性别 选课时间 成绩 授课时间 授课地点 3数据库操作 3.1 创建数据库

创建数据库HNIUEAM。

3.2 创建数据表

根据图2.53和表2.29，创建数据表T_Student、T_Course、T_Teacher。

3.3 创建数据表间的关系

根据物理数据原型，创建数据关系。 3.4 数据操作

? 在T_Course表中插入数据：“KC10001，数据结构，72，4”； ? 查询选修了“数据结构”的学生信息；

? 查询教师名称为“王强”的教师所授课程情况；

? 计算所选课程的时间为“2010-9-1”，课程名称为“数据结构”的所有学生的总成绩；

? 创建视图查询授课教师“李超”所授课的学生情况； 创建存储过程，统计授课教师“李超”所授课程的优秀率，规定成绩大于等于90的为优秀。

本文来源：https://www.bwwdw.com/article/10sx.html