FvWfDEn7

1-- SQL script to create the tables necessary for lab 1 in EDAF75.
2-- MySQL version.
3--
4-- Creates the tables students, courses, taken_courses and
5-- populates them with (simulated) data.
6--
7-- We disable foreign key checks temporarily so we can delete the
8-- tables in arbitrary order, and so insertion is faster.
9
10PRAGMA foreign_keys=OFF;
11DROP TABLE IF EXISTS users;
12DROP TABLE IF EXISTS tickets;
13DROP TABLE IF EXISTS preformances;
14DROP TABLE IF EXISTS movies;
15DROP TABLE IF EXISTS theaters;
16PRAGMA foreign_keys=ON;
17-- Drop the tables if they already exist.
18
19CREATE TABLE users (
20  user_name          TEXT NOT NULL,
21  name   TEXT NOT NULL,
22  address    TEXT,
23  phone_number    CHAR(10),
24  PRIMARY KEY  (user_name)
25);
26
27CREATE TABLE preformances (
28  theater_name TEXT NOT NULL,
29  movie_name TEXT NOT NULL,
30  datee TEXT NOT NULL,
31  PRIMARY KEY   (movie_name, datee),
32  FOREIGN KEY   (movie_name) REFERENCES movies(movie_name),
33  FOREIGN KEY   (theater_name) REFERENCES theaters(theater_name)
34);
35
36CREATE TABLE tickets (
37  id  INT NOT NULL,
38  movie_name TEXT NOT NULL,
39  datee TEXT NOT NULL,
40  user_name TEXT NOT NULL,
41  PRIMARY KEY   (id),
42  FOREIGN KEY   (user_name) REFERENCES users(user_name)
43  FOREIGN KEY   (movie_name, datee) REFERENCES preformances(movie_name, datee)
44);
45
46
47CREATE TABLE movies (
48  movie_name TEXT NOT NULL,
49  PRIMARY key   (movie_name)
50);
51
52CREATE TABLE theaters (
53  theater_name          TEXT NOT NULL,
54  seats         INT NOT NULL,
55  PRIMARY KEY   (theater_name)
56);
57-- We will do a lot of inserts, so we start a transaction to make it faster.
58
59BEGIN TRANSACTION;
60
61-- Populate the students table.
62
63INSERT
64INTO   movies (movie_name)
65VALUES  ('James Bond'),
66        ('Fifty Shades Freed'),
67        ('The Shape of Water'),
68        ('Agatha - granndetektiven'),
69        ('Black Panther'),
70        ('Solsidan'),
71        ('Jumanji'),
72        ('Tjuren Ferdinand'),
73        ('Monky'),
74        ('Ted'),
75        ('The Greatset Showman'),
76        ('Mazerunner');
77
78-- Populate the courses table.
79
80INSERT
81INTO   users (user_name, name, address, phone_number)
82VALUES  ('johan','Johan Karlberg','GG_street', 0701123244),
83        ('agata','Bo El', 'Hyllegränd 3', 0701455348),
84        ('Makkee', 'Marucs T', 'Mickes GÃ¥rd 2A', 0703354428),
85        ('unamed', 'Edolf Klutters',NULL ,0701450928);
86INSERT
87INTO   tickets (id, movie_name, datee, user_name)
88VALUES  ('1', 'Ted', '10/2', 'agata'),
89        ('2', 'Monky', '11/2', 'unamed'),
90        ('3', 'Tjuren Ferdinand', '13/2', 'johan');
91
92INSERT
93INTO   preformances (theater_name, movie_name, datee)
94VALUES  ('Biopalatset', 'Ted', '10/2'),
95        ('Bergs BIO', 'Monky', '11/2'),
96        ('Bergs BIO', 'Mazerunner', '11/2'),
97        ('Biokungen', 'Tjuren Ferdinand', '13/2');
98
99INSERT
100INTO   theaters (theater_name, seats)
101VALUES  ('Biokungen',1337),
102        ('Bergs BIO', 609),
103        ('Biopalatset', 88),
104        ('Kinna',3);
105
106-- Commit the transaction.
107
108END TRANSACTION;
109
110-- And re-enable foreign key checks.
111
112PRAGMA foreign_key = on;