Systemy zarządzania bazami danych

1. Systemy zarządzania bazami danych 14. Strojenie schematu

2. Schemat bazy danych • Schemat relacji składa się z nazwy relacji i zbioru atrybutów R(a int, b varchar[20]); • Egzemplarz relacji o schemacie R to skończony zbiór rekordów z atrybutami schematu R 14. Strojenie schematu

3. Schemat 1: OnOrder1(supplier_id, part_id, quantity, supplier_address) Schemat 2: OnOrder2(supplier_id, part_id, quantity); Supplier(supplier_id, supplier_address); Przestrzeń Schemat 2 zajmuje mniej miejsca Zachowywanie informacji Schemat 1 może gubić adresy dostawców (anomalie aktualizacyjne) Wydajność Jeśli często odczytuje się adres dostawcy na podstawie numeru zamówionej części, to schemat 1 jest dobry Jeśli jest wiele dodawanych wiele zamówień, schemat 1 jest słaby. Pewne schematy są lepsze od innych 14. Strojenie schematu

4. Zależności funkcyjne • X to podzbiór atrybutów relacji R, a A pojedynczy atrybut R. • X determinuje A (w R zachodzi zależność funkcyjna X A) wtw: • Dla każdego egzemplarza I relacji R, jeśli w dwóch rekordach r i r’ egzemplarza I są te same wartości atrybutów ze zbioru X, to rekordy r i r’ mają też tę samą wartość atrybutu A. • OnOrder1(supplier_id, part_id, quantity, supplier_address) • supplier_id supplier_address to istotna zależność funkcyjna 14. Strojenie schematu

5. Klucz relacji • Atrybuty ze zbioru X zawartego w R stanowią klucz R, wtw. X determinuje każdy atrybut R i żaden podzbiór właściwy X nie determinuje wszystkich atrybutów R • OnOrder1(supplier_id, part_id, quantity, supplier_address) • { supplier_id, part_id } jest kluczem • Supplier(supplier_id, supplier_address); • { supplier_id } jest kluczem 14. Strojenie schematu

6. Normalizacja • Relacja jest znormalizowana, wtw. w każdej istotnej zależności funkcyjnej X A na atrybutach R X jest kluczem R. • OnOrder1 nie jest znormalizowana OnOrder1(supplier_id, part_id, quantity, supplier_address) • OnOrder2 i Supplier są znormalizowane OnOrder2(supplier_id, part_id, quantity) Supplier(supplier_id, supplier_address) 14. Strojenie schematu

7. Przykład #1 • Bank przypisuje każdemu klientowi oddział. Każdy oddział podlega jakiemuś sądowi. • Czy poniższa relacja jest znormalizowana? R(customer, branch, jurisdiction) 14. Strojenie schematu

8. Przykład #1 – analiza • Jakie są zależności funkcyjne? • customer  branch • branch  jurisdiction • customer  jurisdiction • Kluczem jest zbiór { customer } • Zależność funkcyjna bez udziału atrybutu customer • R nie jest znormalizowana 14. Strojenie schematu

9. Przykład #2 • Lekarz może pracować w kilku szpitalach i dostaje osobne wynagrodzenie od każdego z nich. • Czy poniższa relacja jest znormalizowana? R(doctor, hospital, salary) 14. Strojenie schematu

10. Przykład #2 – analiza • Jakie są zależności funkcyjne? • doctor, hospital  salary • Kluczem jest zbiór { doctor, hospital } • Relacja jest więc znormalizowana 14. Strojenie schematu

11. Przykład #3 • Do relacji R(doctor, hospital, salary) dodajemy atrybut primary_home_address • Każdy lekarz ma adres stałego zamieszkania • Kilku lekarzy może mieć ten sam adres stałego zamieszkania (przychodzi baba do lekarza a lekarz też baba) • Czy poniższa relacja jest znormalizowana? R(doctor, hospital, salary, primary_home_address) 14. Strojenie schematu

12. Przykład #3 – analiza • Jakie są zależności funkcyjne? • doctor, hospital  salary • doctor  primary_home_address • doctor, hospital  primary_home_address • Klucz jest ten sam { doctor, hospital } • Tym razem mamy jednak zależność częściową • Dekompozycja na schematy znormalizowane: • R1(doctor, hospital, salary) • R2(doctor, primary_home_address) 14. Strojenie schematu

13. Projektowanie schematu w praktyce • Zidentyfikuj encje aplikacji (np., lekarze, szpitale, dostawcy) • Każdej encji dodaj atrybuty (szpital ma adres…). • Dwa ograniczenia na atrybuty: • Atrybut nie może mieć atrybutów • Encja atrybutu musi ten atrybut funkcyjnie determinować 14. Strojenie schematu

14. Projekt logiczny • Każda encja staje się relacją • Do tych relacji dodaje się relacje odzwierciedlające związki, np. • WorksIn (doctor_ID, hospital_ID) • Znajdź zależności funkcyjne między atrybutami i sprawdź, czy schemat jest znormalizowany: • Jeśli zachodzi zależność funkcyjna AB  C, to AB powinno być (nad)kluczem 14. Strojenie schematu

15. Fragmentacja pionowa • Trzy atrybuty: account_ID, balance, address • Zależności funkcyjne • account_ID  balance • account_ID  address • Dwa znormalizowane schematy • (account_ID, balance, address) ORAZ • (account_ID, balance) • (account_ID, address) • Który z nich jest lepszy? 14. Strojenie schematu

16. Wybór projektu zależy od wzorca zapytań: Aplikacja wysyłająca miesięczny wyciąg z konta jest głównym użytkownikiem adresu właściciela konta Saldo jest czytane i modyfikowane kilka razy dziennie Projekt 2 może być lepszy, ponieważ relacja (account_ID, balance) jest mniejsza Więcej par (account_ID, balance) mieści się w pamięci, więc zwiększa się współczynnik trafień Pełny przegląd zadziała szybciej, ponieważ przeczyta mniej stron Fragmentacja pionowa – analiza 14. Strojenie schematu

17. Strojenie normalizacji • Pojedyncza znormalizowana relacja XYZ jest lepsza od dwóch znormalizowanych relacji XY i XZ, o ile częste są zapytania o atrybuty XYZ (wtedy nie jest wymagane złączenie) • Dwie znormalizowane relacje są lepsze, o ile: • Użytkownicy zazwyczaj korzystają z atrybutów ze zbiorów Y i Z oddzielnie • Rozmiar wartości atrybutów Y i Z jest duży 14. Strojenie schematu

18. Antyfragmentacja pozioma • Dealerzy opierają swoje strategie kupna obligacji na trendach ich cen. Baza danych przechowuje ceny zamknięcia z ostatnich 3000 sesji, ale 10 ostatnich sesji jest szczególnie ważnych • Jaki schemat? • (bond_id, issue_date, maturity, …)(bond_id, date, price) Czy? • (bond_id, issue_date, maturity, today_price,…10dayago_price)(bond_id, date, price) • Inna możliwość: perspektywa zmaterializowana 14. Strojenie schematu

19. R (X,Y,Z) X to liczba całkowita YZ to długie napisy Pełny przegląd Fragmentacja pionowa jest wyraźnie gorsza, gdy oba atrybuty są czytane razem Fragmentacja pionowa daje przyspieszenie, o ile tylko jeden z Y albo Z jest odczytywany Fragmentacja pionowa i przegląd 14. Strojenie schematu

20. R (X,Y,Z) X to liczba całkowita YZ to długie napisy Zapytania punktowe czytające XYZ lub XY Fragmentacja pionowa poprawia wydajność, jeśli odsetek zapytań czytających tylko XY jest większy niż 20% Złączenie nie jest kosztowne w porównaniu z pojedynczym odczytem Fragmentacja i zapytania punktowe 14. Strojenie schematu

21. Strojenie denormalizacji • Denormalizacja oznacza pogwałcenie normalizacji w imię lepszej wydajności • Denormalizacja poprawia wydajność, jeśli atrybuty różnych znormalizowanych relacji są często odczytywane razem • Denormalizacja obniża wydajność, jeśli dane w relacjach są często modyfikowane 14. Strojenie schematu

22. Schemat: lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT ); region( R_REGIONKEY, R_NAME, R_COMMENT ); nation( N_NATIONKEY, N_NAME, N_REGIONKEY, N_COMMENT,); supplier( S_SUPPKEY, S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE, S_ACCTBAL, S_COMMENT); Wiersze lineitem = 600000 , nation = 25, region = 5, supplier = 500 Denormalizacja – stan przed 14. Strojenie schematu

23. Denormalizacja – stan po lineitemdenormalized ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE , L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS , L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT , L_SHIPMODE , L_COMMENT, L_REGIONNAME); • 600000 wierszy w lineitemdenormalized • Pusty bufor • Dual Pentium II (450MHz, 512Kb), 512 Mb RAM, dyski 3x18Gb (10000RPM), Windows 2000. 14. Strojenie schematu

24. Zapytania do obu schematów select L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT, R_NAME from LINEITEM, REGION, SUPPLIER, NATION where L_SUPPKEY = S_SUPPKEY and S_NATIONKEY = N_NATIONKEY and N_REGIONKEY = R_REGIONKEY and R_NAME = 'EUROPE'; select L_ORDERKEY, L_PARTKEY, L_SUPPKEY, L_LINENUMBER, L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE, L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT, L_REGIONNAME from LINEITEMDENORMALIZED where L_REGIONNAME = 'EUROPE'; 14. Strojenie schematu

25. Schemat TPC-H Zapytanie: znajdź wszystkie pozycje zamówień dla dostawców w Europie Schemat znormalizowany wymaga poczwórnego złączenia Po denormalizacji lineitem i wprowadzeniu nazwy regionu do lineitem otrzymujemy 30% poprawę wydajności Wyniki eksperymentu 14. Strojenie schematu