====== 1. Projektowanie tabel ======

====== 2. Diagram ERD ======

Podpunkt 1) Projektowanie tabel oraz podpunkt 2) Diagram ERD został zrealizowany podczas części konceptualnej, gdzie oprócz listy encji i diagramu ERD przygotowaliśmy kompletny diagram tabel. Poniżej ten sam diagram:

{{:pl:dydaktyka:sbd:2012:projekty:sieci:start:diagram_v06h.png|}}

====== 3. Tablica krzyżowa ======

{{:pl:dydaktyka:sbd:2012:projekty:sieci:start:tabela_krzyzowa.png|}}

T1 – piętro \\
T2 – pomieszczenie \\
T3 – typ pomieszczenia \\
T4 – urządzenie końcowe \\
T5 – typ urządzenia końcowego \\
T6 – szafa \\
T7 – panel krosowniczy \\
T8 – switch \\
T9 – port \\
T10 – port2port \\
T11 – typ połączenia \\
T12 – VLAN \\
T13 – typ portu \\

====== 4. Projekt danych w języku SQL (DDL) ======



                                                                     
                                                                     
                                                                     
                                             
Running migrations for main:
 - Migrating forwards to 0001_initial.
 > main:0001_initial


   = CREATE TABLE "main_vlan" ("id" serial NOT NULL PRIMARY KEY, "name" varchar(32) NOT NULL, "notes" text NULL); []
   = CREATE TABLE "main_port" ("id" serial NOT NULL PRIMARY KEY, "number" integer NOT NULL, "port_type" varchar(1) NOT NULL, "vlan_id" integer NULL, "switch_id" integer NULL, "terminal_id" integer NULL, "room_id" integer NOT NULL, "patch_panel_id" integer NULL); []
   = CREATE TABLE "main_port2port" ("id" serial NOT NULL PRIMARY KEY, "port1_id" integer NOT NULL UNIQUE, "port2_id" integer NOT NULL UNIQUE, "connection_type_id" integer NOT NULL); []
   = ALTER TABLE "main_port2port" ADD CONSTRAINT "main_port2port_port1_id_16173bc9_uniq" UNIQUE ("port1_id", "port2_id") []
   = CREATE TABLE "main_connectiontype" ("id" serial NOT NULL PRIMARY KEY, "name" varchar(32) NOT NULL); []
   = CREATE TABLE "main_switch" ("id" serial NOT NULL PRIMARY KEY, "rack_id" integer NOT NULL, "name" varchar(32) NOT NULL, "mac_address" varchar(17) NOT NULL); []
   = CREATE TABLE "main_patchpanel" ("id" serial NOT NULL PRIMARY KEY, "rack_id" integer NOT NULL, "name" varchar(32) NOT NULL); []
   = CREATE TABLE "main_rack" ("id" serial NOT NULL PRIMARY KEY, "room_id" integer NOT NULL, "name" varchar(32) NOT NULL); []
   = CREATE TABLE "main_terminal" ("id" serial NOT NULL PRIMARY KEY, "terminal_type_id" integer NOT NULL, "mac_address" varchar(17) NOT NULL, "ip_address" inet NOT NULL, "mask" inet NOT NULL, "gateway" inet NOT NULL, "primary_dns" inet NOT NULL, "secondary_dns" inet NOT NULL); []
   = CREATE TABLE "main_terminaltype" ("id" serial NOT NULL PRIMARY KEY, "name" varchar(32) NOT NULL); []
   = CREATE TABLE "main_room" ("id" serial NOT NULL PRIMARY KEY, "room_type_id" integer NOT NULL, "number" varchar(8) NOT NULL, "floor_id" integer NOT NULL); []
   = CREATE TABLE "main_roomtype" ("id" serial NOT NULL PRIMARY KEY, "name" varchar(32) NOT NULL); []
   = CREATE TABLE "main_floor" ("id" serial NOT NULL PRIMARY KEY, "number" integer NOT NULL, "notes" text NULL); []
   = ALTER TABLE "main_port" ADD CONSTRAINT "vlan_id_refs_id_3e841bd9" FOREIGN KEY ("vlan_id") REFERENCES "main_vlan" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_port_vlan_id" ON "main_port" ("vlan_id"); []
   = ALTER TABLE "main_port" ADD CONSTRAINT "switch_id_refs_id_78d0874e" FOREIGN KEY ("switch_id") REFERENCES "main_switch" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_port_switch_id" ON "main_port" ("switch_id"); []
   = ALTER TABLE "main_port" ADD CONSTRAINT "terminal_id_refs_id_3faacad8" FOREIGN KEY ("terminal_id") REFERENCES "main_terminal" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_port_terminal_id" ON "main_port" ("terminal_id"); []
   = ALTER TABLE "main_port" ADD CONSTRAINT "room_id_refs_id_55468a3d" FOREIGN KEY ("room_id") REFERENCES "main_room" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_port_room_id" ON "main_port" ("room_id"); []
   = ALTER TABLE "main_port" ADD CONSTRAINT "patch_panel_id_refs_id_46557fe" FOREIGN KEY ("patch_panel_id") REFERENCES "main_patchpanel" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_port_patch_panel_id" ON "main_port" ("patch_panel_id"); []
   = ALTER TABLE "main_port2port" ADD CONSTRAINT "port1_id_refs_id_301248ed" FOREIGN KEY ("port1_id") REFERENCES "main_port" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = ALTER TABLE "main_port2port" ADD CONSTRAINT "port2_id_refs_id_301248ed" FOREIGN KEY ("port2_id") REFERENCES "main_port" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = ALTER TABLE "main_port2port" ADD CONSTRAINT "connection_type_id_refs_id_1c5dbca4" FOREIGN KEY ("connection_type_id") REFERENCES "main_connectiontype" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_port2port_connection_type_id" ON "main_port2port" ("connection_type_id"); []
   = ALTER TABLE "main_switch" ADD CONSTRAINT "rack_id_refs_id_5f027284" FOREIGN KEY ("rack_id") REFERENCES "main_rack" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_switch_rack_id" ON "main_switch" ("rack_id"); []
   = ALTER TABLE "main_patchpanel" ADD CONSTRAINT "rack_id_refs_id_3dfecd74" FOREIGN KEY ("rack_id") REFERENCES "main_rack" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_patchpanel_rack_id" ON "main_patchpanel" ("rack_id"); []
   = ALTER TABLE "main_rack" ADD CONSTRAINT "room_id_refs_id_6f41d1e1" FOREIGN KEY ("room_id") REFERENCES "main_room" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_rack_room_id" ON "main_rack" ("room_id"); []
   = ALTER TABLE "main_terminal" ADD CONSTRAINT "terminal_type_id_refs_id_4cec5637" FOREIGN KEY ("terminal_type_id") REFERENCES "main_terminaltype" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_terminal_terminal_type_id" ON "main_terminal" ("terminal_type_id"); []
   = ALTER TABLE "main_room" ADD CONSTRAINT "room_type_id_refs_id_5c9242e5" FOREIGN KEY ("room_type_id") REFERENCES "main_roomtype" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_room_room_type_id" ON "main_room" ("room_type_id"); []
   = ALTER TABLE "main_room" ADD CONSTRAINT "floor_id_refs_id_12efc8fd" FOREIGN KEY ("floor_id") REFERENCES "main_floor" ("id") DEFERRABLE INITIALLY DEFERRED; []
   = CREATE INDEX "main_room_floor_id" ON "main_room" ("floor_id"); []
   = ALTER TABLE "main_port2port" ADD CONSTRAINT p2p_check CHECK (site1_id < site2_id);
 

- Sending post_syncdb signal for main: ['RoomType', 'Port2Port', 'Floor', 'VLAN', 'TerminalType', 'ConnectionType', 'Rack', 'Terminal', 'Switch', 'Port', 'PatchPanel', 'Room']


====== 5. Słowniki danych======



^ **Nazwa** ^ **Zastosowanie** ^ **Wartości** ^
| Port | Port może być zarówno w ścianie (wtedy referencja do pomieszczenia) jak i w panelu, wtedy referencja do panelu. | nazwa_portu: Liczba naturalna, nie globalna. Wraz z oznaczeniem panelu stworzy pełny identyfikator  |
| Typ portu | Rozróżnienie, gdzie port się znajduje | opis_portu: enum ścienny/panelowy|
| Panel krosowniczy | Grupuje gniazdka | Nazwa_panelu: textNajczęściej będzie to jedna litera |
| Urządzenie końcowe | Urządzenie podłączane do sieci. Może mieć jedno lub wiele gniazdek i interfejsów sieciowych | adres_mac: macaddr, adres_IP: inet, maska_podsieci: inet, domyslna_droga:inet, adres_dns_glowny:inet, adres_dns_drugorzedny:inet |
| Typ urządzenia końcowego | Określa typ urządzenia podłączonego do sieci. Pozwala opisać urządzenie, np. czy jest to ruter czy komputer | Opis: text |
| Switch | Urządzenie sieciowe | Opis:text, adres_Mac:macaddr |
| Szafa | Grupuje panele krosownicze | Nazwa:text |
| Pomieszczenie | Grupuje szafy, gniazdka i urządzenia | numer_pomieszczenia:integer |
| Piętro | Grupuje pomieszczenia | numer_pietra:integer pole_techniczne:text |
| Typ pomieszczenia | Określa typ pomieszczenia | Opis:text |
| VLAN | Grupuje gniazdka w switchu w wirtualne sieci lokalne | nazwa_vlanu:text, opis:text |
| Port2Port | Opisuje połączenie między portami | REL_port1<REL_port2 |
| Typ połączenia | Opisuje typ połączenia port2port | Opis:text |


====== 6. Analiza zależności funkcyjnych i normalizacja tabel======
^Nazwa ^ Atrybuty niekluczowe ^ Postać Normalna ^
| Port | Klucz:P_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Typ portu | Klucz:TP_ID, , reszta: niekluczowe zależne od klucza | 3NF |
| Panel Krosowniczy | Klucz:PK_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Urządzenie końcowe | Klucz:UK_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Typ urządzenia końcowego | Klucz:TUP_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Switch | Klucz:S_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Szafa | Klucz:S_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Pomieszczenie | Klucz:P_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Piętro | Klucz:P_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Typ pomieszczenia | Klucz:TP_ID , reszta: niekluczowe zależne od klucza | 3NF |
| VLAN | Klucz:V_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Port2Port | Klucz:PP_ID , reszta: niekluczowe zależne od klucza | 3NF |
| Typ połączenia | Klucz:TP_ID , reszta: niekluczowe zależne od klucza | 3NF |


Uzasadnienie:
  * Każdy wiersz ma swój identyfikator (1NF)
  * Żaden wiersz nie posiada powtarzających się grup (1NF)
  * Każdy atrybut zależy od całości klucza- atrybuty mogą istnieć niezależnie od siebie (2NF)
  * Każdy atrybut zależy od klucza bezpośrednio, nie ma zależności tranzytywnych (3NF)

====== 7. Opcjonalnie: Denormalizacja struktury======

Nie była konieczna

====== 8. Projektowanie operacji na danych ======

# Wyszukanie wszystkich gniazdek na danym piętrze
>>> Port.objects.filter(port_type='R', room__floor__number=1)
SELECT "main_port"."id", "main_port"."number", "main_port"."room_id", "main_port"."port_type", "main_port"."vlan_id", "main_port"."switch_id", "main_port"."terminal_id", "main_port"."patch_panel_id" FROM "main_port" INNER JOIN "main_room" ON ("main_port"."room_id" = "main_room"."id") INNER JOIN "main_floor" ON ("main_room"."floor_id" = "main_floor"."id") WHERE ("main_floor"."number" = 1  AND "main_port"."port_type" = R )

# Wyszukanie wszystkich połączeń między portami podanego switcha (SW1), a portami należącymi do patch paneli.
>>> Port2Port.objects.filter(port1_id__port_type='S', port1_id__switch__name='SW1', port2_id__port_type='P') | Port2Port.objects.filter(port2_id__port_type='S', port2_id__switch__name='SW1', port1_id__port_type='P')
SELECT "main_port2port"."id", "main_port2port"."port1_id", "main_port2port"."port2_id", "main_port2port"."connection_type_id" FROM "main_port2port" INNER JOIN "main_port" ON ("main_port2port"."port1_id" = "main_port"."id") LEFT OUTER JOIN "main_switch" ON ("main_port"."switch_id" = "main_switch"."id") INNER JOIN "main_port" T4 ON ("main_port2port"."port2_id" = T4."id") LEFT OUTER JOIN "main_switch" T5 ON (T4."switch_id" = T5."id") WHERE ( ( "main_switch"."name" = SW1  AND "main_port"."port_type" = S  AND T4."port_type" = P ) OR (T4."port_type" = S  AND "main_port"."port_type" = P  AND T5."name" = SW1 ))

===== Wyszukanie wszystkich połączeń należących do danego VLANA (VLAN1) oraz do urządzenia końcowego =====
<code python>
>>> Port2Port.objects.filter(port1_id__vlan__name='VLAN1', port2_id__port_type='T') | Port2Port.objects.filter(port2_id__vlan__name='VLAN1', port1_id__port_type='T')
</code>

<code sql>
SELECT "main_port2port"."id", "main_port2port"."port1_id", "main_port2port"."port2_id", "main_port2port"."connection_type_id" FROM "main_port2port" INNER JOIN "main_port" ON ("main_port2port"."port1_id" = "main_port"."id") LEFT OUTER JOIN "main_vlan" ON ("main_port"."vlan_id" = "main_vlan"."id") LEFT OUTER JOIN "main_port" T4 ON ("main_port2port"."port2_id" = T4."id") LEFT OUTER JOIN "main_vlan" T5 ON (T4."vlan_id" = T5."id") WHERE ( ("main_vlan"."name" = VLAN1  AND T4."port_type" = T ) OR (T5."name" = VLAN1  AND "main_port"."port_type" = T ))
</code>