dqz数据库原理

一、DB设计方法：ER法、分解法例：设计一个连锁店订单管理系统，在该数据库中，每个商店包括商店编号（store_id），商店的电话号码（pone），库位数（no_bins），所在城市（city），每个商店还存有其所存储货物（items）的编号（item_id），名称（desc），尺寸（size），重量（weight），存储的数量（qty_held），商店向供货商（supplier）订货时的定购日期（order_date），定购的货物编号及数量（qty_ordered）设：1、一个城市不能有一个以上的商店，每商店位于且仅能位于一个城市，每商店不能有一个以上的电话号码，但可以无电话号码。2、每货物有且仅有一个货物编号，存储的货物必须已知名称、尺寸，店可以无数量信息。3、存储的货物必须大于04、订货时定购的各货物数量必须大于0，一份订单不能有一个以上的供货商，但必须有一个供货商以及订货日期，一份订单只能出现已知货物编号的货物。问：1、直接设计分解到3NF的DB2、用SQL-98的DDL描述上述的语义的假设。解：1、直接设计分解到3NF的DB【作题目时可以直接给出设计的各个表结构，不需要写明下面的具体过程】第一步：先根据题目画出ER图草图：据题意可知：有三个实体：商店stores ，货物 itmes，供货商 supplier供货商 supplier供货商名字 supplier商店stores商店编号store_id商店电话号码 pone库位数 no_bins所在城市city货物 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商店编号 store_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所以：没有部分函数依赖或传递依赖，满足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） 共同数据决定某属性的情况。所以：此实体的主码 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_date部分函数依赖于（订单编号 order_no，所订货物编号 item_id）因此：此模式只能达到1NF，不能达到题目要求的3NF所以：必须对联系：订单 hold 进行投影分解。投影分解为以下两个关系：订单 hold（订单编号 order_no，订单日期order_date，商店编号store_id，供货商 supplier）订单明细（订单编号 order_no，所订货物编号 item_id，所订货物数量qty_ordered）这样，两个关系中均不再有部分函数依赖或传递函数依赖，所以满足3NF所以：【注意：订单之类的一般都必须包括两个表，一个是订单表，表示订单的情况概述；另一个是订单明细表，表示订单中具体所订的货物及数量等。】所以，新产生的基本ER图为：两个实体：商店 stores 和货物 items 以及三个联系：商店与货物的联系：存货 hold，商店与供货商联系：订单 orders，订单与货物的联系：item_order【也可认为商店、货物、订单都是实体】【注意：商店、货物、订单都是名词，所以可以是实体而不是联系。】第三步：据以上分析，把基本ER图向数据模型转换：根据转换方法：每个实体用一个表表达，每个联系用一个表表达。所以本小题的答案为：建立以下五个表：stores(stored_id,phone,no_bins,city)items(item_id,desc,size,weight)hold(store_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 )约束： 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】【注意：作为取值唯一的属性列/字段一定同时也是不可为空的，所以对单独一个属性列/字段取值唯一时的约束条件必须连写为： 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 无约束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 是引用于stores（store_id）且不能为空，货物编号item_id是引用于itemss（item_id）且不能为空，存货数量qty_held可以为空但必须大于0，整个hold表的主码为（store_id，item_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),primary 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)约束： Not null Not nullNot nullPrimary keyunique所以SQL语句如下：create table orders( order_no char(8) not null primary key,order_date date not null,supplier char(20) not null unique);（5）、用SQL建立订单明细表 item_order表据订明细表item_order表中的函数依赖，以及题目假设4，知道：知道订明细表item_order表中订单编号order_no是引用于oders（order_id）且不能为空，货物编号item_id是引用于items（item_id）且不能为空，订货数量qty_ordered必须大于0，整个item_order表的主码为（order_no，item_id）即items_order (order_id, item_id, qty_ordered )约束： Not null Not null 0Reference orders(order_id) Reference items(item_id)Primary key所以SQL语句如下：create table items_order( order_id char(8) not null references orders(order_id),item_id char(13) not null references items(item_id),qty_orderednumber(8) check(qty_ordered0),primary key(order_id,item_id);【注意写SQL时，写完一条SQL语句必须以一个分号; 来结束此语句】二、SQL：【主要考察：建表，查询（选择、投影、连接），插入，删除，修改。不考修改表、建立索引、视图，删除表、索引、实体，授权和收回授权】1、查位于Boston的商店的号码（store_id）：select store_id from storeswhere city=Boston ;2、查重量大于10的各种货物的货号（item_id）：select item_id fromitemswhere weight10 ;3、查某供货商P&G的所有供货的订货日期select date fromorderswhere supplier=P&G ;4、查各有货物名称（desc）为window的商店的位置（city）：select A.cityfrom stores A, items B, item_order Cwhere A.store_id=C.store_idand C.item_id=B.item_id and B.desc=window ;5、查备有P&G所供货物的商店号（store_id）select hold.store_id from hold, orders, item_orderwhere hold.item_id=item_order.item_idand item_order.order_no=orders.order_noand orders.supplier=P&G ;或使用 in 短语来实现：selecthold.store_id from holdwhere hold.item_id in(select item_oder.item_id from item_oderwhere item_oder.order_no in (select orders.order_no from orders) ;6、查备有所有货物的商店号（store_id）selectA.store_id fromstoresAwherenot exists( select * from itmes where itmes.item_idno in( select hold.item_idfromhold) ;7、查每个商店备有的各种货物的总数量selectstore_id, sum(qty_held)from holdgroupby store_id ;8、删除订单ord1及相关订购货物delete fromitem_orderwhere order_no=ord1 ;delete fromorderswhere order_no=ord1 ;9、将所有货物重量在原基础上提高三倍update itemsset weight=weight*3 ;如将商店编号（store_id）为km1的商店的货物名称（desc）为cap的货物数量增加20，则用下面带有 where 子句的 update 语句：update holdset qty_held=qty_held+20where store_id=km1 and item_idin( select item_idfromitemswhere desc=cap ) ;【注意此处不能用 item_id（select ），因为此处的（seect ）将输出一个可能有多个元组/行/记录的选择集而不仅仅是一个元组/行/记录，所以只能用 in 短语来连接而不能用 来连接】【注意：在SQL的where子句中如用到一个属性列/字段与一个（select ）子句来连接形成一个逻辑表达式时，一般用 in 短语来代替 作连接符合，以免出错。 in 短语在此处能完全代替 的功能，但不能代替 in 短语的功能】10、将（st_a , 66 , 200 , chicago）插入stores表insert intostores (stored_id , phone , no_bins , city )values (st_a , 66 , 200, chicago ) ;11、查询有两种以上货物的商店号select store_idfromholdgroup bystore_idhaving count(item_id)2 ;或者用下列语句：select A.store_idfromholdABwhere A.store_id=B.store_idand A.item_id B.item_id ;12、按重量由低到高顺序输出货物的信息select*fromitemsorderby weightasc ;13、查重量相同的货物的编号select A.item_id from items A Bwhere A.weight=B.weight andA.item_id B.tiem_id ;三、关系代数：【主要考选择 、投影 、连接 、不考除法】.1、查询有两种以上货物的商店号store_id(store_id,item_id(holdA)store_id,item_id(holdB) )A.store_id=B.store_idAnd A.item_id != B.item_id【在本例子中，参与条件连接的两个运算对象也可以直接是hold表本身，但为了降低运算量，本例子中用的是对hold表在store_id和item_id属性列上的投影。】【注意：在关系代数中，一个表达式写完之后不能相SQL一样加分号; 来结尾。关系代数的表达式不用任何符号来表示结尾。而SQL一条语句完成后必须用一个分号; 来表示语句结束。】【注意：在关系代数中，参与运算的可以是关系/表，也可以是对关系/表的某种操作的表达式。】2、查位于Boston的商店的号码（store_id）store_id(city=Boston(stores)city(desc,item_id(desc=window(items)store_id,item_id(hold)store_id,city(stores)3、查各有货物名称（desc）为window的商店的位置（city）【注意：在关系代数中，当使用自然连接的时候，可以不写出应在连接符号下方写出的条件表达式。】【重点一：数据库的设计（设计其模式：即设计表结构。以及用SQL建立带有指定约束条件的表结构），可能比较复杂，题目目标例如：学生的成绩管理、收费单的管理、订单的管理、人事的管理。特别注意CREATE 语句中的各种约束条件的写法：not null，not nullunique，not null primary key，check(条件表达式)以及 references XXX表(属性列)以及 primary key（属性列1，属性列2）注意：考试时数据库设计的题，设计出的表的个数不要超过5个。重点二：用SQL对表实现SELECT、INERST、UPDATE、DELETE ，不考对索引、视图的操作、不考授权和收回授权，不考对表结构的修改 象征性的考一些：关系代数：选则、投影、连接。不考除法。一共三个题型，形同上述三个例子。不考概念。】附录：1、关系代数：（1）、选择：逻辑表达式 (表的名称) 用于从指定表中选出满足指定逻辑表达式的元组/行/记录。相当于：SELECT * FROM 指定的表 WHERE逻辑表达式; （注意SQL语句必须用分号; 结尾。）例如：city=Boston(stores)（2）、投影：属性列1，属性列2，属性列n(表的名称)用于从指定表中输出指定的属性列。相当于：SELECTDISTINCT属性列1，属性列2，属性列n FROM 指定的表;逻辑表达式例如：store_id,city(stores)（3）、连接：关系1关系2用于完成关系1和关系2的笛卡尔积，并从中选出满足逻辑表达式的元组/行/记录输出。它相当于：逻辑表达式 (关系1关系2)，也即：SELECT * FROM关系1，关系2WHERE 逻辑表达式hold.Store_id=stores.store_id例如：store_id,item_id(hold)store_id,city(stores)2、SQL语言：（1）、查询，SELECT语句：SELECTALL|DISTINCT 别名 ， 别名FROM 别名 ， 别名 WHERE GROUP BY HAVING ORDER BY ASC|DESC ;、ALL和DISTINCT选项：默认为ALL，可省略。如用了DISTINCT 短语，表示从选择结果集中排除哪些值完全相同的重复的元组/行/记录。、目标列表达式的格式可以是以下几种：*代表选取所有属性列/字段输出，即在所有属性列/字段上投影。.*代表选取指定表中的所有元组/行/记录。此表名必须是在FROM子句中出现的表或视图的名称或别名。.此表达式可以是属性列名/字段名，作用于属性列名的集函数表达式或其它函数表达式，属性列名与其它任意类型相同的常数的算术表达式（+ - * /），输出结果是此属性列名表达式的表达式值。其中集函数表达式有：COUNT(属性列名)计选择集分组中元组/行/记录的个数SUM(属性列名)求选择集分组中指定属性列/字段的累加和，其中此指定属性列/字段必须为数值型的量。AVG(属性列名)求选择集分组中指定属性列/字段的平均值，其中此指定属性列/字段必须为数值型的量。MAX(属性列名)求选择集分组中指定属性列/字段的最大值，其中此指定属性列/字段必须为数值型的量。MIN(属性列名)求选择集分组中指定属性列/字段的最小值，其中此指定属性列/字段必须为数值型的量。字符串常量必须以成对出现的英文单引号 括起来，将在查询结果集中加入一列，此列也可以再加别名。每个元组/行/记录在此列上的值即为此处指定的字符串常量。别名是在查询结果中用此别名代替别名之前的属性列名/字段名来作为此列的列标题。注意，别名一定已经是个字符串了，所以不能再用成对出现的英文单引号 括起来。、FROM子句：它指定了参与SELECT语句操作的表，可以是一个或多个表或视图，也可以是对同一个表的多次自身连接。在FROM子句中出现的表名或视图名也可以使用别名，所用别名在SELECT语句中的其它任何位置上出现时依然有效。SELECT语句的操作对象范围就是FROM子句中指定的所有表或视图的广义笛卡尔积，即各个表或视图的连接。但是注意：FROM子句的对象只能是已经存在的表或视图，而不能是一个SELECT子查询语句。、WHERE子句的条件表达式可以是以下几种：l 比较运算： NOT ANY|ALL(SELECT 子查询语句)注意：属性列名可以是表名.属性列名的形式，也可以是表的别名.属性列名的形式。如省略表名，则此属性是WHERE子句所在SELECT语句中的FROM子句中指定的某个表的属性列。为比较运算符：=!=!作用等于大于小于大于等于小于等于不等于不等于不大于不小于如果在比较运算符之前有NOT短语，则表示把比较的结果取反。l 限定范围： NOT BETWEEN AND (SELECT 子查询语句) (SELECT子查询语句)注意：当属性列的值在BETWEENAND短语指定的范围之内时，此表达式为真。其中BETWEEN短语后面的是范围的下限，AND短语后面的是范围的上限。范围包括了下限和上限。l 在集合中： NOT IN (值1，值2，值n) (SELECT 子查询语句) 当属性列的值在指定的集合中存在时，此表达式为真。l 字符匹配： NOT LIKE ESCAPE 匹配字符串必须用成对出现的英文单引号 括起来。其间可以使用通配符：%代表任意长度（长度可为0）的字符串。_（即下划线），代表任意的单个字符。在支持中文的RDBS中，一个汉字也是一个字符，在不支持中文的RDBS中，一个汉字是两个字符。ESCAPE短语用于对通配符进行转义，匹配字符串中紧跟在ESCAPE短语后指定的换码字符之后的那个%或_将不作为通配符使用，而是代表此字符本身。注意：当属性列的值是字符串，并且与匹配字符串相匹配时，此表达式为真。l 判别空或非空： ISNOTNULL注意：要判断属性列的值是否为NULL，只能用此表达式，而不能用等号来判断。 l 判断(SELECT子查询语句)的输出值是否存在：NOT EXISTS (SELECT子查询语句) 当(SELECT子查询语句)有输出值的时候，此表达式为真。l 多个条件表达式的逻辑组合： AND AND OR OR注意：当AND和OR同时出现时，AND的优先级比OR高，但可以使用小括号()来改变优先级。注意：WHERE子句中条件表达式中运算符号的左侧如有项目，则此项目必须是一个属性列名/字段名或一个常量，而不能是(SELECT子查询语句)。(SELECT子查询语句)只能出现在WHERE子句中条件表达式中运算符号的右侧。集函数不能用在WHERE子句中。、GROUP BY 子句：l GROUP BY 子句的用法：用于对查询结果集按一列或多列取值相等的原则分组。如果不使用GROUP BY 子句，则整个SELECT语句的输出结果集作为唯一的一个分组。注意：集函数如SUM（），COUNT（），AVG（），MAX（），MIN（）等的作用范围是一个分组。所以如果SELECT语句中不用GROUP BY 子句，则集函数作用到整个SELECT的结果集上；如用了GROUP BY 子句对SELECT结果集进行了分组，则集函数将分别作用于每个组，也即每个分组将有自己的一个集函数的运算值。l GROUP BY 子句中的HAVING短语：如果分组后还要要求按照一定条件对这些分组作筛选，使得只输出某些指定的组，则用HAVING短语。HAVING短语的作用对象是SELECT选择集中的各个分组，分组的依据是GROUP BY子句中指定的，即所有在这一列上值相等的元组/行/记录属于同一个分组。HAVING短语后面的条件表达式中可以使用集函数。并且集函数可放在条件表达式中运算符号的左边或右边。【WHERE子句的作用对象是FROM子句中指定的所有表或视图的广义笛卡尔积，并且WHERE子句中不能使用集函数。】、ORDER BY 子句：用于对SELECT语句的最终输出结果按指定属性列的升序（ASC）或降序（DESC）方式排序。默认为升序（ASC）排序。（2）、插入，INSERT语句：INSERT INTO ( ,)VALUES( ,);或者：INSERT INTO ( ,) (SELECT子查询语句);注意：l 如果INTO子句中没有指明任何列名，则新插入的记录必须在指定表中每一