Różnice między wybraną wersją a wersją aktualną.
| Both sides previous revision Poprzednia wersja Nowa wersja | Poprzednia wersja | ||
|
pl:dydaktyka:sbd:2012:projekty:przeglady2:logiczny [2012/11/24 14:26] sbd12 |
pl:dydaktyka:sbd:2012:projekty:przeglady2:logiczny [2019/06/27 15:50] (aktualna) |
||
|---|---|---|---|
| Linia 1: | Linia 1: | ||
| =======PROJEKT LOGICZNY======= | =======PROJEKT LOGICZNY======= | ||
| + | |||
| + | ===== 1. Projektowanie tabel, kluczy, kluczy obcych, powiązań między tabelami, indeksów, etc. w oparciu o zdefiniowany diagram konceptualny ERD: ===== | ||
| + | |||
| + | Zdefiniowano typy wyliczeniowe : | ||
| + | *typ_nadwozia(np. sedan) | ||
| + | *skrzynia_biegow(automatyczna/sekwencyjna/manualna) | ||
| + | *rodzaj_silnika(benzynowy/diesel) | ||
| + | |||
| + | Klucze obce są oznaczone czerwonym rombem na diagramie ERD w punkcie 2. Ich nazwa wskazuje do której tabeli się odnoszą. | ||
| + | |||
| + | Indeksy utworzyliśmy na wszystkich kluczach obcych, ze względu na to że są często używane w zapytaniach wybierających wszystkie dane z tabeli. Dodatkowo indeksowana jest kolumna login w encji Użytkownik, ze względu na wyszukiwanie po loginie przy logowaniu. Wszystkie indeksy są wypisane na diagramie ERD, u dołu odpowiednich encji. | ||
| + | |||
| + | |||
| + | ===== 2. Diagram ERD w notacji Barkera/Oracle===== | ||
| + | |||
| + | |||
| + | {{:pl:dydaktyka:sbd:2012:projekty:przeglady2:erdbarker2.png?800}} | ||
| + | |||
| + | ===== 3. Tablica krzyżowa ===== | ||
| + | |||
| + | |||
| + | {{:pl:dydaktyka:sbd:2012:projekty:przeglady2:crosstable.jpg?800}} | ||
| + | *T1 - czynnosc_serwisowa | ||
| + | *T2 - marka | ||
| + | *T3 - model | ||
| + | *T4 - plik | ||
| + | *T5 - rodzaj_czynnosci_serwisowej | ||
| + | *T6 - samochod | ||
| + | *T7 - uzytkownik | ||
| + | *T8 - wersja_samochodu | ||
| + | *T9 - zdarzenie | ||
| ===== 4. Projekt bazy w języku SQL===== | ===== 4. Projekt bazy w języku SQL===== | ||
| + | <code sql> | ||
| - | CREATE TYPE typ_nadwozia_enum AS ENUM ('hatchback', 'sedan', 'kombi'); | + | CREATE TYPE typ_nadwozia_enum AS ENUM ('hatchback', 'sedan', 'kombi');\\ |
| - | CREATE TYPE skrzynia_biegow_enum AS ENUM('manualna','sekwencyjna','automatyczna'); | + | CREATE TYPE skrzynia_biegow_enum AS ENUM('manualna','sekwencyjna','automatyczna');\\ |
| - | CREATE TYPE rodzaj_silnika_enum AS ENUM('spalinowy','diesel'); | + | CREATE TYPE rodzaj_silnika_enum AS ENUM('spalinowy','diesel');\\ |
| CREATE SEQUENCE id_uzytkownika_seq | CREATE SEQUENCE id_uzytkownika_seq | ||
| Linia 21: | Linia 53: | ||
| ); | ); | ||
| - | CREATE TABLE MODEL( | + | CREATE INDEX UZYTKOWNIK_login ON UZYTKOWNIK(login); |
| + | |||
| + | CREATE TABLE MARKA( | ||
| id INTEGER PRIMARY KEY, | id INTEGER PRIMARY KEY, | ||
| nazwa VARCHAR(20) NOT NULL | nazwa VARCHAR(20) NOT NULL | ||
| ); | ); | ||
| - | CREATE TABLE MARKA( | + | CREATE TABLE MODEL( |
| id INTEGER PRIMARY KEY, | id INTEGER PRIMARY KEY, | ||
| + | id_marki INTEGER NOT NULL REFERENCES MARKA(id), | ||
| nazwa VARCHAR(20) NOT NULL | nazwa VARCHAR(20) NOT NULL | ||
| ); | ); | ||
| - | CREATE SEQUENCE id_modelu_samochodu_seq | + | CREATE INDEX MODEL_FKmarka ON MODEL(id_marki); |
| + | |||
| + | CREATE SEQUENCE id_wersji_samochodu_seq | ||
| START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; | START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; | ||
| - | CREATE TABLE MODEL_SAMOCHODU( | + | CREATE TABLE WERSJA_SAMOCHODU( |
| id INTEGER DEFAULT | id INTEGER DEFAULT | ||
| - | nextval('id_modelu_samochodu_seq') PRIMARY KEY, | + | nextval('id_wersji_samochodu_seq') PRIMARY KEY, |
| - | id_marki INTEGER NOT NULL REFERENCES MARKA(id), | + | |
| id_modelu INTEGER NOT NULL REFERENCES MODEL(id), | id_modelu INTEGER NOT NULL REFERENCES MODEL(id), | ||
| skrzynia_biegow skrzynia_biegow_enum, | skrzynia_biegow skrzynia_biegow_enum, | ||
| wersja VARCHAR(20) NOT NULL, | wersja VARCHAR(20) NOT NULL, | ||
| start_produkcji SMALLINT NOT NULL, | start_produkcji SMALLINT NOT NULL, | ||
| - | koniec_produkcji SMALLINT NOT NULL, | + | koniec_produkcji SMALLINT, |
| rodzaj_silnika rodzaj_silnika_enum NOT NULL, | rodzaj_silnika rodzaj_silnika_enum NOT NULL, | ||
| pojemnosc SMALLINT NOT NULL, | pojemnosc SMALLINT NOT NULL, | ||
| CHECK(pojemnosc>0), | CHECK(pojemnosc>0), | ||
| - | CHECK(start_produkcji>0), | + | CHECK(start_produkcji>1900), |
| - | CHECK(koniec_produkcji>0), | + | CHECK(koniec_produkcji>1900), |
| + | CHECK(start_produkcji<2013), | ||
| + | CHECK(koniec_produkcji<2013), | ||
| CHECK(koniec_produkcji>start_produkcji) | CHECK(koniec_produkcji>start_produkcji) | ||
| ); | ); | ||
| + | |||
| + | CREATE INDEX WERSJA_SAMOCHODU_FKmodel ON WERSJA_SAMOCHODU(id_modelu); | ||
| CREATE SEQUENCE id_samochodu_seq | CREATE SEQUENCE id_samochodu_seq | ||
| Linia 58: | Linia 98: | ||
| nextval('id_samochodu_seq') PRIMARY KEY, | nextval('id_samochodu_seq') PRIMARY KEY, | ||
| id_uzytkownika INTEGER NOT NULL REFERENCES UZYTKOWNIK(id), | id_uzytkownika INTEGER NOT NULL REFERENCES UZYTKOWNIK(id), | ||
| - | id_modelu_samochodu INTEGER NOT NULL REFERENCES MODEL_SAMOCHODU(id), | + | id_wersji_samochodu INTEGER NOT NULL REFERENCES WERSJA_SAMOCHODU(id), |
| typ_nadwozia typ_nadwozia_enum, | typ_nadwozia typ_nadwozia_enum, | ||
| VIN CHAR(20) NOT NULL, | VIN CHAR(20) NOT NULL, | ||
| Linia 69: | Linia 109: | ||
| ); | ); | ||
| + | CREATE INDEX SAMOCHOD_FKuzytkownik ON SAMOCHOD(id_uzytkownika); | ||
| + | CREATE INDEX SAMOCHOD_FKwersja_samochodu ON SAMOCHOD(id_wersji_samochodu); | ||
| Linia 78: | Linia 119: | ||
| id INTEGER DEFAULT | id INTEGER DEFAULT | ||
| nextval('id_rodzaju_czynnosci_serwisowej_seq') PRIMARY KEY, | nextval('id_rodzaju_czynnosci_serwisowej_seq') PRIMARY KEY, | ||
| - | opis VARCHAR(200) NOT NULL | + | nazwa VARCHAR(20) NOT NULL, |
| + | opis VARCHAR(200) | ||
| ); | ); | ||
| Linia 87: | Linia 129: | ||
| id INTEGER DEFAULT | id INTEGER DEFAULT | ||
| nextval('id_czynnosci_serwisowej_seq') PRIMARY KEY, | nextval('id_czynnosci_serwisowej_seq') PRIMARY KEY, | ||
| - | id_modelu_samochodu INTEGER NOT NULL REFERENCES MODEL_SAMOCHODU(id), | + | id_wersji_samochodu INTEGER NOT NULL REFERENCES WERSJA_SAMOCHODU(id), |
| id_rodzaju_czynnosci_serwisowej INTEGER NOT NULL REFERENCES RODZAJ_CZYNNOSCI_SERWISOWEJ(id), | id_rodzaju_czynnosci_serwisowej INTEGER NOT NULL REFERENCES RODZAJ_CZYNNOSCI_SERWISOWEJ(id), | ||
| opis VARCHAR(200) NOT NULL, | opis VARCHAR(200) NOT NULL, | ||
| Linia 95: | Linia 137: | ||
| CHECK(przebieg>0) | CHECK(przebieg>0) | ||
| ); | ); | ||
| + | |||
| + | CREATE INDEX CZYNNOSC_SERWISOWA_FKwersja_samochodu ON CZYNNOSC_SERWISOWA(id_wersji_samochodu); | ||
| + | CREATE INDEX CZYNNOSC_SERWISOWA_FKrcs ON CZYNNOSC_SERWISOWA(id_rodzaju_czynnosci_serwisowej); | ||
| CREATE SEQUENCE id_zdarzenia_seq | CREATE SEQUENCE id_zdarzenia_seq | ||
| Linia 109: | Linia 154: | ||
| komentarz VARCHAR(100) NOT NULL | komentarz VARCHAR(100) NOT NULL | ||
| ); | ); | ||
| + | |||
| + | CREATE INDEX ZDARZENIE_FKsamochod ON ZDARZENIE(id_samochodu); | ||
| + | CREATE INDEX ZDARZENIE_FKczynnosc_serwisowa ON ZDARZENIE(id_czynnosci_serwisowej); | ||
| CREATE SEQUENCE id_pliku_seq | CREATE SEQUENCE id_pliku_seq | ||
| Linia 120: | Linia 168: | ||
| obiekt BYTEA NOT NULL | obiekt BYTEA NOT NULL | ||
| ); | ); | ||
| + | |||
| + | CREATE INDEX PLIK_FKzdarzenie ON PLIK(id_zdarzenia); | ||
| + | |||
| + | </code> | ||
| + | |||
| ===== 5. Słowniki danych ===== | ===== 5. Słowniki danych ===== | ||
| Linia 128: | Linia 181: | ||
| *opis = *szczegóły opis czynności serwisowej * * łańcuch znaków długości 0-200* | *opis = *szczegóły opis czynności serwisowej * * łańcuch znaków długości 0-200* | ||
| - | ===skrzynia_biegow=== | ||
| - | *id | ||
| - | *id_rodzaju_skrzyni(klucz obcy do słownika rodzaj_skrzyni) | ||
| - | *ilosc_biegow - dodatnia liczba(np. 5) | ||
| - | |||
| - | ===model_samochodu=== | ||
| - | *id | ||
| - | *id_marki(klucz obcy do słownika marka) | ||
| - | *id_modelu(klucz obcy do słownika model) | ||
| - | *id_skrzyni_biegow(klucz obcy do słownika skrzynia_biegow) | ||
| - | *wersja | ||
| - | *start_produkcji | ||
| - | *koniec_produkcji | ||
| - | *rodzaj_silnika | ||
| - | *pojemnosc | ||
| ===model=== | ===model=== | ||
| *id | *id | ||
| - | *nazwa - łańcuch znaków(np. Corsa) | + | *id_marki = *klucz obcy do słownika marka * * liczba całkowita* |
| + | *nazwa = *nazwa modelu samochodu * * łańcuch znaków długości 1-20* | ||
| - | ===marka=== | + | ===marka=== |
| *id | *id | ||
| - | *nazwa - łańcuch znaków(np. Opel) | + | *nazwa = *nazwa marki samochodu * * łańcuch znaków długości 1-20* |
| - | ===typ_nadwozia=== | + | ===model_samochodu=== |
| *id | *id | ||
| - | *nazwa - łańcuch znaków(np. sedan) | + | *id_modelu = *klucz obcy do słownika model * * liczba całkowita* |
| + | *skrzynia_biegow = *rodzaj skrzyni biegów * *typ wyliczeniowy* | ||
| + | *wersja = *wersja samochodu * *łańcuch znaków długości 1-20* | ||
| + | *start_produkcji = *rok w którym rozpoczęto produkcję samochodu * * liczba całkowita w zakresie 1901-2012* | ||
| + | *koniec_produkcji = *rok w którym zakończono produkcję samochodu * *liczba całkowita w zakresie 1901-2012* | ||
| + | *rodzaj_silnika = * rodzaj silnika * * typ wyliczeniowy* | ||
| + | *pojemnosc = *pojemność silnika * * dodatnia liczba całkowita* | ||
| + | |||
| + | |||
| + | ===== 6. Analiza zależności funkcyjnych i normalizacja tabel (dekompozycja do 3NF, BCNF, 4NF, 5NF) ===== | ||
| + | |||
| + | 1NF - ponieważ wszystkie atrybuty wszystkich tabel są elementarne, więc baza spełnia pierwszą postać normalną | ||
| + | |||
| + | 2NF - każdy atrybut niekluczowy jest w pełni funkcyjnie zależny od klucza głównego dla każdej tabeli, co definiuje drugą postać normalną | ||
| + | |||
| + | 3NF - każdy atrybut niekluczowy jest bezpośrednio zależny od klucza głównego dla każdej tabeli, dzięki czemu baza jest w trzeciej postaci normalnej | ||
| - | ===rodzaj_zdarzenia=== | ||
| - | *id | ||
| - | *nazwa - łańcuch znaków(np | ||
| - | ===Skrzynia biegów=== | ||
| - | Słownik składający się z | ||
| ===== 8. Projektowanie operacji na danych ===== | ===== 8. Projektowanie operacji na danych ===== | ||
| Linia 172: | Linia 222: | ||
| SELECT PASSWORD FROM UZYTKOWNIK WHERE login = 'wilk'; | SELECT PASSWORD FROM UZYTKOWNIK WHERE login = 'wilk'; | ||
| - | |||
| - | ===Wyświetlenie wszystkich samochodów=== | ||
| - | |||
| - | SELECT id, id_użytkownika, id_modelu_samochodu, id_typu_nadwozia, rok_produkcji, VIN, kolor, nr_rejestracyjny, liczba_drzwi, moc FROM UZYTKOWNIK | ||
| ===Dodawanie samochodu=== | ===Dodawanie samochodu=== | ||
| - | INSERT INTO SAMOCHOD(id, id_użytkownika, id_modelu_samochodu, id_typu_nadwozia, rok_produkcji, VIN, kolor, nr_rejestracyjny, liczba_drzwi, moc) VALUES(DEFAULT, 5, 4, 2, 2003, 'W0L0XCF0814000002', 'czarny', RNI-20MC, 5, 130); | + | INSERT INTO SAMOCHOD(id, id_użytkownika, id_wersji_samochodu, typ_nadwozia, rok_produkcji, VIN, kolor, nr_rejestracyjny, liczba_drzwi, moc) VALUES(DEFAULT, 5, 4, 'hatchback', 2003, 'W0L0XCF0814000002', 'czarny', RNI-20MC, 5, 130); |
| ===Edycja samochodu=== | ===Edycja samochodu=== | ||
| - | UPDATE SAMOCHOD SET id użytkownika = 3, id_modelu_samochodu = 2, id_typu_nadwozia = 1, rok produkcji = 2000, VIN = 'W0L0TGF6915216555', kolor = 'czerwony', nr rejestracyjny = 'KR 34AM', liczba drzwi = 3, moc = 200 WHERE id = 5; | + | UPDATE SAMOCHOD SET id użytkownika = 3, id_modelu_samochodu = 2, typ_nadwozia = 'sedan', rok produkcji = 2000, VIN = 'W0L0TGF6915216555', kolor = 'czerwony', nr rejestracyjny = 'KR 34AM', liczba drzwi = 3, moc = 200 WHERE id = 5; |
| ===Usuwanie samochodu=== | ===Usuwanie samochodu=== | ||
| DELETE FROM SAMOCHOD WHERE id = 3; | DELETE FROM SAMOCHOD WHERE id = 3; | ||
| - | |||
| - | ===Wyświetlenie wszystkich czynności serwisowych=== | ||
| - | |||
| - | SELECT id, id_modelu_samochodu, id_samochodu, id_rodzaju_czynnosci_serwisowej, opis, okres, przebieg, komentarz FROM CZYNNOSC_SERWISOWA | ||
| ===Dodawanie czynności serwisowej=== | ===Dodawanie czynności serwisowej=== | ||
| - | INSERT INTO CZYNNOSC_SERWISOWA(id, id_modelu_samochodu, id_samochodu, id_rodzaju_czynnosci_serwisowej, opis, okres, przebieg, komentarz) VALUES(DEFAULT, 2, null, 1, 'Wymiana klocków hamulcowych', ######, 200000, 'Zalecane już przy 180000km'); | + | INSERT INTO CZYNNOSC_SERWISOWA(id, id_wersji_samochodu, id_rodzaju_czynnosci_serwisowej, opis, okres, przebieg, komentarz) VALUES(DEFAULT, 2, 1, 'Wymiana klocków hamulcowych', null, 200000, 'Zalecane już przy 180000km'); |
| ===Edycja czynności serwisowej=== | ===Edycja czynności serwisowej=== | ||
| - | UPDATE CZYNNOSC_SERWISOWA SET id_modelu_samochodu = 3, id_samochodu = 2 , id_rodzaju_czynnosci_serwisowej = 2, opis = 'Przegląd', okres = #####, przebieg = null, komentarz = null WHERE id = 3; | + | UPDATE CZYNNOSC_SERWISOWA SET id_wersji_samochodu = 3 , id_rodzaju_czynnosci_serwisowej = 2, opis = 'Przegląd', okres = 30, przebieg = null, komentarz = null WHERE id = 3; |
| ===Usuwanie czynności serwisowej=== | ===Usuwanie czynności serwisowej=== | ||
| DELETE FROM CZYNNOSC_SERWISOWA WHERE id = 4; | DELETE FROM CZYNNOSC_SERWISOWA WHERE id = 4; | ||
| - | |||
| - | ===Wyświetlenie wszystkich zdarzeń=== | ||
| - | |||
| - | SELECT id, id_rodzaju_zdarzenia, id_samochodu, data, czas, przebieg, komentarz FROM ZDARZENIE | ||
| ===Dodawanie zdarzenia=== | ===Dodawanie zdarzenia=== | ||
| - | INSERT INTO ZDARZENIE( id, id_rodzaju_zdarzenia, id_samochodu, data, czas, przebieg, komentarz) VALUES(DEFAULT, 2, 5, '2012-10-19 10:23:00', 5, 200000, 'Wymiana paska rozrządu'); | + | INSERT INTO ZDARZENIE( id, id_czynnosci_serwisowej, id_samochodu, data, czas, przebieg, komentarz) VALUES(DEFAULT, 2, 5, '2012-10-19 10:23:00', 5, 200000, 'Wymiana paska rozrządu'); |
| ===Edycja zdarzenia=== | ===Edycja zdarzenia=== | ||
| - | UPDATE ZDARZENIE SET id_rodzaju_zdarzenia = 2 , id_samochodu = 5, data = '2012-11-11 12:33:00', czas = 3, przebieg = 10000, komentarz = 'Przegląd techniczny' WHERE id = 5; | + | UPDATE ZDARZENIE SET id_czynnosci_serwisowej = 2 , id_samochodu = 5, data = '2012-11-11 12:33:00', czas = 3, przebieg = 10000, komentarz = 'Przegląd techniczny' WHERE id = 5; |
| ===Usuwanie zdarzenia=== | ===Usuwanie zdarzenia=== | ||
