数据库概论必考经典例题课后重点答案PPT课件.ppt

1 3 用SQL语句建立第二章习题5中的四个表 供应商关系 S SNO SNAME STATUS CITY 零件关系 P PNO PNAME COLOR WEIGHT 工程项目关系 J JNO JNAME CITY 供应情况关系 SPJ SNO PNO JNO QTY 2 定义的关系S有四个属性 分别是供应商号 SNO 供应商名 SNAME 状态 STATUS 和所在城市 CITY 属性的类型都是字符型 长度分别是4 20 10和20个字符 主键是供应商编号SNO 在SQL中允许属性值为空值 当规定某一属性值不能为空值时 就要在定义该属性时写上保留字 NOTNULL 本例中 规定供应商号和供应商名不能取空值 由于已规定供应商号为主码 所以对属性SNO的定义中的 NOTNULL 可以省略不写 CREATETABLES SNOCHAR 4 NOTNULL SNAMECHAR 20 NOTNULL STATUSCHAR 10 CITYCHAR 20 PRIMARYKEY SNO 3 CREATETABLEP PNOCHAR 4 NOTNULL PNAMECHAR 20 NOTNULL COLORCHAR 8 WEIGHTSMALLINT PRIMARYKEY PNO CREATETABLEJ JNOCHAR 4 NOTNULL JNAMECHAR 20 CITYCHAR 20 PRIMARYKEY JNO CREATETABLESPJ SNOCHAR 4 NOTNULL PNOCHAR 4 NOTNULL JNOCHAR 4 NOTNULL QTYSMALLINT PRIMARYKEY SNO PNO JNO FOREIGNKEY SNO REFERENCESS SNO FOREIGNKEY PNO REFERENCESP PNO FOREIGNKEY JNO REFERENCESJ JNO 4 4 针对上题中建立的四个表试用SQL语言完成第二章习题5中的查询1 求供应工程J1零件的供应商号码SNO 2 求供应工程J1零件P1的供应商号码SNO 3 求供应工程J1零件为红色的供应商号SNO 4 求没有使用天津供应商生产的红色零件的工程号JNO 5 求至少用了供应商S1所供应的全部零件的工程号JNO 5 1 求供应工程J1零件的供应商号码SNO SELECTDISTINCTSNOFROMSPJWHEREJNO J1 SELECT子句后面的DISTINCT表示要在结果中去掉重复的供应商编号SNO 一个供应商可以为一个工程J1提供多种零件 2 求供应工程J1零件P1的供应商号码SNO SELECTSNOFROMSPJWHEREJNO J1 ANDPNO P1 3 求供应工程J1零件为红色的供应商号SNO SELECTDISTINCTSNOFROMSPJWHEREJNO J1 ANDPNOIN SELECTPNOFROMPWHERECOLOR 红 6 4 求没有使用天津供应商生产的红色零件的工程号JNO 常见错误 SELECTJNOFROMJWHERENOTEXISTS SELECT FROMS SPJ PWHERESPJ JNO J JNOANDSPJ SNO S SNOANDSPJ PNO P PNOANDS CITY 天津 ANDP COLOR 红 当从单个表中查询时 目标列表达式用 若为多表必须用表名 正确写法SELECTJNOFROMJWHERENOTEXISTS SELECTS SPJ P FROMS SPJ PWHERESPJ JNO J JNOANDSPJ SNO S SNOANDSPJ PNO P PNOANDS CITY 天津 ANDP COLOR 红 7 4 求没有使用天津供应商生产的红色零件的工程号JNO SELECTJNOFROMJWHEREJNONOTIN SELECTJNOFROMS SPJ PWHERES SNO SPJ SNOANDSPJ PNO P PNOANDS CITY 天津 ANDP COLOR 红 SELECTJNOFROMJWHERENOTEXISTS SELECT FROMSPJWHERESPJ JNO J JNOANDSPJ SNOIN SELECTSNOFROMSWHERES CITY 天津 ANDSPJ PNOIN SELECTPNOFROMPWHEREP COLOR 红 8 5 求至少用了供应商S1所供应的全部零件的工程号JNOSELECTDISTINCTJNOFROMSPJSPJ1WHERENOTEXISTS SELECT FROMSPJSPJ2WHERESNO S1 ANDNOTEXISTSPNO ALL SELECT FROMSPJSPJ3WHEREPNO SPJ2 PNOANDJNO SPJ1 JNO 9 5 求至少用了供应商S1所供应的全部零件的工程号JNO 第一种理解 SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS SELECT FROMSPJSPJYWHERESPJY SNO S1 ANDNOTEXISTS SELECT FROMSPJSPJZWHERESPJZ JNO SPJX JNOANDSPJZ PNO SPJY PNOANDSPJZ SNO SPJY SNO 查询结果 第二种理解 SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS SELECT FROMSPJSPJYWHERESPJY SNO S1 ANDNOTEXISTS SELECT FROMSPJSPJZWHERESPJZ JNO SPJX JNOANDSPJZ PNO SPJY PNO 查询结果 J4 SPJZ SNO S1 10 5 针对习题3中的四个表试用SQL语言完成以下各项操作1 找出所有供应商的姓名和所在城市2 找出所有零件的名称 颜色 重量3 找出使用供应商S1所供应零件的工程号码4 找出工程项目J2使用的各种零件的名称及其数量5 找出上海厂商供应的所有零件号码6 找出使用上海产的零件的工程名称7 找出没有使用天津产的零件的工程号码8 把全部红色零件的颜色改成蓝色9 有S5供给J4的零件P6改为由S3供应 请作必要的修改10 从供应商关系中删除S2的记录 并从供应情况关系中删除相应的记录11 请将 S2 J6 P4 200 插入供应情况关系 11 1 找出所有供应商的姓名和所在城市SELECTSNAME CITYFROMS 2 找出所有零件的名称 颜色 重量SELECTPNAME COLOR WEIGHTFROMP 3 找出使用供应商S1所供应零件的工程号码SELECTDISTINCTJNOFROMSPJWHERESNO S1 12 4 找出工程项目J2使用的各种零件的名称及其数量SELECTPNAME QTYFROMP SPJWHEREP PNO SPJ PNOANDSPJ JNO J2 13 5 找出上海厂商供应的所有零件号码SELECTDISTINCTPNOFROMS SPJWHERES SNO SPJ SNOANDS CITY 上海 SELECTDISTINCTPNOFROMSPJWHERESNOIN SELECTSNOFROMSWHERES CITY 上海 6 找出使用上海产的零件的工程名称SELECTJNAMEFROMS SPJ JWHERES SNO SPJ SNOANDJ JNO SPJ JNOANDS CITY 上海 14 7 找出没有使用天津产的零件的工程号码SELECTJNOFROMJWHEREJNONOTIN SELECTJNOFROMSPJ SWHERES SNO SPJ SNOANDS CITY 天津 SELECTJNOFROMJWHERENOTEXISTS SELECT FROMSPJWHEREJNO J JNOANDSNOIN SELECTSNOFROMSWHERES CITY 天津 SELECTJNOFROMJWHERENOTEXISTS SELECTSPJ S FROMSPJ SWHEREJNO J JNOANDSNO S SNOANDS CITY 天津 15 8 把全部红色零件的颜色改成蓝色UPDATEPSETCOLOR 蓝 WHERECOLOR 红 9 由S5供给J4的零件P6改为由S3供应 请作必要的修改UPDATESPJSETSNO S3 WHERESNO S5 ANDJNO J4 ANDPNO P6 10 从供应商关系中删除S2的记录 并从供应情况关系中删除相应的记录DELETEFROMSWHERESNO S2 DELETEFROMSPJWHERESNO S2 11 请将 S2 J6 P4 200 插入供应情况关系INSERTINTOSPJVALUES S2 P4 J6 200 常见错误 INSERTINTOSPJVALUES S2 J6 P4 200 16 11 请为三建工程项目建立一个供应情况的视图SANJIAN SPJ 包括供应商代码 SNO 零件代码 PNO 供应数量 QTY 针对该视图完成下列查询 1 找出三建工程项目使用的各种零件代码及其数量 2 找出供应商S1的供应情况 17 创建视图 CREATEVIEWSANJIAN SPJASSELECTSNO PNO QTYFROMSPJ JWHERESPJ JNO J JNOANDJ JNAME 三建 1 找出三建工程项目使用的各种零件代码及其数量 SELECTPNO SUM QTY SELECTPNO QTYFROMSANJIAN SPJFROMSANJIAN SPJ GROUPBYPNO 2 找出供应商S1的供应情况 SELECT FROMSANJIAN SPJWHERESNO S1 18 数据库设计方法 1 基本设计法分五步进行 a 创建用户视图b 汇总用户视图 得出全局数据视图 即概念模型 c 修改概念模型 d 转换并定义概念模型 转换成DBMS的数据模型 e 设计优化物理模型 即存储策略 19 例如1 关系模式R C T H R S G F C T CS G HT R HR C HS R 则 CT CHR HRT CSG HSR 为一个3NF的既具有无损联接性又具有函数依赖保持性的分解 R的码是HS 20 例如2 关系模式R A B C D E F A D E D D B BC D DC A 则 ED BCD ACD 为一个3NF的具有函数依赖保持性的分解 由于R的码是CE 则 ED BCD ACD CE 为一个3NF的既具有无损联接性又具有函数依赖保持性的分解 21 例如3 关系模式R C S Z F CS Z Z C 则R属于3NF 可以分解为具有无损联接性的BCNF 而不可能分解成具有函数依赖保持性的BCNF 当分解为 SZ CZ 则它为一个BCNF的具有无损联接性的分解 22 例如4 关系模式R T Q P C S Z F T Q T P T C T S PCS Z Z P Z C 试分解R属于3NF既具有无损联接性又具有函数依赖保持性 从题目可知码是T 根据相同左部原则可分解为 TQPCS PCSZ ZPC 由于ZPC包含于PCSZ中 所以分解为 TQPCS PCSZ 而R1 T Q P C S 属于BCNF 但R2 P C S Z 不属于BCNF 再继续分解成 SZ PCZ 后 则属于BCNF 23 例如5 关系模式R S C G T D F SC G C T T D 试分解成BCNF 从题目可知码是SC 首先从关系R中分出TD 即R1 S C G T R2 T D 再从R1中分出CT 即R3 C T R4 S C G R2 R3 R4都属于BCNF 分解完成 24 习题 求候选码 转换3NF BCNF 1 设有关系模式R O I S Q B D 其中F S D I B IS Q B O 2 设有关系模式R A B C D 其中F A C C A B AC D AC BD A 3 设有关系模式R A B C D E 其中F A D E D D B BC D DC A 4 设有关系模式R A B C D E F 其中F A B C F E A CE D 25 习题 求候选码 转换成BCNF 5 设有关系模式R 学号 课程号 学分 成绩 奖学金 其中F 课程号 学分 成绩 奖学金 学号 课程号 成绩 6 设有关系模式R 学生 课程 教师 其中F 教师 课程 学生 课程 教师 26 习题答案 1 KEY IS2 KEY BD3 KEY CE4 KEY CE5 KEY 学号 课程号 6 KEY 学生 课程 R1 学生 教师 R2 教师 课程 27 例如 R A B C F A B C B 当 1 AB AC 时 它具有无损联接性 但不具有依赖保持性 当 2 AB BC 时 它具有依赖保持性 但不具有无损联接性 然而当 3 AB AC BC 时 它既具有依赖保持性 又具有无损联接性 28 依赖保持 设关系模式R的一个分解为 R1 R2 Rk F是R的依赖集 如果F等价于 R1 F R2 F Rk F 则称分解 具有依赖保持性 一个无损联接分解不一定具有依赖保持性 同样一个依赖保持分解不一定具有无损联接 29 模式分解 若要求分解保持函数依赖 那么模式分解总可以达到3NF 但不一定能达到BCNF 若要求分解既保持函数依赖 又具有无损联接性 那么模式分解可以达到3NF 但不一定能达到BCNF 若要求分解既具有无损联接性 那么模式分解一定可以达到4NF 30 求下列最高属于第几范式 1 设R A B C D F B D AB C 2 设R A B C D E F AB CE E AB C D 3 设R A B C D F B D D B AB C 4 设R A B C F A B B A A C 5 设R A B C F A B B A C A 6 设R A B C D F A C D B 7 设R A B C D F A C CD B 31 答案 1 Key AB R 1NF2 Key AB或E R 2NF3 Key AB或AD R 3NF4 Key A或B R BCNF5 Key C R 3NF6 Key AD R 1NF7 Key AD R 1NF 32 BCNF定义 若R 1NF 若X Y且YX时X必含有码 例如 由于 SNO CNO G 满足BCNF的定义 所以SC属于BCNF 当S L分解成SD SNO SDEPT 和DL SDEPT SLOC 后的情形如下 对于SD的函数依赖SNO SDEPT 所以它的码是SNO 所以SD属于BCNF 对于DL的函数依赖SDEPT SLOC 所以它的码是SDEPT 所以DL属于BCNF 33 3NF定义 若R 1NF 且每一个非主属性既不部分函数依赖于码也不传递函数依赖于码 例如 当把S L C分解成SC SNO CNO G 和S L SNO SDEPT SLOC 后 由于 SNO CNO G 满足3NF的定义 所以SC属于3NF 而S L中候选码是SNO 但SDEPT SLOC SNO SDEPT 即非主属性SLOC传递依赖于码 所以S L不属于3NF 34 2NF定义 若R 1NF 且每一个非主属性完全函数依赖于码 例如 S L C SNO SDEPT SLOC CNO G 这里SNO表示学号 SDEPT表示系名 SLOC表示楼号 CNO表示课程号 G表示成绩 函数依赖有 SNO CNO G SDEPT SLOC SNO SDEPT 所以候选码是 SNO CNO 而非主属性SDEPT和SLOC都是部分函数依赖于码 所以S L C不属于2NF 但属于1NF 35 习题 设R A B C r为R的一个值 r ab1c1 ab2c2 ab1c2 ab2c1 问1 r满足条件A B吗 为什么 2 如果在r中任取一三个元组的子集 这些子集满足条件A B吗 为什么 1 r满足条件A B 2 不满足条件A B 36 求关键字 1 设R A B C D E P F A D E D D B BC D CD A 2 设R O I S Q B D F S D D S I B B I B O O B 3 设R X Y Z W F W Y Y W X WY Z WY XZ W 4 设R O I S Q B D F S D I B B O O Q Q I 5 设R O I S Q B D F I B B O I Q S D 37 答案 1 CEP2 QSI QSO QSB QDB QDI QDO3 XZ4 SI SQ SB SO5 IS 38 四大定理 定理1 设K为R中的属性或属性组合 若K是L或N类 则K必为R的任一候选关键字成员 即是主属性 定理2 设X为R中的属性或属性组合 若X是R类 则X不在任何候选关键字中 即是非主属性 定理3 若K是L类 且K 包含R的全部属性 则K必为R的唯一候选关键字 定理4 若K是L和N类属性组合 且K 包含R的全部属性 则K必为R的唯一候选关键字 39 快速求解关键字 给定关系模式R A1 A2 An 和函数依赖集F 可将其属性分为四类 1 仅仅出现在F的函数依赖左部的属性称L类 2 仅仅出现在F的函数依赖右部的属性称R类 3 在F的函数依赖左右均未出现的属性称N类 4 在F的函数依赖左右均出现的属性称LR类 40 Student Sno Sname Sex