数据库原理考试习题及答案

数据库原理复习 设计题一有1个B2C电子购物网站 其业务规则如下 购物网站公司负责维护商品分类信息 category 管理商品 goods 目录 每个商品都需要归属到某个商品分类下 网民可以注册成为网站买家 buyer 买家可以在购物网站通过商品名 商品类名搜索自己需要的商品 把选中的一定数量商品放入自己的购物车 trolley 中 选购完成后下订单 orders 订单需要在明细 detail 中记录商品数量 交易价格 下单时需要填写详细的收货人信息 然后通过第三方网银支付系统付款 付款成功后网银支付系统会在订单中标注付款成功标志 此标志的值分别为0 等待付款 1 等待发货 2 等待收货 3 关闭 公司看到买家付款成功后负责物流快递商品 买家收到商品后确定收货 暂不考虑退 换货业务 商品分类实体的属性包括分类号 cno 类名 cname 商品实体的属性包括商品号 gno 商品名 gname 单位 unit 商品详情 detail 价格 price 买家实体的属性包括用户代码 bno 姓名 bname 电话 bphone 身份证号 id card 订单实体的属性包括订单号 ono 时间 odate 状态 status 收货人 consignee 总金额 money 1 概念模型设计 ER图 1 进行数据库概念模型设计 补充完善图1所示的ER模型 要求同时标出联系的连通词 10分 2 将概念模型转化成关系模型 逻辑设计 2 将概念模型转化成关系模型 标志出主 外键 判断每个关系模式的范式并说明理由 5分 2 将概念模型转化成关系模型 逻辑设计 Category cno cname BC范式Buyer bno bname bphone id card 2范式 因为id card的问题而存在传递依赖 Goods gno gname unit detail price cno BC范式Trolley tno bno gno counts BC范式Orders ono odate status consignee money bno BC范式Detail dno gno ono counts price BC范式 3 DDL 数据定义语言 3 利用SQL语句完成数据库模型中其它表格的创建 要求创建相应的索引 另外需要修改buyer表结构 为此表添加1个email字段 数据类型为varchar 32 15分 createtablecategory cnochar 4 primarykey cnamevarchar 80 go createindexidx category nameoncategory cno go createtablegoods gnochar 10 primarykey gnamevarchar 80 unitvarchar 32 detailvarchar 1024 pricenumeric 8 1 default0 cnochar 4 foreignkeyreferencescategory cno onupdatecascade go createindexidx goods nameongoods gname go createtablebuyer bnovarchar 16 primarykey bnamevarchar 32 bphonevarchar 16 id cardvarchar 18 go createtableorders onovarchar 16 primarykey odatesmalldatetimedefaultgetdate consigneevarchar 256 statuschar 1 default 0 moneynumeric 8 1 default0 bnovarchar 16 foreignkeyreferencesbuyer bno onupdatecascade go createindexidx orders bnoonorders bno go 3 DDL 数据定义语言 3 利用SQL语句完成数据库模型中其它表格的创建 要求创建相应的索引 另外需要修改buyer表结构 为此表添加1个email字段 数据类型为varchar 32 15分 createtabletrolley tnointidentityprimarykey bnovarchar 16 foreignkeyreferencesbuyer bno onupdatecascade gnochar 10 foreignkeyreferencesgoods gno onupdatecascade countsintdefault1 gocreateindexidx trolley bnoontrolley bno createindexidx trolley gnoontrolley gno go 3 DDL 数据定义语言 3 利用SQL语句完成数据库模型中其它表格的创建 要求创建相应的索引 另外需要修改buyer表结构 为此表添加1个email字段 数据类型为varchar 32 15分 createtabledetail dnointidentityprimarykey onovarchar 16 foreignkeyreferencesorders ono onupdatecascade gnochar 10 foreignkeyreferencesgoods gno onupdatecascade countsintdefault1 pricenumeric 8 1 gocreateindexidx detail onoondetail ono createindexidx detail gnoondetail gno go 4 DML 4 请用SQL语句完成以下数据更新操作 20分 1 往商品分类表中插入记录 c001 食品 2 往商品表中插入记录 添加1个商品信息 商品类别 食品 编号 g001 商品名 巧克力 单位 块 详情 榛子 牛奶味 价格 20元 3 进行商品调价操作 将所有食品类商品价格打9折 4 清空买家用户代码为考生学号的购物车 4 DML 4 请用SQL语句完成以下数据更新操作 20分 1 往商品分类表中插入记录 c001 食品 insertcategory cno cname values c001 食品 2 往商品表中插入记录 添加1个商品信息 商品类别 食品 编号 g001 商品名 巧克力 单位 块 详情 榛子 牛奶味 价格 20元 insertintogoods gno gname unit detail price cno values g001 巧克力 块 榛子 牛奶味 20 c001 4 DML 4 请用SQL语句完成以下数据更新操作 20分 3 进行商品调价操作 将所有食品类商品价格打9折 updategoodssetprice price 0 9wherecno c001 4 清空买家用户代码为考生学号的购物车deletetrolleywherebno 004062 5 DML和关系代数表达式 5 完成下列查询要求 写出对应的SQL语句及关系代数表达式 20分 1 查找商品名为 巧克力 的商品信息 包括商品名 单位 价格 2 查找类别为 食品 的所有商品清单 包括商品名 单位 价格 3 查找用户代码为 Berry 的买家所有已购商品清单 包括商品名 价格 数量 4 查找购买过商品名为 手机 或 笔记本电脑 的买家信息 包括买家号 姓名 购买数量 本小题不用写关系代数 5 DML和关系代数表达式 5 完成下列查询要求 写出对应的SQL语句及关系代数表达式 20分 1 查找商品名为 巧克力 的商品信息 包括商品名 单位 价格 selectgname unit pricefromgoodswheregname 巧克力 gname unit price gname 巧克力 goods 5 DML和关系代数表达式 5 完成下列查询要求 写出对应的SQL语句及关系代数表达式 20分 2 查找类别为 食品 的所有商品清单 包括商品名 单位 价格 selectgname unit pricefromgoodsg o ame 食品 gname unit price o o cname 食品 goods category 5 DML和关系代数表达式 5 完成下列查询要求 写出对应的SQL语句及关系代数表达式 20分 3 查找用户代码为 Berry 的买家所有已购商品清单 包括商品名 价格 数量 selectg gname d counts d pricefromgoodsg buyerb orderso detaildwhereb bno o bnoando ono d onoandd gno g gnoandb bname Berry gname counts price b bno o bno o ono d ono d gno g gno b bname Berry goods buyer orders detail 5 DML和关系代数表达式 5 完成下列查询要求 写出对应的SQL语句及关系代数表达式 20分 4 查找购买过商品名为 手机 或 笔记本电脑 的买家信息 包括买家号 姓名 购买数量 本小题不用写关系代数 selectb bno b bnamefrombuyerbwhereb bnoin selecto bnofromorderso detaild goodsgwhereo ono d onoandg gno d gnoand g gname 巧克力 org gname 笔记本电脑 6 view 6 编写1个视图 按商品类别分类统计出所有商品2014年之前的销售数量 销售额 视图列集包括类别名 销售数量 销售金额 10分 6 view 按商品类别分类统计出所有商品2014年之前的销售数量 销售额 视图列集包括类别名 销售数量 销售金额 createviewv sales sum2 cname count c sum c asSame count o sum detail counts detail price Fromcategory goods detail ordersWo oandgoods gno detail gnoanddetail ono orders onoandyear orders odate 2014 o ame 7 procedure 7 编写1个存贮过程 完成往指定订单中添加商品的操作业务 输入参数包括订单编号 商品编号 购买数量 需要检查订单编号 商品编号是否存在 10分 7 procedure 7 编写1个存贮过程 完成往指定订单中添加商品的操作业务 输入参数包括订单编号 商品编号 购买数量 需要检查订单编号 商品编号是否存在 10分 createprocedureprg ins detail onovarchar 16 gnochar 10 countsintasdeclare pricenumeric 6 1 ifnotexists selectonofromorderswhereono ono return6001 ifnotexists selectgnofromgoodswheregno gno return6002 select price pricefromgoodswheregno gno insertintodetail ono gno counts price values ono gno counts price goexecprg ins detail 20141215194302 g024 5 8 trigger 8 编写1个触发器 完成订单表中总金额的自动累计功能 即通过订单明细中商品的价格与数量自动统计订单中的总金额 10分 8 trigger 8 编写1个触发器 完成订单表