Cinephilia sql

cinephilia.sql

DROP TABLE IF EXISTS playing;
DROP TABLE IF EXISTS film;
DROP TABLE IF EXISTS category; 
DROP TABLE IF EXISTS kino;

-- userless: drop tables seem also to drop the sequences
-- DROP SEQUENCE IF EXISTS film_film_id_seq;
-- DROP SEQUENCE IF EXISTS category_category_id_seq;
-- DROP SEQUENCE IF EXISTS  kino_kino_id_seq;



CREATE TABLE category(
 category_id SERIAL PRIMARY KEY,
 name TEXT NOT NULL
);

CREATE TABLE film(
 film_id SERIAL PRIMARY KEY,
 name TEXT NOT NULL,
 date DATE,
 category_id integer REFERENCES category(category_id)
);

CREATE TABLE kino(
 kino_id SERIAL PRIMARY KEY,
 name TEXT NOT NULL,
 address TEXT
);

CREATE TABLE playing(
  film_id integer REFERENCES film (film_id),
  kino_id integer REFERENCES kino (kino_id) ON DELETE CASCADE,
  representation timestamp,
  CONSTRAINT playing_pkey PRIMARY KEY(film_id, kino_id)
);

INSERT INTO category (category_id,name) VALUES (DEFAULT, 'Tragikomödie');
INSERT INTO category (category_id,name) VALUES (DEFAULT, 'Drama');
INSERT INTO category (category_id,name) VALUES (DEFAULT, 'Dokumentation');

INSERT INTO film (film_id, name, date, category_id) VALUES (DEFAULT, 'Madame Mallory und der Duft von Curry', '2014-01-01', 1);
INSERT INTO film (film_id, name, date, category_id) VALUES (DEFAULT, 'Unsere Träume', '2014-01-01', NULL);
INSERT INTO film (film_id, name, date, category_id) VALUES (DEFAULT, 'Imagine', '2014-01-01', 2);
INSERT INTO film (film_id, name, date, category_id) VALUES (DEFAULT, 'Auf den Barockaden', '2014-01-01', 3);

INSERT INTO kino (kino_id, name, address) VALUES (DEFAULT, 'Apollo - Das Kino', ' Gumpendorfer Straße 63a, 1060 Wien');
INSERT INTO kino (kino_id, name, address) VALUES (DEFAULT, 'Bellaria Kino', 'Museumstraße 3, 1070 Wien');
INSERT INTO kino (kino_id, name, address) VALUES (DEFAULT, 'Burg Kino', 'Opernring 19, 1010 Wien');
INSERT INTO kino (kino_id, name, address) VALUES (DEFAULT, 'De France', 'Schottenring 5, 1010 Wien');

INSERT INTO playing (kino_id, name, address) VALUES (DEFAULT, 'De France', 'Schottenring 5, 1010 Wien');