ADB writing

Set 4

CREATE SCHEMA IF NOT EXISTS movie;

USE movie;

CREATE TABLE theaters (

theater_id INT AUTO_INCREMENT PRIMARY KEY,

theater_name VARCHAR(50) NOT NULL,

location VARCHAR(100) NOT NULL

);

CREATE TABLE movies (

movie_id INT AUTO_INCREMENT PRIMARY KEY,

movie_name VARCHAR(100) NOT NULL,

director_name VARCHAR(100) NOT NULL,

duration INT NOT NULL

);

CREATE TABLE screens (

screen_id INT AUTO_INCREMENT PRIMARY KEY,

theater_id INT NOT NULL,

screen_number INT NOT NULL,

capacity INT NOT NULL,

FOREIGN KEY (theater_id) REFERENCES theaters(theater_id)

);

CREATE TABLE showtimes (

showtime_id INT AUTO_INCREMENT PRIMARY KEY,

movie_id INT NOT NULL,

screen_id INT NOT NULL,

showtime DATETIME NOT NULL,

FOREIGN KEY (movie_id) REFERENCES movies(movie_id),

FOREIGN KEY (screen_id) REFERENCES screens(screen_id)

);

CREATE TABLE bookings (

booking_id INT AUTO_INCREMENT PRIMARY KEY,

customer_name VARCHAR(100) NOT NULL,

customer_email VARCHAR(100) NOT NULL,

booking_time DATETIME NOT NULL

);

CREATE TABLE seats (

seat_id INT AUTO_INCREMENT PRIMARY KEY,

screen_id INT NOT NULL,

seat_number VARCHAR(10) NOT NULL,

FOREIGN KEY (screen_id) REFERENCES screens(screen_id)

);

CREATE TABLE booking_details (

booking_id INT NOT NULL,

seat_id INT NOT NULL,

showtime_id INT NOT NULL,

price DECIMAL(10,2) NOT NULL,

FOREIGN KEY (booking_id) REFERENCES bookings(booking_id),

FOREIGN KEY (seat_id) REFERENCES seats(seat_id),

FOREIGN KEY (showtime_id) REFERENCES showtimes(showtime_id)

);

INSERT INTO theaters (theater_name, location) VALUES

('Kino im Kesselhaus', 'Berlin'),

('UCI Luxe Kino', 'Frankfurt'),

('Cineplex Pforzheim', 'Pforzheim');

-- Movies table

INSERT INTO movies (movie_name, director_name, duration) VALUES

('Der Pate', 'Francis Ford Coppola', 175),

('Das Leben der Anderen', 'Florian Henckel von Donnersmarck', 137),

('Good Bye, Lenin!', 'Wolfgang Becker', 121),

('Lola rennt', 'Tom Tykwer', 81);

-- Screens table

INSERT INTO screens (theater_id, screen_number, capacity) VALUES

(1, 1, 100),

(1, 2, 75),

(2, 1, 120),

(3, 1, 80),

(3, 2, 100);

-- Showtimes table

INSERT INTO showtimes (movie_id, screen_id, showtime) VALUES

(1, 1, '2023-05-01 19:00:00'),

(1, 1, '2023-05-01 21:30:00'),

(1, 2, '2023-05-01 20:00:00'),

(2, 3, '2023-05-02 18:30:00'),

(2, 3, '2023-05-02 21:00:00'),

(3, 4, '2023-05-03 15:00:00'),

(3, 4, '2023-05-03 17:30:00'),

(3, 5, '2023-05-03 18:00:00'),

(3, 5, '2023-05-03 20:30:00'),

(4, 2, '2023-05-04 16:00:00'),

(4, 2, '2023-05-04 18:00:00');

-- Bookings table

INSERT INTO bookings (customer_name, customer_email, booking_time) VALUES

('Anna Müller', '[email protected]', '2023-04-30 15:00:00'),

('Benjamin Schmidt', '[email protected]', '2023-05-01 09:30:00'),

('Carina Bauer', '[email protected]', '2023-05-02 11:45:00'),

('David Koch', '[email protected]', '2023-05-03 13:15:00');

-- Seats table

INSERT INTO seats (screen_id, seat_number) VALUES

(1, 'A1'),

(1, 'A2'),

(1, 'A3'),

(2, 'B1'),

(2, 'B2'),

(3, 'C1'),

(3, 'C2'),

(3, 'C3'),

(4, 'D1'),

(4, 'D2'),

(4, 'D3'),

(5, 'E1'),

(5, 'E2'),

(5, 'E3');

INSERT INTO booking_details (booking_id, seat_id, showtime_id, price) VALUES

(1, 1, 1, 9.99),

(1, 2, 1, 9.99);