Запросы второй контрольной

Вариант 211
Вариант 212

Схема и данные для вариантов 211 и 212

Вариант 221
Вариант 222

Схема и данные для вариантов 221 и 222

Схемы БД ко второй контрольной

На второй контрольной надо будет писать запросы разной степени сложности. Сами запросы вы увидите во время контрольной, а схемы БД можно поизучать уже сейчас.

Вариант 21 "Пироги" (почти те самые, но с некоторыми изменениями)
Вариант 23 "Банк"

Экзаменационные вопросы 2014 года

Как выглядели экзаменационные задачи в прошлом году.

Схема БД для контеста

Контест посвящен космическим путешествиям!

Схема БД тут.
А тут SQL-скрипт, генерирующий данные.

 А  тут запросы

Оба скрипта доступны в SQooL в виде шаблона "Контест 2 декабря"

Напомню правила:

• Баллы можно получить за авторство запросов и за решение
• Автор запроса должен написать запрос в человекочитаемом текстовом виде, дать ему оценку по шкале "простой" - "средний" - "сложный" - "никто не решит!", написать свое каноническое решение и прислать мне по почте. 
• У нас не будет много времени на уточнение решения, поэтому постарайтесь делать канонические решения правильными. 
• Каноническое решение должно давать детерминированный ответ. Лучше всего делать ответ упорядоченным и оговаривать условие сортировки в тексте
• В процессе решения на паре вы выбираете запросы из имеющихся, решаете и присылаете мне ответы. Постараюсь проверять в реальном времени. За правильное решение запроса со стоимостью P каждый из N решивших получает P/(N+1) баллов. Автор получает тоже P/(N+1) балл, при условии что запрос вообще хоть кто-то решал (была хоть одна попытка).

Пример запроса:

Найти всех пилотов, водивших корабли на планеты с диктатурой. В ответе должны быть имена пилотов, отсортированные по возрастанию.

Простой.

Решение: 

SELECT DISTINCT C.name FROM Commander C JOIN Flight F ON(C.id = F.commander_id) JOIN Planet P ON (F.planet_id = P.id) JOIN Government G ON (G.id = P.government_id)
WHERE G.value = 'диктатура'
ORDER BY name;

Тренировочный контест

Схема и данные

• Схема
• Данные

Запросы

• Работник-чемпион по количеству интервью 
• У кандидатов с вердиктами 'soft accept' и 'strong accept' найти MAX и MIN средние баллы за интервью и выветси данные о соответствующих кандидатах
• Найти кандидатов, подавшихся на >1 вакансию
• Найти противоречия в данных, например:
• несовпадения дат
• проигнорированный флажок фильтрации
• принятые ('* accept') кандидаты с низким баллом (например < 2.0)

Тексты заданий первой контрольной

Файлопомойка
https://docs.google.com/document/d/1jE_ZAZjP55QQnFLCas0voQpbzNzJiZl6wQpBcc0Y8pM/edit?usp=sharing

Пироги
https://docs.google.com/document/d/1V5rdHUC4EyqQfLZrtoOvE2fKd4-8HpunEtw8QDvBSW8/edit?usp=sharing

Футбол
https://docs.google.com/document/d/1veCHQIPt1WbipQ5FDmNtfzeXsLzu-LDJskqJCMYV6Zk/edit?usp=sharing

Практика 21 октября. Исходники и ДЗ

Исходники, использованные на практике 21 октября

Домашнее задание

Мы хотим организовать ключевые слова, характеризующие содержание статьи или интересы ученого, в деревянную иерархию. Примером такой иерархии может служить ACM Computing Classification System [1]

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

Статья может быть связана с любыми элементами этой классификации, на любом уровне.

Нужно придумать схему хранения этой классификации в БД и связи статей с темами и написать запросы:

• добавляющий новую тему с указанной родительской темой (приветствуется оформление в виде хранимой процедуры)
• показывающий всё поддерево тем, начиная с заданной. Результат должен быть удобен для обработки приложением, чтобы ему не нужно было прикладывать чрезмерных усилий, чтобы напечатать дерево. Добавлять в метки пробелы-отступы, однако, не нужно.
• выдающий все статьи, проассоциированные с темами из поддерева с заданным корнем
• находящий ближайшего общего предка для двух заданных тем

Запросы приведены в порядке возрастания сложности (как мне кажется), так что начните с самых простых

[1] http://dl.acm.org/ccs/ccs.cfm

Практика 14 октября, исходники

Исходники, использованные на практике 14 октября.

Медленная БД и сессии
Представления и хранимые процедуры

Домашнее задание №4

Описание

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

Исходники для генерации схемы и данных тут: hw04.zip

Схема и данные

В приложенном архиве находятся файл со схемой БД и скрипт на Питоне, заполняющий таблицы данными

psql -h localhost -U postgres < 01_gen_schema.sql
python 02_gen_data.py

Схема БД

В базе данных есть таблицы Conference, Paper, Keyword и связи многие-ко-многим PaperConference и PaperKeyword. Смысл таблиц и их атрибутов, полагаю, ясен из их названий.

Задание

В результате должно получиться представление со схемой:

conference TEXT, keyword TEXT, count INT

Запись в представлении означает, что в статьях, поданных на конференцию conference ключевое слово keyword упоминается countраз. В представление должны попасть для каждой конференции ключевые слова с наибольшим значением count, в количестве не более трех. Например, из следующих записей

Conf1 KW1 5
Conf1 KW2 8
Conf1 KW3 1
Conf1 KW4 6

в представление должны попасть первая, вторая и четвертая. Если есть несколько ключевых слов с одинаковым значением count и в представление нужно включить только часть из них, то отсортируйте эти ключевые слова лексикографически и возьмите первые. Пример:

Conf1 KW2 8
Conf1 KW4 6
Conf1 KW1 5
-----------
Conf1 KW3 5

Задание можно решить многими способами. Как-то явно фантазию ограничивать не будем, но все же постарайтесь не увлекаться. Вы написали и забыли, а проверяющим читать. Постарайтесь написать комментарии ко всем нетривиальным решениям.

Домашнее задание №3

TL;DR: нужно написать код простого приложения, общающегося с БД через API. Исходники для генерации схемы и данных тут: hw03.zip

Установка

Для того, чтобы протестировать приложение, нужно запустить Postgres, создать схему и заполнить БД данными. Команды, создающие схему, и питоновский скрипт, генерирующий данные, предполагают, что Постгрес запущен на локальной машине на стандартном для него порту 5432 и в нем есть пользователь postgres с паролем csc.

Инструкции по установке постгреса для вашей OS ищите на сайте постгреса. Те, кто пользуется Докером (разве кто-то еще не пользуется Докером?), могут установить и запустить постгрес командой

docker run --name postgres-dbms-2015-hw03 -e POSTGRES_PASSWORD=csc -d -p 5432:5432 postgres

Если докеровского образа с постгресом у вас еще нет то он будет скачан (несколько десятков мегабайт). Постгрес запустится так, как написано выше (порт, пользователь, пароль)

Кроме постгреса вам понадобится python. В python 3 скорее всего всё тоже будет работать, но выяснять при проверке и чтении каждого из ваших сорока решений, какой же там нынче используется питон, совершенно не хочется. Поэтому если вы можете использовать Python 2, используйте пожалуйста его.

Для работы с постгресом из питона нужно будет установить пакет psycopg2. Пользователи Ubuntu/Debian могут поставить его командой

apt-get install python-psycopg2

Пользователи других систем - обратитесь к документации об установке питоновских пакетов для вашей системы.

Генерация схемы и данных

В архиве hw03.zip находятся несколько файлов.

-- генерация схемы

psql -h localhost -U postgres < 01_gen_schema.sql
-- заполнение схемы данными

python 02_gen_data.py

-- проход по содержимому таблицы Conference

python 03_scan_conference.py

Докеровский образ с готовыми данными

Можно воспользоваться докеровским образом, в котором уже сгенерирована схема и данные. Команда

docker run --name postgres-dbms-2015-hw03 -d -p 5432:5432 dbarashev/postgres:csc_hw03

должна его скачать и запустить постгрес, в котором уже всё есть. Локальный питон и psycopg2 вам всё равно потребуются.

Схема БД

У вас есть 4 таблицы: University(university_id, name), Researcher(researcher_id, name, university_id), Conference(conference_id, name) и Participant(conference_id, researcher_id). Думаю, что их смысл очевиден из названий таблиц и столбцов.

Задание

Вам нужно написать два скрипта. Первый для каждой конференции должен посчитать количество её участников, работающих в заданном университете. Название университета передавайте в аргументах командной строки. Запуск скрипта должен выглядеть примерно так:

python hw3_01.py Uni42

Второй скрипт должен перевести всех исследователей из одного университета в другой, а первый университет удалить. Названия университетов тоже передавайте в командной строке. Запуск должен выглядеть примерно такЖ

python hw3_02.py Uni42 Uni24

После этого Uni42 должен быть удален, а все, кто в нем работал, должны оказаться в Uni24

Замечания

Задачу можно решить разными способами. Если вы знаете хороший, то прекрасно, воспользуйтесь им. Если хорошего не знаете, то решите каким-то, и нам будет о чем поговорить на занятии. Будет неплохо, если вы вкратце опишете своё решение в сопроводительном тексте. Например иду циклом в питоне по исследователям из первого университета и для каждого выполняю запрос...

Помните, что задание не на искусство написания красивого и непонятного питоновского кода. Чем проще будет этот код, тем лучше.

Как сдавать решение

Присылайте решения в виде текстовых файлов с расширением .py в кодировке UTF-8 на адрес dbms@barashev.net. Если можете прицепить два файла, то так и сделайте, если нет, то положите в ZIP архив. Писать код в тексте письма не нужно.

Дедлайн: вечер 12 октября

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

Домашнее задание №2

Задание

Вы делаете информационную систему для рецензирования статей. Она будет работать примерно так:

• Авторы присылают вам статью - название, текст и список тематических меток, которые они берут из специального единого классификатора. Кроме этого авторы указывают, на какую конференцию они подают статью. Сами авторы вам с точки зрения системы неинтересны и хранить вы их не собираетесь
• У каждой конференции есть название, место (одно) и даты проведения (начало и конец)
• У каждой конференции есть программный комитет. У члена программного комитета есть имя, email и известен список тем, из все того же классификатора, в которых он специалист.
• Статья отправляется на рецензирование нескольким членам программного комитета и каждый ставит ей какую-то оценку -- целое число в интервале [1..4]

Конференций может быть много. Один и тот же ученый может состоять в программных комитетах разных конференций. Член программного комитета может рецензировать много статей. Одноименных объектов не бывает (ни статей, ни конференций, ни ученых).

Составьте схему БД для хранения этих данных. Результатом должен быть скрипт из нескольких операторов CREATE TABLE. Хранить нужно все данные, упомянутые выше, за исключением авторов статьи.

Куда присылать

Присылайте решения на почту dbms@barashev.net  в виде одного текстового файла с расширением .txt или .sql в кодировке UTF-8. Сжимать файл архиватором не нужно. 

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

Срок сдачи:  22 сентября 2015 23:59

Домашнее задание №1

В приложенном файле находится скрипт, который создает в базе данных две таблицы и заполняет их данными.

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

Скрипт можно выполнить командой psql -h localhost -f hw01_dump.sql, опционально добавив аргументы -d <database> и -U <username> и указывая, соответственно, название БД, в которой надо выполнять скрипт и имя пользователя БД, от чьего имени выполняется скрипт

Задание

1. Напишите запрос, который для заданного университета вернет названия статей, в авторы которых входят ученые этого университета
2. Напишите запрос, возвращающий университеты, в которых работают ученые, чьи статьи не были приняты на конференцию
3. Напишите запрос, возвращающий место проведения конференции DBLP'13
4. Напишите запрос, возвращающий все города из США (USA), в которых проводились конференции.

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

Куда присылать

Присылайте файлы с кодом и мыслями об улучшении схемы на почту dbms@barashev.net 

Срок сдачи: 15 сентября 2015 23:59

[1] Скрипт hw01_dump.sql

Рекомендации по прохождению практики

Вступительный тест проверен, рекомендации насчёт того, к кому идти на практику смотрите в табличке. Ещё раз напомню, что

• это всего лишь необязательная к исполнению рекомендация.
• при условии адекватного выбора сложность прохождения практики примерно одинаковая.
• на практике у Дмитрия Барашева (обозначен в табличке как ДБ) занимаются прикладным программированием на SQL и, возможно, Python.
• на практике у Дмитрия Зворыгина (обозначен в табличке как ДЗ) занимаются реализацией частей ядра СУБД на C++/Java/других языках

Курс "Базы Данных" в Академическом Университете. Осень 2015

Кому читается

Первый курс магистратуры, направление Software Engineering.

Где и когда

В здании Академического Университета на улице Хлопина. Лекции по средам в 12:00, практика по средам в 14:00.

Отчетность

• Курс состоит из теоретических лекций и практических занятий. 
• Отчетностью является практический зачёт и письменный дифференцируемый зачет по теоретической части. 
• Условия получения или неполучения зачёта по практике определяются преподавателем практики
• Теоретический зачет состоит из нескольких (до десяти) заданий-вопросов, на которые нужно дать обоснованный ответ.
• Если практический зачёт получен, то итоговая оценка равна оценке теоретического зачёта. Если он не получен, то из оценки, полученной на теорзачёте, вычитается 1 балл.
• Какие-либо дополнительные задания, которые возможно будут на лекциях, непосредственного влияния на итоговую оценку не оказывают
  
  Короткое резюме: если справился с практикой, получишь ровно то, что заработал на теорзачёте. Если не справился с практикой, получишь то, что заработал на теорзачёте минус 1 балл

Практика

Практика у Дмитрия Барашева: изучение методов проектирования схемы БД, написание запросов на SQL. Рекомендуется тем, кто не имеет практического опыта разработки баз данных

Практика у Дмитрия Зворыгина: реализация своей собственной игрушечной СУБД на языке типа Java/C++. Рекомендуется тем, кто достаточно хорошо знаком с SQL и проектированием БД и готов попробовать более сложные задачи. 

Сложность получения зачета в обоих вариантах одинаковая.

Контакты

Электропочта: dbms@barashev.net
Веб: dbms.barashev.net

Письменный экзамен: плюсы, минусы, подводные камни

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

Из инстаграмма клетчатого слона Феврония

Твои усилия заключаются в том, чтобы не выгнать и не пристрелить бедолагу. Выгонять у нас как-то не принято, и потом, ты его в дверь, а он в окно, и снова же надо будет мучаться.

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

Письменный экзамен прекрасен тем, что в аудитории он идёт ровно столько, сколько отведено, обычно один академический час. Потом ты собираешь ответы и несколько дней проверяешь. И все ответы перед глазами, никто не забыт, ничто не забыто.

Для студента, однако, появляются подводные камни.

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

Во-вторых, нужно объяснить ответ, и тут оказывается, что мысль не записанная есть отсутствие мысли, а мысль записанная есть ложь (С). То есть натурально, нельзя, как на устном экзамене, сказать глупость, или часть правды, и скорректировать ответ в зависимости от ширины зрачков экзаменатора. Что записано, то записано, что нет, то нет. Предположения, не высказанные на бумаге, уносятся в небытие.

Из инстаграмма клетчатого слона Феврония

Но если все расписывать очень подробно, будучи несколько не в теме, то можно нарваться и на противоположный камень: будет очевидно, что правильный ответ получен неправильными выводами. Да и тупо времени может не хватить.

В общем, жизнь студента на письменном экзамене весьма непроста. Но и мне достается тоже. Письменные работы за редкими исключениями выглядят вот так

Из инстаграмма клетчатого слона Феврония

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

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

Из инстаграмма клетчатого слона Феврония

Новости Папирии: графики и аттракционы

В Папирии появился компилятор графиков gnuplot. За несколько секунд можно состряпать простую гистограмму из CSV файла, а особые ценители могут побаловать себя скриптами на gnuplot и прекрасными графиками.

И только сегодня аттракцион невиданной щедрости: годовая подписка на всё это добро стоит $30, а не $60, как стоила вчера и будет стоить завтра.

Практика 26 ноября. Транзакции

Базы Данных для тех кто еще не, но уже да

Развлекаю себя в этом осеннем семестре тем. что пытаюсь прочитать курс "Базы Данных" студентам, часть из которых его уже слышала (причем даже в моем же исполнении, гыгы), часть скоро услышит, а у третьей части его нет и не будет. И многие при этом, вполне вероятно, на практике с БД хотя бы минимально общались. Не хочется создавать дежавю первым двум частям, но в то же время хочется сделать его интересным и проходимым для третьей части.

Вроде бы получилось. Рассматриваем в основном практические аспекты, теорию оставляю университетскому курсу. Лекции и домашние задания рокировались: сначала нужно попробовать дома решить задачу, а потом уже на лекции разобрать возможные варианты решения. Получается интересно, во всяком случае проверять работы так уж точно.

Возможно, следующим летом к этому прикрутится видеочасть, и в следующем году получится еще интереснее.

Практика №8. Триггеры

Практика №7. Хранимые процедуры