Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00062 009311 11231101 na godz. na dobę w sumie
Mistrzowski SQL. 61 technik pisania wydajnego kodu SQL - ebook/pdf
Mistrzowski SQL. 61 technik pisania wydajnego kodu SQL - ebook/pdf
Autor: , , Liczba stron: 320
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-283-3564-6 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> postgresql - programowanie
Porównaj ceny (książka, ebook (-20%), audiobook).

Bazy danych umożliwiają bezpieczne przechowywanie i użytkowanie danych. Dobrze napisana baza danych pracuje szybko i wydajnie. Najlepsze relacyjne bazy danych są nierozłącznie związane z językiem SQL, zatem aby profesjonalnie podejść do tego tematu, trzeba ten język dobrze opanować. SQL może wydawać się trudny i złożony, a co więcej, dla poszczególnych baz istnieją specyficzne dialekty SQL, niemniej jednak wspaniałe zalety najlepszych baz danych dostępne są tylko dla tych, którzy opanują sztukę pisania efektywnego kodu SQL.

Niniejsza książka jest świetnym kompendium przeznaczonym dla osób o podstawowej znajomości SQL. Dzięki niej poznasz najlepsze współczesne techniki pisania w tym języku. Każdą z nich poparto realistycznymi (i przydatnymi!) przykładami. Innymi słowy, znajdziesz tu zrozumiale objaśnione sztuczki ekspertów i mnóstwo użytecznego kodu. Poza zagadnieniami składni omówiono również tematykę optymalizacji projektu bazy, a także zarządzania hierarchiami i metadanymi. Wyjątkowość tej książki polega na tym, że zawarty w niej materiał bez trudu zastosujesz do baz: IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database czy PostgreSQL.

Spośród 61 zagadnień ujętych w książce warto wspomnieć o:

Przekonaj się, jak szybki i wydajny może być Twój kod SQL!


John L. Viescas — zajmuje się bazami danych od ponad 45 lat. Rozwiązywał problemy baz Access i SQL Server w firmach każdej wielkości. W Applied Data Research prowadził zespół rozwijający systemy bazodanowe IBM dla komputerów mainframe.

Douglas J. Steele — od ponad 40 specjalizuje się w bazach danych i modelowaniu danych. Przez 17 lat był nagradzany tytułem MVP Microsoftu.

Ben G. Clothier — MVP od roku 2009, jest architektem rozwiązań w znanej firmie programistycznej IT Impact. Wszyscy trzej są autorami i współautorami cenionych książek o bazach danych.

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

Tytuł oryginału: Effective SQL: 61 Specific Ways to Write Better SQL Tłumaczenie: Jakub Hubisz ISBN: 978-83-283-3563-9 Projekt okładki: Studio Gravite / Olsztyn Obarek, Pokoński, Pazdrijowski, Zaprucki Authorized translation from the English language edition, entitled: EFFECTIVE SQL: 61 SPECIFIC WAYS TO WRITE BETTER SQL; ISBN 0134578899; by John L. Viescas; and by Douglas J. Steele; and by Ben G. Clothier; published by Pearson Education, Inc, publishing as Addison-Wesley Professional. Copyright © 2017 by Pearson Education, Inc. All rights reserved. No part of this book may by reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from Pearson Education, Inc. Polish language edition published by HELION S.A. Copyright © 2017. Some of the examples used in this book originally appeared in SQL Queries for Mere Mortals®: A Hands-On Guide to Data Manipulation in SQL, Third Edition (Addison-Wesley, 2014). These examples appear with permission from the authors and Pearson Education Inc. Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną, fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym powoduje naruszenie praw autorskich niniejszej publikacji. Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi ich właścicieli. Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje były kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz Wydawnictwo HELION nie ponoszą również żadnej odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji zawartych w książce. Materiały graficzne na okładce zostały wykorzystane za zgodą Shutterstock Images LLC. Wydawnictwo HELION ul. Kościuszki 1c, 44-100 GLIWICE tel. 32 231 22 19, 32 230 98 63 e-mail: helion@helion.pl WWW: http://helion.pl (księgarnia internetowa, katalog książek) Drogi Czytelniku! Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres http://helion.pl/user/opinie/missql Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję. Printed in Poland. • Kup książkę • Poleć książkę • Oceń książkę • Księgarnia internetowa • Lubię to! » Nasza społeczność Spis treści Przedmowa .......................................................................................11 Podziękowania ..................................................................................13 O autorach .......................................................................................15 O korektorach merytorycznych ........................................................17 Wprowadzenie ..................................................................................19 Krótka historia SQL ..................................................................................................19 Bazy danych, które wzięliśmy pod uwagę ..................................................................23 Przykładowe bazy .....................................................................................................24 Gdzie znaleźć przykłady ............................................................................................24 Podsumowanie rozdziałów ........................................................................................25 Rozdział 1: Projektowanie modelu danych ..........................................27 Zagadnienie 1: Sprawdzenie, czy wszystkie tabele posiadają klucz główny ................27 Zagadnienie 2: Eliminacja nadmiarowego przechowywania danych ...........................31 Zagadnienie 3: Pozbywanie się powtarzających się grup ............................................34 Zagadnienie 4: Przechowywanie jednej właściwości w kolumnie ................................37 Zagadnienie 5: Dlaczego przechowywanie danych wyliczeniowych zazwyczaj nie jest dobrym pomysłem ........................40 Zagadnienie 6: Definiowanie kluczy obcych do ochrony integralności referencyjnej ...44 Zagadnienie 7: Upewnij się, że relacje między tabelami mają sens ............................48 Zagadnienie 8: Gdy 3NF to za mało, normalizuj dalej ................................................51 Zagadnienie 9: Wykorzystanie denormalizacji w magazynach danych .......................57 Rozdział 2: Programowalność i projektowanie indeksów ....................61 Zagadnienie 10: Podczas tworzenia indeksów weź pod uwagę wartości NULL ............62 Zagadnienie 11: Rozważne tworzenie indeksów w celu minimalizacji skanowania indeksów i tabel ...........................66 Zagadnienie 12: Wykorzystanie indeksów nie tylko do filtrowania .............................70 Zagadnienie 13: Nie przesadź z wyzwalaczami ..........................................................74 Poleć książkęKup książkę 8 S p i s t reś ci Zagadnienie 14: Rozważ użycie indeksu filtrowanego do wykluczenia lub zawarcia podzbioru danych ..............................78 Zagadnienie 15: Wykorzystanie deklaratywnych więzów integralności zamiast sprawdzeń programistycznych ...........81 Zagadnienie 16: Rozpoznanie, z jakiego dialektu SQL korzysta Twój produkt, i wykorzystanie tej informacji ..........................................................83 Zagadnienie 17: Kiedy wykorzystywać wartości wyliczane w indeksach ....................86 Rozdział 3: Gdy nie możesz zmienić projektu .................................... 91 Zagadnienie 18: Wykorzystanie widoków do uproszczenia tego, czego nie możesz zmienić ................................................................91 Zagadnienie 19: Wykorzystanie ETL do zmiany danych nierelacyjnych w informacje ...97 Zagadnienie 20: Tworzenie tabel z podsumowaniem i ich utrzymywanie .................101 Zagadnienie 21: Wykorzystanie zapytania UNION do przestawienia nieznormalizowanych danych .............................104 Rozdział 4: Filtrowanie i wyszukiwanie danych ............................... 111 Zagadnienie 22: Algebra relacyjna i jej wykorzystanie w SQL ..................................111 Zagadnienie 23: Odszukiwanie rekordów niepasujących lub brakujących ...............117 Zagadnienie 24: Kiedy do rozwiązania problemu wykorzystać klauzulę CASE .........120 Zagadnienie 25: Znane techniki rozwiązywania problemów z wieloma kryteriami ....124 Zagadnienie 26: Dzielenie danych, gdy konieczne jest idealne dopasowanie ............129 Zagadnienie 27: Poprawne filtrowanie zakresu dat dla kolumny zawierającej datę i czas .............................................132 Zagadnienie 28: Pisanie zapytań w taki sposób, aby system na pewno wykorzystał indeksy ....................................136 Zagadnienie 29: Poprawne filtrowanie „prawej” strony „lewego” złączenia ...............140 Rozdział 5: Agregacje ...................................................................... 143 Zagadnienie 30: Jak działa GROUP BY ...................................................................143 Zagadnienie 31: Rozmiar klauzuli GROUP BY .........................................................150 Zagadnienie 32: Wykorzystanie GROUP BY/HAVING do rozwiązywania skomplikowanych problemów .......................................................152 Zagadnienie 33: Odszukiwanie wartości maksymalnych i minimalnych bez wykorzystania GROUP BY ...............................157 Zagadnienie 34: Unikanie błędnego wyniku funkcji COUNT() podczas korzystania z OUTER JOIN ..............................................162 Zagadnienie 35: Uwzględnienie rekordów z wartością zerową podczas sprawdzania HAVING COUNT(x) jakaś liczba ................165 Zagadnienie 36: Wykorzystanie DISTINCT do zliczania unikalnych wartości ...........168 Zagadnienie 37: Jak korzystać z funkcji okna .........................................................171 Zagadnienie 38: Tworzenie numerów wierszy i rankingu rekordów na podstawie innych rekordów ......................................................174 Zagadnienie 39: Tworzenie ruchomej agregacji .......................................................176 Poleć książkęKup książkę S p i s t reś ci 9 Rozdział 6: Podzapytania ................................................................183 Zagadnienie 40: Gdzie można wykorzystać podzapytania ........................................183 Zagadnienie 41: Różnica pomiędzy podzapytaniami skorelowanymi i nieskorelowanymi .......................................................................188 Zagadnienie 42: Wykorzystanie wspólnych wyrażeń tabelarycznych zamiast podzapytań ......................................................................193 Zagadnienie 43: Tworzenie bardziej wydajnych zapytań z wykorzystaniem złączeń zamiast podzapytań ..............................199 Rozdział 7: Pobieranie i analizowanie metadanych ..........................203 Zagadnienie 44: Jak korzystać z analizatora zapytań swojego systemu ...................203 Zagadnienie 45: Pobieranie metadanych o Twojej bazie ..........................................213 Zagadnienie 46: Jak działa plan zapytania .............................................................218 Rozdział 8: Iloczyny kartezjańskie ..................................................227 Zagadnienie 47: Utworzenie kombinacji rekordów pomiędzy dwiema tabelami i oznaczenie tych rekordów z drugiej tabeli, które niebezpośrednio odnoszą się do pierwszej .............................227 Zagadnienie 48: Ustalanie rankingu rekordów na podstawie równych kwantyli ......230 Zagadnienie 49: Łączenie w pary rekordów tabeli ze wszystkimi innymi rekordami ...235 Zagadnienie 50: Wyświetlanie kategorii i liczby rekordów preferowanych ................239 Rozdział 9: Tabele kalkulacyjne .......................................................245 Zagadnienie 51: Wykorzystanie tabeli kalkulacyjnej do generowania rekordów z wartością NULL na podstawie parametru ....................................245 Zagadnienie 52: Sekwencjonowanie za pomocą tabel kalkulacyjnych i funkcji okna ................................................................................249 Zagadnienie 53: Generowanie wielu rekordów na podstawie zakresów wartości w tabelach kalkulacyjnych ........254 Zagadnienie 54: Konwertowanie wartości w jednej tabeli na podstawie zakresu wartości w tabeli kalkulacyjnej ...................258 Zagadnienie 55: Wykorzystanie tabeli z datami do uproszczenia obliczeń na datach ..............................................264 Zagadnienie 56: Tworzenie tabeli kalendarza spotkań z datami zdefiniowanymi w zakresie ..............................................270 Zagadnienie 57: Obracanie tabeli z wykorzystaniem tabeli kalkulacyjnej ................272 Rozdział 10: Modelowanie danych hierarchicznych ..........................279 Zagadnienie 58: Wykorzystanie modelu listy graniczenia jako punktu startu ..........280 Zagadnienie 59: Wykorzystanie zagnieżdżonych zbiorów do wydajnego wyszukiwania przy sporadycznych aktualizacjach ...282 Zagadnienie 60: Wykorzystanie zmaterializowanej ścieżki, prostej w przygotowaniu i dającej ograniczone możliwości przeszukiwania ........................................285 Zagadnienie 61: Wykorzystanie domknięcia podległości dla zaawansowanego wyszukiwania ..............................................287 Poleć książkęKup książkę 10 S p i s t reś ci Dodatek A: Typy, operatory i funkcje dla dat i czasu ....................... 293 IBM DB2 ................................................................................................................293 Microsoft Access .....................................................................................................297 Microsoft SQL Server ..............................................................................................299 MySQL ..................................................................................................................302 Oracle ....................................................................................................................307 PostgreSQL ............................................................................................................309 Skorowidz ..................................................................................... 311 Poleć książkęKup książkę 1 Projektowanie modelu danych „Nie zrobisz jedwabnej sakiewki z ucha maciory”. Ten cytat, przypisywany żyjącemu w XVI w. angielskiemu satyrykowi Stephenowi Gossonowi, zdecydowanie ma przełożenie na bazy danych. Nie możesz pisać efektywnych zapytań SQL, pracując ze złym modelem danych. Gdy Twój model danych nie jest poprawnie znormalizowany i nie ma zdefiniowanych relacji, wyciąganie danych przy wykorzystaniu języka SQL będzie trudne lub nawet niemożliwe. W tym rozdziale omówimy podstawy dobrego projektu relacyjnego. Jeżeli Twoja baza łamie którąś z podanych tutaj zasad, musisz znaleźć problem i go rozwiązać. Jeżeli to nie Ty kontrolujesz projekt, dowiesz się przynajmniej, co powoduje problemy, a to pozwoli Ci przedstawić potencjalne usprawnienia osobom odpowiedzialnym za projekt. Możesz wykorzystać informacje z tego rozdziału do wyjaśnienia, dlaczego trudne lub wręcz niemożliwe jest napisanie jakiegoś zapytania, o które zostałeś poproszony. Jeżeli nie możesz naprawić projektu bazy, np. ze względu na brak uprawnień lub dlatego, że baza należy do kogoś innego, zapoznaj się z rozdziałem 3. „Gdy nie możesz zmienić projektu”, który przedstawia kilka technik SQL pozwalających obejść niektóre problemy. Nie jesteśmy w stanie omówić wszystkich niuansów projektowania baz danych, przedstawiamy jedynie podstawy. Jeżeli chcesz dokładniej zgłębić temat tworzenia relacyjnych baz danych, zapoznaj się z dobrą książką na ten temat, np. Projektowanie baz danych dla każdego. Przewodnik krok po kroku Michaela J. Hernandeza (Helion, 2014). Zagadnienie 1: Sprawdzenie, czy wszystkie tabele posiadają klucz główny Ponieważ przestrzeganie modelu relacyjnego wymaga, aby Twój system bazodanowy był w stanie odróżnić pojedynczy rekord od wszystkich innych, każda z tabel powinna mieć kolumnę lub zestaw kolumn zdefiniowany jako klucz główny. Zawartość klucza głównego musi być unikalna dla każdego rekordu i nie może mieć wartości null (spójrz na zagadnienie 10, „Podczas Poleć książkęKup książkę 28 R o z dz i ał 1. Projektowanie modelu danych tworzenia indeksów weź pod uwagę wartości NULL”). Bez klucza głównego nie ma możliwości zapewnienia, że podczas filtrowania znajdziesz dokładnie zero lub jeden z rekordów. Problemem jest jednak to, że utworzenie tabeli bez klucza głównego jest możliwe. Co więcej, samo posiadanie kolumn nieprzyjmujących wartości null i unikalnych w ramach tabeli nie oznacza jeszcze, że baza będzie w stanie wykorzystać kolumny efektywnie. Musisz jawnie wskazać klucz główny. Bez zdefiniowanego klucza głównego zazwyczaj niemożliwe (lub niewskazane) jest również modelowanie relacji między tabelami. Gdy w tabeli brakuje klucza głównego, występować może wiele rodzajów problemów, w tym powtarzające się i niespójne dane, wolno działające zapytania i niedokładne informacje w raportach. Rozważmy przykład tabeli zawierającej zamówienia (Orders), przedstawionej na rysunku 1.1. Rysunek 1.1. Przykład niespójnych danych Z punktu widzenia komputera wszystkie wartości na rysunku 1.1 są unikalne, ale może być tak, że wszystkie reprezentują tę samą osobę — z pewnością rekordy 1, 2 i 4 są takie same (wariacje nazwiska John A. Smith). Wprawdzie komputery przetwarzają dane znacznie szybciej niż ludzki umysł, ale bez specjalistycznych programów nie są zbyt dobre w określaniu, kiedy dane powinny być uznawane za takie same. Tak więc, jeżeli nawet kolumnę Customer (klient) oznaczymy jako klucz główny tabeli, nie oznacza to, że jest to dobry wybór, ponieważ nie gwarantuje wystarczającej unikalności. Jak zatem określić dobrego kandydata na klucz główny? Kolumna lub kolumny powinny posiadać następujące cechy:  Muszą przechowywać unikalne wartości.  Nie mogą przyjmować wartości null.  Powinny być stabilne, to znaczy nigdy nie może wystąpić konieczność aktualizacji wartości.  Powinny być możliwie proste (np. wykorzystana powinna być liczba całkowita zamiast zmiennoprzecinkowej, preferowana jest jedna kolumna zamiast wielu itp.). Powszechna metoda osiągnięcia powyższych atrybutów to wykorzystanie jako klucza głównego sztucznie generowanych danych numerycznych, które nic nie znaczą. W zależności od wykorzystywanego systemu zarządzania danymi Poleć książkęKup książkę Z a g ad n i e n i e 1 : S p r a w d z e n i e , c zy w sz ys t k i e t a b el e p o sia d a ją klucz g łó w ny 29 (RSZBD) taki mechanizm jest różnie nazywany, np. IDENTITY w IBM DB2, Microsoft SQL Server i Oracle 12c, AutoNumber w Microsoft Access, AUTO_INCREMENT w MySQL i serial w PostgreSQL. We wcześniejszych wersjach Oracle’a do osiągnięcia podobnego wyniku konieczne było wykorzystanie obiektu sekwencji (Sequence), ale był to osobny obiekt, a nie atrybut kolumny. DB2, SQL Server i PostgreSQL również wspierają obiekt Sequence. Integralność referencyjna RI (ang. referential integrity) to bardzo ważna koncepcja w bazach relacyjnych. Wspieranie integralności referencyjnej oznacza, że w przypadku klucza obcego nieakceptującego wartości null dla każdego rekordu z tabeli podrzędnej musi istnieć rekord w tabeli nadrzędnej. W dobrze zaprojektowanej tabeli zamówień informacje o kliencie zapisane by były w postaci klucza obcego do tabeli klientów (Customers) wskazującego na klucz główny tej tabeli. Jeżeli faktycznie istnieje wielu klientów o nazwisku John Smith, każdy z tych klientów będzie posiadał swój własny unikalny klucz. Aby zapewnić integralność referencyjną pomiędzy tabelami, wszelkie zmiany wartości klucza głównego muszą być kaskadowo rozpropagowane we wszystkich tabelach, które się do niego odwołują. W trakcie takich kaskadowych aktualizacji na tabelach odwołujących się zakładane są blokady, które mogą prowadzić do problemów w bazach wymagających równoległego dostępu wielu użytkowników. Rozważmy przykład z rysunku 1.2 przedstawiającego tabelę klientów (Customers) z bazy Northwind będącej częścią pakietu Microsoft Access 2003. Rysunek 1.2. Przykładowe dane z tabeli Customers W tym przykładzie zakładamy, że regułą biznesową jest, aby tekstowy klucz główny, CustomerId, był powiązany z nazwą firmy. Gdyby któraś z firm zmieniła nazwę, identyfikator klienta również zostałby zmieniony. Wymagałoby to wprowadzenia kaskadowych zmian w powiązanych tabelach. Jeżeli wykorzystujesz nieznaczący klucz, unikasz konieczności aktualizowania jego wartości, a na potrzeby wyświetlania w osobnej kolumnie możesz przechowywać identyfikator generowany na podstawie nazwy. Częstym argumentem za tekstowymi kluczami głównymi jest to, że zapobiegają one wstawianiu zduplikowanych wartości. Na przykład gdyby kolumna CompanyName (nazwa firmy) została ustawiona jako klucz główny, nie moglibyśmy wprowadzić dwóch takich samych nazw. Utworzenie unikalnego indeksu na kolumnie Poleć książkęKup książkę 30 R o z dz i ał 1. Projektowanie modelu danych CompanyName tabeli Customers jest proste i również zapewnia unikalność danych w kolumnie. Integralność jest zapewniona i nadal można korzystać z wygenerowanej wartości numerycznej jako klucza głównego. Wykorzystując wskazówki z zagadnienia 2, „Eliminacja nadmiarowego przechowywania danych” i zagadnienia 4, „Przechowywanie jednej właściwości w kolumnie”, unikniesz problemu, który przedstawiliśmy na rysunku 1.1. Z drugiej strony prawdą jest, że wykorzystanie tekstowych kluczy głównych często pozwala uprościć zapytania SQL dzięki uniknięciu złączeń w celu pobrania wartości powiązanych z kluczem numerycznym (CompanyName w przykładzie z rysunku 1.2). W środowisku profesjonalistów baz danych prowadzono wiele dyskusji związanych z tym, który klucz jest bardziej przydatny: czy numeryczny, czy tekstowy. Nie chcemy zajmować strony w tym sporze: najważniejsze jest zastosowanie we wszystkich tabelach unikalnego identyfikatora, który może być wykorzystany jako klucz główny. Nie radzimy również korzystać ze złożonych kluczy głównych, ponieważ z dwóch powodów są mniej wydajne: 1. Gdy definiujesz klucz główny, większość baz danych wspiera definicję za pomocą unikalnego indeksu. Unikalny indeks na więcej niż jednej kolumnie to więcej pracy dla systemu zarządzania bazą danych. 2. Wykonywanie złączenia na kluczu głównym jest dość powszechne, ale robienie tego na większej liczbie kolumn jest bardziej skomplikowane i wolniejsze. W niektórych przypadkach wykorzystanie kilku kolumn jako klucza głównego może mieć jednak swoje uzasadnienie. Rozważmy tabelę łączącą produkty i producentów, która składa się z kolumn VendorID (identyfikator producenta) i ProductID (identyfikator produktu) jako klucza głównego. Tabela może zawierać inne kolumny, np. identyfikator, informację o tym, czy producent jest głównym czy alternatywnym dostawcą produktu, albo cenę, jakiej producent żąda za produkt. Mógłbyś utworzyć dodatkowo generowaną kolumnę z liczbami, która pełniłaby rolę sztucznego klucza głównego, ale jako klucz główny możesz również wykorzystać kombinację kolumn VendorID i ProductID. Złączenia z tą tabelą będą zawsze wykonywane za pomocą pojedynczych kolumn, dlatego zdefiniowanie klucza złożonego może być wydajniejsze niż wykorzystanie dodatkowej kolumny z kluczem. Powinieneś zdefiniować kolumny jako klucz złożony, a z dodatkowej kolumny zrezygnować. W zagadnieniu 8, „Gdy 3NF to za mało, normalizuj dalej”, znajdziesz analizę przykładu, w którym pożądane mogą być złożone klucze główne. Do zapamiętania  Wszystkie tabele powinny posiadać kolumnę (lub zestaw kolumn) zaprojektowaną jako klucz główny. Poleć książkęKup książkę Z a g ad n i e n i e 2 : E li m i n a c j a n a d m i a r o we g o p r ze c ho w y w a n i a d a n yc h 31  Jeżeli martwisz się zduplikowanymi wartościami w kolumnie nie będącej częścią klucza, możesz zdefiniować indeks unikalny na kolumnie, co zapewni integralność.  Wykorzystuj jak najprostsze klucze zawierające wartości, których nie trzeba aktualizować. Zagadnienie 2: Eliminacja nadmiarowego przechowywania danych Nadmiarowo przechowywane dane powodują wiele problemów, np. niespójność danych, anomalie podczas wykonywania operacji wstawiania, aktualizacji i usuwania, a także marnotrawienie miejsca na dysku. Normalizacja to proces dzielenia informacji tematycznie w celu uniknięcia przechowywania duplikatów danych. Zwróć uwagę, że pisząc „nadmiarowe”, nie mamy na myśli oczywistej duplikacji danych klucza głównego jako klucza obcego w innej tabeli. Taka nadmiarowość jest konieczna do zachowania połączenia między tabelami. Bardziej interesuje nas sytuacja, w której użytkownik wprowadza te same dane więcej niż jeden raz. Ze względu na ograniczone miejsce nie możemy się zbytnio zagłębiać w kwestię normalizacji baz danych, jednak bardzo ważne jest, aby ludzie pracujący z bazami danych dokładnie rozumieli ten temat. Jest tu wiele doskonałych źródeł informacji. Jednym z celów normalizacji jest zminimalizowanie konieczności powtarzania tych samych danych zarówno w obrębie tej samej tabeli, jak i we wszystkich tabelach w bazie danych. Kilka przykładów nadmiarowości danych obrazuje baza sprzedaży na rysunku 1.3. Rysunek 1.3. Przykład nadmiarowego przechowywania danych w jednej tabeli Poleć książkęKup książkę 32 R o z dz i ał 1. Projektowanie modelu danych Przykładem niespójnych danych jest adres klienta Toma Franka. W drugim rekordzie wartość numeryczna jego adresu to 7453, natomiast w szóstym 7435. Podobna niespójność może występować we wszystkich kolumnach. Anomalia wstawiania może występować wtedy, gdy na przykład nie można wprowadzić informacji dla modelu samochodu, dopóki nie wystąpi jego sprzedaż, którą wprowadza się wraz z danymi klienta. Projekt wymaga również powtarzania większości danych w momencie zakupu przez klienta nowego samochodu. Te dodatkowo wprowadzane dane powodują marnotrawienie miejsca na dysku, pamięci, zasobów sieciowych, a nawet czasu użytkownika, który wprowadza te dane. Co więcej, ponowne wpisywanie danych znacząco zwiększa ryzyko błędów, takich jak zamiana miejscami numerów w adresie klienta, co obrazuje rysunek 1.3. Może występować również anomalia aktualizacji. Jeśli na przykład sprzedawca zmienia swój stan cywilny, a w konsekwencji nazwisko, konieczna jest aktualizacja wszystkich rekordów, w których występuje nazwisko tej osoby. Może to być problematyczne, gdy masz do czynienia z dużą liczbą rekordów w bazie, z której wspólnie korzysta wielu użytkowników. Dodatkowo taka aktualizacja będzie wykonana poprawnie tylko wtedy, gdy wszystkie wystąpienia nazwiska są napisane dokładnie w ten sam sposób (dane są spójne) i gdy nie ma dwóch osób o tym samym nazwisku. Występuje również anomalia usuwania, ponieważ możesz wtedy stracić dane, których nie chciałeś usuwać z bazy. Dane sprzedażowe przedstawione na rysunku 1.3 mogą być logicznie podzielone na cztery tabele: 1. Customers — tabela z klientami (nazwa, adres itd.) 2. Employees — tabela z pracownikami (nazwa sprzedawcy, data zatrudnienia itd.) 3. AutomobileModels — tabela z modelami samochodów (rok modelowy, model itd.) 4. SalesTransactions — tabela z transakcjami sprzedaży Taki projekt pozwala wprowadzać dane klientów, pracowników i modeli samochodów tylko raz do tabel dla nich przeznaczonych. Wszystkie tabele zawierają unikalny identyfikator, który może zostać ustawiony jako klucz główny. Tabela SalesTransactions wykorzystuje klucze obce do przechowywania szczegółów każdej transakcji. Zobacz rysunek 1.4. Uważny czytelnik mógł zauważyć, że w tym procesie został wyeliminowany jeden zduplikowany rekord klienta. Wymagało to określenia, który rekord adresu jest poprawny dla klienta Tom Frank. Możemy utworzyć relacje (określane również jako więzy integralności klucza głównego), łącząc klucz główny z trzech tabel nadrzędnych (Customers, AutomobileModels i Employees) i klucze obce z tabeli SalesTransactions (patrz rysunek 1.5). Stworzyliśmy przykład na podstawie rysunku, korzystając z edytora relacji zawartego w Microsoft Access. Każda baza relacyjna posiada inny sposób na reprezentowanie relacji pomiędzy tabelami. Poleć książkęKup książkę Z a g ad n i e n i e 2 : E li m i n a c j a n a d m i a r o we g o p r ze c ho w y w a n i a d a n yc h 33 Rysunek 1.4. Przykład dzielenia danych na tabele zgodnie z tematyką Rysunek 1.5. Relacje pomiędzy czterema tabelami, utworzone przez połączenie kluczy głównych i kluczy obcych Poleć książkęKup książkę 34 R o z dz i ał 1. Projektowanie modelu danych Możesz łatwo odtworzyć przedstawione na rysunku 1.3 oryginalne dane, konstruując tabelę wirtualną (zapytanie) zgodnie z listingiem 1.1 i unikając jednocześnie problemów związanych z przechowywaniem nadmiarowych danych. (Tworzenie tabeli wirtualnej to doskonały przykład wykorzystania wspólnych wyrażeń tabelarycznych, które omówione zostały w zagadnieniu 42, „Wykorzystanie wspólnych wyrażeń tabelarycznych zamiast podzapytań”). Listing 1.1. Zapytanie SQL zwracające oryginalne dane SELECT st.SalesID, c.CustFirstName, c.CustLastName, c.Address, c.City, c.Phone, st.PurchaseDate, m.ModelYear, m.Model, e.SalesPerson FROM SalesTransactions st INNER JOIN Customers c ON c.CustomerID = st.CustomerID INNER JOIN Employees e ON e.EmployeeID = st.SalesPersonID INNER JOIN AutomobileModels m ON m.ModelID = st.ModelID; Do zapamiętania  Celem normalizacji baz danych jest eliminacja nadmiarowych danych i minimalizacja wykorzystania zasobów wykorzystywanych podczas przetwarzania danych.  Poprzez usunięcie nadmiarowych danych eliminujesz anomalie wstawiania, aktualizacji i usuwania.  Poprzez eliminację nadmiarowych danych minimalizujesz występowanie niespójnych danych. Odniesienia Jeżeli chcesz dowiedzieć się więcej na temat poprawnych sposobów projektowania baz relacyjnych, skorzystaj z zamieszczonych poniżej dobrych źródeł wiedzy; pierwsze z nich jest przystępne dla początkujących i może być dobre na początek:  Michael J. Hernandez, Projektowanie baz danych dla każdego. Przewodnik krok po kroku, Helion, 2014.  Candace C. Fleming i Barbara von Halle, Handbook of Relational Database Design, Addison-Wesley, 1989. Zagadnienie 3: Pozbywanie się powtarzających się grup Często się zdarza, że arkusze kalkulacyjne zawierają powtarzające się grupy podobnych danych. Pracownicy pracujący z tymi danymi importują je do nowej bazy danych, nie biorąc w ogóle pod uwagę normalizacji. Przykład powtarzających się grup danych przedstawiony został na rysunku 1.6, gdzie numer rysunku (DrawingNumber) został powiązany z pięcioma poprzednikami (Predecessor). Tabela posiada relację „jeden do wielu” pomiędzy numerami rysunków i wartościami poprzedników. Poleć książkęKup książkę Z a g ad n i e n i e 3 : P oz b y w a ni e s ię p o wt a rz a jących się g ru p 35 Rysunek 1.6. Powtarzające się grupy danych w jednej tabeli Przykład na rysunku 1.6 przedstawia pojedynczy atrybut, Predecessor, jako powtarzającą się grupę. Mamy też powtarzającą się wartość dla tego atrybutu, czyli dla ID=3, co nie jest zamierzone. Innym przykładem mogłyby być kolumny nazwane Styczeń, Luty, Marzec itd. Jednak powtarzające się grupy nie są ograniczone do pojedynczych atrybutów. Jeżeli na przykład zobaczysz kolumny takie jak Ilosc1, Opis1, Cena1, Ilosc2, Opis2, Cena2, …, IloscN, OpisN, CenaN, możesz uznać je za wzorzec powtarzającej się grupy. Powtarzające się grupy powodują problemy przy pisaniu zapytań i tworzeniu raportów grupujących po atrybutach. Jeśli do przykładu z rysunku 1.6 chciałbyś dodać wartości poprzedników (Predecessor) lub obniżyć liczbę dostępnych poprzedników, aktualny projekt wymagałby dodawania lub usuwania kolumn. Musiałbyś również zmodyfikować wszystkie zapytania (widoki), formularze i raporty zależne od danych w tej kolumnie. Należy zawsze pamiętać o ważnej zasadzie: Kolumny są kosztowne. Rekordy są tanie. Powinieneś uważać zawsze, gdy projekt bazy wymaga dodawania lub usuwania kolumn, aby sprostać późniejszym wymaganiom danych. Dużo lepszym rozwiązaniem jest projekt pozwalający dodawać i usuwać wiersze. W tym przykładzie utworzymy tabelę Predecessors (poprzednicy) korzystającą z identyfikatora jako klucza obcego. Dla przejrzystości zmienimy również nazwę istniejącej kolumny ID na DrawingID, zgodnie z rysunkiem 1.7. Rysunek 1.7. Znormalizowany projekt uwzględniający relację „jeden do wielu” Podczas pracy z powtarzającymi się grupami użyteczne są zapytania UNION. Możemy wykorzystać takie zapytanie do „znormalizowania” naszych danych w widoku tylko do odczytu, jeżeli nie mamy możliwości utworzenia poprawnie Poleć książkęKup książkę 36 R o z dz i ał 1. Projektowanie modelu danych znormalizowanego schematu. Możemy również wykorzystać podobne zapytanie jako źródło zapytania dodające rekordy do nowej tabeli Predecessors, zgodnie z listingiem 1.2. Listing 1.2. Zapytanie UNION normalizujące dane SELECT ID AS DrawingID, Predecessor_1 AS Predecessor FROM Assignments WHERE Predecessor_1 IS NOT NULL UNION SELECT ID AS DrawingID, Predecessor_2 AS Predecessor FROM Assignments WHERE Predecessor_2 IS NOT NULL UNION SELECT ID AS DrawingID, Predecessor_3 AS Predecessor FROM Assignments WHERE Predecessor_3 IS NOT NULL UNION SELECT ID AS DrawingID, Predecessor_4 AS Predecessor FROM Assignments WHERE Predecessor_4 IS NOT NULL UNION SELECT ID AS DrawingID, Predecessor_5 AS Predecessor FROM Assignments WHERE Predecessor_5 IS NOT NULL ORDER BY DrawingID, Predecessor; UWAGA Jeśli musielibyśmy pobrać wszystkie dane razem, włącznie z duplikatami w ramach wiersza, moglibyśmy dodać słowo kluczowe ALL po każdym wystąpieniu słowa UNION, czyli operować wyrażeniem UNION ALL. W tym przypadku jednak chcemy wyeliminować z tabeli Predecessor zduplikowane wartości, ponieważ są one błędnie wprowadzone, tak jak w przypadku rekordu z identyfikatorem 3. Zapytanie UNION wymaga, aby kolumny były tego samego typu i w tej samej kolejności dla każdego polecenia SELECT. Oznacza to, że umieszczanie AS DrawingID czy AS Predecessor po pierwszej instancji nie jest konieczne, ponieważ zapytanie UNION pobierze nazwy kolumn z pierwszego polecenia SELECT. Każde polecenie SELECT może mieć różne predykaty w klauzuli WHERE. Zależnie od danych możemy również wykluczyć łańcuchy znaków o zerowej długości (ZLS — ang. zero length strings) i/lub białe znaki, takie jak pojedyncza spacja. Zapytanie UNION może wykorzystywać pojedynczą klauzulę ORDER BY na końcu. Możemy definiować referencje porządkowe, czyli ORDER BY 1, 2. Byłoby to równoznaczne z poleceniem ORDER BY DrawingID, Predecessor z listingu 1.2. Do zapamiętania  Celem normalizacji baz danych jest eliminacja powtarzających się grup danych i minimalizacja zmian schematu.  Eliminując powtarzające się grupy danych, możesz wykorzystać indeksowanie do uniknięcia przypadkowej duplikacji danych oraz znacząco ułatwiasz ewentualne tworzenie zapytań. Poleć książkęKup książkę Z a g ad n i e n i e 4 : P r ze c h o w y w a n i e j ed n e j właś ciwo ś ci w kolumnie 37  Usuwanie powtarzających się grup danych sprawia, że projekt bazy jest bardziej elastyczny, ponieważ dodanie nowej grupy wymaga po prostu dodania kolejnego rekordu danych, a nie zmiany schematu i dodania nowych kolumn. Zagadnienie 4: Przechowywanie jednej właściwości w kolumnie W terminologii relacyjnej relacja (tabela) powinna opisywać jeden i tylko jeden przedmiot akcji. Atrybuty (kolumny) zawierają dane odpowiadające tylko jednej właściwości opisującej podmiot definiowany przez relację (takie dane nazywane są danymi „atomowymi”). Atrybut może również być kluczem obcym zawierającym atrybut z innej relacji, a z kolei ten klucz obcy może zawierać połączenie z innym tupletem (wierszem) z innej relacji. Przechowywanie więcej niż jednej wartości właściwości w jednej kolumnie nie jest dobrym pomysłem, ponieważ ciężko wtedy wyizolować wartość właściwości podczas wykonywania wyszukiwania lub agregowania wartości. Powinieneś starać się umieszczać ważne pojedyncze właściwości w ich własnych kolumnach. Przykład tabeli zawierającej wiele właściwości w jednej kolumnie znajdziesz w tabeli 1.1. (Przy okazji: adresy z tej tabeli są prawdziwe, ale nie są to prawdziwe adresy wspomnianych autorów). Tabela 1.1. Tabela zawierająca wiele atrybutów w pojedynczych kolumnach AuthID AuthName AuthAddress 1 2 3 4 John L. Viescas 144 Boulevard Saint-Germain, 75006, Paris, France Douglas J. Steele 555 Sherbourne St., Toronto, ON M4X 1W6, Canada Ben Clothier 2015 Monterey St., San Antonio, TX 78207, USA Tom Wickerath 2317 185th Place NE, Redmond, WA 98052, USA W takiej tabeli napotykamy kilka problemów:  Trudne, a nawet wprost niemożliwe, jest wyszukiwanie po nazwisku. Zakładając, że tabela zawiera więcej niż tylko cztery przykładowe rekordy, a Ty chcesz wyszukać kogoś o nazwisku Smith, wyszukiwanie LIKE z maskowaniem wyszuka również nazwiska Smithson lub Blacksmith.  Możesz szukać po imieniu, ale wtedy musisz użyć mniej wydajnej klauzuli LIKE lub wyciąć imię z ciągu znaków. Klauzula LIKE z maską na końcu może być wykonywana efektywnie, ale ze względu na zwroty grzecznościowe (takie jak Mr.) musisz użyć maski również na początku, aby mieć pewność, że odnajdziesz szukane imię, a to z kolei spowoduje skanowanie danych.  Nie możesz łatwo wyszukiwać po nazwie ulicy, mieście, stanie/województwie czy kodzie pocztowym. Poleć książkęKup książkę 38 R o z dz i ał 1. Projektowanie modelu danych  Mimo prób łączenia danych w grupy (być może złączonych z inną tabelą zawierającą przypisane rozdziały i liczby stron) ciężko jest wyekstrahować stan/województwo, kod pocztowy czy kraj na potrzeby grupowania. Najczęściej tego typu dane spotkasz w przypadku importów z innych źródeł danych, takich jak arkusze kalkulacyjne. Tak źle zaprojektowaną tabelę nierzadko spotkasz w środowiskach produkcyjnych. Bardziej poprawnym rozwiązaniem będzie utworzenie tabeli podobnej do tej z listingu 1.3. Listing 1.3. Listing tworzący tabelę autorów (Authors) z oddzielnymi atrybutami CREATE TABLE Authors ( AuthorID int IDENTITY (1,1), AuthFirst varchar(20), AuthMid varchar(15), AuthLast varchar(30), AuthStNum varchar(6), AuthStreet varchar(40), AuthCity varchar(30), AuthStProv varchar(2), AuthPostal varchar(10), AuthCountry varchar(35) ); INSERT INTO Authors (AuthFirst, AuthMid, AuthLast, AuthStNum, AuthStreet, AuthCity, AuthStProv, AuthPostal, AuthCountry) VALUES ( John , L. , Viescas , 144 , Boulevard Saint-Germain ,  Paris , , 75006 , France ); INSERT INTO Authors (AuthFirst, AuthMid, AuthLast, AuthStNum, AuthStreet, AuthCity, AuthStProv, AuthPostal, AuthCountry) VALUES ( Douglas , J. , Steele , 555 , Sherbourne St. , Toronto , ON ,  M4X 1W6 , Canada ); -- … kolejne rekordy. Zwróć uwagę, że również dla numeru domu użyliśmy klucza typu znakowego, ponieważ niekiedy „numer” zawiera również litery i inne znaki (np. 8a, a we Francji numery zawierają dodatkowo końcówkę bis). To samo dotyczy kodów pocztowych, które w Stanach Zjednoczonych są numeryczne, ale w Kanadzie czy Wielkiej Brytanii zawierają litery i spacje. Używając zasugerowanej tabeli, dane można podzielić tak, aby każdy atrybut przechowywany był w odrębnej kolumnie zgodnie z tabelą 1.2. Teraz łatwo możemy wykonywać wyszukiwanie i grupowanie na pojedynczych lub wielu właściwościach, ponieważ każda właściwość przechowywana jest w odrębnej kolumnie. Poleć książkęKup książkę Z a g ad n i e n i e 4 : P r ze c h o w y w a n i e j ed n e j właś ciwo ś ci w kolumnie 39 Tabela 1.2. Poprawnie zaprojektowana tabela Authors przechowująca właściwości w odrębnych kolumnach Auth ID Auth Name Auth Mid Auth Last AuthSt Num Auth Street Auth City AuthSt Prov Auth Postal Auth Cou- ntry 1 2 3 4 John L. Viescas 144 Douglas J. Steele 555 Ben Tom Clot- -hier Wicke- -rath 2015 2317 Boule- -vard Saint- Germain Sherbo-- urne St. Monte- -rey St. 185th Place NE Paris 75006 France Toronto ON M4X 1W6 Canada San Antonio TX 78207 USA Redmont WA 98052 USA Jeżeli musisz powtórnie połączyć właściwości, aby na przykład utworzyć listę mailingową, wystarczy wykorzystać w zapytaniu SQL konkatenację. Listing 1.4 pokazuje, jak to zrobić. Listing 1.4. Powtórne połączenie oryginalnych danych przy wykorzystaniu konkatenacji SELECT AuthorID AS AuthID, CONCAT(AuthFirst, CASE WHEN AuthMid IS NULL THEN ELSE CONCAT( , AuthMid, ) END, AuthLast) AS AuthName, CONCAT(AuthStNum, , AuthStreet, , AuthCity, , , AuthStProv, , AuthPostal, , , AuthCountry) AS AuthAddress FROM Authors; UWAGA IBM DB2, Microsoft SQL Server, MySQL, Oracle i PostgreSQL wspierają funkcję CONCAT(), ale w DB2 i Oracle funkcja ta przyjmuje tylko dwa argumenty, musisz więc zagnieździć funkcję CONCAT(), aby złączyć wiele ciągów znaków. Standard ISO do konkatenacji definiuje jedynie operator ||. DB2, Oracle i PostgreSQL akceptują ten operator, a MySQL akceptuje go, jeżeli ustawienie sql_mode ma wartość PIPES_AS_CONCAT. W SQL Server jako operator konkatenacji wykorzystywany jest operator +. Microsoft Access nie wspiera funkcji CONCAT(), ale pozwala łączyć ciągi znaków przy wykorzystaniu operatorów lub +. Wcześniej wspominaliśmy, że listing 1.3 przedstawia jeden z kilku możliwych „w miarę poprawnych” projektów. Możesz się więc teraz zastanawiać, dlaczego rekomendujemy oddzielenie numeru domu od nazwy ulicy. Prawdę powiedziawszy, w większości zastosowań połączenie nazwy ulicy z numerem domu będzie Poleć książkęKup książkę 40 R o z dz i ał 1. Projektowanie modelu danych wystarczające. Musisz jednak zawsze dokładnie rozpatrzyć potrzeby aplikacji. W przypadku bazy urzędu geodezyjnego oddzielenie numeru domu od nazwy ulicy (być może nawet dodanie typu adresu, np. ulica, aleja, osiedle) może być niezbędne. W innych aplikacjach ważne może być rozdzielenie kodu kraju, numeru kierunkowego i lokalnego numeru telefonu. Musisz zdecydować, które części są wystarczająco ważne, aby ustalić, jak bardzo będą rozdzielone atrybuty. Oczywiste jest, że rozdzielenie właściwości na osobne kolumny ułatwia wyszukiwanie i grupowanie poszczególnych elementów danych. Powtórne złożenie danych na potrzeby raportów lub list wyświetlanych jest również bardzo proste. Do zapamiętania  Poprawny projekt bazy przypisuje pojedyncze właściwości do własnej kolumny. Gdy kolumna zawiera wiele właściwości, wyszukiwanie i grupowanie staje się trudne lub wręcz niemożliwe.  W przypadku niektórych aplikacji konieczność filtrowania danych na podstawie kolumn takich jak adres czy numer telefonu może dyktować szczegółowość danych.  W przypadku konieczności powtórnego złączenia właściwości na potrzeby raportu lub list wyświetlanych wykorzystaj konkatenację. Zagadnienie 5: Dlaczego przechowywanie danych Zagadnienie 5: Dlaczego przechowywanie danych wyliczeniowych zazwyczaj nie jest dobrym pomysłem Zagadnienie 5: Dlaczego przechowywanie danych Czasami możesz poczuć pokusę przechowania danych wyliczeniowych, szczególnie gdy wynik uzależniony jest od danych w tabeli powiązanej. Rozważmy przykład z listingu 1.5. Listing 1.5. Przykładowa definicja tabeli w SQL CREATE TABLE Orders ( OrderNumber int NOT NULL, OrderDate date NULL, ShipDate date NULL, CustomerID int NULL, EmployeeID int NULL, OrderTotal decimal(15,2) NULL ); Na pierwszy rzut oka zawarcie w tabeli Orders (zamówienia) kolumny OrderTotal (suma zamówienia) zawierającej najprawdopodobniej sumę iloczynów liczby produktów i ceny jednostkowej wszystkich powiązanych rekordów z tabeli przechowującej szczegóły zamówienia może wydawać się dobrym pomysłem, Poleć książkęKup książkę Z a g ad n i e n i e 5 : D l a cz eg o prz e cho wy w a n i e d a ny ch 41 ponieważ nie będzie trzeba pobierać powiązanych rekordów i wykonywać obliczeń za każdym razem, gdy będziemy chcieli pobrać zamówienia wraz z ich wartością. Takie pole może być dobre w hurtowni danych, ale może negatywnie odbić się na wydajności bazy aktywnie użytkowanego systemu (patrz również zagadnienie 9, „Wykorzystanie denormalizacji w magazynach danych”). Trudne może okazać się utrzymanie integralności danych, ponieważ wciąż musiałbyś pamiętać o tym, aby dane w kolumnie były zmieniane za każdym razem, gdy któryś z powiązanych rekordów zostanie zaktualizowany, wstawiony lub usunięty. Dobrą wiadomością jest natomiast to, że wiele współczesnych systemów zarządzania bazami zapewnia sposoby na takie ustawienie kolumny, aby kod działający na serwerze wykonywał kalkulacje za Ciebie. Najbardziej prymitywnym sposobem na dopilnowanie, aby kolumna miała zawsze aktualne wartości, jest utworzenie wyzwalacza dla tabeli zawierającej dane źródłowe dla obliczeń. Wyzwalacz to kod uruchamiany wtedy, gdy do tabeli wstawiane są dane lub gdy istniejące dane są aktualizowane bądź usuwane. W przykładzie z listingu 1.5 wyzwalacz zostałby utworzony dla tabeli Order_Details (szczegóły zamówienia) i przeliczałby wartość kolumny OrderTotal. Wyzwalacze mogą jednak być kosztowne, a ich poprawne utworzenie może nie być proste (patrz również zagadnienie 13, „Nie przesadź z wyzwalaczami”). Niektóre systemy bazodanowe dają potencjalnie lepsze rozwiązanie problemu, czyli możliwość zdefiniowania kolumny wyliczeniowej podczas definiowania tabeli. Uważamy, że to rozwiązanie jest lepsze, ponieważ pozwala uniknąć konieczności tworzenia skomplikowanego kodu często wymaganego w wyzwalaczach. Niektóre systemy RSZBD (szczególnie w nowszych wersjach) wspierają już definiowanie kolumn wyliczeniowych. Na przykład Microsoft SQL Server udostępnia słowo kluczowe AS, po którym należy podać wyrażenie definiujące wymagane obliczenia. Gdy w obliczeniach wykorzystywane są jedynie kolumny z tej samej tabeli, możesz po prostu wykorzystać nazwy kolumn w definicji wyrażenia. Jeżeli do obliczenia konieczne jest wykorzystanie wartości z tabeli powiązanej, niektóre systemy pozwalają zdefiniować funkcję wykonującą obliczenia, a następnie wykorzystać funkcję w klauzuli AS podczas tworzenia lub modyfikacji tabeli docelowej. Listing 1.6 przedstawia przykładową funkcję i definicję tabeli w systemie Microsoft SQL Server. Zwróć uwagę, że ponieważ funkcja korzysta z danych z innej tabeli, jest ona niedeterministyczna, nie można więc utworzyć indeksu dla kolumny wyliczeniowej. Listing 1.6. Przykładowa funkcja i definicja tabeli dla Microsoft SQL Server CREATE FUNCTION dbo.getOrderTotal(@orderId int) RETURNS money AS BEGIN DECLARE @r money SELECT @r = SUM(Quantity * Price) FROM Order_Details WHERE OrderNumber = @orderId RETURN @r; END; Poleć książkęKup książkę 42 R o z dz i ał 1. Projektowanie modelu danych GO CREATE TABLE Orders ( OrderNumber int NOT NULL, OrderDate date NULL, ShipDate date NULL, CustomerID int NULL, EmployeeID int NULL, OrderTotal money AS dbo.getOrderTotal(OrderNumber) ); Deterministyczny kontra niedeterministyczny Funkcja deterministyczna to taka, która zawsze zwróci ten sam wynik dla jednego ze- stawu parametrów wsadowych. Funkcja niedeterministyczna może zwracać różne re- zultaty dla każdego wywołania z tym samym zestawem parametrów. Na przykład funk- cja wbudowana bazy SQL Server DATEADD() jest deterministyczna, ponieważ zawsze zwróci taki sam wynik dla takiego samego zestawu trzech parametrów, natomiast funkcja GETDATE() jest niedeterministyczna, gdyż zawsze wywoływana jest z tym sa- mym argumentem, jednak wartość zwracana może się zmienić przy każdym jej uru- chomieniu. (Zakładamy, że trzy parametry funkcji DATEADD() również są deterministycz- ne. To znaczy, że nie możesz użyć funkcji GETDATE() jako jednego z argumentów). W dodatku „Typy, operatory i funkcje dla dat i czasu” znajdziesz dokładniejsze in- formacje dotyczące funkcji dat i czasu w Twoim systemie bazodanowym. Zrobienie tego w ten sposób jest jednak bardzo złym pomysłem. Ponieważ funkcja jest niedeterministyczna, kolumna nie może zostać zapisana na stałe (PERSISTED) jak prawdziwa kolumna. Na kolumnie nie można zbudować indeksu, a za każdym razem gdy odwołasz się do tej kolumny, serwer będzie musiał wykonać dużo pracy, ponieważ funkcja będzie wywoływana dla każdego rekordu. Znacznie efektywniejsze byłoby połączenie tabel za pomocą podzapytania wykonującego obliczenia z grupowaniem po kolumnie OrderID za każdym razem, gdy potrzebne będą dane. W systemie IBM DB2 istnieje podobna funkcjonalność, ale słowo kluczowe to GENERATED. W DB2 niemożliwe jest jednak utworzenie kolumny wyliczeniowej z wykorzystaniem funkcji, która wywołuje zapytanie, ponieważ czyni to funkcję niedeterministyczną. Można natomiast utworzyć kolumnę z wykorzystaniem funkcji lub wyrażenia deterministycznego. Listing 1.7 pokazuje, jak zdefiniować wyrażenie obliczające liczbę produktów pomnożonych przez cenę i zwracające całkowitą cenę, które może być wykorzystane do utworzenia kolumny w tabeli Order_Details. Listing 1.7. Przykładowa definicja kolumny w DB2, wykorzystująca wyrażenie -- Wyłączenie więzów integralności, aby możliwa była modyfikacja tabeli SET INTEGRITY FOR Order_Details OFF; -- Utworzenie kolumny wyliczeniowej z wykorzystaniem wyrażenia Poleć książkęKup książkę Z a g ad n i e n i e 5 : D l a cz eg o prz e cho wy w a n i e d a ny ch 43 ALTER TABLE Order_Details ADD COLUMN ExtendedPrice decimal(15,2) GENERATED ALWAYS AS (QuantityOrdered * QuotedPrice); -- Powtórne włączenie więzów integralności SET INTEGRITY FOR Order_Details IMMEDIATE CHECKED FORCE GENERATED; -- Założenie indeksu na kolumnie wyliczeniowej CREATE INDEX Order_Details_ExtendedPrice ON Order_Details (ExtendedPrice); Ponieważ wyrażenie jest teraz zawsze deterministyczne, możesz utworzyć kolumnę oraz indeks. Listing 1.7 pokazuje przykład dla DB2, ale w plikach z listingami zawarliśmy również przykłady dla innych baz danych. Jeżeli będziesz chciał utworzyć w Oracle’u kolumnę wyliczeniową (nazywaną kolumną wirtualną), wykorzystaj GENERATED [ALWAYS] AS. SQL tworzący kolumnę ExtendedPrice (suma pozycji zamówienia) dla tabeli Order_Details w bazie Oracle mógłby wyglądać tak, jak na listingu 1.8. Listing 1.8. Przykładowa definicja tabeli z osadzonym wyrażeniem dla bazy Oracle CREATE TABLE Order_Details ( OrderNumber int NOT NULL, OrderNumber int NOT NULL, ProductNumber int NOT NULL, QuotedPrice decimal(15,2) DEFAULT 0 NULL, QuantityOrdered smallint DEFAULT 0 NULL, ExtendedPrice decimal(15,2) GENERATED ALWAYS AS (QuotedPrice * QuantityOrdered) ); W tym miejscu pewnie się zastanawiasz, dlaczego to zagadnienie zatytułowane zostało „Dlaczego przechowywanie danych wyliczeniowych zazwyczaj nie jest dobrym pomysłem”, chociaż właśnie pokazaliśmy, jak to zrobić. Czas teraz na złe wieści: jeżeli ta tabela ma być intensywnie użytkowaną tabelą dla systemu czasu rzeczywistego, dodanie takiej kolumny może spowodować dodatkowe obciążenie serwera, co z kolei negatywnie przełoży się na czas odpowiedzi serwera. Jeżeli korzystasz z IBM DB2, Microsoft SQL Server lub Oracle, być może będziesz mógł również utworzyć indeks dla kolumny wyliczeniowej, co pomoże podczas wykonywania zapytań wykorzystujących tę kolumnę. Pamiętaj, że w SQL Server dla przykładu z listingu 1.6 nie będziesz w stanie założyć indeksu (nie mógłbyś też w żadnym innym systemie bazodanowym), ponieważ wynik działania funkcji jest niedeterministyczny — uzależniony jest on od wyniku pobrania danych z innej tabeli (patrz również zagadnienie 17, „Kiedy wykorzystywać wartości wyliczane w indeksach”). W SQL Server musisz wykonać jeszcze jeden krok polegający na podaniu dla wyrażenia słowa kluczowego PERSISTED, natomiast w DB2 takie kolumny zapisywane są domyślnie po utworzeniu indeksu dla kolumny. Poleć książkęKup książkę 44 R o z dz i ał 1. Projektowanie modelu danych W przypadku listingu 1.7 dodatkowe operacje mają miejsce za każdym razem, gdy wartość wywoływanej funkcji mogłaby ulec zmianie — gdy aktualizujesz, wstawiasz lub usuwasz rekord z tabeli Order_Details. Ktoś siedzący przy terminalu i wpisujący wiele elementów zamówień może doświadczać nieakceptowalnych czasów reakcji systemu, ponieważ za każdym razem należy wykonać funkcję, a jej wynik musi zostać zapisany w indeksie. Na listingu 1.6 lub listingu 1.8 nadmiarowe operacje wykonywane byłyby podczas pobierania danych z tabeli i tutaj nieakceptowalny czas odpowiedzi może występować podczas wykonywania operacji SELECT, zawierającej kolumnę wyliczeniową i pobierającej wiele rekordów. Do zapamiętania  Wiele systemów pozwala Ci definiować kolumny wyliczeniowe podczas definiowania tabel, ale musisz być świadomy implikacji wydajnościowych (szczególnie w przypadku wyrażeń i funkcji niedeterministycznych).  Możesz również definiować kolumny wyliczeniowe jako standardowe kolumny, a następnie dbać o wartości za pomocą wyzwalaczy, ale potrzebny do tego kod może być skomplikowany.  Kolumny wyliczeniowe powodują konieczność wykonywania dodatkowych operacji w systemie bazodanowym, używaj ich więc tylko wtedy, gdy korzyści przewyższają koszty.  Zazwyczaj będziesz chciał tworzyć indeksy na kolumnach wyliczeniowych, aby zyskać pewne korzyści w zamian za zwiększenie wykorzystania przestrzeni dyskowej i wolniejszą aktualizację.  Wykorzystanie widoków do definiowania obliczeń jest często dobrą alternatywą dla przechowywania obliczeń w tabeli tam, gdzie nie jest konieczne indeksowanie. Zagadnienie 6: Definiowanie kluczy obcych do ochrony integralności referencyjnej Podczas tworzenia poprawnego projektu schematu bazy danych w wielu tabelach tworzone są klucze obce zawierające klucze główne z tabel nadrzędnych. Na przykład tabela Orders (zamówienia) w bazie danych sprzedaży powinna zawierać kolumnę CustomerId (identyfikator klienta) lub CustomerNumber (numer klienta) wskazujący na klucz główny tabeli Customers (klienci), dzięki czemu możliwe jest zidentyfikowanie tego, kto złożył dane zamówienie. Rysunek 1.8 przedstawia możliwy układ „typowej” bazy sprzedaży. UWAGA Rysunek 1.8 został utworzony przy wykorzystaniu narzędzia do tworzenia diagramów, które jest częścią pakietu Microsoft SQL Server Management Studio. Podobne narzędzia dostępne są w DB2, MySQL, Oracle i Microsoft Access, a także w specjalizowanych narzędziach do modelowania, takich jak Erwin oraz Idera ER/Studio. Poleć książkęKup książkę Z a g ad n i e n i e 6 : D e fi n i o w a n i e k lu c z y o b c y c h d o o c h ro n y i nt e g r al n oś ci referencyjnej 45 Rysunek 1.8. Projekt typowej bazy danych sprzedaży Diagram jasno pokazuje relacje pomiędzy poszczególnymi tabelami. Symbol klucza na końcu linii relacji wskazuje, że relacja jest od klucza głównego, a nieskończoność po drugiej stronie oznacza relację „jeden do wielu” dla klucza obcego w drugiej tabeli. System bazodanowy zna relacje pomiędzy tabelami, ponieważ zadeklarowaliśmy więzy deklaratywnej integralności referencyjnej (DRI — ang. declarative referential integrity). Te definicje relacji mają dwa cele: 1. Podczas tworzenia nowego widoku lub procedury przechowywanej za pomocą graficznego edytora zapytań dla bazy edytor wie, jak poprawnie konstruować złączenia. 2. System bazodanowy wie, jak wspierać integralność danych podczas wstawiania lub zmiany w tabeli po stronie „wielu”, lub podczas zmiany lub usuwania danych po stronie „jeden”. Drugi punkt jest najważniejszy, ponieważ musisz zapewnić, że na przykład nie mogą być tworzone wiersze zamówienia dla nieistniejących identyfikatorów klientów. Jeżeli możliwa jest zmiana identyfikatora klienta w tabeli klientów, powinieneś mieć pewność, że nowa wartość zostanie rozpropagowana (można Poleć książkęKup książkę 46 R o z dz i ał 1. Projektowanie modelu danych to wyspecyfikować za pomocą klauzuli ON UPDATE CASCADE) do wszystkich powiązanych rekordów zamówień. Jeżeli natomiast użytkownik spróbuje usunąć rekord klienta, który posiada powiązane rekordy w tabeli zamówień, powinieneś zadbać o to, aby albo usunięcie nie było dozwolone, albo wszystkie powiązane rekordy w tabeli zamówień również zostały usunięte (można to wyspecyfikować za pomocą klauzuli ON DELETE CASCADE). Aby włączyć tę ważną funkcję w swojej bazie danych, musisz dodać więzy klucza obcego (FOREIGN KEY) —albo podczas tworzenia tabeli po stronie „wielu” przy wykorzystaniu CREATE TABLE, albo później za pomocą ALTER TABLE. Zobaczmy, jak to zrobić dla tabeli klientów (Customers) i zamówień (Orders). Najpierw utwórzmy tabelę Customers — jej definicja znajduje się na listingu 1.9. Listing 1.9. Utworzenie tabeli Customers CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, CustFirstName varchar(25) NULL, CustLastName varchar(25) NULL, CustStreetAddress varchar(50) NULL, CustCity varchar(30) NULL, CustState varchar(2) NULL, CustZipCode varchar(10) NULL, CustAreaCode smallint NULL DEFAULT 0, CustPhoneNumber varchar(8) NULL ); Teraz utwórzmy tabelę Orders (zamówienia), a potem wykonajmy polecenie ALTER TABLE, które zdefiniuje relację. Potrzebne polecenia znajdują się na listingu 1.10. Listing 1.10. Utworzenie tabeli Orders i zdefiniowanie relacji CREATE TABLE Orders ( OrderNumber int NOT NULL PRIMARY KEY, OrderDate date NULL, ShipDate date NULL, CustomerID int NOT NULL DEFAULT 0, EmployeeID int NULL DEFAULT 0, OrderTotal decimal(15,2) NULL DEFAULT 0 ); ALTER TABLE Orders ADD CONSTRAINT Orders_FK99 FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID); Zauważ, że jeżeli najpierw utworzysz dwie tabele, dodasz do nich dane, a następnie postanowisz dodać więzy klucza obcego, wówczas próba modyfikacji tabeli Orders może zakończyć się niepowodzeniem, jeżeli dane w tabelach nie przejdą sprawdzenia integralności. W niektórych systemach bazodanowych dodanie relacji może zakończyć się powodzeniem, ale będzie ona uznana za Poleć książkęKup książkę Z a g ad n i e n i e 6 : D e fi n i o w a n i e k lu c z y o b c y c h d o o c hro n y i n t eg r al n oś ci referencyjnej 47 niewiarygodną i nie będzie wykorzystywana przez optymalizator, dlatego samo jej zdefiniowanie nie gwarantuje jeszcze integralności wstawionych danych, gdy relacja jeszcze nie istniała. Możesz również zdefiniować więzy podczas tworzenia tabeli podrzędnej, tak jak na listingu 1.11. Listing 1.11. Definiowanie klucza obcego podczas tworzenia tabeli CREATE TABLE Orders ( OrderNumber int NOT NULL PRIMARY KEY, OrderDate date NULL, ShipDate date NULL, CustomerID int NOT NULL DEFAULT 0 CONSTRAINT Orders_FK98 FOREIGN KEY REFERENCES Customers (CustomerID), EmployeeID int NULL DEFAULT 0, OrderTotal decimal(15,2) NULL DEFAULT 0 ); W niektórych systemach bazodanowych (np. Microsoft Access) zdefiniowanie więzów integralności referencyjnej automatycznie tworzy indeks na kolumnie (lub kolumnach) z kluczem obcym, więc podczas wykonywania złączeń wydajność może być lepsza. Dla tych baz, w których taki indeks nie jest tworzony automatycznie (np. DB2), dobrą praktyką jest jego utworzenie w celu optymalizacji sprawdzania więzów. Do zapamiętania  Jawne określenie klucza obcego pozwala zapewnić integralność danych pomiędzy połączonymi tabelami poprzez sprawdzenie, że nie istnieje żaden rekord w tabeli podrzędnej, dla którego nie istnieje odpowiadający rekord w tabeli nadrzędnej.  Próba dodania więzów klucza obcego do tabel zawierających dane zakończy się niepowodzeniem, jeżeli istniejące dane pogwałcają dodawane więzy.  W niektórych systemach wydajność złączeń może zostać poprawiona, ponieważ dodanie więzów klucza obcego automatycznie powoduje dodanie indeksu. W innych systemach powinieneś sam zadbać o utworzenie indeksu pokrywającego relację klucza obcego. Nawet bez indeksów optymalizatory niektórych systemów mogą traktować kolumny inaczej i tworzyć lepsze plany zapytań. Poleć książkęKup książkę 48 R o z dz i ał 1. Projektowanie modelu danych Zagadnienie 7: Upewnij się, że relacje między tabelami mają sens Teoretycznie możesz utworzyć między dwiema tabelami taką relację, jaką tylko chcesz, o ile typy danych każdej pary połączonych kolumn będą takie same. Ale to, że możesz coś zrobić, nie oznacza jeszcze, że powinieneś. Rozważmy diagram schematu bazy zawierającej informacje o zamówieniach, przedstawiony na rysunku 1.9. Rysunek 1.9. Diagram schematu bazy danych zamówień Na pierwszy rzut oka wydaje się, że wszystko gra: jest kilka tabel, a każda z nich zawiera dane odrębnej encji. Skupmy się na trzech z nich: Employees (pracownicy), Customers (klienci) i Vendors (dostawcy). Jeżeli przyjrzysz się tym trzem tabelom, zobaczysz, że posiadają wiele podobnych pól. Często nie jest to uważane za problem, ponieważ dane w tabelach są zazwyczaj różne. Jeżeli jednak ta firma miałaby dostawców lub pracowników, którzy są również klientami firmy, ten model pogwałcałby reguły dotyczące duplikacji danych omówione w zagadnieniu 2, „Eliminacja nadmiarowego przechowywania danych”. Można próbować rozwiązać ten problem poprzez utworzenie jednej tabeli, nazwanej choćby Contacts (kontakty), w której wymienione byłyby wszystkie rodzaje kontaktów. Takie podejście nie jest jednak pozbawione wad. Poleć książkęKup książkę Z a g ad n i e n i e 7 : U p e w n i j się , ż e relacje mię dzy tabelami mają s e ns 49 Na przykład EmployeeID (identyfikator pracownika), CustomerID (identyfikator klienta) i VendorID (identyfikator dostawcy) pochodziłyby teraz z jednego klucza głównego, czyli ContactID (identyfikator kontaktu), który nie daje nam możliwości zweryfikowania, że dany identyfikator należy faktycznie do danego typu kontaktu. Można rozwiązać ten problem, dodając tabele Customers, Vendors i Employees zawierające relacje „jeden do jednego” do tabeli Contacts. Zaletą tego podejścia jest to, że dane wyjątkowe dla encji, takie jak ManagerID (identyfikator menadżera) czy VendWebPage (strona internetowa dostawcy), pozwala trzymać oddzielnie od innych rekordów, które nie potrzebują tych informacji. Oznacza to również, że aplikacja wykorzystująca schemat będzie bardziej rozbudowana, ponieważ musi zawierać logikę sprawdzającą, czy encja istnieje, a jeżeli tak, czy posiada wypełnione wymagane dane. Dodatkowe pola nie miałyby sensu, gdyby aplikacja mogła bezmyślnie wstawiać dane bez sprawdzenia istnienia duplikatów. Co zrozumiałe, nie wszystkie firmy chcą wydawać więcej pieniędzy i poświęcać więcej czasu na dodatkowe utworzenie bardziej złożonego i rozbudowanego rozwiązania (zawierającego więcej tabel). Bardziej prawdopodobne jest, że firma sprzedająca produkty nie ma klientów, którzy są jednocześnie dostawcami lub pracownikami, więc sporadyczna duplikacja w tych rzadkich przypadkach jest niewielką ceną do zapłacenia za uproszczenie schematu bazy. Rozważmy scenariusz, w którym musimy przypisać terytoria sprzedaży do pracowników i w konsekwencji mapować klientów do pracowników znajdujących się na tych terytoriach. Jednym ze sposobów może być utworzenie relacji pomiędzy kolumną CustZipCode (kod pocztowy klienta) w tabeli Customers (klienci) i kolumną EmpZipCode (kod pocztowy pracownika) w tabeli Employees (pracownicy). Obie kolumny mają ten sam typ danych i zawierają dane z tej samej domeny. Zamiast tworzyć relacje pomiędzy tabelami, mógłbyś wykonać złączenie na tych kolumnach, aby sprawdzić, którzy klienci mieszkają blisko których pracowników. Możliwe jest utworzenie po prostu klucza obcego EmployeeID (identyfikator pracownika) w tabeli Customers (klienci) i połączenie w ten sposób klienta z pracownikiem. Powoduje to jednak nowe problemy. Po pierwsze, załóżmy, że klient przeprowadza się do innego terytorium sprzedażowego. Osoba wprowadzająca dane może poprawnie zmienić adres klienta, ale nie zdawać sobie sprawy lub nie pamiętać o tym, że konieczna jest również aktualizacja pracownika. Może to być źródłem nowych błędów. Lepszym rozwiązaniem byłoby utworzenie tabeli SalesTerritory (terytorium sprzedażowe), posiadającej klucz obcy EmployeeID, a rekordy w tej tabeli identyfikowałyby kody pocztowe (TerrZIP) przypisane do danego pracownika. Każdy kod pocztowy byłby un
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Mistrzowski SQL. 61 technik pisania wydajnego kodu SQL
Autor:
, ,

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: