baza danych teatr (zapytanie SQL)

CREATE TABLE IF NOT EXISTS `actors` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `gender` varchar(1) NOT NULL,
 `birth_date` date NOT NULL,
 `description` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ;

INSERT INTO `actors` (`id`, `first_name`, `last_name`, `gender`, `birth_date`, `description`) VALUES
(1, 'Antonina', 'Choroszy', 'K', '1970-03-05', 'Od 3 lat działa w teatrze'),
(2, 'Stefan', 'Wleklak', 'M', '1984-04-05', 'Dostał Oskara za rolę myszki'),
(3, 'Andrzej', 'Krajna', 'M', '1985-02-05', 'Szczególnie angażowany w role książąt'),
(5, 'Stefan', 'Wleklak', 'M', '1984-04-05', 'Dostał Oskara za rolę myszki'),
(6, 'Wojciech', 'Łuczak', 'M', '1980-02-05', 'Dostał złotą malinę oraz oskara za króla denarów');

CREATE TABLE IF NOT EXISTS `performances` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `description` text DEFAULT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `performances` (`title`) VALUES
('Gra o tron'),
('Dexter'),
('Wikingowie'),
('Macbeth'),
('Hamlet'),
('Otello');


CREATE TABLE IF NOT EXISTS `performances_performed` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `performance_id` int(10) NOT NULL,
 `date` date NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO performances_performed (`id`, `performance_id`, `date`) VALUES
(1,'1', '2014-05-06-19-00-00'),
(2,'2', '2015-01-06-19-00-00'),
(3,'3', '2012-03-07-19-00-00');


CREATE TABLE IF NOT EXISTS `roles` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `actor_id` int(10) NOT NULL,
 `performance_id` int(10) NOT NULL,
 PRIMARY KEY (`id`)
);


CREATE TABLE IF NOT EXISTS `authors` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `nick` varchar(255) DEFAULT NULL,
 `description` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
);


INSERT INTO `authors` (`id`, `first_name`, `last_name`, `nick`, `description`) VALUES
(1, 'William', 'Shakespeare', NULL , 'dżentelmen z Londynu'),
(2, 'Dorota', 'Masłowska', 'Masłoska', 'Rocznik 1984'),
(3, 'T.S.', 'Eliot', NULL , 'Egzystencjalista trendsetter'),
(4, 'Samuel', 'Beckett', 'Sam', 'Geniusz');


CREATE TABLE IF NOT EXISTS `directors` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `nick` varchar(255) DEFAULT NULL,
 `description` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ;

INSERT INTO `directors` (`id`, `first_name`, `last_name`, `nick`, `description`) VALUES
(1, 'Anna', 'Kozłowska', NULL , 'Gwiazda'),
(2, 'Krzysztof', 'Warlikowski', NULL, 'Okrzyknięty najlepszym polskim reżyserem');

CREATE TABLE IF NOT EXISTS `reviewers` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `reviewers` (`id`, `name`, `last_name`) VALUES
(1, 'Łukasz', 'Drewniak'),
(2, 'Anna', 'Koza');

CREATE TABLE IF NOT EXISTS `reviews` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) DEFAULT NULL,
 `content` varchar(255) NOT NULL,
 `newspaper_title` varchar(255) DEFAULT NULL,
 `reviewer_id` int(10) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `reviews` (`id`, `title`, `content`, `newspaper_title`) VALUES
(1, 'Skandal w teatrze', 'Aktorka zdjęła bluzkę', 'Fakt'),
(2, 'Anioł to reżyserował', 'Tylko tyle', 'Super Express');




CREATE TABLE IF NOT EXISTS `photos` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `picture_path` varchar(255) NOT NULL,
 `photographer` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ;

CREATE TABLE IF NOT EXISTS `stages` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `stages` (`id`, `name`) VALUES
(1, 'Malarnia'),
(2, 'Scena Wielka');


CREATE TABLE IF NOT EXISTS `zones` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `stage_id` varchar(8) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `zones` (`id`, `name`, `stage_id`) VALUES
(1, 'Strefa VIP', 1),
(2, 'Balkon 1', 2),
(3, 'Strefa normal', 1),
(4, 'Parter', 2),
(5, 'Balkon 2', 2);

CREATE TABLE IF NOT EXISTS `seats` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `number` varchar(8) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `seats` (`id`, `number`) VALUES
(1, '1'),
(2, '2'),
(3, '3'),
(4, '4'),
(5, '5');

CREATE TABLE IF NOT EXISTS `tickets` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `price` varchar(8) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `tickets` (`id`, `price`) VALUES
(1, '100'),
(2, '200'),
(3, '35'),
(4, '40'),
(5, '50');


CREATE TABLE IF NOT EXISTS `tickets_printed` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `ticket_id` int(10) NOT NULL,
 `seat_id` int(10) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `tickets_printed` (`id`, `ticket_id`, `seat_id`) VALUES
(1, '1', '1');

CREATE TABLE IF NOT EXISTS `ticket_pricegroups` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO `ticket_pricegroups` (`id`, `name`) VALUES
(1, 'Emeryci'),
(2, 'Studenci'),
(3, 'Normalne'),
(4, 'Wejściówki');

RELACJE:

performances_performed_stages
#id
id_performance_performed
id_stage
-----------------------------
CREATE TABLE IF NOT EXISTS `performances_performed_stages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_performance_performed` int(10) NOT NULL,
  `id_stage` varchar(255) NOT NULL,
  );
--------------------------------------------------------------------
--------------------------------------------------------------------
zones_stages
#id
id_zone
id_stage
-------------------------------
CREATE TABLE IF NOT EXISTS `zones_stages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_zone` int(10) NOT NULL,
  `id_stage` varchar(255) NOT NULL,
  );
--------------------------------------------------------------------
--------------------------------------------------------------------
seats_zones
#id
id_seat
id_zone
-------------------------------
CREATE TABLE IF NOT EXISTS `seats_zones` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_seat` int(10) NOT NULL,
  `id_zone` varchar(255) NOT NULL,
  );
--------------------------------------------------------------------
-------------------------------------------------------------------- 
roles_actor_replacements
#id
id_role
id_actor
start_date
finish_date
-------------------------------
CREATE TABLE IF NOT EXISTS `roles_actor_replacements` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_role` int(10) NOT NULL,
  `id_actor` varchar(255) NOT NULL,
  `start_date` date NOT NULL,
  `finish_date` date,
  );
--------------------------------------------------------------------
-------------------------------------------------------------------- 
tickets_performances_performed
#id
id_performance performed
id_ticket
-------------------------------
CREATE TABLE IF NOT EXISTS `tickets_performances_performed` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_performance performed` int(10) NOT NULL,
  `id_ticket` int(10) NOT NULL,
  );
--------------------------------------------------------------------
--------------------------------------------------------------------  
tickets_printed_tickets_price_groups
#id
id_ticket_printed
id_ticket_price_group
-------------------------------
CREATE TABLE IF NOT EXISTS `tickets_printed_tickets_price_groups` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `id_ticket_printed` int(10) NOT NULL,
  `id_ticket_price_group` int(10) NOT NULL,
  );
Reklamy

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s