
CREATE TABLE addrs
   (
   addr_id  SERIAL PRIMARY KEY,
   ip_addr  inet,
   mac_addr macaddr,
   UNIQUE ( ip_addr, mac_addr )
   );

CREATE TABLE protocols
   (
   protocol_id SERIAL PRIMARY KEY,
   protocol    TEXT
   );

CREATE TABLE incident
   (
   inci_id             SERIAL PRIMARY KEY,
   date                TIMESTAMP NOT NULL,
   interface           TEXT NOT NULL,
   source_addr_id      INTEGER REFERENCES addrs( addr_id) ON DELETE CASCADE,
   destination_addr_id INTEGER REFERENCES addrs( addr_id) ON DELETE CASCADE,
   len                 INTEGER,
   tos                 INTEGER,
   prec                INTEGER,
   ttl                 INTEGER,
   id                  INTEGER,
   ce                  BOOLEAN,
   df                  BOOLEAN,
   mf                  BOOLEAN,
   frag                INTEGER,
   protocol_id         INTEGER REFERENCES protocols( protocol_id )
                          ON UPDATE CASCADE ON DELETE CASCADE,
   spt                 INTEGER,
   dpt                 INTEGER,
   seq                 INTEGER,
   ack_value           INTEGER,
   window              INTEGER,
   res                 INTEGER,
   urg                 BOOLEAN,
   ack                 BOOLEAN,
   psh                 BOOLEAN,
   rst                 BOOLEAN,
   syn                 BOOLEAN,
   fin                 BOOLEAN,
   urgp                INTEGER,
   type                INTEGER,
   code                INTEGER
   );

CREATE VIEW inci AS
SELECT date, inci_id, protocol, a.ip_addr, b.ip_addr, spt, dpt
FROM      (SELECT addr_id, ip_addr FROM addrs) AS a
     JOIN incident ON incident.source_addr_id = a.addr_id
     JOIN (SELECT addr_id, ip_addr FROM addrs)
           AS b ON incident.destination_addr_id = b.addr_id
     JOIN protocols USING( protocol_id );
