Copyright © 2011 Accenture All Rights Reserved. Accenture, its logo, and High Performance Delivered are trademarks of Accenture.

Część 2: Normalizacja a wydajność

Optymalizacja aplikacji bazodanowych

Plan

• Normalizacja – przypomnienie• Denormalizacja• Konsekwencje denormalizacji dla odczytu i modyfikacji danych• Ćwiczenie

Gdzie jesteśmy?

• Dążenie do optymalizacji powinno następować na każdym etapie tworzenia systemu informatycznego.– Analiza

-> konstrukcja procesów biznesowych– Projektowanie

-> model danych-> moduły programowe-> dobór technologii

– Kodowanie-> jakość kodu

– Testy-> kontrola efektywności

– Wdrożenie-> konfiguracja środowiska

Normalizacja danych

• Celem normalizacji jest usunięcie niepożądanych cech struktury bazy danych.

• Przykłady cech niepożądanych to:– redundancja (nadmiarowość) danych – wpływa na wielkość zbiorów danych,– anomalia wstawiania – wstawienie nowego rekordu może wymagać

znajomości danych, które na początku nie są wymagane,– anomalia modyfikacji – modyfikacja danych w jednym miejscu wymusza

zmianę danych w wielu krotkach, co skutkuje ryzykiem utraty spójności,– anomalia usunięć – usunięcie jednych danych skutkuje zniknięciem innych

danych.• W wyniku normalizacji zmienia się struktura bazy danych, natomiast

dane nie są tracone.• Normalizacja jest dokonywana poprzez przekształcanie struktury bazy

danych do kolejnych postaci normalnych (ang. Normal Form)

Normalizacja danych

• Postacie zdefiniowane przez E.F.Codda– Pierwsza postać normalna = 1 PN = 1 NF– Druga postać normalna = 2 PN = 2 NF– Trzecia postać normalna = 3 PN = 3 NF

• Dodatkowe postacie, zdefiniowane w późniejszym okresie– Postać normalna Boyce’a - Codd’a = BCNF– Czwarta postać normalna– Piąta postać normalna

Normalizacja danych

Imię i Nazwisko Adresy

1. Jan Kowalski 1. Główny: 99-999 Łódź, Włókniarzy 10 m. 92. Korespondencyjny: 98-088 Łódź, Wyszyńskiego 23 m. 14

2. Anna Malinowska 1. Główny: 01-010 Warszawa, Jerozolimskie 102

3. Stefan Kwiatkowski 1. Główny: 34-567 Kraków, Stary Rynek 8 m. 52. Korespondencyjny: 33-333 Kraków, Dworcowa 12/13

Id osoby

Imię Nazwisko Id adresu

Rodzaj adresu

Kod Miejsco-wość

Ulica Nr domu Nr lokalu

1 Jan Kowalski 1 G 99-999 Łódź Al. Włókniarzy 10 9

2 K 98-088 Łódź Wyszyńskiego 23 14

2 Anna Malinowska 1 G 01-010 Warszawa Al. Jerozolimskie 102

3 Stefan Kwiatkowski 1 G 34-567 Kraków Start Rynek 8 5

2 K 33-333 Kraków Dworcowa 12/13

Normalizacja danych

Id osoby

Imię Nazwisko Id adresu

Rodzaj adresu

Kod Miejsco-wość

Ulica Nr domu Nr lokalu

1 Jan Kowalski 1 G 99-999 Łódź Al. Włókniarzy 10 91 Jan Kowalski 2 K 98-088 Łódź Wyszyńskiego 23 14

2 Anna Malinowska 1 G 01-010 Warszawa Al. Jerozolimskie 102

3 Stefan Kwiatkowski

1 G 34-567 Kraków Start Rynek 8 5

3 Stefan Kwiatkowski

2 K 33-333 Kraków Dworcowa 12/13

1PN

Id osoby

Imię Nazwisko Id adresu

Rodzaj adresu

Kod Miejsco-wość

Ulica Nr domu Nr lokalu

1 Jan Kowalski 1 G 99-999 Łódź Al. Włókniarzy 10 92 K 98-088 Łódź Wyszyńskiego 23 14

2 Anna Malinowska 1 G 01-010 Warszawa Al. Jerozolimskie 102

3 Stefan Kwiatkowski 1 G 34-567 Kraków Start Rynek 8 5

2 K 33-333 Kraków Dworcowa 12/13

Normalizacja danychId osoby

Imię Nazwisko Id adresu

Rodzaj adresu

Kod Miejsco-wość

Ulica Nr domu

Nr lokalu

1 Jan Kowalski 1 G 99-999 Łódź Al. Włókniarzy 10 9

1 Jan Kowalski 2 K 98-088 Łódź Wyszyńskiego 23 14

2 Anna Malinowska 1 G 01-010 Warszawa Al. Jerozolimskie 102

3 Stefan Kwiatkowski 1 G 34-567 Kraków Start Rynek 8 5

3 Stefan Kwiatkowski 2 K 33-333 Kraków Dworcowa 12/13

Id osoby

Imię Nazwisko

1 Jan Kowalski

2 Anna Malinowska

3 Stefan

Kwiatkowski

Id osoby

Id adresu

Rodzaj adresu

Kod Miejsco-wość

Ulica Nr domu

Nr lokalu

1 1 G 99-999 Łódź Al. Włókniarzy 10 91 2 K 98-088 Łódź Wyszyńskiego 23 14

2 1 G 01-010 Warszawa Al. Jerozolimskie 102

3 1 G 34-567 Kraków Start Rynek 8 53 2 K 33-333 Kraków Dworcowa 12/13

1PN

2PNW relacja (1PN) jest jeden klucz kandydujący (Id osoby, Id adresu).Imię i Nazwisko są funkcjonalnie zależnie od klucza kandydującego, ale nie są w pełni funkcjonalnie zależne od tego klucza, bo są również funkcjonalnie zależne od podzbioru atrybutów tego klucza – są zależne od (Id osoby).Atrybuty Rodzaj adresu, Kod, Miejscowość, Ulica, Nr domy, Nr lokalu są w pełni funkcjonalnie zależne od klucza kandydującego (Id osoby, Id adresu).

Normalizacja danych

Id osoby

Imię Nazwisko

1 Jan Kowalski

2 Anna Malinowska

3 Stefan

Kwiatkowski

2NF3NF

Id osoby

Imię Nazwisko

1 Jan Kowalski

2 Anna Malinowska

3 Stefan Kwiatkowski

Id osoby

Id adresu

Rodzaj adresu

Kod Miejsco-wość

Ulica Nr domu

Nr lokalu

1 1 G 99-999 Łódź Al. Włókniarzy 10 91 2 K 98-088 Łódź Wyszyńskiego 23 14

2 1 G 01-010 Warszawa Al. Jerozolimskie 102

3 1 G 34-567 Kraków Start Rynek 8 53 2 K 33-333 Kraków Dworcowa 12/13

Id osoby

Id adresu

Rodzaj adresu

Kod Ulica Nr domu

Nr lokalu

1 1 G 99-999

Al. Włókniarzy 10 9

1 2 K 98-088

Wyszyńskiego 23 14

2 1 G 01-010

Al. Jerozolimskie

102

3 1 G 34-567

Start Rynek 8 5

3 2 K 33-333

Dworcowa 12/13

Kod Miejscowość

99-999

Łódź

98-088

Łódź

01-010

Warszawa

34-567

Kraków

33-333

Kraków

Denormalizacja

• Wprowadzenie nadmiarowych danych do struktury bazy• Celem denormalizacji może być:– Zwiększenie wydajności systemu– Uproszczenie zapytań– Poprawienie przejrzystości struktury bazy danych

• Najpierw normalizacja, potem denormalizacja• Denormalizować należy świadomie• Sposób denormalizacji wynika ze specyfiki danego systemu;

nie podlega standaryzacji.• Czynniki wpływające na zasadność denormalizacji:– Częstość odczytu danych– Zmienność danych– Liczebność danych

Denormalizacja cd

• Najczęstsze sposoby denormalizacji– Powtórzenie kluczowych danych w kilku tabelach (nr klienta, nr

faktury)– Złączenia kilku tabel w jedną– Przechowanie kluczy obcych do tabel pośrednio powiązanych– Przechowywanie wartości wyliczonych (wartość faktury, średnia ocen,

licznik transakcji)

Przykład 1

• Model danych znormalizowany– Klient (nr klienta, nazwa, NIP)– Faktura (nr faktury, nr klienta, data)– Pozycja faktury (nr faktury, nr pozycji, id produktu, ilość, cena netto)– Produkt (id produktu, nazwa produktu, cena netto, stawka VAT)

• Model danych zdenormalizowany– Klient (nr klienta, nazwa, NIP)– Faktura (nr faktury, nr klienta, NIP, data, wartość netto, wartość

VAT, wartość brutto, liczba pozycji)– Pozycja faktury (nr faktury, nr pozycji, nr klienta, id produktu, nazwa

produktu, ilość, cena netto, wartość netto, stawka VAT, kwota VAT, cena brutto, wartość brutto)

– Produkt (id produktu, nazwa produktu, cena netto, stawka VAT, cena brutto)

Przykład 2

• Model danych znormalizowany– Osoba (id osoby, imię, nazwisko)– Adres osoby (id adresu, id osoby, typ adresu)– Adres (id adresu, kod pocztowy, id miejscowości, ulica, nr ulicy, nr

lokalu)– Kod pocztowy (kod pocztowy, poczta, województwo)– Miejscowość (id miejscowości, miejscowość)

• Model danych zdenormalizowany– Osoba (id osoby, imię, nazwisko, liczba adresów)– Adres (id adresu, id osoby, typ adresu, kod pocztowy, poczta,

województwo, miejscowość, ulica, nr ulicy, nr lokalu)

Przykład 3 (do ćwiczeń)

• Model danych znormalizowany– Stanowisko (Id stanowiska, nazwa, dodatek)– Pracownik (Id pracownika, imię, nazwisko, płeć, pensja, id

stanowiska, id szefa)– Kara (Id kary, z tytułu, id pracownika, kwota)– Nagroda (Id nagrody, z tytułu, id pracownika, kwota)

• Model danych zdenormalizowany– Stanowisko (Id stanowiska, nazwa, dodatek)– Pracownik (Id pracownika, imię, nazwisko, płeć, pensja, id

stanowiska, id szefa, liczba nagród, suma nagród, liczba kar, suma kar)

– Jednorazowa zmian wypłaty (Id zmiany, z tytułu, id pracownika, kwota, typ, id stanowiska)

Co o tym mówi Sieć(do poczytania w wolnej chwili)

• http://www.devblogi.pl/2010/03/moze-normalizowanie-nie-jest-normalne.html

„normalizuj dopóki to nie boli, denormalizuj dopóki to działa”• http://msdn.microsoft.com/pl-pl/library/ms191178.aspx• http://webmaster.helion.pl/index.php/kursmysql-projektowani

e-relacyjnych-baz-danych• http://www.zyxist.com/pokaz.php/kilka_sztuczek_ad_projekt

owania_baz_danych

Ćwiczenie

1. Zalogować się jako SYSTEM2. Założyć użytkownika i przelogować się na niego (1-1_user.sql)3. Założyć strukturę tabel (1-2_struktura.sql) i wypełnić danymi (1-

3_dane.sql)4. Założyć zdenormalizowaną strukturę tabel (2-

1_struktura_denormalizacja.sql) i wypełnić danymi (2-2_dane_denormalizacja.sql)

5. Dla obu struktur tabel napisać polecenia SQL wykonujące:a. Dodanie nowej karyb. Zmianę kwoty nagrody o id W z kwoty X na Y dla pracownika Zc. Raport zestawiający łączną kwotę wypłaty dla wskazanego pracownikad. Raport w postaci: Stanowisko, liczba pracowników, suma kar, suma

nagród.

Questions & Answers