Wykład 1 - bazy danych budowa - optymalizacja
advertisement
Relacyjne bazy danych Grzegorz Kowalski Relacyjny model baz danych jeden z modeli logicznych baz danych początki w roku 1969 – dr Edgar F. Codd (IBM) nazwa pochodzi od pojęcia relacja stanowiącego element teorii mnogości znajomość podstaw matematycznych nie jest konieczna ☺ do zarządzania słuŜą tzw. SZRBD - systemy zarządzania relacyjnymi bazami danych Systemy zarządzania relacyjnymi bazami danych – SZRBD architektura klient-serwer m. in. Oracle (Oracle Corp.), SQL Server (Microsoft), MySQL Anatomia relacyjnych baz danych tabele pola rekordy klucze perspektywy relacje: jeden-do-jednego jeden-do-wielu wiele-do-wielu Anatomia RBD – tabele, pola, rekordy CustomerlD FirstName LastName StreetAdress City State ZipCode 1010 Michael Davolio 672 Lamont Ave Houston TX 77201 1011 Margaret Peacock 667 Red River Road Austin TX 78710 1012 Estella Pundt 2500 Rosales Lane Dallas TX 75260 1013 Mark Rosales 323 Advocate Lane El Paso TX 79915 1014 Consuelo Maynez 3445 Cheyenne Road El Paso TX 79915 1015 Ryan Ehrlich 455 West Palm Ave San Antonio TX 78284 POLA REKORDY Klienci Anatomia RBD - pola najmniejsza struktura bazy danych dane mogą być odczytywane i zapisywane jakość informacji zaleŜy od ilości czasu poświęconego na wprowadzenie integralności strukturalnej i integralności danych pole powinno zawierać tylko jedną wartość nazwa pola adekwatna do zawartości Anatomia RBD - rekordy CustomerlD FirstName LastName StreetAdress City State ZipCode 1010 Michael Davolio 672 Lamont Ave Houston TX 77201 1011 Margaret Peacock 667 Red River Road Austin TX 78710 kaŜdy rekord stanowi pełny zbiór pól 1012 Estella Pundt 2500 Rosales Lane Dallas przypisanych do danej tabeli TX 75260 1013 Mark Rosales 323 Advocate Lane El Paso TX 79915 1014 Consuelo Maynez 3445 Cheyenne Road El Paso TX 79915 1015 Ryan Ehrlich 455 West Palm Ave San Antonio TX 78284 Anatomia RBD - klucze pola specjalne pełniące szczególne funkcje w tabelach tabele mogą zawierać kilka rodzajów kluczy dwa najwaŜniejsze rodzaje kluczy: klucz główny klucz obcy Anatomia RBD – klucze główne Klucz główny – pole lub grupa pól jednoznacznie identyfikująca kaŜdy rekord w tabeli wartość klucza identyfikuje konkretny rekord w bazie danych pole klucza (pole kluczowe) identyfikuje tabelę w całej bazie danych wymuszają integralność na poziomie tabel pomagają tworzyć relacje pomiędzy tabelami KaŜda tabela powinna zawierać klucz główny Anatomia RBD – klucze główne CustomerlD FirstName LastName StreetAdress City State ZipCode 1010 Michael Davolio 672 Lamont Ave Houston TX 77201 1011 Margaret Peacock 667 Red River Road Austin TX 78710 1012 Estella Pundt 2500 Rosales Lane Dallas TX 75260 1013 Mark Rosales 323 Advocate Lane El Paso TX 79915 1014 Consuelo Maynez 3445 Cheyenne Road El Paso TX 79915 1015 Ryan Ehrlich 455 West Palm Ave San Antonio TX 78284 Anatomia RBD – klucze obce Klucz obcy – kopia klucza głównego w innej tabeli umoŜliwiają wprowadzanie relacji między tabelami wymuszają integralność na poziomie relacji Anatomia RBD – klucze obce Agenci KLUCZ GŁÓWNY AgentID AgentFirstName AgentLastName DateofHire AgentHomePhone 1001 Stella Rosales 1994-07-11 299-5764 1002 Steve Pundt 1994-05-01 515-5762 1003 Randi Nathanson 1994-09-11 998-3882 «otherfields» KLUCZ OBCY Artyści KLUCZ GŁÓWNY EntertainerlD AgentID EntertainerName EntertainerPhone 98100 1002 The Mike Hernandez Trio 959-8837 98101 1002 Jazz Times 555-9928 98102 1003 The Country Squires 709-3542 «otherfields» Anatomia RBD - perspektywy Perspektywa – wirtualna tabela składająca się z kolumn jednej lub większej ilości tabel Anatomia RBD - perspektywy Klienci CustomerlD CustFirstName CustLastName CustPhone 10001 Sally Callahan 555-2671 10002 Ann Fuller 555-2496 10003 James Levelling 555-2501 «otherfielcls» AngaŜe EngagementNumber CustomerlD StartDate End Date StartTime 1 52113 1999-07-01 1199-07-04 13:00 2 54223 1999-07-01 1999-07-05 13:00 3 52233 1999-07-10 1999-07-15 13:00 «other fields» Klienci_AngaŜe (perspektywa) EngagementNumber CustFirstName CustLastName StartDate EndDate 1 Mark Rosales 1999-07-01 1999-07-04 2 Thomas Fuller 1999-07-01 1999-07-05 3 Sally Callahan 1999-07-10 1999-07-15 Anatomia RBD - relacje Relacje – powiązanie rekordów jednej tabeli z rekordami innej tabeli jeden-do-jednego jeden-do-wielu wiele-do-wielu Anatomia RBD – relacje – jeden-do-jednego Pojedynczemu rekordowi z jednej tabeli odpowiada tylko jeden rekord z drugiej tabeli oraz pojedynczemu rekordowi z drugiej tabeli odpowiada tylko jeden rekord z pierwszej tabeli. W tego typu relacjach istnieje tabela główna i tabela podrzędna. Anatomia RBD – relacje – jeden-do-jednego Agenci AgentID AgentFirstName AgentLastName DateofHire AgentHomePhone 1001 Stella Rosales 1994-07-11 299-5764 1002 Steve Pundt 1994-05-01 515-5762 1003 Randi Nathanson 1994-09-11 998-3882 «otherfields» Odszkodowania AgentID HourlyRate Commission Rate 1001 21.50 4.5% 1002 25.75 3.0% 1003 20.00 4.5% «other fields» Anatomia RBD – relacje – jeden-do-wielu Pojedynczemu rekordowi z jednej tabeli odpowiada wiele rekordów z drugiej tabeli oraz pojedynczemu rekordowi z drugiej tabeli odpowiada tylko jeden rekord z pierwszej tabeli. Anatomia RBD – relacje – jeden-do-wielu Artyści ArtistID ArtistFirstName ArtistLastName GroupName «innepola» 67001 Mike Hernandez The Mike Hernandez Trio 67002 Zachary Ehrlich Jazz Times 67003 Gerry Greer The Country Squires AngaŜe EngagementID ArtistID CustomerlD StartDate EndDate 1001 67003 701 1998-09-10 1998-09-12 1002 67001 625 1998-09-11 1998-09-12 1003 67001 712 1998-09-15 1998-09-19 «innepola» Anatomia RBD – relacje – wiele-do-wielu Pojedynczemu rekordowi z dowolnej tabeli odpowiada wiele rekordów z tabeli komplementarnej. wymagane jest ustanowienie dodatkowej tabeli łączącej tabela łącząca powinna zawierać klucze główne obydwu tabel Anatomia RBD – relacje – wiele-do-wielu Artyści ArtistlD ArtistFirstName ArtistLastName GroupName 67001 Mike Hernandez The Mike Hernandez Trio 67002 Zachary Ehrlich Jazz Times 67003 Gerry Greer The Country Squires Artyści_Nagrania ArtistlD RecordingID 67001 1102 67001 1105 67002 1102 67002 1106 67003 1104 «innepola» (tabela łącząca) Nagrania RecordingID Title YearReleased 1102 Jazz 'Round Midnight 1995 1103 Until I Return 1998 1104 Love Me, Donl Leave Me 1995 1105 Midnight Breeze 1996 1106 No Puede Ver 1994 «inne pola» Relacje – określanie typu uczestnictwa druga kreska – uczestnictwo obowiązkowe kółko – uczestnictwo opcjonalne Tworzenie reguł usuwania usunięcie rekordu z tabeli Komisje spowoduje usunięcie rekordów z tabeli Komisje_Członkowie Poprawna struktura bazy danych Niepoprawny projekt bazy danych: stwarza problemy w implementacji zapytań SQL moŜe okazać się, Ŝe BD będzie bezuŜyteczna Jakość bazy danych zaleŜy od ilości czasu poświęconego na etapie projektowania. Poprawna struktura bazy danych Korygowanie pól Korygowanie tabel Kontrola poprawności relacji Korygowanie pól nazwa pola powinna odzwierciedlać jego zawartość unikatowe nazwy kaŜdego pola w BD Czy pole nie identyfikuje więcej niŜ jedną cechę? Zastanowić się czy nie podzielić pola na mniejsze pola. bezpiecznie jest uŜywać nazw pól zaczynających się od litery i mogą zawierać jedynie litery, cyfry i podkreślenie Struktura pól bazy danych pole powinno odzwierciedlać cechę któremu poświęcona jest tabela macierzysta – Czy pole rzeczywiście powinno znajdować się w danej tabeli? pole powinno zawierać pojedynczą wartość wartości pól nie powinny być wynikiem operacji matematycznej ani konkatenacji – brak pól wyliczonych kaŜde pole moŜe występować w bazie tylko jeden raz Tabela - zbędne pola Personel StaffID Staff FirstName StaffLastName StaffStreetAddress StaffCity StaffState 98014 James Leverling 722 Moss Bay Blvd. Kirkland WA 98019 Laura Callahan 901 Pine Avenue Portland OR 98020 Albert Buchanan 13920 S.E. 40th Street Bellevue WA 98021 Tim Smith 30301-166th Ave. N.E. Seattle WA 98022 Janet Leverling 722 Moss Bay Blvd. Kirkland WA 98023 Alaina Hallmark Route 2, Box 203 B Woodinville WA «innepola» Klasy ClassID Class Classroom ID StaffID StaffLastName «inne pola» StaffFirstName 1031 Art History 1231 98014 Leverling James 1030 Art History 1231 98014 Levelling James 2213 Biological Principles 1532 98021 Smith Tim 2005 Chemistry 1515 98019 Callahan Laura 2001 Chemistry 1519 98233 Hallmark Alaina 1006 Drawing 1627 98020 Buchanan Albert 2907 Elementary Algebra 3445 98022 Levelling Janet «inne pola» Eliminacja pól wieloczęściowych CustomerlD CustomerName StreetAddress PhoneNumber 1001 Suzanne Viescas 15127 NE 24th, #383, Redmond, WA 98052 425 555-2686 1002 Will Thompson 122 Spring River Drive, Duvall, Wa 98019 425 555-2681 1003 Gary Hallmark Route 2, Box 203B, Auburn, WA 98002 253 555-2676 1004 Michael Davolio 672 Lamont Ave, Houston, TX 77201 713 555-2491 1005 Kenneth Peacock 4110 Old Redmond Rd., Redmond, WA 98052 425 555-2506 «innepola» POLA WIELOCZĘŚCIOWE CustomerlD CustFirstName CustLastName CustAddress CustCity CustState CustZipcode 1001 Suzanne Viescas 15127 NE 24th, #383 Redmond WA 98052 1002 Wil Thompson 122 Spring River Drive Duvall WA 98019 1003 Gary Hallmark Route2,Box203B Auburn WA 98002 1004 Michael Davolio 672 Lamont Ave Houston TX 77201 1005 Kenneth Peacock 4110 Old Redmond Rd. Redmond WA 98052 Eliminowanie pól wielowartościowych Piloci PilotJD PilotFirstName PilotLastName HireDate Certifications 25100 John Leverling 1994-07-11 727, 737,757, MD80 25101 David Callahan 1994-05-01 737, 747,757 25102 David Smith 1994-09-11 757, MD80, DC9 25103 Kathryn Patterson 1994-07-11 727, 737,747,757 25104 Michael Hernandez 1994-05-01 737.757.DC10 25105 Kendra Bonnicksen 1994-09-11 757, MD80, DC9 «innepola» Eliminowanie pól wielowartościowych Piloci PilotID PilotFirstName PilotLastName HireDate «inne pola» 25100 John Leverling 1994-07-11 25101 David Callahan 1994-05-01 25102 David Smith 1994-09-11 25103 Kathryn Patterson 1994-07-11 25104 Michael Hernandez 1994-05-01 25105 Kendra Bonnicksen 1994-09-11 Piloci_Certyfikaty PilotID CertificationID 25100 8102 25100 8103 25100 8105 25100 8106 25101 8103 25101 25101 (tabela łącząca) Certyfikaty CertificationID TypeofAircraft 8102 Boeing 727 8103 Boeing 737 8104 Boeing 747 8104 8105 Boeing 757 8105 8106 McDonnell Douglas MD80 «innepola» Korygowanie tabel - nazwa unikatowa i zrozumiała nazwa tabeli nazwa precyzyjnie i jednoznacznie opisuje jej zawartość nie naleŜy uŜywać skrótów tabela powinna opisywać tylko jeden temat nazwy tabel powinny być w liczbie mnogiej (łatwiej odróŜnić wtedy je od nazw pól) Korygowanie tabel – kontrola poprawności struktur tabela powinna opisywać tylko jeden temat kaŜda tabela powinna mieć klucz główny tabele nie powinny zawierać pól wielowartościowych ani wieloczęściowych eliminacja pól wyliczonych brak zbędnych duplikatów pól Korygowanie tabel – usuwanie zbędnych duplikatów pól Pracownicy EmployeelD EmpFirstName EmpLastName 7004 7005 7006 7007 7008 7009 7010 7011 Peacock Kennedy Thompson Callahan Buchanan Smith Patterson Viescas Samuel John Sarah David Andrea David Neil Michael Committeel Committee2 Steering Y2K Conformance Safety Safety Y2K Conformance Steering Y2K Conformance Steering Safety Y2K Conformance Y2K Conformance Steering Safety EmpFirstName «inne pola» Członkowie_Komitety Pracownicy Employee ID Committee3 EmpLastName EmpCity 7004 7005 7006 7007 7008 7009 7010 Peacock Kennedy Thompson Callahan Buchanan Smith Patterson Samuel John Sarah David Andrea David Neil Chico Portland Lubbock Salem Medford Fremont San Diego 7011 Viescas Michael Redmond «inne pola» EmployeelD 7004 7005 7005 7006 7006 7006 7008 7009 Committee Steering Y2K Conformance Safety Safety Y2K Conformance Steering Y2K Conformance Steering Korygowanie tabel – usuwanie zbędnych duplikatów pól Pracownicy Employee ID EmpFirstName EmpLastName EmpCity 7004 7005 7006 7007 7008 7009 7010 Peacock Kennedy Thompson Callahan Buchanan Smith Patterson Samuel John Sarah David Andrea David Neil Chico Portland Lubbock Salem Medford Fremont San Diego 7011 Viescas Michael Redmond Członkowie_Komitety Komitety EmployeelD CommitteelD 7004 7005 7005 7006 7006 7006 7008 7009 CommitteelD 103 104 102 102 104 103 104 103 «inne pola» CommitteeName MeetingRoom MeetingDay 100 Budget 11-C Tuesday 101 102 Christmas Safety 9-F 12-B Monday Monday 103 Steering 12-D Tuesday 104 Y2K Compliance Main-South Wednesday Korygowanie tabel – klucze kaŜda tabela powinna zawierać klucz główny klucz główny powinien jednoznacznie identyfikować rekord w tabeli klucze główne wykorzystywane są w tworzeniu relacji Korygowanie tabel – klucze Pracownicy EmployeeID EmpFirstName EmpLastName EmpCity EmpCity EmpState EmpState EmpZip EmpZip 98001 Peacock Samuel Chico Chico CA CA 95926 95926 98002 Kennedy John Portland Portland OR OR 97208 97208 98003 Thompson Michael Redmond Redmond WA WA 98052 98052 98004 Callahan David Salem Salem OR OR 98005 Buchanan Andrea Medford Medford OR OR 97501 97501 98006 Smith Michael Fremont Fremont CA CA 94538 94538 98007 Peacock Neil San San Diego Diego CA CA 92199 92199 98008 Kennedy John Redmond Redmond WA WA 98052 98052 «inne «inne pola» pola»