Mau oyamba a Functional Dependencies

M'nkhaniyi tikambirana za kudalira kwa magwiridwe antchito - zomwe zili, komwe zimagwiritsidwa ntchito komanso ma aligorivimu omwe alipo kuti awapeze.

Tidzalingalira za kudalira kwa magwiridwe antchito pazogwirizana ndi ma database. Kunena movutikira kwambiri, mu nkhokwe zotere zambiri zimasungidwa m'matebulo. Kenako, timagwiritsa ntchito mfundo zofananira zomwe sizingasinthidwe m'malingaliro okhwima a ubale: tidzatcha tebulo lokha ubale, mizati - zikhumbo (magawo awo - schema yaubale), ndi magawo amizere pagawo lazikhalidwe. - ndi tuple.

Mwachitsanzo, patebulo pamwambapa, (Benson, M, M organ) ndi chiwerengero cha makhalidwe (Patient, Paul, Doctor).
Mwamwayi, izi zalembedwa motere: [Wodwala, Jenda, Dokotala] = (Benson, M, M organ).
Tsopano titha kuyambitsa lingaliro la kudalira ntchito (FD):

Tanthauzo 1. Ubale R umakwaniritsa lamulo la feduro X → Y (pomwe X, Y ⊆ R) ngati ndi zolemba zilizonse , ∈ R agwira: ngati [X] = [X], ndiye [Y] = [Y]. Pachifukwa ichi, timati X (chidziwitso, kapena chofotokozera za makhalidwe) chimagwira ntchito Y (yodalira).

Mwa kuyankhula kwina, kukhalapo kwa lamulo la federal X → Y zikutanthauza kuti ngati tili ndi ma tuple awiri mkati R ndipo zimagwirizana m'makhalidwe X, Kenako adzafanana m’makhalidwe Y.
Ndipo tsopano, mu dongosolo. Tiyeni tione makhalidwe ake Woleza mtima и Kugonana zomwe tikufuna kudziwa ngati pali zodalira pakati pawo kapena ayi. Pazinthu zotere, zodalira izi zitha kukhalapo:

1. Wodwala → Jenda
2. Jenda → Woleza mtima

Monga tafotokozera pamwambapa, kuti kudalira koyamba kugwire, mtengo uliwonse wapadera Woleza mtima ndime imodzi yokha iyenera kufanana Kugonana. Ndipo kwa tebulo lachitsanzo izi ndizochitikadi. Komabe, izi sizigwira ntchito mosiyana, ndiye kuti, kudalira kwachiwiri sikukhutitsidwa, ndipo Kugonana si determinant kwa Woleza mtima. Mofananamo, ngati titenga kudalira Dokotala → Wodwala, mukuwona kuti ikuphwanyidwa, popeza mtengo wake Robin chikhalidwe ichi chili ndi matanthauzo angapo - Ellis ndi Graham.

Chifukwa chake, kudalira kwa magwiridwe antchito kumapangitsa kuti zitheke kudziwa maubwenzi omwe alipo pakati pamagulu azinthu zama tebulo. Kuyambira pano kupita mtsogolo tiwona zolumikizana zosangalatsa kwambiri, kapena m'malo mwake X → Yzomwe iwo ali:

• zosakhala zazing'ono, ndiko kuti, mbali yakumanja ya kudalira si kagawo kakang'ono ka kumanzere (Y ̸⊆ X);
• zochepa, ndiye kuti, palibe kudalira koteroko Z → Y, izo Z ⊂ X.

Zodalira zomwe zimaganiziridwa mpaka pano zinali zokhwima, ndiye kuti, sizinapereke zolakwa zilizonse patebulo, koma kuwonjezera pa izo, palinso zomwe zimalola kusagwirizana pakati pa zikhalidwe za tuples. Kudalira kotereku kumayikidwa m'gulu lapadera, lotchedwa pafupifupi, ndipo amaloledwa kuphwanyidwa pa chiwerengero china cha tuples. Ndalamayi imayendetsedwa ndi chizindikiro chachikulu cha zolakwika emax. Mwachitsanzo, kuchuluka kwa zolakwika = 0.01 angatanthauze kuti kudalira kungathe kuphwanyidwa ndi 1% ya zolemba zomwe zilipo pamagulu omwe amaganiziridwa. Ndiko kuti, pazolemba 1000, ma tuples 10 opitilira akhoza kuphwanya Lamulo la Federal. Tikambirana za metric yosiyana pang'ono, kutengera mitundu iwiri ya ma tuple omwe akufananizidwa. Za kuledzera X → Y pa maganizo r zimaganiziridwa ngati izi:

Tiyeni tiwerengere cholakwika cha Dokotala → Wodwala kuchokera ku chitsanzo pamwambapa. Tili ndi zilembo ziwiri zomwe zikhalidwe zake zimasiyana pamalingaliro Woleza mtima, koma kugwirizana Dokotala: [Dokotala, Wodwala] = (Robin, Ellis) ndi [Dokotala, Wodwala] = (Robin, Graham). Kutsatira tanthauzo la cholakwika, tiyenera kuganizira awiriawiri onse otsutsana, kutanthauza kuti padzakhala awiri a iwo: (, ) ndi kusintha kwake (, ). Tiyeni tisinthe mu fomula ndikupeza:

Tsopano tiyeni tiyese kuyankha funso: "N'chifukwa chiyani zonsezi?" Ndipotu, malamulo a federal ndi osiyana. Mtundu woyamba ndizomwe zimadalira zomwe zimatsimikiziridwa ndi woyang'anira pa siteji ya mapangidwe a database. Nthawi zambiri amakhala ochepa, okhwima, ndipo ntchito yayikulu ndikukhazikika kwa data ndi kapangidwe ka schema.

Mtundu wachiwiri ndi zodalira, zomwe zimayimira deta "zobisika" ndi maubwenzi osadziwika kale pakati pa zikhumbo. Ndiko kuti, kudalira koteroko sikunaganiziridwe pa nthawi ya mapangidwe ndipo iwo akupezeka kale kwa deta yomwe ilipo, kotero kuti pambuyo pake, pogwiritsa ntchito malamulo ambiri odziwika a federal, ziganizo zilizonse zingatheke zokhudzana ndi zomwe zasungidwa. Ndi zodalira izi zomwe timagwira nazo ntchito. Amayendetsedwa ndi gawo lonse la migodi ya data ndi njira zosiyanasiyana zofufuzira ndi ma algorithms omangidwa pamaziko awo. Tiyeni tiwone momwe zodalira zomwe zapezeka (ndendende kapena pafupifupi) mu data iliyonse zitha kukhala zothandiza.

Masiku ano, imodzi mwazofunikira kwambiri pakudalira ndikuyeretsa deta. Zimaphatikizapo kupanga njira zozindikiritsira "zambiri zonyansa" ndikuzikonza. Zitsanzo zodziwika bwino za "deta yonyansa" ndizobwerezabwereza, zolakwika za data kapena typos, zikhalidwe zomwe zikusowa, deta yakale, malo owonjezera, ndi zina zotero.

Chitsanzo cha vuto la data:

Zitsanzo zobwerezedwa mu data:

Mwachitsanzo, tili ndi tebulo ndi malamulo a federal omwe ayenera kutsatiridwa. Kuyeretsa deta pankhaniyi kumaphatikizapo kusintha deta kuti Malamulo a Federal akhale olondola. Pachifukwa ichi, chiwerengero cha zosinthidwa chiyenera kukhala chochepa (njirayi ili ndi ma algorithms ake, omwe sitidzayang'ana nawo m'nkhaniyi). Pansipa pali chitsanzo cha kusintha kwa deta koteroko. Kumanzere ndi ubale wapachiyambi, momwe, mwachiwonekere, zofunikira za FL sizikukwaniritsidwa (chitsanzo cha kuphwanya chimodzi mwa FLs chikuwonetsedwa mofiira). Kumanja ndi ubale wosinthidwa, ndi maselo obiriwira omwe akuwonetsa zosinthika. Pambuyo pa njirayi, kudalira kofunikira kunayamba kusungidwa.

Ntchito ina yotchuka ndi kapangidwe ka database. Apa ndi bwino kukumbukira yachibadwa mitundu ndi normalization. Normalization ndi njira yobweretsera chiyanjano kuti chigwirizane ndi zofunikira zina, zomwe zimatanthauzidwa ndi mawonekedwe abwino mwa njira yakeyake. Sitidzafotokozera zofunikira zamitundu yosiyanasiyana yanthawi zonse (izi zimachitika m'buku lililonse pamaphunziro a database kwa oyamba kumene), koma tingowona kuti aliyense wa iwo amagwiritsa ntchito lingaliro la kudalira kwa magwiridwe antchito mwanjira yake. Kupatula apo, ma FL ndi zolepheretsa kukhulupirika zomwe zimaganiziridwa popanga nkhokwe (motengera ntchitoyi, ma FL nthawi zina amatchedwa superkeys).

Tiyeni tiwone momwe angagwiritsire ntchito mawonekedwe anayi omwe ali pachithunzichi. Kumbukirani kuti mawonekedwe a Boyce-Codd ndi okhwima kwambiri kuposa mawonekedwe achitatu, koma ocheperako kuposa wachinayi. Sitikuganizira zotsirizirazo pakadali pano, popeza kupangidwa kwake kumafuna kumvetsetsa za kudalira kwamtengo wapatali, zomwe sizosangalatsa kwa ife m'nkhaniyi.

Mbali ina yomwe odalira adapeza kuti akugwiritsa ntchito ndikuchepetsa kukula kwa gawolo muzochita monga kupanga gulu losazindikira la Bayes, kuzindikira mawonekedwe ofunikira, ndikukonzanso mawonekedwe obwerera. M'zolemba zoyambirira, ntchitoyi imatchedwa kutsimikiza kofunikira komanso kufunikira kwa mawonekedwe [5, 6], ndipo imathetsedwa ndikugwiritsa ntchito malingaliro a database. Kubwera kwa ntchito zotere, tinganene kuti lero pali kufunikira kwa mayankho omwe amatilola kugwirizanitsa nkhokwe, kusanthula ndi kukhazikitsa mavuto omwe ali pamwambawa kukhala chida chimodzi [7, 8, 9].

Pali ma algorithms ambiri (amakono komanso amakono) ofufuza malamulo a federal mu seti ya data.

• Ma algorithms pogwiritsa ntchito traversal of algebraic lattices (Lattice traversal algorithms)
• Ma algorithms otengera kusaka kwa zinthu zomwe mwagwirizana (Kusiyana- ndi kuvomereza-ma algorithms)
• Ma algorithms otengera kufananitsa kwa pawiri (Dependency induction algorithms)

Kufotokozera mwachidule kwa mtundu uliwonse wa algorithm kumaperekedwa patebulo ili pansipa:


Mutha kuwerenga zambiri za gulu ili [4]. Pansipa pali zitsanzo zama algorithms amtundu uliwonse:

Pakadali pano, ma aligorivimu atsopano akuwoneka omwe amaphatikiza njira zingapo zopezera kudalira kwa magwiridwe antchito. Zitsanzo za ma aligorivimu otere ndi Pyro [2] ndi HyFD [3]. Kupenda ntchito yawo kukuyembekezeredwa m’nkhani zotsatirazi za mpambo uno. M'nkhaniyi tidzangoyang'ana mfundo zoyambira ndi lemma zomwe ndizofunikira kuti timvetsetse njira zodziwira kudalira.

Tiyeni tiyambe ndi yosavuta - kusiyana- ndi kuvomereza-kukhazikitsidwa, kugwiritsidwa ntchito mumtundu wachiwiri wa ma aligorivimu. Difference-set ndi magulu a ma tuples omwe alibe makhalidwe ofanana, pamene kuvomereza-set, m'malo mwake, ndi ma tuple omwe ali ndi makhalidwe ofanana. Ndikoyenera kudziwa kuti pamenepa tikungoganizira za kumanzere kwa kudalira.

Lingaliro lina lofunika lomwe tinakumana nalo pamwambapa ndi latisi ya algebraic. Popeza ma aligorivimu ambiri amakono amagwira ntchito pa lingaliro ili, tiyenera kukhala ndi lingaliro la zomwe zili.

Kuti tidziwitse lingaliro la lattice, ndikofunikira kutanthauzira seti yokhazikitsidwa pang'ono (kapena seti yokhazikitsidwa pang'ono, yofupikitsidwa ngati positi).

Tanthauzo 2. Seti S imanenedwa kuti idakonzedwa pang'ono ndi ubale wa binary ⩽ ngati pa onse a, b, c ∈ S zinthu zotsatirazi zakhutitsidwa:

1. Reflexivity, ndiye kuti, a ⩽ a
2. Antisymmetry, ndiye kuti, ngati ⩽ b ndi b ⩽ a, ndiye a = b
3. Kusintha, ndiko kuti, kwa ⩽ b ndi b ⩽ c kumatsatira kuti a ⩽ c

Ubale woterewu umatchedwa (loose) partial order relation, ndipo seti yokhayo imatchedwa seti yokonzedwa pang'ono. Zolemba zokhazikika: ⟨S, ⩽⟩.

Monga chitsanzo chosavuta cha seti yoyitanitsa pang'ono, titha kutenga seti ya manambala achilengedwe N molingana ndi dongosolo lanthawi zonse ⩽. Ndikosavuta kutsimikizira kuti ma axiom onse ofunikira amakwaniritsidwa.

Chitsanzo chatanthauzo kwambiri. Ganizirani zamagulu ang'onoang'ono {1, 2, 3}, oyendetsedwa ndi mgwirizano wophatikiza ⊆. Zowonadi, chibalechi chimakwaniritsa zofunikira zonse, kotero ⟨P ({1, 2, 3}), ⊆⟩ ndi seti yokonzedwa pang'ono. Chithunzi chomwe chili pansipa chikuwonetsa mawonekedwe a seti iyi: ngati chinthu chimodzi chitha kufikidwa ndi mivi kupita ku chinthu china, ndiye kuti ali muubwenzi.

Tidzafunika matanthauzidwe awiri osavuta kuchokera ku masamu - supremum ndi infimum.

Tanthauzo 3. Lolani ⟨S, ⩽⟩ ikhale yokonzedwa pang'ono, A ⊆ S. Kumtunda kwa A ndi chinthu u ∈ S chotere ∀x ∈ S: x ⩽ u. Lolani U kukhala seti ya malire onse apamwamba a S. Ngati pali chinthu chaching'ono kwambiri mu U, ndiye kuti chimatchedwa supremum ndipo chimatchedwa sup A.

Lingaliro la kutsika kwenikweni limayambitsidwa mofananamo.

Tanthauzo 4. Lolani ⟨S, ⩽⟩ ikhale yokonzedwa pang'ono, A ⊆ S. Infimum ya A ndi chinthu l ∈ S kotero kuti ∀x ∈ S: l ⩽ x. Lolani L kukhala seti ya malire onse apansi a S. Ngati pali chinthu chachikulu kwambiri mu L, ndiye kuti imatchedwa infimum ndipo imatchedwa inf A.

Ganizirani monga chitsanzo chokhazikitsidwa pamwambapa ⟨P ({1, 2, 3}), ⊆⟩ ndikupeza supremum ndi infimum mmenemo:

Tsopano titha kupanga tanthauzo la algebraic lattice.

Tanthauzo 5. Lolani ⟨P,⩽⟩ kukhala yokonzedwa pang'ono kuti gawo laling'ono lililonse lazinthu ziwiri likhale ndi malire apamwamba ndi apansi. Ndiye P amatchedwa algebraic lattice. Pamenepa, sup{x, y} amalembedwa ngati x ∨ y, ndi inf {x, y} monga x ∧ y.

Tiyeni tiwone ngati chitsanzo chathu ⟨P ({1, 2, 3}), ⊆⟩ ndi latisi. Zowonadi, kwa a, b ∈ P ({1, 2, 3}), a∨b = a∪b, ndi a∧b = a∩b. Mwachitsanzo, lingalirani za seti {1, 2} ndi {1, 3} ndikupeza infimum ndi supremum yawo. Ngati tidutsana nawo, tidzalandira seti {1}, yomwe idzakhala infimum. Timapeza supremum powaphatikiza - {1, 2, 3}.

Mu ma algorithms ozindikiritsa zovuta zakuthupi, malo osakira nthawi zambiri amaimiridwa ngati latice, pomwe ma seti a chinthu chimodzi (werengani gawo loyamba la latisi yofufuzira, pomwe mbali yakumanzere ya zodalira imakhala ndi chikhalidwe chimodzi) imayimira chilichonse. za ubale wapachiyambi.
Choyamba, timaganizira za kudalira kwa fomu ∅ → Khalidwe limodzi. Gawo ili limakupatsani mwayi wodziwa zomwe zili makiyi oyambira (pazifukwa zotere palibe zotsimikizira, chifukwa chake kumanzere kulibe kanthu). Kuphatikiza apo, ma algorithms oterowo amasunthira mmwamba motsatira latisi. Ndikoyenera kudziwa kuti si latisi yonse yomwe ingadutse, ndiko kuti, ngati kukula kwakukulu kwa mbali yakumanzere kumadutsa kuzomwezo, ndiye kuti ndondomekoyi sichitha kupitirira mlingo ndi kukula kwake.

Chithunzi chomwe chili pansipa chikuwonetsa momwe algebraic lattice ingagwiritsire ntchito vuto lopeza FZ. Apa m'mphepete uliwonse (X, XY) imayimira kudalira X → Y. Mwachitsanzo, tadutsa gawo loyamba ndipo tikudziwa kuti chizolowezicho chimasungidwa A → B (tidzawonetsa izi ngati kulumikizana kobiriwira pakati pa ma vertices A и B). Izi zikutanthauza kuti kupitilira apo, tikamayenda m'mphepete mwa latisi, sitingayang'ane kudalira A, C → B, chifukwa sichidzakhalanso chochepa. Mofananamo, sitingayang'ane ngati kudalira kunachitika C → B.

Kuphatikiza apo, monga lamulo, ma aligorivimu onse amakono osaka malamulo a federal amagwiritsa ntchito mawonekedwe a data monga kugawa (mu gwero loyambirira - kugawa magawo [1]). Tanthauzo lokhazikika la magawo ali motere:

Tanthauzo 6. Lolani X ⊆ R ikhale mndandanda wa zikhumbo za ubale r. Cluster ndi mndandanda wa ma tuples mu r omwe ali ndi mtengo wofanana wa X, ndiko kuti, c(t) = {i|ti[X] = t[X]}. Gawo ndi gulu lamagulu, osaphatikiza magulu a kutalika kwa mayunitsi:

M'mawu osavuta, kugawa kwa chikhalidwe X ndi mndandanda wa mindandanda, pomwe mndandanda uliwonse uli ndi manambala amizere okhala ndi zikhalidwe zomwezo X. M'mabuku amakono, mawonekedwe oyimira magawo amatchedwa index list index (PLI). Magulu a kutalika kwa ma unit sakuphatikizidwa pazolinga zopondereza za PLI chifukwa ndi magulu omwe amakhala ndi nambala yokhayo yokhala ndi mtengo wapadera womwe uzikhala wosavuta kuzindikira.

Tiyeni tione chitsanzo. Tiyeni tibwerere ku tebulo lomwelo ndi odwala ndikupanga magawo amizere Woleza mtima и Kugonana (chigawo chatsopano chawonekera kumanzere, komwe manambala amizere yatebulo amalembedwa):

Komanso, malinga ndi tanthauzo, kugawa kwa ndime Woleza mtima adzakhala opanda kanthu, chifukwa magulu amodzi sachotsedwa pagawo.

Partitions angapezeke ndi makhalidwe angapo. Ndipo pali njira ziwiri zochitira izi: podutsa patebulo, pangani magawo pogwiritsa ntchito zofunikira zonse nthawi imodzi, kapena kumanga pogwiritsa ntchito njira zophatikizira magawo pogwiritsa ntchito kagawo kakang'ono. Ma aligorivimu aku Federal Law search amagwiritsa ntchito njira yachiwiri.

M'mawu osavuta, kuti, mwachitsanzo, kupeza magawo ndi mizati ABC, mukhoza kutenga partitions kwa AC и B (kapena gulu lina lililonse la magawo osagwirizana) ndikudutsana nawo. Kugwiritsiridwa ntchito kwa mphambano ya magawo awiri kumasankha magulu aatali kwambiri omwe ali ofanana ndi magawo onse awiri.

Tiyeni tione chitsanzo:

Poyamba, tinalandira gawo lopanda kanthu. Ngati muyang'anitsitsa patebulo, ndiye kuti, palibe mikhalidwe yofanana pamikhalidwe iwiriyi. Ngati tisintha pang'ono tebulo (mlandu womwe uli kumanja), tipeza kale mphambano yopanda kanthu. Kuphatikiza apo, mizere 1 ndi 2 imakhala ndi zikhalidwe zomwezo Kugonana и Dokotala.

Chotsatira, tidzafunika lingaliro lotere monga kukula kwa magawo. Mwamwayi:

Mwachidule, kukula kwa magawo ndi chiwerengero cha magulu omwe akuphatikizidwa mu magawowo (kumbukirani kuti magulu amodzi sanaphatikizidwe mu magawowo!):

Tsopano titha kufotokozera imodzi mwama lemmas ofunikira, omwe magawo opatsidwa amatipatsa mwayi wodziwa ngati kudalira kulipo kapena ayi:

Lema 1. Kudalira A, B → C kumakhala ngati ndipo pokhapokha ngati

Malinga ndi lemma, kuti mudziwe ngati kudalira kumagwira ntchito, njira zinayi ziyenera kuchitidwa:

1. Werengani kugawa kwa kumanzere kwa kudalira
2. Werengani kugawa kwa mbali yakumanja ya kudalira
3. Kuwerengera mankhwala a sitepe yoyamba ndi yachiwiri
4. Fananizani kukula kwa magawo omwe amapezeka mugawo loyamba ndi lachitatu

Pansipa pali chitsanzo chowonera ngati kudalira kumagwirizana ndi lemma iyi:

M'nkhaniyi, tapenda malingaliro monga kudalira ntchito, kudalira pafupifupi ntchito, kuyang'ana komwe amagwiritsidwa ntchito, komanso ma algorithms osakasaka ntchito zakuthupi alipo. Tidasanthulanso mwatsatanetsatane mfundo zoyambira koma zofunika zomwe zimagwiritsidwa ntchito mwachangu munjira zamakono pofufuza malamulo a federal.

Zolozera:

1. Huhtala Y. et al. TANE: Algorithm yothandiza kuti mupeze zomwe zimagwira ntchito komanso pafupifupi kudalira // Magazini yamakompyuta. - 1999. - T. 42. - No. 2. - masamba 100-111.
2. Kruse S., Naumann F. Kupeza kothandiza kwa pafupifupi zodalira // Zokambirana za VLDB Endowment. - 2018. - T. 11. - No. 7. - masamba 759-772.
3. Papenbrock T., Naumann F. Njira yosakanizidwa yopeza ntchito yodalira //Proceedings of the 2016 International Conference on Management of Data. - ACM, 2016. - tsamba 821-833.
4. Papenbrock T. et al. Kupezeka kwa kudalira kogwira ntchito: Kuwunika koyeserera kwa ma algorithms asanu ndi awiri //Proceedings of the VLDB Endowment. - 2015. - T. 8. - No. 10. - masamba 1082-1093.
5. Kumar A. et al. Kujowina kapena kusalowa nawo?: Kuganizira kawiri zojowina musanasankhe mawonekedwe //Zotsatira za Msonkhano Wapadziko Lonse wa 2016 pa Management of Data. - ACM, 2016. - masamba 19-34.
6. Abo Khamis M. et al. Kuphunzira mu database ndi ma sparse tensor //Zotsatira za 37th ACM SIGMOD-SIGACT-SIGAI Symposium pa Mfundo za Database Systems. - ACM, 2018. - tsamba 325-340.
7. Hellerstein J. M. et al. Laibulale ya MADlib analytics: kapena luso la MAD, SQL //Proceedings of the VLDB Endowment. - 2012. - T. 5. - No. 12. - masamba 1700-1711.
8. Qin C., Rusu F. Zoyerekeza zongoyerekeza za kukhathamiritsa kwa kutsika kwa terascale //Proceedings of the Fourth Workshop on Data analytics in the Cloud. - ACM, 2015. - P. 1.
9. Meng X. et al. Mllib: Kuphunzira pamakina mu apache spark // Journal of Machine Learning Research. - 2016. - T. 17. - No. 1. - masamba 1235-1241.

Olemba nkhani: Anastasia Birillo, wofufuza pa Kafukufuku wa JetBrains, CS Center wophunzira и Nikita Bobrov, wofufuza pa Kafukufuku wa JetBrains

Source: www.habr.com