C, PHP, VB, .NET

На представената ER диаграма е моделирана база от данни за прожекциите на филми в различни кина. Всяко кино (Theater, за които пазим уникален номер, уникални име и град в който се намира) има по няколко зали (Screen, за които пазим номер на залата и тип – един от три възможни: normal, deluxe и VIP), които правят прожекции (Show, за които пазим време на излъчване на прожекциите и брой на зрители, които са си закупили билети) на определени филми (Movie, за които пазим уникален номер, уникално име, година и държава, в която са снимани). Специално отбелязваме, че номера на зала (screen.no) НЕ е първичен ключ – този номер отбелязва зала 1, 2, 3 и т.н. във всяко кино и не е уникален идентификатор за зала в системата.

Задача 1. Създайте базата от данни на езика SQL, като внимателно подбирате подходящи типове за данните. Бъдете внимателни при определянето на първичните ключове – на ER диаграмата са обозначени само ключовете на Movie и Theater, а за останалите две таблици трябва вие сами да определите кои колони ще формират първичен ключ. Обяснете със свободен текст защо точно по този начин сте определили първичните ключове на тези таблици.

Решение: Първо създаваме таблиците theater и movie:

CREATE TABLE theater(
  id SMALLINT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL,
  city VARCHAR(64) NOT NULL,
  UNIQUE(name, city),
  PRIMARY KEY(id)
);

CREATE TABLE movie(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL,
  year YEAR(4) NOT NULL,
  country VARCHAR(64) NOT NULL,
  PRIMARY KEY(id)
);

В таблица screen е отбелязано специално, че ние трябва да подберем първичния ключ. Подходяща комбинация от колони от целочислен тип са theater_id и no, защото те уникално идентифицират всяка зала. За ваше удобно е възможно е и добавянето на нова колона "id" в тази таблица, но това не отменя нуждата от въвеждане на ключ UNIQUE(theater_id, no). Ще реализираме първото решение (обърнете внимание на обяснението под таблица shows - то е свързано с този избор):

CREATE TABLE screen(
  no TINYINT UNSIGNED NOT NULL,
  type ENUM("normal", "VIP", "deluxe") NOT NULL,
  theater_id SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY(theater_id) REFERENCES theater(id),
  PRIMARY KEY(no, theater_id)
);

Остана таблица shows. Тя изпълнява ролята на връзка M:M, в която се пази допълнителна информация - времето на излъчване и броя на посетителите. Първичният ѝ ключ ще бъде съставен от комбинацията идентификатор на зала и време на излъчване, понеже в една зала не е позволено да има прожекция на един и същи филм по едно и също време:

CREATE TABLE shows(
  visitors TINYINT UNSIGNED NOT NULL,
  time DATETIME NOT NULL,
  movie_id INT UNSIGNED NOT NULL,
  screen_no TINYINT UNSIGNED NOT NULL,
  theater_id SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY(movie_id) REFERENCES movie(id),
  FOREIGN KEY(screen_no, theater_id)
          REFERENCES screen(no, theater_id),
  PRIMARY KEY(time, screen_no, theater_id)
);

Забележете, че реализирано по този начин ние имаме удобна директна връзка между shows и theater - shows.theater_id отговаря на theater.id! Тя не е маркирана като FOREIGN KEY, но въпреки това изпълнява тази функция (възможно е да се направи и FK, но ще бъде излишно, понеже screen се грижи за валидността на своето theater_id, а от там и shows също ще го направи).

Задача 2. Изведете списък с имената на кината, номерата на залите и времената на излъчване за филм с име „Fast and furious 7”, но само за зали със статус “VIP” или “deluxe”. Сортирайте списъка по азбучен ред първо по имена на кина, а след това по номера на зали.

Решение: Задачата е в общи линии тривиална комбинация от JOIN и вложен SELECT в WHERE:

SELECT theater.name, screen.no, shows.time
FROM theater 
JOIN screen 
     ON theater.id = screen.theater_id
JOIN shows 
     ON (screen.no, screen.theater_id) 
        = (shows.screen_no, shows.theater_id)
WHERE screen.type IN ("VIP", "deluxe")
        AND
      shows.movie_id IN(
        SELECT movie.id FROM movie
        WHERE name="Fast and furious 7"
      )
ORDER BY theater.name, screen.no;

Задача 3. Намерете общия брой на хората, които са гледали филма „Fast and furious 7” във VIP зала на кино с име “Arena Mladost”.

Решение: Тук също имаме стандартна заявка с агрегатна функция, в която има по-сложно условие WHERE с няколко вложени SELECT:

SELECT SUM(visitors)
FROM shows
WHERE (screen_no, theater_id) IN(
        SELECT no, theater_id
        FROM screen
        WHERE type="VIP"
                AND
              theater_id IN(
                 SELECT id
                 FROM theater
                 WHERE name="Arena Mladost"
              )
      )
        AND
      movie_id IN(
        SELECT id FROM movie
        WHERE name="Fast and furious 7"
      );