DROP DATABASE IF EXISTS facebook_twitter;

CREATE DATABASE IF NOT EXISTS facebook_twitter CHARACTER SET utf8 COLLATE utf8_unicode_ci;

USE facebook_twitter;

CREATE TABLE stan (
  SID INTEGER NOT NULL,
  Stan VARCHAR(24),
  PRIMARY KEY (SID)
);

INSERT INTO stan VALUES (1, "Razmerje");
INSERT INTO stan VALUES (2, "Zakonski");
INSERT INTO stan VALUES (3, "Samski");
INSERT INTO stan VALUES (4, "Zapleteno");

CREATE TABLE oseba (
  ID INTEGER,
  Ime VARCHAR(24) NOT NULL,
  Rojen DATE,
  SID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (SID) REFERENCES stan(SID)
);

INSERT INTO oseba VALUES (1, "Jill", '1990-03-09', 1);
INSERT INTO oseba VALUES (2, "Jack", '1950-06-02', 1);
INSERT INTO oseba VALUES (3, "Joe", '1989-08-01', 4);
INSERT INTO oseba VALUES (4, "Jenn", '2001-01-07', 2);
INSERT INTO oseba (ID, Ime, SID) VALUES (5, "Jeff", 2);
INSERT INTO oseba VALUES (6, "Edna", '2011-04-23', 3);
INSERT INTO oseba VALUES (7, "North", NULL, 3);

CREATE TABLE twitter (
  OID INTEGER,
  SID INTEGER,
  PRIMARY KEY (OID, SID),
  FOREIGN KEY (OID) REFERENCES oseba(ID),
  FOREIGN KEY (SID) REFERENCES oseba(ID)
);

INSERT INTO twitter VALUES (1, 2), (3, 2), (4, 3), (2, 4);

CREATE TABLE facebook (
  OID INTEGER,
  PID INTEGER,
  PRIMARY KEY (OID, PID),
  FOREIGN KEY (OID) REFERENCES oseba(ID),
  FOREIGN KEY (PID) REFERENCES oseba(ID)
);

INSERT INTO facebook VALUES (1, 2), (2, 1), (2, 3), (3, 2), (2, 4), (4, 2), (3, 4), (4, 3);
