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', 'anna.mueller@example.com', '2023-04-30 15:00:00'),
('Benjamin Schmidt', 'benjamin.schmidt@example.com', '2023-05-01 09:30:00'),
('Carina Bauer', 'carina.bauer@example.com', '2023-05-02 11:45:00'),
('David Koch', 'david.koch@example.com', '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);