[RaidoR]

Vaja lahendada kooliga seoses üks andmebaasi projekteerimise ülesanne. Ei oota, et keegi minu eest selle ära lahendaks, vaid pigem ootan suunavaid vihjeid.

Ülesanne ise:

Spoiler

tsitaat:

Eesti turule siseneb uus mobiilsideoperaator „Mobifon“. Nii nagu teised operaatorid, jagab Mobifon oma kliente eraklientideks ja äriklientideks. Mitu eraklienti võivad moodustada grupi - „pere“ (piiratud 5 liikmega), kellele kehtib kokkulepitud soodustus (%-des) tarbitud teenustele. Lisaks sellele kõik riigisisesed kõned pereliikmete vahel on alati tasuta. Iga pereliige võib tarbida üht või mitut erinevat Mobifoni teenust. Mobifoni teenused eraklientidele jagunevad kolme gruppi: kõneteenused, andmesideteenused ja lisateenused. Kõikide kõneteenuspakettide omadusteks on: a) võrgusisese kõneminuti hind, b) võrgust väljuva kõneminuti hind, c) lühisõnumi saatmise hind, d) kuumakse e) miinimumarve suurus. f) Välismaale helistamiseks kehtib sõltumatu teenuspakettist ühine hinnakiri, mis määrab kõneminuti hinda erinevatesse riikidesse. Paketid erinevad üksteisest lubatud maksimaalse andmesidekiiruse järgi. Andmeedastusmahule ega muid piiranguid Mobifonil ei ole. Igal keskööl väljastab Mobifoni võrgutarkvara järgmised andmed: a) millised kõned ja lühisõnumid olid tehtud kõigilt ööpäeva jooksul Mobifoni võrgus registreeritud telefoninumbritelt, b) kõnede alguskellaeg sekundi täpsusega, kõne korral selle kestus ja sihtriik. Eeltoodud andmestik on aluseks klientide arveldamiseks ja on eelkirjeldatud infosüsteemi lahutamatu osa. Teie ülesandeks on: töötada välja andmebaasi struktuur ja esitada see ER-mudelina , mis on sisendiks tarkvaraarendaja meeskonnale kliendihaldustarkvara väljatöötamiseks. Loodava kliendihaldustarkvara põhiülesanded on järgmised: · kliendiregistri pidamine koos klientide kontaktandmete hoidmisega · välistada andmete liigset dubleerimist (arvestada sellega, et ühel kliendil võib olla mitu telefoninumbrit, ta võib olla mitme pere liige (kuid erinevate telefoninumbritega) · võrgus registreeritud mobiiltelefoninumbrite registri (HLR) pidamine koos selle seostamisega kliendiga ja talle määratud teenuspaketiga · ajaloo salvestamine: milliseid telefoninumbreid ja teenuspakette iga klient kasutab praegu ja on kasutanud varem · iga kliendi kohta kõneeristuse väljastamine koos tarbitud teenuste hinnaga iga kuu lõikes

Kas saan õigesti aru, et vaja luua tabelid: eraklient, äriklient, pere, pakett, kõneteenused, andesideteenused, lisateenused, võrgutarkvara andmed ning näidata nende vahelised seosed?

Kas minu lahendus ülesandele on mingitpidi õige?

Spoiler

[neros]

Okei, ma pole küll kunagi pidanud kõneoperaatorite andmemudelit uurima... aga pealiskaudsel vaatamisel...

1) Kas Erakliendi ja PereGrupi vahel ei peaks olema many-to-many suhe? Kuna Eraklient võiks teoorias kuuluda mitmesse peregruppi?
2) Rahvusvaheline kõne ja "Kõna" eraldi tabelites? Miks seal KõneSihtRiik siis on? Sama hästi võiks RhvKõne olemata olla.
3) Võrgutarkvara vahetabel? Pigem tee sellest telefoninumbrite tabel, kuna teoorias on ühel 'arvel' võimalik korraga olla mitu telefoninumbrit ning seotud ühe kliendiga. Mitte kunagi ei tohiks kasutada arbitraarset numbrit primary keyna. Mis juhtub, kui klient lepingu tühistab ja teine saab sama numbri? Terve süsteem läheb tuksi. Primary keyd peaks üldjuhul (on erandeid) olema kordumatud täisarvud.

Tegelikult on seal veel rohkemgi apsakaid, aga vaata kriitilise pilguga üle ning loe veel teooriat ja kindlasti leiad nad ka ise üles.

[RaidoR]

Sain vahepeal juhiseid/soovitusi, et võiks kogu asja käima panna läbi "Lepingud"te.
Ise kindel ei ole, kas parent-child lahendus siinkohal sobilik on. Andke tõesti andeks, kui tundub jabur, alles õpin asja

Spoiler

[neros]

Kuhu telefoninumber salvestatakse? Või oledki loogilised väljad ära jätnud Kõne tabelist?

[RaidoR]

Loogilised väljad olen ära jätnud jah millegi pärast Telefoni nr peaks siis "Kõne" tabelisse salvestatama?

On muidu "Leping"u ja child'ide asi mõistlikult lahendatud?

[Max Powers]

Alumine osa on natuke nigel vist, sest ühel lepingul võib olla mitu numbrit.

[vallo]

Pole küll ammu enam asjaga tegelenud, aga miks Sa tahad äri ja era kliendid eraldi tabelisse panna?
Kas poleks otstarbekam kasutada mingit tunnust ehk siis tabel klient, kus on kliendi andmed eesnimi, perenimi, telefon, isikukood/firmaregnr jne... ja väli äriklient, mis oleks siis tõeväärtus (1 näiteks äriklient, 0 eraklient)
sel juhul on ka lepingutabelis üks kliendiID.

[RaidoR]

vallo, Mõtlesin selle peale tegelikult

[vallo]

veel on mul ähmane mälestus, et selliseid asju nagu lisateenus tehti nii, et on tabel lisateenused, kus on siis teenuseID, teenuse nimi jne.. ja siis eraldi tabel (näiteks nimega lepinguteenused) kus on väljad lepinguID ja teenuseID

[RaidoR]

Ehk midagi sellist? Suhted ei pruugi õiged olla.

Spoiler

[vallo]

ei, 2 tabelit
Lisateenus, kus on sees teenuseID ja Teenuse nimi, kui vaja siis veel midagi (näiteks kirjeldus) ja siis on tabel lepinguteenused, kus on väljad lepinguID ja TeenuseID
Tabel teenus on seotud tabeliga lepinguteenused suhtega üks mitmele ja tabel lepingud on seotud tabeliga lepinguteenused samuti suhtega üks mitmele (mõlemal juhul on lepinguteenused poolne ots mitmele)
Ehk siis ühel lepingul võib olla mitu teenust ja ühel teenusel mitu lepingut.

EDIT, ma vist vaatasin natuke mööda, hetkel ei suuda kohe öelda, kas on mõistlik panna kõik need asjad ühte kokku (sms -id kõned, lepingud jne...)
ei tundu päris hea variant.
Kuna kõnesid ja sms -e tuleb palju, samas teenuseid on ühe lepingu kohta piiratud ja vähemuutuv arv, ma lööks need lahku

Hetkel jääb mulle ka segaseks, kuidas tekib näiteks numbripõhine kõneeristus Number on seotud lepinguga, aga kas lepingul võib olla ka mitu numbrit?, siis mismoodineid eraldada

[RaidoR]

E:

Hetkel siis jääks nii, et eraldi on Kõne ja SMS - need seoks telefoninumbri tabeliga.
Lisateenus ja Andmeside jääks ka eraldi tabelisse ja seoks need Lepinguteenustega, mis omakorda seotud lepinguga?

[riaak]

Minu visioon läheb vallo omast natuke teist rada pidi. Vaata, ehk meeldib sulle see rohkem.


Mida teeb Lepinguteenused? Kas seal on üks võimalikest sündmustest (kõne, sõnum või mõni muu teenus) või peaks see hoopis pakettide andmeid hoidma? Antud hetkel see sul vist segamini läinud. Ühe lepinguga saaks justkui teha ühe kõne, sõnumi, ... või siis teisel juhul oleks iga kõne ja sms puhul eraldi lepinguteenus, mis oleks ka kasutu kuna Kõne või SMS all on sündmus juba olemas. Sa vist ei ole suutnud neid sündmusi (kõne, sms, ...) kliendiga siduda ja siis oled sellise üllitise sinna vahele konstrueerinud. Sarnast asja ka natuke mujal näha.
Ma kaotaks selle Lepinguteenuste tabeli. Lepingute tabelist kaotaks kõnega seonduvad võtmed kuna need rohkem kõnepaketi teema ja need ei ole otseselt seotud lepinguga.

(arvestada sellega, et ühel kliendil võib olla mitu telefoninumbrit, ta võib olla mitme pere liige (kuid erinevate telefoninumbritega)
Telefoninumbrite tabelisse panna grupinr. Nii saab klient omada mitut numbrit ja iga number võib olla erinevas grupis. Hetkel kliendil ainult üks peregrupp võimalik ja milline ta numbritest selles grupis on? Kes seda teab.

ajaloo salvestamine: milliseid telefoninumbreid ja teenuspakette iga klient kasutab praegu ja on kasutanud varem
Eraldi võiks olla paketid (kõne, sms, andmeside), kus on kirjas pakettide andmed (hind, nimetus, kirjeldus jms).
Ja pakettidest läheks edasi ValitudKõnePakett(pakett, telefoninr, lepingunr, paketi lisamise aeg, lõpuaeg), ValitudSMSPakett(...), ...
Nii on võimalik tuvastada, mis paketti klient kuna kasutas ja kas see on veel aktiivne (küsida raha teenuse eest v mitte).

iga kliendi kohta kõneeristuse väljastamine koos tarbitud teenuste hinnaga iga kuu lõikes
Kõne teeks hoopis helistaja number, aeg, kestvus, sihtriik, vastuvõtja number. Sarnaselt ka SMS, andmeside (ehk ka muud teenused kui on vaja nende kasutamise koguse üle arvet pidada).
Ehk peaks lepingunr ka juures olema, aga see ei ole otseselt vajalik sest see on tuletatav.

[RaidoR]

Üritasin su mõttelõnga jälgida. Kas mingi selline idee oli sul?

Spoiler

[serk]

praktiliselt igas tabelis peab olema 4 lisa veergu:

created_by
created_dtime
last_updated_by
last_updated_dtime

ja nende täitmine triggeriga.

[napoleon]

Mõned kommentaarid:
peregrupp on hetkel valesti disainitud. Kuidas sa nüüd omavahelise kõne puhul kontrolliks, kas number kuulub gruppi või mitte? Õige oleks midagi sellist, tabel peregrupp(GrupiID, soodustus%) ja tabel peregruppLiikmed(pgliikmeid(PK), GrupiID, lepinguid). Ja lepinguID ei ole siin kogemata, gruppi peaks kuuluma leping mitte klient eedlusel, et üks leping tähendab ühte numbrit. Samuti peaks liikmete tabelis olema algus- ja lõpukuupäev.

hind peaks olema hinnakirjas mitte paketi juures. Alternatiivina võib lahendada ka ühe tabeliga, aga siis peab lisama sinna ka alguse ja lõpu kuupäeva. Milleks? Aga mis juhtub siis, kui hind muutub? Arveldus arveldus toimub enamasti kord kuus, samuti peab jääma eelmistel perioodidel kehtinud hindadest märk maha.

pakettidel peaks olema algus- ja lõpukuupäev. Kui operaator otsustab, et mõnda paketti enam ei müüda, peab äriloogika keelama selle paketi müügi, kuid baasi peab see alles jääma.

lepingul peab olema ka algus- ja lõpukuupäev(ehk kui klient lepingu lõpetab, määratakse lõpu kuupäev mitte ei kustutata lepingut baasist)

lisateenusel peab samuti olema hind ja ühik(kas teenusel on kuumaks(näiteks kõnepost) või muu hinnaskeem nagu näiteks tüki või minutipõhine hind)

Tabeli Kõne asemel võiks olla tabel võrguteenused, kus oleks lisaks veerg teenuse liik(kõne, sms, mms, andmeside jne.). Soovitav on lisada ka lõpuaeg(reaalselt tuleb sealt võrgust veel hulk muid andmeid, aga antud ülesande puhul pole vist oluline)

Mina hoiaks pigem lepingu tabelis telefoninumbri id-d. Täiesti võimalik on ka olukord, kus number on võrgus avatud, aga pole seotud ühegi lepinguga(nagu näiteks aga mitte ainult kõnekaardid)

Tegelikult on juba ülesandes endas viga, Kõned tabelis peaks olema lisaks sihtriigile ka sihtvõrk kuna võrgusisese kõne hind on erinev. Teine võimalus on kasutada ainult sihtvõrku, mis võib asuda nii Eestis kui välismaal.

PS. kui reaalse baasi tegemiseni jõuad, siis mina ei soovitaks tabelite ja veergude nimedes täpitähti kasutada kuigi paljud mootorid seda lubavad kuna varem või hiljem sellest mingi jama tuleb...

PPS. kui misiganes baasi disainid, siis disaini see alati nii, et baasist mitte kunagi midagi ei kustutata ehk kasuta alati algus- ja lõpukuupäevi või kui kuupäev oluline pole, siis linnukest mitteaktiivne/kustutatud vms.

[serk]

linnukest üldjuhul ei soovita kasutada, alati on parem kasutada valid_from ja valid_to - määratleb väga selgelt kirje kehtivuse ja annab oluliselt rohkem paindlikust.

[RaidoR]

napoleon, Soovitad siis, et näiteks Kõnepaketi ja ValitudKõnePaketi vahel oleks tabel Hinnakiri? Ja Lepingu tabelis oleks LepinguID asemel TelefoniNR(PK)?

[neros]

Soovitan kindlasti tutvuda sellise asjaga http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

[napoleon]

Ei.

1. Oleks tabel hinnakiri mis oleks midagi sellist (hinnakiriID,KehtivAlates,KehtivKuni,PaketiID,Hind,yhik). Hinna leidmisel leiad kõigepealt kliendi kõnepaketi ja siis vastavalt kõne algusele otsid hinnakirjast hinna(muidugi võib hind lisaks olla ka ValitudKõnePakett tabelis, et saaks seda erikokkulepetega ainult ühel numbril muuta, aga see oleks antud ülesande puhul juba liiga advanced lähenemine). Ehk seos on number<-leping<-valitudpakett<-pakett<-hind. Mina oleks üritanud disaini nii teha, et kõik teenused on ühes tabelis(ja vajadusel lisatabelid konkreetse teenuse lisaparameetrite hoidmiseks), nagu isegi näed läheb selle lähenemisega kui iga teenus eraldi tabelis on hinnakirjaga sidumine keeruliseks kui just hinnakirja jaoks samuti nelja erinevat tabelit teha ei taha.

2. Mõtlesin niipidi, et numbrite tabel on lepingust sõltumatu ja lepingu tabelis on veerg, mis näitab milline number antud lepinguga seotud on st. lepingu tabelis on lisaks veerg numbriID ja number tabelist kaotaks viite lepingule ära. Kui reaalse elu peale mõelda, siis oleks sul numbrite tabelis kõik numbrid, mis operaatorile eraldatud on sh. ka need, mis veel või enam võrgus avatud pole, aga antud ülesandes pole see nõutud. Hetkel on sul lihtsam hallata asja nii, kui üks leping tähendab ühte numbrit kuna ülesandes pole nõutud, et ühe lepinguga mitu numbrit saaks siduda(ja kui sa seda teha tahaks, oleks vähemalt ühte tabelit juurde vaja)

[RaidoR]

Krt juba praegu läheb üle käte ära, kuna see esimene disain mis teen

[neros]

Sulle anti ka paras tükk teha muidugi. Mismoodi selline asi su esimene disain saab olla? Ma oleks arvanud, et esimeseks on ikka midagi lihtsamat nagu... töötajate andmebaas vms.

E: Ja kes see neeger on kes lilla täpi pani teemale? Ilusasti vormistatud teema millest võiks tulevikus kasu olla ju! Tegu pole niiväga kooliülesannete lahendamisega kui pigem hea nõu andmisega ja juhendamisega.

[Fukiku]

Võibolla teen teemaalgatajale liiga, aga lõhnab sedamoodi, et mingi andmebaaside teemalise ülikooli aine läks sessi ajal põlema ja üritatakse paaniliselt kursuse projektitööd valmis saada ilma vahepealseid loenguid ja harjutusi läbimata. Vabandan juba ette, kui tõesti teemaalgatajale liiga teen.

Ülesanne on iseenesest huvitav, aga niivõrd mahukas, et ei suuda leida endas energiat sellesse süvenemiseks ja nõu andmiseks. Olen ise kunagi ühe kohaliku mobiilioperaatori andmemudelit näinud päris palju.. aga peab tunnistama, et suurt muud ei mäleta peale selle, et üsnagi komplitseeritud koletis oli.

[neros]

Ei pruugi nii olla - andmebaasid võivad keerulisuse tasandilt hüpata äärmiselt lihtsast võimatuna näivani. Olen ise pidanud üüratuid andmemudeleid kokku panema/fixima/lugema ning lõbus tegevus see igatahes ei olnud Enamasti on muidugi kaks võimalikku tulemit - äärmiselt lihtne andmemudel kust ühe-kahe tabeli haaval on lihtne andmeid pärida, kuid võimatu / aeglane suuri päringuid teha või keeruline andmemudel kust saab väga lihtsalt joinidega andmeid pärida.

[RaidoR]

Fukiku, ei läinud midagi põlema ja kursusetöö teema juba valitud ning projekt esitatud Asi selles, et kohe kohe AB ja SQL eksam tulemas ning mõtlesin, et teen endale asja vähekene selgemaks, lahendades ülesandeid mis õppejõu poolt antud

Sain kasutajalt napoleon päris palju näpunäiteid ülesande lahendamiseks. Kui mingi lahenduse valmis saan, siis postitan teemasse.

Vaja veits abi ka päringutega. AB http://www.upload.ee/image/2389684/AB_ja_SQL__Ulesanne__Ulesanne_3._SQL___muujad__tellimused.png

1. Väljasta nende müüjate nimed ja vahendustasud, kellel on tellimused firmast "EestiEhitus". Tulemust näidata vahendustasu kahanevas järjekorras (alampäringuga)
2. Müüjate ja nende ostajte nimed, kaasarvatud need müüjad kellel pole ostjaid.
3. Nende müüjate nimed kellel on 2 või enam tellimust