4£® д³öʵÏÖÏÂÁвÙ×÷µÄSQLÓï¾ä£¬Ö´ÐÐÕâЩÓï¾ä£¬²¢ÔÚSSMS¹¤¾ßÖй۲ìÓï¾äÖ´Ðнá¹û¡£ £¨1£© ÔÚStudent±íµÄSdeptÁÐÉϽ¨Á¢Ò»¸ö°´½µÐòÅÅÐòµÄ·Ç¾Û¼¯Ë÷Òý£¬Ë÷ÒýÃûΪ£ºIdx_Sdept¡£
´ð£ºCreate index Idx_Sdept on student(Sdept DESC)
£¨2£© ÔÚStudent±íµÄSnameÁÐÉϽ¨Á¢Ò»¸öΨһµÄ·Ç¾Û¼¯Ë÷Òý£¬Ë÷ÒýÃûΪ£ºIdx_Sname¡£ ´ð£ºCreate unique index Idx_Sname on student(Sname)
£¨3£© ÔÚCourse±íÉÏΪCnameÁн¨Á¢Ò»¸ö·Ç¾Û¼¯Ë÷Òý£¬Ë÷ÒýÃûΪ£º Idx_Cname ´ð£ºCreate index Idx_Cname on Course(Cname)
£¨4£© ÔÚSC±íÉÏΪSnoºÍCno½¨Á¢Ò»¸ö×éºÏµÄ·Ç¾Û¼¯Ë÷Òý£¬Ë÷ÒýÃûΪ£ºIdx_SnoCno¡£ ´ð£ºCreate index Idx_SnoCno on SC(Sno,Cno)
£¨5£© ɾ³ýÔÚSnameÁÐÉϽ¨Á¢µÄIdx_SnameË÷Òý¡£ ´ð£ºdrop index Idx_Sname on Student
¡¤21¡¤ 21
¡¤22¡¤
µÚ6Õ ¹ØÏµÊý¾ÝÀíÂÛ
Ò»£®Ñ¡ÔñÌâ
1£®¶Ô¹ØÏµÄ£Ê½½øÐй淶»¯µÄÖ÷ҪĿµÄÊÇ
B
A£®Ìá¸ßÊý¾Ý²Ù×÷ЧÂÊ B£®Î¬»¤Êý¾ÝµÄÒ»ÖÂÐÔ
C£®¼ÓÇ¿Êý¾ÝµÄ°²È«ÐÔ D£®ÎªÓû§Ìṩ¸ü¿ì½ÝµÄÊý¾Ý²Ù×÷
2£®¹ØÏµÄ£Ê½ÖеIJåÈëÒì³£ÊÇÖ¸
D
A£®²åÈëµÄÊý¾ÝÎ¥·´ÁËʵÌåÍêÕûÐÔÔ¼Êø B£®²åÈëµÄÊý¾ÝÎ¥·´ÁËÓû§¶¨ÒåµÄÍêÕûÐÔÔ¼Êø C£®²åÈëÁ˲»¸Ã²åÈëµÄÊý¾Ý D£®Ó¦¸Ã±»²åÈëµÄÊý¾Ý²»Äܱ»²åÈë
3£®Èç¹ûÓк¯ÊýÒÀÀµX¡úY£¬²¢ÇÒ¶ÔXµÄÈÎÒâÕæ×Ó¼¯X¡¯£¬¶¼ÓÐX¡¯
Y£¬Ôò³Æ C
A£®XÍêÈ«º¯ÊýÒÀÀµÓÚY B£®X²¿·Öº¯ÊýÒÀÀµÓÚY C£®YÍêÈ«º¯ÊýÒÀÀµÓÚX
D£®Y²¿·Öº¯ÊýÒÀÀµÓÚX
4£®Èç¹ûÓк¯ÊýÒÀÀµX¡úY£¬²¢ÇÒ¶ÔXµÄij¸öÕæ×Ó¼¯X¡¯£¬ÓÐX¡¯¡úY³ÉÁ¢£¬Ôò³Æ B
A£®YÍêÈ«º¯ÊýÒÀÀµÓÚX B£®Y²¿·Öº¯ÊýÒÀÀµÓÚX C£®XÍêÈ«º¯ÊýÒÀÀµÓÚY
D£®X²¿·Öº¯ÊýÒÀÀµÓÚY
5£®ÈôX¡úYºÍY¡úZÔÚ¹ØÏµÄ£Ê½RÉϳÉÁ¢£¬ÔòX¡úZÔÚRÉÏÒ²³ÉÁ¢¡£¸ÃÍÆÀí¹æÔò³ÆÎª C
A£®×Ô·´¹æÔò B£®Ôö¹ã¹æÔò C£®´«µÝ¹æÔò
D£®Î±´«µÝ¹æÔò
6£®Èô¹ØÏµÄ£Ê½RÖÐÊôÐÔA½ö³öÏÖÔÚº¯ÊýÒÀÀµµÄ×󲿣¬ÔòAΪ A
A£®LÀàÊôÐÔ B£®RÀàÊôÐÔ C£®NÀàÊôÐÔ
D£®LRÀàÊôÐÔ
7£®Èô¹ØÏµÄ£Ê½RÖÐÊôÐÔAÊÇNÀàÊôÐÔ£¬ÔòA D
A£®Ò»¶¨²»°üº¬ÔÚRÈκκòÑ¡ÂëÖÐ B£®¿ÉÄܰüº¬Ò²¿ÉÄܲ»°üº¬ÔÚRµÄºòÑ¡ÂëÖÐ C£®Ò»¶¨°üº¬ÔÚRµÄij¸öºòÑ¡ÂëÖÐ D£®Ò»¶¨°üº¬ÔÚRµÄÈκκòÑ¡ÂëÖÐ
8£®ÉèFÊÇij¹ØÏµÄ£Ê½µÄ¼«Ð¡º¯ÊýÒÀÀµ¼¯¡£ÏÂÁйØÓÚFµÄ˵·¨£¬´íÎóµÄÊÇ
B
A£®FÖÐÿ¸öº¯ÊýÒÀÀµµÄÓÒ²¿¶¼±ØÐëÊǵ¥¸öÊôÐÔ B£®FÖÐÿ¸öº¯ÊýÒÀÀµµÄ×󲿶¼±ØÐëÊǵ¥¸öÊôÐÔ C£®FÖв»ÄÜÓÐÈßÓàµÄº¯ÊýÒÀÀµ
D£®FÖÐÿ¸öº¯ÊýÒÀÀµµÄ×󲿲»ÄÜÓÐÈßÓàÊôÐÔ
9£®ÓйØÏµÄ£Ê½£ºÑ§Éú£¨Ñ§ºÅ£¬ÐÕÃû£¬ËùÔÚϵ£¬ÏµÖ÷ÈΣ©£¬ÉèÒ»¸öϵֻÓÐÒ»¸öϵÖ÷ÈΣ¬Ôò¸Ã¹ØÏµÄ£Ê½ÖÁÉÙÊôÓÚA£®µÚÒ»·¶Ê½ B£®µÚ¶þ·¶Ê½ C£®µÚÈý·¶Ê½
D£®BC·¶Ê½
10£®ÉèÓйØÏµÄ£Ê½R(X, Y, Z)£¬ÆäF={Y¡úZ, Y¡úX, X¡úYZ}£¬Ôò¸Ã¹ØÏµÄ£Ê½ÖÁÉÙÊôÓÚ
D
A£®µÚÒ»·¶Ê½
B£®µÚ¶þ·¶Ê½ 22
B
C£®µÚÈý·¶Ê½
11£®ÏÂÁйØÓÚ¹ØÏµÄ£Ê½Ó뷶ʽµÄ˵·¨£¬´íÎóµÄÊÇ
A£®ÈκÎÒ»¸öÖ»°üº¬Á½¸öÊôÐԵĹØÏµÄ£Ê½Ò»¶¨ B£®ÈκÎÒ»¸öÖ»°üº¬Á½¸öÊôÐԵĹØÏµÄ£Ê½Ò»¶¨ C£®ÈκÎÒ»¸öÖ»°üº¬Á½¸öÊôÐԵĹØÏµÄ£Ê½Ò»¶¨ D£®ÈκÎÒ»¸öÖ»°üº¬Èý¸öÊôÐԵĹØÏµÄ£Ê½Ò»¶¨
D£®BC·¶Ê½ D ÊôÓÚ3NF ÊôÓÚBCNF ÊôÓÚ2NF ÊôÓÚ3NF
¡¤23¡¤ 12£®ÓйØÏµÄ£Ê½£º½èÊ飨ÊéºÅ£¬ÊéÃû£¬¿â´æÁ¿£¬¶ÁÕߺţ¬½èÊéÈÕÆÚ£¬»¹ÊéÈÕÆÚ)£¬ÉèÒ»¸ö¶ÁÕß¿ÉÒÔ¶à´Î½èÔÄͬһ±¾Ê飬µ«¶ÔÒ»ÖÖÊ飨ÓÃÊéºÅΨһ±êʶ£©²»ÄÜͬʱ½è¶à±¾¡£¸Ã¹ØÏµÄ£Ê½µÄÖ÷ÂëÊÇ
A£®£¨ÊéºÅ£¬¶ÁÕߺţ¬½èÊéÈÕÆÚ£© B£® £¨ÊéºÅ£©
¶þ£®Ìî¿ÕÌâ
1£®ÔÚ¹ØÏµÄ£Ê½RÖУ¬ÈôÊôÐÔAÖ»³öÏÖÔÚº¯ÊýÒÀÀµµÄÓÒ²¿£¬ÔòAÊÇ_____ÀàÊôÐÔ¡£ R 2£®Èô¹ØÏµÄ£Ê½R¡Ê2NF£¬ÔòRÖÐÒ»¶¨²»´æÔÚ·ÇÖ÷ÊôÐÔ¶ÔÖ÷ÂëµÄ_____º¯ÊýÒÀÀµ¡£ ²¿·Ö 3£®Èô¹ØÏµÄ£Ê½R¡Ê3NF£¬ÔòRÖÐÒ»¶¨²»´æÔÚ·ÇÖ÷ÊôÐÔ¶ÔÖ÷ÂëµÄ_____º¯ÊýÒÀÀµ¡£ ´«µÝ
4£®ÉèÓйØÏµÄ£Ê½X(S, SN, D)ºÍY(D, DN, M)£¬XµÄÖ÷ÂëÊÇS£¬YµÄÖ÷ÂëÊÇD£¬ÔòDÔÚ¹ØÏµÄ£Ê½XÖб»³ÆÎª_____¡£ ÍâÂë
5£®ÉèÓйØÏµÄ£Ê½R(U, F)£¬U={X, Y, Z, W}£¬F={XY¡úZ, W¡úX}£¬Ôò(ZW)+ = _____£¬RµÄºòÑ¡ÂëΪ_____£¬¸Ã¹ØÏµÄ£Ê½ÊôÓÚ_____·¶Ê½¡£XZW YW
µÚÒ»
6£®ÔÚ¹ØÏµÄ£Ê½RÖУ¬ÈôÊôÐÔA²»ÔÚÈκκ¯ÊýÒÀÀµÖгöÏÖ£¬ÔòAÊÇ_____ÀàÊôÐÔ¡£ N
7£®ÔÚ¹ØÏµÄ£Ê½RÖУ¬ÈôÓÐX¡úY£¬ÇÒZ?Y£¬ÔòX¡úZÔÚRÉÏÒ²³ÉÁ¢£¬¸ÃÍÆÀí¹æÔòΪArmstrong¹«ÀíϵͳÖеÄ_____¡£·Ö½â¹æÔò
8£®¸ù¾ÝArmstrong¹«ÀíϵͳÖеÄ×Ô·´¹æÔò£¬¶Ô¹ØÏµÄ£Ê½RÖеÄÊôÐÔ¼¯X£¬ÈôY?X£¬ÔòÒ»¶¨ÓÐ_____¡£X¡úY 9£®¹ØÏµÊý¾Ý¿âÖеĹØÏµ±íÖÁÉÙ¶¼Âú×ã_____·¶Ê½ÒªÇó¡£ µÚÒ»
10£®¹ØÏµ¹æ·¶»¯µÄ¹ý³ÌÊǽ«¹ØÏµÄ£Ê½´ÓµÍ·¶Ê½¹æ·¶»¯µ½¸ß·¶Ê½µÄ¹ý³Ì£¬Õâ¸ö¹ý³Ìʵ¼ÊÉÏÊÇͨ¹ý_____ʵÏֵġ£ ģʽ·Ö½â
11£® Èô¹ØÏµÄ£Ê½RµÄÖ÷ÂëÖ»°üº¬Ò»¸öÊôÐÔ£¬ÔòRÖÁÉÙÊôÓÚµÚ_____·¶Ê½¡£ ¶þ
12£®Èô¹ØÏµÄ£Ê½RÖÐËùÓеķÇÖ÷ÊôÐÔ¶¼ÍêÈ«º¯ÊýÒÀÀµÓÚÖ÷Â룬ÔòRÖÁÉÙÊôÓÚµÚ_____·¶Ê½¡£ ¶þ
Èý£®¼ò´ðÌâ
1£® ¹ØÏµ¹æ·¶»¯ÖеIJÙ×÷Òì³£ÓÐÄÄЩ£¿ËüÊÇÓÉʲôÒýÆðµÄ£¿½â¾öµÄ°ì·¨ÊÇʲô£¿ 2£®µÚÒ»·¶Ê½¡¢µÚ¶þ·¶Ê½ºÍµÚÈý·¶Ê½¹ØÏµÄ£Ê½µÄ¶¨Òå·Ö±ðÊÇʲô£¿ 3£®Ê²Ã´ÊDz¿·Öº¯ÊýÒÀÀµ£¿Ê²Ã´ÊÇ´«µÝº¯ÊýÒÀÀµ£¿Çë¾ÙÀý˵Ã÷¡£ 4£®µÚÈý·¶Ê½µÄ¹ØÏµÄ£Ê½ÊÇ·ñÒ»¶¨²»°üº¬²¿·Öº¯ÊýÒÀÀµ¹ØÏµ£¿ 5£®ÉèÓйØÏµÄ£Ê½R(A, B, C, D)£¬F={D¡úA, D¡úB} £¨1£©ÇóD¡£
£¨2£©ÇóRµÄÈ«²¿ºòÑ¡Âë¡£ ´ð£º£¨1£©D=DAB
£¨2£©ÒòΪDÊÇLÀàÊôÐÔ£¬Òò´ËDÒ»¶¨³öÏÖÔÚRµÄÈκκòÑ¡ÂëÖУ» ÒòΪCÊÇNÀàÊôÐÔ£¬Òò´ËDÒ»¶¨³öÏÖÔÚRµÄÈκκòÑ¡ÂëÖУ» (CD) = CDAB = RµÄÈ«²¿ÊôÐÔ£¬Òò´ËRµÄΨһºòÑ¡ÂëÊÇCD¡£
6.ÉèÓйØÏµÄ£Ê½R(W, X, Y, Z)£¬F={X¡úZ, WX¡úY}£¬¸Ã¹ØÏµÄ£Ê½ÊôÓÚµÚ¼¸·¶Ê½£¬Çë˵Ã÷ÀíÓÉ¡£
½â£ºRÊÇ1NF£¬RµÄºòÑ¡ÂëΪWX,Òò´ËY,ZÊÇ·ÇÖ÷ÊôÐÔ£¬ÓÖÓÉÓÚ´æÔÚX¡úZ£¬Òò´ËFÖдæÔÚ·ÇÖ÷ÊôÐÔ¶ÔÖ÷ÂëµÄ²¿·Öº¯Êý
23
+
++
A
B£®£¨ÊéºÅ£¬¶ÁÕߺţ© D£®£¨¶ÁÕߺţ©
¡¤24¡¤ ÒÀÀµ£¬Òò´ËR²»ÊÇ2NF¡£
7£®ÉèÓйØÏµÄ£Ê½R(A, B, C, D)£¬F = {A¡úC, C¡úA, B¡úAC, D¡úAC} £¨1£©ÇóB+£¬(AD)+¡£
£¨2£©ÇóRµÄÈ«²¿ºòÑ¡Â룬ÅжÏRÊôÓÚµÚ¼¸·¶Ê½¡£ £¨3£©ÇóFµÄ¼«Ð¡º¯ÊýÒÀÀµ¼¯Fmin¡£ ´ð£º
£¨1£© B+ = BAC
(AD)+ = ADC
£¨2£© ÓÉÓÚB¡¢DÊÇLÀàÊôÐÔ£¬Òò´ËB¡¢D»áÔÚÈκκòÑ¡ÂëÖУ»
(BD)+ = BDAC = RµÄÈ«²¿ÊôÐÔ£¬Òò´ËRµÄºòÑ¡ÂëÊÇBD¡£ RÊǵÚ1·¶Ê½£¬ÒòΪ´æÔÚ²¿·Öº¯ÊýÒÀÀµ£º
£¨3£©¢Ù ½«ËùÓк¯ÊýÒÀÀµµÄÓұ߸ÄΪµ¥¸öÊôÐÔ£¬µÃµ½£ºB¡úAC, D¡úAC
F1 = { A¡úC, C¡úA, B¡úA, B¡úC, D¡úA, D¡úC }
¢Ú È¥µôº¯ÊýÒÀÀµ×󲿵ĶàÓàÊôÐÔ£º¸Ã¹ØÏµÄ£Ê½µÄÈ«²¿º¯ÊýÒÀÀµ¼¯×󲿾ùΪһ¸öÊôÐÔ£¬Òò´Ë²»´æÔÚ×ó²¿ÓжàÓà
ÊôÐÔµÄÇé¿ö¡£ ¢Û È¥µô¶àÓàµÄº¯ÊýÒÀÀµ¡£
È¥µôA¡úC£¬µÃµ½F2 = { C¡úA, B¡úA, B¡úC, D¡úA, D¡úC }
A+F2 = AC£¬°üº¬C£¬Òò´ËΪ¶àÓຯÊýÒÀÀµ£¬Ó¦È¥µô£»
È¥µôC¡úA£¬µÃµ½F3 = { B¡úA, B¡úC, D¡úA, D¡úC }
C+F3 = C£¬²»°üº¬A£¬Òò´ËC¡úA²»ÊǶàÓຯÊýÒÀÀµ£»
È¥µôB¡úA£¬µÃµ½F4 = { C¡úA, B¡úC, D¡úA, D¡úC }
B+F4 = BCA£¬°üº¬A£¬Òò´ËB¡úAÊǶàÓຯÊýÒÀÀµ£¬Ó¦È¥µô£»
È¥µôB¡úC£¬µÃµ½F5 = { C¡úA, D¡úA, D¡úC }
B+F5 = B£¬²»°üº¬C£¬Òò´ËB¡úC²»ÊǶàÓຯÊýÒÀÀµ£»
È¥µôD¡úA£¬µÃµ½F6 = { C¡úA, B¡úC, D¡úC }
D+F5 = DCA£¬²»°üº¬A£¬Òò´ËD¡úAÊǶàÓຯÊýÒÀÀµ£¬Ó¦È¥µô£»
È¥µôD¡úC£¬µÃµ½F7 = { C¡úA, B¡úC }
D+F5 = D£¬²»°üº¬C£¬Òò´ËD¡úC²»ÊǶàÓຯÊýÒÀÀµ¡£
ËÄ£® Éè¼ÆÌâ
1£® ÉèÓйØÏµÄ£Ê½£ºÑ§ÉúÐ޿Σ¨Ñ§ºÅ£¬ÐÕÃû£¬ËùÔÚϵ£¬ÐԱ𣬿γ̺ţ¬¿Î³ÌÃû£¬Ñ§·Ö£¬³É¼¨£©¡£ÉèÒ»¸öѧÉú¿ÉÒÔÑ¡¶à
Ãſγ̣¬Ò»Ãſγ̿ÉÒÔ±»¶àÃûѧÉúÑ¡¡£Ò»¸öѧÉúÓÐΨһµÄËùÔÚϵ£¬Ã¿ÃſγÌÓÐΨһµÄ¿Î³ÌÃûºÍѧ·Ö¡£Ã¿¸öѧÉú¶ÔÿÃſγÌÓÐΨһµÄ³É¼¨¡£ £¨1£© ÇëÖ¸³ö´Ë¹ØÏµÄ£Ê½µÄºòÑ¡Âë¡£ £¨2£© д³ö¸Ã¹ØÏµÄ£Ê½µÄ¼«Ð¡º¯ÊýÒÀÀµ¼¯¡£
£¨3£© ¸Ã¹ØÏµÄ£Ê½ÊôÓÚµÚ¼¸·¶Ê½£¿²¢¼òµ¥ËµÃ÷ÀíÓÉ¡£
£¨4£© Èô²»ÊǵÚÈý·¶Ê½µÄ£¬Ç뽫Æä¹æ·¶»¯ÎªµÚÈý·¶Ê½¹ØÏµÄ£Ê½£¬²¢Ö¸³ö·Ö½âºóµÄÿ¸ö¹ØÏµÄ£Ê½µÄÖ÷ÂëºÍÍâÂë¡£ ´ð£º£¨1£©ºòÑ¡Â룺£¨Ñ§ºÅ£¬¿Î³ÌºÅ£©
£¨2£©Ñ§ºÅ¡úÐÕÃû£¬Ñ§ºÅ¡úËùÔÚϵ£¬Ñ§ºÅ¡úÐԱ𣬿γ̺šú¿Î³ÌÃû£¬¿Î³ÌºÅ¡úѧ·Ö£¬ £¨Ñ§ºÅ£¬¿Î³ÌºÅ£©¡ú³É¼¨
£¨3£©ÊôÓÚµÚ¶þ·¶Ê½£¬ÒòΪ´æÔÚ²¿·Öº¯ÊýÒÀÀµ£ºÑ§ºÅ¡úÐÕÃû¡£ £¨4£©µÚÈý·¶Ê½¹ØÏµÄ£Ê½£º
ѧÉú£¨Ñ§ºÅ£¬ÐÕÃû£¬ËùÔÚϵ£¬ÐԱ𣩠¿Î³Ì£¨¿Î³ÌºÅ£¬¿Î³ÌÃû£¬Ñ§·Ö£©
24
×îÖÕFmin =£ûC¡úA, B¡úC, D¡úC£ý