#import "@docs/bmstu:1.0.0":*
#import "@preview/tablex:0.0.8": tablex, rowspanx, colspanx, cellx
#let work(content) = {
  set page(
    paper: "a4",
    margin: (left: 30mm, right: 15mm, top: 20mm, bottom: 20mm),
    numbering: "1"
  )
  set text(font: "Times New Roman", size: 14pt, lang: "ru")
 
  set list(marker: [--])
  // Покажем основное содержимое работы.
  {
    let indent = 1.25cm
    set list(indent: indent)

    set heading(numbering:
    (..nums) => {
      if (nums.pos().len() == 2) {nums.pos().at(1)} 
      if (nums.pos().len() == 3) [#{nums.pos().at(1)}.#{nums.pos().at(2)}]; 
    })
    show heading.where(level:1): it => {
      // if breakfirst == true {pagebreak()}
      set text(16pt, hyphenate: false)
      it
    }
    show heading.where(level:2): it => {
      set text(16pt, hyphenate: false)
      it
    }
    show heading.where(level:3): it => {
      set text(14pt, hyphenate: false)
      it
    }
    set par(justify: true, first-line-indent: indent)
    show list: it => {
      for p in (..it.children){[#par([--#h(5pt) #p.body])]}    
    }
    set enum(indent: 1.25cm)
    show enum: it => {
      let listit = counter("1")
      listit.update(0)
      for p in (..it.children){[#par([#listit.step() #listit.display(). #p.body])]}    
    }

    show raw: it => {
      box(
        inset: (x:6pt, y:0pt),
        outset: (y:3pt),
        stroke: luma(0),
        align(left)[#it]
      )
    }
    content
  }
}
#let theme = "themes/Catppuccin Latte.tmTheme"
#let parserasm(lst) = {
  let carr = ()
  let i = 0
  let fnc = ""
  let nocol = 1
  lst = lst.split("")
  let func = 0
  while (i < lst.len()) {
    let dict = (
      text: "",
      tab : 0,
      func: func,
      nocol: nocol,
      len: 0,
    )
    let predi = i
    let tab = true
    while (i < lst.len() and lst.at(i)!="\n"){
      if (tab and lst.at(i)==" "){
        dict.tab+=1
      }
      if (tab and lst.at(i) != " "){
        if (dict.tab == 0){
          func = nocol
        }
        tab = false
      }
      dict.text += lst.at(i)
      i += 1
    }
    dict.len = i - predi
    dict.func = func
    carr.push(dict)
    nocol += 1
    i += 1
  }
  return carr
}
#let parsercpp(lst) = {
  let carr = ()
  let i = 0
  let fnc = ""
  let nocol = 1
  lst = lst.split("")
  let func = 0
  while (i < lst.len()) {
    let dict = (
      text: "",
      tab : 0,
      func: func,
      nocol: nocol,
      len: 0,
    )
    let predi = i
    let tab = true
    while (i < lst.len() and lst.at(i)!="\n"){
      if (tab and lst.at(i)==" "){
        dict.tab+=1
      }
      if (tab and lst.at(i) != " "){
        if (dict.tab == 0){
          func = nocol
        }
        tab = false
      }
      dict.text += lst.at(i)
      i += 1
    }
    dict.len = i - predi
    dict.func = func
    carr.push(dict)
    nocol += 1
    i += 1
  }
  return carr
}

#let writeft(lst, f, t) = {
  let fi = 0
  let i = 0
  let fo = 0
  let out = ""
  let nlst = ()
  lst = lst.split("\n")
  while (i < lst.len()) {
    i += 1
    if (i >= f and i <= t) {
      nlst.push(lst.at(i))
    }
  }
  return nlst.join("\n")
}

#let funcno(carr, no) = {
  let out = ""
  for i in carr {
    if (i.func == no){
      out += i.text + "\n"
    }
  }
  return out
}

#let funcstr(carr, func) = {
  let out = ""
  for i in carr {
    if (i.text == func){
      return funcno(carr, i.func)
    }
  }
  return ""
}
#let lst = counter("listing")


#let code(data, lang, lable, num: false, size: 14pt) = {
  set par(justify: true, first-line-indent: 0pt)
  lst.step()
  align(left)[ 
    Листинг #lst.display() #sym.bar.h #lable
  ]
  v(-8pt)
  set text(size)
  if (num){
    show raw.line: it => {
      if (it.number < 10){h(0.6em)}
      text(fill: gray)[#it.number]
      // text(fill: gray)[|]
      it.body
    }
      raw(data, lang:lang, theme:theme)
  } else {
      raw(data, lang:lang, theme:theme)
  }
  set text(14pt)
  set par(justify: true, first-line-indent: 1.25pt)
}
#show par: it => {
  it
}
#set raw(theme:theme)
#pagebreak()
#counter(page).update(2)
#pagebreak()
#show: work
#set page(numbering:"1")
#align(center)[*РЕФЕРАТ*]
Рассчетно-пояснительная записка состоит из 43 страницы, включающих 20 рисунков, 37 листингов, 10 таблиц и 4 приложения.

Перечень ключевых слов: База данных, PostgreSQL, Python, Ruby, PGAdmin4, Актеры театра, Занятость, Репетиции, Сцены, Роли, Инфологическая модель, Датологическая модель, Бизнеспроцессы, СУБД, Реализация базы данных

Целью создания данной базы данных является организация и хранение структурированной информации о занятости актеров. 

Для реализации этой базы данных используются следующие инструменты и технологии:

- Система управления базами данных (СУБД) PostgreSQL: мощная объектно-реляционная СУБД, обеспечивающая высокую производительность, надежность и расширяемость для управления данными.
- Языки программирования Python и Ruby: используются для написания скриптов и выполнения операций с базой данных, таких как вставка и обновление данных.
- Среда разработки PGAdmin4: универсальная многофункциональная программа для работы с базами данных, предоставляющая удобный графический интерфейс для разработки, администрирования и управления базами данных.
#pagebreak()
#set par(justify: true, first-line-indent: 0cm)
#align(center)[*СОДЕРЖАНИЕ*]
#v(-15pt)#h(-35pt)#outline(title: [], indent: n => {if (n >1){h(1em)}})
#set par(justify: true, first-line-indent: 1.25cm)
#show heading.where(level:1): it => {
      pagebreak()
      align(center)[#text(16pt, hyphenate: false)[#upper[#it]]]
}
= Введение

Цель данной курсовой работы — разработка комплексной базы данных для организации, хранения и управления структурированной информацией о занятости актеров театра. Эта база данных будет предназначена для эффективного учета и отслеживания всех аспектов участия актеров в театральных постановках, включая данные о репетициях, спектаклях, ролях и контрактах.

Основная задача этой базы данных — централизовать и упростить хранение данных, что позволит улучшить доступ к информации, упростить её обработку и анализ, а также повысить прозрачность и эффективность работы театрального коллектива.

Основная проблема, которую необходимо решить с помощью этой базы данных, — эффективное управление большими объемами разнородной информации, связанной с занятостью актеров. В настоящее время данные часто хранятся в разных форматах и местах, что затрудняет их поиск и использование. Кроме того, отсутствие централизованного хранилища данных может привести к потере информации, дублированию записей и увеличению времени на обработку запросов.

Актуальность данной работы обусловлена ростом числа выпускников актерских вузов, которые будут нуждаться в работе в театре.  Эффективное управление данными позволит сократить время на поиск и обработку информации, повысить точность и надежность данных о занятости актеров, а также улучшить взаимодействие между различными подразделениями театра.

Для достижения поставленных целей необходимо решить следующие задачи:

- Разработать структуру базы данных для удобного и эффективного хранения информации о репетициях, спектаклях, ролях и контрактах.
- Реализовать функциональность для учета актеров, включая сохранение их данных и фотографий.
- Организовать хранение информации о спектаклях, включая название, бюджет, год постановки и возрастные ограничения.
- Обеспечить учет занятости актеров, включая их участие в репетициях и спектаклях, а также распределение ролей.
- Управлять информацией о контрактах, включая даты приема и увольнения, а также ставки.
#pagebreak()
== Анализ предметной области

Для анализа предметной области занятости актеров театра были выявлены несколько ключевых сущностей, которые необходимы для полного и точного отражения всех аспектов функционирования театра. Эти сущности включают информацию о актерах, спектаклях, контрактах, занятости в спектаклях, репетициях, пьесах, сценах и ролях. Каждая сущность играет важную роль в обеспечении целостности и полноты данных, необходимых для эффективного управления занятостью актеров театра.

Ниже приведен анализ каждой из выявленных сущностей:

- *Актеры* - включает информацию о театральных актерах, например ФИО, дата рождения, фото, стаж работы и звания/награды.

- *Спектакли* - содержит информацию о театральных спектаклях, включая название, бюджет, год постановки, возрастное ограничение и код пьесы.

- *Контракт* - охватывает данные о контрактах актеров, включая дату приема, дату увольнения и ставку.

- *Занятость в спектаклях* - включает информацию о занятости актеров в спектаклях.

- *Репетиции* - содержит данные о репетициях спектаклей, такие как дата и время, продолжительность, является ли репетиция читкой и код спектакля.

- *Сцены в репетициях* - охватывает информацию о сценах, репетируемых в конкретных репетициях.

- *Пьесы* - включает данные о пьесах, таких как название, год выпуска и автор.

- *Сцены* - содержит информацию о сценах в пьесах, включая название, продолжительность и код пьесы.

- *Роли* - охватывает данные о ролях, исполняемых актерами в пьесах, включая название роли, является ли роль главной, код пьесы и код актера.

- *Занятость ролей в сценах* - включает информацию о занятости ролей в конкретных сценах.

Этот анализ помогает структурировать данные для эффективного управления занятостью актеров в театре и обеспечивает точное и полное представление всех аспектов театральной деятельности.

=== Инфологическая модель базы данных

Исходя из сущностей и их свойств, построим инфологическую модель базы данных. 

#img(image("1.png", width:87%), [Инфологическая модель базы данных])


=== Датологическая модель базы данных

На основе анализа предметной области театра и выявленных ключевых сущностей и их взаимосвязей, можно построить ERD-диаграмму (датологическую модель базы данных).

#img(image("2.png", width:87%), [Датологическая модель базы данных])

На основе этой диаграммы создадим 10 таблиц.

=== Таблицы базы данных

#h(-35pt)
Таблица 1 -- Актеры
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]  ,[ Описание]                   ,[ Тип          ],
[КодАктера]         ,[ Код актера (первичный ключ)],[ ```sql SERIAL```       ],
[ФИО]               ,[ ФИО]                        ,[ ```sql VARCHAR(50)```  ],
[ДатаРождения]      ,[ Дата рождения]              ,[ ```sql DATE```         ],
[Фото]              ,[ Фото]                       ,[ ```sql VARCHAR(100)``` ],
[СтажРаботы]        ,[ Стаж работы]                ,[ ```sql VARCHAR(100)``` ],
[ЗваниеНаграды]     ,[ Звания и награды]           ,[ ```sql VARCHAR(50)[]```],
)
#pagebreak()
Таблица 2 -- Спектакли
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]          ,[ Описание]                      ,[ Тип          ],
[КодСпектакля]              ,[ Код спектакля (первичный ключ)],[ ```sql SERIAL```       ],
[Название]                  ,[ Название]                      ,[ ```sql VARCHAR(50)```  ],
[Бюджет]                    ,[ Бюджет]                        ,[ ```sql INTEGER```      ],
[ГодПостановки]             ,[ Год постановки]                ,[ ```sql INTEGER```      ],
[ОграничениеПоВозрасту]     ,[ Ограничение по возрасту]       ,[ ```sql VARCHAR(3)```   ],
[КодПьесы]                  ,[ Код пьесы]                     ,[ ```sql SERIAL```       ]
)
#v(20pt)
Таблица 3 -- Контракт
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]  ,[ Описание]                      ,[ Тип     ],
[КодКонтракта]      ,[ Код контракта (первичный ключ)],[ ```sql SERIAL```  ],
[ДатаПриема]        ,[ Дата приема]                   ,[ ```sql DATE```    ],
[ДатаУвольнения]    ,[ Дата увольнения]               ,[ ```sql DATE```    ],
[Ставка]            ,[ Ставка]                        ,[ ```sql INTEGER``` ],
[КодАктера]         ,[ Код актера (внешний ключ)     ],[ ```sql SERIAL```  ]
)
#v(20pt)
Таблица 4 -- Занятость в спектаклях
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]  ,[ Описание]                      ,[ Тип     ],
[КодЗанятости]      ,[ Код занятости (первичный ключ)],[ ```sql SERIAL```  ],
[КодСпектакля]      ,[ Код спектакля (внешний ключ)  ],[ ```sql SERIAL```  ],
[КодАктера]         ,[ Код актера (внешний ключ)     ],[ ```sql SERIAL```  ]
)
#pagebreak()
Таблица 5 -- Репетиции
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]  ,[ Описание]                      ,[ Тип         ],
[КодРепетиции]      ,[ Код репетиции (первичный ключ)],[ ```sql SERIAL```      ],
[ДатаВремя]         ,[ Дата и время]                  ,[ ```sql TIMESTAMP```   ],
[Продолжительность] ,[ Продолжительность]             ,[ ```sql INTERVAL```    ],
[Читка]             ,[ Читка]                         ,[ ```sql BOOLEAN```     ],
[КодСпектакля]      ,[ Код спектакля (внешний ключ)  ],[ ```sql SERIAL```      ]
)
#v(20pt)
Таблица 6 -- Сцены в репетициях
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]  ,[ Описание]                              ,[ Тип     ],
[КодСценыВРепетиции],[ Код сцены в репетиции (первичный ключ)],[ ```sql SERIAL```  ],
[КодРепетиции]      ,[ Код репетиции (внешний ключ)          ],[ ```sql SERIAL```  ],
[КодСцены]          ,[ Код сцены (внешний ключ)              ],[ ```sql SERIAL```  ]
)
#v(20pt)
Таблица 7 -- Пьесы
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]  ,[ Описание]                   ,[ Тип         ],
[КодПьесы]          ,[ Код пьесы (первичный ключ)],[ ```sql SERIAL```      ],
[Название]          ,[ Название]                   ,[ ```sql VARCHAR(50)``` ],
[ГодВыпуска]        ,[ Год выпуска]                ,[ ```sql INTEGER```     ],
[Автор]             ,[ Автор]                      ,[ ```sql VARCHAR(50)``` ]
)
#pagebreak()
Таблица 8 -- Сцены
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]  ,[ Описание]                      ,[ Тип         ],
[КодСцены]          ,[ Код сцены (первичный ключ)    ],[ ```sql SERIAL```      ],
[Название]          ,[ Название]                      ,[ ```sql VARCHAR(50)``` ],
[Продолжительность] ,[ Продолжительность]             ,[ ```sql INTERVAL```    ],
[КодПьесы]          ,[ Код пьесы (внешний ключ)      ],[ ```sql SERIAL```      ]
)
#v(20pt)
Таблица 9 -- Роли
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]  ,[ Описание]                      ,[ Тип         ],
[КодРоли]           ,[ Код роли (первичный ключ)     ],[ ```sql SERIAL```      ],
[НазваниеРоли]      ,[ Название роли]                 ,[ ```sql VARCHAR(50)``` ],
[Главная]           ,[ Главная роль]                  ,[ ```sql BOOLEAN```     ],
[КодПьесы]          ,[ Код пьесы (внешний ключ)      ],[ ```sql SERIAL```      ],
[КодАктера]         ,[ Код актера (внешний ключ)     ],[ ```sql SERIAL```      ]
)
#v(20pt)
Таблица 10 -- Занятость роли в сценах
#v(-10pt)
  #tablex(
    columns: 3,
    inset: 10pt,
    align: center + horizon,
[Название колонки]  ,[ Описание]                                    ,[ Тип     ],
[КодЗанятостиРоли]  ,[ Код занятости роли в сценах (первичный ключ)],[ ```sql SERIAL```  ],
[КодРоли]           ,[ Код роли (внешний ключ)                     ],[ ```sql SERIAL```  ],
[КодСцены]          ,[ Код сцены (внешний ключ)                    ],[ ```sql SERIAL```  ]
)
#pagebreak()
=== Разработка бизнес-процессов предметной области

1. Регистрация нового актера

   #h(-35pt)
     [Запрос на регистрацию на роль получен] $arrow$ (Проверка полноты данных) $arrow$ [Данные полные] $arrow$ (Регистрация актера) $arrow$ [Актер зарегистрирован] $arrow$ (Заключение контракта с актером) $arrow$ [Контракт заключен]

2. Перенос репетиций и спектакля 

   #h(-35pt)
   [Запрос на перенос получен]$arrow$(Изменение времени проведения спектакля)$arrow$[Спектакль перенесен]$arrow$(Проверка занятости актеров, с учетом занятости перенос репетиций)$arrow$ [Репетиции перенесены]

3. Постановка спектакля 

   #h(-35pt)
   (Поиск пьесы)$arrow$
   [Пьеса найдена]$arrow$
   (Распределение ролей)$arrow$
   [Роли распределены]$arrow$
   (Читка)$arrow$
   [Читка проведена]$arrow$
   (Распределение сцен и репетиций)$arrow$
   [Сцены и репетиции распределены]$arrow$
   (Репетиции)$arrow$
   [Репетиции завершены]$arrow$
   (Назначение премьеры)$arrow$
   [Спектакль поставлен]

== Реализация базы данных

=== Создание базы данных и таблиц

После анализа предметной области и создания датологической и инфологической моделей базы данных, приступим к ее реализации. Для анчала создадим саму базу данных в PostgreSQL (листинг 1).

#let ct = parserasm(read("createTables.sql"))
#code(funcstr(ct, "CREATE DATABASE Занятость_актеров_театра"), "sql", [Создаем новую базу данных])


Разработаем, на основании выделенных сущностей, таблицы в PostgreSQL (листинги 2-11).
#pagebreak()
#code(funcstr(ct, "CREATE TABLE Актеры (")+");", "sql", [Таблица Актеры])
#code(funcstr(ct, "CREATE TABLE Спектакли (")+");", "sql", [Таблица Спектакли])
#code(funcstr(ct, "CREATE TABLE Контракт (")+");", "sql", [Таблица Контракт])
#code(funcstr(ct, "CREATE TABLE ЗанятостьВСпектаклях (")+");", "sql", [Таблица ЗанятостьВСпектаклях])
#code(funcstr(ct, "CREATE TABLE Репетиции (")+");", "sql", [Таблица Репетиции])
#pagebreak()
#code(funcstr(ct, "CREATE TABLE СценыВРепетициях (")+");", "sql", [Таблица СценыВРепетициях])
#code(funcstr(ct, "CREATE TABLE Пьесы (")+");", "sql", [Таблица Пьесы])
#code(funcstr(ct, "CREATE TABLE Сцены (")+");", "sql", [Таблица Сцены])
#code(funcstr(ct, "CREATE TABLE Роли (")+");", "sql", [Таблица Роли])
#code(funcstr(ct, "CREATE TABLE ЗанятостьРолиВСценах (")+");", "sql", [Таблица ЗанятостьРолиВСценах])

=== Связывание таблиц

После создания таблиц необходимо вторичные ключи связать с первичными, для этого немного изменим таблицы (Листинги 12-24):

#v(10pt)
#pagebreak()
#code("ALTER TABLE Контракт
ADD CONSTRAINT fk_Контракт_Актеры FOREIGN KEY (КодАктера)
REFERENCES Актеры (КодАктера);", "sql", [Добавление внешнего ключа, связывающего\ актеров и контракты])

#v(10pt)
#code("ALTER TABLE ЗанятостьВСпектаклях
ADD CONSTRAINT fk_ЗанятостьВСпектаклях_Актеры FOREIGN KEY (КодАктера)
REFERENCES Актеры (КодАктера);", "sql", [Добавление внешнего ключа, связывающего\ актеров и их занятость в спектаклях])


#v(20pt)
#code("ALTER TABLE ЗанятостьВСпектаклях
ADD CONSTRAINT fk_ЗанятостьВСпектаклях_Спектакли FOREIGN KEY (КодСпектакля)
REFERENCES Спектакли (КодСпектакля);", "sql", [Добавление внешнего ключа, связывающего\ спектакли и занятость актеров в них])


#v(10pt)
#code("ALTER TABLE Репетиции
ADD CONSTRAINT fk_Репетиции_Спектакли FOREIGN KEY (КодСпектакля)
REFERENCES Спектакли (КодСпектакля);", "sql", [Добавление внешнего ключа, связывающего\ спектакли и репетиции])


#v(10pt)
#code("ALTER TABLE СценыВРепетициях
ADD CONSTRAINT fk_СценыВРепетициях_Репетиции FOREIGN KEY (КодРепетиции)
REFERENCES Репетиции (КодРепетиции);", "sql", [Добавление внешнего ключа, связывающего\ сцены в репетициях и репетиции])


#v(10pt)
#code("ALTER TABLE СценыВРепетициях
ADD CONSTRAINT fk_СценыВРепетициях_Сцены FOREIGN KEY (КодСцены)
REFERENCES Сцены (КодСцены);", "sql", [Добавление внешнего ключа, связывающего\ сцены в репетициях и сцены])


#v(10pt)
#pagebreak()
#code("ALTER TABLE Сцены
ADD CONSTRAINT fk_Сцены_Пьесы FOREIGN KEY (КодПьесы)
REFERENCES Пьесы (КодПьесы);", "sql", [Добавление внешнего ключа, связывающего\ пьесы и сцены])


#v(10pt)
#code("ALTER TABLE Роли
ADD CONSTRAINT fk_Роли_Пьесы FOREIGN KEY (КодПьесы)
REFERENCES Пьесы (КодПьесы);", "sql", [Добавление внешнего ключа, связывающего\ пьесы и роли])


#v(10pt)
#code("ALTER TABLE Роли
ADD CONSTRAINT fk_Роли_Актеры FOREIGN KEY (КодАктера)
REFERENCES Актеры (КодАктера);", "sql", [Добавление внешнего ключа, связывающего\ актеров и роли])


#v(10pt)
#code("ALTER TABLE ЗанятостьРолиВСценах
ADD CONSTRAINT fk_ЗанятостьРолиВСценах_Роли FOREIGN KEY (КодРоли)
REFERENCES Роли (КодРоли);", "sql", [Добавление внешнего ключа, связывающего\ занятость роли в сценах и роли])


#v(10pt)
#code("ALTER TABLE ЗанятостьРолиВСценах
ADD CONSTRAINT fk_ЗанятостьРолиВСценах_Сцены FOREIGN KEY (КодСцены)
REFERENCES Сцены (КодСцены);", "sql", [Добавление внешнего ключа, связывающего\ занятость роли в сценах и сцены])


#v(10pt)
#code("ALTER TABLE Спектакли
ADD CONSTRAINT fk_Спектакли_Пьесы FOREIGN KEY (КодПьесы)
REFERENCES Пьесы (КодПьесы);", "sql", [Добавление внешнего ключа, связывающего\ пьесы и спектакли])

=== Заполнение базы данных

Для начала необходимо продумать заполнение базы данных. Основной таблицей базы данных будет таблица занятости актеров в спектаклях -- в ней будет больше всего записей. По условию, данных записей должно быть не меньше миллиона. Количество актеров возьмем меньшее -- 1000 человек. Таким образом контрактов тоже должно быть не меньше 1000 (предположим, что у всех актеров один контракт -- контрактов тоже будет ровно 1000). Количество спектаклей возьмем побольше -- 2000 записей. Для одного спектакля в среднем требуется 30-40 репетиций, поэтому количество репетиций возьмем равным 80000. Количество сцен возьмем в 2 раза меньше -- 40000, а количество пьес пусть будет равно количеству спектаклей -- 2000. Количество ролей в спектакле совпадет с количеством занятости актеров, так как на одну роль предусмотрен один актер, то есть ролей тоже будет 1000000.

Для данного набора данных логическая составляющая все еще не очень верная, так как актер не может играть в 1000 спектаклях. Но если увеличивать количество актеров хотя бы до 100000 база данных будет весить около 60 Гб. Поэтому возьмем количество актеров меньшее, чем того требует логика, для тестирования хранения и управления данными этого хватит.

Перед тем, как перейти к заполнению таблиц необходимо разобраться с фото. Основных способов хранения фото в БД всего 2: 
1. Хранение фото на внешнем носителе/в интернете, а в базе только ссылки/пути, таким образом сама база будет весить меньше, но это будет требовать дополнительной обработки, постоянный доступ к ресурсам, то есть уменьшается централизованность данных
2. Хранение фото в виде бинарной строки непосредственно в базе данных. Этот способ позволяет хранить фото непосредственно внутри базы.

Так как фото будут храниться в небольшом разрешении и для повышения централизованности данных я выбрал 2 способ хранения. 

Сгенерировать осмысленные текстовые данные с помощью кода достаточно просто, но генерация картинок требует большой вычислительной мощности и хорошего обучения, поэтому для генерации фотографий актеров воспользуемя интернет-сервисом по генерации фото несуществующих людей #link("https://thispersondoesnotexist.com/"). Сервис не имеет защиты от ботов, поэтому напишем код, который 1000 раз обновит страницу и сохранит все 1000 сгенерированных людей в одну папку (Приложение А).

Добавление больших данных в базу требует небольшой подготовки. Необходимо отключить всю индексацию для столбца. Для этого выполним: ```sql ALTER TABLE "Актеры" DROP CONSTRAINT "Актеры_Фото_key";``` Теперь индексация для столбца Фото производиться не будет.



Общее время, которое ушло на генерацию 1000 фото заняло около 15 минут, при учете того, что в коде есть тайм-аут, чтобы не слишком сильно нагружать сайт.

Вот пример сгенерированного фото.

#img(image("imgs/6.jpeg", width:37%), [Пример сгенерированного изображения])

После генерации всех картинок остается только написать код для генерации остальных данных и вставки всех данных в БД.

Тут необходимо выбрать какой именно язык программирования использовать для наилучшей генерации данных. Я остановился на python, так как в библиотеке faker уже встроен русский язык. При такой генерации была обнаружена одна проблема: нельзя генерировать названия пьес и спектаклей. Для начала, как названия пьес и спектаклей, я взял просто обычные слова. Таким образом удалось за несколько минут сгенерировать и загрузить в базу данных основной набор данных. Так как данные грузились не равномерно во все таблицы, коды, которые представлены в листингах 12-23, я выполнил только после окончания генерации. (Приложение Б)

Но названия пьес и спектаклей все еще не были осмысленными. Поэтому я решиб обратиться к библеотеки faker, написанной для ruby. В этоой библиотеке нет русского языка, но есть генерация названий книг, поэтому я сгенерировал все названия пьес и спектаклей и внес их в таблицу. (Приложение В)

Так как названия были на английском их оставалось только перевести на русский. Поэтому был написан код, который делает именно это. (Приложение Г)

Итак, вот заполненные таблицы:


#img(image("10.png", width:50%), [Результат заполнения таблицы Актеры])
#img(image("11.png", width:45%), [Результат заполнения таблицы ЗанятостьВСпектклях])
#img(image("12.png", width:50%), [Результат заполнения таблицы ЗанятостьРолиВСценах])
#img(image("13.png", width:50%), [Результат заполнения таблицы Контракт])
#img(image("14.png", width:50%), [Результат заполнения таблицы Пьесы])
#img(image("15.png", width:50%), [Результат заполнения таблицы Репетиции])
#img(image("16.png", width:50%), [Результат заполнения таблицы Роли])
#img(image("17.png", width:50%), [Результат заполнения таблицы Спектакли])
#img(image("18.png", width:50%), [Результат заполнения таблицы Сцены])
#img(image("19.png", width:50%), [Результат заполнения таблицы СценыВРепетициях])


#pagebreak()
== Запросы и триггеры
=== Запрос 1
Запрос выбирает актеров, которые заняты более чем в одном спектакле, имеют более трех наград и хотя бы одну премию. Возвращаются их ФИО, список премий, количество спектаклей и звание награды. (Листинг 25, результат на рис. 14)


#code(funcstr(ct, "SELECT a.ФИО, премии.Премии, subquery.NumberOfPlays, a.ЗваниеНаграды"), "sql", [Запрос 1], size:14pt)
#img(image("3.png", width:100%), [Результат выполнения 1 запроса])

Используемые функции и концепции:

- `JOIN`: Объединение таблиц для получения данных.
- `GROUP BY` и `HAVING`: Группировка данных и фильтрация групп.
- `ARRAY_AGG` и `UNNEST`: Работа с массивами в PostgreSQL.
- `EXISTS`: Проверка наличия записей.
#pagebreak()
=== Запрос 2
Запрос выводит информацию об актерах, играющих главную роль в спектакле с бюджетом выше среднего и имеющих больше 5 сцен в этом спектакле, включая название спектакля, бюджет, ФИО актера, количество главных ролей и количество сцен в этих ролях. (Листинг 26, результат на рис. 15)


#code(funcstr(ct, "SELECT DISTINCT s.Название AS НазваниеСпектакля,"), "sql", [Запрос 2], size:14pt)
#img(image("4.png", width:100%), [Результат выполнения 2 запроса])

Используемые функции и концепции:

- `JOIN`: Объединение таблиц для получения данных.
- `GROUP BY`: Группировка данных.
- `HAVING`: Фильтрация групп.
- `COUNT`: Подсчет записей.
- `AVG`: Вычисление среднего значения.
- `ORDER BY`: Сортировка результатов.

#pagebreak()
=== Запрос 3
Запрос выводит информацию о действующих контрактах актеров, включая ФИО актера, дату подписания текущего контракта и список спектаклей, в которых актеры заняты. (Листинг 27, результат на рис. 16)


#code(funcstr(ct, "WITH CurrentContracts AS (") + funcstr(ct, "SELECT a.ФИО, cc.ДатаПриема AS ДатаПодписанияДействующегоКонтракта, ap.Спектакли"), "sql", [Запрос 3], size:12pt)
#img(image("5.png", width:80%), [Результат выполнения 3 запроса])

Используемые функции и концепции:

- `WITH`: Создание временных таблиц для упрощения запроса.
- `ARRAY_AGG`: Агрегация значений в массив.
- `JOIN`: Объединение таблиц для получения данных.
- `ORDER BY`: Сортировка результатов.

#pagebreak()
=== Запрос 4
Запрос выводит информацию о пьесах, включая название пьесы, название спектакля, количество наград и количество репетиций. В выборку входят только пьесы с наградами. (Листинг 28, результат на рис. 17)


#code(funcstr(ct, "SELECT  p.Название AS НазваниеПьесы,"), "sql", [Запрос 4], size:10pt)
#img(image("6.png", width:55%), [Результат выполнения 4 запроса])

Используемые функции и концепции:

- `JOIN`: Объединение таблиц для получения данных.
- `LEFT JOIN`: Левое объединение для включения всех записей из левой таблицы.
- `UNNEST`: Работа с массивами в PostgreSQL.
- `GROUP BY`: Группировка данных.
- `HAVING`: Фильтрация групп.
- `COUNT`: Подсчет записей.
- `ORDER BY`: Сортировка результатов.

=== Запрос 5
Запрос выводит информацию о спектаклях, включая название спектакля, бюджет, количество актеров и количество сцен. Сортируется по количеству актеров. (Листинг 29, результат на рис. 18) 

Так же добавим график зависимости количества сцен в спектакле и количества актеров от бюджета.(рис. 19)


#code(funcstr(ct, "SELECT s.Название AS НазваниеСпектакля, s.Бюджет, COUNT(DISTINCT z.КодАктера) AS КоличествоАктеров, "), "sql", [Запрос 5], size:14pt)
#img(image("7.png", width:70%), [Результат выполнения 5 запроса])
#img(image("9.png", width:90%), [Зависимость количества сцен в спектакле и количества актеров от бюджета])

Используемые функции и концепции:

- `JOIN`: Объединение таблиц для получения данных.
- `GROUP BY`: Группировка данных.
- `COUNT`: Подсчет записей.
- `ORDER BY`: Сортировка результатов.

#pagebreak()
=== Запрос 6
Запрос выводит информацию о занятости актеров в спектаклях и репетициях, включая ФИО актера, название спектакля, роль, дата и время репетиции, сцена и продолжительность сцены. (Листинг 30, результат на рис. 20)


#code(funcstr(ct, "SELECT  a.ФИО AS Актер,"), "sql", [Запрос 6], size:12pt)
#img(image("8.png", width:80%), [Результат выполнения 6 запроса])

Используемые функции и концепции:

- `JOIN и LEFT JOIN`: Объединение таблиц для получения данных.
- `ORDER BY`: Сортировка результатов.


#pagebreak()
=== Триггер
Триггер обновляет стаж работы актера при вставке, обновлении или удалении записей в таблице "Контракт".(Листинг 31)

#code(funcstr(ct, "CREATE OR REPLACE FUNCTION обновить_стаж_работы() RETURNS TRIGGER AS $$") + funcstr(ct, "CREATE TRIGGER обновить_стаж_работы_триггер"), "sql", [Триггер])

Используемые функции и концепции:

- `TRIGGER`: Автоматическое выполнение функций при определенных действиях.
- `PL/pgSQL`: Процедурный язык PostgreSQL для написания триггерных функций.
- `AGE`: Вычисление разницы между датами.
- `COALESCE`: Возвращение первого ненулевого значения.
- `MIN и MAX`: Нахождение минимального и максимального значений.

= Заключение

В процессе выполнения данной работы были проведены анализы и проектирование базы данных "Занятость актеров театра".

- Был проведен детальный анализ предметной области, а ходе которого были выявлены следующие сущности: Контракт, Актеры, Занятость в спектаклях, Роли, Спектакли, Пьесы, Репетиции, Занятость роли в сценах, Сцены, Сцены в репетициях. 
- Определены основные связи между сущностями, построена инфологическая и датологическая модель базы данных, разработаны бизнес-процессы.
- Был написан код для создания базы данных и таблиц. 
- Была разработана модель вставки псеводорандомных, осмысленных данных и написан код для их генерации.
- Было написано 6 сложных запросов и триггер для базы данных.

= Список использованной литературы

+ Моргунов, Е. <NAME>. Основы языка SQL: учеб. пособие / Е. <NAME>; под ред. Е. <NAME>, <NAME>. — СПб.: БХВ-Петербург, 2018. — 336 с.: ил.

+ Новиков <NAME>. Основы технологий баз данных: учебное пособие / <NAME>, <NAME>, <NAME>; под ред. Е. В. Рогова. — 2-е изд. — М.: ДМК Пресс, 2020. — 582 с.

// Бунатян, Г. Г. Прогулки по рекам и каналам Санкт-Петербурга :
// путеводитель / Г. Г. Бунатян, <NAME>. – Санкт-Петербург : Паритет,
// 2007. – 254 с. – ISBN 978-5-93437-164-8.

#set heading(numbering: (..numbers) => {
})
#pagebreak()
#align(center)[#text(16pt)[*ПРИЛОЖЕНИЯ*]]
== Приложение А Генерация фотографий
#code(read("genphoto.py"), "py", [Генерируем 1000 фото людей])

#pagebreak()
== Приложение Б Генерация основного объема данных
#code(
"
import psycopg2
from faker import Faker
import random

fake = Faker('ru_RU')

awards_list = [
    'Лучшая мужская роль', 'Лучшая женская роль', 'Лучшая роль второго плана',
    'Выдающееся исполнение', 'Премия за вклад в искусство', 'Лучшая новая звезда',
    'Премия критиков', 'Премия зрительских симпатий', 'Лучшая комедийная роль',
    'Лучшая драматическая роль', 'Лучший актерский ансамбль', 'Лучший актер озвучивания',
    'Лучший международный актер', 'Лучшая роль в главной роли', 'Лучшая роль второго плана',
    'Лучшее исполнение в короткометражке', 'Лучшее исполнение в полнометражном фильме',
    'Лучшее исполнение в спектакле', 'Лучшее исполнение в мюзикле',
    'Лучшее исполнение в телесериале', 'Лучшее исполнение в веб-сериале',
    'Лучшее исполнение в рекламе', 'Лучшее исполнение в музыкальном видео',
    'Лучшее исполнение в анимационном фильме', 'Лучшее исполнение в видеоигре',
    'Лучший детский актер', 'Лучший подростковый актер', 'Лучший актер старшего возраста',
    'Лучший мужской актер', 'Лучшая женская актерская роль', 'Лучший трансгендерный актер',
    'Актер года', 'Лучший прорыв', 'Лучшее гостевое исполнение', 'Лучшее камео',
    'Лучший злодей', 'Лучший герой', 'Лучший дуэт', 'Лучший актерский дебют',
    'Лучшее исполнение в исторической роли', 'Лучшее исполнение в научно-фантастической роли',
    'Лучшее исполнение в фэнтезийной роли', 'Лучшее исполнение в роли ужасов',
    'Лучшее исполнение в боевой роли', 'Лучшее исполнение в комедийной роли',
    'Лучшее исполнение в драматической роли', 'Лучшее исполнение в романтической роли',
    'Лучшее исполнение в триллере', 'Лучшее исполнение в военной роли',
    'Лучшее исполнение в вестерне', 'Лучшее исполнение в документальном фильме'
]

def insert_actors(cursor):
    for i in range(1000):
        name = fake.name()
        birth_date = fake.date_of_birth(minimum_age=18, maximum_age=65)
        f = open(f'/Volumes/pr/BDkurs/imgs/{i+1}.jpeg', 'rb')
        photo = f.read()
        f.close()
        experience = f'{random.randint(1, 50)} years'
        awards = random.sample(awards_list, random.randint(0, 10))
        cursor.execute(
 'INSERT INTO \'Актеры\' (\'ФИО\', \'ДатаРождения\', \'Фото\', \'СтажРаботы\', \'ЗваниеНаграды\') '
            'VALUES (%s, %s, %s, %s, %s)',
            (name, birth_date, photo, experience, awards)
        )
"
, "py", [Генерируем актеров], size:10pt)
#pagebreak()
#code(
"
def insert_plays(cursor):
    for i in range(2000):
        title = fake.sentence(nb_words=random.randint(2,3), variable_nb_words=True)
        budget = random.randint(1000, 10000)
        year = random.randint(1900, 2023)
        age_limit = f'{random.randint(0, 18)}+'
        cursor.execute(
            'INSERT INTO \'Спектакли\' (\'Название\', \'Бюджет\', \'ГодПостановки\', \'ОграничениеПоВозрасту\', \'КодПьесы\') '
            'VALUES (%s, %s, %s, %s, %s)',
            (title, budget, year, age_limit, i + 1)
        )

def insert_contracts(cursor):
    for i in range(1000):
        start_date = fake.date_between(start_date='-20y', end_date='today')
        end_date = fake.date_between(start_date='today', end_date='+7y')
        salary = random.randint(30000, 100000)
        cursor.execute(
            'INSERT INTO \'Контракт\' (\'ДатаПриема\', \'ДатаУвольнения\', \'Ставка\', \'КодАктера\') '
            'VALUES (%s, %s, %s, %s)',
            (start_date, end_date, salary, i + 1)
        )

def insert_employment(cursor):
    for _ in range(1000000):
        actor_id = random.randint(1, 1000)
        play_id = random.randint(1, 2000)
        cursor.execute(
            'INSERT INTO \'ЗанятостьВСпектаклях\' (\'КодСпектакля\', \'КодАктера\') '
            'VALUES (%s, %s)',
            (play_id, actor_id)
        )

def insert_rehearsals(cursor):
    for _ in range(80000):
        date_time = fake.date_time_between(start_date='-20y', end_date='now')
        duration = f'{random.randint(1,4)}:{random.randint(0, 59)}:00'
        read_through = fake.boolean()
        play_id = random.randint(1, 2000)
        cursor.execute(
            'INSERT INTO \'Репетиции\' (\'ДатаВремя\', \'Продолжительность\', \'Читка\', \'КодСпектакля\') '
            'VALUES (%s, %s, %s, %s)',
            (date_time, duration, read_through, play_id)
        )

def insert_scenes(cursor):
    for i in range(40000):
        title = f'{fake.sentence(nb_words=random.randint(2,3), variable_nb_words=True)}'
        duration = f'00:{random.randint(1, 30)}:00'
        play_id = random.randint(1, 2000)
        cursor.execute(
            'INSERT INTO \'Сцены\' (\'Название\', \'Продолжительность\', \'КодПьесы\') '
            'VALUES (%s, %s, %s)',
            (title, duration, play_id)
        )

"
, "py", [Генерируем пьесы, контракты, занятость, репетиции и сцены], size:10pt)
#pagebreak()
#code(
"
def insert_roles(cursor):
    for i in range(1000000):
        title = fake.first_name() + ' ' + fake.last_name()
        main_role = fake.boolean()
        play_id = random.randint(1, 2000)
        actor_id = random.randint(1, 1000)
        cursor.execute(
            'INSERT INTO \'Роли\' (\'НазваниеРоли\', \'Главная\', \'КодПьесы\', \'КодАктера\') '
            'VALUES (%s, %s, %s, %s)',
            (title, main_role, play_id, actor_id)
        )

def insert_role_engagement(cursor):
    for _ in range(1000000):
        role_id = random.randint(1, 1000000)
        scene_id = random.randint(1, 40000)
        cursor.execute(
            'INSERT INTO \'ЗанятостьРолиВСценах\' (\'КодРоли\', \'КодСцены\') '
            'VALUES (%s, %s)',
            (role_id, scene_id)
        )

def insert_scenes_in_rehearsals(cursor):
    for _ in range(80000):
        rehearsal_id = random.randint(1, 80000)
        scene_id = random.randint(1, 40000)
        cursor.execute(
            'INSERT INTO \'СценыВРепетициях\' (\'КодРепетиции\', \'КодСцены\') '
            'VALUES (%s, %s)',
            (rehearsal_id, scene_id)
        )

def insert_plays_details(cursor):
    for i in range(2000):
        title = f'{fake.sentence(nb_words=random.randint(2,3), variable_nb_words=True)}'
        year = random.randint(1900, 2023)
        author = fake.name()
        cursor.execute(
            'INSERT INTO \'Пьесы\' (\'Название\', \'ГодВыпуска\', \'Автор\') '
            'VALUES (%s, %s, %s)',
            (title, year, author)
        )

"
, "py", [Генерируем роли, роли в сценах, сцены в репетициях, пьесы], size:12pt)
#pagebreak()
#code(
"
def main():
    try:
        conn = psycopg2.connect(
            dbname='Занятость актеров театра',
            user='pluttan',
            password=input('Password: '),
            host='localhost',
            port='5432'
        )
        print('Connected to the database')
        cursor = conn.cursor()
        print('generation actors')
        insert_actors(cursor)
        conn.commit()
        print('generation plays')
        insert_plays(cursor)
        conn.commit()
        print('generation contracts')
        insert_contracts(cursor)
        conn.commit()
        print('generation employment')
        insert_employment(cursor)
        conn.commit()
        print('generation rehearsals')
        insert_rehearsals(cursor)
        conn.commit()
        print('generation scenes')
        insert_scenes(cursor)
        conn.commit()
        print('generation roles')
        insert_roles(cursor)
        conn.commit()
        print('generation role engagement')
        insert_role_engagement(cursor)
        conn.commit()
        print('generation scenes in rehearsals')
        insert_scenes_in_rehearsals(cursor)
        conn.commit()
        print('generation plays details')
        insert_plays_details(cursor)
        conn.commit()
        print('Data inserted successfully')
    except Exception as e:
        print(f'Error: {e}')
    finally:
        if conn:
            conn.close()

if __name__ == '__main__':
    main()

"
, "py", [Вызываем все функции и записываем в базу], size:12pt)
#pagebreak()
== Приложение B Генерация названий пьес и спектаклей

#code(read("genbook.rb"), "rb", [Перегенерируем названия пьес и спектаклей, заменяя их на осмысленные], size:11pt)

#pagebreak()
== Приложение Г Перевод названий пьес и спектаклей


#code(read("genbook.py"), "py", [Переводим все названия на русский язык], size:11pt)

// = Использованная литература



