Úvod do databázových systémů B Ing. David Chudán 2. přednáška
Databáze, využití MS Excel
Obsah přednášky • Teoretické základy databázových systémů (slajdy 3-34) • MS Excel (slajdy 35-39) • Příklady na cvičení (slajdy 40-41) • Zdroje (slajd 42)
2
Co je to databáze <<< Kartotéka Informace neuspořádané
uspořádané
Pojem databáze • Databáze (DB) = množina dat (báze dat, zakódované informace) a nástrojů, které s těmito daty manipulují. • Data jsou organizovány v tabulkách • Databáze je uložená na disku v souboru • V přeneseném smyslu pojmem databáze označuje i databázový informační systém • Databáze lze vytvářet v programech MS Access, MS SQL Server, FoxPro, Sybase, Oracle, MySQL, OpenOffice.org Base, aj.
Příklady databází • • • • • • •
Pacienti (jméno, zdravotní stav, léky, …) Knihy (název, autor, rok vydání, …) Sklad (název, číselný kód, množství, …) Obyvatelé města (jméno, bydliště, …) Pozemky (číslo, místo, vlastník, …) Sbírka (název, datum získání, …) Zákazníci (jméno, nákup, …)
Role pokročilý uživatel
běžný uživatel
aplikační programátoři
aplikační programátoři
běžná uživatelka
správce databáze
Přístup k datům Sdílení dat aplikacemi, paralelní přístup
aplikace aplikace
DATA DATA aplikace
aplikace
Vrstva odstiňující aplikace od ukládání dat a chránící data.
Technologická architektura aplikační server
tenký klient tlustý klient
DATA
SŘBD (DBMS) – systém řízení báze dat (database management system) – říká se mu databázový server – např. IBM DB2, Oracle, Informix, MS SQL Server, Sybase, …,
MySQL, PostgreSQL, Firebird …
tenký klient
aplikační server
DATA
Tenký klient : např. web browser
Aplikační server: např. Apache s PHP Databázový server: např. MySQL
DATA
tlustý klient
Aplikační logika je v tlustém klientovi: –programován v C, Javě …, nebo v proprietárním prostředí příslušném k db serveru, nebo to může být i např. aplikace v MS Access. Databázový server může být např. Oracle…
file server
Datový soubor
„klientská databáze“
„Klientská databáze“ např. MS Access, Paradox, DBase
Databázový server vs. file server • Přístup k jednotlivým datovým položkám záznamů, nikoli k celému souboru – názvy pro různé objekty databázové struktury • na strukturu je možno se dotázat...
• Specifická přístupová práva k jednotlivým typům záznamů pro různé uživatele – v rámci OS má k datovému souboru přístup pouze SŘBD
Databázový server vs. file server • Zajišťování integrity a konzistence dat – Validace vstupu – Obnova po poruchách až k poslednímu konzistentnímu stavu
• Efektivní správa velkého objemu dat
Databázový systém • (maximální) požadavky – sdílení dat, paralelní přístup – nezávislost aplikací na fyzickém uložení dat, logický přístup k datům, centrální popis dat – ochrana před neoprávněným přístupem před poruchami – kontrola konzistence dat – velké objemy dat
Různé modely pro databázovou strukturu d a ta b á zo vé m o d e ly
SŘBD
1960 s íťo vý , h ie ra rc h ic k ý re la č n í
s íťo vé , h ie ra rc h ic k é
1970 re la č n í
1980
o b je k to vý m u ltid im e n zio n á ln í o b je k to vě -re la č n í
o b je k to vé
1990 d a ta w a re h o u s e
o b je k to vě -re la č n í
Databázová struktura obecně • Definují se typy záznamů – výčet položek, jejich typů (domén) a jmen
• V typu záznamu může být určen primární klíč – skupina položek k jednoznačné identifikaci záznamu – užití v relačních a objektových databázích
• Záznamy mohou být vyhledávány navigací podle odkazů mezi záznamy – v síťových a objektových databázích
• Vyhledávat se může také sekvenčně – hierarchických databázích – když to nejde jinak
Data a vztahy mezi nimi Jméno
Jan
Adresa-město
Praha 4
Adresa-ulice
Sládkovičova 13 690318/1478
Sídlo-ulice
Havlova 3 Oddělení
31
54 800 Kč
Prodej
Základní plat
Číslo dveří Adresa-město
Jméno
Anna
Sezimovo Ústí Adresa-ulice
855912/0671
Kollárova 4 Foto
22 400 Kč Základní plat
Foto
Relační databáze Zaměstnenec RČ
Adresa-město
Jméno
Adresa-ulice
Základní plat
Foto
Oddělení
690318/1478 Jan
Praha 4
Sládkovičova 13 54 800 Kč
Prodej
855912/0671 Anna
Sezimovo Ústí
Kollárova 4
Prodej
Oddělení Číslo dveří
Název
Sídlo-ulice
Prodej
Havlova 3 31
Vedoucí
690318/1478
Primární klíče
22 400 Kč
Relace, relační tabulky Cizí klíče
Relační databáze • Data jsou organizována do tabulek – Jeden záznam = jeden řádek nějaké tabulky – Sloupce tabulky tvoří pole/položky záznamů
• Položky jsou atomické – nejsou složeny z částí, které by nesly nějaký význam (ve světě aplikace) – není v nich více údajů
Struktura relačního modelu • Data v databázi jsou organizovány v tabulkách – Jedna databáze může obsahovat i více tabulek
• Vztahy a vazby mezi tabulkami popisují tzv. relace • Každá tabulka tvořena jednotlivými záznamy (řádek tabulky) – Jeden záznam pro každého pacienta, knihu, pracovníka, …
• Každý záznam se skládá z určitých datových polí (sloupce tabulky), označují se též jako položky nebo atributy – Např. jméno, příjmení, datum narození, atd., pro pacienta
• Struktura tabulky = jaká datová pole tabulka obsahuje • Struktura databáze = jaké tabulky (a vztahy mezi nimi) tvoří celou databázi
Tabulka
Relační databáze • Položky v relační databázi jsou atomické – nejsou složeny z částí, které by nesly nějaký význam (ve světě aplikace) – není v nich více údajů
Co jsou atomické položky? Nejsou atomické: Vysoká škola ekonomická Vysoká škola finanční a správní
W.Churchilla 4, 224-095-111 130 00 Praha 3 Estonská 500, 210-088-800 101 00 Praha 10 271-741-597
Vnitřní struktura významů.
Více hodnot se stejným významem.
Svět aplikace
Číslo objednávky
Zákazník
Zaměstnanec
Datum objednávky
Dodat dne
11074
SIMOB
King, Robert
06.05.1998
03.06.1998
11075
RICSU
Callahan, Laura
06.05.1998
03.06.1998
11076
BONAP
Peacock, Margaret
06.05.1998
03.06.1998
Číslo objednávky
Kód zákazníka
Výrobek
Jednotková cena
Mnoţství
databázová struktura
Sleva
Číslo výrobku
Název výrobku
Dodavatel
11074
16
436,25 Kč
14
5,00%
11075
2
475,00 Kč
10
15,00%
1
Chai
Aux joyeux ecclésiastiques
11075
46
300,00 Kč
30
15,00%
2
Chang
Exotic Liquids
11075
76
450,00 Kč
2
15,00%
…
11076
6
625,00 Kč
20
25,00%
46
Spegesild
Lyngbysild
11076
14
581,25 Kč
20
25,00%
…
11076
19
230,00 Kč
10
25,00%
76
Lakkalikööri
Karkki Oy
Firma
Adresa
Město
Země
RICAR
Ricardo Adocicados
Av. Copacabana, 267
Rio de Janeiro
Brazílie
RICSU
Richter Supermarkt
Grenzacherweg 237
Ţeneva
Švýcarsko
ROMEY
Romero y tomillo
Gran Vía, 1
Madrid
Španělsko
relační databáze
Datová pole vs. datové typy • Pole tabulky mohou být, podle charakteru údaje, který obsahují, různého druhu (datového typu): – Text – Číslo (celé, reálné) • Automatické číslo
– Datum – Logická hodnota (ANO / NE) – Memo (poznámka) – Objekt OLE (např. obrázek) – Hypertextový odkaz
Vlastnosti datového pole 1 • Pole mají definovanou určitou délku, danou počtem znaků nebo míst u čísel, a taky formát • Lze také definovat masku pole, tj. jak mají zadávané hodnoty vypadat (např. rodné číslo) • S hodnotami v polích typu číslo lze provádět všechny aritmetické operace • Hodnoty v polích typu číslo, text či datum lze uspořádat (vzestupně či sestupně) • Pokud při výpočtu vznikne číslo, které se do pole nevejde, dojde k chybě • Volitelná vlastnost „Nutno zadat (required) hodnotu“
Vlastnosti datového pole 2 • „Primární klíč“ – speciální vlastnost, označení nějakého jiného pole – Obvykle celé kladné číslo nebo automatické číslo, jedinečné pro každý záznam v celé tabulce
• Pole mohou mít určena omezení na zadávané hodnoty (tzv. ověřovací pravidlo) – Omezovací text se vypíše, když pravidlo není splněno
• Předdefinovaná hodnota (default) – Automaticky vyplněná hodnota, uživatel ji může přepsat
• Povolení nulové délky hodnoty (prázdné políčko) • Indexování podle pole – Pro rychlejší vyhledávaní
Relace • Databáze jsou tvořeny jednotlivými tabulkami, vztahujícími se k určitému předmětu (osobě, činnosti) – V DB knihovny je např. vhodné mít tabulky pro knihy, autory, apod.
• Tabulky jsou mezi sebou propojeny přes určitá pole, tomuto propojení se říká relace • Relace zpřehledňuje databázi, zabraňuje vícenásobnému zadávání stejných údajů • K propojení polí v záznamech v různých tabulkách se používají tzv. sekundární klíče – V tabulce je pole, jehož hodnota se „odvolává“ na primární klíč v jiné tabulce
Typy relací • Relace 1 : 1 – 1 kniha – 1 autor (nepraktické a zbytečné)
• Relace 1 : N – 1 autor – více knih
• Relace M : N – Více autorů – více knih (rozšířeni relace 1 : N i „opačným“ směrem – 1 knihu může být i od více autorů)
• MS Access nabízí k zobrazení relacemi propojených záznamů v tabulkách tzv. vnořené datové listy – U propojených záznamů v relaci 1 : N se po kliknutí na záznam na straně 1 relace otevřou záznamy z jiné tabulky
32
Význam databází • Z předešlého stručného výčtu nasazení databázových aplikaci je zřejmý jejich obrovský význam. Dnešní společnost eviduje v databázích téměř všechno. – Co není v databázích, vlastně neexistuje. (Parafráze na starý latinský výrok: „Qout non in actis est non in mundis.“)
• Problematika ochrany dat v databázích je velmi závažná! – Více než 90% peněz v ekonomice nejsou fyzické peníze (bankovky, mince), ale jen čísla na účtech podniků a občanů, uložená v nějaké databázi v počítači nějaké banky. – Databáze pacientů u lékařů obsahují často citlivé údaje, které mohou být proti nim zneužity.
Význam databází • Současný rozvoj a masové rozšíření počítačů jsou v hlavní míře zásluhou nasazování DB aplikací. Vývoj počítačů stál (a stojí) stamiliardy dolarů, musela proto existovat oblast jejich nasazení, která tyto peníze byla (a je) schopna vydělat. Touto oblastí jsou právě databáze – firmy a instituce jsou za kvalitní, rychlou a dostupnou evidenci toho co potřebují ke své činnosti zaplatit nemalou částku. Žádné jiné využití (textové a tabulkové zpracování dat, grafika, multimédia, modelování a simulace, hry) by tyto částky na vývoj a hromadnou levnou velkovýrobu ICT v jejich počátcích nezajistilo.
MS Excel a databáze • MS Excel nabízí databázové funkce, pomocí kterých je možné vypočítat například sumu, průměr, maximum, minimum a počet. • Rozdíl oproti standardnímu použití těchto funkcí spočívá možnosti definovat určité podmínky, například maximum. Hodnoty nad toto maximum do výpočtu nebudou zahrnuty. • Příklad: funkce DAVERAGE
Příklad – funkce DPRŮMĚR DPRŮMĚR(databáze;pole;kritéria) • Databáze je oblast buněk, která tvoří seznam nebo databázi. Databáze je seznam souvisejících dat, ve kterých řádky souvisejících informací představují záznamy a sloupce dat jsou pole. První řádek seznamu obsahuje popisky sloupců. • Pole určuje, který sloupec je ve funkci používán. Argument pole může být zadán jako text s popiskem sloupce v uvozovkách, například "Stáří" nebo "Výnos", nebo jako číslo představující umístění sloupce v seznamu: hodnota 1 představuje první sloupec, hodnota 2 druhý sloupec atd. • Kritéria je oblast buněk, která obsahuje zadaná kritéria. Pro argument kritéria můžete použít libovolnou oblast, která zahrnuje nejméně jeden popisek sloupce a nejméně jednu buňku pod popiskem sloupce určující podmínku sloupce.
Řešený příklad, funkce DPOČET Vytvořte databázovou funkci, která vrátí počet hodnot, jejichž spotřeba je menší než 10 litrů a průměrná rychlost je větší než 55km/h.
Řešený příklad
Požadované DB funkce • DPOČET - vrátí počet buněk obsahující čísla v poli (sloupci) záznamů databáze, které splňují zadaná kritéria. • DPRŮMĚR – viz předchozí slide • DSUMA – sečte čísla v poli (sloupci) záznamů databáze, která splňují zadaná kritéria
Procvičení I. V souboru Excel_DB: 1. Vytvořte databázovou funkci, která vrátí počet hodnot, jejichž záznam v poli „tankováno“ je větší než 50. 2. Vytvořte databázovou funkci, která vrátí počet hodnot, jejichž záznam v poli „průměrná rychlost“ je menší než 50 a zároveň „spotřeba“ je větší než 11.
Procvičení II. V souboru Excel_DB: 3. Vytvořte databázovou funkci, která vrátí průměr hodnot sloupce „spotřeba“, jejichž záznam v poli „průměrná rychlost“ je větší než 80. 4. Vytvořte databázovou funkci, která vrátí součet hodnot sloupce „km“, jejichž záznam v poli „spotřeba“ je menší než 9.
Procvičení III. 5. Vytvořte vlastní tabulku, která bude obsahovat alespoň 4 číselné sloupce a 10 řádků. Tabulka a sloupce by měly být smysluplné, vyplněné hodnoty mohou být náhodné. Nad vytvořenou tabulkou vymyslete zadání alespoň 3 příkladů a příklady vyřešte.
Zdroje • • •
Kubačák, Martin. Tvorba databáze & MS Access 1,2 Dostupné na: dec52.lf1.cuni.cz/~mkuba/vyuka/files/P08-INF2009-2010-Databaze.ppt Palovská, Helena. Slajdy k předmětu Databázové systémy. Dostupné na: http://nb.vse.cz/~palovska/uds/databazove_systemy.pdf DPRŮMĚR. MS Office Excel. Dostupné na: http://office.microsoft.com/cs-cz/excel-help/dprumer-HP005209045.aspx
