Baza danych sklepu muzycznego

Spis Treści1. Opis problemu projektowego2. Schemat bazy danych

2.1. Schemat relacyjny2.2. Opis tabel i kwerend wraz z konstrukcjami tworzącymi je w języku SQL

2.2.1. Tabela Towary2.2.2. Tabela Kategorie2.2.3. Tabela Stanowiska2.2.4. Tabela Pracownicy2.2.5. Tabela Kontrahenci2.2.6. Tabela RuchyTowarów2.2.7. Tabela Spec_RuchyTowarów

3. Opis funkcjonalny systemu3.1. Procesy

3.1.1. Dodanie nowej kategorii3.1.2. Dodanie nowego towaru3.1.3. Dodanie nowego stanowiska3.1.4. Dodanie nowego pracownika3.1.5. Dodanie nowego kontrahenta3.1.6. Zasilenie magazynu sklepu w towar3.1.7. Sprzedaż towaru klientowi

3.2. Kwerendy3.2.1. Kwerenda Spec_RuchyTowarówWartości3.2.2. Kwerenda Spec_RuchyTowarówSum3.2.3. Kwerenda PodsumowanieRuchyTowarów3.2.4. Kwerenda Przychody3.2.5. Kwerenda Rozchody3.2.6. Kwerenda PełnaListaTowarów3.2.7. Kwerenda PracownicyIStanowiska

4. Operatorzy systemu5. Instrukcja instalacji

1. Opis problemu projektowego

Rozwiązanie informatyczne dotyczyć ma sklepu ze sprzętem muzycznym. Każdy sklep zajmuje się pozyskiwaniem i sprzedażą towarów zatem głownie do rejestracji właśnie takich zdarzeń powinien być przeznaczony system informatyczny wspomagający zarządzanie sklepem.

Sama rejestracja zdarzeń sprzedaży lub pozyskiwania towarów wiąże się także ze składowaniem informacji na temat przedmiotu działalności omawianej jednostki, którą w tym przypadku stanowi asortyment sklepu czyli instrumenty muzyczne. Zatem system powinien przechowywać informacje na temat sprzedawanego w sklepie asortymentu i odzwierciedlać stany magazynu sklepu.

Nieodłącznym elementem działalności sklepu muzycznego są pracownicy, będący również operatorami systemu. Projektowany system powinien więc przechowywać informację o nich i odpowiednio je wykorzystywać w procesach obsługi zdarzeń mających miejsce. W tym przypadku, pracownik jest nieodłącznie związany ze sprzedażą i kupnem towarów, ponieważ to on uczestniczy w każdym zdarzeniu kupna i sprzedaży.

Równie ważnym elementem działalności sklepu są dostawcy i odbiorcy asortymentu. Oczywistym jest, że każdy sklep pozyskuje skądś swoje towary i ma klientów którzy je od niego kupują. W tym przypadku jednostki o których mowa będziemy nazywali kontrahentami. W sklepie muzycznym kontrahentami typu dostawcy są hurtowi dystrybutorzy sprzętu muzycznego lub jego producenci, a odbiorcami zwykli klienci przychodzący do sklepu z zamiarem kupienia jakiegoś produktu z branży, jak również w przypadku kiedy sklep jest duży i zajmuje się sprzedażą hurtową inni pośrednicy w procesie dystrybuowania i sprzedaży produktów.

2. Schemat bazy danych

2.1. Schemat Relacyjny

Relacyjny schemat projektowanej bazy danych przedstawia poniższa ilustracja:

Powyższa lustracja przedstawia wyłącznie wszystkie fizycznie istniejące w bazie zestawy danych podzielone na tabele wraz z zaznaczonymi relacjami pomiędzy nimi. Z powyższych zestawów danych utworzone są inne zestawy danych w postaci kwerend, które opisane zostaną w dalszej części dokumentu.

2.2. Opis tabel i kwerend wraz z konstrukcjami tworzącymi je w języku SQL

2.2.1. Tabela Towary

Zadaniem tabeli Towary jest przechowywanie listy całego asortymentu sklepu, który kiedykolwiek znajdował się w sklepie. Jest to tabela do której ze względu na zachowanie integralności danych mogą być wyłącznie dodawane rekordy. Takie rozwiązanie sprzyja generowaniu raportów za minione okresy, w których w asortymencie sklepu znajdowały się towary nie znajdujące się w nim aktualnie. Jednocześnie przy odpowiednim filtrowaniu można z tabeli Towary odczytać towary nie będące już w sprzedaży lub wyłącznie te które znajdują się w niej aktualnie.

Warto nadmienić, że sam fakt obecności towaru w tabeli Towary nie oznacza, ze był on kiedykolwiek fizycznie w sklepie lub, że kiedykolwiek został sprzedany, a jedynie że znajdował się kiedyś w potencjalnej ofercie sklepu co nie znaczy, że został zamówiony u dystrybutora (dostawcy). Tabela Towary spełnia zatem równocześnie rolę słownika towarów, dzięki któremu przy każdym zamówieniu lub sprzedaży danego towaru operator systemu nie musi wpisywać kilkakrotnie nazwy towaru ponieważ może ją pobrać ze słownika.

Specyfikacja tabeli Towary

Lp. Nazwa kolumny Typ danych kolumny Opis

1 IdTowaru integer Klucz główny tabeli2 NazwaTowaru varchar(50) Nazwa towaru

3 IdKategorii integer Klucz obcy odwołujący się do jednej i tylko jednej pozycji tabeli Kategorie zawierającej zbiór kategorii asortymentu w sklepie.

4 CenaDomyślna money Cena jednostkowa danego sprzętu znajdującego się w asortymencie sklepu. Jest to cena ustalana odgórnie i może być ceną sprzedaży towaru, jednak na etapie sprzedaży może zostać zmieniona. Pole to ma charakter informacyjny i pomocniczy w realizacji transakcji między sprzedającym (sklepem) a odbiorcą, ponieważ dzięki niemu sprzedający nie musi pamiętać wszystkich cen sprzedawanych towarów.

5 WSprzedaży BIT Pole określające czy produkt jest aktualnie w ofercie sklepu czy został wycofany. Pole może zawierać jedną z dwóch wartości: 1 (TAK) lub (0) NIE.

6 Opis varchar(500) Pole pomocnicze do dodatkowych

informacji na temat towaru np. o tym, że produkt już niedługo zostanie wycofany.

Relacje w jakich znajduje się tabela Towary :

Tabela Towary znajduje się w bezpośredniej relacji z dwoma innymi tabelami. Pierwszą tabelą z jaką tabela Towary jest w relacji jest tabela Kategorie. Tabela Kategorie jest tabelą nadrzędną względem tabeli Towary, ponieważ znajduje się z tabelą Towary w relacji jeden-do wielu. Oznacza to, że jednemu elementowi (rekordowi) tabeli Kategorie może być przyporządkowane wiele rekordów tabeli Towary.

W praktyce tabela Kategorie dzieli towary znajdujące się w tabeli Towary na kategorie. Mogą to być np. instrumenty strunowe, akcesoria muzyczne, kostki do gitary itd... w zależności od koncepcji i potrzeb konkretnego sklepu używającego projektowanego systemu. Dzięki takiemu rozwiązaniu system staje się znacznie bardziej elastyczny i uniwersalny ponieważ może być z takim samym powodzeniem używany w sklepie muzycznym sprzedającym instrumenty i akcesoria do nich jak i w sklepie z nagraniami muzycznymi.

Kolejną tabelą która jest w relacji z tabelą Towary jest tabela o nazwie Spec_RuchyTowarów. Jest to tabela zawierająca specyfikację transakcji przychodu (dostarczenia towaru do sklepu) lub transakcje rozchodu (sprzedaż sprzętu klientowi). Tabela Towary jest tabelą nadrzędną dla tabeli Spec_RuchyTowarów, co oznacza, że jednemu elementowi tabeli Towary może być przyporządkowanych wiele elementów (pozycji) z tabeli Spec_RuchyTowarów. Tabela Towary znajduje się z tabelą Spec_RuchyTowarów w relacji jeden-do wielu i można powiedzieć, że dzieli lub porządkuje pozycje w tabeli Spec_RuchyTowarów według konkretnych towarów.

Instrukcja SQL tworzącą omawianą tabelę będzie wyglądała następująco:

create table Towary

(IdTowaru integer Primary Key,NazwaTowaru varchar(50),IdKategorii integer,CenaDomyślna money,WSprzedaży BIT,Opis varchar(200),Foreign Key (IdKategorii) References Kategorie (IdKategorii)

);

2.2.2. Tabela Kategorie

Tabela Kategorie przechowuje spis grup do jakich może należeć towar znajdujący się w tabeli Towary.

Specyfikacja tabeli Kategorie

Lp. Nazwa kolumny Typ danych kolumny Opis

1 IdKategorii Integer Klucz główny tabeli2 NazwaKategorii varchar(50) Nazwa kategorii

3 Opis varchar(200) Kolumna pomocnicza przeznaczona do przechowywania dodatkowych informacji na temat Kategorii np. w przypadku kiedy kolumna Nazwa Kategorii jest skrótowa i nie mówi wszystkiego o danej kategorii w opisie użytkownik systemu może umieścić pełne dane.

Relacje w jakich znajduje się tabela Kategorie :

Tabela Kategorie jest w relacji jeden-do wielu z tabelą Towary. Każdy z towarów musi należeć do dokładnie jednej kategorii, która znajduje się w tabeli Kategorie.

Instrukcja SQL tworzącą omawianą tabelę będzie wyglądała następująco:

create table Kategorie( IdKategorii integer primary Key, NazwaKategorii varchar(50), Opis varchar(200));

2.2.3. Tabela Stanowiska

Tabela Stanowiska przechowuje listę stanowisk jakie mogą zajmować pracownicy sklepu.

Specyfikacja tabeli Stanowiska

Lp. Nazwa kolumny Typ danych kolumny Opis

1 IdStanowiska Integer Klucz główny tabeli2 NazwaStanowiska varchar(50) Nazwa kategorii

3 Opis varchar(200) Kolumna pomocnicza przeznaczona do przechowywania dodatkowych informacji na temat Stanowiska np. jakie mają zarobki osoby pracujące na danym stanowisku

Relacje w jakich znajduje się tabela Towary :

Tabela Stanowiska znajduje się w relacji jeden-do wielu z tabelą Pracownicy. Każdy z pracowników musi mieć jedno i tylko jedno stanowisko.

Instrukcja SQL tworzącą omawianą tabelę będzie wyglądała następująco:

create table Stanowiska( IdStanowiska integer primary Key, NazwaStanowiska varchar(50), Opis varchar(200));

2.2.4. Tabela Pracownicy

Tabela Pracownicy przechowuję informacje o wszystkich pracownikach którzy kiedykolwiek pracowali w sklepie. Informacje z tej tabeli wykorzystywane są przy

najważniejszych operacjach wykonywanych za pomocą systemu czyli przy kupowaniu towarów od odbiorców i sprzedawaniu ich klientom sklepu.

Specyfikacja tabeli Pracownicy

Lp. Nazwa kolumny Typ danych kolumny Opis

1 IdPracownika integer Klucz główny tabeli2 Imię varchar(50) Imię pracownika

3 Nazwisko varchar(50) Nazwisko pracownika.

4 Miasto varchar(50) Miasto z adresu zameldowania pracownika.

5 KodPocztowy varchar(6) Kod pocztowy z adresu zameldowania pracownika w formacie XX-XXX.

6 Ulica varchar(50) Ulica z adresu zameldowania pracownika.

7 NrDomu char(10) Nr domu z adresu zameldowania pracownika.

8 NrMieszkania char(10) Nr mieszkania z adresu zameldowania pracownika

9 Pracujący BIT Określa czy pracownik aktualnie pracuje przyjmując wartość 1 (logiczna prawda) lub nie pracuje 0 (logiczny fałsz).

10 IdStanowiska integer Klub obcy wiążący tabelę Pracownicy z tabelą Stanowiska.

11 Uwagi varchar(200) Dodatkowe informacje o pracowniku.

Relacje w jakich znajduje się tabela Pracownicy:

Tabela Pracownicy znajduje się w podobnej relacji co tabela Towary. Z jednej strony jest w relacji jeden-do wielu z nadrzędną tabelą Stanowiska, a z drugiej sama będąc nadrzędną, jest w relacji jeden-do wielu z tabelą RuchyTowarów. Pierwsza relacja odzwierciedla zasadę, że każdy pracownik pracuje na określonym stanowisku.

Druga wiąże się z tym, że zarówno w procesie przyjmowania towaru od dostawcy jak i w przypadku sprzedaży towaru klientowi uczestniczy pracownik, który ponosi odpowiedzialność za dane działanie przyjmując lub sprzedając towar. Zatem każda transakcja jest sygnowana jego identyfikatorem.

Instrukcja SQL tworzącą omawianą tabelę będzie wyglądała następująco:

create table Pracownicy( IdPracownika integer primary Key, Imie varchar(50), Nazwisko varchar(50), Miasto varchar(50), KodPocztowy varchar(6), Ulica varchar(50), NrDomu varchar(10), NrMieszkania varchar(10), Pracujący BIT, IdStanowiska integer, Uwagi varchar(200),

Foreign Key (IdStanowiska) References Stanowiska (IdStanowiska));

2.2.5. Tabela Kontrahenci

Zadaniem tabeli Kontrahenci jest przechowywanie informacji na temat podmiotów dostarczających produkty do sklepu jak również klientów dokonujących regularnie zakupów w sklepie np. firm rozprowadzających sprzęt muzyczny lub

prowadzących działalność w której niezbędny jest asortyment sklepu muzycznego (np. studia nagraniowe).

Specyfikacja tabeli Kontrahenci

Lp. Nazwa kolumny Typ danych kolumny

Opis

1 IdKontrahenta integer Klucz główny tabeli2 NazwaKontrahenta varchar(50) Np. Nazwa firmy, imię i nazwisko osoby

itd.3 Miasto varchar(50) Miasto z adresu kontrahenta.

4 KodPocztowy varchar(6) Kod pocztowy z adresu kontrahenta w formacie XX-XXX.

5 Ulica varchar(50) Ulica z adresu kontrahenta.

6 NrDomu char(10) Nr domu z adresu kontrahenta.

7 NrMieszkania char(10) Nr mieszkania z adresu kontrahenta

8 Typ char(1) Określa czy kontrahent jest odbiorcą (osoba prywatna) przyjmując wartość ‘O’ czy kontrahent jest dostawcą (firma) przyjmując wartość ‘F’.

9 NrTelefonuKom varchar(20) Numer telefonu komórkowego kontrahenta.

10 NrTelefonuDom varchar(20) Numer telefonu stacjonarnego kontrahenta.

11 Opis varchar(200) Dodatkowe informacje o kontrahencie.

Relacje w jakich znajduje się tabela Kontrahenci:

Każdy kontrahent jest nieodłącznie związany z transakcją, w której bierze udział. Jak już ustaliliśmy w sklepie są to głównie transakcje kupna i sprzedaży o których informacje zawiera tabela RuchyTowarów. Stąd relacja jeden-do wielu tabeli Kontrahenci z tabelą RuchyTowarów.

Instrukcja SQL tworzącą omawianą tabelę będzie wyglądała następująco:

create table Kontrahenci( IdKontrahenta integer primary Key, NazwaKontrahenta varchar(50), Miasto varchar(50), KodPocztowy varchar(6), Ulica varchar(50), NrDomu varchar(10), NrMieszkania varchar(10), Typ char(1), NrTelefonuKom varchar(20), NrTelefonuDom varchar(20), Opis varchar(200));

2.2.6. Tabela RuchyTowarów

Tabela RuchyTowarów odpowiada za przechowywanie informacji na temat wszystkich przychodów i rozchodów towarowych w sklepie tzn. procesów kupna towarów od dystrybutora i procesów sprzedaży tych towarów klientom sklepu.

Można sobie zatem wyobrazić, że jest przechowalnią dokumentów kupna i sprzedaży, szufladą w którą wkładamy wszystkie nasze rachunki, i wyciągi gotówki z konta ograniczone jednak tylko do nazwy instytucji od której bierzemy lub której dajemy pieniądze i daty transakcji.

Dopiero w połączeniu z tabelą Spec_RuchyTowarów, z którą tabela RuchyTowarów jest w relacji, otrzymujemy wszystkie informacje o przepływie środków przez sklep.

Specyfikacja tabeli RuchyTowarów

Lp. Nazwa kolumny Typ danych kolumny

Opis

1 IdRuchuTowaru integer Klucz główny tabeli2 IdKontrahenta integer Klucz obcy łączący tabelę w relację z

tabelą Kontrahenci i przechowujący

unikalny identyfikator kontrahenta biorącego udział w transakcji.

3 IdPracownika integer Klucz obcy łączący tabelę w relację z tabelą Pracownicy i przechowujący unikalny identyfikator pracownika biorącego udział w transakcji (sprzedającego sprzęt lub odbierającego sprzęt).

5 Typ char(1) Określa czy mamy do czynienia z dostawą sprzętu (przychodem – wtedy przyjmuje wartość ‘P’) czy ze sprzedażą sprzętu (rozchodem – wtedy przyjmuje wartość ‘R’)

6 DataOperacji date Data rozchodu lub przychodu.

Relacje w jakich znajduje się tabela RuchyTowarów:

Tabela RuchyTowarów zawiera informacje z trzech innych tabel. Ponieważ zarówno z nabyciem towaru jak i ze sprzedażą związani są kontrahenci i pracownicy, każdy dokument określający nabycie lub sprzedaż towaru musi być sygnowany unikalnym numerem kontrahenta i unikalnym numerem pracownika. Stąd właśnie relacje z tabelami zawierającymi dane o pracownikach i kontrahentach.

Z drugiej strony tabela RuchyTowarów jest uogólnieniem tabeli Spec_RuchyTowarów, która specyfikuje poszczególne towary biorące udział w procesie nabycia towaru od kontrahenta typu dostawca – stąd relacja jeden-do wielu tabeli RuchyTowarów z tabelą Spec_RuchyTowarów (jak sama nazwa wskazuje jest to specyfikacja tabeli RuchyTowarów).

Instrukcja SQL tworzącą omawianą tabelę będzie wyglądała następująco:

create table RuchyTowarów( IdRuchuTowaru integer primary Key, IdKontrahenta integer, IdPracownika integer, Typ char(1), DataOperacji date,

Foreign Key (IdKontrahenta) References Kontrahenci (IdKontrahenta),

Foreign Key (IdPracownika) References Pracownicy (IdPracownika));

2.2.7. Tabela Spec_RuchyTowarów

Tabela Spec_RuchyTowarów przechowuje wszystkie transakcje sprzedaży lub kupna (dostawy) towarów. Jest to tabela podrzędna względem tabeli RuchyTowarów i jak sama jej nazwa wskazuje (przedrostek Spec) specyfikuje pozycje tabeli RuchyTowarów.

Specyfikacja tabeli Spec_RuchyTowarów

Lp. Nazwa kolumny Typ danych kolumny

Opis

1 IdSpecRuchówTowarów integer Klucz główny tabeli2 IdRuchuTowaru integer Klucz obcy łączący tabelę w relację z

tabelą RuchyTowarów i przechowujący unikalny identyfikator transakcji dostawy lub sprzedaży towaru.

3 IdTowaru integer Klucz obcy łączący tabelę w relację z tabelą Towary i przechowujący unikalny identyfikator towaru, który jest kupowany lub sprzedawany.

5 Cena money Zawiera informacje za jaką cenę jednostkową został sprzedany lub kupiony dany towar. W mechanizmie

zarządzającym bazą w to miejsce powinna być wklejana domyślnie CenaDomyślna z tabeli Towary.

6 Ilość double Określa jaka jest „porcja” sprzedawanego towaru np. 2 gitary elektryczne.

Relacje w jakich znajduje się tabela Spec_ RuchyTowarów:

Tabela Spec_RuchyTowarów znajduje się w opisanej wcześniej relacji z nadrzędną tabelą RuchyTowarów, jak również w relacji jeden-do wielu z tabelą Towary.

Instrukcja SQL tworzącą omawianą tabelę będzie wyglądała następująco:

create table Spec_RuchyTowarów( IdSpec_RuchówTowarów integer primary Key, IdRuchuTowaru integer, IdTowaru integer, Cena money, Ilość double,

Foreign Key (IdRuchuTowaru) References RuchyTowarów (IdRuchuTowaru),

Foreign Key (IdTowaru) References Towary (IdTowaru));

3. Opis funkcjonalny systemu

3.1. Procesy

3.1.1. Dodanie nowej kategoriiZładowanie kategorii: - load into table kategorie from 'C:\Documents and Settings\Boss\Pulpit\zal bazy\kategorie.txt'3.1.2. Dodanie nowego towaruZaładowanie towarów; - load into table towary from 'C:\Documents and Settings\Boss\Pulpit\zal bazy\towary.txt'3.1.3. Dodanie nowego stanowiska 3.1.4. Dodanie nowego pracownika3.1.5. Dodanie nowego kontrahenta3.1.6. Zasilenie magazynu sklepu w towar3.1.7. Sprzedaż towaru klientowi

3.2. Kwerendy

3.2.1. Kwerenda Spec_RuchyTowarówWartości

Zadaniem powyższej kwerendy jest obliczenie wartości pojedynczych wpisów w tabeli Spec_RuchyTowarów. Zadanie realizowane jest poprzez pomnożenie przez siebie ilości sprzedawanego towaru i jego ceny jednostkowej.

create view Spec_RuchyTowarówWartości as select IdSpec_RuchówTowarów, IdRuchuTowaru, IdTowaru, Cena, Ilość, (Cena * Ilość) as Wartość from Spec_RuchyTowarów;

3.2.2. Kwerenda Spec_RuchyTowarówSum

Zadaniem powyższej kwerendy jest pogrupowanie specyfikacji ruchów towarów względem IdRuchuTowaru, dzięki czemu będzie można ja wykorzystać w innych kwerendach.

create view Spec_RuchyTowarówSum as select IdRuchuTowaru, Sum(Wartość) As Wartość, Sum(Ilość) As Ilośćfrom Spec_RuchyTowarówWartościgroup by IdRuchuTowaruorder by Ilość;

3.2.3. Kwerenda PodsumowanieRuchyTowarów

Kwerenda wyświetla podsumowania wartości i ilości towarów mieszczących się w ramach jednego ruchu towarowego (przychodu lub rozchodu) wraz z nazwami kontrahentów i imionami i nazwiskami pracowników. Kwerenda korzysta z poprzednio utworzonych kwerend.

create view PodsumowanieRuchyTowarów as select NazwaKontrahenta, Imie + ' ' + Nazwisko as Pracownik, RuchyTowarów.Typ, DataOperacji, Wartość, Ilośćfrom Spec_RuchyTowarówSum, RuchyTowarów, Kontrahenci, Pracownicy where RuchyTowarów.IdRuchuTowaru = Spec_RuchyTowarówSum.IdRuchuTowaru and Pracownicy.IdPracownika = RuchyTowarów.IdPracownika and Kontrahenci.IdKontrahenta = RuchyTowarów.IdKontrahenta;

3.2.4. Kwerenda Przychody

Kwerenda prezentuje wszystkie transakcje zasielenia sklep w produkty.

create view Przychody as select * from PodsumowanieRuchyTowarów where Typ = 'P';

3.2.5. Kwerenda Rozchody

Kwerenda prezentuje wszystkie transakcje sprzedaży produktów.

create view Rozchody as select * from PodsumowanieRuchyTowarów where Typ = 'R';

3.2.6. Kwerenda PełnaListaTowarów

Kwerenda pokazująca listę towarów wraz z kategoriami do których towary należą. Dodatkowo kwerenda sortuje tworzona listę względem Nazwy Towarów.

create view PełnaListaTowarów as select NazwaTowaru, NazwaKategorii, CenaDomyślna, WSprzedaży from Towary, Kategorie where Towary.IdKategorii = Kategorie.IdKategoriiorder by NazwaTowaru asc;

3.2.7. Kwerenda PracownicyIStanowiska

Poniższa kwerenda wyświetla posortowaną rosnąco według nazwiska listę pracowników wraz ze stanowiskami które zajmują w sklepie.

create view PracownicyIStanowiska as select Imie, Nazwisko, Pracujący, NazwaStanowiska from Pracownicy, Stanowiska where Pracownicy.IdStanowiska = Stanowiska.IdStanowiska order by Nazwisko asc;