dqz数据库原理

《dqz数据库原理》由会员分享，可在线阅读，更多相关《dqz数据库原理（16页珍藏版）》请在装配图网上搜索。

1、dqz数据库原理一、DB设计方法：ER法、分解法例：设计一个连锁店订单管理系统，在该数据库中，每个商店包括商店编号（storejd）,商 店的电话号码（pone）,库位数（no.bins）,所在城市（city）,每个商店还存有其所存储货物 （items）的编号（item_id）,名称（desc）,尺寸（size）,重量（weight）,存储的数量（qtyjield）, 商店向供货商（supplier）订货时的定购日期（ordejdate ）,定购的货物编目及数量 （qty_ordered）设：1 一个城市不能有一个以上的商店，每商店位于且仅能位于一个城市，每商店不能有一 个以上的电话号码，但可

2、以无电话号码。2、每货物有且仅有一个货物编号，存储的货物必须已知名称、尺寸，店可以无数量信息。3、存储的货物必须大于04、订货时定购的各货物数量必须大于0, 一份订单不能有一个以上的供货商，但必须有 一个供货商以及订货日期，一份订单只能出现已知货物编号的货物。问：1、直接设计分解到3NF的DB2、用SQL-98的DDL描述上述的语义的假设。解：1、直接设计分解到3NF的DB【作题目时可以直接给出设计的各个表结构,不需要写明下面的具体过程】第一步：先根据题目画出E-R图草图：据题意可知：有三个实体：商店stores , 供货商supplier百 七华 Mm（Jr）还有三个联系：商店与货物联系：存

3、货hold, 土1货物itmes,是个l:n的联系-# -商店与供货商联系：订单orders第二步：据E-R草图分析处理、修改重构得到基本E-R图：分析可知：（1）实体：商店stores中，存在以下函数依赖：商店编号store_id商店电话号码pone商店编号store_id库位数no_bins商店编号storejd所在城市city所以：此实体而主码primary key必定是：商店编号storejd所以：没有部分函数依赖或传递依赖，满足3NF要求，所以：商店store实体不需要修 改。（2）、实体：供货商supplier中只有一个属性，所以可以把它变成为联系：订单hold的 一个属性。所以取

4、消实体：商店（3）、实体：货物items中，存在以下函数依赖：货物编号item_id-货物名称desc货物编号itemjd货物尺寸size货物编号货物重量weight所以：此实体的主码primary kev必定是：货物编号itemjd所以：没有部分函数依赖或居递在赖，满足3NF要求，所以：货物items实体不需要修 改。（4）、联系：订单hold中，存在以下函数依赖：订单编号ordejno订单日期ordejdate订单编号ordejno商店编号store id订单编号order no供货商supplier订单编号ordejno所订货物编号item id（订单编号order_no,所订货物编号i

5、tem_id）所订货物数量qty_ordcred发现：由于在联系：订单hold中，既存足由订单编号ordejno单疝数据决定某属性 的情况，又存在由（订单编号order。，所订货物编号item_id）共同数据决定某属性的 情况。所以：此实体的主码primary key必定只能是：（订单编号order_no,所订货物编号 item_i（l）烝而：决定了（订单编号order no,所订货物编号item id）为主码，则又有下列关系：（订单编号ordejno,所订货物编号item id） 订单日期ordcr_datc（订单编号order no,所订货物编号item_id） 商店编号!it（）re_i

6、d（订单编号ordcr_no,所订货物编号item id）供货商supplier-3 -于是，此关系中存在部分函数依赖！因为存在:订单编号。丫胡丫0订单日期 order _date,并且又存在:（订单编号order_nof所订货物编号 item_id ） 订单日期 order0Reference stores(storejd) Reference iteins(iteinjd)Primary key所以SQL语句如下： create table holdstore_id char(8) not null references stores(store_id), itemjd char(13)

7、not null references items(item_id)5qty_held number(8) check(qty_held0)5primary key(stroe_id4tem_id)；【注意：作为主码的属性列/字段一定同时也是不可为空的，所以对多个属性列/字段作主码 时，在主码的各属性列/字段上必须写有约束条件：not null ,而在表定义的最后必须用primary key (主码的属性列/字段的集合)语句来定义表的主码，以 保证其实体完整性】【注意：虽然题目中没有明确引用关系，但是在建立表的时候，特别是建立表示联系的表的 时候，必须自己指出其中在其他表中已经出现过的属性列/

8、字段是从什么表的什么字 段引用过来的，以保证其参照完整性】【注意：用到了引用关系的属性列/字段的数据类型定义及长度必须与被引用对象相同】(4)、用SQL建立订单orders表据订单orders表中的函数依赖，以及题目假设4,知道：知道订单orders表中订单编号order】。不能为空且为主码，订货日期ordejdate不能 为空，供货商supplier不能为空且为唯一(题目中要求一份订单不能有一个以上的供应商但 必须有一个供应商)，即：orders ( ordejno , order_date, supplier )Not null unique所以SQL语句如下： create table

9、orders(order_no char(8) order_date date supplier char(20)；not null primary key, not nuli,not null unique约束:Not nullNot nullPrimary key-7 -(5)、用SQL建立订单明细表item_order表据订明细表ltein_order表中的函数依赖，以及题目假设4,知道：知道订明细表ltem_order表中订单编号ordejno是引用于oders (orderjd)且不能为 空，货物编号是引用于items且不能为空，订货数量qty_ordered必须大于0,整个item

10、_order表的主码为(order】。，itemjd)即items_order ( ordejid ,qty_ordered )约束：Not nullNot null0Reference orders(orderjd) Reference Iteins(iteinjd) Primary key所以SQL语句如下：create table items_order( orderjdchar(8) not null references orders(order_id)5itemjdchar(13) not null references items(item_id),qty_ordered num

11、ber(8) check(qty_ordered0), primary key(order_id4tem_id);【注意写SQL时，写完一条SQL语句必须以一个分号；来结束此语句】二、SQL：【主要考察：建表，查询(选择、投影、连接)，插入，删除，修改。不考修改表、 建立索引、视图，删除表、索引、实体，授权和收回授权】1、查位于Boston的商店的号码(storejd)：select storejd from stores where city= Boston;2、查重量大于10的各种货物的货号(itemjd)：select itemjd from items where weight10 ;

12、3、查某供货商P&G的所有供货的订货日期select date from orders where supplier=P&G;4、查各有货物名称(desc)为window的商店的位置(city)：select A.city from stores A, items B, item_order Cwhere A.store_id=Cstore_id and Ceitem_id=Bitem_id and B.desc=9window9;5、查备有P&G所供货物的商店号(storejd)select hold.storejd from hold, orders, item_orderwhere ho

13、ld.item_id=item_ordenitem_id anditem_ordereorder_no=orders.order_noand orders.supplier=T&G5;或使用in短语来实现：select holdestore_id from holdwhere hold.item_id in(select item_oder.item_id from item_oderwhere item_odenorder_no in(select ordersorder_no from orders) );6、查备有所有货物的商店号(storjld) select Aestore_id f

14、rom stores A where not exists(select * from itmeswhere itmes.itemjd no in(select hold.item_id from hold) );7、查每个商店备有的各种货物的总数量select store_id, sum(qty_held) from hold group by store_id ;8、删除订单。rdl及相关订购货物 delete from item_order where order no=ordU; delete from orders where order no=9ordr;9、将所有货物重量在原基础

15、上提高三倍 update itemsset weight=weight*3;如将商店编号(storeJd)为，kmP的商店的货物名称(desc)为，cap，的货物数量增加20,则 用下面带有where子句的update语句：update holdset qty_held=qty_held+20where store_id=9kml5 and item_id in(select item_id from iteins where desc=cap);【注意此处不能用itemjd = (select ),因为此处-8 -【注意：在SQL的where子句中如用到一个属性列/字段与一个(select

16、)子句来连接形成一个逻辑表达式时，一般用in短语来代替=作连接符合，以免出错。in短语 在此处能完全代替=的功能，但=不能代替in短语的功能】10、将，66,200 Jchicago)插入 stores 表insert into stores (stored_id , phone, no_bins, city) values ( 4st_a 66,200, Chicago);11、查询有两种以上货物的商店号 select storejd from hold group by storejd having count(item_id)2;或者用下列语句：select A.store_id fro

17、m hold A Bwhere A.store_id=Bestore_id and Aitem_id Bitem_id ;12、按重量由低到高顺序输出货物的信息 select * from items order by weight asc;13、查重量相同的货物的编号select A.item_id from items A Bwhere A.weight=B.weight and Aitem_id Btiem_id ;三、关系代数：【主要考选择6、投影n、连接 X、不考除法】 、查询有两种以上货物的商店号o“store_id(n storedd,A)XInstore_id, item_id

18、0 B)A. store_id=B. store.【注意：在关系代数中，一个表达式写完之后不能相SQL一样加分号；来结尾。关系代数的 表达式不用任何符号来表示结尾。而SQL一条语句完成后必须用一个分号；来表示 语句结束。】【注意：在关系代数中，参与运算的可以是关系/表，也可以是对关系/表的某种操作的表达 式。】2、查位于Boston的商店的号码(storejd)nstore_id(5city=, Boston Stores)3、查各有货物名称（desc）为window的商店的位置（city）ncity ndesc,des* windown store_id）item_idXnste_id, c

19、ity（stes）【注意：在关系代数中，当使用自然连接的时候，可以不写出应在连接符号下方写出的条件 表达式。】【重点一：数据库的设计（设计其模式：即设计表结构。以及用SQL建立带有指定约束条件 的表结构），可能比较复杂，题目目标例如：学生的成绩管理、收费单的管理、订 单的管理、人事的管理。特别注意CREATE语句中的各种约束条件的写法：not null, not null unique , not null primary key , check（条件表达式）以及 references XXX表（属性列）以及primary key （属性列1,属性列2） 注意:考试时数据库设计的题,设计出自表

20、质个数不要超过5个。重点二：用SQL对表实现SELECT、INERST. UPDATE. DELETE ,不考对索弓、 视图的操作、不考授权和收回授权，不考对表结构的修改象征性的考一些：关系代数：选则、投影、连接。不考除法。一共三个题型，形同上述三个例子。不考概念。】-11 -附录：1、关系代数：（1）、选择：5逻辑表达式（表的名称）用于从指定表中选出满足指定逻辑表达式的元组/行/记录。相当于：SELECT * FROM指定的表 WHERE逻辑表达式；（注意SQL语句必须用分号；结尾。）例如： cit尸，Boston（stes）（2）、投影：口属性列1,属性列2,属性列n（表的名称）用于从指定

21、表中输出指定的属性列。相当于：SELECT DISTINCT 属性列1,属性列2,，属性列n FROM指定的表；例如：11 store_id, city（stores）（3）、连接：关系1 X 关系2、皿 ifca 主用于完成关系1和关系2的笛卡尔积，并从中选出满足逻辑表达式的元组/行/记录输出。它相当于：6逻辑表达式（关系IX关系2）,也即：SELECT * FROM关系1,关系2 WHERE逻辑表达式例如：11 store-id, item_id（hold）nStore_id, city（stores）L - 1O 一2、SQL语言：、查询，SELECT语句:SELECT ALL|DIST

22、INCT目标列表达式别名, 目标列表达式 别名FROM 表名或视图名别名,表名或视图名别名WHERE不包含有集函数的作用于基本表或视图的条件表达式GROUP BY 属性列名1 HAVING 可包含有集函数的作用于选择集分组的条件表达式）ORDER 8丫属性列名 2 ASC|DESC;1 ALL和DISTINCT选项：默认为ALL,可省略。如用了 DISTINCT短语，表示从选 择结果集中排除哪些值完全相同的重复的元组/行/记录。2目标列表达式的格式可以是以下几种：*代表选取所有属性列/字段输出，即在所有属性列/字段上投影。表名.*代表选取指定表中的所有元组/行/记录。此表名必须是在FROM子句

23、中出现的表或视图的名称或别名。表名）.属性列名表达式此表达式可以是属性列名/字段名，作用于属性列名的集函数表达式或其它函数表达式，属性列名与其它任意类型相同 的常数的算术表达式输出结果是此属性列名表达式的表达式 值。其中集函数表达式有：COUNT（属性列名）计选择集分组中元组/行/记录的个数SUM （属性列名）求选择集分组中指定属性列/字段的累加和，其中此指定属性列/字AVG （属性列名）MAX （属性列名）MIN （属性列名）段必须为数值型的量。求选择集分组中指定属性列/字段 的平均值，其中此指定属性列/字 段必须为数值型的量。求选择集分组中指定属性列/字段 的最大值，其中此指定属性列/字

24、段必须为数值型的量。求选择集分组中指定属性列/字段 的最小值，其中此指定属性列/字 段必须为数值型的量。字符串常量必须以成对出现的英文单引号括起来，将在查询结果集中加入一列，此列也可以再加别名。每个元组/行/记录在此列上的值 即为此处指定的字符串常量。别名是在查询结果中用此别名代替别名之前的属性列名/字段名来作为此列的列标题。注意，别名一定已经是个字符串了，所以不能再用 成对出现的英文单引号括起来。v3、FROM 子句:它指定了参与SELECT语句操作的表，可以是一个或多个表或视图，也可以是 对同一个表的多次自身连接。在FROM子句中出现的表名或视图名也可以使用别名, 所用别名在SELECT语

25、句中的其它任何位置上出现时依然有效。SELECT语句的操 作对象范围就是FROM子句中指定的所有表或视图的广义笛卡尔积，即各个表或视图的连接。但是注意：FROM子句的对象只能是已经存在的表或视图，而不能是一个 SELECT子查询语句。4、WHERE子句的条件表达式可以是以下几种：比较运算：属性列名NOT9属性列名常量ANY I ALL （SELECT 子查询语句）0=1 = !,即所有在属性列名1这一列上值相等的元组/ 行/记录属于同一个分组。HAVING短语后面的条件表达式中可以使用集函数。并且集函数可放在条件表 达式中运算符号的左边或右边。WHERE子句的作用对象是FROM子句中指定的所有

26、表或视图的广义笛卡尔 积,并且WHERE子句中不能使用集函数。v6、ORDER BY 子句：用于对SELECT语句的最终输出结果按指定属性列的升序（ASC）或降序（DESC） 方式排序。默认为升序（ASC）排序。（2）、插入，INSERT语句：INSERT INTO 表名 （属性列名1 ,属性列名2）VALUES常量1 ,）;或者：INSERT INTO表名 属性列名1 , ）（SELECT子查询语句）； 注意： 如果INTO子句中没有指明任何列名，则新插入的记录必须在指定表中每一个属性 列上都插入有值。 如果某些属性列/字段在INTO子句中指明了，而另外某些属性列/字段在INTO子句 中没有

27、出现，则新插入的元组/行/记录中这些未指明的属性列/字段处值为NULL。 如果在表定义中说明了 NOT NULL的属性列，则在此列属性列上必须插入一个值， 不能为空，否则回出错。 无论VALUES子句中是一个（常量集）还是一个（SELECT子查询语句）的选择集，其各 个值的类型必须与INTO子句中指定的表的各属性列的类型相匹配。（3）、修改，UPDATE语句：UPDATE表名SET属性列名表达式1 ,属性列名2=表达式2WHERE不包含有集函数的作用于基本表或视图的条件表达式;注意：当WHERE子句省略时表示修改整个指定表中的所有元组/行/记录。 UPDATE语句的WHERE子句的用法和注意事

28、项完全同SELECT语句中的WHERE子句的 用法，例如同样也可以在WHERE子句中使用含（SELECT子查询）的表达式。（4）、删除，DELETE语句：DELETE FROM 表名WHERE不包含有集函数的作用于基本表或视图的条件表达式;注意：当WHERE子句省略时表示删除整个指定表中的所有元组/行/记录,但仍然保留表的 结构，即一张空表。 DELETE语句的WHERE子句的用法和注意事项完全同SELECT语句中的WHERE子句的 用法。例如同样也可以在WHERE子句中使用含（SELECT子查询）的表达式。（5）、建立表结构：CREATE TABLE 数据类型列级完整性约束条件,属性列名2数

29、据类型列级完整性约束条件,表级完整性约束条件）；INT FLOAT NUMBER(n) CHAR(n) DATE TIME全字长二进制整型1考试中可能出现的数据类型可以是以下几种：双字长浮点数长度为n的定长数值型长度为n的定长字符串日期型，格式为YYYYMMDD 时间型，格式为HH.MNLSS.XX2列级完整性约束条件可以是以下几种:NOT NULLNOT NULL UNIQUENOT NULL PRIMARY KEYNOT NULLCHECK（条件表达式）取值非空取值唯一，即表明此属性列/字段是个候选码 表明此属性列/字段是表的主码REFERENCES 表名（属 性列名）表面此属性列/ 字段

30、与另外某个表的某个 字段有参照关系。此属性列的取值必须满足条件表达式的规定。此 处条件表达式可用SELECT语句中的WHERE 子句中的比较运算和限定范围的条件表达式。3、表级完整性约束条件可以是：PRIMARY KEY （属性列1,属性列2,属性列n）注意：在CREATE语句中，每个属性列/字段的定义完毕之后，如果下面还有子语，则各子 句之间用英文逗号，分隔开。而在一个属性列/字段的定义行上字段名、数据类型、 各种列级约束条件等之间只能用空格来分隔。（6）、复制表结构但不拷贝数据CREATE TABLE新表名LIKE 旧表名；（7）、复制表结构并拷贝所有数据CREATE TABLE新表名SELECT * FROM 旧表名；【注意：SQL中每条语句写完之后必须以英文的分号；来结束。】3、第三范式，3NF：满足3NF的充要条件：关系模式中非主属性既不部分依赖于码，也不传递依赖于码。-15 -