Êý¾Ý¿âϵͳ¸ÅÂÛÊÔÌâ¼°´ð°¸ °æ ÏÂÔØ±¾ÎÄ

11¡¢¹ØÏµÄ£Ê½µÄ·Ö½â B ¡£

A£®Î©Ò» B£®²»Î©Ò»

12¡¢¸ù¾Ý¹ØÏµÊý¾Ý¿â¹æ·¶»¯ÀíÂÛ£¬¹ØÏµÊý¾Ý¿âÖеĹØÏµÒªÂú×ãµÚÒ»·¶Ê½¡£ÏÂÃæ¡°²¿ÃÅ¡±¹ØÏµÖУ¬ÒòÄĸöÊôÐÔ¶øÊ¹Ëü²»Âú×ãµÚÒ»·¶Ê½? B ¡£

²¿ÃÅ(²¿Ãźţ¬²¿ÃÅÃû£¬²¿ÃųÉÔ±£¬²¿ÃÅ×ܾ­Àí) A£®²¿ÃÅ×ܾ­Àí ¶þ¡¢Ìî¿ÕÌâ

1¡¢ÔÚ¹ØÏµA(S£¬SN£¬D)ºÍB(D£¬CN£¬NMÖУ¬AµÄÖ÷¼üÊÇS£¬BµÄÖ÷¼üÊÇD£¬ÔòDÔÚSÖгÆÎª Íⲿ¼ü ¡£ 2¡¢¶ÔÓڷǹ淶»¯µÄģʽ£¬¾­¹ý ʹÊôÐÔÓò±äΪ¼òµ¥Óò ת±äΪ1NF,

½«1NF¾­¹ý Ïû³ý·ÇÖ÷ÊôÐÔ¶ÔÖ÷¹Ø¼ü×ֵIJ¿·ÖÒÀÀµ ת±äΪ2NF£¬ ½«2NF¾­¹ý Ïû³ý·ÇÖ÷ÊôÐÔ¶ÔÖ÷¹Ø¼ü×ֵĴ«µÝÒÀÀµ ת±äΪ3NF¡£

3¡¢ÔÚ¹ØÏµÊý¾Ý¿âµÄ¹æ·¶»¯ÀíÂÛÖУ¬ÔÚÖ´ÐС°·Ö½â¡±Ê±£¬±ØÐë×ñÊØ¹æ·¶»¯Ô­Ôò£º±£³ÖÔ­ÓеÄÒÀÀµ¹ØÏµºÍ ÎÞËðÁ¬½ÓÐÔ ¡£ Èý¡¢¸ÅÄî½âÊÍ£º

ƽ·²µÄº¯ÊýÒÀÀµ

ÔÚ¹ØÏµÄ£Ê½R(U)ÖУ¬¶ÔÓÚUµÄ×Ó¼¯XºÍY£¬

Èç¹ûX¡úY£¬µ«Y ? X£¬Ôò³ÆX¡úYÊÇ·ÇÆ½·²µÄº¯ÊýÒÀÀµ ·Çƽ·²µÄº¯ÊýÒÀÀµ

ÔÚ¹ØÏµÄ£Ê½R(U)ÖУ¬¶ÔÓÚUµÄ×Ó¼¯XºÍY£¬ ÈôX¡úY£¬µ«Y ? X, Ôò³ÆX¡úYÊÇÆ½·²µÄº¯ÊýÒÀÀµ

B£®²¿ÃųÉÔ±

C£®²¿ÃÅÃû

D£®²¿ÃźÅ

ËÄ¡¢×ÛºÏÁ·Ï° 1¡¢ÒÑ֪ѧÉú¹ØÏµÄ£Ê½

S(Sno£¬Sname£¬SD£¬Sdname£¬Course£¬Grade)

ÆäÖУºSnoѧºÅ¡¢SnameÐÕÃû¡¢SDϵÃû¡¢SdnameϵÖ÷ÈÎÃû¡¢Course¿Î³Ì¡¢Grade³É¼¨¡£ (1)д³ö¹ØÏµÄ£Ê½SµÄ»ù±¾º¯ÊýÒÀÀµºÍÖ÷Âë¡£

(2)Ô­¹ØÏµÄ£Ê½SΪ¼¸·¶Ê½£¿ÎªÊ²Ã´£¿·Ö½â³É¸ßÒ»¼¶·¶Ê½£¬²¢ËµÃ÷Ϊʲô? (3)½«¹ØÏµÄ£Ê½·Ö½â³É3NF£¬²¢ËµÃ÷Ϊʲô? (1)д³ö¹ØÏµÄ£Ê½SµÄ»ù±¾º¯ÊýÒÀÀµºÍÖ÷Âë¡£

´ð: ¹ØÏµÄ£Ê½SµÄ»ù±¾º¯ÊýÒÀÀµÈçÏ£º

Sno¡úSname£¬SD¡úSdname£¬Sno¡úSD£¬(Sno£¬Course) ¡úGrade ¹ØÏµÄ£Ê½SµÄÂëΪ£º£¨Sno£¬Course£©¡£

(2)Ô­¹ØÏµÄ£Ê½SΪ¼¸·¶Ê½£¿ÎªÊ²Ã´£¿·Ö½â³É¸ßÒ»¼¶·¶Ê½£¬²¢ËµÃ÷Ϊʲô?

´ð: Ô­¹ØÏµÄ£Ê½SÊôÓÚ1NF¡£ÂëΪ(Sno£¬Course)£¬·ÇÖ÷ÊôÐÔÖеijɼ¨ÍêÈ«ÒÀÀµÓÚÂ룬¶øÆäËü·ÇÖ÷ÊôÐÔ¶ÔÂëµÄº¯ÊýÒÀÀµÎª²¿·Öº¯ÊýÒÀÀµ£¬ËùÒÔ²»ÊôÓÚ2NF¡£

Ïû³ý·ÇÖ÷ÊôÐÔ¶ÔÂëµÄº¯ÊýÒÀÀµÎª²¿·Öº¯ÊýÒÀÀµ£¬½«¹ØÏµÄ£Ê½·Ö½â³É2NFÈçÏ£º S1(Sno£¬Sname£¬SD£¬Sdname) S2(Sno£¬Course£¬Grade)

(3)½«¹ØÏµÄ£Ê½·Ö½â³É3NF£¬²¢ËµÃ÷Ϊʲô£¿

´ð: ½«ÉÏÊö¹ØÏµÄ£Ê½·Ö½â³É3NFÈçÏ£º

¹ØÏµÄ£Ê½S1ÖдæÔÚSno¡úSD£¬SD¡úSdname£¬

¼´·ÇÖ÷ÊôÐÔSdname´«µÝÒÀÀµÓÚSno£¬ËùÒÔS1²»ÊÇ3NF¡£ ½øÒ»²½·Ö½âÈçÏ£º

S11(Sno£¬Sname,SD) S12(SD£¬Sdname)

·Ö½âºóµÄ¹ØÏµÄ£Ê½S11¡¢S12Âú×ã3NF¡£

¶Ô¹ØÏµÄ£Ê½S2²»´æÔÚ·ÇÖ÷ÊôÐÔ¶ÔÂëµÄ´«µÝÒÀÀµ£¬¹ÊÊôÓÚ3NF¡£

ËùÒÔ£¬Ô­Ä£Ê½S(Sno£¬Sname£¬SD£¬Sdname£¬Course£¬Grade)°´ÈçÏ·ֽâÂú×ã3NF¡£ S11(Sno£¬Sname£¬SD) S12(SD£¬Sdname)

S2(Sno£¬Course£¬Grade)

2¡¢ÉèÓÐÈçϹØÏµR

(1)ËüΪµÚ¼¸·¶Ê½? Ϊʲô?

(2)ÊÇ·ñ´æÔÚɾ³ý²Ù×÷Òì³£?Èô´æÔÚ£¬Ôò˵Ã÷ÊÇÔÚʲôÇé¿öÏ·¢ÉúµÄ?

(3)½«Ëü·Ö½âΪ¸ßÒ»¼¶·¶Ê½£¬·Ö½âºóµÄ¹ØÏµÊÇÈçºÎ½â¾ö·Ö½âǰ¿ÉÄÜ´æÔÚµÄɾ³ý²Ù×÷Òì³£ÎÊÌâ? (1)ËüΪµÚ¼¸·¶Ê½? Ϊʲô?

½â: ËüÊÇ2NF¡£ ÒòΪRµÄºòÑ¡¹Ø¼ü×ÖΪ¡°¿Î³ÌÃû¡±¡£

ÒÀÀµ¹ØÏµ: ¿Î³ÌÃû¡ú½ÌʦÃû£¬½ÌʦÃû ¡ú ¿Î³ÌÃû£¬½ÌʦÃû¡ú½ÌʦµØÖ·£¬ËùÒÔ ¿Î³ÌÃû¡ú½ÌʦµØÖ·¡£¼´´æÔÚ·ÇÖ÷ÊôÐÔ¡°½ÌʦµØÖ·¡±¶ÔºòÑ¡¹Ø¼ü×ֿγÌÃûµÄ´«µÝº¯Êý£¬Òò´ËR²»ÊÇ3NF¡£µ«£ºÒòΪ²»´æÔÚ·ÇÖ÷ÊôÐÔ¶ÔºòÑ¡¹Ø¼ü×ֵIJ¿·Öº¯ÊýÒÀÀµ£¬ËùÒÔRÊÇ2NF¡£

(2)ÊÇ·ñ´æÔÚɾ³ý²Ù×÷Òì³£?Èô´æÔÚ£¬Ôò˵Ã÷ÊÇÔÚʲôÇé¿öÏ·¢ÉúµÄ?

½â: ´æÔÚ¡£µ±É¾³ýijÃſγÌʱ»áɾ³ý²»¸Ãɾ³ýµÄ½ÌʦµÄÓйØÐÅÏ¢¡£

(3)½«Ëü·Ö½âΪ¸ßÒ»¼¶·¶Ê½£¬·Ö½âºóµÄ¹ØÏµÊÇÈçºÎ½â¾ö·Ö½âǰ¿ÉÄÜ´æÔÚµÄɾ³ý²Ù×÷Òì³£ÎÊÌâ?

½â: ·Ö½âΪ¸ßÒ»¼¶·¶Ê½ÈçͼËùʾ¡£

R1ÈçÏÂ: R2ÈçÏÂ:

·Ö½âºó£¬Èôɾ³ý¿Î³ÌÊý¾Ýʱ£¬½ö¶Ô¹ØÏµR1²Ù×÷£¬½ÌʦµØÖ·ÐÅÏ¢ÔÚ¹ØÏµR2ÖÐÈÔÈ»±£Áô£¬²»»á¶ªÊ§½Ìʦ·½ÃæµÄÐÅÏ¢¡£

3¡¢ÉèijÉÌÒµ¼¯ÍÅÊý¾Ý¿âÖÐÓÐÒ»¹ØÏµÄ£Ê½RÈçÏ£º

R £¨É̵ê±àºÅ£¬ÉÌÆ·±àºÅ£¬ÊýÁ¿£¬²¿ÃűàºÅ£¬¸ºÔðÈË£© Èç¹û¹æ¶¨£º

£¨1£© ÿ¸öÉ̵êµÄÿÖÖÉÌÆ·Ö»ÔÚÒ»¸ö²¿ÃÅÏúÊÛ£» £¨2£© ÿ¸öÉ̵êµÄÿ¸ö²¿ÃÅÖ»ÓÐÒ»¸ö¸ºÔðÈË£» £¨3£© ÿ¸öÉ̵êµÄÿÖÖÉÌÆ·Ö»ÓÐÒ»¸ö¿â´æÊýÁ¿¡£ ÊԻشðÏÂÁÐÎÊÌ⣺

£¨1£© ¸ù¾ÝÉÏÊö¹æ¶¨£¬Ð´³ö¹ØÏµÄ£Ê½RµÄ»ù±¾º¯ÊýÒÀÀµ£» ´ð: ¹ØÏµÄ£Ê½SµÄ»ù±¾º¯ÊýÒÀÀµÈçÏ£º

(É̵ê±àºÅ£¬ÉÌÆ·±àºÅ)¡ú²¿ÃűàºÅ£¬ (É̵ê±àºÅ£¬²¿ÃűàºÅ)¡ú¸ºÔðÈË£¬ (É̵ê±àºÅ£¬ÉÌÆ·±àºÅ)¡úÊýÁ¿ £¨2£© ÕÒ³ö¹ØÏµÄ£Ê½RµÄºòÑ¡Â룻

´ð: ¹ØÏµÄ£Ê½RµÄÂëΪ£º£¨É̵ê±àºÅ£¬ÉÌÆ·±àºÅ, ²¿ÃűàºÅ£©¡£ £¨3£© ÊÔÎʹØÏµÄ£Ê½R×î¸ßÒѾ­´ïµ½µÚ¼¸·¶Ê½£¿ÎªÊ²Ã´£¿

´ð: Ô­¹ØÏµÄ£Ê½RÊÇÊôÓÚ1NFµÄ£¬ÂëΪ£¨É̵ê±àºÅ£¬ÉÌÆ·±àºÅ, ²¿ÃűàºÅ£©£¬·ÇÖ÷ÊôÐÔ¶ÔÂëµÄº¯ÊýÒÀÀµÈ«Îª²¿·Öº¯ÊýÒÀÀµ£¬ËùÒÔ²»ÊôÓÚ2NF¡£

Ïû³ý·ÇÖ÷ÊôÐÔ¶ÔÂëµÄº¯ÊýÒÀÀµÎª²¿·Öº¯ÊýÒÀÀµ£¬½«¹ØÏµÄ£Ê½·Ö½â³É2NFÈçÏ£º R1(É̵ê±àºÅ£¬ÉÌÆ·±àºÅ, ²¿ÃűàºÅ, ÊýÁ¿) R2(É̵ê±àºÅ£¬²¿ÃűàºÅ, ¸ºÔðÈË)

£¨4£© Èç¹ûR²»ÊôÓÚ3NF£¬Ç뽫R·Ö½â³É3NFģʽ¼¯¡£ ´ð£º½«R·Ö½âΪ

R1(É̵ê±àºÅ£¬ÉÌÆ·±àºÅ, ²¿ÃűàºÅ, ÊýÁ¿) R2(É̵ê±àºÅ£¬²¿ÃűàºÅ, ¸ºÔðÈË)

·Ö½âºóµÄR²»´æÔÚ´«µÝµÄº¯ÊýÒÀÀµ£¬ËùÒÔ·Ö½âºóµÄRÒѾ­ÊǵÚ3NF

µÚ¾ÅÕ ¹ØÏµ²éѯ´¦ÀíºÍ²éѯÓÅ»¯

¿Îºó×÷Òµ£ºP275£¬2Ì⣺ ´ð°¸£º ¡ÇCname Join = Student ¦Ä=¡¯IS¡¯ ½áProject(Cna¡ÇCname ¦Ä= Select =¡¯IS¡¯ ¦Ä= ¡Á Join= ¡Á ¦ÄJoin = ¡Á ¦ÄJoin = course course SC ¡Á Student SC ¦Ä=¡¯IS¡¯ SC Student course