Majitel autobazaru z Vás byl nadšený a hned Vás doporučil svému kamarádovi, čerstvému majiteli realitní kanceláře. Ten po Vás chce, abyste pro něj navrhli databázi, přičemž doposud si záznamy vedl ve formě deníku. Jenže teď zjistil, že z toho vlastně nic nevyčte. Potřeboval by tedy data převést do nějaké databáze, která mu umožní jejich analýzu, a Vás si na to najal. A protože jste odbornicí na relační databáze a zejména PostgreSQL, zvolili jste právě tuto možnost. Jeho deník obsahuje následující data za prvních pár měsíců provozu. Navíc Vás po pročtení deníku napadlo pár dotazů, které majitel ihned zodpověděl (jsou pod deníkem). DENÍK -----------------> ČERVENEC 1.7.2022 - Tak jsem založil realitku. Zatím mám dva makléře a oběma dávám 40 000 měsíčně, k tomu pronájem kanclu... No, snad se nám to vrátí na provizích. 22.7.2022 - Nahlásit účtárně: Petr Chajda koupil byt 1+1 (Kosinova 10) od Jiřího Bolka, zaplatil za něj 3 000 000 korun, kde 150 000 byla naše provize (zprostředkoval to Josef Lemr, to se mu povedlo). 25.7.2022 - Včera Jan Suchý zařídil prodej pozemku na Holické v Olomouci za 2 500 000 korun Jiřímu Bolkovi. Původní majitel (Tomáš Marný) za něj chtěl 2 450 000 korun, takže nám i něco zůstalo. (poslat na účetní) SRPEN 6.8.2022 - Přibrali jsme nového makléře - Alois Boura (dal jsem mu plat 35 000 korun měsíčně, tak uvidíme). 13.8.2022 - Alois se činí, je tu týden a už prodal byt - tu 2+1 na Pavlovické 8 v Olomouci od Karla Vejpustka (za 2 000 000 korun Josefu Nejezchlebovi), provize z toho byla jen 50 000 korun, ale lepší než nic. ZÁŘÍ 7.9.2022 - Už skoro měsíc jsme nic neprodali, musím kluky trochu motivovat... 20.9.2022 - Konečně něco! Josef Lemr zazářil, provize čtvrt milionu na rodinném domku ve Velkém Týnci (Olomoucká 4); Ivana Blatná - majitelka - chtěla 5 000 000 korun ale Tomáš Marný dal dokonce 5 250 000, takže nám zůstala krásná provize). 23.9.2022 - Prodej řadovky ve Šternberku na Opavské 4 (zařídil Alois). Původní majitel: Jan Pavel, nový majitel: Jiří Lunt, cena: 6 000 000, provize: 5% 24.9.2022 - Poslat prodeje za poslední týden na účtárnu! ŘÍJEN 14.10.2022 - Pepa Lemr prodal ten domek (5+2) na Bělidlech s 3% provizí. To je dalších 300 000 korun pro nás. Paráda! Prodávající: Alena Koulová, kupující: Petr Chajda. 28.10.2022 - Petr Chajda prodal ten byt (Kosinova 10), co jsme mu sehnali před pár měsíci a celkem na tom vydělal - koupil ho Jiří Lunt za 3 500 000 korun, zprostředkoval to Honza Suchý a nám z toho připadlo 100 000. 30.10.2022 - Pepa Lemr se chtěl přestěhovat do Olomouce, tak jsme mu sehnali byt (2+1, Riegerova 10, prodávala Jiřina Nováková). Stálo ho to 4 000 000 korun. Tentokrát bez provize, vždyť je to kolega (a navíc si to zprostředkoval sám). <------------------- DOTAZY NA MAJITELE -------------------> Po schůzce nad deníkem jste si navíc udělali tyto poznámky s odpověďmi na otázky, které deník vyvolal: - DOTAZ: Provize realitní kanceláře v procentech se počítá z kupní, nebo prodejní ceny? ODPOVĚĎ: Provize je z prodejní ceny. Tedy byl-li například nějaký dům prodán za 100 000 korun a provize byla 5 procent, pak RK získala 5 000, prodávající získal 95 000 a kupující zaplatil 100 000. - DOTAZ: Kolik je pronájem kanceláře? ODPOVĚĎ: 25 000 korun za měsíc. - DOTAZ: Jaké jsou další náklady na provoz kanceláře? ODPOVĚĎ: Vychází to na paušálních 10 000 korun na měsíc. - DOTAZ: Přejete si uchovávat informaci o výdajích RK? ODPOVĚĎ: Celkové výdaje mě aktuálně nezajímají - to vyřeší účetní. Zajímají mě jen výplaty jednotlivých zaměstnanců a kolik jednotliví zaměstnanci přinesli RK na provizích. ULEHČENÍ: Navíc, abychom to zbytečně nekomplikovali, nepočítáme se zvyšováním platu a počítáme s tím, že (plný) plat člověk dostane již v měsíc nástupu. Stejně tak všechny platy byly zaplaceny i v aktuálním měsíci. - DOTAZ: Pracuje na každé zakázce jen jeden makléř? ODPOVĚĎ: Ano. - DOTAZ: Do databáze bych potřeboval vědět ještě tyto údaje: 22.7.2022 - město, ve kterém se byt prodal (tento údaj je potřebný i k 28.10.2022) 25.7.2022 - v adrese chybí číslo popisné 20.9.2022 - chybí počet místností v domě (není to zas tak nutný údaj, ale u jiného domu je ten počet uveden) 14.10.2022 - je třeba dodat přesnou adresu (ulice, číslo popisné, město). ODPOVĚĎ: 22.7.2022 - Adresa je Kosinova 10, Olomouc. 25.7.2022 - Adresa je Holická 20, Olomouc. 20.9.2022 - Dům je dispozice 6+1. 14.10.2022 - Adresa je Bystrovanská 28, Olomouc. - DOTAZ: Lze předpokládat, že prodej nemovitosti byl poslán účtárně, pokud majitel RK nenapsal k prodeji nemovitosti, že ji ještě má dodatečně posílat? Tedy, kde není poznámka o účtárně, tak jestli mohu počítat s tím, že už byl prodej u účtárny vyřízen? ODPOVĚĎ: Všechny objednávky z období pokrytého deníkem již byly předány účtárně a zpracovány. - DOTAZ: Další dotaz mám na počet místností. Ten je všude uveden až tedy na pozemek a na řadový dům, který se prodal 23.9.2022. Mohl bych poprosit o doplnění? ODPOVĚĎ: Dispozice dané řadovky jsou 4+1. U pozemku mohu dodat jedině výměru: $800 m^2$. - DOTAZ: Jak to funguje s provizemi (vzhledem k dotazu: Který zaměstnanec vydělal na provizích nejvíce v daném období)? ODPOVĚĎ: Provize jdou RK (to jsou jediné příjmy), zaměstnanci dostávají fixní plat. Tj. zde nás zajímá, který zaměstnanec v zadaném období získal pro RK nejvyšší provize. <------------------------ DALŠÍ INFORMACE K ZADÁNÍ ------------------------> Cílem úkolu je demonstrovat schopnost vyčíst a zpracovat důležitá data ze špatně strukturovaných a případně neúplných záznamů (obsahujících i nedůležité informace navíc) do podoby rozumně navržené relační databáze. Tedy navrhnout databázi (v PostgreSQL) někomu, kdo o ukládání a zpracování dat nic neví, ale přitom už nějaká data nasbíral. Máte-li pocit, že v deníku něco důležitého chybí, podívejte se do dotazů a odpovědí pod deníkem. Nic dalšího by chybět nemělo. Pokud byste přesto měli pocit, že něco chybí, ozvěte se. Tedy: 1, Navrhněte databázi. Relační/tabulkové proměnné navrhujte tak, abyste co nejvíce dodrželi principy probírané na přednáškách a cvičeních. Tedy například aby: - měly vhodně zvolené typy atributů (využijte i vlastní skalární typy, pokud to dává smysl), - byly v BCNF, - obsahovaly vhodné primární klíče, - obsahovaly vhodné cizí klíče, - obsahovaly další vhodná integritní omezení, pokud někde dávají smysl, - databáze jako celek neobsahovala redundance, kterým umíte zabránit, - ... 2, Databázi naplňte daty dle deníku. 3, Dále napište dotazy, které z databáze získají následující údaje (toto našeho majitele zajímá): A, Kdo prodal komu a za kolik nějakou nemovitost. B, Kolik RK vydělala na prodeji dané nemovitosti. C, Který zaměstnanec vydělal na provizích nejvíce v daném období. D, Všechny zaměstnance RK a pro každého z nich: - součet provizí, které pro RK získal (od jeho nástupu) - součet platů, které od RK obdržel (od jeho nástupu) - rozdíl součtu provizí a součtu platů (tedy čistá suma, kterou pro RK od jeho nástupu vydělal; záporný výsledek znamená, že danou sumu RK prodělal)