II.10. Допълнителни операции в релационната алгебра — Разширена писмена разработка
Разширена писмена разработка. II.10. Допълнителни операции в релационната алгебра
Подробен авторски текст за подготовка по конспекта на СУ. Краткият вариант е запазен на основната страница на темата. За преговор използвай плана; за обосновка — определенията и разработените примери. Това не е официален изпитен образец.
Кратка разработка и уроци по темата · План за изпита · Покритие · Източници
Обхват от конспекта
Допълнителни операции в релационната алгебра. Премахване на дубликати. Агрегатни операции. Групиране. Оператор за групиране. Разширяване на оператора за проекция. Оператор за сортиране. Външни съединения.
Покритие на конспекта
Всяка подточка води към съответния раздел на разработката.
- Допълнителни операции в релационната алгебра — 1. Защо класическите операции се разширяват
- Премахване на дубликати — 1. Защо класическите операции се разширяват
- Агрегатни операции — 2. Агрегатни операции
- Групиране — 3. Групиране и оператор γ
- Оператор за групиране — 3. Групиране и оператор γ
- Разширяване на оператора за проекция — 4. Разширена проекция
- Оператор за сортиране — 5. Сортиране
- Външни съединения — 6. Външни съединения
Кратък план за изпита
- Разширена алгебра и видове резултати.
- Дедупликация и агрегати.
- Групиране и оператор γ.
- Разширена проекция и сортиране.
- Ляво, дясно и пълно външно съединение; NULL.
1. Защо класическите операции се разширяват
Практическите заявки трябва да изчисляват суми, да формират групи, да подреждат резултати и да запазват редове без съвпадение. Затова към класическата релационна алгебра се добавят операции с изрично определена множествена или мултимножествена семантика. Не всички разширения връщат същия математически вид обект: сортирането връща подредена последователност.
Операторът за премахване на дубликати δ превръща всяка положителна кратност в 1. Например δ({4,4,6}) = {4,6}. Той не избира произволен „представител“ измежду различни редове: премахва само напълно еднакви кортежи в текущата схема. При проекция може първо да възникнат дубликати и едва след това δ да ги отстрани.
Редът спрямо агрегация е съществен: SUM({4,4,6}) = 14, а SUM(δ({4,4,6})) = 10. Различието е желано, когато въпросите са „обща сума на срещанията“ и „сума на различните стойности“. Не трябва да се въвежда DISTINCT само заради външния вид на таблицата.
2. Агрегатни операции
COUNT, SUM, AVG, MIN и MAX обобщават множество или мултимножество стойности. COUNT брои, SUM сумира, AVG е аритметично средно, а MIN и MAX намират крайни стойности според избрания ред. SUM и AVG обикновено изискват числов домейн; MIN и MAX изискват сравнимост. При мултимножество повторенията участват: средното на 4,4,6 е 14/3, а не 5.
В SQL COUNT(*) брои редовете, докато COUNT(A) брои редовете с не-NULL стойност на A. SUM, AVG, MIN и MAX също игнорират NULL входовете. За празен вход COUNT връща 0; SUM, AVG, MIN и MAX връщат NULL според SQL семантиката. Това не е определение на математическа сума на празно множество, а правило на SQL, което трябва да бъде разграничено.
При обединяване на предварително пресметнати средни не е правилно винаги да се взема обикновено средно от тях: групите могат да са с различен брой стойности. За общо средно са нужни сумите и броят на участващите стойности. Например група с една оценка 6 и група с три оценки 4 имат общо средно 4,5, а средното на двете групови средни е 5.
3. Групиране и оператор γ
Групирането разделя входните редове на групи с еднакви стойности по избраните групиращи атрибути. Агрегатите се изчисляват независимо за всяка група. Записът γКурс; COUNT(*)→Брой, AVG(Оценка)→Средна(R) означава групиране по Курс и получаване на по един ред за всяка група. Стрелките тук дават имена на резултатни атрибути, а не задават ФЗ.
ОЦЕНКИ(Курс, Курсист, Оценка) (К01, У01, 6) (К01, У02, 4) (К02, У03, NULL) Курс | COUNT(*) | COUNT(Оценка) | AVG(Оценка) К01 | 2 | 2 | 5 К02 | 1 | 0 | NULL
Използвана е SQL съвместима обработка на NULL. За К02 има група, защото има входен ред, но няма налична оценка. Курс без никакъв входен ред изобщо няма група. За да се покаже и той, първо е нужно подходящо външно съединение с релацията на всички курсове.
При групиране резултатът може да съдържа групиращите атрибути и агрегатите. Произволно негрупирано ИмеКурсист няма еднозначна стойност за група с няколко курсисти. Селекция преди γ филтрира входните редове, а селекция след γ филтрира груповите резултати; това съответства на разликата WHERE/HAVING. За „курсове с поне двама записани“ условието Брой ≥ 2 се прилага след броенето.
При празен вход групиране по Курс дава нула групи. Глобална SQL агрегация без GROUP BY връща един обобщаващ ред дори за празен вход. Когато алгебричната нотация се разширява с този специален случай, конвенцията трябва да се заяви, вместо резултатът да се отгатва.
4. Разширена проекция
Разширената проекция позволява изрази, константи и преименуване, а не само избор на съществуващи атрибути. За РЕД(Артикул, Количество, Цена) изразът πАртикул, Количество*Цена→Стойност(РЕД) изчислява стойност на всеки ред. От (Книга, 2, 15) се получава (Книга, 30). Това е изчисление за отделен кортеж; сумирането на всички стойности вече е агрегатна операция.
Ако семантиката е мултимножествена, еднаквите резултатни редове остават повторени. При NULL в аритметичен операнд SQL обикновено получава NULL; заместването с 0 изисква изрично и смислово оправдано правило. Неприложима цена и действителна нулева цена не са непременно един и същ факт.
5. Сортиране
Операторът τ подрежда редовете по списък атрибути с посоки. Например τСредна DESC, Курс ASC сортира първо по намаляваща средна оценка, а при равенство — по код на курса. Резултатът е последователност; обикновената релация сама по себе си няма ред. Следващо преобразуване не е длъжно да пази подредбата, ако семантиката му не го заявява.
При равни всички използвани ключове за сортиране взаимният ред може да не е определен. За устойчиво страниране се добавя уникален разграничител. Мястото на NULL и езиковото сравняване на низове зависят от избраните правила и СУБД; не са универсални свойства на символа τ.
6. Външни съединения
Вътрешното съединение губи редовете без партньор. Лявото външно съединение R ⟕ S връща съвпаденията и добавя всеки несъвпаднал ред от R, допълнен с NULL за атрибутите на S. Дясното пази несъвпадналите от S, а пълното — и от двете страни. При няколко партньора остават всички съвпадения; външното съединение не означава „точно един резултатен ред на входен ред“.
КУРС(Код, Име) = {(К01, SQL), (К02, ER)}
ЗАПИСВАНЕ(Код, Курсист) = {(К01, У01), (К01, У02)}
КУРС ⟕ ЗАПИСВАНЕ =
{(К01, SQL, У01), (К01, SQL, У02), (К02, ER, NULL)}При последващо групиране COUNT(*) за К02 е 1 заради добавения ред; COUNT(Курсист) е 0. За броя действителни записвания се брои не-NULL идентификаторът на записването. Ако след join поставим WHERE Курсист = 'У01', NULL редът не удовлетворява условието и отпада. Преместването на условие от ON във WHERE може да промени смисъла на outer join.
Разширените операции правят алгебрата полезна за реалните справки, но изискват внимателно заявяване на кратности, NULL, групиране и ред. Точно тези уточнения обясняват много резултати, които иначе изглеждат неочаквани в SQL.
Литературна опора и граници на сверяването
Официална библиография на СУ — номерата по-долу са от нея.
- СУ 11 — Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom, Database Systems: The Complete Book, 2. изд., Prentice Hall, 2008. Пълен текст, наличен в проекта и използван за сверка.
- Точно четене: §5.2.1–5.2.7; печатни 214–221 / PDF 251–258. PDF страниците са поредни от началото на локалния файл; в това копие печатната страница + 37 дава PDF страницата.
- Страница на авторите за второто издание. Примерите тук са авторски. Означенията са обяснени в текста; SQL уточненията са разграничени от класическия модел.
Текстът и примерите са авторска учебна разработка, а не официален отговор или указание на изпитна комисия на СУ. Номерацията на разделите в плана е авторска. Препратките показват опората за понятията, а не че примерите са преписани от учебник.