ĆWICZENIE 1

TWORZENIE BAZY DANYCH W

MS ACCESS 2007

Warszawa 2009

Spis treści

1. Opis bazy danych KADRY

Celem ćwiczeń narzędzia MS Access 2007 jest przygotowanie systemu bazodanowego KADRY, który zautomatyzuje naliczanie miesięcznej wypłaty poszczególnym pracownikom firmy ALFA. Wypłata pracowników obliczana jest według następującej formuły:

Wypłata = Płaca zasadnicza + Dodatek stażowy + Dodatek funkcyjny + Dodatek BHP + Dodatek na dzieci - Podatek - Rata pożyczki

Płaca zasadnicza wynika z umowy o pracę, podpisanej przez pracownika z firmą ALFA. Dodatek stażowy jest uzależniony od daty zatrudnienia pracownika w firmie ALFA. Dodatek funkcyjny jest związany z miejscem i stanowiskiem zatrudnienia pracownika w firmie ALFA. Dodatek BHP wypłacany jest wszystkim pracownikom, którzy pracują w uciążliwych lub szkodliwych warunkach. Dodatek na dzieci jest wypłacany do momentu ukończenia przez dzieci pracownika 18 lat, więc zależy od daty urodzenia dziecka pracownika. Rata pożyczki jest potrącana, jeżeli pracownik skorzystał z pożyczki firmowej. Wielkość podatku jest ustalana zgodnie z bieżącą ustawą podatkową.

Z powyższego opisu wynika, że w celu określenia prawidłowej wysokości wypłaty pracownika, należy wprowadzić do bazy danych systemu KADRY szereg danych elementarnych, które są niezbędne do obliczenia poszczególnych składowych jego wypłaty. Zatem pierwszym krokiem tworzenia systemu bazodanowego jest przygotowanie poprawnej struktury bazy danych KADRY.

Struktura bazy danych Kadry powinna się składać z 4 tabel:

• Pracownicy - to główna tabela bazy danych, przechowująca dane osobowe i zawodowe pracownika,

• Jednostki - zawierającą charakterystykę trzech oddziałów firmy: Zakładu Analiz i Projektowania, Zakładu Wdrożeń Systemów oraz Zakładu Usług Programistycznych,

• Stanowiska - to tabela pomocnicza dla tabeli Pracownicy (tzw. tabela słownikowa), przechowująca aktualną listę wszystkich stanowisk w firmie ALFA,

• Dzieci - zawierająca dane dzieci poszczególnych pracowników firmy ALFA.

Ćwiczenie 1 prezentuje tworzenie bazy danych w narzędziu MS Access 2007.

2. Opis ekranu MS Access 2007

Ze względu na konieczność odwoływania się w treści instrukcji do poszczególnych elementów ekranu programu Ms Access, poniżej został zamieszczony ich opis.

3. Sformułowanie zadania

• Należy utworzyć bazę danych KADRY, w której będzie można przechowywać dane o pracownikach firmy ALFA (ich danych osobowych i zawodowych) oraz dzieciach tych pracowników.

• Struktura bazy danych powinna składać się z 4 tabel: Pracownicy, Jednostki, Stanowiska i Dzieci.

• Tabela Pracownicy zawiera następujące atrybuty:

• idPrac - numer identyfikacyjny pracownika w bazie danych KADRY,

• nazwiskoP - nazwisko pracownika,

• imięP - imię pracownika,

• dataUrP - data urodzenia pracownika,

• dataZat - data zatrudnienia pracownika w firmie ALFA,

• kodMiastoP - kod i miejscowość zamieszkania pracownika,

• ulicaNrP - ulica, numer domu i mieszkania pracownika,

• telefonP - telefon kontaktowy do pracownika,

• stanowisko - stanowisko zatrudnienia pracownika,

• płaca - płaca zasadnicza pracownika,

• pożyczka - wysokość pożyczki firmowej pobranej przez pracownika,

• bhp - czy pracownikowi przysługuje dodatek BHP za szkodliwe lub uciążliwe warunki pracy?

• idJo - numer identyfikacyjny jednostki organizacyjnej (oddziału) firmy ALFA, w której pracownik jest zatrudniony.

• Tabela Jednostki zawiera następujące atrybuty:

• idJo - numer identyfikacyjny jednostki organizacyjnej (oddziału) firmy ALFA,

• nazwaJo - nawa jednostki organizacyjnej (oddziału) firmy ALFA,

• kodMiastoJo - kod i miejscowość adresu jednostki organizacyjnej,

• ulicaNrJo - ulica i numer adresu jednostki organizacyjnej,

• telefonJo - telefon kontaktowy jednostki organizacyjnej,

• infoJo - opis informacyjny dotyczący funkcjonowania jednostki organizacyjnej.

• Tabela Stanowiska zawiera tylko jeden atrybut o nazwie stanowisko. Z danych tej tabeli korzysta atrybut stanowisko w tabeli Pracownicy.

• Tabela Dzieci zawiera następujące atrybuty:

• idDziecka - numer identyfikacyjny dziecka pracownika,

• imięD - imię dziecka pracownika (zakładamy, że nazwisko dziecka jest zgodne z nazwiskiem pracownika - rodzica),

• dataUrD - data urodzenia dziecka,

• idPrac - numer identyfikacyjny rodzica dziecka.

• Poszczególne atrybuty tabel powinny zostać zabezpieczone przed błędami wprowadzania danych, a format i wygląd pól powinien ułatwiać wprowadzanie danych do tabel.

• Tabele muszą zostać połączone relacjami, w celu utworzenia poprawnej struktury bazy danych.

• Po sprawdzeniu poprawności struktury bazy danych, do tabel powinny zostać wprowadzone dane w celu przetestowania prawidłowości jej funkcjonowania.

4. Wykonanie zadania

1. Utworzenie bazy danych KADRY

• Po uruchomieniu programu MS Access 2007, tworzymy nową bazę danych. Przez wybranie z menu Przycisku pakietu Office opcji Nowy(Rys. 1).

Rys. 1 Tworzenie nowej, pustej bazy danych

• Do okienka tekstowego, które pojawi się w prawej, dolnej części ekranu, wprowadzamy nazwę bazy danych (Kadry) oraz podajemy lokalizację wskazaną przez Prowadzącego laboratorium (Rys. 2).

Rys. 2 Wprowadzenie nazwy i miejsca składowania bazy danych

1. Definiowanie tabel bazy danych KADRY

• Po utworzeniu i nazwaniu bazy danych KADRY otrzymujemy dostęp do definicji pierwszej tabeli w strukturze bazy danych. Należy do niej wprowadzić opis poszczególnych atrybutów tabeli Pracownicy, według wzorca umieszczonego na Rys. 3.

Rys. 3 Definiowanie nowej tabeli w widoku projektu

Definicja każdego atrybutu tabeli musi się składać co najmniej z nazwy pola (max 64 znaki) i typu danych (wybieranego z listy wyboru). Ponieważ nazwa pola powinna być możliwie krótka, więc jej uzupełnieniem jest opis pola (który jednak nie jest obowiązkowy).

Typy danych wykorzystane w definicji tabeli Pracownicy to:

• Autonumerowanie - kolejny numer automatycznie przypisywany przez program do rekordu, nie podlega możliwości zmiany przez użytkownika, jest łączony na stałe z rekordem i wraz z nim na stałe usuwany z tabeli, w każdej tabeli może być wykorzystany tylko jeden raz (najwyżej do jednego pola tabeli),

• Tekst - standardowe pole tekstowe o rozmiarze max 255 znaków,

• Data/Godzina - pole umożliwiające wprowadzanie wartości typu data lub godzina w formacie wybranym przez użytkownika,

• Waluta - wartości liczbowe wyrażone w jednostkach monetarnych,

• Tak/Nie - pole typu logicznego przyjmujące wartości Tak lub Nie,

• Liczba - pole liczbowe (całkowite lub ułamkowe).

• Projektując bazę danych, nie wolno zapomnieć o możliwości efektywnego wyszukiwania i pozyskiwania przechowywanych w niej danych. Aby to zrealizować, należy w każdej tabeli wyznaczyć identyfikator tabeli, czyli pole, którego poszczególne wartości będą zawsze unikalne (każda może w tabeli wystąpić tylko jeden raz). Dzięki temu każdy pracownik będzie mógł być szybko odnaleziony po swoim unikalnym numerze identyfikacyjnym. Identyfikatorem tabeli Pracownicy, noszącym nazwę klucza podstawowego, jest pole idPrac. Wartość klucza w tym polu ustawiamy wybierając zakładkę Projekt z menu górnego oraz narzędzie Klucz podstawowy (Rys. 4).

Rys. 4 Definiowanie klucza podstawowego w tabeli Pracownicy

• Teraz należy zapisać definicję tabeli poprzez wybranie z paska narzędziowego Szybki dostęp narzędzia dyskietki
  , a następnie wpisania nazwy tabeli Pracownicy do okienka tekstowego (Rys. 5).

Rys. 5 Zapisywanie tabeli Pracownicy

Tak przygotowana tabela jest gotowa do wprowadzania danych.

• Teraz zdefiniujemy tabelę Stanowiska. W tym celu utworzymy nową tabelę, przez wybranie zakładki Tworzenie z menu górnego i narzędzia Tabela (Rys. 6).

Rys. 6 Tworzenie nowej tabeli - menu Tworzenie, narzędzie Tabela

• Utworzona tabela zostaje udostępniona w widoku arkusza danych, przeznaczonym do wprowadzania danych. Ponieważ tabela nie została jeszcze zdefiniowana, więc należy przejść do widoku projektu, który służy do celów definicyjnych. Najeżdżając kursorem myszy na zakładkę z nazwą Tabela2, trzeba wybrać prawy klawisz na myszy i na menu podręcznym wskazać opcję - Widok projektu (Rys. 7)

Rys. 7 Przejście do widoku projektu tabeli

• Przed zmianą widoku tabeli, program musi ją zapisać. Należy wprowadzić do okienka tekstowego nazwę tabeli Stanowiska (Rys. 8).

Rys. 8 Zapisywanie tabeli Stanowiska

• Stanowiska jest tabelą jednoatrybutową. Należy ją zdefiniować zgodnie z zamieszczonym wzorcem (Rys. 9).

Rys. 9 Definicja tabeli Stanowiska

UWAGA! Nie wolno zapomnieć o ustawieniu klucza podstawowego dla atrybutu stanowisko!

1. Utworzenie relacji bazy danych KADRY

• Po zdefiniowaniu tabel w bazie danych należy połączyć je relacjami, które definiują związki pomiędzy poszczególnymi tabelami i umożliwiają prawidłowe funkcjonowanie bazy danych. Aby zdefiniować relację pomiędzy tabelą Pracownicy i Stanowiska należy z menu górnego wybrać zakładkę Narzędzia bazy danych, a następnie narzędzie Relacje (Rys. 10).

Rys. 10 Aktywowanie okna relacji

Istotne jest, że w momencie tworzenia relacji tabele, dla których te relacje są definiowane, muszą być zamknięte!

• Teraz należy wskazać tabele, które powinny pojawić się w oknie relacji. Dodajemy do okna relacji obydwie utworzone tabele. (Rys. 11).

Rys. 11 Dodawanie tabel do okna relacji

Następnie zamykamy okno pokazywania tabel.

• W celu utworzenia prawidłowej relacji pomiędzy dwiema tabelami, należy szczegółowo przeanalizować typ związku, w jakim one pozostają. Taka analiza wymaga głębszego poznania metodyki projektowania baz danych, która będzie prezentowana na zajęciach z baz danych w semestrze III. Na potrzeby zajęć POINF problem zostanie uproszony do znalezienia identycznych atrybutów (atrybuty łączące) w obydwu tabelach. Atrybut taki istnieje i nosi nazwę stanowisko. Żeby utworzyć relację pomiędzy tabelą Pracownicy i Stanowiska, należy przeciągnąć myszką pole stanowisko z tabeli Stanowiska na pole stanowisko w tabeli Pracownicy (Rys. 12).

Rys. 12 Tworzenie relacji pomiędzy tabelami Pracownicy i Stanowiska

• Otworzy się okno definiowania relacji, którym można sprawdzić, czy do definicji relacji włączone zostały prawidłowe atrybuty oraz zdefiniować dodatkowe właściwości relacji. Przykładowo, dla bieżącej relacji należy uaktywnić Wymuszaj więzy integralności oraz Kaskadowo aktualizuj pola pokrewne (Rys. 13). Własności te zabezpieczą tabele Pracownicy i Stanowiska przez błędami wprowadzania danych - nie będzie można wpisać przy Pracowniku nieistniejącego stanowiska, czyli takiego które nie zostało wymienione w tabeli Stanowiska.

Rys. 13 Definiowanie relacji

• Prawidłowo zdefiniowana struktura relacji prezentowana jest na Rys. 14.

Rys. 14 Relacja Pracownicy-Stanowiska

1. Definiowanie właściwości pól w tabelach

• Baza danych powinna być tak przygotowana, żeby możliwie ułatwić jej obsługę użytkownikom, a jednocześnie chronić zasoby wprowadzanych danych przed najczęstszymi błędami. Jednym z typów zabezpieczeń bazy danych jest zdefiniowana struktura relacji oraz więzy integralności aktywowane w relacjach. Kolejny typ to własności dodawane do definicji pól w tabelach. Zacznijmy od tabeli Stanowiska, którą należy otworzyć w widoku projektu. Do otwarcia tabeli służy okienko nawigacyjne, a z menu podręcznego można wybrać Widok projektu (Rys. 18).

Rys. 18 Tworzenie formularza z wykorzystaniem kreatora formularzy

• Okno robocze tabeli Stanowiska składa się z dwóch części: okna podstawowego, w którym znajduje się lista wszystkich atrybutów tabeli oraz okna właściwości pola, które umożliwia wprowadzanie dodatkowych zabezpieczeń dla poszczególnych atrybutów tabeli (Rys. 19).

Rys. 19 Budowa okna widoku projektu tabeli

• Problemem wszystkich atrybutów typu tekstowego jest format tekstu drukowanego w takim polu. Trudno jest ustalić, a następnie utrzymać, jednolity tryb pisania: tylko dużymi, tylko małymi lub z pierwszej dużej litery. Załóżmy, że w polu stanowisko tekst będzie drukowany zawsze dużymi literami, bez względu na sposób pisania osoby wprowadzającej dane. Aby to zapewnić należy w oknie Właściwości pola, we właściwości Format wprowadzić znak > (Rys. 20).

Rys. 20 Ustawianie formatu wielkich liter

• W celu sprawdzenia poprawności ustawionego formatu, należy zapamiętać wprowadzone do definicji tabeli zmiany (narzędzie dyskietki na pasku Szybki dostęp) oraz przejść do widoku arkusza danych (opcja Widok arkusza danych w menu podręcznym), gdzie będzie możliwe wprowadzenie danych do tabeli Stanowiska (Rys. 21)

Rys. 21 Uaktywnianie widoku arkusza danych tabeli

• W Widoku arkusza danych należy wprowadzić nazwy stanowisk roboczych firmy ALFA, widoczne na Rys. 22. Bez względu na sposób wprowadzania danych (małe litery, duże litery czy pierwsza duża litera), wynik zawsze wyświetli się w formacie wielkich liter.

Rys. 22 Dane tabeli Stanowiska

• Kolejnym etapem będzie wykorzystanie listy wprowadzonych stanowisk w tabeli Pracownicy. W tym celu należy otworzyć tabelę Pracownicy w Widoku projektu, ustawić kursor w polu stanowisko, a następnie w oknie Właściwości pola wybrać zakładkę Odnośnik (Rys. 23).

Rys. 23 Zakładka Odnośnik w oknie Właściwości pola

• Teraz własność Pole tekstowe należy zmienić na Pole kombi (Rys. 24). Pole kombi, czyli rozwijana lista wyboru, pozwoli na wybór stanowiska z listy, zamiast jego ręcznego wpisywania.

Rys. 24 Zmiana typu formantu na Pole kombi

• W celu utworzenia listy kombi w polu stanowisko należy podać Źródło wierszy, którym jest tabela Stanowiska (Rys. 25). Należy też zwrócić uwagę na własność Ogranicz do listy, która ustawiona na Tak gwarantuje, że w polu będą mogły występować jedynie wartości istniejące w tabeli Stanowiska (nie będzie można zatem wpisać nieistniejącego stanowiska).

Rys. 25 Ustawianie parametrów pola kombi

• Po przejściu do Widoku arkusza danych tabeli Pracownicy w polu stanowisko dostępna jest lista stanowisk pobierana z tabeli Stanowiska (Rys. 26). Wprowadzenie danych do pola polega na wskazaniu właściwej wartości na liście. Gwarantuje to poprawność oraz znacznie ułatwia i przyspiesza procedurę wprowadzania danych.

Rys. 26 Lista stanowisk

• Zabezpieczać można także pola typu liczbowego i walutowego. Załóżmy, że pożyczka udzielana pracownikom firmy ALFA może zawierać się w przedziale od 0 do 20 000 zł. Można zatem zdefiniować Regułę sprawdzenia poprawności dla tego pola oraz Tekst reguły sprawdzania poprawności, który pojawi się w razie wystąpienia błędu (Rys. 27 i Rys. 28).

Rys. 27 Reguła sprawdzania poprawności dla pola pożyczka

Rys. 28 Działanie reguły sprawdzania poprawności dla pola pożyczka

• Reguła sprawdzenia poprawności może również być wykorzystana przy polach typu Data/Godzina. Na Rys. 29 został przedstawiony sposób sprawdzenia, czy data urodzenia pracownika nie jest młodsza od daty dzisiejszej. Wykorzystana w regule funkcja standardowa Date(), odczytuje wartość daty bieżącej na zegarze systemowym komputera.

Rys. 29 Reguła sprawdzenia poprawności dla pola DataUrP

• Na Rys. 30 przedstawiono działanie reguły sprawdzenia poprawności w wypadku popełnienia błędu przy wprowadzaniu danych do pola dataUrP.

Rys. 30 Działanie reguły sprawdzenia poprawności dla pola DataUrP

W taki właśnie sposób definiuje się zabezpieczenia bazy danych.

1. Zapełnianie bazy KADRY danymi

• Utworzona i zabezpieczona baza danych może być testowana na próbnym zestawie danych. Należy je wprowadzać w widoku arkusza danych poszczególnych tabel bazy danych.

• Żeby przyspieszyć i ułatwić wykonanie ćwiczeń laboratoryjnych należy nadpisać bazę danych Kadry (kompletna struktura bazy danych + dane testowe), z lokalizacji podanej przez Prowadzącego laboratorium, i na niej wykonywać kolejne ćwiczenia.

dr inż. K. Rostek Zakład Systemów Informatycznych

WZ PW POINF - laboratorium

Ćw1 - Tworzenie bazy danych, 2009-09-09 2

Narzędzia menu górnego

Zakładki menu górnego

Pasek narzędziowy Szybki dostęp

Przycisk pakietu Office

Menu podręczne (pod prawym klawiszem myszy)

Zakładki okna roboczego

Okienko nawigacji

Pasek stanu

Okno robocze

Okno podstawowe

Okno właściwości pola

---