《数据库程序设计》第三章-精选文档

合集下载

《数据库》第三章参考答案

(1)检索检索LIU老师所授课程的课程号、课程名。老师所授课程的课程号、检索老师所授课程的课程号课程名。
π CNO，CNAME(σTNAME =‘LIU’(C)) ，
(2) 检索年龄大于岁的男学生的学号与姓名。检索年龄大于23岁的男学生的学号与姓名。岁的男学生的学号与姓名
πsno，sname
[例 3.11]设有三个关系：例设有三个关系：设有三个关系学生关系: 学生关系 S（SNO，SNAME，AGE，SEX，SDEPT）（，，，，）学习关系: 学习关系 SC（SNO，CNO，GRADE）（，，）课程关系: 课程关系 C（CNO，CNAME，CDEPT，TNAME）（，，，）试用关系代数表达式表示下列查询语句。试用关系代数表达式表示下列查询语句。
（7）检索全部学生都选修的课程的课程号与）课程名。课程名。
πcno
（S）)) ）
，CNAME
（C
∞ ( πSNO，CNO（SC），）
÷
π
SNO
（8）检索选修课程包含）检索选修课程包含LIU老师所授老师所授课程的学生学号。课程的学生学号。
π sno，CNO(SC)
÷πCNO(σTNAME =‘LIU’(C))
(σAGE＞’23’ ∧ SEX=‘M’(s)) ＞
（3）检索学号为学生所学课程的课程名与）检索学号为S3学生所学课程的课程名与任课老师名。任课老师名。
πCNAME，TNAME(σSNO =‘S3’ ( sc∞c)) ，
（ 4）检索至少选修）检索至少选修LIU老师所授课程中一门老师所授课程中一门课的女学生姓名。课的女学生姓名。
πSNAME(σSEX=‘F’∧TNAME=‘LIU’ (s∞sc ∞c))

数据库管理系统VFP第三章PPT课件

例如，一个命令按钮控件的常用属性设置如表所示。
9
10
连续设置多个属性，可以使用WITH…ENDWITH结构一次为对象设置多个属性。
With结构如下所示： WITH 对象语句块 ENDWITH
11
对象的方法
方法：封装在对象中的过程，但又不同于一般的过程。VFP的方法属于对象的内部函数，只用于完成某个特定任务的功能模块，方法的使用不像事件程序一般需要在某一事件发生时响应，而是在程序中直接调用。而不一定相应某事件。方法被封装在对象内部，不同的对象有不同的方法，根据需要用户可以根据需要自行建立新方法。
LostFocus：对象失去焦点时发生的事件。
KeyPress：当用户按下或释放键时发生的事件。
InteractiveChange：以交互方式改变对象的值时发生的事件。
ProgrammaticChange：以编程方式改变对象的值时发生
的事件。
16
对象的引用
对象的包容层次对象的引用
17
（1）绝对引用：通过提供对象的完整容器层次来引用对象，从最高容器开始逐层向下直到某个对象为止的引用称为绝对引用。 Formset.Form.OptionGroup.Option.Enabled=.F.(使选项按钮组第一个选项控件无效)
14
事件发生时具体完成什么任务则取决于用户在事件中编写的程序代码即事件过程。如单击某一个命令按钮将进行的操作就决定于在命令按钮中编写的事件过程（即程序代码）事件过程除了由事件的激发而调用其程序代码外，也可以在程序运行中调用其代码，在程序中调用事件代码的格式是：表单名.对象名.事件名 VFP编程的核心是为每个要处理的事件编写响应事件的程序代码，对象的事件名在代码窗口的过程列表框中。

程序设计语言VF第3章

3.4 数据表的操作
3.4.2 工作区与数据表的基本操作
5.记录的浏览与显示 (1)BROWSE命令 (2)LIST命令 (3)DISPLAY命令
3.4 数据表的操作
3.4.3 记录的定位与检测
•1.指针的绝对移位 •2.指针位置的相对移位 •3.记录检测函数
3.4 数据表的操作
3.4.3 记录的定位与检测
3.5 数据表的维护
3.记录的物理删除 (1)将带有逻辑删除标记的记录物理删除格式：PACK 功能：在当前表中物理删除带有逻辑删除标记的记录。 (2)记录的一次性删除使用ZAP命令可以一次性的将当前数据表中的所有记录从表文件中删除，仅保留表结构(第一行的字段名)。格式：ZAP 功能：一次性物理删除表中所有记录，仅保留表结构。
&&显示从当前记录开始的6 数据表的操作
• VFP中常用的命令子句
3.WHILE子句格式：WHILE <条件> 功能：也用于指明进行操作的条件。当遇到第一个不满足条件的记录时，
命令即停止。例如：LIST NEXT 6 WHILE 成绩<85
3.4 数据表的操作
• VFP中常用的命令子句
1.数据表的统计 (2)求和命令格式：SUM [<数值表达式列表>] [<记录范围>] [FOR<条件1>] [WHILE<条件 2>] [TO <内存变量列表> ] [TO ARRAY <数组名>] 功能：对当前数据表中满足条件的记录根据指定的数值型字段表达式按列求和。 (3)求平均值命令格式：AVERAGE [<数值表达式列表>] [<记录范围>] [FOR<条件1>] [WHILE<条件2>] [TO <内存变量列表> ] [TO ARRAY <数组名>] 功能：对当前数据表中满足条件的记录按指定的数值型字段求平均值。

【精选】数据库第三章课后习题

order by grade
• 14、 • (1)GRANT SELECT ON 职工,部门TO 王明 • (2) GRANT INSERT,DELETE ON 职工,部门TO
李勇
• (3) GRANT SELECT ON 职工WHEN USER() = NAME TO ALL
• (4) GRANT SELECT,UPDATE(工资) ON 职工 TO 刘星
• 7、视图的优点 • 视图能够简化用户的操作 • 视图使用户能以多种角度看待同一数据 • 视图对重构数据库提供了一定程度的逻辑
独立性； • 视图能够对机密数据提供安全保护。
• 8、所有的视图是否都可以更新？
• 不是。视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。因为有些视图的更新不能惟一有意义地转换成对相应基本表的更新，所以，并不是所有的视图都是可更新的。
SPJ TO 李天明；
• 13、 • （1）INSERT INTO SC(Sno,Cno,Grade)
VALUES("2000012", "1128", NULL); • （2）SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;
• （3）SELECT cname,grade FROM course,Sc WHERE o=o AND cname="英语"
• (1) SELECT DIST PNO,QTY FROM SPQ
• (2) SELECT DIST * FROM SPQ WHERE SNO="S1‘
• 12、 • （1）GRANT INSERT
ON TABLE S TO 张勇

完整word版数据库系统基础教程第三章答案

Exercise 3.1.1Answers for this exercise may vary because of different interpretations. Some possible FDs:Social Security number Area code stateStreet address, city, statenamezipcodePossible keys:{Social Security number, street address, city, state, area code, phone number}Need street address, city, state to uniquely determine location. A person could have multiple addresses. The same is true for phones. These days, a person could have a landline and a cellular phoneExercise 3.1.2Answers for this exercise may vary because of different interpretations Some possible FDs:ID x-position, y-position, z-position ID x-velocity, y-velocity, z-velocity x-position, y-position, z-position IDPossible keys:{ID}{x-position, y-position, z-position}The reason why the positions would be a key is no two molecules can occupy the same point. Exercise 3.1.3a(n-1)1. Thus, there are 2 such subsets, sincen may independently be chosen in or out.Exercise 3.1.3b1 or A 2. There are2 (n-1) such subsets when (n-2)2 through A n . There are 2 such subsets when3 through A n . We do not count A 1 in these subsets becausethey are already counted in the first group of subsets. The total number of subsets is 2 (n-1) + 2 (n-2) .Exercise 3.1.3cThe superkeys are any subset that contains {A 1,A 2} or {A 3,A 4}. There are 2 such subsets when considering {A 小2} and the n-2 attributes A 3 through A n . There are 2(n-2)— 2 (n-4) suchsubsets when considering {A 3,A 4} and attributes A5 through A n along with the individualThe superkeys are any subset that contains A each of the n-1 attributes A 2 through AThe superkeys are any subset that contains A considering A 1 and the n-1 attributes A considering A 2 and the n-2 attributes Aattributes A 1 and A 2. We get the 2 (n-4) term because we have to discard the subsets thatcontain the key {A 1,A2} to avoid double counting. The total number of subsets is 2 (n-2) + 2(n-2) _ 2(n-4)Exercise 3.1.3dThe superkeys are any subset that contains {A 1,A2} or {A 1,A3}. There are 2 (n-2)such subsets when considering {A 1,A2} and the n-2 attributes A 3 through A n. There are 2 (n-3)such subsets when considering {A 1,A3} and the n-3 attributes A 4 through A n We do not count A 2 in these subsets because they are already counted in the first group of subsets. The total numberof subsets is 2(n-2)+ 2(n-3).Exercise 3.2.1aWe could try inference rules to deduce new dependencies until we are satisfied we have them all. A more systematic way is to consider the closures of all 15 nonempty sets of attributes.For the single attributes we have {A} + = A, {B} + = B, {C} + = ACD, and {D} + = AD. Thus, the only new dependency we get with a single attribute on the left is C A.Now consider pairs of attributes:{AB} + = ABCD, so we get new dependency AB D. {AC} + = ACD, and AC D is nontrivial. {AD} = AD, so nothing new. {BC} + = ABCD, so we get BC A, and BC D. {BD} + = ABCD, giving us BD A and BD C. {CD} + = ACD, giving CD A.+For the triples of attributes, {ACD} = ACD, but the closures of the other sets are eachABCD. Thus, we get new dependencies ABC D, ABD C, and BCD A.Since {ABCD} + = ABCD, we get no new dependencies.The collection of 11 new dependencies mentioned above are:C A, AB D, AC D, BC A, BC D, BD A, BD C, CD A, ABC D, ABD C, and BCD A.Exercise 3.2.1bFrom the analysis of closures above, we find that AB, BC, and BD are keys. All other sets either do not have ABCD as the closure or contain one of these three sets.Exercise 3.2.1cThe superkeys are all those that contain one of those three keys. That is, a superkey that is not a key must contain B and more than one of A, C, and D. Thus, the (proper) superkeys are ABC, ABD, BCD, and ABCD.Exercise 3.2.2ai) For the single attributes we have {A} += ABCD, {B} + = BCD, {C} + = C, and {D} + = D. Thus,the new dependencies are AC and A D.Now consider pairs of attributes:{AB}+ = ABCD, {AC} + = ABCD, {AD} + = ABCD, {BC} + = BCD, {BD} + = BCD, {CD} + = CD. Thus the new dependencies are AB C, AB D, AC B, AC D, AD B, AD C, BC D and BD C.= BCD, but the closures of the other sets are eachABCD. Thus, we get new dependencies ABCSince {ABCD} + = ABCD, we get no new dependencies.The collection of 13 new dependencies mentioned above are:Now consider pairs of attributes:{AB}+ = ABCD, {AC} + = AC, {AD} + = ABCD, {BC} + = ABCD, {BD} + = BD, {CD} + = ABCD. Thus the new dependencies are ABD, AD C, BC A and CD B.For the triples of attributes, all the closures of the sets are each ABCD. Thus, we get new dependencies ABC D, ABD C, ACD B and BCD A.+Since {ABCD} = ABCD, we get no new dependencies.The collection of 8 new dependencies mentioned above are:AB D, AD C, BC A, CD B, ABC D, ABD C, ACD B and BCD A. += ABCD, {B} + = ABCD, {C} + = ABCD, and {D} + = C, A D, B D, BA, C A, C B, D B and D C.Since all the single attributes ' closures are ABCD, any superset of the singleattributes will also lead to a closure of ABCD. Knowing this, we can enumerate the rest of the newdependencies.The collection of 24 new dependencies mentioned above are:A C, A D,B D, B A,C A, C B,D B, D C, AB C, AB D, AC B, AC D, AD B, AD C, BC A, BC D, BD A, BD C, CD A, CD B, ABC D, ABD C, ACD B and BCD A. Exercise 3.2.2bFor the triples of attributes, {BCD} D, ABD C, and ACD B. A C, A D, AB C, AB D, AC B, AC D, AD ACD B. ii)For the single attributes we have {A} +there are no new dependencies.B, AD C, BC D, BD C, ABC D, ABD C and = A, {B} + = B, {C} + = C, and {D} + = D. Thus, iii) For the single attributes we have {A} ABCD. Thus, the new dependencies are Ai) From the analysis of closures in 3.2.2a(i), we find that the only key is A. All other sets either do not have ABCD as the closure or contain A.ii) F rom the analysis of closures 3.2.2a(ii), we find that AB, AD, BC, and CD are keys. All other sets either do not have ABCD as the closure or contain one of these four sets.iii) From the analysis of closures 3.2.2a(iii), we find that A, B, C and D are keys. All other sets either do not have ABCD as the closure or contain one of these four sets. Exercise 3.2.2ci) The superkeys are all those sets that contain one of the keys in 3.2.2b(i). The superkeys are AB, AC, AD, ABC, ABD, ACD, BCD and ABCD.ii) The superkeys are all those sets that contain one of the keys in 3.2.2b(ii). The superkeys are ABC, ABD, ACD, BCD and ABCD.iii) The superkeys are all those sets that contain one of the keys in 3.2.2b(iii). The superkeys are AB, AC, AD, BC, BD, CD, ABC, ABD, ACD, BCD and ABCD. Exercise 3.2.3aSi nee A 1A 2 …AC con tai ns A 4 …A n , then the closure of A 1A …A n C contains B. Thus it followsthat A 1A 2 …A n C B. Exercise 3.2.3bFrom 3.2.3a, we know that A1A 2…A n C B. Using the concept of trivial dependencies, we can show that A 1A 2…A n C C. Thus A 1A 2 …A n C BC.Exercise 3.2.3cFrom A 1A 2…代巳匕…E , we know that the closure contains B BB 2…B m The B 1B 2…B m and the E 1E 2…E combine to form the C AA 2 …A n EE …E j con tai ns D as well. Thus, A 1A 2 …AE 1E 2 …E Exercise 3.2.3dFrom A 1A 2 …A n C i C 2 …C, we kn ow that the closure con tai ns B B B 2…B m The C 1C 2…C< also tell us that the closure of A AA 2…A n C i C 2…C k BiB>…B<DD …D. Exercise 3.2.4aIf attribute A represented Social Security Number and B represented a person ' s name,then we would assume A B but B A would not be valid because there may be many people with the same name and different Social Security Numbers. Exercise 3.2.4b1B 2…B m because of the FD A 1A 2 …A n 1C 2 …C k . Thus the closure ofD.1B 2…B m because of the FD A 1A 2 …A n1A 2 …AGG …C con tai ns D 1C 2 …D. Thus,Let attribute A represent Social Security Number, B represent gender and C represent name.Surely Social Security Number and gender can uniquely identify a person ' s name (i.e. AB C). A Social Security Number can also uniquely identify a person ' s name (i.e. A C). However, gender does not uniquely determine a name (i.e. B C is not valid).Exercise 3.2.4cLet attribute A represent latitude and B represent longitude. Together, both attributescan uniquely determine C, a point on the world map (i.e. AB C). However, neither A nor B can uniquely identify a point (i.e. A C and B C are not valid).Exercise 3.2.5Give n a relati on with attributes A \A…A n, we are told that there are no fun ctio naldependencies of the form B i E2…B-i C where B iB…B-i is n-1 of the attributes from A 1A2…A and C is the remaining attribute from A 1A2•…A n. In this case, the set B 1庄・…B>1 and any subset do not functionally determine C. Thus the only functional dependencies that we can make are ones where C is on both the left and right hand sides. All of these functional dependencies would be trivial and thus the relation has no nontrivial FD 's.Exercise 3.2.6Let's prove this by using the contrapositive. We wish to show that if X + is not a subsetof Y +, then it must be that X is not a subset of Y.If X + is not a subset of Y +, there must be attributes A 1A2…A in X + that are notin Y +. If any of these attributes were originally in X, then we are done because Y does not contain any of the A1A2…A. However, if the A iA…A were added by the closure, then we must examine the case further. Assume that there was some FD C 1C2…C m A1A2…A where A 1A2…A issome subset of A i A e …A. It must be then that C iG…C m or some subset of C 1C2 …C m is in X. However, the attributes C 1C2•…C m cannot be in Y because we assumed that attributes A 1A2•…A ++are only in X and are not in Y . Thus, X is not a subset of Y.++By proving the contrapositive, we have also proved if X ? Y, then X ? Y .Exercise 3.2.7+The algorithm to find X is outlined on pg. 76. Using that algorithm, we can prove that(X+)+ = X+. We will do this by using a proof by contradiction.+ + + + +Suppose that (X ) MX. Then for (X ) , it must be that some FD allowed additional attributes to be added to the original set X +. For example, X + A where A is someattribute not in X +. However, if this were the case, then X + would not be the closure of X.The closure of X would have to include A as well. This contradicts the fact that we weregiven the closure of X,X closure of X. . Therefore, it must be that (X) = X or else X is not theExercise 3.2.8a If all sets of attributes are closed, then there cannot be any nontrivial functionaldependencies. Suppose A 1A 2...A n B is a nontrivial dependency. Then {A 1A 2...A n } + contains B and thus A 1A 2...A n is not closed. Exercise 3.2.8b and {A,B,C,D}, then the following FDs hold: A BA C A DB AB CB DC AC BC D D AD BD CAB C AB DAC B AC DAD B AD CBC A BC DBD A BD CCD A CD BIf the only closed sets are ABC D ABD C ACD B BCD A Exercise 3.2.8c If the only closed sets are {A,B} and {A,B,C,D}, then the following FDshold:AB BA C A C B C D D A D B D CAC B AC D AD B AD C BC A BC D BD A BD C CD A CD B ABC DABD CACD BBCD AExercise 3.2.9We can think of this problem as a situation where the attributes A,B,C represent cities and the functional dependencies represent one way paths between the cities. The minimal bases are the minimal number of pathways that are needed to connect the cities. We do not want to create another roadway if the two cities are already connected.The systematic way to do this would be to check all possible sets of the pathways. However, we can simplify the situation by noting that it takes more than two pathways to visit the two other cities and come back. Also, if we find a set of pathways that is minimal, adding additional pathways will not create another minimal set.The two sets of minimal bases that were given in example 3.11 are:{A B, B C, C A}{A B, B A, B C, C B}The additional sets of minimal bases are:{C B, B A, A C}{A B, A C, B A, C A}{A C, B C, C A, C B}Exercise 3.2.10aWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets:{A} +=A{B} +=B+{C} +=ACE{AB} +=ABCDE+{AC} +=ACE{BC} +=ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: C A and AB C. Note that BC->A is true, but follows logically from C->A, and therefore may be omitted from our list.Exercise 3.2.10bWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets:{A} +=AD{B} +=B{C} +=C{AB} +=ABDE{AC} =ABCDE {BC} +=BCWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: AC B.Exercise 3.2.10cWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets: {A} +=A {B} +=B {C} +=C {AB} +=ABD {AC} +=ABCDE {BC} +=ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: AC Band BC A. Exercise 3.2.10dWe need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets: {A} +=ABCDE {B} +=ABCDE {C} +=ABCDE {AB} +=ABCDE+{AC} +=ABCDE {BC} +=ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC is: A B,B C and C A. Exercise 3.2.11For step one of Algorithm 3.7, suppose we have the FD ABC DE. We want to use Armstrong s Axioms to show that ABC D and ABC E follow. Surely the functional dependencies DE and DE E hold because they are trivial and follow the reflexivity property.Using the transitivity rule, we can derive the FD ABC D from the FDs ABC we can do the same for ABCDE and DE E and derive the FD ABCFor steps two through four of Algorithm 3.7, suppose we have the initial set of attributes of the closure as ABC. Suppose also that we have FDs C to Algorithm 3.7, the closure should become ABCDE. Taking the FD C sides with attributes AB we get the FD ABC ABD. We can use the splitting method in step one to get the FD ABCD. Since D is not in the closure, we can add attribute D. Takingthe FD D E and augmenting both sides with attributes ABC we get the FD ABCDABCDE.DE and DE D. Likewise, E. D and D E. According D and augmenting bothUsing again the splitting method in step one we get the FD ABCD E. Since E is not in the closure, we can add attribute E.Given a set of FDs, we can prove that a FD F follows by taking the closure of the left side of FD F. The steps to compute the closure in Algorithm 3.7 can be mimicked by Armstrong ' s axioms and thus we can prove F from the given set of FDs using Armstrong ' s axioms.Exercise 3.3.1aIn the solution to Exercise 3.2.1 we found that there are 14 nontrivial dependencies, including the three given ones and eleven derived dependencies. They are: C A, C D,D A, AB D, AB C, AC D, BC A, BC D, BD A, BD C, CD A, ABC D, ABD C, and BCD A.We also learned that the three keys were AB, BC, and BD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. These are: C A, C D,D A, AC D, and CD A.One choice is to decompose using the violation C D. Using the above FDs, we get ACD andBC as decomposed relations. BC is surely in BCNF, since any two-attribute relation is.Using Algorithm 3.12 to discover the projection of FDs on relation ACD, we discover that ACD is not in BCNF since C is its only key. However, D A is a dependency that holds inABCD and therefore holds in ACD. We must further decompose ACD into AD and CD. Thus, the three relations of the decomposition are BC, AD, and CD.Exercise 3.3.1bBy computing the closures of all 15 nonempty subsets of ABCD, we can find all the nontrivial FDs. They are BC, B D, AB C, AB D, BC D, BD C, ABC D and ABD C. Fromthe closures we can also deduce that the only key is AB. Thus, any dependency above that does not contain AB on the left is a BCNF violation. These are: B C, B D, BC D andBD C.One choice is to decompose using the violation B C. Using the above FDs, we get BCD andAB as decomposed relations. AB is surely in BCNF, since any two-attribute relation is.Using Algorithm 3.12 to discover the projection of FDs on relation BCD, we discover that BCD is in BCNF since B is its only key and the projected FDs all have B on the left side.Thus the two relations of the decomposition are AB and BCD.Exercise 3.3.1cIn the solution to Exercise 3.2.2(ii), we found that there are 12 nontrivial dependencies, including the four given ones and the eight derived ones. They are AB C, BC D, CD A,AD B, AB D, AD C, BC A, CD B, ABC D, ABD C, ACD B and BCD A.We also found out that the keys are AB, AD, BC, and CD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. However, all of the FDs contain a key on the left so there are no BCNF violations.No decomposition is necessary since all the FDs do not violate BCNF. Exercise 3.3.1dIn the solution to Exercise 3.2.2(iii), we found that there are 28 nontrivial dependencies, including the four given ones and the 24 derived ones. They are A C D, D A, A C, A D, B D, B A, C A, C B, D B, D C, AB C, AB D, ACWe also found out that the keys are A,B,C,D. Thus, any dependency above that does not have one of these attributes on the left is a BCNF violation. However, all of the FDs contain a key on the left so there are no BCNF violations.No decomposition is necessary since all the FDs do not violate BCNF. Exercise 3.3.1eBy computing the closures of all 31 nonempty subsets of ABCDE, we can find all the nontrivial FDs. They are AB C, DE C, B D, AB D, BC D, BE C, BE D, ABC D, ABD ABE C, ABE D, ADE C, BCE D, BDE C, ABCE D, and ABDE C. From the closures we can also deduce that the only key is ABE. Thus, any dependency above that does not containABE on the left is a BCNF violation. These are: AB C, DE C, B D, AB D, BC D, BEBE D, ABC D, ABD C, ADE C, BCE D and BDE C. One choice is to decompose using the violation ABC. Using the above FDs, we get ABCD and ABE as decomposed relations. Using Algorithm 3.12 to discover the projection of FDs on relation ABCD, we discover that ABCD is not in BCNF since AB is its only key and the FD B D follows for ABCD. Using violation BD to further decompose, we get BD and ABC asdecomposed relations. BD is in BCNF because it is a two-attribute relation. Using Algorithm 3.12 again, we discover that ABC is in BCNF since AB is the only key and AB Cis the only nontrivial FD. Going back to relation ABE, following Algorithm 3.12 tells us that ABE is in BCNFbecause there are no keys and no nontrivial FDs. Thus the three relations of the decomposition are ABC, BD and ABE. Exercise 3.3.1fBy computing the closures of all 31 nonempty subsets of ABCDE, we can find all thenontrivial FDs. They are: C B, C D, C E, D B, D E, AB C, AB D, AB E, AC B, AC D, AC E, AD B, AD C, AD E, BC D, BC E, BD E, CD B, CD E, CE B, CE D, DE B, ABC D, ABC E, ABD C, ABD E, ABE C, ABE D, ACD B, ACD E, ACE B, ACE D, ADE B, ADE C, BCD E, BCE D, CDE B, ABCD E, ABCE D, ABDE C and ACDE B. From the closures we can also deduce that the keys are AB, AC and AD. Thus, any dependency above that does not contain one of the above pairs on the left is a BCNF violation. These are: CB, C D,B, B C,B, AC D, AD B, AD C, BC A, BC D, BD A, BD C, CD A, CD B, ABC D, ABD C, ACDB and BCD A.C,C,C E,D B, D E, BC D, BC E, BD E, CD B, CD E, CE B, CE D, DE B, BCD E, BCE D and CDE B. One choice is to decompose using the violatio n DB. Us ing the above FDs, we get BDE andABC as decomposed relations. Using Algorithm 3.12 to discover the projection of FDs on relation BDE, we discover that BDE is in BCNF since D, BD, DE are the only keys and all the projected FDs con tain D, BD, or DE in the left side. Goi ng back to relati on ABC, following Algorithm 3.12 tells us that ABC is not in BCNF because since AB and AC are its on ly keys and the FD C B follows for ABC. Usi ng violatio n C B to further decompose, weget BC and AC as decomposed relati ons. Both BC and AC are in BCNF because they are two- attribute relations. Thus the three relations of the decomposition are BDE, BC and AC. Exercise 3.3.2Yes, we will get the same result. Both A B and A BC have A on the left side and part ofthe process of decompositi on in volves finding {A}+to form one decomposed relati on and Aplus the rest of the attributes not in {A} +as the sec ond relati on. Both cases yield thesame decomposed relati ons. Exercise 3.3.3Yes, we will still get the same result. Both A part of the process of decompositi on in volves finding {A} relation and A plus the rest of the attributes not in {A} cases yield the same decomposed relati ons. Exercise 3.3.4This is take n from Example 3.21 pg. 95.Suppose that an in sta nee of relati on R only contains two tuples.The projections of R onto the relations with schemas {A,B} and {B,C} are:B and A BC have A on the left side and +to form one decomposed +as the sec ond relati on. BothThe result of the natural join is not equal to the original relation R.Exercise 3.4.1aSince there is not an un subscripted row, the decompositi on for R is no t lossless for this set of FDs.We can use the final tableau as an in sta nee of R as an example for why the join is not lossless. The projected relati ons are:This is the in itialA.The joined relati on is:Exercise 3.4.1bThis is the in itial tableau:This is the final tableau after applying FDs ACSince there is an un subscripted row, the decompositi on for R is lossless for this set of FDs. Exercise 3.4.1cThis is the in itial tableau:E and BC DD, D E and B D.Since there is an un subscripted row, the decompositi on for R is lossless for this set of FDs.Exercise 3.4.1dThis is the in itial tableau:This is the final tableau after applyi ng FDs A D, CD E and E DSince there is an un subscripted row, the decompositi on for R is lossless for this set of FDs.Exercise 3.4.2When we decompose a relati on into BCNF, we will project the FDs onto the decomposed relati ons to get new sets of FDs. These depe nden cies are preserved if the union of these new sets is equivale nt to the origi nal set of FDs.For the FDs of 3.4.1a, the depe nden cies are not preserved. The union of the new sets of FDs is CE A. However, the FD B E is not in the union and cannot be derived. Thus the two sets of FDs are not equivale nt.For the FDs of 3.4.1b, the depe nden cies are preserved. The union of the new sets of FDs is AC E and BC D. This is precisely the same as the origi nal set of FDs and thus the two sets of FDs are equivale nt.For the FDs of 3.4.1c, the dependencies are not preserved. The union of the new sets ofFDs is B D and A E. The FDs A D and D E are not in the union and cannot be derived. Thus the two sets of FDs are not equivalent.For the FDs of 3.4.1d, the dependencies are not preserved. The union of the new sets ofFDs is AC E. However, the FDs A D, CD E and E D are not in the union and cannot bederived. Thus the two sets of FDs are not equivalent.Exercise 3.5.1aIn the solution to Exercise 3.3.1a we found that there are 14 nontrivial dependencies.They are: C A, C D, D A, AB D, AB C, AC D, BC A, BC D, BD A, BD C, CD A, ABC D, ABD C, and BCD A.We also learned that the three keys were AB, BC, and BD. Since all the attributes on the right sides of the FDs are prime, there are no 3NF violations.Since there are no 3NF violations, it is not necessary to decompose the relation.Exercise 3.5.1bIn the solution to Exercise 3.3.1b we found that there are 8 nontrivial dependencies.They are B C, B D, AB C, AB D, BC D, BD C, ABC D and ABD C.We also found out that the only key is AB. FDs where the left side is not a superkey or the attributes on the right are not part of some key are 3NF violations. The 3NF violations are B C, B D, BC D and BD C.Using algorithm 3.26, we can decompose into relations using the minimal basis B C andB D. The resulting decomposed relations would be BC and BD. However, none of these two sets of attributes is a superkey. Thus we add relation AB to the result. The final set of decomposed relations is BC, BD and AB.Exercise 3.5.1cIn the solution to Exercise 3.3.1c we found that there are 12 nontrivial dependencies.They are AB C, BC D, CD A, AD B, AB D, AD C, BC A, CD B, ABC D, ABD C, ACD B and BCD A.We also found out that the keys are AB, AD, BC, and CD. Since all the attributes on the right sides of the FDs are prime, there are no 3NF violations.Since there are no 3NF violations, it is not necessary to decompose the relation.Exercise 3.5.1d In the solution to Exercise 3.3.1d we found that there are 28 nontrivial dependencies.They are A B, B C, C D, D A, A C, A D, B D, B A, C A, C B, D B, D C, AB C, AB D, AC B, AC D, AD B, AD C, BC A, BC D, BD A, BD C, CD A, CD B, ABC D, ABD C, ACD B and BCD A.We also found out that the keys are A,B,C,D. Since all the attributes on the right sidesof the FDs are prime, there are no 3NF violations.Since there are no 3NF violations, it is not necessary to decompose the relation.Exercise 3.5.1eIn the solution to Exercise 3.3.1e we found that there are 16 nontrivial dependencies.They are AB C, DE C, B D, AB D, BC D, BE C, BE D, ABC D, ABD C, ABE C, ABE D, ADE C, BCE D, BDE C, ABCE D, and ABDE C.We also found out that the only key is ABE. FDs where the left side is not a superkey orthe attributes on the right are not part of some key are 3NF violations. The 3NF violations are AB C, DE C, B D, AB D, BC D, BE C, BE D, ABC D, ABD C, ADE C,BCE D and BDE C.Using algorithm 3.26, we can decompose into relations using the minimal basis AB C,DE C and B D. The resulting decomposed relations would be ABC, CDE and BD. However, none of these three sets of attributes is a superkey. Thus we add relation ABE to the result. The final set of decomposed relations is ABC, CDE, BD and ABE.Exercise 3.5.1fIn the solution to Exercise 3.3.1f we found that there are 41 nontrivial dependencies.They are: C B, C D, C E, D B, D E, AB C, AB D, AB E, AC B, AC D, AC E, AD B, AD C, AD E, BC D, BC E, BD E, CD B, CD E, CE B, CE D, DE B, ABC D, ABC E, ABD C, ABD E, ABE C, ABE D, ACD B, ACD E, ACE B, ACE D, ADE B, ADE C, BCD E, BCE D, CDE B, ABCD E, ABCE D, ABDE C and ACDE B.We also found out that the keys are AB, AC and AD. FDs where the left side is not a superkey or theattributes on the right are not part of some key are 3NF violations. The 3NF violations are C E, D E, BC E, BD E, CD E and BCD E.Using algorithm 3.26, we can decompose into relations using the minimal basis AB C, C D,D B and D E. The resulting decomposed relations would be ABC, CD, BD and DE. Since relation ABC contains a key, we can stop with the decomposition. The final set of decomposed relations is ABC, CD, BD and DE.Exercise 3.5.2aThe usual procedure to find the keys would be to take the closure of all 63 nonempty subsets. However, if we notice that none of the right sides of the FDs containsattributes H and S. Thus we know that attributes H and S must be part of any key. We eventually will find out that HS is the only key for the Courses relation.Exercise 3.5.2bThe first step to verify that the given FDs are their own minimal basis is to check to see if any of the FDs can be removed. However, if we remove any one of the five FDs, the remaining four FDs do not imply the removed FD.The second step to verify that the given FDs are their own minimal basis is to check to see if any of theleft sides of an FD can have one or more attributes removed without losing the dependencies. However, this is not the case for the four FDs that contain two attributes on the left side.Thus, the given set of FDs has been verified to be the minimal basis.Exercise 3.5.2cSince the only key is HS, the given set of FDs has some dependencies that violate 3NF. We also know that the given set of FDs is a minimal basis. Thus the decomposed relations are CT, HRC, HTR, HSR and CSG. Since the relation HSR contains a key, we do not need to add an additional relation. The final set of decomposed relations is CT, HRC, HTR, HSR and CSG.None of the decomposed relations violate BCNF. This can be verified by projecting the given set of FDs onto each of the decomposed relations. All of the projections of FDs have superkeys on their left sides.。

《数据库原理》第三章

• R（U，D，DOM，F）
– – – – – R 关系名 U 组成该关系的属性名集合 D 属性组U中属性所来自的域 DOM 属性向域的映象集合 F 属性间的数据依赖关系集合
• 关系模式通常可以简记为：R (U) 或R (A1，A2，…， An)
– R 关系名 – A1，A2，…，An 属性名
表3.3 “销售”关系
T002 T003 T004
商品名称康佳彩电SP21808
TCL彩电L19N6 康佳彩电P29AS281 长虹彩电PD29916
生产商康佳集团股份有限公司
TCL集团康佳集团股份有限公司四川长虹电器有限公司
销售价格 899
1490 1490 1690
T005
T006
长虹彩电CHD25800
TCL彩电HD29H73S
• 扩展的关系代数
– 广义投影 – 聚集运算 – 外联接
基本运算
• 选择运算
– 选择（Selection）运算是根据某些条件对关系进行水平分割，即选择符合条件的元组。条件用命题公式F表示，F中的运算对象是常量（用引号括起来）或元组分量（属性名或列的序号），设关系R关于公式F的选择操作用σF(R) ,形式定义如下：
1490 1490 1690
T005
T006
长虹彩电CHD25800
TCL彩电HD29H73S
四川长虹电器有限公司
TCL集团
1699
1790
• 列出“商品”关系中“所有商品代码及销售价格”。可以表示为：商品代码，销售价格（商品）。
商品代码 T001 T002 销售价格 899 1490
T003
• R×S={t│t＝＜tr,ts＞∧trR∧tsS} • 若R有n个元组，S有m个元组，则R×S有n×m个元组，n×m称为该笛卡儿积的基数。

数据库第三章习题参考答案范文大全

数据库第三章习题参考答案范文大全第一篇：数据库第三章习题参考答案3-2 对于教务管理数据库的三个基本表S(SNO,SNAME, SEX, AGE,SDEPT) SC(SNO,CNO,GRADE)C(CNO,CNAME,CDEPT,TNAME) 试用SQL的查询语句表达下列查询：⑴ 检索LIU老师所授课程的课程号和课程名。

⑵ 检索年龄大于23岁的男学生的学号和姓名。

⑶ 检索学号为200915146的学生所学课程的课程名和任课教师名。

⑷ 检索至少选修LIU老师所授课程中一门课程的女学生姓名。

⑸ 检索WANG同学不学的课程的课程号。

⑹ 检索至少选修两门课程的学生学号。

⑺ 检索全部学生都选修的课程的课程号与课程名。

⑻ 检索选修课程包含LIU老师所授课程的学生学号。

解：⑴ SELECT C#,CNAME FROM C WHERE TEACHER=’LIU’; ⑵ SELECT S#,SNAME FROM S WHERE AGE>23 AND SEX=’M’; ⑶ SELECT CNAME，TEACHER FROM SC,C WHERE SC.C#=C.C# AND S#=’200915146’ ⑷ SELECT SNAME (连接查询方式) FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C# AND TEACHER=’LIU’;或：SELECT SNAME (嵌套查询方式) FROM S WHERE SEX=’F’AND S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE TEACHER=’LIU’)) 或：SELECT SNAME (存在量词方式)SEX=’F’ AND FROM S WHERE SEX=’F’ AND EXISTS(SELECT* FROM SC WHERE SC.S#=S.S# AND EXISTS(SELECT * FROM C WHERE C.C#=SC.C# AND TEACHER=’LIU’)) ⑸ SELECT C# FROM C WHERE NOT EXISTS(SELECT * FROM S,SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=’WANG)); ⑹ SELECT DISTINCT X.S# FROM SC AS X，SC AS Y WHERE X.S#=Y.S# AND X.C#!=Y.C#; ⑺ SELECT C#.CNAME FROM C WHERE NOT EXISTS (SELECT * FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE S#=S.S# AND C#=C.C#)); ⑻ SELECT DISTINCT S# FROM SC AS X WHERE NOT EXISTIS (SELECT * FROM C WHERE TEACHER=’LIU’ AND NOT EXISTS (SELECT * FROM SC AS Y WHERE Y.S#=X.S# AND Y.C#=C.C#)); 3-3 试用SQL查询语句表达下列对3.2题中教务管理数据库的三个基本表S、SC、C查询：⑴ 统计有学生选修的课程门数。

数据库作业第三章习题答案

数据库作业第三章习题答案数据库作业第三章习题答案数据库作业是数据库课程中非常重要的一部分，通过完成作业可以帮助学生巩固和加深对数据库知识的理解和应用。

第三章习题主要涉及数据库设计和查询语言的使用。

在本篇文章中，我们将回答第三章习题，并探讨一些相关的概念和技巧。

1. 设计一个关系模式，用于存储学生的基本信息，包括学生编号、姓名、性别、年龄和专业。

请给出该关系模式的定义。

答案：学生（学生编号，姓名，性别，年龄，专业）2. 设计一个关系模式，用于存储课程的信息，包括课程编号、课程名称和学分。

请给出该关系模式的定义。

答案：课程（课程编号，课程名称，学分）3. 设计一个关系模式，用于存储学生选课的信息，包括学生编号、课程编号和成绩。

请给出该关系模式的定义。

答案：选课（学生编号，课程编号，成绩）4. 编写一个SQL查询语句，查询学生的姓名和年龄。

答案：SELECT 姓名, 年龄 FROM 学生;5. 编写一个SQL查询语句，查询选修了某门课程的学生的姓名和成绩。

答案：SELECT 学生.姓名, 选课.成绩FROM 学生, 选课WHERE 学生.学生编号 = 选课.学生编号AND 选课.课程编号 = '某门课程编号';6. 编写一个SQL查询语句，查询某个学生的选课情况，包括课程名称和成绩。

答案：SELECT 课程.课程名称, 选课.成绩FROM 课程, 选课WHERE 课程.课程编号 = 选课.课程编号AND 选课.学生编号 = '某个学生编号';通过以上习题的回答，我们可以看到数据库设计和查询语言的基本应用。

关系模式的定义是数据库设计的基础，它描述了数据表的结构和属性。

在查询语言的使用中，我们可以通过SELECT语句来检索和过滤数据，通过WHERE子句来指定查询条件。

除了上述习题的答案，我们还可以进一步探讨数据库设计的一些原则和技巧。

例如，为了提高数据库的性能和可扩展性，我们可以使用索引来加快数据的检索速度。

mysql 数据库程序设计教材

mysql 数据库程序设计教材MySQL数据库程序设计是计算机科学和软件工程领域中非常重要的一个方向。

本文将介绍MySQL数据库程序设计的基本概念和技术，以及一些常用的MySQL数据库程序设计方法。

MySQL是一个开源的关系型数据库管理系统（RDBMS），它使用SQL（结构化查询语言）来管理和操作数据库。

MySQL具有以下几个重要的特点：1.简单易用：MySQL的命令和语法相对简单，上手较快。

同时，MySQL配备了强大的图形化界面工具，如phpMyAdmin，可以帮助开发人员更方便地操作数据库。

2.可靠稳定：MySQL具有高可用性、高性能和高扩展性的特点。

它可以处理数十亿行数据，并支持大型企业级应用程序。

3.跨平台支持：MySQL可以在多个操作系统上运行，包括Windows、Linux、macOS等。

这使得MySQL成为了一个广泛使用的数据库管理系统。

MySQL数据库程序设计的基本概念和技术包括以下几个方面：1.数据库设计：在进行MySQL数据库程序设计之前，首先需要进行数据库的设计。

数据库设计包括确定数据表的结构、定义字段和字段类型、设置主键和外键、创建索引等。

好的数据库设计可以提高数据库的性能和可用性。

2.数据库连接：MySQL数据库连接是数据库程序设计的基础。

开发人员可以使用多种编程语言和技术来连接和操作MySQL数据库，如PHP、Java、Python等。

3. SQL查询和操作：在MySQL数据库程序设计中，使用SQL查询语言来操作数据库是非常常见的。

SQL查询包括增删改查等操作，开发人员可以根据需要使用不同的SQL语句来完成各种任务。

4.数据库事务：数据库事务是指一组操作，要么全部执行，要么全部取消。

MySQL数据库支持事务的原子性、一致性、隔离性和持久性（ACID）特性。

使用事务可以确保数据库中的数据一致性和完整性。

5.数据库优化：数据库优化是提高MySQL数据库性能的一种关键技术。

《计算机基础与Access数据库程序设计3》

6. 输入数据
日期输入形式：yyyy/yy/dd 1990/2/4
3.2.2 使用向导创建表（操作演示）
3.2.3 通过输入数据创建表（操作演示）
3.2.4 修改表结构 1. 打开表设计器三种打开方法：在数据库操作界面中选中“表”对象，在对象显示子窗口中单击选中待修改的表，“设计”工具栏按钮有效，单击该按钮即可在表设计器中打开相应的表。在数据库操作界面中选中“表”对象，右击对象显示子窗口中待修改的表，在弹出的快捷菜单中选择“设计视图”，同样可以在表设计器中打开相应的表。如果处于数据表视图，选择“视图”→“设计视图”命令即可切换到表设计视图状态。
3. 字段大小
文本、数字和自动编号等类型字段需要设置字段大小。对文本字段而言，字段大小为1~255，即该字段允许保存的最大字符个数。对于数字字段而言，其设置值可以从一个下拉列表中选择，包括：字节、整型、长整型、单精度型、双精度型、同步复制ID、小数。参阅表3-2 。
4. 设置主键 (操作演示) 5. 保存表 (操作演示)
《计算机基础与Access数据库程序设计》
第3章
Access数据库及数据库表
目
3.1 3.2 3.3 3.4
录
建立和操作Access数据库建立和操作Access数据库表 Access表数据处理建立Access表间的关系
3.1
建立和操作Access数据库 (操作演示)
3.1.1 建立空数据库 3.1.2 从已有模板建立数据库
(2) 输入掩码文本、数字、日期/时间等类型字段拥有该属性。输入掩码是一串具有特殊意义的字符，用于控制数据输入、减少输入错误。可以自定义掩码或利用向导自动生成掩码。常用的掩码字符如表3-15所示。掩码向导只针对文本和日期型字段，启动掩码向导操作步骤如下： ① 单击表设计器字段定义行； ② 再单击选中输入掩码属性，系统显示如图所示的向导按钮； ③ 单击向导按钮，即可打开输入掩码向导对话框。

数据库系统概论第三章

AB
其中A,B分别为R和S上度数相等且可比的属性组。 θ是比较运算符。连接运算从R和S的广义笛卡尔积 R×S中选取（R关系）在A属性组上的值与(S关系) 在B属性组上值满足比较关系θ的元组。
计算机科学与工程学院
举例
A a1 a1 a2 B b1 b2 b3 C
5 6 8
B
E
3 7
b1 b2
b3
数据库原理与应用
《数据库系统概论》第4版王珊萨师煊
第3章关系数据库

关系模型的三个要素
关系的数据结构
关系的操作
关系的完整性约束

关系代数
传统的集合运算专门的关系运算
计算机科学与工程学院
3.1 关系数据结构及形式化定义
1、关系模型的数据结构—关系
关系：二维表。
属性（字段）：二维表中的列。
（3）检索选修课程名为Maths的学生的学号与姓名（4）检索选修课程号为C2或C4的学生的学号（5）检索至少选修课程号为C2和C4的学生的学号（6）检索学习全部课程的学生的姓名（7）检索所学课程包含学生S3所学课程的学生的学号
计算机科学与工程学院
（1）检索学习课程号为C2的学生的学号和成绩
R÷ S
A a
B b
c
k
计算机科学与工程学院
设有三个关系：
S(SNO,SNAME,AGE,SEX) SC(SNO,CNO,GRADE)
C(CNO,CNAME,TEACHER)
试用关系代数表达式表示下列查询语句：（1）检索学习课程号为C2的学生的学号和成绩
（2）检索学习课程号为C2的学生的学号与姓名
a1的象集｛(b1,c2),(b2,c3),(b2,c1)｝

数据库讲稿演示第三章(课件)

数据库系统基础
5
➢② 由于数据的重复存储，会给更新带来麻烦。如果一位任三门课的教师改变了地址，三个元组的地址都要更新，一旦一个元组的地址末修改就会导致数据不一致。如果某个系改变办公地址，所要修改的数据量会更大。（更新异常）
➢③ 如果学校新调入一个教师，暂时末主讲任何课程。由于缺少关键字的一部分，而关键字不允许出现空值，新教师就不能插入到此关系中去。只有当他开设了课程之后才能插入，这是不合理的。（插入异常）
数据库讲稿演示第三章（课件）
§3.1规范化问题
数据库是一组相关数据的集合。它
不仅包括数据本身，而且包括关于数据之间的联系，即数据模型。给出一组数据，如何构造一个适合的数据模型，在关系数据库中应该组织成几个关系模式，每个关系模式包括那些属性。这是数据库逻辑设计要解决的问题。
数据库系统基础
001 马明教授 A1 D1 信息 L1 C3 DB OK 4
002 李露讲师 A2 D1 信息 L1 C3 DB 良 4
002 李露讲师 A2 D1 信息 L1 C4 VFP 良 4
003 陈伟教授 A3 D1 信息 L1 C4 VFP OK 4
003 陈伟教授 A3 D1 信息 L1 C1 C OK 3
数据库系统基础
7
➢教师(职工号，姓名，职称，住址，系号)
➢系(系号，系名，系址）
➢课程（课程号，课程名，学分)
➢授课(职工号，课程号，水平)
新关系模型包括四个关系模式，教师和系之间通过教师中的外关键字系号相联系；教师与课程之间多对多的联系通过授课中的外关键字职工号和课程号相联系。需要时再进行自然联接，则恢复了原来的关系。
数据库系统基础

数据库原理第三章课后习题答案

第三章作业一、试述SQL特点SQL集数据查询、数据操纵、数据定义和数据控制功能于一体，其主要特点包括以下几部分。

1.综合统一2.高度非过程化3.面向集合的操作方式4.以同一种语法结构提供多种使用方式5.语言简洁，易学易用二、设有两个关系S(A,B,C,D)和T(C,D,E,F)，写出与下列查询等价的SQL表达式(1)select A,B,S.C, S.D,E,Ffrom S,Twhere S.C=T.C(2)select * from S,Twhere S.C=T.C三、设关系RA B C10 NULL 2020 30 NULL写出查询语句SELECT * FROM R WHERE X的查询结果，其中X分别为1.1 A IS NULL;1.2 A>8 AND B<20;1.3 A>8 OR B<20;1.4 C+10>25;1.5 EXISTS (SELECT B FROM R WHERE A=10);use Rcreate table R(A tinyint primary key,B tinyint,C tinyint)1.11.21.31.41.5四、基于教材中的学生-课程数据库，用SQL完成如下查询：2.1 创建一张新表，记录每个学生的学号、选课门数和总学分数。

格式如下SCC(sno, totalCourse, totalCredit)并插入每个学生相应的数据。

create table SCC( sno char(10),totalcourse tinyint,totalcredit int)insertinto SCC(sno,totalcourse,totalcredit)select sc.sno,count(distinct o)as totalcourse,sum(ccredit)as totalcredit from sc,student,coursegroup by sc.snoselect*from SCC2.2、查询缺考和不及格课程多于3门的学生的学号和姓名select sc.sno,snamefrom student,scwhere exists(select snofrom scwhere grade<60 or grade=nullgroup by snohaving count(grade)>3)2.3 查询每个学生超过他自己选修课程平均成绩的课程号（写出3种以上类型的方法）(1)select cnofrom sc,(select sno,avg(grade)from sc group by sno)as avg_sc(avg_sno,avg_grade)where sc.sno=avg_sc.avg_sno and sc.grade>=avg_sc.avg_grade(2)select sno,cnofrom sc xwhere grade>=(select avg(grade)from sc ywhere y.sno=x.sno);2.4 查询同时选修了“数据库”和“数据结构”的学生的学号和姓名（写出5种以上类型方法）(1)select sno,snamefrom student,coursewhere cname='数据库'and sno in(select snofrom scwhere cname='数据结构')(2)select sc.sno,snamefrom student,course,scwhere student.sno=sc.sno and o=o and cname='数据库'intersectselect sc.sno,snamefrom student,course,scwhere student.sno=sc.sno and o=o and cname='数据结构';五、在上机实践过程中遇到过什么问题？解决方案是什么？。

Visual Foxpro 程序设计第三章20

上述第①步的操作也可以对数据库设计器击右键，选“添加表”，或在“数据库设计器”工具栏单击“添加表”工具按钮。若屏幕未出现“数据库设计器工具栏”，可在“显示”菜单下单击“工具栏…”选项后将其选出。
在数据库中建立新表
在数据库设计器中也可以直接建立新表。
菜单方式：
①打开数据库设计器。
②在“数据库”菜单下选“新建表（N）…”（也可以在快捷菜单或在设计器工具栏中选择）。
打开和关闭数据库设计器
打开数据库设计器
作用：将数据库文件调人内存，并打开数据库设计器窗口。
菜单方式
（1）“文件”菜单下单击“打开”项；
（2）在“打开”对话框中，文件类型选择“数据库（.DBC）”，然后在文件列单中选择要打开的数据库名，最后单击“确定”按钮。
向数据库中加入和删除表
对于数据库来说，可以向其中加入表，也可将其中的表移出数据库或删除。
带条件（WHERE）的查询语句
格式：SELECT [ALL | DISTINCT] <字段列表>
FROM <表>
[WHERE <条件表达式> ]
功能：从一个表中查询满足条件的数据。
说明：<条件表达式>由一系列用AND或OR连接的条件表达式组成，条件表达式的格式可以是以下几种：
<字段名1><关系运算符><字段名2>。
讲稿
授课内容
备注
说明：
（1）双击关联线可设置两表间的关联关系。
（2）单击关联线并按Delete键，可删除该关联线，即取消两表间的关联关系。（也可右击关联线，然后在快捷菜单中选“删除关系”）。
三、数据库添入项目管理器
数据库既可以通过项目管理器建立，也可在建成后添入其中，以方便随后的管理与开发。

数据库第三章

23:40
教师信息关系模式： Teacher(teacher_id,teacher_name,dept_id,schooling,title,sex,ag e,resumd,course_name,period,c_type) 教师任课信息关系模式： Teach(teacher_id,course_id) 成绩信息关系模式： score(student_id,course_id,grade)
1）实体具有描述信息，而属性没有。即属性必须是不可分的数据项，不能再由另一些属性组成。 2）属性不能与其他实体具有联系，联系只能发生在实体之间。
23:40
成绩管理系统数据库中的实体与实体之间的局部E-R图如下。
1．教师实体与部门实体之间的E-R图如图3.2。
图3.2
23:40
2.班级实体与部门实体之间的E-R图如图3.3。
成绩管理系统后台数据库全局的实体联系图如图3.7
图3.7
23:40
3.2.3 数据库逻辑结构设计
概念设计中得到的E-R图是由实体、属性和联系组成的，而关系数据库逻辑设计的结果是一组关系模式的集合。所以将E-R图转换为关系模型实际上就是将实体、属性和联系转换成关系模式。在转换中要遵循以下原则： 1）一个实体型转换为一个关系模式。实体的属性就是关系的属性。实体的码就是关系的码。
23:40
3．逻辑结构设计阶段（关系模型）逻辑设计是将上一步所得到的概念模型转换为某个DBMS 所支持的数据模型，并对其进行优化。
4．物理设计阶段（库、表）物理设计是为逻辑数据模型建立一个完整的能实现的数据库结构，包括存储结构和存取方法。
5．数据库实施阶段根据物理设计的结果把原始数据装入数据库，建立一个具体的数据库并编写和调试相应的应用程序。应用程序的开发目标是开发一个可依赖的有效的数据库存取程序，来满足用户的处理要求。