Uniwersytet im. Adama Mickiewicza w Poznaniu
Wydział Matematyki i Informatyki
Projekt bazy danych
E-Ultrasshop
Ziemowit Przebitkowski
Nr indeksu: 362670
Prowadzący zajęcia:
prof. dr hab. Marek Wisła
Poznań, styczeń 2013
1
OPIS BAZY DANYCH
Projektem jest Baza Danych fikcyjnego sklepu internetowego dla
kibiców.
Baza składa się z siedmiu tabel : kategoria, podkategoria,
produkt, klient, zamowienie, zamowiony_produkt.
W tabeli Kategoria znajduje się lista kategorii sprzedawanych
produktów.
W tabeli Podkategoria znajduje się lista podkategorii,
przypisanych do odpowiednich kategorii.
W tabeli Produkt mamy listę sprzedawanych produktów.
W tabeli Klient znajdują się dane dotyczące klientów.
W tabeli Zamowienie znajdują się data zamówienia, status
zamówienia oraz klucz obcy do tabeli Klient.
W tabeli Zamowiony_Produkt znajdują się powiązania między
zamówieniami a produktami w sklepie. Dodatkowo w tabeli tej mamy
kolumnę ilosc_sztuk, która przechowuje ilość zamówionych produktów
danego typu oraz kolumnę cena, która zawiera cene produktu
zapamiętaną w momencie składania zamówienia.
W tabeli Cenniki znajduje się klucz obcy do tabeli Produkt –
ID_Produktu, kolumny Data_pocz i Data_kon zawierające daty
rozpoczęcia i zakończenia się obowiązującej ceny, kolumna
Ilosc_min zawierająca minimalną ilosć_sztuk produktu od której
zależna jest jego cena
Dodatkowo utworzony został widok Wszystkie_zamowienia, będący
połączeniem informacji ze wszystkich tabel
2
RAPORTY
1. Zestawienie ilości sprzedanych produktów z kategorii
"Pirotechnika" wg miejscowości
Miejscowosc Ilosc_piro
1 Warszawa
30
2 Kraków
43
3 Poznań
46
4 Łódź
48
5 Białystok
69
6 Luboń
92
2. Zestawienie wydatków klientów w e-sklepie wg domeny adresu
Nazwisko Email
Wydatki
1 Dworski
dwora@o2.pl
1095.00
2 Gibski
mgibski@o2.pl 1400.00
3 Jerzyński pjerzyn@o2.pl 650.00
4 Szulc
fszulc@o2.pl 280.00
3. Procedura dodania nowego klienta do tabeli Klient
ID
Imię
Nazwisko Kod_Po
cztowy
Adres
Miejscowosc
Telefon
23 Beata Komandor 62-123 Dmowskieg
o 12
Poznań
6682323
31
bkomand@wp.pl
3
4. Procedura dodania nowego zamówienia
Procedura po wczytaniu trzech parametrów (ID klienta, ID oraz
ilość sztuk zamawianego produktu + opcjonalny Status_Zamowienia i
Data) ustawia datę zamówienia na aktualną jeśli nie podano innej,
ustawia cenę zamówienia na wartość z Ilosc_zamowionych_szt
pomnożoną razy cenę produktu z tabeli Produkt, bądź jeśli produkt
jest objęty promocją razy cenę z tabeli Cenniki.
Następnie do tabeli Zamowienie wstawia wartości: Status, Data,
ID_Klient, do tabeli Zamowiony_Produkt wartości: ID_Zamowienie,
ID_Produkt, Ilosc_Szt, Cena
Imie
Nazwisko Miejsc
owosc
Status_
Zamowie
nia
Ilosc_za
mowionyc
h_szt
Cena_zam
owienia
Nazwa_
produk
tu
Opis_p
rodukt
u
Kamil
Rydz
Poznań Oczekuj
e
50
1500
COMET
Czerwo
na
raca
morska
5. Zestawienie 5 najbardziej dochodowych zamówień
Nazwisk
o
Miejscow
osc
Status
_Zamow
ienia
Ilosc_z
amowion
ych_szt
Cena_zam
owienia
Nazwa_pr
oduktu
Opis_p
rodukt
u
Podkat
egoria
Kaczmar
ek
Luboń
Oczeku
je
72
2160
COMET
Czerwo
na
raca
morska
Race
4
OPIS TABEL
Tabela Klient
Kolumna
Opis
Typ
Pusta
wartość
Standardowa
wartość
Powią
zania
ID
Identyfikator
Klienta
INT
NIE
Automatycznie
zwiększany
licznik
PK
Imie
Imię
VARCHAR NIE
Nazwisko
Nazwisko
VARCHAR NIE
Kod_Pocztowy Kod Pocztowy
VARCHAR NIE
Adres
Adres
VARCHAR NIE
Miejscowosc
Miejscowosc
VARCHAR NIE
Telefon
Tefefon
INT
NIE
VARCHAR NIE
Tabela Zamowienie
Kolumna
Opis
Typ
Pusta
wartość
Standardowa
wartość
Powiąz
ania
ID
Identyfikator
realizowanego
zamowienia
INT
NIE
Automatyczni
e zwiększany
licznik
PK
Status
Aktualny status
zamowienia
VARCHAR
NIE
Data
Data momentu
złożenia
zamówienia
DATETIME NIE
ID_Klient
Identyfikator
klienta
składającego
zamowenie
ID
NIE
FK
Klient
.ID
5
Tabela Zamowiony_Produkt
Kolumna
Opis
Typ
Pusta
wartość
Standardow
a wartość
Powiązania
ID
Identyfikator
powiązania
INT
NIE
Automatycz
nie
zwiększany
licznik
PK
ID_Zamowienie Identyfikator
zamowienia
INT
NIE
FK
Zamowienie
.ID
ID_Produkt
Identyfikator
produktu
INT
NIE
FK
Produkt.ID
Ilosc_Sztuk
Ilosc sztuk
zamowionego
produktu
INT
NIE
Cena
Cena
zamowienia
MONEY
NIE
Tabela Cenniki
Kolumna
Opis
Typ
Pusta
wartość
Standardow
a wartość
Powiązania
ID_Produktu
Identyfikator
produktu
INT
NIE
Automatycz
nie
zwiększany
licznik
PK
Data_pocz
Początek
obowiązywania
danej ceny
INT
TAK
Data_kon
Koniec
obowiązywania
danej ceny
INT
TAK
Ilosc_min
Minimalna
ilosc sztuk
dla danej
ceny
INT
TAK
Cena
Cena za
sztuke
MONEY
TAK
6
Tabela Produkt
Kolumna
Opis
Typ
Pusta
wartość
Standardow
a wartość
Powiązania
ID
Identyfikator
produktu
INT
NIE
Automatycz
nie
zwiększany
licznik
PK
Nazwa
Nazwa
produktu
VARCHAR NIE
Opis
Opis produktu TEXT
NIE
Cena
Cena produktu MONEY
NIE
ID_Kategoria
Identyfikator
kategorii
INT
NIE
FK
Kategoria.
ID
ID_Podkategor
ia
Identyfikator
podkategorii
INT
NIE
FK
Podkategor
ia.ID
Tabela Kategoria
Kolumna
Opis
Typ
Pusta
wartość
Standardow
a wartość
Powiązania
ID
Identyfikator
kategorii
INT
NIE
Automatycz
nie
zwiększany
licznik
PK
Nazwa
Nazwa
kategorii
VARCHAR NIE
Tabela Podkategoria
Kolumna
Opis
Typ
Pusta
wartość
Standardow
a wartość
Powiązania
ID
Identyfikator
podkategorii
INT
NIE
Automatycz
nie
zwiększany
licznik
PK
ID_Kategoria
Identyfikator
kategorii
INT
NIE
FK
Kategoria.
ID
Nazwa
Nazwa
podkategorii
VARCHAR NIE
7
DIAGRAM ERD
Klient
PK
ID (INT)
Imię (VARCHAR)
Nazwisko (VARCHAR)
Kod_Pocztowy
(VARCHAR)
Adres (VARCHAR)
Miejscowosc
(VARCHAR)
Telefon (INT)
Email (VARCHAR)
Zamowienie
PK
ID (INT)
Status (VARCHAR)
Data (DATETIME)
FK
ID_Klient (INT)
Zamowiony_Produkt
PK
ID (INT)
FK
ID_Zamowienie (INT)
FK
ID_Produkt (INT)
Ilosc_Sztuk (INT)
Cena (MONEY)
Cenniki
FK
ID_Produktu (INT)
Data_pocz(DATETIME)
Data_kon (DATETIME)
Ilosc_min (INT)
Cena (MONEY)
Produkt
PK
ID (INT)
Nazwa (VARCHAR)
Opis (TEXT)
Cena (MONEY)
FK
ID_Kategoria (INT)
FK
ID_Podkategoria
(INT)
Podkategoria
PK
ID (INT)
FK
ID_Kategoria (INT)
Nazwa (VARCHAR)
Kategoria
PK
ID (INT)
Nazwa (VARCHAR)
8
n
1
1
n
n
n
1
1
n
1
1
n
n
Dane Demonstracyjne – Tabele
Tabela Klient
ID
Imię
Nazwisko Kod_Po
cztowy
Adres
Miejscowosc
Telefon
1 Piotr
Kowalski
62-010
Kościuszki 32 Pobiedziska
669819811 pkowalkski@wp.pl
2 Bartosz Kaczmarek 62-012
Lubelska 13
Luboń
618812711 kaczmarek@wp.pl
3 Kamil
Nowak
41-234
Kwiecista 43 Poznań
724111543 knowak@gmail.co
m
4 Piotr
Jerzyński
35-123
Górnicza 16
Poznań
693420541 pjerzyn@o2.pl
5 Daria
Polakowska 53-020
Piastowska 10 Łódź
781112211 dariapol@wp.pl
6 Donald Kaczor
21-900
Różana 3
Warszawa
501211313 dkaczor@gmail.co
m
7 Filip
Szulc
62-010
Szucha 1
Kraków
721151735 fszulc@o2.pl
8 Mateus
z
Gibski
21-938
Kościuszki 12 Białystok
697564392 mgibski@o2.pl
9 Kamil
Rydz
62-015
Piątkowska
11
Poznań
508267331 krydz@gmail.com
10 Bartosz Filar
Nowy Świat 3 Warszawa
511748322 bfil@wp.pl
11 Piotr
Olejniczak
Łąkowa 445
Łódź
693455544 polej@wp.pl
12 Sławek Dworski
Robocza 543 Kraków
723680453 dwora@o2.pl
13 Michał Kular
Strusia 32
Białystok
669722112 mkular@gmail.co
m
Tabela Zamowienie
ID
Data
Status
ID_Klient
1
2012-11-04
Zrealizowane
4
2
2011-03-21
Zrealizowane
2
3
2012-11-29
Oczekuje
5
4
2012-10-13
Zrealizowane
2
5
2012-11-30
Oczekuje
5
6
2012-03-27
Zrealizowane
6
7
2012-10-28
Oczekuje
3
8
2012-11-11
Oczekuje
1
9
2012-09-06
Zrealizowane
4
10
2011-12-09
Zrealizowane
7
11
2011-08-23
Zrealizowane
13
12
2011-11-09
Zrealizowane
8
13
2012-04-11
Zrealizowane
11
9
ID
Data
Status
ID_Klient
14
2012-11-04
Oczekuje
9
15
2012-11-30
Oczekuje
12
16
2012-03-30
Zrealizowane
10
17
2012-12-01
Oczekuje
2
18
2011-07-27
Zrealizowane
12
19
2011-11-29
Zrealizowane
9
20
2012-01-09
Zrealizowane
4
21
2012-10-30
Oczekuje
7
22
2011-05-12
Zrealizowane
11
Tabela Zamowiony_Produkt
ID ID_Zamowie
nie
ID_Produ
kt
Ilosc_Sztu
k
Cena=Ilosc_sztuk
*Cennik_cena
1
1
5
22
330,00 zł
2
3
1
60
900,00 zł
3
2
4
4
160
4
4
6
20
400,00 zł
5
5
14
25
375,00 zł
6
9
12
1
20.00
7
6
3
33
825,00 zł
8
8
17
1
25.00
9
7
3
3
90
10
10
7
5
200
11
14
2
12
180
12
13
4
41
1640
13
22
6
23
460
14
11
6
29
580
15
15
12
11
220
16
12
5
40
1400
17
20
11
20
300
18
21
7
2
80
19
16
6
30
600
20
19
8
3
36
21
17
5
72
2160
22
18
5
25
875
10
Tabela Kategoria
ID
Nazwa
1
Choreografie
2
Pirotechnika
3
Odziez
Tabela Podkategoria
ID
ID_Kategoria
Nazwa
1
1
Farby w Spray'u
2
1
Flagi na kiju
3
2
Race
4
2
Świece Dymne
5
2
Stroboskopy
6
2
Petardy
7
2
Wulkany
8
3
T-shirty
9
3
Szale
10
3
Czapki Zimowe
11
3
Chusty
Tabela Cenniki
ID_Produktu
Data_pocz
Data_kon
Ilosc_min
Cena
1
30.12.2012
31.01.2013
50
15,00 zł
2
NULL
NULL
NULL
NULL
3
1.01.2012
1.04.2012
20
25,00 zł
4
NULL
NULL
NULL
NULL
5
30.12.2011
31.12.2012
20
30,00 zł
6
NULL
NULL
NULL
NULL
7
NULL
NULL
NULL
NULL
8
NULL
NULL
NULL
NULL
9
NULL
NULL
NULL
NULL
10
NULL
NULL
NULL
NULL
11
NULL
NULL
NULL
NULL
11
ID_Produktu
Data_pocz
Data_kon
Ilosc_min
Cena
12
NULL
NULL
NULL
NULL
13
NULL
NULL
NULL
NULL
14
2012-09-30
2013-01-31
20
15,00 zł
15
NULL
NULL
NULL
NULL
16
NULL
NULL
NULL
NULL
17
NULL
NULL
NULL
NULL
18
NULL
NULL
NULL
NULL
19
NULL
NULL
NULL
NULL
20
NULL
NULL
NULL
NULL
21
NULL
NULL
NULL
NULL
22
NULL
NULL
NULL
NULL
Tabela Produkt
ID
Nazwa
Opis
Cena
ID_Kategoria ID_Podkategor
ia
1
MONTANA Czarna farba w
spray'u, 600 ml
20.00
1
1
2
MONTANA
Czarna farba w
spray'u, 400 ml
15.00
1
1
3
PANED
Flaga na kiju w
kratę, 2m x 1m
30.00
1
2
4
PATTERN
Flaga na kiju, z
wybranym
wzorem,
2m x 1m
40.00
1
2
5
COMET
Czerwona raca
morska
35.00
2
3
6
MARINE
Czerwona flara
morska
20.00
2
3
7
SMOKE
Czarna,
wojskowa
świeca dymna,
250g
40.00
2
4
8
SMBOMB
Ręczne świece
dymne w
wybranych
kolorach, 80 g
12.00
2
4
9
STROBOMAS
TER
Bialy
stroboskop,
5.00
2
5
12
ID
Nazwa
Opis
Cena
ID_Kategoria ID_Podkategor
ia
100 g
10
STROBO
Biały
stroboskop, 30
g
2.50
2
5
11
ACHTUNG
Petarda
hukowa
„Achtung”
15.00
2
6
12
FP3
Petarda
hukowa „FP3”
20.00
2
6
13
BVOLCANO Duży wulkan –
1 kg
40.00
2
7
14
SVOLCANO Mały wulkan –
400 g
20.00
2
7
15
T-SHIRT
LECH
Koszulka
Lecha Poznań
35.00
3
8
16
T-SHIRT
ARKA
Koszulka Arki
Gdynia
35.00
3
8
17
SZAL LECH
Szal tkany
Lecha Poznań
25.00
3
9
18
SZAL
CRACOVIA
Szal tkany
Cracovii
Kraków
25.00
3
9
19
CZAPKA
KSZO
Czapka
zimowa KSZO
Ostrowiec
20.00
3
10
20
CZAPKA
ARKA
Czapka
zimowa Arki
Gdynia
20.00
3
10
21
CHUSTA
NSNP
Bandana
„Nigdy Się Nie
Poddawaj”
15.00
3
11
22
CHUSTA KKS
Bandana
„Kibolski Klub
Sportowy”
15.00
3
11
13