Różnice między wybraną wersją a wersją aktualną.
|
pl:dydaktyka:sbd:2012:projekty:sieci:start:logiczny [2012/12/04 23:15] sbd12 |
pl:dydaktyka:sbd:2012:projekty:sieci:start:logiczny [2019/06/27 15:50] |
||
|---|---|---|---|
| Linia 1: | Linia 1: | ||
| - | ====== 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====== | ||
| - | |||
| - | {{:pl:dydaktyka:sbd:2012:projekty:sieci:start:slowniki.png|}} | ||
| - | |||
| - | ====== 6. Analiza zależności funkcyjnych i normalizacja tabel====== | ||
| - | |||
| - | {{:pl:dydaktyka:sbd:2012:projekty:sieci:start:normalizacja.png|}} | ||
| - | |||
| - | 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 | ||
| - | >>> 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') | ||
| - | 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 )) | ||
