LR4.Tyutterin_Yakov_Z1411

Лабораторная работа № 4. ФИЗИЧЕСКАЯ РЕАЛИЗАЦИЯ РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ НА СЕРВЕРЕ MYSQL

Цели работы: Физическая реализация реляционной БД на сервере MySql.

Вариант 18. Занятость актеров театра. Работников театра можно подразделить на актеров, музыкантов, постановщиков и служащих. Каждая из перечисленных категорий имеет уникальные атрибуты-характеристики и может подразделяться (например, постановщики) на более мелкие категории. Театр возглавляет директор, в функции которого входят контроль за постановками спектаклей, утверждение pепеpтуаpа, принятие на работу новых служащих, приглашение актеров и постановщиков. Актеры, музыканты и постановщики, работающие в театре, могут уезжать на гастроли. Актеры театра могут иметь звания заслуженных и народных артистов, могут быть лауреатами конкурсов. Также актерами театра могут быть и студенты театральных училищ. Каждый актер имеет свои вокальные и внешние данные (пол, возраст, голос, pост и т.п.), которые могут подходить для каких-то pолей, а для каких-то нет (не всегда женщина может сыграть мужчину и наоборот). Для постановки любого спектакля необходимо подобрать актеров на роли и дублеров на каждую главную pоль. Естественно, что один и тот же актер не может играть более одной pоли в спектакле, но может играть несколько pолей в различных спектаклях. У спектакля также имеется pежисеp-постановщик, художник-постановщик, диpижеp- постановщик, автор. Спектакли можно подразделить по жанрам: музыкальная комедия, трагедия, оперетта и пр. С другой стороны, спектакли можно подразделить на детские, молодежные и пр. В pепеpтуаpе театра указывается какие спектакли, в какие дни и в какое время будут проходить, а также даты пpемьеp. В кассах театра можно заранее приобрести билеты или абонемент на любые спектакли. Абонемент обычно включает в себя билеты на спектакли либо конкретного автора, либо конкретного жанра. Цена билетов зависит от места, и спектакля. На премьеры билеты дороже. Администрацией театра фиксируется количество проданных билетов на каждый спектакль.

Выполнил скрипт для создания структуры таблиц БД:

Скрипт:

CREATE TABLE IF NOT EXISTS genres (

id integer primary key auto_increment not null, name varchar(15) not null unique

);

CREATE TABLE IF NOT EXISTS categories (

id integer primary key auto_increment not null, name varchar(15) not null unique

);

CREATE TABLE IF NOT EXISTS positions (

id integer primary key auto_increment not null, name varchar(15) not null UNIQUE

);

CREATE TABLE IF NOT EXISTS achievements (

id integer primary key auto_increment not null, name varchar(30) not null UNIQUE

);

CREATE TABLE IF NOT EXISTS workers (

id integer primary key auto_increment not null, first_name varchar(30) not null,

last_name varchar(30) not null, patronymic varchar(30),

gender char(1) not null, salary DECIMAL(10,2) not null, date_of_birth date not null, voice integer,

height integer,

CONSTRAINT CHECK_GENDER CHECK (gender in('M','F','T'))

);

CREATE TABLE IF NOT EXISTS theaters (

id integer primary key auto_increment not null, title varchar(30) not null,

director_id integer not null,

FOREIGN KEY (director_id) REFERENCES workers (id)

);

CREATE TABLE IF NOT EXISTS shows (

id integer primary key auto_increment not null, title varchar(20) not null,

date timestamp not null, duration integer not null, theatre_id integer not null,

FOREIGN KEY (theatre_id) REFERENCES theaters (id)

);

CREATE TABLE IF NOT EXISTS roles (

id integer primary key auto_increment not null, name varchar(30) not null UNIQUE

);

CREATE TABLE IF NOT EXISTS subscriptions (

id integer primary key auto_increment not null, price DECIMAL(10,2) not null

);

CREATE TABLE IF NOT EXISTS tickets (

id integer primary key auto_increment not null, show_id integer not null,

available bool default true not null, subscription_id integer,

FOREIGN KEY (subscription_id) REFERENCES subscriptions (id),

FOREIGN KEY (show_id) REFERENCES shows (id)

);

CREATE TABLE IF NOT EXISTS genre_show ( show_id integer not null, genre_id integer not null,

CONSTRAINT PK_GENRE_SHOW PRIMARY KEY genre_show (show_id, genre_id),

FOREIGN KEY (show_id) REFERENCES shows (id), FOREIGN KEY (genre_id) REFERENCES genres (id)

);

CREATE TABLE IF NOT EXISTS achievement_worker ( achievement_id integer not null, worker_id integer not null,

CONSTRAINT PK_WORKER_ACHIVEMENT PRIMARY KEY achievement_worker (achievement_id, worker_id),

FOREIGN KEY (achievement_id) REFERENCES achievements (id), FOREIGN KEY (worker_id) REFERENCES workers (id)

);

CREATE TABLE IF NOT EXISTS position_worker ( position_id integer not null, worker_id integer not null,

theater_id integer not null,

CONSTRAINT PK_WORKER_POSITION PRIMARY KEY position_worker (position_id, worker_id, theater_id),

FOREIGN KEY (position_id) REFERENCES positions (id), FOREIGN KEY (worker_id) REFERENCES workers (id), FOREIGN KEY (theater_id) REFERENCES theaters (id)

);

CREATE TABLE IF NOT EXISTS category_show ( category_id integer not null, show_id integer not null,

CONSTRAINT PK_GENRE_SHOW PRIMARY KEY category_show (category_id, show_id),

FOREIGN KEY (category_id) REFERENCES categories (id),

FOREIGN KEY (show_id) REFERENCES shows (id)

);

CREATE TABLE IF NOT EXISTS role_show_worker ( show_id integer not null,

role_id integer not null, worker_id integer not null,

CONSTRAINT PK_ROLE_SHOW_WORKER PRIMARY KEY role_show_worker (show_id, worker_id, role_id),

FOREIGN KEY (show_id) REFERENCES shows (id), FOREIGN KEY (role_id) REFERENCES roles (id),

FOREIGN KEY (worker_id) REFERENCES workers (id)

);

Результат выполнения SELECT-запроса для получения записей из таблицы Genres

Также сразу можно заполнить таблицу категорий спектаклей. За основу возьмем возрастные категории(MPA):

Результат выполнения SELECT-запроса:

Заполним таблицу positions(позиции работников) учитывая то, что названия позиций уникальны.

Результат выполнения SELECT-запроса:

После этого можем связать работников с их позициями, занимаемыми в конкретном театре.

INSERT INTO position_worker(position_id, worker_id, theater_id) VALUES (9, 7, 1),

(9, 8, 1), (9, 9, 1), (9, 10, 1), (9, 11, 1), (9, 12, 1), (13, 8, 1), (15, 9, 1), (10, 5, 1), (11, 1, 1), (16, 2, 1), (14, 3, 1), (12, 4, 1), (15, 6, 1);