Ocena struktury bazy SQL

0

Tak jak w temacie. Prosiłbym o sprawdzenie diagramu i kodu niżej.Temat dość luźny czyli "lotnisko" a baza musi się składać z min. 7 tabel. Założeniem jest jest stworzenie bazy pod działanie lotniska. Dodam tylko ze żadna aplikacja nie będzie pod to tworzona, tylko dodatkowe skrypty,widoki.
4357375bbb.png

 
CREATE DATABASE Lotnisko
GO

USE Lotnisko
GO


CREATE TABLE PRZEWOZNIK

	(PRZEW_NAZWA VARCHAR(45)  CONSTRAINT PK_PRZEWOZNIK PRIMARY KEY NOT NULL,
	PRZEW_KRAJ VARCHAR(45) NOT NULL,
	PRZEW_MIASTO VARCHAR(45) NOT NULL,
	PRZEW_ULICA VARCHAR(45) NOT NULL,
	PRZEW_TEL NUMERIC(15),
	PRZEW_STRONA VARCHAR(45),
	PRZEW_EMAIL VARCHAR(45) NULL);	


CREATE TABLE SAMOLOT
	(SAM_ID int IDENTITY (1, 1) CONSTRAINT PK_SAMOLOT PRIMARY KEY,
	SAM_NAZWA VARCHAR(15) NOT NULL,
	SAM_MODEL VARCHAR(15) NOT NULL,
	SAM_NR_SERYJNY VARCHAR(15) NOT NULL,
	SAM_LICZBA_MIEJSC NUMERIC(3) CHECK (SAM_LICZBA_MIEJSC>0) );


CREATE TABLE LOTY
	(LOTY_ID int IDENTITY (1, 1) CONSTRAINT PK_LOTY PRIMARY KEY,
	LOTY_SKAD_MIASTO VARCHAR(20) DEFAULT ('POZNAN'),
	LOTY_SKAD_LOTNISKO VARCHAR(20) DEFAULT ('LAWICA'),
	LOTY_DOKAD_MIASTO VARCHAR(20),
	LOTY_DOKAD_LOTNISKO VARCHAR(20),
    LOTY_DATA_WYLOTU DATE,
	LOTY_GODZ_WYLOTU TIME,
	LOTY_DATA_PRZYLOTU DATE,
    LOTY_GODZ_PRZYLOTU TIME,
	LOTY_SAM_ID INT CONSTRAINT FK_LOTY_SAMOLOT REFERENCES SAMOLOT(SAM_ID));

ALTER TABLE LOTY WITH NOCHECK ADD CONSTRAINT CK_LOTY_DATA CHECK (LOTY_DATA_PRZYLOTU >= LOTY_DATA_WYLOTU);

CREATE TABLE ZALOGA
	(ZALOGA_ID int IDENTITY (1, 1) CONSTRAINT PK_ZALOGA PRIMARY KEY,
	ZALOGA_IMIE VARCHAR(15),
	ZALOGA_NAZWISKO VARCHAR(15),
	ZALOGA_PESEL NUMERIC(11),
	ZALOGA_MIEJSCOWOSC VARCHAR(15),
	ZALOGA_ROLA VARCHAR(15)  CONSTRAINT CK_ZALOGA_ROLA CHECK(ZALOGA_ROLA IN ('PILOT','DRUGI PILOT','STEWARDESSA','STEWARD')),
	ZALOGA_DATA_ZATRUD DATE,
	ZALOGA_PLACA NUMERIC(8,2) CONSTRAINT MIN_PLACA CHECK(ZALOGA_PLACA>500),
	ZALOGA_LOTY_ID INT CONSTRAINT FK_ZALOGA_LOTY REFERENCES LOTY(LOTY_ID));



CREATE TABLE PASAZEROWIE
    (PASAZ_ID int IDENTITY (1, 1) CONSTRAINT PK_PASAZ PRIMARY KEY,
	PASAZ_IMIE VARCHAR(15) NOT NULL,
	PASAZ_NAZWISKO VARCHAR(15) NOT NULL,
	PASAZ_WIEK NUMERIC(3) NOT NULL, 
	PASAZ_GR_WIEKOWA VARCHAR(15) CONSTRAINT CK_PASAZEROWIE_GR_WIEKOWA CHECK(PASAZ_GR_WIEKOWA IN ('NIEMOWLE','DZIECKO','MLODZIEZ','DOROSLY')),
    PASAZ_PLEC VARCHAR(10) CONSTRAINT CK_PASAZEROIWE_PLEC CHECK(PASAZ_PLEC IN ('K','M')),
	PASAZ_PESEL NUMERIC(11) NOT NULL, 
    PASAZ_KRAJ_ZAM VARCHAR(11) NOT NULL,
	PASAZ_MIEJSCE_ZAM VARCHAR(15) NOT NULL,
    PASAZ_ULICA VARCHAR(15) NULL,
	PASAZ_TEL NUMERIC(9),
	PASAZ_LOTY_ID int CONSTRAINT FK_LOTY_PASAZEROWIE REFERENCES LOTY(LOTY_ID));

			
CREATE TABLE BILETY
	(BILET_ID int IDENTITY (1, 1) CONSTRAINT PK_BILETY PRIMARY KEY,
	BILET_CENA NUMERIC(5,2),
	BILET_NR_MIEJSCA NUMERIC(3) CONSTRAINT MAX_BILET_NR_MIEJSCA CHECK(BILET_NR_MIEJSCA<=300),
	BILET_KLASA  CHAR(10) CONSTRAINT CK_BILET_KLASA CHECK(BILET_KLASA IN ('ECONOMY','PREMIUM','BUSINESS')) DEFAULT 'ECONOMY',
	BILET_ZNIŻKA VARCHAR(15),
	BILET_PASAZ_ID INT CONSTRAINT FK_BILETY_PASAZEROWIE REFERENCES PASAZEROWIE(PASAZ_ID),
	BILET_PRZEW_NAZWA VARCHAR(45) CONSTRAINT FK_PRZEWOZNIK REFERENCES PRZEWOZNIK(PRZEW_NAZWA));

	
	
CREATE TABLE BAGAZE
	(BAGAZ_ID int IDENTITY (1, 1) CONSTRAINT PK_BAGAZE PRIMARY KEY,
	BAGAZ_RODZAJ CHAR(10) CONSTRAINT CK_BAGAZE_RODZAJ CHECK(BAGAZ_RODZAJ IN ('TORBA PODROZNA','PLECAK','WALIZKA')) DEFAULT 'TORBA PODROZNA',
	BAGAZ_WAGA NUMERIC(3) CONSTRAINT MAX_BAGAZ_WAGA CHECK(BAGAZ_WAGA <=100),
	BAGAZ_NR NUMERIC(8),
	BAGAZ_PASAZ_ID int CONSTRAINT FK_BAGAZ_PASAZEROWIE REFERENCES PASAZEROWIE(PASAZ_ID));


0

nie ma takiej rzeczy, której by się nie dało sp..dolić, jak również skrócić. Zdaje się, że nie wiesz jak port lotniczy działa lub również system zamoień :)

2

1.Nazewnictwo. Tutaj oczywiście kwestia czysto subiektywna, lecz skoro tabela nazywa się np.Pasażerowie, to nie znajdzie się w niej informacja o średniej liczbie kobiet na metr kwadratowy w Brazylii, więc nazywanie potem wszystkiego PASAZ_IMIE etc. mija się z celem. CREATE TABLE Pasazerowie (id, imie, nazwisko, wiek ..., to odnośnie wszystkich tabel.
A jeżeli już musisz tak nazywać, to chociaż pełną nazwą tabeli: pasazer_id, pasazer_imie, a nie pasaż :P

2.Grupę wiekową powinieneś dedukować z wieku pasażera. Dzięki temu unikniesz problemów typu 55-letni pan Jan z grupą niemowlę.

3.LOTY_SKAD_MIASTO + LOTY_SKAD_LOTNISKO, a potem to samo odnośnie dokąd - łamanie zasady DRY. Ja zrobiłbym to na zasadzie osobnej tabeli z miejscami lotnisk: CREATE TABLE Lotniska (id INT PRIMARY KEY, miasto VARCHAR(128), lotnisko VARCHAR(128)). Łatwiej to później rozbudować o kraj, planetę, konstelację, supergromad... em - pozostańmy przy tym, że jest to łatwiejsze do zarządzania.

4.LOT_SAM_ID nic nie mówi. samolot_id jest znacznie wymowniejszą nazwą kolumny i nie kojarzy się z nazewnictwem rodem z Basica.

5.Id bagażu i numer bagażu - hę? Czym to się różni?

6.Tabela Załoga jest przemyślana ok, jeżeli stawiasz na wiarygodne zapamiętywanie historii. Jednak niewątpliwą wadą tego rozwiązania jest to, że znajduje się tam na przykład kolumna Płaca czy Miejscowość, która nijak nie jest bezpośrednio powiązana z lotem. Dlatego tutaj zrobiłbym to na innej zasadzie - tabela Załoga niepowiązana bezpośrednio z Loty, a w zamian stworzona tabela asocjacyjna Załoga_Loty z dwoma kolumnami będącymi kluczami obcymi do tabeli Loty oraz Załoga.

7.Skoro wszystko ma całkowitoliczbowe id-ki, niech i przewoźnicy je mają. Poza tym liczbowe indeksy są wydajniejsze.

8.Numer telefonu nie musi mieć 9 cyfr. Zapamiętuj go jako ciąg znaków.

9.Defaultowanie miejscowości startu. Fe.

Hm, to chyba tyle z tego, co rzuciło mi się w oczy.

0
  1. W sumie racja nie brzmi to najlepiej, ale podobnie robiliśmy na zajęciach, wiec robiłem podobnie :P

  2. Własnie myślałem jakby takie coś zrobić jednak na nic nic wpadłem :/ Potem chciałem dopisać jakąś procedurę.

  3. Chodziło o numer naszego bagażu, ale chyba coś mi się pomyliło :D Zaraz to skasuję.

  4. Tak wiem, zdążyłem to już zmienić :)

  5. No skoro nasze lotnisko znajduje się załóżmy w Poznaniu to czemu nie może być to jako wartość domyślna?

Dziękuję bardzo za pomoc i resztę postaram się zmienić :)

2

Odnosząc się do @Patryk27

Nazewnictwo. Tutaj oczywiście kwestia czysto subiektywna, lecz skoro tabela nazywa się np.Pasażerowie,

Powinny być jeszcze wszystkie nazwy po angielsku, ale skoro to projekt szkolny to pewnie nie przejdzie.

2.Grupę wiekową powinieneś dedukować z wieku pasażera. Dzięki temu unikniesz problemów typu 55-letni pan Jan z grupą niemowlę.

.LOTY_SKAD_MIASTO + LOTY_SKAD_LOTNISKO, a potem to samo odnośnie dokąd - łamanie zasady DRY.

To bardziej łamanie 3NF (http://en.wikipedia.org/wiki/Third_normal_form) a nie DRY, ale racja.

Ja zrobiłbym to na zasadzie osobnej tabeli z miejscami lotnisk: CREATE TABLE Lotniska (id INT PRIMARY KEY, miasto VARCHAR(128), lotnisko VARCHAR(128)). Łatwiej to później rozbudować o kraj, planetę, konstelację, supergromad... em - pozostańmy przy tym, że jest to łatwiejsze do zarządzania.

Albo, jeśli lotnisko jest unikalne, można pokusić się o zostawienie tylko LOTY_SKAD_LOTNISKO, i dodanie tabeli CREATE TABLE LotniskaMiasta (miasto VARCHAR(128), lotnisko VARCHAR(128)) (1:n do joinowania lotnisk z miastami - ale to raczej gorszy pomysł niż Twój).

Albo w ogóle teoretycznie "najczystsze" rozwiązanie, czyli zrobienie LOTY_SKAD_LOTNISKO_ID oraz tabel Lotniska (id, nazwa, id_miasta) oraz Miasta (id, nazwa) - ale to sztuka dla sztuki jeśli jedyne co wiążemy z lotniskami i miastami to nazwa.

Ja bym dodał:

    LOTY_SKAD_MIASTO VARCHAR(20) DEFAULT ('POZNAN'),
    LOTY_SKAD_LOTNISKO VARCHAR(20) DEFAULT ('LAWICA')

Te defaulty to raczej nie logika która powinna być na poziomie bazy danych. Raczej tutaj nie ma po co ustawiać defaulta i wymagać jawnego podania miejsca startu.

Analogicznie do rozważenia:

 BILET_KLASA  CHAR(10) CONSTRAINT CK_BILET_KLASA CHECK(BILET_KLASA IN ('ECONOMY','PREMIUM','BUSINESS')) DEFAULT 'ECONOMY',
BAGAZ_RODZAJ CHAR(10) CONSTRAINT CK_BAGAZE_RODZAJ CHECK(BAGAZ_RODZAJ IN ('TORBA PODROZNA','PLECAK','WALIZKA')) DEFAULT 'TORBA PODROZNA',
BILET_NR_MIEJSCA NUMERIC(3) CONSTRAINT MAX_BILET_NR_MIEJSCA CHECK(BILET_NR_MIEJSCA<=300),

Czemu na poziomie bazy wymuszasz takie rzeczy? Ogólnie jestem przeciwnikiem logiki w bazie danych, ale w tym przypadku jedyne co ten constraint Ci daje że jeśli kupisz większy samolot to nie będziesz i tak mógł do niego zmieścić więcej niż 300 ludzi ;). Ale ok, projekt szkolny.

 PASAZ_PLEC VARCHAR(10) CONSTRAINT CK_PASAZEROIWE_PLEC CHECK(PASAZ_PLEC IN ('K','M')),

Tutaj aż varchar(10) jest niepotrzebny, skoro i tak są tylko 2 wartości dozwolone (K i M) (btw. niepostępowe i niepoprawne politycznie, powinno być "kobieta", "mężczyzna" oraz "inne" ;) )

...ale ogólnie to wszystko to mniejsze lub większe szczegóły, ogólnie baza nie jest zła.

0

Te defaulty są wstawione bardziej tak na pokaz, że umiem takie coś zrobić. Zdaję sobie sprawę,iż nie jest to zbyt poprawne, ale to jest projekt szkolny, więc trzeba to trochę potraktować z przymrużeniem oka ;)

Tutaj aż varchar(10) jest niepotrzebny, skoro i tak są tylko 2 wartości dozwolone (K i M) (btw. niepostępowe i niepoprawne politycznie, powinno być "kobieta", "mężczyzna" oraz "inne" ;) )

No w sumie może mogłem kogoś tym obrazić :P

Do wszystkich wyżej zapronowanych przez Was uwag starałem się zastosować. Projekt jutro zostanie oddany, więc myślę, że nie ma już w nim "aż takich błędów". Bardzo dziękuję za pomoc, doceniam to.

Pozdrawiam.

0
Patryk27 napisał(a):

1.Nazewnictwo. Tutaj oczywiście kwestia czysto subiektywna, lecz skoro tabela nazywa się np.Pasażerowie, to nie znajdzie się w niej informacja o średniej liczbie kobiet na metr kwadratowy w Brazylii, więc nazywanie potem wszystkiego PASAZ_IMIE etc. mija się z celem. CREATE TABLE Pasazerowie (id, imie, nazwisko, wiek ..., to odnośnie wszystkich tabel.
A jeżeli już musisz tak nazywać, to chociaż pełną nazwą tabeli: pasazer_id, pasazer_imie, a nie pasaż :P

2.Grupę wiekową powinieneś dedukować z wieku pasażera. Dzięki temu unikniesz problemów typu 55-letni pan Jan z grupą niemowlę.

3.LOTY_SKAD_MIASTO + LOTY_SKAD_LOTNISKO, a potem to samo odnośnie dokąd - łamanie zasady DRY. Ja zrobiłbym to na zasadzie osobnej tabeli z miejscami lotnisk: CREATE TABLE Lotniska (id INT PRIMARY KEY, miasto VARCHAR(128), lotnisko VARCHAR(128)). Łatwiej to później rozbudować o kraj, planetę, konstelację, supergromad... em - pozostańmy przy tym, że jest to łatwiejsze do zarządzania.

4.LOT_SAM_ID nic nie mówi. samolot_id jest znacznie wymowniejszą nazwą kolumny i nie kojarzy się z nazewnictwem rodem z Basica.

5.Id bagażu i numer bagażu - hę? Czym to się różni?

6.Tabela Załoga jest przemyślana ok, jeżeli stawiasz na wiarygodne zapamiętywanie historii. Jednak niewątpliwą wadą tego rozwiązania jest to, że znajduje się tam na przykład kolumna Płaca czy Miejscowość, która nijak nie jest bezpośrednio powiązana z lotem. Dlatego tutaj zrobiłbym to na innej zasadzie - tabela Załoga niepowiązana bezpośrednio z Loty, a w zamian stworzona tabela asocjacyjna Załoga_Loty z dwoma kolumnami będącymi kluczami obcymi do tabeli Loty oraz Załoga.

7.Skoro wszystko ma całkowitoliczbowe id-ki, niech i przewoźnicy je mają. Poza tym liczbowe indeksy są wydajniejsze.

8.Numer telefonu nie musi mieć 9 cyfr. Zapamiętuj go jako ciąg znaków.

9.Defaultowanie miejscowości startu. Fe.

Hm, to chyba tyle z tego, co rzuciło mi się w oczy.

witam robię podobną bazę i mam pytanie, skoro skasować tabelę LOTY, to wszystko sie posypie i np jak chce zrobic połązcenie LOTY z Załoga (dodając asocjacyjną tabelkę) lub łącząc loty z czymkolwiek innym np Pasażerami to teraz jak mam mieć tabele LOTNISKA (id miasto nazwa) to co mam z tym teraz zrobić, czy w ogóle dawać gdzies te godziny odlotow dokąd skąd itp? jestem w kropce. pozdrawiam
ps. robię bazę w MS ACCES

1 użytkowników online, w tym zalogowanych: 0, gości: 1