Базы данных и ИС (2011-2012)

MySQL, PostgreSQL, MSSQL, Oracle и т.д. Диалекты SQL, формирование запросов, хранимые процедуры, тригерры, транзакции и многое другое

Базы данных и ИС (2011-2012)

Сообщение Vladislav_133 08 сен 2011, 09:45

Добрый день. Как я и говорил здесь мы будем обсуждать трудные вопросы по дисциплинам БД и ИС.
Посмотрите также старые темы
viewtopic.php?f=18&t=406
viewtopic.php?f=18&t=206

Жду ваших вопросов.
Последний раз редактировалось Vladislav_133 03 апр 2012, 09:00, всего редактировалось 1 раз.
Аватара пользователя
Vladislav_133
Elite
 
Сообщения: 1386
Зарегистрирован: 13 дек 2008, 18:08
Полное имя: П.В.Ю.

Re: Базы данных и ИС (2011)

Сообщение xdsl 09 сен 2011, 07:58

По удалению из таблиц у нас на форуме почему-то еще ничего не было. А ведь память компьютера - она не резиновая ;).

Даны две таблицы First(id,name) и Second(id,name,to_first_id), Second.to_first_id ссылается на First.id.
Задача - удалить из таблицы First все записи, на которые нет ссылок из Second.
xdsl
 
Сообщения: 1236
Зарегистрирован: 09 дек 2008, 05:16
Откуда: ВЦ ШГПИ
Полное имя: Слинкин Д.А.

Re: Базы данных и ИС (2011-2012)

Сообщение Vladislav_133 09 сен 2011, 09:49

Пусть это пока будет задачкой для знатоков. Не большая олимпиада.
Ответы на следующей неделе.
Последний раз редактировалось Vladislav_133 03 апр 2012, 09:01, всего редактировалось 1 раз.
Аватара пользователя
Vladislav_133
Elite
 
Сообщения: 1386
Зарегистрирован: 13 дек 2008, 18:08
Полное имя: П.В.Ю.

Re: Базы данных и ИС (2011-2012)

Сообщение Vladislav_133 12 дек 2011, 13:10

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

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

Часть 1. Удаление и ссылочная целостность.

Ссылочная целостность является важнейшим элементом безопасности БД. Кратко говоря, ссылочная целостность представляет собой учет связей между таблицами при выполнении некоторых операций. В частности и в большей степени это относится к удалению. Связи между таблицами можно разделить на «статические» и «динамические». Динамические связи возникают в момент выполнения команды SQL, если она относится сразу к нескольким таблицам. После выполнения команды эта связь пропадает. Начну со статической связи. Статическая связь представляет собой отражение некоторых устойчивых связей, существующих реально в предметной области. Ну, мой любимый пример «ученики» - «оценки». Оценка ставится конкретному ученику – связь налицо. В реляционной базе данных такая устойчивая (статическая) связь выражается с помощью связки «первичный ключ - PK» - «внешний ключ - FK». Посмотрим, как это происходит.
Ученики <id, fio> - оценки <id, id_u, mark>. Для простоты количество столбцов я взял по минимуму. При создании таблицы в большинстве современных СУБД можно указать внешний ключ, в котором указывается главная таблица. В нашем случае при создании таблицы «оценки» создает внешний ключ, в котором указывается таблица «Ученики». Так возникает связка id – id_u, где id – первичный ключ ученика. Это и есть статическая связь. В свойствах статической связи можно указать реакцию на удаление строк из главной таблицы (Ученики). Существует три возможных реакции:
1. Запрет удаления из главной таблицы, если удаляемая строка связана со строками подчиненной таблицы (оценки).
2. Каскадное удаление – вместе со строкой из главной таблицы удаляются все связанные с ней строки в подчиненной таблице.
3. Пометка. При удаление строк из главной таблицы, связанные строки в подчиненной таблице помечаются. Например, значению внешнего ключа присваивается значение NULL.
Вот собственно и все. Установим реакцию 2.
Затем выполним команду типа
DELETE from Ученики where (какое-то условие)
В результате выполнения будут удаляться строки сразу из двух таблиц.

Если же мы выберем вариант 1 (а он обычно действует по умолчанию), то в этом случае как раз будут удаляться только записи, у которых нет связанных записей в подчиненной таблице (ученики без оценок).

Продолжение следует...
Последний раз редактировалось Vladislav_133 03 апр 2012, 09:01, всего редактировалось 2 раз(а).
Аватара пользователя
Vladislav_133
Elite
 
Сообщения: 1386
Зарегистрирован: 13 дек 2008, 18:08
Полное имя: П.В.Ю.

Re: Базы данных и ИС (2011-2012)

Сообщение Vladislav_133 14 дек 2011, 11:47

Часть 2. Подзапросы.

Подзапросы, моя любимая тема. Хотя и говорю студентам, что не стоит ими увлекаться. Так как вложенность запросов сильно влияет на скорость выполнения. Но здесь особая статья - команда delete. И так, следующая команда удаляет только тех учеников, у которых нет оценок.

Код: Выделить всё
DELETE FROM Ученики WHERE not id in
(
    SELECT a.id FROM Ученики a INNER JOIN оценки b on a.id=b.id_u
)

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

Продолжение следует ...
Последний раз редактировалось Vladislav_133 03 апр 2012, 09:01, всего редактировалось 2 раз(а).
Аватара пользователя
Vladislav_133
Elite
 
Сообщения: 1386
Зарегистрирован: 13 дек 2008, 18:08
Полное имя: П.В.Ю.

Re: Базы данных и ИС (2011-2012)

Сообщение Vladislav_133 16 дек 2011, 07:38

Часть 3. Некоторые пояснения по поводу подзапросов

Я полагаю, что у тех, кто думает, должен возникнуть вопрос по поводу вот этого

Код: Выделить всё
WHERE not id in
(
    SELECT a.id FROM Ученики a INNER JOIN оценки b on a.id=b.id_u
)


а именно, почему собственно нельзя было написать по другому


Код: Выделить всё
WHERE id in
(
    SELECT a.id FROM Ученики a INNER JOIN оценки b on a.id<>b.id_u
)



казалось бы все логично. Ан нет.
Дело в том, что происходит соединение двух таблиц по принципу все строки первой таблицы со всеми строками второй. А далее идет условие, по которому должны быть отобраны строки из этого множества. Т.е. остануться все пары, в которых id первой таблицы, не равно id_u второй таблицы. Количество таких строк легко подсчитать. Если в первой таблице N1 строк, а во второй N2 строк, то в результирующей таблице будет N1*N2 - N2 строк, но это при условии, что все строки второй таблицы участвуют в связи. В нашем случае это естественно, так как не бывает оценки без ученика.

Вот почему мне больше нравится, когда связь указывается в разделе from, а не в разделе where. В первом случае понятно, что таблицы в начале объединяются и потом происходит отбор, а во втором из записи это не очевидно. Сравните два равноправных запроса

SELECT * FROM Ученики a INNER JOIN оценки b on a.id=b.id_u

и

SELECT * FROM Ученики a, оценки b
where a.id=b.id_u


Продолжение следует...
Последний раз редактировалось Vladislav_133 03 апр 2012, 09:02, всего редактировалось 1 раз.
Аватара пользователя
Vladislav_133
Elite
 
Сообщения: 1386
Зарегистрирован: 13 дек 2008, 18:08
Полное имя: П.В.Ю.

Re: Базы данных и ИС (2011-2012)

Сообщение Vladislav_133 16 дек 2011, 18:30

Часть 4. Экзотика.

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

Вот пример.

Код: Выделить всё
delete from Ученики
where id in
(
select aa.id from Ученики aa inner join оценки bb on aa.id<>bb.id_u
group by aa.id
having count(*)=
(
select max(c.co)
from
(
   select   count(*) as "co" from Ученики a inner join оценки b on a.id<>b.id_u
   group by a.id
) c
)
)



Ну что? Хорошо! Попробуйте разобраться. Разумеется, результат тот же, что и в предыдущих примерах.
Как видите, здесь мы используем условие a.id<>b.id_u, которое мы ранее отвергли.

Продолжение следует...
Последний раз редактировалось Vladislav_133 03 апр 2012, 09:02, всего редактировалось 1 раз.
Аватара пользователя
Vladislav_133
Elite
 
Сообщения: 1386
Зарегистрирован: 13 дек 2008, 18:08
Полное имя: П.В.Ю.

Re: Базы данных и ИС (2011-2012)

Сообщение Vladislav_133 03 апр 2012, 08:59

Выкладываю на форум задачи для МО-ПОВТ по базам данных. Всем прорешать.

Появятся вопросы,могу разобрать пару задач.
Вложения
экзамен.doc
(77 Кб) Скачиваний: 438
Аватара пользователя
Vladislav_133
Elite
 
Сообщения: 1386
Зарегистрирован: 13 дек 2008, 18:08
Полное имя: П.В.Ю.

Re: Базы данных и ИС (2011-2012)

Сообщение Алексей_ 27 май 2012, 18:37

Владислав Юрьевич, а как быть прикладникам 284-285х групп с предметом ИС? Вы обещали выложить вопросы для нас. Заранее спасибо
Последний раз редактировалось Алексей_ 28 май 2012, 15:35, всего редактировалось 1 раз.
Алексей_
 
Сообщения: 55
Зарегистрирован: 15 фев 2010, 16:33
Полное имя: Алексей

Re: Базы данных и ИС (2011-2012)

Сообщение Vladislav_133 28 май 2012, 12:38

Да, я выложил. См. в разделе факультет информатики.
Аватара пользователя
Vladislav_133
Elite
 
Сообщения: 1386
Зарегистрирован: 13 дек 2008, 18:08
Полное имя: П.В.Ю.

Re: Базы данных и ИС (2011-2012)

Сообщение Алексей_ 28 май 2012, 15:35

Благодарю!
Алексей_
 
Сообщения: 55
Зарегистрирован: 15 фев 2010, 16:33
Полное имя: Алексей


Вернуться в Базы данных

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 2

cron