Gc1J30CV

1PRAGMA foreign_keys = OFF;
2DROP TABLE IF EXISTS 'theatres';
3DROP TABLE IF EXISTS 'performances';
4DROP TABLE IF EXISTS 'movies';
5DROP TABLE IF EXISTS 'tickets';
6DROP TABLE IF EXISTS 'customers';
7PRAGMA foreign_keys = ON;
8CREATE TABLE theatres (
9    theatre_name VARCHAR(64) NOT NULL,
10    capacity INT NOT NULL CHECK(capacity >= 0),
11    company VARCHAR(64) NOT NULL,
12    PRIMARY KEY(theatre_name);
13);
14CREATE TABLE performances (
15    performance_id TEXT DEFAULT (lower(hex(randomblob(16)))),
16    theatre_name VARCHAR(64) NOT NULL,
17    movie_id TEXT NOT NULL,
18    start_time DATETIME NOT NULL,
19    PRIMARY KEY(performance_id),
20    FOREIGN KEY(theatre_name),
21    FOREIGN KEY(movie_id);
22);
23CREATE TABLE movies (
24    movie_id TEXT DEFAULT (lower(hex(randomblob(16)))),
25    title TEXT NOT NULL,
26    production_year SMALLINT NOT NULL CHECK(production_year >= 1500),
27    imdb_key TEXT,
28    running_time SMALLINT NOT NULL,
29    PRIMARY KEY(movie_id);
30);
31CREATE TABLE tickets (
32    ticket_id TEXT DEFAULT (lower(hex(randomblob(16)))),
33    username VARCHAR(64) NOT NULL,
34    performance_id TEXT NOT NULL,
35    PRIMARY KEY(ticket_id),
36    FOREIGN KEY(username),
37    FOREIGN KEY(performance_id);
38);
39CREATE TABLE customers (
40    username VARCHAR(64) NOT NULL,
41    full_name TEXT NOT NULL,
42    password VARCHAR(64) NOT NULL,
43    PRIMARY KEY(username);
44);
45INSERT INTO theatres (theatre_name, capacity, company)
46VALUES ("Royal", 150, "SF Bio"),
47    ("Storgatan", 80, "SF Bio");
48INSERT INTO performances (
49        performance_id,
50        theatre_name,
51        movie_id,
52        start_time
53    )
54VALUES (
55        "AHKSHIUHAHSIUB876556",
56        "Royal",
57        "UHSAUH876876",
58        "2021-02-14 18:30"
59    ),
60    (
61        "79162796AHGSYSABD",
62        "Storgatan",
63        "19827378AGYISB",
64        "2021-02-14 20:00"
65    );
66INSERT INTO movies (
67        movie_id,
68        title,
69        production_year,
70        imdb_key,
71        running_time
72    )
73VALUES ("UHSAUH876876", "Avatar", 2009, "tt0499549", 162),
74    (
75        "19827378AGYISB",
76        "Shutter Island",
77        2010,
78        "tt1130884",
79        138
80    );
81INSERT INTO tickets (username, performance_id)
82VALUES ("iDaviddd", "AHKSHIUHAHSIUB876556"),
83    ("knobswitch", "79162796AHGSYSABD");
84INSERT INTO customers (username, full_name, password)
85VALUES ("iDaviddd", "David Öhrbom", "pass123"),
86    ("knobswitch", "Damil Sabotic", "knobswitcher101");