aPEymdbk

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 ('filahf', 'Filip', 'Södra esplanaden','0735153442'),
59       ('alex55', 'Alexander', 'Lund','0123456789'),
60       ('nico44', 'Nicoline', 'Malmö','0987654321');
61--Populate theater
62INSERT
63INTO   theater (theater_name,seats)
64VALUES  ('Filmstaden','300');
65--Popultate movies
66INSERT
67INTO   movies (movie_name)
68VALUES  ('Aladdin'),
69	('The Bodyguard'),
70	('Home Alone'),
71	('Basic Instinct'),
72	('Lethal Weapon 3'),
73	('Batman Returns'),
74	('A Few Good Men'),
75	('Sister Act'),
76	('Bram Stokers'),
77	('Waynes World');
78--Populate shows
79INSERT
80INTO   shows (date,movie_name,theater_name)
81VALUES  ('2017–01–24','Aladdin','Filmstaden'),
82		('2017–01–24','Batman','Filmstaden'),
83		('2017–01–25','Aladdin','Filmstaden'),
84		('2017–01–26','Aladdin','Filmstaden'),
85		('2017–01–24','Lethal Weapon 3','Filmstaden'),
86		('2017–01–27','Aladdin','Filmstaden');
87
88INSERT
89INTO   reservations (username,res_number,date,movie_name)
90VALUES ('filahf',NULL,'2017–01–25','Aladdin');