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

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

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 e-mail

	Nazwisko	Email	Wydatki
1	Dworski	[e-mail]	1095.00
2	Gibski	[e-mail]	1400.00
3	Jerzyński	[e-mail]	650.00
4	Szulc	[e-mail]	280.00

3. Procedura dodania nowego klienta do tabeli Klient

ID	Imię	Nazwisko	Kod_Pocztowy	Adres	Miejscowosc	Telefon	Email
23	Beata	Komandor	62-123	Dmowskiego 12	Poznań	668232331	[e-mail]

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	Miejscowosc	Status_Zamowienia	Ilosc_zamowionych_szt	Cena_zamowienia	Nazwa_produktu	Opis_produktu
Kamil	Rydz	Poznań	Oczekuje	50	1500	COMET	Czerwona raca morska

5. Zestawienie 5 najbardziej dochodowych zamówień

Nazwisko	Miejscowosc	Status_Zamowienia	Ilosc_zamowionych_szt	Cena_zamowienia	Nazwa_produktu	Opis_produktu	Podkategoria
Kaczmarek	Luboń	Oczekuje	72	2160	COMET	Czerwona raca morska	Race

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
Email	E-mail	VARCHAR	NIE

Tabela Zamowienie

Kolumna	Opis	Typ	Pusta wartość	Standardowa wartość	Powiązania
ID	Identyfikator realizowanego zamowienia	INT	NIE	Automatycznie 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

Tabela Zamowiony_Produkt

Kolumna	Opis	Typ	Pusta wartość	Standardowa wartość	Powiązania
ID	Identyfikator powiązania	INT	NIE	Automatycznie 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ść	Standardowa wartość	Powiązania
ID_Produktu	Identyfikator produktu	INT	NIE	Automatycznie 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

Tabela Produkt

Kolumna	Opis	Typ	Pusta wartość	Standardowa wartość	Powiązania
ID	Identyfikator produktu	INT	NIE	Automatycznie 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_Podkategoria	Identyfikator podkategorii	INT	NIE		FK Podkategoria.ID

Tabela Kategoria

Kolumna	Opis	Typ	Pusta wartość	Standardowa wartość	Powiązania
ID	Identyfikator kategorii	INT	NIE	Automatycznie zwiększany licznik	PK
Nazwa	Nazwa kategorii	VARCHAR	NIE

Tabela Podkategoria

Kolumna	Opis	Typ	Pusta wartość	Standardowa wartość	Powiązania
ID	Identyfikator podkategorii	INT	NIE	Automatycznie zwiększany licznik	PK
ID_Kategoria	Identyfikator kategorii	INT	NIE		FK Kategoria.ID
Nazwa	Nazwa podkategorii	VARCHAR	NIE

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)

Dane Demonstracyjne – Tabele

Tabela Klient

ID	Imię	Nazwisko	Kod_Pocztowy	Adres	Miejscowosc	Telefon	Email
1	Piotr	Kowalski	62-010	Kościuszki 32	Pobiedziska	669819811	[e-mail]
2	Bartosz	Kaczmarek	62-012	Lubelska 13	Luboń	618812711	[e-mail]
3	Kamil	Nowak	41-234	Kwiecista 43	Poznań	724111543	[e-mail]
4	Piotr	Jerzyński	35-123	Górnicza 16	Poznań	693420541	[e-mail]
5	Daria	Polakowska	53-020	Piastowska 10	Łódź	781112211	[e-mail]
6	Donald	Kaczor	21-900	Różana 3	Warszawa	501211313	[e-mail]
7	Filip	Szulc	62-010	Szucha 1	Kraków	721151735	[e-mail]
8	Mateusz	Gibski	21-938	Kościuszki 12	Białystok	697564392	[e-mail]
9	Kamil	Rydz	62-015	Piątkowska 11	Poznań	508267331	[e-mail]
10	Bartosz	Filar		Nowy Świat 3	Warszawa	511748322	[e-mail]
11	Piotr	Olejniczak		Łąkowa 445	Łódź	693455544	[e-mail]
12	Sławek	Dworski		Robocza 543	Kraków	723680453	[e-mail]
13	Michał	Kular		Strusia 32	Białystok	669722112	[e-mail]

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
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_Zamowienie	ID_Produkt	Ilosc_Sztuk	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

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
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_Podkategoria
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	STROBOMASTER	Bialy stroboskop, 100 g	5.00	2	5
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