· 8 years ago · Feb 13, 2018, 11:10 AM
1--Lab2 sql
2-- Delete the tables if they exist.
3-- Disable foreign key checks, so the tables can
4-- be dropped in arbitrary order.
5PRAGMA foreign_keys=OFF;
6
7DROP TABLE IF EXISTS users;
8DROP TABLE IF EXISTS movies;
9DROP TABLE IF EXISTS reservations;
10DROP TABLE IF EXISTS shows;
11DROP TABLE IF EXISTS theater;
12
13
14PRAGMA foreign_key = on;
15
16CREATE TABLE users (
17 username TEXT NOT NULL UNIQUE,
18 name TEXT NOT NULL,
19 address TEXT NOT NULL,
20 phn_nbr CHAR(10),
21 PRIMARY KEY (username)
22);
23
24CREATE TABLE theater (
25 theater_name TEXT NOT NULL UNIQUE,
26 seats CHAR(3) NOT NULL,
27 PRIMARY KEY (theater_name)
28);
29
30CREATE TABLE movies (
31 movie_name TEXT NOT NULL UNIQUE,
32 PRIMARY KEY (movie_name)
33);
34
35CREATE TABLE shows (
36 date DATE,
37 movie_name TEXT NOT NULL,
38 theater_name TEXT NOT NULL,
39 PRIMARY KEY (movie_name,date)
40 FOREIGN KEY (theater_name) REFERENCES theater(theater_name)
41 FOREIGN KEY (movie_name) REFERENCES movies(movie_name)
42);
43
44CREATE TABLE reservations (
45 username TEXT NOT NULL,
46 res_number INTEGER,
47 date DATE,
48 movie_name TEXT NOT NULL,
49 PRIMARY KEY (res_number)
50 FOREIGN KEY (username) REFERENCES users(username)
51 FOREIGN KEY (movie_name,date) REFERENCES shows(movie_name,date)
52);
53
54
55--Populate users
56INSERT
57INTO users (username, name, address, phn_nbr)
58VALUES ('vik96', 'Viktor', 'Helsingborg','0733882275'),
59 ('alex96', 'Alexander', 'Lund','074422178'),
60 ('calle96', 'Carl', 'Malmo','0732119054');
61--Populate theater
62INSERT
63INTO theater (theater_name,seats)
64VALUES ('Filmstaden','150');
65--Popultate movies
66INSERT
67INTO movies (movie_name)
68VALUES ('Solsidan'),
69 ('Maze Runner'),
70 ('Downsizing'),
71 ('The Past'),
72 ('Lethal Weapon 3'),
73 ('Jumanji'),
74 ('Ted'),
75 ('Step Brothers'),
76 ('Darkest Hour'),
77 ('Logan');
78--Populate shows
79INSERT
80INTO shows (date,movie_name,theater_name)
81VALUES ('2017–01–24','Solsidan','Filmstaden'),