《dqz数据库原理》由会员分享，可在线阅读，更多相关《dqz数据库原理（11页珍藏版）》请在金锄头文库上搜索。
1、.一、DB设计方法：ER法、分解法例：设计一个连锁店订单管理系统，在该数据库中，每个商店包括商店store_id，商店的 号码pone，库位数no_bins，所在城市city，每个商店还存有其所存储货物items的item_id，名称desc，尺寸size，重量weight，存储的数量qty_held，商店向供货商supplier订货时的定购日期order_date，定购的货物及数量qty_ordered设：1、一个城市不能有一个以上的商店，每商店位于且仅能位于一个城市，每商店不能有一个以上的 号码，但可以无 号码。2、每货物有且仅有一个货物，存储的货物必须名称、尺寸，店可以无数量信息。3、存
2、储的货物必须大于04、订货时定购的各货物数量必须大于0，一份订单不能有一个以上的供货商，但必须有一个供货商以及订货日期，一份订单只能出现货物的货物。问：1、直接设计分解到3NF的DB2、用SQL-98的DDL描述上述的语义的假设。解：1、直接设计分解到3NF的DB【作题目时可以直接给出设计的各个表构造，不需要写明下面的具体过程】第一步：先根据题目画出ER图草图：据题意可知：有三个实体：商店stores ，货物 itmes，供货商 supplier供货商 supplier供货商名字 supplier商店stores商店store_id商店 号码 pone库位数 no_bins所在城市city货物
3、 items货物item_id货物名称desc货物尺寸 size货物重量 weight还有三个联系：商店与货物联系：存货 hold，是个1:n 的联系存货 hold存货数量 qty_held货物 items商店 stores1n商店与供货商联系：订单 orders 订单 hold订单order_no商店 stores供货商 suppliernm订单日期order_date所订货物item_id所订货物数量qty_ordered第二步： 据ER草图分析处理、修改重构得到根本ER图：分析可知：1、实体：商店 stores 中，存在以下函数依赖：商店 store_id商店 号码 pone商店 sto
4、re_id库位数 no_bins商店 store_id所在城市 city所以：此实体的主码 primary key 必定是：商店 store_id所以：没有局部函数依赖或传递依赖，满足3NF要求，所以：商店 store 实体不需要修改。2、实体：供货商 supplier 中只有一个属性，所以可以把它变成为联系：订单 hold 的一个属性。所以取消实体：商店3、实体：货物 items 中，存在以下函数依赖：货物 item_id货物名称 desc货物 item_id货物尺寸 size货物 item_id货物重量 weight所以：此实体的主码 primary key 必定是：货物 item_id所
5、以：没有局部函数依赖或传递依赖，满足3NF要求，所以：货物 items实体不需要修改。4、联系：订单 hold 中，存在以下函数依赖：订单 order_no订单日期order_date订单 order_no商店store_id订单 order_no供货商 supplier订单 order_no所订货物 item_id订单 order_no，所订货物 item_id所订货物数量qty_ordered发现：由于在联系：订单 hold 中，既存在由订单 order_no 单独数据决定*属性的情况，又存在由订单 order_no，所订货物 item_id 共同数据决定*属性的情况。所以：此实体的主码
6、primary key 必定只能是：订单 order_no，所订货物 item_id然而：决定了订单 order_no，所订货物 item_id为主码，则又有以下关系：订单 order_no，所订货物 item_id订单日期order_date订单 order_no，所订货物 item_id商店store_id订单 order_no，所订货物 item_id供货商 supplier于是，此关系中存在局部函数依赖！因为存在：订单 order_no订单日期order_date，并且又存在：订单 order_no，所订货物 item_id订单日期order_date，所以实际上有订单日期order_
7、date局部函数依赖于订单 order_no，所订货物 item_id因此：此模式只能到达1NF，不能到达题目要求的3NF所以：必须对联系：订单 hold 进展投影分解。投影分解为以下两个关系：订单 hold订单 order_no，订单日期order_date，商店store_id，供货商 supplier订单明细订单 order_no，所订货物 item_id，所订货物数量qty_ordered这样，两个关系中均不再有局部函数依赖或传递函数依赖，所以满足3NF所以：【注意：订单之类的一般都必须包括两个表，一个是订单表，表示订单的情况概述；另一个是订单明细表，表示订单中具体所订的货物及数量等。
8、】所以，新产生的根本ER图为：两个实体：商店 stores 和货物 items 以及三个联系：商店与货物的联系：存货 hold，商店与供货商联系：订单 orders，订单与货物的联系：item_order【也可认为商店、货物、订单都是实体】【注意：商店、货物、订单都是名词，所以可以是实体而不是联系。】第三步：据以上分析，把根本ER图向数据模型转换：根据转换方法：每个实体用一个表表达，每个联系用一个表表达。所以本小题的答案为：建立以下五个表：stores(stored_id,phone,no_bins,city)items(item_id,desc,size,weight)hold(store_
9、id,item_id,qty_held)orders(order_no,order_date,supplier)item_order(order_no,item_id,qty_ordered)2、用SQL-98的DDL描述题目中规定的语义的假设。1、用SQL建立商店 stores表据商店 stores表中的函数依赖，以及题目假设1，知道：商店 store_id不能为空且为主码，商店所在城市 city不能为空且必须唯一， 号码 pone可为空即无约束，因为货位数no_bins 没有指明约束条件，所以用默认即可以为空，即stores (stored_id, phone, no_bins, city
10、 )约束： Not null 无约束无约束 Not nullPrimary key Unique所以SQL语句如下：create table stores( store_id char(8) not null primary key, pone char(15), no_bins number(3), city char(20) not null unique);【注意：作为主码的属性列/字段一定同时也是不可为空的，所以对单独一个属性列/字段作主码时的约束条件必须连写为： not null primary key】【注意：作为取值唯一的属性列/字段一定同时也是不可为空的，所以对单独一个属性列/
11、字段取值唯一时的约束条件必须连写为： not null unique 实际上取值唯一的属性列/字段也就代表了它必定是一个候选索引。】【注意：不写 not null时则约束条件默认为可以为空，所以题目中说可以为空则不需要特别指明其约束条件】2、用SQL建立货物 items表据货物 items表中的函数依赖，以及题目假设2，知道：知道货物 items表中货物item_id不能为空且为主码，货物名称的desc不能为空，货物尺寸size不能为空，货物重量无约束，即：items (item_id, desc, size, wieght )约束： Not null Not nullNot null 无约束
12、Primary key所以SQL语句如下：create table itmes( item_id char(13) not null primary key,desc char(20) not null,size char(20) not null,weight number(8);3、用SQL建立存货 hold表据存货 hold表中的函数依赖，以及题目假设3，知道：知道商店 store_id 是引用于storesstore_id且不能为空，货物item_id是引用于itemssitem_id且不能为空，存货数量qty_held可以为空但必须大于0，整个hold表的主码为store_id，it
13、em_id即items (store_id, item_id, qty_held )约束： Not null Not null 0Reference stores(store_id) Reference items(item_id)Primary key所以SQL语句如下：create table hold( store_idchar(8) not null references stores(store_id),item_idchar(13) not null references items(item_id),qty_heldnumber(8) check(qty_held0),prima
14、ry key(stroe_id,item_id);【注意：作为主码的属性列/字段一定同时也是不可为空的，所以对多个属性列/字段作主码时，在主码的各属性列/字段上必须写有约束条件： not null ，而在表定义的最后必须用 primary key主码的属性列/字段的集合 语句来定义表的主码，以保证其实体完整性】【注意：虽然题目中没有明确引用关系，但是在建立表的时候，特别是建立表示联系的表的时候，必须自己指出其中在其他表中已经出现过的属性列/字段是从什么表的什么字段引用过来的，以保证其参照完整性】【注意：用到了引用关系的属性列/字段的数据类型定义及长度必须与被引用对象一样】4、用SQL建立订单 orders表据订单 orders表中的函数依赖，以及题目假设4，知道：知道订单 orders表中订单order_no不能为空且为主码，订货日期order_date不能为空，供货商supplier不能为空且为唯一题目中要求一份订单不能有一个以上的供应商但必须有一个供应商，即：orders (order_no, order_date, supplier)约束： N
