Іспит 📚 Бази Даних

47.Команди опису мови DDL. ALTER, DROP.

SQL ALTER TABLE - зміна таблиці бази даних Команда мови SQL ALTER TABLE призначена для зміни структури стовпця таблиці бази даних, якщо таблиця вже існує. ALTER TABLE для додавання та видалення стовпця, значення за замовчуванням Запит з командою ALTER TABLE на додавання нового стовпця в таблицю повинен виглядати так: ALTER TABLE ім'я_таблиці ADD ім'я_нового_стовпця ТИП_ДАНИХ Приклад 1. Нехай дано базу даних фірми - Company. У ній є таблиця Employee (Співробітник). Потрібно додати в цю таблицю новий стовпець - SEX (Стать). Відповідний запит виглядає наступним чином: ALTER TABLE EMPLOYEE ADD SEX CHAR(1) Таким чином в таблиці з'явився новий стовпець, значення якого можуть приймати символьний тип і складаються з одного символу. Нехай тепер потрібно встановити значення записів в таблиці за замовчуванням. Запит з командою ALTER TABLE для цієї мети містить ще одну команду - ALTER COLUMN - і має наступний синтаксис: ALTER TABLE ІМ'Я_ТАБЛИЦІ ALTER COLUMN ІМ'Я_СТОВБЦЯ SET DEFAULT 'ЗНАЧЕННЯ_ПО_ЗАМОВЧЕННЮ' Приклад 2. Нехай у фірмі, база даних якої має назву Company, трудяться в основному жінки. Адміністратор бази даних вирішив скоротити процес заповнення даними стовпчика SEX (Стать) і встановити його значення за замовчуванням - 'F' (що означає жіноча стать). Відповідний запит виглядає наступним чином: ALTER TABLE EMPLOYEE ALTER COLUMN SEX SET DEFAULT 'F' Тепер якщо новий співробітник - чоловік, тільки буде потрібно вписувати в стовпець SEX значення 'M', а в більшості випадків значення встановиться за замовчуванням - 'F'. 21 За допомогою запиту з командою ALTER TABLE можна і видалити стовпець з таблиці. Синтаксис команди буде наступним: ALTER TABLE ІМ'Я_ТАБЛИЦІ DROP COLUMN ІМ'Я_СТОВБЦЯ Приклад 3. Нехай в тій же базі даних Company з таблиці Employee потрібно видалити стовпець SEX (Стать). Відповідний запит виглядає наступним чином: ALTER TABLE EMPLOYEE DROP COLUMN SEX Таким чином в таблиці з'явився новий стовпець, значення якого можуть приймати символьний тип і складатися з одного символу. ALTER TABLE для додавання ключів Якщо при створенні таблиці бази даних не був визначений первинний ключ (PRIMARY KEY), то це може бути зроблено за допомогою команди ALTER TABLE. Синтаксис команди в разі простого первинного ключа наступний: ALTER TABLE ІМ'Я_ТАБЛИЦІ ADD PRIMARY KEY(ІМ'Я_ІДЕНТИФІКАТОРА) Синтаксис команди в разі складеного первинного ключа: ALTER TABLE ІМ'Я_ТАБЛИЦІ ADD PRIMARY KEY(ІМ'Я_ІДЕНТИФІКАТОРА_1, ІМ'Я_ІДЕНТИФІКАТОРА_2) Приклад 4. Працюємо все з тією ж базою даних Company, зі стовпцем EMPLOYEE. Ми можемо додати простий первинний ключ цього стовпчика у вигляді ідентифікатора співробітника і зробити це так: ALTER TABLE EMPLOYEE ADD PRIMARY KEY(EMP_ID) Якщо вирішено використовувати складений ключ, що складається з ідентифікатора співробітника та ідентифікатора відділу, в якому співробітник працює, то використовуємо наступний запит: ALTER TABLE EMPLOYEE ADD PRIMARY KEY(EMP_ID, DEPT_ID) 22 ALTER TABLE для додавання і видалення обмежень Нагадаймо, що при видаленні будь-якого рядка з однієї таблиці (1), в іншій таблиці (2), рядок якої посилається на рядок таблиці (1), не повинно бути посилань, які не містяться в таблиці (1), тобто посилань, що ведуть в нікуди. Команда з виразом ALTER TABLE може знадобитися для установки значення NULL для такого посилання. Її синтаксис наступний: ALTER TABLE ІМ'Я_ТАБЛИЦІ_ЯКА_ПОСИЛАЄТЬСЯ ADD CONSTRAINT ІМ'Я_ІДЕНТИФІКАТОРА FOREIGN KEY(ІМЯ_ІДЕНТИФІКАТОРА) REFERENCES ІМ'Я_ТАБЛИЦІ_НА_ЯКУ_ПОСИЛАЮТЬСЯ ON DELETE SET NULL Приклад 5. Нехай в тій же базі даних Company потрібно встановити Посилальне обмеження: при видаленні будь-якого відділу з таблиці Dept в таблиці Employee, містяться посилання на ці відділи, значення посилання (зовнішнього ключа) має бути встановлено в NULL (повністю невизначений). Відповідний запит виглядає наступним чином: ALTER TABLE EMPLOYEE ADD CONSTRAINT DEPT_ID FOREIGN KEY(DEPT_ID) REFERENCES DEPT ON DELETE SET NULL Таким чином, якщо в таблиці Employee є співробітник, який працює у відділі, який раптом був ліквідований і запис про нього видалений з таблиці Dept, то в запису про цього співробітника значення стовпця DEPT_ID прийме значення NULL. Запитом з командою ALTER TABLE можна також встановити заборону на видалення записів, що посилаються з таблиці (1), якщо існує хоча б один запис в таблиці (2), на який посилається запис в таблиці (1). Синтаксис такого запиту наступний: ALTER TABLE ІМ'Я_ТАБЛИЦІ_ЯКА_ПОСИЛАЄТЬСЯ ADD CONSTRAINT ІМ'Я_ІДЕНТИФІКАТОРА FOREIGN KEY(ІМ'Я_ІДЕНТИФІКАТОРА) REFERENCES ІМ'Я_ТАБЛИЦІ_НА_ЯКУ_ПОСИЛАЮТЬСЯ ON DELETE RESTRICT 23 Приклад 6. Далі працюємо з базою даних мережі аптек. Наступна команда з виразом ALTER TABLE встановлює заборону на видалення групи препаратів з таблиці Group, якщо існує хоча б один препарат цієї групи, що визначається записом в таблиці Preparation: ALTER TABLE PREPARATION ADD CONSTRAINT GR_ID FOREIGN KEY(GR_ID) REFERENCES GROUP ON DELETE RESTRICT Встановлюємо заборону на видалення аптеки з таблиці Pharmacy, якщо існує хоча б один співробітник цієї аптеки, що визначається записом в таблиці Employee: ALTER TABLE EMPLOYEE ADD CONSTRAINT PH_ID FOREIGN KEY(PH_ID) REFERENCES PHARMACY ON DELETE RESTRICT Запитом з командою ALTER TABLE можна також визначити, що якщо видалити запис в таблиці (2), на яку посилається запис в таблиці (1), то повинні бути видалені всі відповідні записи в таблиці (1) (каскадне видалення). Синтаксис такого запиту наступний: ALTER TABLE ІМ'Я_ТАБЛИЦІ_ЯКА_ПОСИЛАЄТЬСЯ ADD CONSTRAINT ІМ'Я_ІДЕНТИФІКАТОРА FOREIGN KEY(ІМ'Я_ІДЕНТИФІКАТОРА) REFERENCES ІМ'Я_ТАБЛИЦІ_НА_ЯКУ_ПОСИЛАЮТЬСЯ ON DELETE CASCADE Приклад 7. Продовжуємо працювати з базою даних мережі аптек. Знову змінюємо таблицю AVAILABILITY і визначаємо, що якщо видалити препарат з таблиці PREPARATION, то повинні піти всі записи цього препарату в таблиці AVAILABILITY: ALTER TABLE AVAILABILITY ADD CONSTRAINT PR_ID FOREIGN KEY(PR_ID) REFERENCES PREPARATION ON DELETE CASCADE Тепер визначимо, що якщо видалити аптеку з таблиці PHARMACY, то повинні піти всі записи цієї аптеки в таблиці AVAILABILITY: ALTER TABLE AVAILABILITY ADD CONSTRAINT PH_ID FOREIGN KEY(PH_ID) REFERENCES PHARMACY ON DELETE CASCADE 24 Наступна команда модифікує таблицю DEFICIT і визначає, що якщо видалити препарат з таблиці PREPARATION, то повинні піти всі записи цього препарату в таблиці DEFICIT: ALTER TABLE DEFICIT ADD CONSTRAINT PR_ID FOREIGN KEY(PR_ID) REFERENCES PREPARATION ON DELETE CASCADE Знову модифікуємо таблицю DEFICIT і визначаємо, що якщо видалити аптеку з таблиці PHARMACY, то повинні піти всі записи цієї аптеки в таблиці DEFICIT: ALTER TABLE DEFICIT ADD CONSTRAINT PH_ID FOREIGN KEY(PH_ID) REFERENCES PHARMACY ON DELETE CASCADE Запитом з командою ALTER TABLE можна встановити перевірочне обмеження. Синтаксис такого запиту наступний: ALTER TABLE ІМ'Я_ТАБЛИЦІ ADD CONSTRAINT CHECK_DATA CHECK(УМОВА_ЩО_ПЕРЕВІРЯЄТЬСЯ) Приклад 8. Продовжуємо працювати з базою даних мережі аптек. Потрібно модифікувати таблицю AVAILABILITY і визначити, що в одному і тому ж записі значення атрибута DateStart таблиці не повинно бути менше значення DateEnd: ALTER TABLE AVAILABILITY ADD CONSTRAINT CHECK_DATA CHECK(DateStart < DateEnd) Змінюємо таблицю DEFICIT і визначаємо, що в одному і тому ж записі значення атрибута DateStart таблиці не повинно бути менше значення DateEnd: ALTER TABLE DEFICIT ADD CONSTRAINT CHECK_DATA CHECK(DateStart < DateEnd)

1. Основні поняття та архітектура. Основні вимоги до систем керування базами даних.

--Бази даних зазвичай допускають наступні чотири типи взаємодії: ● Визначення: створення, модифікація, та видалення визначень; ● Оновлення: вставка, зміна та видалення даних; ● Отримання: надання доступу до збережених даних; ● Адміністрування: керування користувачами, безпекою, моніторинг продуктивності тощо. --Керування формою та структурою даних у системі У базах даних, які працюють з регулярними даними, такі як реляційні БД, ці визначення часто називають схемою бази даних. Схема бази даних - це чіткий план того, як дані повинні бути відформатовані, щоб їх прийняла конкретна БД. Охоплює конкретні поля, які повинні бути присутніми в окремих записах, а також вимоги до таких значень, як тип даних, довжина поля, мінімальні чи максимальні значення --Оновлення даних для передачі, зміни та видалення даних із системи Повернення даних не впливає на жодну інформацію, що в даний час зберігається в базі даних, ці дії називаються операціями зчитування (read) - це основний спосіб збору даних, які вже зберігаються в БД. СКБД майже завжди мають прямий спосіб доступу до даних за допомогою унікального ідентифікатора (id), який часто називають первинним ключем (primary key). Це дозволяє отримати доступ до будь-якого запису, просто надавши ключ. --Адміністрування системи керування базами даних Операції адміністрування, які входять до цієї групи, включають: ➢ Керування користувачами, дозволами, автентифікацією та авторизацією. ➢ Налаштування та підтримка резервних копій. ➢ Налаштування резервного носія для зберігання. ➢ Управління реплікацією та інші міркування щодо масштабування. ➢ Надання варіантів офлайн та онлайн відновлення.

17.Друга нормальна форма (2НФ). Повна функціональна залежність.

--Повна функціональна залежність. Якщо А і B - атрибути відношення, то атрибут B знаходиться в повній функціональній залежності від атрибута А, якщо атрибут B є функціонально залежним від А, але не залежить від жодної власної підмножини атрибута А. --Тобто функціональна залежність А→B є повною функціональною залежністю, якщо видалення будь-якого атрибута з А (складений атрибут) призводить до втрати цієї залежності. • Функціональна залежність А→B називається частковою, якщо в А є якийсь атрибут, при видаленні якого ця залежність зберігається. --Друга нормальна форма. відношення, яке знаходиться в першій нормальній формі і кожен атрибут якого, що не входить до складу первинного ключа, характеризується повною функціональною залежністю від цього первинного ключа. --Нормалізація відношень 1НФ з приведенням до форми 2НФ передбачає усунення часткових залежностей. Якщо у відношенні між атрибутами існує часткова залежність, то функціональнозалежні атрибути видаляються з нього і поміщаються в нове відношення разом з копією їх детермінанта. --Типи функціональних залежностей. • Вважається, що атрибут B відношення R функціонально залежить від атрибута A того ж відношення, якщо в кожний момент часу кожному значенню атрибута A відповідає не більш ніж одне значення атрибута B. • Функціональна залежність відображається так: A→B. --Приклад. Розглянемо відношення «ВИКЛАДАЧ_ПРЕДМЕТ» з складеним первинним ключем "табельний_номер" і "назва_предмету". Вважаємо, що викладач закріплений за однією кафедрою. --В цьому відношенні атрибут "посада" функціонально залежить від атрибута "оклад", "табельний_номер" від атрибута "прізвище ", "прізвище" від атрибута " табельний_номер" і т. д. Атрибут може функціонально залежати від цілої групи атрибутів. --Якщо неключовий атрибут залежить від всього складеного ключа і не знаходиться в частковій залежності від його частин, то говорять про його повну функціональну залежність від складеного ключа. • У відношенні ВИКЛАДАЧ_ПРЕДМЕТ не має атрибутів, які знаходяться в повній функціональній залежності від складеного ключа. • Якщо всі можливі ключі відношення містять по одному атрибуту, то таке відношення є відношенням в 2НФ, так як всі атрибути, які не є первинними, повністю залежать від можливих ключів. Якщо ключі складаються більш ніж з одного атрибута, то відношення, яке задане в 1НФ може не бути відношенням у 2НФ. --Якщо для атрибутів A, B, C виконуються умови A→B, B→C, а зворотна залежність відсутня, то говорять, що C залежить від A транзитивно. Наприклад, "прізвище"→"кафедра"→"телефон". • У відношеннях між атрибутами може існувати ще один тип залежності - багатозначна залежність. --У відношенні R атрибут B багатозначно залежить від A (A→→B), якщо кожному значенню A відповідає множина значень B, ніяк не пов'язаних з іншими атрибутами з R. • Багатозначна залежність можлива при наявності у відношенні хоча б трьох атрибутів: ключа і не менш двох незалежних один від одного атрибутів. --Наприклад, розглянемо відношення «ВИКЛАДАЧ_ РОЗКЛАД» Між викладачами і групами студентів є зв'язок типу "один-до-багатьох" (1:М), оскільки викладач може вести курсові проекти в одній і більше групах, однак кожній групі відповідає один викладач. • Між викладачем і предметами є зв'язок типу "багато-до-багатьох" (M:N), оскільки викладач може читати один і більше предметів, і навпаки, один предмет можуть читати декілька викладачів. --У відношенні «ВИКЛАДАЧ_ПРЕДМЕТ» можна відмітити часткову функціональну залежність атрибутів "прізвище", "посада", "оклад", "кафедра", "телефон" від частини "табельний_номер" складеного ключа. --Така часткова залежність приводить до наступних аномалій: 1. Має місце дублювання даних про викладача, оскільки викладач може читати декілька предметів. 2. Існує проблема контролю надлишковості даних, так як зміна, наприклад, окладу спричиняє необхідність пошуку та зміни значень окладів у всіх кортежах з даним викладачем. 3. Виникає проблема з викладачами, які вданий час не ведуть предмети. --1. Відношення знаходиться у другій нормальній формі, якщо воно знаходиться у 1НФ і кожний неключовий атрибут функціонально повно залежить від складеного ключа. 2. Побудувати проекцію на частину складеного ключа і атрибути, залежні від цієї частини; 3. Побудувати проекцію без атрибутів, які знаходяться в функціональній залежності від складеного ключа. --Отримаємо два відношення ВИКЛАДАЧ і ПРЕДМЕТ, які знаходяться у 2НФ Приведення до 2НФ можна описати наступним чином. Нехай дана змінна-відношення R(A,B,C,D) PRIMARY KEY {A,B} і є функціональна залежність A→D. Процедура нормалізації в 2НФ передбачає заміну цієї змінноївідношення наступними двома проекціями R1 і R2: R1(A,D) PRIMARY KEY {A} R2(A,B,C) PRIMARY KEY {A,B} FOREIGN KEY {A} REFERENCES R1

53.Збережені функції

6. Збережені функції Крім форми CREATE PROCEDURE, що створює процедуру, допускається використання CREATE FUNCTION, яка створює функцію. Функція на відміну від процедури може викликатися безпосередньо, без використання оператора CALL і повертати одне значення, яке підставляється на місце виклику функції, як у випадку вбудованих функцій MySQL. Створимо найпростішу функцію say_hello (), яка буде приймати єдиний вхідний параметр з ім'ям name і повертати фразу "Hello, name!", Де замість підрядка name буде підставлено значення параметра name (приклад). CREATE FUNCTION say_hello(name CHAR(20)) RETURNS CHAR(50) BEGIN RETURN CONCAT(' Hello, ',name,'!'); END // SELECT say_hello('world'), say_hello('softtime')// say_hello('world') say_hello('softtime') Hello, world! Hello, softtime! Після оголошення параметрів функції слідує оператор returns, який задає тип повертається функцією значення. Повернути значення з функції можна за допомогою оператора return (наступний приклад), який може бути викликаний у будь-якій точці функції. Виклик оператора return означає, що функція повинна негайно завершити виконання і повернути значення, передане в якості аргументу оператора return. Важливо. - При оголошенні параметрів функції використання ключових слів IN, INOUT та OUT неприпустимо. Всі параметри, передані функцією, є вхідними. 17 - Функція обов'язково повинна містити оператор RETURNS, що встановлює тип значення функції, і хоча б один оператор RETURN в тілі функції, який повертає це значення. Наприклад: CREATE FUNCTION func_catalog(id INT) RETURNS TINYTEXT BEGIN DECLARE catalog TINYTEXT; SELECT name INTO catalog FROM CATALOGS WHERE id_catalog = id LIMIT 1; RETURN catalog;" SELECT name INTO catalog FROM CATALOGS WHERE id_catalog = id + 1 LIMIT 1; RETURN catalog; END // SELECT func_catalog (1)// func_catalog(1) Процессори Збережена функція func_catalog() приймає єдиний параметр id, первинний ключ таблиці CATALOGS. Прийнявши, як параметр id значення 1, функція повертає результат ("процесори"), досягнувши першого оператора RETURN. При цьому другий оператор SELECT і RETURN не досягаються ніколи (інакше поверталося б значення "Оперативна пам'ять"). Це не означає, що двох операторів RETURN у тілі функції не повинно зустрічатися. Нижче будуть розглянуті умовні конструкції, що дозволяють в залежності від умов вибирати варіант, який повинен повертатися - в такій ситуації використання множинного виходу з функції за допомогою декількох операторів RETURN не уникнути. Якщо послідовність операторів, які вирішено оформити у вигляді збереженої процедури, повертають єдине значення, набагато зручніше оформити їх у вигляді збереженої функції, так як працювати з ними у виразах набагато зручніше. Наприклад, створимо дві функції: coun_jproduct_in_catalog() та count_product(), які будуть повертати загальне число товарних позицій в каталозі і загальне число товарних позицій в навчальному електронному магазині shop: CREATE FUNCTION count_product_in_catalog(id INT) RETURNS INT BEGIN DECLARE total INT; SELECT SUM(count) INTO total FROM PRODUCTS WHERE id_catalog = id LIMIT 1; 18 RETURN total; END // CREATE FUNCTION count_product() RETURNS INT BEGIN DECLARE total INT; SELECT SUM(count) INTO total FROM PRODUCTS; RETURN total; END// SELECT count_product_in_catalog(1) AS total, count_product_in_catalog(1)/countjproduct()*100 AS persent// total persent 56 26.5403 Як видно з прикладу, функції зручно використовувати у виразах в яких, наприклад, підрахувати процентний вклад товарних позицій каталогу в загальне число товарів в електронному магазині shop.

7. Ієрархічна модель даних.

Ієрархічна модель даних. --Ієрархічна модель дозволяє будувати бази даних з деревовидною структурою, де кожен вузол містить свій тип даних (сутність (entity) - це дещо, про що зберігає інформацію) На верхньому рівні дерева в цій моделі є один вузол - корінь, на наступному рівні розташовуються вузли, пов'язані з цим коренем, потім вузли, пов'язані з вузлами попереднього рівня і т.д. При цьому кожен вузол може мати тільки одного предка. Графічно: Предок - вузол на кінці стрілки, а Нащадок - вузол на вістрі стрілки (рис. 2.1). Пошук даних в ієрархічній системі завжди починається з кореня. Потім проводиться спуск з одного рівня дерева на інший, поки не буде досягнутий шуканий рівень. Переміщення між пунктами від одного запису до іншого здійснюються за допомогою посилань. У базах даних визначено, що вузли - це типи записів, а стрілки представляють відносини один-до-одного або один-до-багатьох. Основна перевага ієрархічної моделі: - простота опису ієрархічних структур (обсяг займаного ОЗУ невеликий). Недолік: - щоразу починати пошук потрібних даних необхідно з кореня, що збільшує час пошуку необхідної інформації. Зазначений недолік знятий у мережній моделі, де можливі зв'язки всіх інформаційних об'єктів з усіма.

35.Агрегатні (статичні) функції SQL. COUNT, SUM, AVG, MIN, MAX.

Агрегатні (статичні) функції SQL -Агрегатні функції призначені для обчислення підсумкових значень на основі всіх записів набору даних або на основі певної групи рядків. Запити можуть виконувати узагальнене групове значення полів так як і значення одного поля. Це робиться за допомогою агрегатних функцій. Агрегатні функції виконують одиночне значення для всієї групи таблиці. Є список цих функцій: COUNT (вираз) - ця функція обчислює кількість входжень відповідного виразу у всі рядки чи у групу рядків результуючого набору даних SUM (вираз) - ця функція обчислює суму значень виразу по всіх рядках чи по групі рядків результуючої таблиці AVG (вираз) - ця функція обчислює середнє арифметичне виразу MAX (вираз) - ця функція обчислює максимальне значення виразу MIN (вираз) - ця функція обчислює мінімальне значення виразу -Слід розрізняти два випадки застосування агрегатних функцій. • Перший: агрегатні функції використовуються самі по собі і повертають одне результуюче значення. • Другий: агрегатні функції використовуються з оператором SQL GROUP BY, тобто з угрупованням по полях (стовпцях) для отримання результуючих значень в кожній групі. -• Функція COUNT підраховує кількість записів в таблиці. • Умова, за якою обиратимуться записи, задається за допомогою команди WHERE. • Команда WHERE не є обов'язковою, якщо її не вказати - будуть підраховані всі записи в таблиці. -Синтаксис: 1) Підрахунок всіх записів: SELECT COUNT(*) FROM ім'я_таблиці WHERE умова 2) Підрахунок всіх записів, де задане поле не рівно NULL: SELECT COUNT(поле) FROM ім'я_таблиці WHERE умова 3) Тільки унікальні значення поля: SELECT COUNT(DISTINCT поле) FROM ім'я_таблиці WHERE умова -Функція SUM • Функція SUM підсумовує значення зазначеного поля по всіх вибраних рядках. • Наприклад, якщо у нас є поле 'зарплата', ми можемо знайти сумарну зарплату всіх працівників. Синтаксис: SELECT SUM(поле) FROM ім'я_таблиці WHERE умова -Функція AVG • Функція AVG повертає середнє арифметичне по всіх знайдених записах. Середнє арифметичне групи чисел - це їх сума, поділена на їх кількість. • Наприклад, у нас є таблиця з користувачами, в якій зберігаються їх віку. За допомогою AVG ми можемо знайти їх середній вік. Синтаксис: SELECT AVG(поле) FROM ім'я_таблиці WHERE умова -Наприклад дано таблицю, як і в попередньому прикладі, workers. Приклад 1. Знати середню зарплату по всій таблиці: SELECT AVG(salary) as avg FROM workers -Функція MIN повертає мінімальне значення поля серед знайдених рядків. Синтаксис: SELECT MIN(поле) FROM ім'я_таблиці WHERE умова -Функція MAX повертає максимальне значення поля серед знайдених рядків. Синтаксис: SELECT MAX(поле) FROM ім'я_таблиці WHERE умова

29.Операції над множинами. Вибірка. Проекція.

Вибірка S предикат (R) Операція вибірки застосовується до одного відношення R і визначає результуюче відношення, яке містить тільки ті кортежі (рядки) з відношення R, які задовольняють заданій умові (предикату). R і S - це два відношення, визначені на атрибутах А = (а1, а2,.. Аn) і В = (b1, b 2,..., B м) відповідно. 8 Вибірка - це скорочена назва -вибірки, де позначає будь-який скалярний оператор порівняння (=, , >, , ≤, <). -вибіркою з відношення А по атрибутам X Y (у цьому порядку). A WHERE X Y називається відношення, що має той самий заголовок, що і відношення А і тіло, що містить множину всіх кортежів відношення А, для яких перевірка умови X Y дає значення true. Атрибути X і Y повинні бути визначені на одному і тому ж домені, а оператор повинен мати сенс для цього домену. --Проекція P аi, аj, ..., az (R) застосовується до одного відношення, (R) і визначає нове відношення містить вертикальну підмножину відношень R, створювану за допомогою вилучення значень зазначених атрибутів. Проекцією відношення А по атрибутам X, Y, ..., Z, де кожен з атрибутів належить відношенню А [X, Y, ..., Z] називається відношення з заголовком {X, Y, ..., Z} і тілом, що містить множину всіх кортежів {Х: х, Y: y, ..., Z: z}, таких, для яких у відношенні А значення атрибута X рівне х, атрибута Y рівне y, ..., атрибута Z рівне z. Таким чином, за допомогою оператора проекції отримано "вертикальну" підмножину даного відношення, тобто підмножина, що отримується виключенням всіх атрибутів, не вказаних у списку атрибутів, і наступним виключенням дублюючих кортежів. Початкове відношення A і результат операції проекції відношення A по атрибуту CityName. Ніякий атрибут не може бути вказаний в списку атрибутів більш ніж один раз. Синтаксис дозволяє опустити список атрибутів зовсім (разом з квадратними дужками). Дія такої операції є еквівалентною вказівнику списку всіх атрибутів вихідного відношення, тобто така операція є тотожною проекції. Іншими словами, ім'я відношення є допустимим реляційним виразом. Проекція виду R [], тобто така, в якій список атрибутів не пропущений, але порожній, теж допустима. Вона являє собою "нульову" проекцію.

57.Тригери. Визначення. Використання.

Визначення тригерів -Тригер - це механізм, який викликається, коли в зазначеній таблиці відбувається певна дія. Кожен тригер має такі основні складові: • ім'я, • дія, • виконання. Ім'я тригера може містити максимум 128 символів. Дією тригера може бути або інструкція DML, або інструкція DDL. Виконавча складова тригера зазвичай складається з збереженої процедури або пакета. Тригери виконуються після застосування правил та інших перевірок цілісності посилань, тому якщо операція не проходить ці попередні перевірки, тригери не виконуються. SQL підтримує два основних типи тригерів: • DML-тригери • DDL-тригери. Тригери - це збережені процедури особливого типу, що автоматично вступають в силу, якщо відбувається подія, що зачіпає таблицю або відношення, визначене в тригері. --Використання тригерів DML-тригери виконуються при виникненні подій мови маніпулювання даними (INSERT, UPDATE, DELETE) в базі даних. Тригери DML можуть використовуватися для написання бізнес-правил та правил цілісності даних, виконання запитів до інших таблиць і включення складних інструкцій SQL. Застосування DML-тригерів: • Для забезпечення цілісності. Тригери DML дозволяють каскадно проводити зміни через зв'язані таблиці в базі даних; але ці зміни можуть здійснюватися більш ефективно з використанням каскадних обмежень посилальної цілісності. • Для створення бізнес-правил • Як додатковий засіб безпеки. При використанні тригерів DML може статися відкат змін, що порушують посилальну цілісність, що приводить до заборони модифікації даних. Подібні тригери можуть застосовуватися при зміні зовнішнього ключа у випадках, коли нове значення не відповідає первинному ключу. Зазвичай в зазначених випадках використовуються оператор обмеження FOREIGN KEY. DDL-тригери спрацьовують у відповідь на деякі інструкції мови визначення даних (CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS). Застосування DDL-тригерів: • Запобігти внесення певних змін в схему бази даних. • Виконати в базі даних деякі дії у відповідь на зміни в схемі бази даних. • Записувати зміни або події схеми бази даних.

3. Основні поняття і архітектура. Використання баз даних.

Використання баз даних Розглянемо приклад типового веб-додатку з базами даних. Уявімо, що додаток містить основний магазин і здійснює продаж товарів, які можна відстежувати в описі. --Зберігання та обробка даних сайту У нашому прикладі БД заповнює більшу частину вмісту сайту, включаючи інформацію про товар, деталі товарно-матеріальних цінностей та інформацію про профіль користувача. База даних буде задіяна при: ● відображенні поточних та минулих замовлень; ● розрахунку вартості доставки та застосуванні знижок шляхом перевірки кодів знижок або підрахунку частих винагород клієнтів; ● створенні замовлень, поєднуючи інформацію про товар, товарний запас та інформацію про користувачів. --Аналіз інформації для прийняття оптимальних рішень Можливість детальної відповіді на запитання про те, як продаються різні товари, хто ваші найвигідніші користувачі та які фактори впливають на ваші продажі. Такі типи операцій часто називають бізнес-аналітикою або аналітикою. У нашому прикладі, з БД можна отримати відповіді на питання про тенденції в продуктах, реєстраційних номерах користувачів, в яких зазначено, що ми постачаємо найбільше, або хто є нашими лояльними користувачами. --Керування конфігурацією програмного забезпечення Після завантаження, програми можна налаштувати так, щоб спостерігати за змінами ключів, пов'язаних з їх конфігурацією. Якщо виявлено зміну, програма може переконфігурувати себе, щоб використовувати нову конфігурацію. Цей процес іноді керується процесом управління, який з часом розгортає нові значення, змінюючи активну конфігурацію з часом, щоб зберегти доступність. --Збір журналів (logs), подій та інших результатів Запущені додатки можуть генерувати великий обсяг виведення результатів. Сюди входять файли журналів, події та інші результати. Вони можуть бути записані на диск або в інше місце, але це обмежує їх корисність. Збір цього типу даних в БД спрощує роботу, виявляє закономірності і аналізує події, коли трапляється щось неочікуване, або коли вам потрібно дізнатися більше про історію продуктивності.

32.Створення запиту. Команда SELECT. Реляційні операції.

Всього в SQL існує приблизно 40 команд. Кожна команда просить СУБД виконати певну дію: вибрати дані, створити таблицю чи вставити в таблицю нові дані. Кожна команда SQL починається із ключового слова, яке визначає дію, що виконується командою. Команда містить одну або кілька секцій. Секція описує дані, з якими працює команда, або містить уточнюючу інформацію про дії, що виконуються командою. Кожна секція також починається із ключового слова, наприклад: WHERE, FROM, INTO. Деякі секції в команді обов'язкові, а деякі необов'язкові. Конкретна структура та зміст секцій залежить від команди. Багато секцій містять імена таблиць чи стовпчиків, деякі секції містять додаткові ключові слова, константи, вирази. Команда SELECT. Команда SELECT отримує записи з бази даних по певній умові, що задається за допомогою команди where. Ці записи можна відсортувати за допомогою команди order by, а також можна обмежити їх кількість за допомогою limit. Синтаксис команди SELECT: SELECT * FROM ім'я_таблиці SELECT * FROM ім'я_таблиці WHERE умова SELECT поле1, поле2... FROM ім'я_таблиці WHERE умова

6. Дані та їхня семантика. Моделювання даних.

Дані та їхня семантика. Моделювання даних. --Одними з основних у концепції БД є узагальнені категорії «дані та модель даних». Слово «дані» походить від латинського «datum» - факт, проте дані не завжди відповідають конкретним чи навіть реальним фактам. Іноді вони не точні або описують те, чого насправді не існує. Даними ми вважатимемо опис будьякого явища, що викликає зацікавленість через певні потреби. З даними нерозривно пов'язана їхня інтерпретація (або семантика), тобто той зміст, який їм приписується. Інтерпретація даних може бути статичною та незмінною, а може різнитися. Наприклад, розробника ПЗ можна розглядати, як працівника фірми, з точки зору замовника - як виконавця замовлення, а з точки зору медичного страхування - як споживача. Так, системні адміністратори, розробники, тестери, менеджери незалежно від роду своєї діяльності можуть розглядатися в кадровій системі, як працівники фірми (в нашому випадку, як ФОПи). Дані - це набір конкретних значень та параметрів, що характеризують об'єкт. Існує багато типів моделей - фізичні, математичні, економічні тощо, які відображають різні аспекти реального світу. Модель даних відображає уявлення про реальний світ. Вважатимемо, що модель даних - це сукупність структури даних, операцій над ними (операції маніпулювання даними) та обмежень цілісності. Іншими словами, модель даних можна розуміти, як концептуальний (що відповідає деякій концепції, деяким правилам) опис предметної області. Вона включає визначення сутностей (все те, про що можна зберігати інформацію) та їх атрибутів: наприклад, сутність Customer (Замовник) може мати атрибути Name (Ім'я) та Address (Адреса). Предметна область має складну структуру і невпорядкована - і це природно, адже якби вона була простою і впорядкованою, то не було б потреби в її моделі. Основою для будь-якої структури даних є відображення елементарної одиниці даних у вигляді такої трійки: <об'єкт, властивість об'єкта, значення властивості>. Сукупність взаємопов'язаних між собою елементарних одиниць даних може відображатися різноманітними способами, що призводить до 2 формування різних структур, а відтак - різних моделей. За способом встановлення зв'язків між даними розрізняють ієрархічну, мережну та реляційну моделі.

28.Операції над множинами. Декартовий добуток.

Декартовий добуток RxS. Операція декартового добутку визначає нове відношення, яке є результатом конкатенації (зчеплення) кожного кортежу з відношення R з кожним кортежем з відношення S. Операція декартового добутку застосовується для множення двох відношень. Множенням двох відношень називається створення іншого відношення, що складається з усіх можливих пар кортежів обох відношень. Отже, якщо одне відношення має I кортежів і N атрибутів, а інше - J кортежів і М атрибутів, то їх декартовий добуток буде містити (I х J) кортежів і (N + М) атрибутів. Якщо подібні відношення містять атрибути з однаковими іменами, то в цьому випадку імена атрибутів міститимуть назви відношень у вигляді префіксів. Це необхідно для забезпечення унікальності імен атрибутів у відношенні, отриманому як результат виконання операції декартового добутку. CityNo CityName RgNo 2 Кривій Ріг 1 3 П'ятихатки 1 4 Львів 2 5 Отже, декартовий добуток двох відношень, має бути множиною впорядкованих пар кортежів. Але знову таки, необхідно зберегти властивість замкнутості; інакше кажучи, результат повинен містити кортежі, а не впорядковані пари кортежів. Декартовий добуток двох відношень А і В (A TIMES B), де А і В не мають спільних імен атрибутів, визначається як відношення з заголовком, яке представляє собою зчеплення двох заголовків вихідних відносин А і В, і тілом, що складається з безлічі всіх кортежів t, таких, що t являє собою зчеплення кортежу a, що належить відношенню А, і кортежу b, що належить відношенню В. Кардинальне число результату дорівнює добутку кардинальних чисел вихідних відношень А і В, а ступінь дорівнює сумі їх ступенів. У цій таблиці нам відомі ID номери клієнтів, які здійснювали огляд техніки, але невідомі їхні прізвища тощо. Імена клієнтів зберігаються у відношенні «Клієнт», а відомості про виконані ними огляди - у відношенні «Перегляд техніки». Щоб отримати список клієнтів і коментарі про переглянуту ними техніку необхідно об'єднати ці два відношення А: ID номер, прізвище, місто (Клієнт) х В: ID Номер, Код пристрою, Коментар (Перегляд техніки) Результати виконання цієї операції показано у табл. 6.2. У такому вигляді це відношення містить більше інформації, ніж необхідно. Наприклад, перший кортеж цього відношення містить різні значення атрибута ID номер. Для отримання шуканого списку необхідно для цього відношення провести операцію вибірки з витяганням тих кортежів, для яких виконується рівність: кліент = ID номер.пр. техніки (декартовий добуток).

30.Операції над множинами. Ділення. З'єднання.

З'єднання Операція з'єднання має кілька різновидів. Однак найбільш важливим, без сумніву, є природне з'єднання, причому настільки, що для позначення виключно природного з'єднання майже постійно використовується загальний термін "з'єднання". Нехай відношення А і В мають заголовки {Xl, X2, ..., Xm, Y1, Y2, ..., Yn} і {Yl, Y2, ..., Yn, Zl, Z2, ..., Zp} відповідно, тобто атрибути Yl, Y2, ..., Yn і тільки вони - спільне для двох відношень Х1, Х2, ... ,Хm - інші атрибути відношення А; Zl, Z2, ..., Zp - інші атрибути відношення В. Припустимо також, що відповідні атрибути (тобто атрибути з однаковими іменами) визначені на одному і тому ж домені. Розглядати вираз {X1, Х2, ..., Хm}, {Y1, Y2, ..., Yn} та {Zl, Z2, ..., Zp}, як три складових атрибуту X, Y та Z, відповідно. Тоді природним з'єднанням відношення А і В (A JOIN B) називається відношення з заголовком {X, Y, Z} і тілом, що містить множину всіх кортежів {Х: х, Y: y, Z: z}, таких, для яких у відношенні А значення атрибута X дорівнює х, а атрибуту Y рівне у, і у відношенні В значення атрибута Y рівне у, а атрибуту Z дорівнює z. Приклад операції природного з'єднання наведено на рис. 6.1. З'єднання має властивості асоціативності і комутативності. Звідси випливає, що вираз: (A JOIN В) JOIN C і A JOIN (В JOIN С) можуть бути однозначно спрощені до наступного: A JOIN В JOIN C Крім того, вирази: A JOIN В та B JOIN A еквівалентні. Нижче перераховані різні типи операцій з'єднання, які дещо відрізняються один від одного і можуть бути тією чи іншою мірою корисні. • Тета -з'єднання (theta join). • З'єднання за еквівалентністю (equijoin), яке є окремим видом Тета - з'єднання. • Природне з'єднання (natural join). • Зовнішнє з'єднання (outer join). • Напівз'єднання (semijoin). Тета-з'єднання Операція тета-з'єднання R> ,> =, <, <=, =, <>). Якщо предикат F містить тільки оператор рівності (=), то з'єднання називається з'єднанням за еквівалентністю - EQUI-JOIN. --Ділення Нехай відношення А і В мають заголовки: {X1, X2, ..., Xm, Y1, Y2, ..., Yn} і {Y1, Y2, ..., Yn} відповідно, тобто атрибути Y1, Y2, ..., Yn - загальні для двох відношень, і відношення A має додаткові атрибути X1, Х2, ..., Хm, а відношення В не має додаткових атрибутів. (Відношення А і В представляють відповідно ділене і дільник.) Припустимо також, що відповідні атрибути (тобто атрибути з однаковими іменами) визначені на одному і тому ж домені. Нехай тепер вираз {X1, Х2, ..., Хm} та {Y1, Y2, ..., Yn} позначають два складових атрибута Х і Y відповідно. Тоді діленням відношень А на В (A DIVIDEBY B) називається відношення з заголовком {X} і тілом, що містить множину всіх кортежів {X: x}, таких що існує кортеж {Х: х, Y: y}, який належить відношенню A для всіх кортежів {Y: y}, що належать відношенню В. Не строго це можна сформулювати так: результат містить такі X-значення з відношення А, для яких відповідні Yзначення (з А) включають всі Y-значення з відношення В. Приклад операції ділення наведено на рис. 6.2. Відношення M є проекцією відношення Marks, а відношення S - проекцією відносини Subjects. Результатом операції ділення M DIVIDE BY S фактично містить номери студентів, які здавали дисципліни з номерами 1 і 5.

20.Теорія нормалізації даних. Визначення другої нормальної форми.

Друга нормальна форма (2НФ, 2NF) — нормальна форма, що використовується для нормалізації баз даних. 2НФ первісно була визначена 1971 року Едгаром Коддом.[1] Щоб перебувати в другій нормальній формі, таблиця, що перебуває в першій нормальній формі, має відповідати додатковим критеріям. А саме: 1НФ таблиця перебуватиме в 2НФ тоді й лише тоді, коли для будь-якого потенційного ключа K і будь-якого атрибута A, який не є частиною потенційного ключа, A залежить саме від цілого потенційного ключа, а не від його частини. Тобто, 1НФ таблиця перебуває в 2НФ тоді й тільки тоді, коли всі її неключові атрибути функціонально залежні від потенційного ключа в цілому. У разі, якщо 1НФ таблиця не має складних потенційних ключів (таких, що складаються більш ніж з одного атрибута), тоді вона автоматично перебуватиме в 2НФ.

13.Модель «Сутність-зв'язок». Ключі.

Ключі відношення. Відношення з математичної точки зору є множиною і не може містити співпадаючих елементів, тобто в будь-який момент часу ніякі два кортежі відношення не можуть бути дублікатами один одного. Таким чином, у відношенні повинен бути присутнім певний атрибут (або набір атрибутів), який однозначно визначає кожен кортеж відношення і забезпечує унікальність рядків таблиці. Такий атрибут (або набір атрибутів) називають первинним ключем відношення. Властивості первинного ключа: - унікальність: у будь-який момент часу ніякі два кортежі відношення не повинні мати одного і того ж значення; - мінімальність: жоден з атрибутів не може бути виключений з набору атрибутів первинного ключа, без порушення властивостей унікальності. В залежності від кількості атрибутів, що входять у ключ, розрізняють прості і складні (складені) ключі. Простий ключ - ключ, що містить тільки один атрибут. Як правило, в якості нього використовують найкоротший і найпростіший з можливих типів 7 даних (цілочисловий тип), при цьому операції які використовують ключ (операції об'єднання) виконуються значно швидше. Складний (складений) ключ - ключ, що складається з декількох атрибутів. Суперключ - складний ключ, з великою кількістю стовпців, що не задовольняє властивості мінімальності. Використовується вкрай рідко, коли надмірність може виявитися корисною користувачеві. З точки зору інформативності атрибуту (або декількох атрибутів) який складає первинний ключ, розрізняють штучні і природні ключі. Штучний або сурогатний ключ - ключ створюваний самою СКБД або користувачем за допомогою певної процедури, ключ сам по собі не містить інформації. Використовується для створення унікальності ідентифікаторів рядків. Ним так само замінюють занадто складні ключі. Як правило, користувачеві вони не показуються. Природний ключ - ключ, який містить лише значущі атрибути, тобто містить інформацію. До переваг природних ключів можна віднести наступні: вони несуть цілком певну інформацію, і їх використання не призводить до необхідності додавати до таблиці атрибути, значення яких для користувача не несуть ніякого сенсу і використовуються тільки для зв'язку між відношеннями, що дозволяє одержати більш компактну форму таблиць. Основним недоліком природних ключів є те, що їх використання дуже важке у випадку зміни предметної області. Значення атрибутів первинного ключа не повинні змінюватися, тобто одного разу задане значення первинного ключа для кортежу не може бути змінено. Ця вимога необхідна для підтримки посилальної цілісності бази даних, тому що зв'язок між відношеннями зазвичай встановлюється по первинному ключу. Як правило, для уникнення подібних проблем у відношення водяться штучні ключі. Іншим недоліком природних ключів є те, що, як правило, вони є складними і містять рядкові атрибути, що відображається на швидкості виконання операцій над даними і в цьому випадку так само зручніше буває вводити сурогатні ключі. У будь-якій з таблиць може виявитися кілька наборів атрибутів, які можна вибрати в якості ключа, такі набори називаються потенційними і альтернативними ключами. 8 Вторинні ключі - ключі, що мають комбінації атрибутів відмінні від комбінації атрибутів первинного ключа. Вони можуть не мати властивість унікальності. Ключі які перекриваються - складні ключі, які мають один або кілька загальних стовпців. У зв'язках між відношеннями використовують ключі, і в залежності від типу ключів, що беруть участь у зв'язках виникають різні типи зв'язків. Основною умовою зв'язку між відношеннями є збіг доменів (або типів, якщо домени не використовуються) ключів використовуваних у зв'язку. При цьому ключ зв'язку в підлеглому відношенні називається зовнішнім ключем. Іноді зовнішній ключ може посилатися на ту ж таблицю, до якої він належить, в цьому випадку він буде називатися рекурсивним

22.Проектування схем баз даних. Діаграми види діаграм.

Діаграми. Види діаграм -Виділяють три рівня логічної моделі. Цим рівням відповідають наступні діаграми: • презентаційна діаграма; • ключова діаграма; • повна атрибутивна діаграма. -1. Презентаційна діаграма. Такі діаграми описують тільки найосновніші класи сутностей і їх зв'язки. Ключі в таких діаграмах можуть не описуватися зовсім, і відповідно, зв'язки - ніяк не індивідуалізуватися. Тому допустимими є зв'язки типу «багато до багатьох», хоча зазвичай їх намагаються уникати або, якщо вони все-таки присутні, - деталізувати. -2. Ключова діаграма На відміну від презентаційних діаграм ключові діаграми описують обов'язково всі класи сутностей і їх зв'язки, правда, в термінах тільки первинних ключів. Тут зв'язки «багато до багатьох» вже неодмінно деталізуються (тобто зв'язку такого типу, у чистому вигляді тут просто не може бути задано). Багатозначні атрибути все ще допускаються так само, як і в презентаційній діаграмі, але якщо вони присутні в ключовій діаграмі, то, як правило, вони перетворюються в самостійні класи сутностей. -3. Повна атрибутивна діаграма Повні атрибутивні діаграми найбільш детально з усіх вищеназваних описують всі класи сутностей, їх атрибути та зв'язки між цими класами сутностей. Як правило, такі діаграми представляють дані, що знаходяться в третій нормальній формі, тому природно, що в базових відносинах, описаних такими діаграмами, не має бути складних чи багатозначних атрибутів, так само як і не має бути недеталізованих зв'язків типу «багато до багатьох».

40.З'єднання таблиць. SQL Join

З'єднання таблиць. SQL Join Приєднання таблиць в запитах - це базовий інструмент при роботі з базами даних. Давайте розглянемо, які приєднання (JOIN) існують, і як від них залежать результати запитів. Наприклад, дано наступні дві таблиці: це таблиця з іменами співробітників і словник з переліком посад. -Команда INNER JOIN використовуються для зв'язування таблиць за певними полями зв'язку. Тобто це внутрішнє приєднання, яке рівносильно просто JOIN або CROSS JOIN. Синтаксис: SELECT поле FROM ім'я_таблиці INNER JOIN ім'я_зв'язаної_таблиці ON умова_зв'язку WHERE умова_вибірки -Наприклад, вивести дані з таблиць тільки якщо умова зв'язування дотримується - тобто для співробітника зазначений існуючий в словнику ідентифікатор посади: -SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name `Посада` FROM `persons` p INNER JOIN `positions` ps ON ps.id = p.post_id -Умовно представимо собі ці таблиці, як дві множини, що перетинаються, де перетин - це наявність зв'язку між таблицями. -Зовнішнє приєднання. Розрізняють LEFT OUTER JOIN і RIGHT OUTER JOIN, і зазвичай опускають слово «OUTER». Зовнішнє приєднання включає в себе результати запиту INNER і додаються «невикористані» рядки з однієї з таблиць. Яку таблицю використовувати в якості «добавки» - вказує токен LEFT або RIGHT. -Команда LEFT JOIN використовуються для зв'язування таблиць за певними полях зв'язку. Синтаксис: SELECT поле FROM ім'я_таблиці LEFT OUTER JOIN ім'я_зв'язаної_таблиці ON умова_зв'язку WHERE умова_вибірки -Наприклад, використати зовнішнє приєднання «зліва»: SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name `Посада` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id -Приєднання «справа». Синтаксис: SELECT поле FROM ім'я_таблиці RIGHT OUTER JOIN ім'я_зв'язаної_таблиці ON умова_зв'язку WHERE умова_вибірки -Повна множина MySQL не знає з'єднання FULL OUTER JOIN. Що якщо потрібно отримати повну множину? -Перший спосіб - об'єднання запитів LEFT і RIGHT: (SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name `Посада` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id) UNION (SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name `Посада` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id) -Другий спосіб - об'єднання LEFT і RIGHT, але в одному із запитів ми виключаємо частину, що відповідає INNER. А об'єднання задаємо як UNION ALL, що дозволяє «движку» SQL обійтися без сортування: -(SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name `Посада` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id) UNION ALL (SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name `Посада` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE p.id IS NULL) -Ліва множина SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name `Посада` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE ps.id IS NULL У нашому прикладі - це фахівці, у яких не задана посада, чи немає посади з вказаним ключем. -Права підмножина Аналогічно виділяємо праву підмножину: SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name `Посада` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE p.id is NULL -У нашому випадку отримаємо посади, які нікому не призначені. -Все окрім перетину Залишився останній варіант, той коли виключено перетин множин. Його можна скласти з двох попередніх запитів через UNION ALL (тому що підмножини не перетинаються). -(SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name `Посада` FROM `persons` p LEFT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE ps.id IS NULL) UNION ALL (SELECT p.id, p.name `Ім'я співробітника`, ps.id `pos.id`, ps.name ` Посада ` FROM `persons` p RIGHT OUTER JOIN `positions` ps ON ps.id = p.post_id WHERE p.id IS NULL)

25.Реляційна алгебра. Замкнутість в реляційній алгебрі.

Замкнутість в реляційній алгебрі. -Результатом кожної операції над відношеннями (чи реляційної операції) також є відношенням. Ця реляційна властивість називається властивістю замкнутості. Оскільки результат будь-якої операції має той же тип, що і вихідні об'єкти (відношення), то результат однієї операції може використовуватися в якості вихідних даних для іншої. -Якщо розглядати замкнутість більш строго, кожна реляційна операція повинна бути визначена таким чином, щоб видавати результат з належним заголовком (тобто з відповідним набором необхідних імен атрибутів). Причина такої вимоги до результуючих відношень полягає у необхідності мати можливість звертатися до імен атрибутів в подальших операціях.

59.Програмування тригерів. Оператор CREATE TRIGGER.

Застосування тригерів --Обмеження при створенні тригерів: • Оператор CREATE TRIGGER може застосовуватися тільки в одній таблиці. • Тригер можна створювати тільки в поточній базі даних, але в ньому можна посилатися на зовнішні об'єкти. • В одному операторі створення тригера можна вказувати декілька дій, на які він буде реагувати. • У тексті тригера НЕ можна використовувати такі інструкції: ALTER DATABASE, ALTER PROCEDURE, ALTER TABLE, CREATE DEFAULT, CREATE PROCEDURE, ALTER TRIGGER, ALTER VIEW, CREATE DATABASE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW, DISK INIT, DISK RESIZE, DROP DATABASE, DROP DEFAULT, DROP PROCEDURE, DROP RULE, DROP TRIGGER, DROP VIEW, RESOTRE DATABASE, RESTORE LOG, RECONFIGURE, UPDATE STATISTICS. • Будь-яка правильна операція SET працює тільки в період існування тригера. • Не можна виконати тригер, аналізуючи в стовпцях таблиць INSERTED і DELETED стан великого двійкового об'єкта BLOB (тип даних BLOB - двійковий об'єкт великого розміру, який може містити змінну кількість даних. ), що має тип даних text або image, незалежно від того, записується ця процедура в журнал чи ні. • Не слід застосовувати інструкції SELECT, які повертають результуючі набори з тригера, для програми-клієнта, що вимагає спеціального управління результуючими наборами, незалежно від того, робиться це в збереженій процедурі чи ні. • Не можна створювати INSTEAD OF UPDATE - і DELETE - тригери на таблиці, що мають зовнішні ключі до встановлених опцій каскадної зміни або видалення, відповідно. --Програмування тригерів Для відстеження змін в таблицях сервер автоматично створює при виклику тригера дві спеціальні таблиці - INSERTED і DELETED. Ці таблиці, використовуються для перевірки результатів змін даних і для установки умов спрацьовування тригерів DML. Не можна в цих таблицях змінювати дані безпосередньо або виконувати над ними операції мови опису даних DDL, наприклад інструкцію CREATE INDEX. Таблиці INSERTED і DELETED не існує фізично, а являють собою логічні структури, створювані сервером індивідуально для кожного тригера. Ці таблиці доступні в режимі read only. У таблиці deleted знаходяться копії рядків, з якими працювали інструкції DELETE або UPDATE. При виконанні інструкції DELETE або UPDATE відбувається видалення рядків з таблиці тригера і їх перенесення в таблицю deleted. У таблиці deleted зазвичай немає загальних рядків до таблиці тригера. У таблиці inserted знаходяться копії рядків, з якими працювали інструкції INSERT або UPDATE. При виконанні транзакції вставки або поновлення відбувається одночасне додавання рядків в таблицю тригера і в таблицю inserted. Рядки таблиці inserted є копіями нових рядків таблиці тригера. Наприклад, при оформленні нового замовлення, тобто при додаванні нового запису в таблицю orders, можна створити тригер, який автоматично буде віднімати число замовлених товарних позицій у таблиці products. --Важливо. Підтримка тригерів введена в СКБД MySQL, починаючи з версії 5.0.2. У даній лекції розглядається створення, видалення і використання тригерів. \\-Оператор CREATE TRIGGER -\\ Оператор create trigger дозволяє створити новий тригер і має наступний синтаксис: CREATE TRIGGER trigger_name trigger_time trigger_event ON TBL_NAME FOR EACH ROW trigger_stmt Оператор CREATE TRIGGER створює trigger_name, прив'язаний до таблиці tbl_name. Конструкція trigger_time вказує момент виконання тригера і може приймати два значення: • BEFORE - дії тригера проводяться до виконання операції зміни таблиці; • AFTER - дії тригера проводяться після виконання операції зміни таблиці. Конструкція trigger_event показує, на яку з подій повинен реагувати тригер і може приймати три значення: • INSERT - тригер прив'язаний до події вставки нового запису в таблицю; • UPDATE - тригер прив'язаний до події оновлення запису таблиці; • DELETE - тригер прив'язаний до події видалення записів таблиці. --Важливо. Для таблиці TBL_NAME може бути створений лише один тригер для кожного з подій trigger_event і моменту trigger_time. Тобто для кожної з таблиць може бути створено лише шість тригерів --Конструкція trigger_stmt представляє тіло тригера, тобто оператор, який необхідно виконати при виникненні події trigger_event в таблиці tbl_name. Якщо потрібно виконати декілька операторів, то слід використати складений оператор BEGIN ... END, в якому розміщуються всі необхідні запити. Всередині складеного оператора BEGIN ... END допускаються всі специфічні для збережених процедур оператори та конструкції: • інші складені оператори BEGIN ... END; • оператори керування потоком (IF, CASE, WHILE, LOOP, REPEAT, LEAVE, ITERATE); • оголошення локальних змінних за допомогою оператора DECLARE і призначення їм значень за допомогою оператора SET; • іменовані умови і обробники помилок. --Важливо: • Для створення тригера за допомогою оператора CREATE TRIGGER потрібна наявність привілеї SUPER. • В СКБД MySQL тригери не можна прив'язати до каскадного оновлення або видалення записів з таблиці по зв'язку первинний ключ / зовнішній ключ. Тригери дуже складно використовувати, не маючи доступу до нових записів, які вставляються в таблицю, або старих записів, які оновлюються або видаляються. Для доступу до нових і старих записів використовуються префікси NEW і OLD відповідно. Тобто якщо в таблиці оновлюється поле total, то отримати доступ до старого значенням можна по імені OLD. total, а до нового - NEW.total. Наприклад, створимо найпростіший тригер, який при оформленні нового замовлення (додавання нового запису в таблицю ORDERS) буде присвоювати значення 1 для змінної користувача @tot: CREATE TRIGGER sub_count AFTER INSERT ON ORDERS FOR EACH ROW BEGIN SET @tot = 1; END // SELECT @tot// INSERT INTO ORDERS VALUES (NULL,1, NOW(),1,10)// SELECT @tot// Відредагуємо тригер sub_count таким чином, щоб до змінної @tot додавалося щоразу число замовлених товарних позицій number, наприклад: CREATE TRIGGER sub_count AFTER INSERT ON ORDERS FOR EACH ROW BEGIN SET @tot = @tot + NEW.number; END// SELECT @tot// INSERT INTO ORDERS VALUES (NULL,1,NOW(),5,10)// SELECT @tot// --Важливо: • Для коректної роботи тригера необхідно, щоб змінна користувача @tot мала значення, відмінне від NULL, так як операції складення з NULL призводять до NULL. • При створенні тригерів для таблиці в каталозі даних створюється файл, назва якого збігається з ім'ям таблиці. Файл є текстовим і має розширення trg. Попередні два приклади демонстрували роботу тригерів після додавання запису в таблицю (AFTER) без втручання в запит. Розглянемо тригер, який буде викликатися до (BEFORE) вставки нових записів в таблицю ORDERS. Основне завдання тригера полягає в обмеженні числа замовлених товарів до 1, наприклад: CREATE TRIGGER restrict_count BEFORE INSERT ON ORDERS FOR EACH ROW BEGIN SET NEW.number = 1; END // INSERT INTO ORDERS VALUES (NULL,1,NOW(),2,10)// Часто при оновленні одних полів таблиці оператори баз даних забувають оновити пов'язані поля таблиці або здійснюється спроба додавання некоректних значень. Нехай при додаванні нового клієнта необхідно перетворити імена та по батькові клієнтів в ініціали. Тригер для обробки цієї ситуації може виглядати так, як у наступному прикладі: CREATE TRIGGER restrict_user BEFORE INSERT ON USERS FOR EACH ROW BEGIN SET NEW.name = LEFT(NEW.name,1); SET NEW.patronymic = LEFT(NEW.patronymic,1); END// INSERT INTO users VALUES (NULL, 'Ремизов', 'Алексеевич', 'Сергей', '83-89-00'f NULL, NULL, 'active')// SELECT surname, patronymic, name FROM USERS WHERE id_user = LAST_INSERT_ID()//

48.Збережені процедури в SQL.

Збережені процедури в SQL -Збережена процедура MySQL становить собою підпрограму, що зберігається в базі даних. Вона містить ім'я, список параметрів і оператори SQL. Всі популярні системи управління базами даних підтримують збережені процедури. Вони були введені в MySQL 5. Переваги збережених процедур: • Збережені процедури працюють швидко. • MySQL збережені процедури є універсальними. • Безпека - збережені процедури використовуються для всіх стандартних банківських операцій. • Простота доступу. Збережені процедури дозволяють інкапсулювати складний код і оформити його у вигляді простого виклику з осмисленим ім'ям. При створенні нового каталогу набагато простіше оперувати процедурою create_new_catalog(), ніж кількома операторами незрозумілого призначення. При реєстрації угоди, що вимагає пошуку в таблицях catalogs products, users і редагуванні таблиць orders і users набагато простіше оформити послідовність SQL-операторів для оформлення угоди у збережену процедуру ordering() --Збережені процедури і привілеї: СУБД MySQL вимагає від користувачів наявності наступних привілеїв при роботі з збереженими процедурами: • для створення збережених процедур необхідна наявність привілеї create routine; • для редагування та видалення збереженої процедури необхідно наявність привілеї alter routine • для виклику процедури, що зберігається необхідна наявність привілеї execute

23.Проектування схем баз даних. Зв'язки та міграції ключів

Зв'язки та міграції ключів -Процес встановлення зв'язків пов'язаний з переносом простого або складеного первинного ключа одного класу сутностей в інший клас. Сам процес такого перенесення ще називають міграцією ключів. При цьому клас сутностей, первинні ключі якого переносяться, називається батьківським класом, а клас сутностей, в чиї зовнішні ключі і відбувається міграція, називається дочірнім класом сутностей. -У дочірньому класі сутностей атрибути ключа отримують статус атрибутів зовнішнього ключа і при цьому можуть брати участь або навпаки, не брати участь у формуванні його власного первинного ключа. Таким чином, при міграції первинного ключа з батьківського класу сутностей в дочірній в дочірньому класі виникає зовнішній ключ, що посилається на первинний ключ батьківського класу. -Для зручності формулярного уявлення міграції ключів, введемо такі маркери ключів: • PK - так ми будемо позначати будь-який атрибут первинного ключа (primary key); • FK - цим маркером ми будемо позначати атрибути зовнішнього ключа (foreign key); • 𝑲 𝑷K - таким маркером будемо позначати атрибут первинного / зовнішнього ключа, тобто будьякий такий атрибут, який входить до складу єдиного первинного ключа деякого класу сутностей та, одночасно, до складу деякого зовнішнього ключа цього ж класу сутностей. -Таким чином, атрибути класу сутностей з маркерами PK і FK утворюють первинний ключ цього класу. А атрибути з маркерами FK і 𝑲 𝑷K входять до складу якихось деяких зовнішніх ключів цього класу сутностей. Всього розрізняють дві схеми міграції ключів. -1. Схема міграції ?PK (PK |?𝑲 𝑷K) У цьому записі символ «|?» означає поняття «мігрує», тобто наведена формула читається наступним чином: будь-який (кожен) атрибут первинного ключа PK батьківського класу сутностей переноситься (мігрує) до складу первинного ключа 𝑲 𝑷K дочірнього класу сутностей, який, природно, є одночасно і зовнішнім ключем для цього класу. -Серед зв'язків ідентифікуючого типу, в свою чергу, виділяють ще два можливих самостійних типи зв'язків. Отже, ідентифікуючі зв'язки бувають наступних двох типів: 1) повністю ідентифікуючі. Ідентифікуючий зв'язок називається повністю ідентифікованим в тому і тільки в тому випадку, коли атрибути мігруючого первинного ключа батьківського класу сутностей повністю формують первинний (і одночасно зовнішній) ключ дочірнього класу сутностей. -2) не повністю ідентифікуючі Ідентифікуючий зв'язок називається не повністю ідентифікуючим в тому і тільки в тому випадку, коли атрибути мігруючого первинного ключа батьківського класу сутностей лише частково формує первинний (і одночасно зовнішній) ключ дочірнього класу сутностей. -Таким чином, крім ключа з маркером 𝑲 𝑷K буде також присутній ключ з маркером PK. При цьому зовнішній ключ 𝑲 𝑷K дочірнього класу сутностей буде повністю визначатися первинним ключем PK батьківського класу сутностей, а просто первинний ключ PK цього дочірнього відношення не буде визначатися первинним ключем PK батьківського класу сутностей, він буде сам по собі. -2. Схема міграції ?PK (PK |? FK) Така схема міграції читається наступним чином: існують такі атрибути первинного ключа батьківського класу сутностей, які при міграції переносяться до складу обов'язково неключових атрибутів дочірнього класу сутностей. Такий тип зв'язку називається не ідентифікуючим, адже, дійсно, батьківський ключ не бере участь у формуванні дочірніх сутностей, він просто не ідентифікує їх. -Серед не ідентифікуючих зв'язків також виділяють два можливих типи зв'язків. Таким чином, не ідентифікуючі зв'язки бувають двох наступних видів: 1) обов'язково не ідентифікуючий. • Не ідентифікуючий зв'язок називається обов'язково не ідентифікуючим в тому і тільки в тому випадку, коли Null-значення для всіх атрибутів мігруючого ключа дочірнього класу сутностей заборонені; -2) не обов'язково не ідентифікуючий. • Не ідентифікуючий зв'язок називається не обов'язково не ідентифікуючим в тому і тільки в тому випадку, коли Null-значення для деяких атрибутів мігруючого ключа дочірнього класу сутностей дозволені. -Узагальнимо все вище сказане у вигляді такої таблиці, щоб полегшити завдання систематизації та розуміння наведеного матеріалу. Також в цю таблицю ми включимо інформацію про те, які типи зв'язків ( «не більше одного до одного», «багато до одного», «багато до не більше одного») відповідають видам зв'язків (повністю ідентифікуючі, в повному обсязі які ідентифікують, обов'язково НЕ ідентифікуючі, не обов'язково не ідентифікуючі). -Отже, ми бачимо, що у всіх випадках, крім останнього, посилання не порожня (not null)? 1. Тенденція: На батьківському кінці зв'язку у всіх випадках, крім останнього, встановлюється кратність «один». -Це відбувається тому, що значенням зовнішнього ключа у випадках цих зв'язків (а саме, повністю ідентифікуючий, в повному обсязі ідентифікує і обов'язково НЕ ідентифікуючий вид зв'язків) обов'язково має відповідати (і до того ж єдине) значення первинного ключа батьківського класу сутностей. В останньому випадку через те, що значення зовнішнього ключа допускає рівність Null-значення (прапорець допустимості FK: null), на батьківському кінці зв'язку встановлюється кратність «не більше одного». -На дочірньому кінці зв'язку у всіх випадках, за винятком першого, встановлюється кратність «багато». Це відбувається тому, що за рахунок неповної ідентифікації, як у другому випадку, (або взагалі її відсутність, у другому і третьому випадках), значення первинного ключа батьківського класу сутностей може багаторазово зустрічатися серед значень зовнішнього ключа дочірнього класу. -А в першому випадку зв'язок - повністю ідентифікуючий, тому атрибути первинного ключа батьківського класу сутностей можуть зустрічатися серед атрибутів ключів дочірнього класу сутностей тільки один раз.

11.Модель «Сутність-зв'язок». Зв'язки.

Зв'язки. --Прикладний зв'язок, або просто зв'язок - це поіменована асоціація двох або більше сутностей. Зв'язок двох або більше сутностей називається бінарним. З кожного боку бінарний зв'язок має такі характеристики: - Ім'я; - Множинність, або потужність; - Обов'язковість - зв'язок може бути обов'язковим або факультативним. --Розрізняють дві множинності зв'язку - «один» і «багато». Якщо зв'язок між сутностями А і В з боку сутності А має множинність «один», то це означає, що кожний екземпляр B асоціюється даним зв' язком не більше ніж з одним екземпляром А. І навпаки, якщо екземпляр В може асоціюватися певним зв'язком із довільною кількістю екземплярів A, то зв'язок з боку сутності А має множинність «багато». --На діаграмах бінарні зв'язки зображуються лініями, що сполучають два прямокутники сутностей або рекурсивно один і той же прямокутник з самим собою.

36.Групування записів. Команда GROUP BY

Команда GROUP BY дозволяє групувати результати при вибірці з бази даних. Інколи потрібно отримати агрегатні значення не для всього результуючого набору даних, а оремо для кожної із груп, які входять у цей результуючий набір. Кожна група рядків характеризується однаковим значенням деякого стовпчика. -Правила виконання SQL-запиту на вибірку із врахуванням секції GROUP BY: 1) Сформувати декартовий добуток таблиць, вказаних в секції FROM. Якщо в секції FROM вказана одна таблиця, то декартовим добутком буде вона сама. -2) Якщо є секція WHERE, то умову вказану в цій секції слід застосувати до кожного рядка таблиці, утвореної в результаті декартового добутку, і залишити лише ті рядки, для яких результат умови має значення TRUE. Рядки, для яких умова має значення NULL або FALSE відкидаються. 3) Якщо є секція GROUP BY, то потрібно розділити рядки, які залишились в результуючій таблиці на групи так, щоб рядки в кожній групі мали однакові значення у всіх стовпчиках групування одночасно. -Стовпчик групування - це той стовпчик, який визначає групу рядків. В межах групи всі рядки у стовпчику групування мають одне і те саме значення. -4) Для кожного рядка, що залишився, або для кожної групи рядків потрібно обчислити значення кожного елемента зі списку полів секції SELECT і утворити один рядок таблиці результатів запиту, при цьому при будь-якому звертанні до стовпчика береться значення стовпчика для біжучого рядка або групи рядків. Якщо є секція GROUP BY, то в якості аргументу агрегатної функції використовується значення стовпчика із всіх рядків, які входять у групу. Якщо ж секції GROUP BY немає, то використовується значення стовпчика із усіх рядків таблиці результату запиту. -5) Якщо в секції SELECT є слово DISTINCT, то потрібно вилучити із таблиці результатів запиту всі рядки дублікати. 6) Якщо є секція ORDER BY, то потрібно відсортувати результати запиту. SQL дозволяє групувати результати запиту на основі кількох стовпчиків. Наприклад, потрібно згрупувати замовлення по працівниках та клієнтах. -Синтаксис GROUP BY: SELECT * FROM ім'я_таблиці WHERE умова GROUP BY поле_для_групування

37.Команда HAVING - умова відбору груп.

Команда HAVING - умова відбору груп Якщо в результуючому наборі даних потрібно виводити підсумкові значення не для всіх груп, а лише для тих, які задовольняють певній умові, то після секції GROUP BY перед секцією ORDER BY вказується секція HAVING. Синтаксис: GROUP BY поле HAVING умова Загальний вигляд оператора SELECT описується так: Умова відбору груп секції HAVING має вигляд: Подібно до того, як секція WHERE використовується для відбору окремих рядків, так і секція HAVING використовується для відбору груп. Формат секції HAVING такий як і в секції WHERE, за одним дуже важливим винятком: в секції WHERE не можна вказувати агрегатних функцій. 11 Порядок виконання секцій в операторі SELECT такий: 1) FROM. 2) WHERE. 3) GROUP BY. 4) HAVING. 5) SELECT. 6) ORDER BY. Наприклад дано наступну таблицю workers. id name age salary 1 Дмитро 23 100 2 Петро 23 200 3 Василь 23 300 4 Микола 24 1000 5 Іван 24 2000 6 Кирило 25 1000 Приклад 1. В даному прикладі демонструється робота GROUP BY без умови HAVING: SELECT age, SUM(salary) as sum FROM workers GROUP BY age SQL-запит вибере наступні рядки: age sum 23 600 24 3000 25 1000 А тепер за допомогою умови HAVING залишимо тільки ті рядки, в яких сумарна зарплата більше або дорівнює 1000: SELECT age, SUM(salary) as sum FROM workers GROUP BY age HAVING sum>=1000 age sum 24 3000 25 1000 12 Приклад 2. Підрахувати за допомогою функції COUNT кількість записів в групі (не використовуючи HAVING): SELECT age, COUNT(*) as count FROM workers GROUP BY age age count 23 3 24 2 25 1 А тепер за допомогою умови HAVING залишимо тільки ті групи, в яких кількість рядків менше або дорівнює двом: SELECT age, COUNT(*) as count FROM workers GROUP BY age HAVING count<=2 age count 24 2 25 1 Аналогічного ефекту можна досягти, якщо скористатися командою IN: SELECT age, COUNT(*) as count FROM workers GROUP BY age HAVING count IN(1,2) Можна також використовувати команду BETWEEN: SELECT age, COUNT(*) as count FROM workers GROUP BY age HAVING count BETWEEN 1 AND 2 Як бачимо, в HAVING допустимі всі команди, які використовуються в умові WHERE. Обмеження на умову відбору груп. Секція HAVING використовується для того, щоб відбирати групи рядків, тому умова відбору секції HAVING застосовується не до окремих рядків, а до групи в цілому. Це означає, що в умову відбору секції HAVING може входити: 1) константа; 2) агрегатна функція, яка повертає одне значення для всіх рядків, що входять у групу; 13 3) стовпчик групування, який за означенням має одне й те саме значення в усіх рядках групи; 4) вираз, який складається із всіх вищевказаних елементів. На практиці умова відбору секції HAVING завжди містить як мінімум одну агрегатну функцію. Якщо це не так, то таку умову можна і навіть буде краще перемістити у секцію WHERE. Значення null і умова відбору груп. Як і у секції WHERE умова відбору в секції HAVING може дати один з наступних результатів: якщо умова набуває значення TRUE, то така група рядків залишається і для неї генерується один єдиний рядок таблиці результатів запиту; якщо ж умова отримує значення FALSE або NULL, то така група рядків ігнорується і для неї жодний рядок таблиці результатів не створюється. Правила опрацювання значень NULL в умовах відбору секції HAVING такі самі, що і для секції WHERE. Секція HAVING без секції GROUP BY На практиці секція HAVING завжди використовується разом із секцією GROUP BY, але синтаксис оператора SELECT цього не вимагає. Якщо секція HAVING використовується без секції GROUP BY, то СУБД вважає всі результати запиту однією групою, тобто агрегатні функції вказані в секції HAVING будуть застосовуватися до однієї єдиної групи, яка складається з усіх рядків.

34.Команди вибірки. IS NULL, DISTINCT. Булеві операції: AND, OR, NOT.

Команда IS NULL Команда IS NULL перевіряє поле на NULL. Синтаксис команди IS NULL: WHERE поле IS NULL --Команда IS NOT NULL Команда IS NOT NULL перевіряє поле на НЕ NULL. Синтаксис команди IS NULL: WHERE поле IS NOT NULL Приклад 1. Обрати всі записи у яких зарплата НЕ рівна NULL: SELECT * FROM workers WHERE salary IS NOT NULL -Команда DICTINCT Команда DISTINCT дозволяє вибирати тільки унікальні значення з бази даних (тобто відсіювати дублювання: наприклад, в таблиці є дві Маші - тоді запит виведе тільки першу). Замість DISTINCT можна використовувати DISTINCTROW - в mySQL це одне і те ж. Синтаксис команди DICTINCT: При вибірці: SELECT DISTINCT поле FROM ім'я_таблиці WHERE умова 13 При підрахунку: SELECT COUNT(DISTINCT поле) FROM ім'я_таблиці WHERE умова При сумуванні: SELECT SUM(DISTINCT поле) FROM ім'я_таблиці WHERE умова Приклад 1. Обрати всі унікальні значення зарплат: SELECT DISTINCT salary FROM workers -Команда NOT Команда NOT задає заперечення для інших команд: замість IN можна написати NOT IN, замість BETWEEN - NOT BETWEEN і так далі. Як це працює на практиці: Наприклад, візьмемо, команду IN і з її допомогою виберемо записи, у яких id має значення 1, 3, 7, 14, 28. Це буде виглядати так: WHERE id IN (1, 3, 7, 14, 28) А тепер скористаємося запереченням NOT і виберемо всі записи, у яких id НЕ дорівнює 1, 3, 7, 14, 28 таким чином: WHERE id NOT IN (1, 3, 7, 14, 28) Синтаксис команди NOT: NOT IN (значення1, значення2...) NOT BETWEEN значення1 AND значення2 NOT LIKE шаблон Приклад 1. Обрати всі записи з id НЕ рівним 1, 3 та 5: SELECT * FROM workers WHERE id NOT IN (1, 3, 5) -Булеві операції: AND, OR Команда AND є логічним 'І', яке дозволяє комбінувати умови в команді WHERE. Синтаксис команди AND: WHERE умова1 AND умова2 Приклад 1. Обрати всі записи у яких id більше 2-х і менше 4-х: SELECT * FROM workers WHERE id>=2 AND id<=4 -Команда OR є логічним 'АБО', яке дозволяє комбінувати умови в команді WHERE. Синтаксис команди OR: WHERE умова1 OR умова2 Приклад 1. Обрати записи у яких зарплата рівна 400 чи вік 23 (хоча б одне з них): SELECT * FROM workers WHERE id=3 OR salary=400

41.Команда LIMIT. Функції SQL ALL та ANY.

Команда LIMIT. • Команда LIMIT задає обмеження на кількість записів, які обирають з бази даних. • Дана команда може використовуватися спільно з командою SELECT, командою DELETE, і командою UPDATE. • Також можна порівняти з команду ORDER, за допомогою якої можна впорядкувати вибрані записи. Синтаксис: 1) SELECT * FROM ім'я_таблиці WHERE умова LIMIT кількість_записів 2) SELECT * FROM ім'я_таблиці WHERE умова LIMIT з_якого_запису, кількість_записів 3) SELECT * FROM ім'я_таблиці WHERE умова ORDER BY по_чому_сортуровати LIMIT число, число 4) DELETE FROM ім'я_таблиці WHERE умова LIMIT кількість_записів 5) UPDATE ім'я_таблиці SET ... WHERE умова LIMIT кількість_записів -Приклад 1. Обрати перші 3 записи: SELECT * FROM workers WHERE id>0 LIMIT 3 -Функції SQL ALL та ANY Функції SQL ALL і ANY називаються кванторними функціями. Аргументом такої функції є множина значень деякого стовпця, що враховується у підзапиті виду: ... ALL (SELECT ім'я_стовпця_що_враховується FROM ім'я_таблиці [WHERE умова]) -По аналогії пояснимо дію функції ANY: ... ANY (SELECT ім'я_стовпця_що_враховується FROM ім'я_таблиці [WHERE умова]) У функції ANY є синонім - SOME (діє повністю ідентично). Функції ALL і ANY застосовуються з операторами порівняння: (>, <,> =, <=, =). -Функція ALL застосовується зазвичай для отримання вибірки, що характеризується значеннями стовпця, що враховується, які більше (або менше) всіх значень того ж стовпця іншої вибірки, яка витягується підзапитом. -Наприклад, дано наступні таблиці: Таблиця Object містить дані про об'єкти, причому Space_Total - це загальна площа об'єкта, а District - район, в якому він знаходиться. Таблиця Deal містить дані про операції, причому значення стовпця Type може бути або Sale (продаж), або Rent (оренда) .Таблиця Client містить дані відповідно про клієнтів. -Таблиця Object INSERT INTO Object (Obj_ID, Type, District, Rooms, Space_Total, Space_Living, Space_Kitchen, LogBalc, Owner_ID, Project, Material) VALUES • (1, 'flat', 'Центр', 2, 64, 35, 12, 1, 1, 'спецпроект', 'кирпич'), • (2, 'flat', 'Центр', 2, 48, 32, 6, 0, 2, 'хрущевка', 'блоки'), • (3, 'house', 'Волжский', 4, 146, 80, 15, 1, 3, 'спецпроект', 'бетон'), • (4, 'flat', 'Центр', 2, 58, 32, 11, 0, 4, 'сталинка', 'кирпич'), • (5, 'house', 'Волжский', 5, 210, 130, 20, 1, 5, 'спецпроект', 'кирпич'), • (6, 'flat', 'Пашино', 2, 52, 35, 8, 1, 6, 'брежневка', 'блоки'), • (7, 'flat', 'Центр', 3, 80, 52, 12, 0, 7, 'сталинка', 'кирпич'), • (8, 'house', 'Сосновка', 3, 120, 78, 13, 0, 8, 'спецпроект', 'кирпич'), • (9, 'flat', 'Сосновка', 1, 33, 18, 7, 0, 9, 'брежневка', 'кирпич'), • (10, 'flat', 'Пашино', 2, 53, 33, 9, 1, 10, 'брежневка', 'блоки'), • (11, 'flat', 'Сосновка', 3, 60, 44, 6, 0, 11, 'хрущевка', 'кирпич'), • (12, 'flat', 'Сосновка', 2, 44, 30, 5, 0, 12, 'хрущевка', 'блоки'), • (13, 'flat', 'Сосновка', 1, 33, 18, 6, 1, 11, 'хрущевка', 'кирпич'); -Таблиця Deal INSERT INTO Deal (Deal_ID, Type, Object_ID, Client_ID, Cost, Manager_ID, Date) VALUES • (1, 'sale', 1, 1, 45000, 1, '2018-06-03'), • (2, 'sale', 2, 2, 58000, 1, '2018-07-12'), • (3, 'rent', 3, 3, 12000, 3, '2018-07-18'), • (4, 'sale', 4, 4, 120000, 3, '2018-08-15'), • (5, 'sale', 5, 5, 45000, 2, '2018-08-17'), • (6, 'sale', 6, 6, 48000, 4, '2018-09-09'), • (7, 'sale', 7, 7, 90000, 1, '2018-09-16'), • (8, 'rent', 8, 8, 15000, 4, '2018-09-30'), • (9, 'sale', 9, 9, 57200, 3, '2018-11-05'), • (10, 'sale', 10, 10, 58000, 2, '2018-11-22'), • (11, 'sale', 11, 11, 46700, 1, '2019-02-20'), • (12, 'sale', 12, 12, 46500, 1, '2019-03-16'); --Приклад 1. Потрібно отримати загальну площу і райони об'єктів, у яких загальна площа більше загальної площі всіх (будь-якого з) об'єктів, розташованих в районі "Сосновка". -Пишемо запит з використанням функції ALL: SELECT Space_Total, District FROM OBJECT WHERE Space_Total > ALL (SELECT Space_Total FROM OBJECT WHERE District='Сосновка -Функція ANY (або її повний аналог SOME) застосовується для отримання вибірки, що характеризується значеннями стовпця, що враховується, які дорівнюють хоча б якомусь із значень того ж стовпчика іншої вибірки, яка витягується підзапитом. -Приклад 2. Потрібно знайти клієнтів, які уклали угоди на оренду нерухомості. У таблиці Deal (угода) значення стовпця Type може бути або Sale (продаж), або Rent (оренда). -Пишемо запит з використанням функції ANY, в якому основний запит звернений до таблиці CLIENT, а підзапит - до таблиці DEAL: SELECT Client_ID FROM CLIENT WHERE Client_ID = ANY (SELECT Client_ID FROM DEAL WHERE Type='rent' -Приклад 3. Потрібно знайти об'єкти, з якими не були укладені угоди. Пишемо запит з використанням функції ANY, в якому основний запит звернений до таблиці OBJECT, а підзапит - до таблиці DEAL: SELECT Obj_ID FROM OBJECT WHERE NOT Obj_ID = ANY (SELECT Object_ID FROM DEAL

39.Об'єднання таблиць між собою. UNION.

Команда UNION • Команда UNION об'єднує дані з декількох таблиць в одну при вибірці. • При об'єднанні кількість стовпців у всіх таблицях повинна співпадати, інакше буде помилка. • Імена стовпців будуть такі ж, як в основній таблиці, в яку додаються дані з інших таблиць. -Синтаксис: З видаленням дублювання: SELECT * FROM ім'я_таблиці1 WHERE умова UNION SELECT * FROM ім'я_таблиці2 WHERE умова Без видалення дублювання: SELECT * FROM ім'я_таблиці1 WHERE умова UNION ALL SELECT * FROM ім'я_таблиці2 WHERE умова -З об'єднанням трьох і більше таблиць: SELECT * FROM ім'я_таблиці1 WHERE умова UNION SELECT * FROM ім'я_таблиці2 WHERE умова UNION SELECT * FROM ім'я_таблиці3 WHERE умова UNION SELECT * FROM ім'я_таблиці4 WHERE умова -Приклад 1. Об'єднати записи з двох таблиць: SELECT id, name FROM countries UNION ALL SELECT id, name FROM cities -Приклад 7. Даний запит видає помилку, так як в таблицях не співпадає кількість колонок: SELECT id, name FROM countries UNION SELECT id, name, country_id FROM cities Наступний запит теж буде видавати помилку - кількість колонок в обох таблицях не співпадає: SELECT * FROM countries UNION SELECT * FROM cities -Якщо нам потрібно забрати з таблиці стільки полів, що в іншій таблиці стільки і немає, можна створювати додаткові поля вручну. Наприклад. Необхідно забрати 3 поля з другої таблиці, а в першій таблиці полів тільки 2. Вирішимо цю проблему створивши поле з ім'ям country_id і вмістом 0 для першої таблиці (ось так: 0 as country_id): SELECT id, name, 0 as country_id FROM countries UNION SELECT id, name, country_id FROM cities

33.Команди вибірки. WHERE, IN, BETWEEN, LIKE.

Команда WHERE. Команда WHERE задає умову, за якою обиратимуться рядки з бази даних. Дана команда може використовуватися для вибірки рядків за допомогою SELECT, видалення рядків з допомогою DELETE, редагування рядків за допомогою UPDATE. Синтаксис команди WHERE: WHERE умова ------- Команда IN Команда IN вибирає записи з бази даних по певним значенням поля. Наприклад, можна вибрати записи, у яких id має значення 1, 3, 7, 14, 28. Це буде виглядати так: WHERE id IN (1, 3, 7, 14, 28) Або ж всі записи, у яких поле name має значення 'Дмитро', 'Василь' або 'Миколя'. Це буде виглядати так: WHERE id IN (' Дмитро ', ' Василь ', ' Миколя ') 9 В принципі, для таких речей можна користуватися і командою OR, тоді перший приклад буде виглядати так: WHERE id=1 OR id=3 OR id=7 OR id=14 OR id=28. Варіант з IN виглядає простіше та читабельно. Синтаксис команди IN: SELECT * FROM ім'я_таблиці WHERE поле IN (значення1, значення2...) --Команда BETWEEN Команда BETWEEN задає діапазон значень, за яким вибираються записи з бази даних. Наприклад, можна вибрати записи для поля id від 3 до 10-ти. Синтаксис команди BETWEEN: SELECT * FROM ім'я_таблиці WHERE поле BETWEEN від AND до -Команда LIKE Команда LIKE задає пошук за певним шаблоном. Синтаксис команди LIKE: SELECT * FROM ім'я_таблиці WHERE поле LIKE шаблон_пошуку Приклад 1. Знайти всі записи у який ім'я закінчується на 'я'. SELECT * FROM workers WHERE name LIKE '%я' . Знайти всі записи вік яких починається з цифри 2 за яким йде ще один символ: SELECT * FROM workers WHERE age LIKE '2_'

44.Команди модифікації мови DML. INSERT.

Команди модифікації мови DML -Дані в реляційних базах даних керуються за допомогою DML команд (Data Manipulation Language). На даний момент найбільш популярною мовою DML є SQL. Функції мови DML визначаються першим словом в реченні (яке часто називають запитом), яке майже завжди є дієсловом. У випадку з SQL цими дієсловами є - «select» («вибрати»), «insert» («вставити»), «update» («оновити»), і «delete» («видалити»). Строго кажучи існує п'ять DML команд: • SELECT • INSERT • UPDATE • DELETE • MERGE На практиці професіонали в області баз даних, SELECT зазвичай не розглядають як частину DML. Зазвичай SELECT розглядається окремо. Команда MERGE теж часто не розглядається. MERGE можна розглядати, як ярлик для виклику команд INSERT та DELETE або UPDATE в залежності від поставлених умов --Оператор SQL INSERT Оператор SQL INSERT призначений для вставки значень стовпців в таблицю баз даних. Синтаксис: INSERT INTO ім'я_таблиці [(імена стовпців)] VALUES (значення_що_вставляються) Квадратні дужки [], в які вкладено елемент запиту (імена стовпців), означають, що цей елемент є необов'язковим. Вставка значень в таблицю з зазначенням / без зазначенням стовпців Для прикладу, Будемо працювати з базою даних порталу оголошень. У ній є таблиця ADS, що містить дані про оголошеннях, поданих за тиждень. Забігаючи наперед, можемо продемонструвати вмістиме оператору CREATE: CREATE TABLE ADS ( Id INT(11) NOT NULL DEFAULT '100', Category varchar(25) DEFAULT 'Some Category', Part varchar(25) DEFAULT 'Some Part', Units INT(5) DEFAULT NULL, Money INT(10) DEFAULT NULL, PRIMARY KEY (Id) ) Для того, щоб створити новий рядок в цій таблиці використовуємо наступний запит: INSERT INTO ADS (Id, Category, Part, Units, Money) VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620) Або без вказання імен стовпців: INSERT INTO ADS VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620) Тому на MS SQL Server потрібно виконати такий запит: USE adportal1; INSERT INTO ADS (Category, Part, Units, Money) VALUES ('Недвижимость', 'Гаражи', 22, 4620); MS SQL Server у відповідь на такий запит видасть повідомлення про помилку, так як при створенні таблиці було зазначено, що значення стовпця Id є ідентифікаторами і вставляються при додаванні нових рядків автоматично з приростом 1. Тому на MS SQL Server потрібно виконати такий запит: USE adportal1; INSERT INTO ADS (Category, Part, Units, Money) VALUES ('Недвижимость', 'Гаражи', 22, 4620); В результаті виконання запиту в таблиці з'явиться новий рядок: З прикладу видно, що для вставки числових значень в таблицю значення потрібно вказувати без лапок, а для вставки строкових значень - в одинарних лапках. --Вставка значень за замовчуванням (DEFAULT) і невизначених значень (NULL) Зауважимо, що при створенні таблиці було передбачено, що значення стовпців можуть мати значення за замовчуванням (DEFAULT). Для стовпців Category та Part значення за замовчуванням - відповідно Some Category і Some Part, а для стовпців Units і Money - значення NULL. Якщо в запиті на вставку даних на MySQL деякі стовпці відсутні, то в них будуть вставлені значення за замовчуванням. На MS SQL в якості значень потрібно вказати DEFAULT. --Вставка значень з використанням оператора SET в MySQL Конструкція з оператором SET схожа на конструкцію оператора UPDATE і має наступний синтаксис: INSERT INTO ім'я_таблиці SET ім'я_стовпця_1=значення, ім'я_стовпця_2=значення, ..., ім'я_стовпця_N=значення Приклад 6. Вставимо в таблицю рядок, при цьому стовпці Units і Money приймуть значення за замовчуванням: INSERT INTO ADS SET Id=13, Category='Недвижимость', Part='Гаражи' --Використання механізму автоматичного збільшення при вставці даних Механізм автоматичного збільшення призначений для автоматичного генерування унікальних значень первинного ключа в таблиці. У різних діалектах SQL синтаксис автоматичного збільшення різниться. В MySQL використовується ключове слово AUTO_INCREMENT. CREATE TABLE ADS ( Id INT(11) NOT NULL AUTO_INCREMENT, Category varchar(25) DEFAULT 'Some Category', Part varchar(25) DEFAULT 'Some Part', Units INT(5) DEFAULT NULL, Money INT(10) DEFAULT NULL, PRIMARY KEY (Id) ) У SQL Server використовується ключове слово IDENTITY (N, M), де N - початкове значення стовпця, M - крок збільшення. Так, вказавши IDENTITY (1, 1) ми забезпечимо початкове значення первинного ключа 1 і приріст на 1 значення при кожній вставці нового рядка: CREATE TABLE ADS ( Id int IDENTITY(1, 1) PRIMARY KEY, Category varchar(25) DEFAULT 'Some Category', Part varchar(25) DEFAULT 'Some Part', Units INT(5) DEFAULT NULL, Money INT(10) DEFAULT NULL ) --Вставка де кількох рядків в таблицю Для цього застосовується або однорядковий, або багаторядковий оператор INSERT. Приклад 7. Якщо використовується механізм автоматичного збільшення значень первинного ключа, то на MySQL вставити нові рядки в таблицю можна, застосувавши кілька разів оператор INSERT і вказавши в якості значень первинного ключа 0 або NULL: INSERT INTO ADS VALUES (NULL, 'Электротехника', 'Телевизоры', 127, 8255); INSERT INTO ADS VALUES (NULL, 'Электротехника', 'Холодильники', 137, 8905); -В MySQL і SQL Server існує багаторядковий оператор INSERT. Його відмінність від однорядкового варіанту в тому, що для вставки декількох рядків він використовується один раз, а після ключового слова VALUES вказується не один, а декілька списків значень рядків, що додаються.

46.Команди опису мови DDL. CREATE

Команди опису мови DDL. CREATE, ALTER, DROP Data Definition Language - мова визначення даних. DDL, як і DML, є підмножиною мови SQL. Команди DDL використовуються для створення нових баз даних, таблиць і стовпців. Мова DDL служить для створення і модифікації структури БД, тобто для створення / зміни / видалення таблиць та зв'язків. У цю групу входять наступні оператори: - CREATE - ALTER - DROP Команди CREATE (створення), ALTER (модифікація) і DROP (видалення) мають більшість типів об'єктів баз даних (таблиць, уявлень, процедур, тригерів, табличних областей, користувачів і ін.). 13 Дехто вважає, що застосування DDL є прерогативою адміністраторів бази даних, а команди DML повинні писати розробники, але ці дві мови не так просто розділити. Складно організувати афективний доступ до даних і їх обробку, не розуміючи, які структури доступні і як вони пов'язані. Також складно проектувати відповідні структури, не знаючи, як вони будуть оброблятися. Створення бази даних SQL і таблиць бази даних Перша команда, яку ми будемо застосовувати для створення бази даних - це команда CREATE DATABASE. Її синтаксис наступний: CREATE DATABASE ІМ'Я_БАЗИ ДАНИХ Далі для створення таблиць нашої бази даних будемо багаторазово використовувати команду CREATE TABLE. Її синтаксис наступний: CREATE TABLE ІМ'Я_ТАБЛИЦІ (ім'я_першого_стовпця тип даних, ..., ім'я_останнього_стовпця тип даних, первинний ключ, обмеження (не обов'язково)) До прикладу, наша база даних буде моделювати мережу аптек, то в ній є такі сутності, як "Аптека" (таблиця Pharmacy в нашому прикладі створення бази даних), "Препарат" (таблиця Preparation в нашому прикладі створення бази даних), "Доступність (препаратів в аптеці) "(таблиця Availability в нашому прикладі створення бази даних)," Клієнт "(таблиця Client в нашому прикладі створення бази даних) та ін. Нас цікавлять команди мови SQL для створення БД і таблиць в ній, то будемо вважати, що зв'язки між сутностями вже нам зрозумілі. На малюнку нижче наведено подання моделі нашої БД з атрибутами сутностей (таблиць) і зв'язками між таблицями. 14 Слід пам'ятати, що при створенні БД в якій таблиці зв'язані між собою, важливо забезпечити цілісність даних. Це означає, наприклад, що якщо видалити препарат з таблиці Preparation, то повинні піти всі записи цього препарату в таблиці Availability. Ще приклад обмеження цілісності: потрібно встановити заборону на видалення назви групи препарату з таблиці Group, якщо існує хоча б один препарат цієї групи. Особливий випадок становить зміна даних в одній таблиці, коли виконуються дії з даними в іншій таблиці. Тепер приступимо до створення команд. Перша наша команда SQL створює базу даних PHARMNETWORK: CREATE DATABASE PHARMNETWORK Опис таблиці PHARMACY (Аптека): Ім'я поля Тип даних Опис PH_ID smallint Ідентифікаційний номер аптеки Address varchar(40) Адрес аптеки Пишемо команду, яка створює таблицю PHARMACY (Аптека), значення первинного ключа PH_ID генеруються автоматично від 1 з кроком 1, вноситься перевірка на те, щоб значення атрибуту Address в цій таблиці були унікальними: CREATE TABLE PHARMACY(PH_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), Address varchar(40) NOT NULL, PRIMARY KEY(PH_ID), CONSTRAINT PH_UNIQ UNIQUE(Address)) 15 Слід звернути увагу на те, що автоматичне генерування первинного ключа з приростом забезпечено засобами, що застосовуються в діалекті SQL: PH_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) Засоби автоматичного генерування первинного ключа з приростом (коротко це називається автоінкрементом) в різних діалектах SQL різняться. Так, в MySQL використовується ключове слово AUTO_INCREMENT і відповідна частина запиту на створення таблиці виглядає наступним чином: PH_ID int(4) NOT NULL AUTO_INCREMENT У SQL Server механізм автоінкремента забезпечується так: PH_ID int IDENTITY(1, 1) PRIMARY KEY Запис (1, 1) тут означає, що значення первинного ключа повинні створюватися починаючи з 1 зі збільшенням на 1 Отже, пам'ятайте про те, що в залежності від СУБД і діалекту SQL механізми автоінкремента розрізняються. Опис таблиці GROUP (Група препаратів): Ім'я поля Тип даних Опис GR_ID smallint Ідентифікаційний номер групи препаратів Name varchar(40) Назва групи препаратів Пишемо команду, яка створює таблицю Group (Група препаратів), значення первинного ключа GR_ID генеруються автоматично від 1 з кроком 1, проводиться перевірка унікальності найменування групи (для цього використовується ключове слово CONSTRAINT): CREATE TABLE GROUP(GR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, PRIMARY KEY(GR_ID), CONSTRAINT GR_UNIQ UNIQUE(Name)) Опис таблиці PREPARATION (Препарат): Ім'я поля Тип даних Опис PR_ID smallint Ідентифікаційний номер препарату GR_ID smallint Ідентифікаційний номер групи препарату Name varchar(40) Назва препарату Команда, яка створює таблицю PREPARATION: значення первинного ключа PR_ID генеруються автоматично від 1 з кроком 1; визначається, що 16 значення зовнішнього ключа GR_ID (Група препаратів) не можуть приймати значення NULL; визначена перевірка унікальності значень атрибута Name: CREATE TABLE PREPARATION(PR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, GR_ID int NOT NULL, PRIMARY KEY(PR_ID), CONSTRAINT PR_UNIQ UNIQUE(Name)) Далі нам потрібно врахувати обмеження цілісності. Це дуже зручно зробити за допомогою команди ALTER TABLE. Ця команда вивчається нижче. Тепер самий час створити таблицю AVAILABILITY (Доступність або Наявність препарату в аптеці). Її опис: -Пишемо команду, яка створює таблицю AVAILABILITY. Визначаються дати початку (не може бути NULL) і закінчення (за замовчуванням NULL). CREATE TABLE AVAILABILITY(A_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), PH_ID int NOT NULL, PR_ID int NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL, QUANTITY int NOT NULL, MART varchar(3) DEFAULT NULL, PRIMARY KEY(A_ID), CONSTRAINT AVA_UNIQ UNIQUE(PH_ID, PR_ID)) 17 Створюємо таблицю DEFICIT (Дефіцит препарату в аптеці, тобто, незадоволений запит). Її опис: Пишемо команду, яка створює таблицю DEFICIT: CREATE TABLE DEFICIT(D_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), PH_ID int NOT NULL, PR_ID int NOT NULL, Solution varchar(40) NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL) Залишилося небагато. Ми вже дійшли до команди, яка створює таблицю EMPLOYEE (Співробітник). Її опис: Пишемо команду, яка створює таблицю EMPLOYEE (Співробітник), з первинним ключем, що генерується за тими ж правилами, що і первинні ключі попередніх таблиць, в яких вони існують. Зовнішнім ключем PH_ID Співробітник пов'язаний з PHARMACY (Аптекою): CREATE TABLE EMPLOYEE(E_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), F_Name varchar(40) NOT NULL, L_Name varchar(40) NOT NULL, Post varchar(40) NOT NULL, PH_ID int NOT NULL, PRIMARY KEY(E_ID)) 18 Тепер черга дійшла до створення таблиці CLIENT (Клієнт). Її опис: Пишемо команду, яка створює таблицю CLIENT (Клієнт), для первинного ключа відповідає попередній опис. Особливість цієї таблиці в тому, що її атрибути F_Name та L_Name мають за замовчуванням значення NULL. Це пов'язано з тим, що клієнти можуть бути як зареєстрованими, так і незареєстрованими. У останніх значення імені та прізвища якраз і будуть невизначеними (тобто NULL): CREATE TABLE CLIENT(C_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), FName varchar(40) DEFAULT NULL, LName varchar(40) DEFAULT NULL, DateReg varchar(20), PRIMARY KEY(C_ID)) --\-І наостанок, обіцяний відступ про дотримання обмежень цілісності, коли рішення - більш складне, ніж написання команди. У нашому прикладі необхідно дотримуватися таких умов: при покупці одиниці препарату значення кількості цього препарату в таблиці AVAILABILITY має відповідно зменшитися. Взагалі кажучи, для таких операцій в мові SQL існують спеціальні засоби, які називаються тригерами. Але тригери, на практиці можуть і не спрацювати або спрацювати не так, як передбачено. Тому розробники по можливості шукають програмні засоби вирішення таких завдань. Програмними засоби для вирішення зазначеної вище задачі: зменшення значення кількості препарату. А саме: в умові додавання відповідного препарату в таблицю BUYING (Покупка) пишеться функція на мові програмування, на якій виконано програму, із запитом з ключовим словом UPDATE на заміну значення кількості цього препарату на одиницю менше в тій же аптеці. І таблиця BUYING, і таблиця AVAILABILITY мають зовнішній ключ PH_ID - ідентифікатор певної аптеки.

8. Мережна модель даних.

Мережна модель даних --Мережна модель даних свого часу була дуже популярною, а у деяких системах використовується і нині (термін "мережна" не має відношення до комп'ютерної мережі). Мережна модель даних визначається в тих же термінах, що і ієрархічна. Вона складається з багатьох записів, що можуть бути власниками, або членами групових відносин. Зв'язок між записом-власником і записом-членом також має вид 1:N. Основне розходження цих моделей полягає в тому, що в мережній моделі запис може бути членом більш ніж одного групового відношення. Відповідно до цієї моделі кожне групове відношення іменується і проводиться розходження між його типом і екземпляром. Тип групового відношення задається його ім'ям і визначає властивості загальні для всіх екземплярів даного типу. Екземпляр групового відношення представляється записом-власником і множиною (можливо порожньою) підлеглих записів. При цьому є наступне обмеження: екземпляр запису не може бути членом двох екземплярів групових відносин одного типу. В мережній моделі будь-який елемент може бути пов'язаний з будь-яким іншим елементом. Підлеглий елемент може мати більше одного початкового. В цій моделі реалізовано зв'язок "багато-до-багатьох" (рис. 2.2). Будь-яка мережна структура може бути приведена до більш простого виду шляхом введення надлишковості даних (рис. 2.3). Мережу можливо перетворити в дерево, якщо вказати деякі імена декілька разів. В деяких випадках надлишковість, яка при цьому виникає, мала і є допустимою, в інших вона виявляється занадто високою. Багато методів, які широко використовуються для фізичного представлення деревовидних структур, є непридатними для представлення мережних. Саме цьому більшість програм, які оброблюють деревовидні структури, не можуть працювати з мережними моделями. Завдання виявляється ще більш складним ще й тому, що методи, які застосовуються для одного виду мережних структур, є незадовільними для іншого виду. Мережна модель не є вільною від основного недоліку - чітко визначати на фізичному рівні зв'язки між даними і також чітко дотримуватись 4 цій структурі при організації запитів до БД. Запобігти зростаючий складності ієрархічної і мережної моделі даних вдалося введенням реляційної моделі. Основна перевага мережної моделі: - можливість визначати дуже складні інформаційні структури, які максимально відповідають природі інформаційних процесів. Недоліки: - є необхідність в описі зв'язків між сутностями, що в свою чергу вимагає збільшення пам'яті комп'ютера; - для цієї моделі характерна складність реалізації системи управління базами даних.

54.Оператори керування потоками даних. IF...THEN...ELSE. Оператор CASE.

Оператори керування потоком даних Збережені процедури це не просто зручні контейнери для групи запитів, вони дозволяють реалізувати, досить складну логіку, використовуючи оператори розгалуження та цикли. Важливо. Оператори, що описані нижче, окрім збережених процедур застосовувати не можна. Оператор IF...THEN...ELSE Оператор IF дозволяє реалізувати розгалуження програми за умовою і має наступний синтаксис: IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF Логічний вираз search_condition може приймати два значення: 0 (false) і значення, відмінне від нуля (true). Якщо логічний вираз істинний, то оператор statement_list після ключового слова виконується, інакше виконується список операторів у блоці ELSE (якщо блок else є). Як statement_list може виступати складений оператор BEGIN ... END. 19 Важливо. Слід зазначити, що в СКБД MySQL, окрім оператора if, описаного тут, існує функція if (). Розглянемо найпростіший приклад використання оператора IF. Процедура pricelist (), що подана у прикладі нижче, виводить список товарних позицій в каталозі, первинний ключ яких дорівнює id. Другим параметром функції є число cur, що може приймати значення 0, якщо потрібно вивести ціни в рублях, і 1, якщо ціни повинні бути перераховані в долари з курсом 28,5 гривень. Важливо. Для створення логічних виразів можна використовувати всі оператори порівняння ("=", ">", ">=" "<>", "<", "<="). Крім того, логічні вирази можна комбінувати між собою за допомогою операторів "&&" (І), а також "||" (АБО). Приклад: CREATE PROCEDURE pricelist (id INT, cur INT) LANGUAGE SQL BEGIN IF(cur = 0) THEN SELECT name, price FROM products WHERE id_cataiog = id; END IF; IF(cur = 1) THEN select name, price/28.5 FROM PRODUCTS WHERE id_catalog = id; END IF END // CALL pricelist (1,1)// name price/28.5 Celeron G5900 55.381944 Celeron N4020 68.368056 Рекомендується використовувати ключове слово LANGUAGE SQL вже зараз для забезпечення сумісності з майбутніми версіями MySQL та іншими СУБД. Оператор IF може бути оснащений додатковим блоком ELSE, після якого виконуються оператори, якщо умова виявилася хибною. Функцію pricelist () можна переписати так, як це показано у прикладі. Зауваження. Після ключового слова ELSE ставити крапку з комою не потрібно, оскільки цим самим єдиний оператор IF розбивається на частини - крапку з комою ставлять після ключового слова END IF. 20 Наприклад: CREATE PROCEDURE pricelist (id INT, cur INT) LANGUAGE SQL BEGIN IF(cur) THEN SELECT name, price/28.5 FROM PRODUCTS WHERE id_catalog = id; ELSE SELECT name, price FROM PRODUCTS WHERE id_catalog = id; END IF; END // З точки зору функціональності, збережені процедури з прикладів абсолютно однакові, але в останньому випадку знадобився лише один оператор IF, який приймає в якості логічного виразу параметр cur. Якщо cur дорівнює 1, що є істиною, виконується перший оператор, який виводить ціни в доларах, якщо параметр cur дорівнює 0, що є брехнею, виконується запит в блоці ELSE. Оператор CASE Оператор CASE дозволяє здійснити множинний вибір і має дві форми. Синтаксис першої форми оператора виглядає наступним чином: CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE Синтаксис другої форми: CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE Зауваження. Синтаксис оператора CASE всередині збереженої процедури трохи відрізняється від синтаксису SQL-виразу CASE. Оператор CASE не може містити конструкціію ELSE NULL, і його виконання завершується за допомогою виразу END CASE, а не END. У першій формі оператор CASE порівнює вираз case_value з when value. Як тільки відповідність знайдено, виконується відповідний оператор statement_list. Якщо ні однієї відповідності не знайдено, виконується оператор statement_list, розміщений після ключового слова ELSE (якщо воно, звичайно, присутнє). Наприклад, давайте перепишемо процедуру pricelist () з прикладу вище за допомогою оператора CASE (приклад нижче): 21 CREATE PROCEDURE pricelist (id INT, cur INT) LANGUAGE SQL BEGIN CASE cur WHEN 0 THEN SELECT name, price FROM PRODUCTS WHERE id_catalog = id; WHEN 1 THEN SELECT name, price/28.5 FROM PRODUCTS WHERE id_catalog = id; WHEN 2 THEN SELECT name, price/14.5 FROM PRODUCTS WHERE id_catalog id; WHEN 3 THEN SELECT name, price/50.4 FROM PRODUCTS WHERE id_cat.alog = id; ELSE SELECT 'Помилка в параметрі cur'; END CASE; END // Ситуація, коли в якості параметра cur передано помилкове значення, оброблена спеціально в блоці ELSE. Підхід із застосуванням оператора IF це також передбачає, але в операторі CASE ключове слово ELSE краще виділяється на фоні послідовності ключових слів WHEN порівняно з ключовими словами ELSE IF в операторі IF. Друга форма оператора CASE дозволяє здійснювати порівняння безпосередньо в конструкції WHEN - як тільки буде знайдено перше істинне значення, виконується оператор statement_list і процедура виходить з оператора CASE. У прикладі представлена процедура pricelist (), реалізована з використанням другої форми оператора CASE. Приклад: CREATE PROCEDURE pricelist (id INT, cur INT) LANGUAGE SQL BEGIN CASE WHEN cur = 0 THEN SELECT name, price FROM PRODUCTS WHERE id_catalog = id; WHEN cur = 1 THEN SELECT name, price/28.5 FROM PRODUCTS WHERE id_catalog = id; WHEN cur = 2 THEN SELECT name, price/14.5 FROM PRODUCTS WHERE id_catalog = id; WHEN cur = 3 THEN 22 SELECT name, price/20.4 FROM PRODUCTS WHERE id_catalog = id; ELSE SELECT 'Ошибка в параметре cur'; END CASE; END // Якщо в одному блоці WHEN необхідно виконати декілька запитів, слід використовувати блок BEGIN ... END.

19.Теорія нормалізації даних. Нормальна форма Бойса-Кодда.

Нормальна форма Бойса - Кодда (НФБК) Виникнення перекриваються потенційних ключів, коли частина одного ключа входить до складу іншого ключа, є досить частим випадком, і використання правил приведення відносин до тієї чи іншої нормальній формі сильно проблематично. Зокрема, для таких випадків була сформульована нормальна форма Бойса - Кодда (НФБК). Відношення знаходиться в нормальній формі Байса - Кодда тоді і тільки тоді, коли кожна його нетривіальна і не приводиться зліва функціональна залежність має в якості свого детермінанта деякий потенційних ключ [1] . Припустимо, що розглядається відношення містить три атрибути - "Замовлення", "Товар" і "Постачальник" (рис. 2.79), при цьому володіє деякими обмеженнями: • в кожному замовленні вказується певний товар від одного постачальника; • кожен постачальник поставляє тільки один товар; • певний товар можуть поставляти кілька постачальників. В даному прикладі є перекриваються потенційні ключі: { "Замовлення", "Товар"} і { "Замовлення", "Постачальник"}. Наявне відношення знаходиться в третій нормальній формі, але не в нормальній формі Байса - Кодда. Це викликає аномалії оновлення, що заважають коректно зберігати і обробляти дані. У підсумку, дане відношення варто розбити на два відносини (рис. 2.80). Здавалося б, проблеми зв'язку замовлення і постачальника дозволені і аномалії оновлення зникли, але не всі. При спробі додати в відношення "Товари замовлення" новий екземпляр даних необхідно обов'язково додати відповідний екземпляр в відношення "Товари постачальника", оскільки важливо розуміти, товар якого постачальника буде розміщений в замовленні. Правило переходу ЗНФ -> НФБК Якщо відношення знаходиться в ЗНФ і існують функціональні залежності, де детермінанти не є потенційними ключами, то такі залежності необхідно виділити в окремі відносини. Іншим прикладом перекриваються потенційних ключів може бути такою: залежність кількості товару від детермінанта "Товар" "Замовлення" і залежність цінового сегмента від детермінанта "Товар" - "Постачальник". Цей приклад найбільш наближений до реалій предметної області, оскільки кількість товару в замовленні повинно визначатися сукупністю атрибутів "Замовлення" і "Товар", а ціновий сегмент товару визначається не тільки самим товаром, але і постачальником цього товару. Цей приклад представляє відношення в нормальній формі Байса - Кодда, хоча і містить перекриваються ключі (рис. 2.81), оскільки ці ключі є єдиними детермінантами для відповідного неключових атрибута і процес нормалізації не потрібно.

27.Операції над множинами. Об'єднання. Перетин. Різниця

Об'єднання Об'єднання в реляційній алгебрі не повністю збігається з математичним об'єднанням, точніше, це особлива форма об'єднання, в якій потрібно, щоб два вихідних відношення були сумісні за типом. Об'єднання двох відношень R та S визначає нове відношення, яке включає всі кортежі, що містяться тільки в R і тільки в S, одночасно в R і S причому всі дублікати кортежів виключені. При цьому відношення R і S мають бути сумісними з об'єднанням. Якщо R і S включають, відповідно, I і J кортежів, то об'єднання цих відношень можна отримати, зібравши всі кортежі в одне відношення, яке може містити не більше (I + J) кортежів. Об'єднання можливе, тільки якщо схеми двох відношень збігаються, тобто складаються з однакової кількості атрибутів, причому кожна пара відповідних атрибутів має однаковий домен. Інакше кажучи, відношення мають бути сумісними з об'єднанням. 3 Зазначимо, що у визначенні сумісності з об'єднанням не вказано, що атрибути повинні мати однакові імена. У деяких випадках для отримання двох сумісних з об'єднанням відношень може бути використана операція проекції. Будемо говорити, що два відношення сумісні за типом, якщо у них ідентичні заголовки, точніше: 1. Якщо кожне з них має оду і ту ж множину імен атрибутів (отже, зауважте, вони повинні мати один і той же степінь); 2. Якщо відповідні атрибути (тобто атрибути з тими ж самими іменами в двох відношеннях) визначені на одному і тому ж домені. Операції об'єднання, перетину і віднімання вимагають від операндів сумісності по типу. Об'єднанням двох сумісних по типу відносин А і В (A UNION B) називається відношення з тим же заголовком, як і в відношеннях А і В, з тілом, що складається з множин всіх кортежів, що належать А або В або обом відносинам. --Перетин R Ώ S. Операція перетину визначає відношення, яке містить кортежі, присутні як у відношенні R, так і у відношенні S. Відношення R і S мають бути сумісними з об'єднанням. Перетином двох сумісних по типу відносин А і В (A INTERSECT B) називається відношення з тим же заголовком, як і у відношеннях А і В, і з тілом, що складається з множини всіх кортежів, що належать одночасно обом відносинам A і B. -Різниця R - S. Різниця двох відношень R і S складається з кортежів, які є у відношенні R, але відсутні у відношенні S. Причому відношення R і S мають бути сумісними з об'єднанням. Різницею двох сумісних по типу відношень А і В (A MINUS B) називається відношення з тим самим заголовком, як і у відношеннях А і В, і з тілом, що складається з множини всіх кортежів, що належать відношенню A і не належать відношенню B.

26.Реляційна алгебра. Операції над множинами

Операції над множинами -Об'єднання Об'єднання двох відношень R та S визначає нове відношення, яке включає всі кортежі, що містяться тільки в R і тільки в S, одночасно в R і S причому всі дублікати кортежів виключені. При цьому відношення R і S мають бути сумісними з об'єднанням. -Будемо говорити, що два відношення сумісні за типом, якщо у них ідентичні заголовки, точніше: 1. Якщо кожна з них має оду і ту ж множину імен атрибутів (отже, зауважте, вони повинні мати один і той же степінь); 2. Якщо відповідні атрибути (тобто атрибути з тими ж самими іменами в двох відношеннях) визначені на одному і тому ж домені. -Об'єднанням двох сумісних по типу відносин А і В (A UNION B) називається відношення з тим же заголовком, як і в відношеннях А і В, з тілом, що складається з безлічі всіх кортежів, що належать А або В або обом відносинам. -R Ώ S. Операція перетину визначає відношення, яке містить кортежі, присутні як у відношенні R, так і у відношенні S. Відношення R і S мають бути сумісними з об'єднанням. Перетином двох сумісних по типу відношень А і В (A INTERSECT B) називається відношення з тим же заголовком, як і в відношеннях А і В, і з тілом, що складається з множини всіх кортежів, що належать одночасно обом відношенням A і B. -R - S. Різниця двох відношень R і S складається з кортежів, які є у відношенні R, але відсутні у відношенні S. Причому відношення R і S мають бути сумісними з об'єднанням. Різницею двох сумісних по типу відношень А і В (A MINUS B) називається відношення з тим самим заголовком, як і у відношеннях А і В, і з тілом, що складається з множини всіх кортежів, що належать відношенню A і не належать відношенню B. -RxS. Операція декартового добутку визначає нове відношення, яке є результатом конкатенації (зчеплення) кожного кортежу з відношення R з кожним кортежем з відношення S. Операція декартового добутку застосовується для множення двох відношень. Множенням двох відношень називається створення іншого відношення, що складається з усіх можливих пар кортежів обох відношень. -Отже, якщо одне відношення має I кортежів і N атрибутів, а інше - J кортежів і М атрибутів, то їх декартовий добуток буде містити (I х J) кортежів і (N + М) атрибутів. Декартовий добуток двох відношень, має бути множиною впорядкованих пар кортежів. Але знову таки, необхідно зберегти властивість замкнутості; інакше кажучи, результат повинен містити кортежі, а не впорядковані пари кортежів. -Декартовий добуток двох відношень А і В (A TIMES B), де А і В не мають спільних імен атрибутів, визначається як відношення з заголовком, яке представляє собою зчеплення двох заголовків вихідних відношень А і В, і тілом, що складається з множини всіх кортежів t, таких, що t являє собою зчеплення кортежу a, що належить відношенню А, і кортежу b, що належить відношенню В. -Щоб отримати список клієнтів і коментарі про переглянуту ними техніку необхідно об'єднати ці два відношення А: ID номер, прізвище, місто (Клієнт) х В: ID Номер, Код пристрою, Коментар (Перегляд техніки) -У такому вигляді це відношення містить більше інформації, ніж необхідно. Наприклад, перший кортеж цього відношення містить різні значення атрибута ID номер. Для отримання шуканого списку необхідно для цього відношення провести операцію вибірки з витяганням тих кортежів, для яких виконується рівність: кліент = ID номер.пр. техніки (декартовий добуток). -S предикат (R) Операція вибірки застосовується до одного відношення R і визначає результуюче відношення, яке містить тільки ті кортежі (рядки) з відношення R, які задовольняють заданій умові (предикату). R і S - це два відношення, визначені на атрибутах А = (а1, а2,.. Аn) і В = (b1, b 2,..., B м) відповідно. -Вибірка - це скорочена назва -вибірки, де позначає будь-який скалярний оператор порівняння (=, , >, , ≤, <). -вибіркою з відношення А по атрибутам X Y (у цьому порядку). A WHERE X Y називається відношення, що має той самий заголовок, що і відношення А і тіло, що містить множину всіх кортежів відношення А, для яких перевірка умови X Y дає значення true. Атрибути X і Y повинні бути визначені на одному і тому ж домені, а оператор повинен мати сенс для цього домену. -Складіть список усіх співробітників із зарплатою, що перевищує 5000. Тут вихідним відношенням є відношення «Службовець», а предикатом - вираз оклад > 5000. Операція вибірки визначає нове відношення, що містить тільки ті кортежі відношення «Службовець», в яких значення атрибута оклад перевищує 5000. -P аi, аj, ..., az (R) застосовується до одного відношення, (R) і визначає нове відношення містить вертикальну підмножину відношень R, створювану за допомогою вилучення значень зазначених атрибутів. Проекцією відношення А по атрибутам X, Y, ..., Z, де кожен з атрибутів належить відношенню А [X, Y, ..., Z] називається відношення з заголовком {X, Y, ..., Z} і тілом, що містить множину всіх кортежів {Х: х, Y: y, ..., Z: z}, таких, для яких у відношенні А значення атрибута X рівне х, атрибута Y рівне y, ..., атрибута Z рівне z. -Нехай відношення А і В мають заголовки {Xl, X2, ..., Xm, Y1, Y2, ..., Yn} і {Yl, Y2, ..., Yn, Zl, Z2, ..., Zp} відповідно, тобто атрибути Yl, Y2, ..., Yn і тільки вони - спільне для двох відношень Х1, Х2, ... ,Хm - інші атрибути відношення А; Zl, Z2, ..., Zp - інші атрибути відношення В. Припустимо також, що відповідні атрибути (тобто атрибути з однаковими іменами) визначені на одному і тому ж домені. Розглядати вираз {X1, Х2, ..., Хm}, {Y1, Y2, ..., Yn} та {Zl, Z2, ..., Zp}, як три складових атрибуту X, Y та Z, відповідно. -Тоді природним з'єднанням відношення А і В (A JOIN B) називається відношення з заголовком {X, Y, Z} і тілом, що містить множину всіх кортежів {Х: х, Y: y, Z: z}, таких, для яких у відношенні А значення атрибута X дорівнює х, а атрибуту Y рівне у, і у відношенні В значення атрибута Y рівне у, а атрибуту Z дорівнює z. -З'єднання має властивості асоціативності і комутативності. Звідси випливає, що вираз: • (A JOIN В) JOIN C і A JOIN (В JOIN С) можуть бути однозначно спрощені до наступного: A JOIN В JOIN C • Крім того, вирази: A JOIN В та B JOIN A еквівалентні. -Типи операцій з'єднання, які дещо відрізняються один від одного і можуть бути тією чи іншою мірою корисні. • Тета -з'єднання (theta join). • З'єднання за еквівалентністю (equijoin), яке є окремим видом Тета - з'єднання. • Природне з'єднання (natural join). • Зовнішнє з'єднання (outer join). • Напівз'єднання (semijoin) -Ділення Нехай відношення А і В мають заголовки: {X1, X2, ..., Xm, Y1, Y2, ..., Yn} і {Y1, Y2, ..., Yn} відповідно, тобто атрибути Y1, Y2, ..., Yn - загальні для двох відношень, і відношення A має додаткові атрибути X1, Х2, ..., Хm, а відношення В не має додаткових атрибутів. (Відношення А і В представляють відповідно ділене і дільник.) -Припустимо також, що відповідні атрибути (тобто атрибути з однаковими іменами) визначені на одному і тому ж домені. Нехай тепер вираз {X1, Х2, ..., Хm} та {Y1, Y2, ..., Yn} позначають два складових атрибута Х і Y відповідно. -Тоді діленням відношень А на В (A DIVIDEBY B) називається відношення з заголовком {X} і тілом, що містить множину всіх кортежів {X: x}, таких що існує кортеж {Х: х, Y: y}, який належить відношенню A для всіх кортежів {Y: y}, що належать відношенню В. Не строго це можна сформулювати так: результат містить такі X-значення з відношення А, для яких відповідні Y-значення (з А) включають всі Y-значення з відношення В.

10.Модель «Сутність-зв'язок». Сутності.

Одна з найбільш важливих і розповсюджених семантичних моделей є модель "сутність-зв'язок", ER-модель (Entity Relationship). Основними поняттями ER-моделі є сутність, зв'язок і атрибут. Як і в реляційних схемах, в ER-моделях вводиться поняття нормальних форм, їх зміст дуже близький до змісту реляційних нормальних форм. Цей підхід дозволяє на початковій стадії правильно спроектувати логічну структуру БД. --------------------- Сутності. --Сутність - це реальний або уявний об'єкт, інформація про який має бути зібрана або збережена. Графічно сутність зображується поіменованим прямокутником із заокругленими кутами. Ім'я сутності подається в однині і пишеться великими літерами --Властивості сутностей: - будь-який предмет або об'єкт може бути відображений лише однією сутністю, тобто сутності завжди є взаємовиключаючими; - кожна сутність має бути унікально ідентифікована, тобто має існувати спосіб залежної ідентифікації кожного екземпляра сутності, що дає змогу відрізняти його від інших її екземплярів.

60.Програмування тригерів. Оператор DROP TRIGGER. Застосування тригерів для створення логу таблиці.

Оператор DROP TRIGGER. -Оператор DROP TRIGGER дозволяє видаляти існуючі тригери і має наступний синтаксис: DROP TRIGGER TBL_NAME.trigger_name Оператор видаляє тригер з ім'ям trigger_name таблиці TBL_NAME. У прикладі демонструється видалення тригера restrict_user таблиці users, створеного в прикладах вище: DROP TRIGGER USERS.restrict_user; --Застосування тригерів для створення логу таблиці Одним з визначення логу, може бути наступне: логи - це текстові файли, в яких зберігається інформація про відвідування, параметри відвідувань вашого сайту і помилки, які виникали на ньому. Також ведуть логи змін даних користувачами в базі даних. До прикладу, створимо таблицю, за якою ми будемо стежити: CREATE TABLE `test` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `content` TEXT NOT NULL) ENGINE = MYISAM Лог: CREATE TABLE `log` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , `msg` VARCHAR(255) NOT NULL , `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `row_id` INT(11) NOT NULL) ENGINE = MYISAM Тригер: CREATE TRIGGER `update_test` AFTER INSERT ON `test` FOR EACH ROW BEGIN INSERT INTO log Set msg = 'insert', row_id = NEW.id; END;// Тепер додайте запис в таблицю test. У таблиці log теж з'явиться запис, зверніть увагу на поле row_id, в ньому зберігається id вставленої вами рядка. Розширений лог Видаляємо тригер: DROP TRIGGER `update_test`; Створимо ще одну таблицю, в якій будуть зберігатися резервні копії рядків з таблиці test: CREATE TABLE `testing`.`backup` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `row_id` INT(11) UNSIGNED NOT NULL, `content` TEXT NOT NULL ) ENGINE = MYISAM Тригери: DELIMITER | CREATE TRIGGER `update_test` before update ON `test` FOR EACH ROW BEGIN INSERT INTO backup Set row_id = OLD.id, content = OLD.content; END; CREATE TRIGGER `delete_test` before delete ON `test` FOR EACH ROW BEGIN INSERT INTO backup Set row_id = OLD.id, content = OLD.content; END

56.Оператори керування потоками даних. Оператор REPEAT. Видалення та редагування збережених процедур та функцій

Оператор REPEAT Оператор REPEAT, так само як і оператор WHILE, реалізує цикл: [label:] REPEAT statement_list UNTIL search_condition END REPEAT [label] Відмінною особливістю даного циклу є той факт, що умова циклу search_condition перевіряється не на початку, як у циклі WHILE, а в кінці оператора (ключове слово UNTIL). Таким чином, цикл виконує, принаймні, одну ітерацію незалежно від умови. Слід зазначити, що цикл REPEAT виконується, поки умова search_concition помилкова. Оператор REPEAT може бути використаний з необов'язковою міткою label, по якій можна здійснювати достроковий вихід з циклу за допомогою операторів LEAVE та ITERATE. У прикладі представлена процедура binrand (), яка генерує і виводить випадкову бінарну послідовність з 20 символів. Для формування бінарної послідовності використовується цикл REPEAT. CREATE PROCEDURE binrand () LANGUAGE SQL BEGIN 27 DECLARE i INT DEFAULT 0; DECLARE bin TINYTEXT DEFAULT ''; REPEAT SET i = i + 1; IF RAND() >0.5 THEN SET bin = CONCAT(bin, ' 1'); ELSE SET bin = CONCAT(bin, '0') ; END IF; UNTIL i >= 20 END REPEAT; SELECT bin; END // CALL binrand()// bin 1010100011110001111110 8. Видалення та редагування збережених процедур та функцій Видалення збережених процедур Для видалення збережених процедур використовується синтаксис оператора DROP PROCEDURE, який має наступний синтаксис: DROP PROCEDURE [IF EXISTS] nameproc Оператор DROP ROCEDURE дозволяє видалити збережену процедуру nameproc. Якщо процедури з таким ім'ям не існує, синтаксис оператора повертає помилку, яку можна усунути, якщо використовувати необов'язкове ключове слово IF EXISTS. Наприклад, створимо в базі даних «test» збережену процедуру test () і застосуємо до неї синтаксис оператора DROP PROCEDURE: CREATE PROCEDURE test() SELECT VERSION(); DROP PROCEDURE test; Проте використання оператора DROP PROCEDURE стосовно збереженої функції закінчується помилкою Для видалення збережених функцій необхідно використовувати спеціальний оператор DROP FUNCTION: DROP FUNCTION test; 28 Редагування збережених процедур Для зміни характеристик процедури призначений оператор ALTER PROCEDURE. Редагування збереженої функції виконується за допомогою оператора ALTER FUNCTION. Оператори мають наступний синтаксис: ALTER PROCEDURE sp_name [characteristic ...] ALTER FUNCTION sp_name [characteristic ...] Характеристика characteristic може приймати такі значення: 1. SQL SECURITY (DEFINER | INVOKER) - даний параметр визначає режим виконання: процедура виконується або з правами користувача, що її створив (DEFINER), або з правами користувача, що викликав її (INVOKER). 2. COMMENT 'string' - даний параметр дозволяє призначити коментар для процедури. Важливо. Для виконання операторів ALTER PROCEDURE та ALTER FUNCTION необхідно мати привілей ALTER ROUTINE. Даний привілей автоматично передається користувачеві, що створив збережену процедуру: ALTER PROCEDURE test SQL SECURITY INVOKER COMMENT 'Функція повертає версію сервера';

45.Команди модифікації мови DML. UPDATE, DELETE

Оператор SQL UPDATE -Оператор SQL UPDATE призначений для оновлення (редагування) даних в таблиці. Він застосовується, коли в тому чи іншому рядку таблиці вже записані деякі дані і потрібно внести в них зміни. Синтаксис: UPDATE ім'я_таблиці SET ім'я_стовпця_1=значення, ..., ім'я_стовпця_N=значення [WHERE умова] Квадратні дужки [], в які укладена частина запиту WHERE умова, означає, що ця частина є необов'язковою. Використання оператора UPDATE разом з секцією WHERE Хоча частина запиту на оновлення даних є необов'язковою, все ж у більшості випадків вона застосовується, так як частіше оновлювати потрібно значення стовпців в певних рядках. Приклад 1. Потрібно змінити значення стовпців Units і Money в рядку з Id = 4. Для цього напишемо наступний запит (на MS SQL Server - з передуючою конструкцією USE adportal1;): UPDATE ADS SET Units=148, Money=23680 WHERE Id=4 Запитом на оновлення даних, з використанням оператора UPDATE та секції WHERE, можна змінити значення стовпців і в декількох рядках, які відповідають умові, що вказана в секції WHERE. --Використання оператора UPDATE і обчислювані значення Приклад 3. Тепер припустимо, що під час заповнення таблиці даними, змінилися розцінки на оголошення, що публікуються на порталі. Потрібно збільшити значення стовпця Money в 2 рази у всіх рядках таблиці. Пишемо наступний запит (на MS SQL Server - з передуючою конструкцією USE adportal1;): UPDATE ADS SET Money = Money*2 --Використання оператора UPDATE без секції WHERE Приклад 4. Потрібно зробити невизначеними (NULL) значення стовпців Units і Money у всіх рядках таблиці. Запит для такого оновлення даних буде наступним (на MS SQL Server - з передуючою конструкцією USE adportal1;): UPDATE ADS SET Units=NULL, Money=NULL ---Оператор SQL DELETE Оператор DELETE і видалення даних з умовою Оператор SQL DELETE призначений для видалення даних з таблиці. Синтаксис: DELETE FROM ім'я_таблиці WHERE умова Якщо не вказувати умову, з таблиці будуть видалені всі рядки. Крім того, слід пам'ятати, що можуть бути видалені лише рядки з первинними ключами, на які не посилаються зовнішні ключі в інших таблицях. Приклад 1. Потрібно видалити з таблиці рядок, що має ідентифікатор 4. Для цього напишемо наступний запит (на MS SQL Server - з передуючою конструкцією USE adportal1;): DELETE FROM ADS WHERE Id=4 --Оператор DELETE і видалення всіх даних з таблиці Для видалення всіх рядків з таблиці застосовується оператор SQL DELETE без умов, заданих в секції WHERE і без будь-яких інших обмежень та умов, наприклад, діапазону видалених рядків. Таким чином, для видалення всіх рядків синтаксис оператора DELETE буде наступним (на MS SQL Server - з передуючою конструкцією USE adportal1;): DELETE FROM ім'я_таблиці Приклад 4. Щоб видалити всі дані з таблиці ADS, досить написати наступний запит: DELETE FROM ADS Оператору DELETE без умов і обмежень відповідає оператор TRUNCATE TABLE. Він також видаляє з таблиці всі рядки, але виконується набагато швидше. Ця команда часто розглядається разом з DML. Але, насправді це DDL команда, але так як ефект для користувачів такий же, як і від команди DELETE, то команда TRUNCATE задовольняє параметрам DML команд. Приклад 5. Запит на видалення всіх даних з таблиці ADS за допомогою оператора TRUNCATE TABLE буде наступним (на MS SQL Server - з передуючою конструкцією USE adportal1;): TRUNCATE TABLE ADS

55.Оператори керування потоками даних. Оператор WHILE. ITERATE label.

Оператор WHILE Оператор WHILE виконує цикл і має наступний синтаксис: [label:] WHILE search_condition DO statement_list END WHILE [label] Цикл WHILE виконує оператори statement_list до тих пір, поки умова search_condition істинна. При кожній ітерації умова search_condition перевіряється і якщо при черговій перевірці вона буде хибною (0), цикл завершить своє виконання. Це означає, що якщо умова search_condition хибна з самого початку, цикл не виконає жодної ітерації. Якщо в циклі потрібно виконати більше одного оператора, не обов'язково укладати, їх у блок BEGIN ... END, так як цю функцію виконує сам оператор WHILE. Виведемо 3 рази поточну дату за допомогою циклу WHILE (приклад): CREATE PROCEDURE NOW5() LANGUAGE SQL BEGIN DECLARE i INT DEFAULT 3; WHILE i > 0 DO SELECT NOW(); SET i = i - 1; END WHILE; END // CALL NOW5()// NOW() 2020-07-18 12:36:59 NOW() 23 2020-07-18 12:36:59 NOW() 2020-07-18 12:36:59 Перший оператор в циклі WHILE виводить поточну дату, а другий віднімає з локальної змінної 'i' одиницю. Якщо одиницю не віднімати, то утворюється нескінченний цикл, з якого процедура ніколи не вийде, а буде марно навантажувати сервер, поки сеанс з ним не припиниться. Необхідно дуже уважно проектувати цикли, щоб уникати нескінченних циклів. У прикладі нижче представлений код збереженої процедури, яка виводить поточну дату num число раз, де num - параметр, що задається користувачем. Приклад: CREATE PROCEDURE NOWN (IN num INT) LANGUAGE SQL BEGIN DECLARE i INT DEFAULT 0; IF (num > 0) THEN wet : WHILE i < num DO SELECT NOW(); SET i = i + 1; END WHILE wet; ELSE SELECT 'Помилкове значення параметра'; END IF; END // CALL NOWN(2)// NOW() 2020-07-18 12:51:27 NOW() 2020-07-18 12:51:27 Як видно з попереднього прикладу, цикл WHILE іменований міткою wet. Мітка в циклі призначена не тільки для того, щоб полегшити читання коду при дуже довгих циклах, вона дозволяє здійснювати достроковий вихід з циклу. Для дострокового виходу з циклу призначений оператор LEAVE, який має наступний синтаксис: LEAVE label Оператор LEAVE припиняє виконання блоку, позначеного міткою label. Важливо. Оператор LEAVE еквівалентний оператору break в С-подібних мовах програмування. 24 Процедура NOWN () має недолік - якщо задати дуже велике значення аргументу num, можна створити псевдобескінечний цикл, який дозволить зловмиснику завантажити сервер «марною» роботою. Для запобігання такої ситуації можна скористатися оператором LEAVE, який припинить виконання циклу після досягнення критичного числа ітерацій. У лістингу нижче наводиться приклад збереженої процедури, де число ітерації обмежена двома. CREATE PROCEDURE NOWN (IN num INT) LANGUAGE SQL BEGIN DECLARE i INT DEFAULT 0; IF (num > 0) THEN wet : WHILE i < num DO IF i > 2 THEN LEAVE wet; END IF; SELECT N0W(); SET i = i + 1; END WHILE wet; ELSE SELECT 'Помилкове значення параметра'; END IF; END // CALL NOWN(10)// NOW() 2020-07-18 12:51:27 NOW() 2020-07-18 12:51:27 Умова if i> 2 then leave wet; перевіряє, чи не перевищино значення лічильника «i» число 2, і якщо це так, відбувається припинення циклу while. Використання міток дозволяє точно вказати, який цикл необхідно перервати. Якщо є вкладений цикл, можна явно вказати, який з двох циклів потрібно перервати (приклад). Приклад: first : WHILE i < num DO second : WHILE j < num DO IF i > 2 && j > 2 THEN LEAVE first; END IF; SELECT NOW(); SET j = j + 1; END WHILE second; SET i = i + 1; END WHILE first; 25 При досягненні умови i> 2 && j> 2 оператор LEAVE перериве не вкладений цикл second, а зовнішній цикл first, так як мітка зовнішнього циклу явно вказана після оператора. ITERATE label На відміну від оператора LEAVE, оператор ITERATE не припиняє виконання циклу, він лише виконує дострокове припинення поточної ітерації. Важливо. Оператор ITERATE еквівалентний оператору continue в С-подібних мовах програмування. Розглянемо програму, яка в циклі формує бінарну послідовність, додаючи до рядка дві одиниці на парних ітераціях та дві одиниці і два нулі на непарних (приклад). Приклад: CREATE PROCEDURE binstring (IN num INT) LANGUAGE SQL BEGIN DECLARE i INT DEFAULT 0; DECLARE bin TINYTEXT DEFAULT ''; IF (num > 0) THEN wet : WHILE 1 < num DO SET i = i + 1; SET bin = CONCAT(bin, 'll'); IF !(i/2 - CEILING(i/2)) THEN ITERATE wet; END IF; SET bin = CONCAT(bin, '00'); END WHILE wet; SELECT bin; ELSE SELECT 'Хибне значення параметру'; END IF; END // CALL binstring(10)// bin 110011110011110011110011110011 1 Бінарна послідовність зберігається в тимчасовому рядку bin, який обов'язково має бути ініційований порожній рядком: DECLARE bin TINYTEXT DEFAULT ''; Якщо ініціалізація не проведена, змінна отримає значення NULL, і всі операції з цією змінною також повертатимуть null. На кожній ітерації змінної bin 26 за допомогою функції CONCAT () додається послідовність '11 '. Якщо індекс i є непарним (1,3,5,7,9), поточний цикл припиняється за допомогою ключового слова iterate, якщо індекс є парним (0,2, 4,6, 8), то ітерація виконується до кінця, так як до тимчасового рядку bin додається ще й послідовність '00 '. На парність індекс «i» перевіряється за допомогою рядку: i/2 - CEILING(i/2) Якщо індекс i ділиться на 2 без залишку, цей вираз поверне 0 (брехня), якщо число є непарним, то вираз поверне 0.5 (істина). Зауваження. Необхідно стежити, щоб оператор SET, що збільшує значення лічильника «i» на одиницю, на кожній ітерації був розташований до оператора ITERATE, інакше це призведе до створення нескінченного циклу - значення лічильника буде залишатися не парним, «і» збільшуватися не буде, тому що оператор ITERATE припинятиме виконання ітерації циклу достроково.

5. Основні поняття і архітектура. Основи роботи з базами даних.

Основи роботи з базами даних. --Перевід даних між додатком та базою даних. Вам потрібно буде створювати або використовувати існуючий інтерфейс для зв'язку з базою даних. Ви можете безпосередньо підключатися до бази даних, використовуючи звичайні мережеві функції, прості бібліотеки або бібліотеки програмування більш високого рівня. --Перевід даних між додатком та базою даних Наприклад, конструктори запитів (query builders) або ORM (Object-relational mapping - технологія програмування, яка зв'язує БД з концепціями об'єктно-орієнтованих мов програмування, створюючи «віртуальну об'єктну базу даних»). ORM - це шари зіставлення, які переводять таблиці, знайдені в реляційній базі даних у класи використовуючи програми об'єктноорієнтованих мов, і навпаки. --Синхронізація структурних змін із базою даних Поширеним підходом до синхронізації структур даних вашої програми з вашою БД - це процес, який називається міграцією бази даних або міграцією схеми (обидва більш відомі як міграція). Міграція передбачає оновлення структури вашої БД для відображення змін під час розвитку моделі даних вашого додатку. --Захист доступу до ваших даних та очистка вводу Вашому додатку потрібен привілейований доступ до Вашої БД для виконання рутинних задач. З міркувань безпеки система авторизації БД допомагає обмежити тип операцій, які може виконувати ваша програма. Одна зі специфічних проблем - очищення введення користувача введення (sanitizing user input). Очистка введення даних, Sanitizing input (або очисні фільтри для сайту) - означає вжиття спеціальних запобіжних заходів при роботі з будь-якими даними, наданими користувачем.

12.Модель «Сутність-зв'язок». Атрибути: прості и складні, однозначний та багатозначний, домен атрибута.

Основними поняттями реляційних баз даних є тип даних, домен, атрибут, кортеж, первинний ключ і відношення. Бази даних, між окремими таблицями якої існують зв'язки, називаються реляційними (від relation - відношення). Таким чином, реляційна модель даних представляє інформацію у вигляді сукупності взаємопов'язаних таблиць, які прийнято називати відношеннями або реляціями. Пов'язані відношення взаємодіють за принципом головна (master) - підлегла (detail). Головну таблицю часто називають батьківською, а підлеглу - дочірньою. Одна і та ж таблиця може бути головною у відношенні до однієї таблиці БД і дочірньою по відношенню до іншої. Відношення - реляційна таблиця. Тип даних. Поняття тип даних в реляційній моделі повністю еквівалентно до відповідного поняття в алгоритмічних мовах. Тип даних визначає можливі способи обробки даних і місце, необхідне для їх зберігання. Набір підтримуваних Багато Факультативний Один Обов'язковий 4 типів даних визначається СКБД і може сильно відрізнятися в різних системах. Однак існують типи даних загальні для всіх СКБД: • цілочисловий тип; • дійсний; • рядковий; • спеціалізований тип даних для грошових величин; • спеціальні типи даних для зберігання дати або дати і часу; • типи бінарних об'єктів (даний тип не має аналога в мовах програмування; зазвичай для його позначення використовується абревіатура BLOB - Binary Large Object). Домен - це множина атомарних значень одного і того ж типу. Домени представляють собою користувацький тип. Атрибут - це характеристика об'єкта (сутності). Атрибути мають імена, через які до них відбувається звернення. Ім'я атрибута повинно бути унікальним всередині відношення. Простий атрибут - атрибут, що складається з одного компонента з незалежним існуванням. Прості атрибути не можуть бути розділені на більш дрібні компоненти. Прикладом простих атрибутів є атрибут position (Посада) або salary (Зарплата) сутності Службовці. Прості атрибути іноді називають елементарними. Складений атрибут - атрибут, що складається з декількох компонентів, кожен з яких характеризується незалежним існуванням. Приклад складеного атрибута - це адреса (Вулиця Будинок Кв), як бачимо, вона складається з простих атрибутів (Хоча будинок може бути теж складеним, але в рамках іншої моделі). Однозначний атрибут. Атрибут, який містить одне значення для кожного екземпляра сутності певного типу. Більшість атрибутів є однозначними. Наприклад, для кожного окремого примірника сутності «Деталі, що випускаються» завжди є єдине значення в атрибуті «Код деталі», наприклад «мк1247», тому атрибут «Код деталі» є однозначним. Багатозначний атрибут. Атрибут, який містить кілька значень для кожного екземпляра сутності певного типу. Деякі атрибути можуть мати кілька значень для кожного екземпляра сутності. Наприклад, сутність «Клієнт» може мати кілька значень для атрибута 5 «N_телефону» (Номер телефона клієнта). Припустимо, Клієнт1 має два номери +380503174534, +380997283875. Отже, атрибут «N_телефону» в цьому випадку буде багатозначним. Багатозначний атрибут допускає присутність певної кількості значень (можливо, в заданих межах, що визначають максимальну і мінімальну кількість). Наприклад, атрибут «N_телефону» клієнтів фірми «Софт» може мати від одного до трьох значень. Іншими словами, будь-який клієнт фірми «Софт» повинен мати не менше одного номера телефону і не більше трьох номерів телефонів. Похідний атрибут. Атрибут, який представляє значення, похідне від значення пов'язаного з ним атрибута або деякої безлічі атрибутів, що належать деякому (не обов'язково даному) типу сутності. Деякі атрибути можуть бути пов'язані з певною сутністю. Наприклад, значення атрибута «Термін тестування» сутності «ПЗ» обчислюється на основі атрибутів «Початок тестування» і «Кінець тестування», які також належать до типу сутності «ПЗ». У деяких випадках значення атрибута є похідним від багатьох екземплярів сутності одного і того самого типу. Наприклад, атрибут «Загальна кількість співробітників» сутності типу «Службовці» може бути обчислений на основі підрахунку загальної кількості примірників сутності «Службовці». Похідні атрибути можуть також створюватися у формі асоціацій атрибутів сутностей різних типів. Наприклад, розглянемо атрибут «Премія» сутності типу «Службовці». Значення атрибута «Премія» розраховується як «кількість наукових статей» помножене на значення середньо годинної оплати за рік. Отже, значення атрибута «Премія» сутності «Службовці» є похідним від атрибута «кількість наукових статей» сутності типу «Наукова робота».

51.Параметри збережених процедур.

Параметри збережених процедур. -Як згадувалося раніше, кожен параметр може бути оголошений одним з модифікатором IN, OUT або INOUT. У наступному прикладі наводиться приклад функції, яка присвоює змінній користувача @х нове значення: CREATE PROCEDURE set_x (IN value INT) BEGIN SET @x = value; END // CALL set_x(123456)// SELECT @x// --Важливо. На відміну від змінної @х, яка є глобальною і доступна як всередині збереженої процедури set_x(), так і поза нею, параметри функції є локальними і доступні для використання тільки всередині функції. Використання ключового слова IN не є обов'язковим - якщо жоден з модифікаторів не вказано, СУБД MySQL вважає, що параметр оголошений з модифікатором IN: CREATE PROCEDURE set_y (value INT) BEGIN SET value = 7; SET @x = value; END // SET @val = 123456// CALL set_y(@val) --Важливо. Слід зазначити, що імена параметрів при оголошенні збереженої процедури і при виклику не обов'язково повинні збігатися. Всередині процедури всі локальні змінні використовуються без символу `@` в той час як для глобальних змінних символ `@` перед ім'ям обов'язковий Після виконання процедури, значення користувача змінної @val, переданої функції як параметр, не змінюється. Якщо потрібно, щоб значення змінної змінювалося, необхідно оголосити параметр процедури з модифікатором OUT. CREATE PROCEDURE set_y (OUT value INT) BEGIN SET @x = value; SET value = 7; END// SET @val = 123456// CALL set_y(@val)// SELECT @x,@val// --Важливо. Якщо локальна або користувацька змінні не ініцілізовуються за допомогою оператора SET чи ключового слова DEFAULT, вони отримують значення NULL. Для того щоб через параметр можна було, як передати значення всередину процедури, так і отримати значення, яке потрапляє в параметр, в результаті обчислень всередині процедури, його слід оголосити з модифікатором INOUT. CREATE PROCEDURE set_y (INOUT value INT) BEGIN SET @x = value; SET value = 7; END // SET @val = 123456// CALL set_y(@val)// SELECT @x, @val//

16.Теорія нормалізації даних. Перша нормальна форма (1НФ).

Перша нормальна форма (1НФ). --Ненормалізована форма (ННФ) - таблиця, що містить одну або кілька повторюваних груп даних. Перша нормальна форма (1НФ) - ставлення, в якому на перетині кожного рядка і кожного стовпця міститься одне і тільки одне значення. Для перетворення ненормалізованої таблиці в першу нормальну форму (1НФ) у вихідній таблиці слід знайти й усунути всі повторювані групи даних. --Повторюваною групою називається група, що складається з одного або декількох атрибутів таблиці, в якій можлива наявність декількох значень для одного значення ключового атрибута (атрибутів) таблиці. --Існують два способи виключення повторюваних груп з ненормалізованих таблиць. У першому способі: • Повторювані групи усуваються шляхом введення відповідних даних у порожні місця стовпців рядка з повторюваними даними. • Інакше кажучи, порожні місця при цьому заповнюються дублікатами даних, що не повторюються. --У другому способі: • Один атрибут або група атрибутів призначаються ключем ненормалізованої таблиці. • Потім групи, що повторюються, вилучаються і поміщаються в окремі відношення разом з копіями ключа вихідної таблиці. • Далі в нових відношеннях встановлюються свої первинні ключі. Отриманий набір відношень знаходитиметься в 1НФ тільки тоді, коли в жодному з них не буде повторюваних груп атрибутів. --Розглянемо приклади. • Кожен атрибут повинен бути простим, тобто мати атомарні або неподільні значення. Наприклад, відношення СТУДЕНТ_СПОРТ ненормалізоване, оскільки містить складний атрибут "спорт". --Первинним ключем цього відношення є атрибут "ПІБ". • Відношення, у якого всі атрибути прості, називається приведеним до першої нормальної форми (1НФ). • Приведемо це відношення до 1НФ, тобто звільнимося від складного атрибута "спорт" --В отриманому відношенні ключ складається з атрибутів "ПІБ" та "вид_спорт".

24.Реляційна алгебра. Поняття реляційної алгебри.

Поняття реляційної алгебри. -Реляційна алгебра - це мова операцій, що дозволяють створювати на основі одного або декількох відношень інше відношення без зміни самих вихідних відношень. Реляційна алгебра є мовою послідовного використання відношень, в якій всі кортежі, можливо, навіть взяті з різних відношень, обробляються однією командою, без організації циклів. -Реляційна алгебра, яка визначена Коддом, складається з восьми операторів, які складаються з двох груп, по чотири оператора в кожній: 1. Традиційні операції над множинами: 1) Об'єднання (union); 2) Перетин(intersection) ; 3) Різниця множин (set difference); 4) Декартовий добуток (cartesian product) (модифіковані з урахуванням того, що їх операндами є відношення, а не довільні множини). -2. Спеціальні реляційні операції: 1) Вибірка (selection) , 2) Проекція (projection), 3) З'єднання (join) 4) Ділення (division).

42.Предикат EXISTS SQL і перевірка існування набору значень

Предикат EXISTS SQL і перевірка існування набору значень Предикат мови SQL EXISTS виконує логічну задачу. У запитах SQL цей предикат використовується в виразах вигляду: EXISTS (SELECT * FROM ім'я_таблиці...) Цей вираз повертає true, коли за запитом знайдено один чи більше рядків, що відповідають умові і false, коли не знайдено жодного рядку. Як правило, предикат EXISTS застосовується у випадках, коли необхідно знайти значення, що відповідні основній умові, заданій у секції WHERE і додатковій умові, вкладеній у підзапит, який є аргументом предиката. Для NOT EXISTS все навпаки: NOT EXISTS (SELECT * FROM ім'я_таблиці...) Вираз повертає істину, коли за запитом не знайдено жодного рядка і false, коли знайдено хоча б один рядок. До прикладу візьмемо з базу даних бібліотеки і її таблиці "Книга в користуванні" (BOOKINUSE) і "Користувач" (USER). Поки нам буде потрібно лише таблиця "Книга в користуванні" (BOOKINUSE). Приклад 1. Визначити ID користувачів, яким видано книги Толстого, яким також видані книги Чехова. У зовнішньому запиті відбираються дані про користувачів, яким видано книги Толстого, а предикат EXISTS задає додаткову умову, яке перевіряється у внутрішньому запиті - користувачі, яким видано книги Чехова. Додатковою умовою у внутрішньому запиті є збіг ідентифікаторів користувачів з зовнішнього і внутрішнього запитів: Customer_ID = tols_user.Customer_id. Запит буде наступним: SELECT Customer_ID FROM Bookinuse AS tols_user WHERE Author='Толстой' AND EXISTS (SELECT Customer_ID FROM Bookinuse WHERE Author='Чехов' AND Customer_ID=tols_user.Customer_id) Це поверне наступний результат: Customer_ID 65 205 Приклад використання NOT EXISTS у запиті, що вирішує схоже завдання. Приклад 2. Визначити ID користувачів, яким видано книги Чехова, і яким при цьому не видані книги Ільфа і Петрова. Конструкція запиту аналогічна конструкції з попереднього прикладу з тією різницею, що додаткова умова задається предикатом NOT EXISTS. Запит буде наступним: SELECT Customer_ID FROM Bookinuse AS cheh_user WHERE Author='Чехов' 16 AND NOT EXISTS (SELECT Customer_ID FROM Bookinuse WHERE Author='Ильф и Петров' AND Customer_ID=cheh_user.Customer_id) Цей запит поверне наступний результат: User_ID 120 65 205 Приклад 3. Cпробуйте самостійно визначити автора (авторів), книги якого видано користувачу з ID 120, а також з ID 18. --Відмінності предикатів EXISTS і IN При першому погляді на запити з командою EXISTS може виникнути враження, що він ідентичний команді IN. Це не вірно. Хоча вони дуже схожі. Предикат IN веде пошук значень з діапазону, заданого в його аргументі і якщо такі значення є, то вибираються всі рядки, що відповідають цьому діапазону. Результатом дії EXISTS є відповідь "так" або "ні" на питання про те, чи є взагалі будь-які значення, що відповідають зазначеним в аргументі. Крім того, перед предикатом IN вказується ім'я стовпця, за яким слід шукати рядки, що відповідають значенням в діапазоні. Розберемо приклад, що показує відмінність предиката EXISTS від предиката IN і завдання, що вирішується за допомогою предиката IN. Приклад 4. Визначити ID користувачів, яким видано книги авторів, книги яких видані користувачу з ID 31. Запит буде наступним: SELECT Customer_ID FROM Bookinuse WHERE Author IN (SELECT Author FROM Bookinuse WHERE Customer_ID = 31) User_ID 120 65 205 Внутрішній запит (після IN) вибирає авторів: Чехов; Ільф і Петров. Зовнішній запит вибирає всіх користувачів, яким видано книги цих авторів. Як 17 видно, на відміну від команди EXISTS, команді IN передує ім'я стовпця, в даному випадку - Author. Запити з предикатом EXISTS та додатковими умовами Якщо додатково до команди EXISTS у запиті застосувати хоча б одну додаткову умову, наприклад, задану за допомогою агрегатних функцій, то такі запити можуть слугувати вже для простого аналізу даних. Переконаємося в цьому на наступному прикладі. Приклад 5. Визначити ID користувачів, яким видана хоча б одна книга Пастернака, і яким при цьому видано понад 2 книги. Для цього напишемо наступний запит, в якому перша умова задається предикатом EXISTS зі вкладеним запитом, а друга умова з оператором HAVING завжди має слідувати після вкладеного запиту: SELECT Customer_ID FROM Bookinuse AS pas_user WHERE EXISTS (SELECT Customer_ID FROM Bookinuse WHERE Author='Пастернак' AND Customer_ID=pas_user.Customer_ID) GROUP BY Customer_ID HAVING COUNT(Title) > 2 User_ID 120 Як видно з таблиці BOOKINUSE, книга Пастернака видана також користувачу з ID 18, але йому видана всього одна книга і він не потрапляє до вибірки. Якщо застосувати до подібного запиту ще раз функцію COUNT, але вже для підрахунку обраних рядків (потренуйтеся в цьому самостійно), то можна отримати відомості про те, скільки користувачів, які читають книги Пастернака, при цьому читають також книги інших авторів. Запити з предикатом EXISTS до двох таблиць Запити EXISTS можуть отримувати дані з більш ніж однієї таблиці. Багато задач можна з тим же результатом вирішити за допомогою оператора JOIN, але в ряді випадків використання EXISTS дозволяє скласти менш громіздкий запит. Використовувати EXISTS краще в тих випадках, коли в результуючу таблицю потраплять стовпці лише з однієї таблиці. 18 Приклад 6. Визначити авторів, книги яких видані користувачу на прізвище Краснов. Напишемо наступний запит, в якому командеою EXISTS задано єдина умова: SELECT DISTINCT Author FROM Bookinuse bk WHERE EXISTS (SELECT * FROM Customer cs WHERE cs.Customer_ID=bk.Customer_ID AND Surname='Краснов') Результатом виконання запиту буде наступна таблиця: Author Чехов Маяковский Пастернак Як і у випадках використання оператора JOIN, при роботі з більше ніж однією таблицею слід використовувати псевдоніми таблиць для перевірки відповідності значень ключів, що з'єднують таблиці. У нашому прикладі псевдоніми таблиць - bk і us, а ключ, що з'єднує таблиці - User_ID. Предикат EXISTS в з'єднаннях більше двох таблиць У наступних прикладах побачимо, чому використовувати EXISTS краще в тих випадках, коли в результуючу таблицю потраплять стовпці лише з однієї таблиці. Таблиця Deal містить дані про операції. Для наших завдань в цій таблиці буде важливий стовпець Type з даними про тип угоди - продаж або оренда. Таблиця Object містить дані про об'єкти. У цій таблиці нам знадобляться значення стовпців Rooms (число кімнат) та LogBalc, що містить дані про наявність лоджії або балкона у булевом форматі: 1 (так) або 0 (немає). Таблиці Client, Manager та Owner містять дані відповідно про клієнтів, менеджерів фірми і власників об'єктів нерухомості. У цих таблицях FName та LName відповідно ім'я і прізвище.

15.Теорія нормалізації даних. Процес нормалізації.

Процес нормалізації. --Головною метою групування даних в кортежі - зменшення надмірності даних. Для рішення цієї проблеми застосовують підхід, який зветься нормалізацією відношень. Деякі функціональні залежності атрибутів є небажаними через побічні явища та аномалії, які вони можуть викликати. --Зазвичай розрізняють наступні проблеми: • надмірність даних; • аномалії оновлення; • аномалії видалення; • аномалії введення. Надмірність даних характеризується наявністю в кортежах відношень повторюваної інформації. --Аномалії оновлення, пов'язані з надмірністю даних, що призводить до проблем при їх зміні. Аномалії видалення можуть виникати при видаленні записів з ненормалізованих таблиць і характеризуються ймовірністю видалення не всіх дубльованих кортежів. Аномалії введення виникають при додаванні в таблицю нових записів, зазвичай в поля з обмеженнями NOT NULL (не порожні). --Для ліквідації небажаних функціональних залежностей є спеціальний формальний механізм названий нормалізацією. Нормалізація - це формальний метод аналізу відношень на основі їх первинного ключа (або потенційних ключів) та існуючих функціональних залежностей. Він включає ряд правил, які можуть використовуватися для перевірки окремих відношень так, щоб вся база даних могла бути нормалізована до бажаного ступеня --Нормалізація відношень - це покроковий зворотній процес розкладання початкових відношень на більш дрібні і прості. При цьому встановлюються всі можливі функціональні залежності. Апарат нормалізації був розроблений Коддом. В ньому визначаються різні нормальні форми. Кожна з нормальних форм обмежує типи функціональних залежностей відношень. --У теорії реляційних БД звичайно виділяється наступна послідовність нормальних форм: • перша нормальна форма (1НФ); • друга нормальна форма (2НФ); • третя нормальна форма (3НФ); • нормальна форма Бойса-Кодда (БКНФ); • четверта нормальна форма (4НФ); • п'ята нормальна форма, або нормальна форма проекції-з'єднання (5НФ або ПЗ/НФ).

43.Підзапити мови SQL. Корелюючі та некорелюючі підзапити

Підзапити мови SQL. Корелючі та некорелючі підзапити Підзапити, які повертають єдине значення Підзапити (вкладені запити), які повертають єдине значення, часто застосовуються у випадках, коли значення певного стовпця в основному запиті потрібно порівняти з деяким єдиним значенням за допомогою одного з операторів порівняння (=, <, >, <= , >=). Значення, з яким проводиться порівняння, як раз і повертається підзапитом (вкладеним запитом). Вірною ознакою того, що підзапит поверне одне єдине значення є: - в підзапиті застосовується одна з агрегатних функцій (COUNT, SUM, AVG, MAX, MIN); - підзапит витягує значення унікального ідентифікатора, наприклад, первинного ключа. В інших випадках потрібно бути повністю впевненим, що з умовами, зазначеними в секції WHERE підзапиту, відповідає єдине значення обраного стовпця. Для прикладу, далі будемо розглядати базу даних "Театр". Таблиця Play містить дані про постановках. Таблиця Team - про ролі акторів. Таблиця Actor - про акторів. Таблиця Director - про режисерів. Поля таблиць, первинні та зовнішні ключі можна побачити на нижче. 23 Приклад 1. Вивести спектаклі режисера, до прикладу, з ім'ям John Barton. Запит буде наступним: SELECT Name FROM PLAY WHERE Dir_ID= (SELECT Dir_ID FROM DIRECTOR WHERE FName='John' AND LName='Barton') Оскільки підзапит повертає значення унікального ідентифікатора, можна бути впевненим в тому, що він поверне єдине значення. Менше використовують підзапити, які повертають єдине значення - у запитах з командою BETWEEN, де підзапити задають межі інтервалу. Підзапити, які повертають безліч значень Підзапити, які повертають множину значень, можуть застосовуватися в запитах з командами IN та EXISTS і кванторними функціями ALL та ANY. Приклад 2. Уявімо собі, що у таблиці TEAM стовпець Mainteam містить дані про те, чи головна роль закріплена за актором у виставі. Наприклад, значення стовпця 'Y' означає "так", 'N' - "ні". Вивести список акторів, які колись виконували головні ролі. Запит буде наступним: SELECT FName, LName FROM ACTOR WHERE Actor_ID IN (SELECT Actor_ID FROM TEAM WHERE Mainteam='Y') Підзапит поверне безліч значень FName і LName (з таблиці ACTOR, як видно з рисунку), які через ключ Actor_ID буде передано в основний запит і остаточно виведено в якості результату. Приклад 3. Уявімо, що у таблиці ACTOR (актор) є стовпець SEX (стать), що містить дані про поле ('M' - чоловіча, 'F' - жіноча) актора. Вивести спектаклі, в яких грають тільки чоловіки. Запит буде наступним: SELECT pl.* FROM PLAY pl WHERE NOT EXISTS (SELECT 1 FROM TEAM te JOIN ACTOR ac ON ac.Actor_ID=te.Actor_ID WHERE te.Play_ID=pl.Play_ID AND ac.Sex='F') Предикат NOT EXISTS приймає підзапит, як аргумент і оцінює його, як прийнятним, якщо значення sex для одного або більше акторів в таблиці ACTOR нерівні F. 24 Приклад 4. Визначити найпопулярніший жанр театру. Пишемо запит з використанням кванторної функції ALL: SELECT Genre FROM PLAY GROUP BY Genre HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM PLAY GROUP BY Genre) Кванторная функція ALL перевіряє значення кількості жанрів серед усіх вистав і потім знаходить жанр з більшою кількістю, ніж у будь-якого іншого жанру, або рівним йому. Корелюючі та некорелюючі підзапити Підзапит, що повертає результат або результати, для отримання яких значення вказаного стовпця НЕ повинні співвідноситися (корелюватися) зі значеннями стовпців, зазначених в основному запиті, називається некорелюючим. Результат виконання некорелюючого запиту не залежить від значень, що повертаються основним запитом. Зазвичай некорелюючі запити застосовуються в запитах, в яких значення певного стовпця порівнюється зі значенням, що повертається підзапитом, в запитах з предикатом IN, кванторними функціями ALL та ANY. Однак, вже в запитах з предикатом EXISTS застосовуються корелюючі підзапити. Підзапит, що повертає результат або результати, для отримання яких значення вказаного стовпця повинні співвідноситися (корелюватися) зі значеннями стовпців, зазначених в основному запиті, називається корелючим. Іншими словами, результат, виконання підзапиту залежить від значень, що повертаються основним запитом. Часто корелюючі підзапити застосовуються для отримання значень одного з стовпців результуючої таблиці і в цих випадках підзапит, вкладений у дужки, перераховується через кому разом з іменами стовпців з таблиць або зі з'єднання таблиць. Приклад 1. Нехай, нам необхідно вивести список акторів з кількістю їх ролей. Для цього напишемо наступний запит з корелючим підзапитом: SELECT DISTINCT a.Actor_ID, a.FName, a.LName, (SELECT COUNT(*) FROM ACTOR a1 JOIN team t1 ON a1.Actor_ID=t1.ACTOR_ID WHERE a1.Actor_ID=a.Actor_ID GROUP BY a1.Actor_ID) AS NumRoles FROM ACTOR a JOIN team t ON a.Actor_ID=t.ACTOR_ID ORDER BY a.Actor_ID 25 В основному запиті відбувається перше звернення до таблиці ACTOR, яка отримує псевдонім `a`. У підзапиті відбувається друге звернення до таблиці ACTOR, яка отримує псевдонім `a1`. При цьому в секції WHERE підзапиту зазначена умова: ідентифікатори акторів, які повертаються основним запитом і підзапитом, повинні збігатися. Ця умова - характерна ознака корелюючого підзапиту.

9. Реляційна модель даних.

Реляційна модель даних. --Недоліки ієрархічної та мережної моделей привели до появи нової, реляційної моделі даних, створеної Е. Ф. Коддом в 1970-1971 роках і яка викликала загальний інтерес. Реляційна модель була спробою спростити структуру бази даних. У ній були відсутні явні посилання на предків і нащадків, а всі дані були представлені у вигляді простих таблиць, розбитих на рядки й стовпці. Перед тим як перейти до більш докладного вивчення цієї моделі, введемо деякі поняття. Одна з найбільш складних проблем організації бази даних пов'язана з тим, що архітектори БД, розробники та кінцеві користувачі, як правило, розглядають дані та їх призначення по-різному. Проект, який буде розроблений, відповідатиме всім вимогам замовників тільки за тієї умови, якщо і архітектори, і замовники прийдуть до єдиного розуміння. Для цього необхідно використовувати загальну модель, яка не ускладнена технічними подробицями і не допускає подвійних тлумачень. Одним із прикладів моделі такого типу є модель «Сутність-зв'язок» (Entity-Relationship model, або ER-модель). У ER-моделюванні використовують спадний підхід до проектування бази даних, який починається з виявлення 1 2 3 2 1 4 5 5 5 6 3 4 6 рівносильно 5 найбільш важливих даних, вони називаються сутностями (entities) - це те, про що необхідно зберігати інформацію, і зв'язків (relationships) між даними, які мають бути представлені в моделі. Потім у модель вносяться додаткові відомості, наприклад, вказується інформація про сутність і зв'язок, які називають атрибутами (attributes), а також всі обмеження, пов'язані з сутностями, зв'язками і атрибутами. ER-моделювання - це важливий метод, яким має володіти будь-який проектувальник бази даних. Звернемося до прикладу фірми «Sun», яка займається випуском мікропроцесорів і створимо для неї ER-модель. Крім основних сутностей (у нашому прикладі це постачальники, комплектуючі і т. д.), існують ще й зв'язки між ними, які об'єднують ці основні сутності. На рис. 2.4 зв'язки подані ромбами з сполучними лініями. Наприклад, між постачальниками і комплектуючими існує зв'язок ПК (постачальники комплектуючих): - кожен постачальник поставляє певні комплектуючі, і, навпаки; - кожна комплектуюча поставляється певними постачальниками. (точніше, кожен постачальник поставляє певні види комплектуючих і кожен вид комплектуючих поставляється певними постачальниками.) Аналогічно комплектуючі використовуються в проектах, для реалізації проектів потрібні комплектуючі (зв'язок КПр); комплектуючі зберігаються на складах, а склади зберігають комплектуючі (зв'язок СК) і т. д. Зауважимо, що ці зв'язки двосторонні, тобто їх можна розглядати в обох напрямках. Зокрема, використовуючи зв'язок ПК між постачальниками і комплектуючими, можна відповісти на такі питання: - заданий постачальник - можна визначити комплектуючі, що ним поставляються; - задана комплектуюча - можна знайти постачальників, які поставляють таку деталь. Важливим є те, що цей зв'язок (як і інші зв'язки, представлені на рис. 2.4) є такою ж частиною даних підприємства, як і основні сутності. Тому зв'язки мають бути представлені в базі даних нарівні з основними сутностями предметної області. Хоча більшість зв'язків на цій діаграмі пов'язує два типи сутностей (тобто вони є бінарними), це зовсім не означає, що всі зв'язки мають бути бінарними. У прикладі є один зв'язок (ПКПр), що зв'язує три типи сутностей (постачальник, комплектуючі, проекти). Це приклад тернарного (потрійного) зв'язку. Інтерпретація цього зв'язку така: певні постачальники поставляють певні комплектуючі для певних проектів. Звернімо увагу, що в загальному випадку такий тернарний зв'язок не є еквівалентним простій комбінації з трьох бінарних зв'язків: «постачальники поставляють комплектуючі», «комплектуючі використовуються у проектах» і «проекти забезпечуються постачальниками». Зокрема, наведене нижче твердження а) говорить нам більше, ніж наступні за ним три твердження: а) «Джон» постачає модуль керування пам'яттю для проекту «Процесор»; б) «Джон» постачає модуль керування пам'яттю; в) модуль керування пам'яттю використовуються для проекту «Процесор»; г) проект «Процесор» забезпечується «Джоном». Знаючи тільки ствердження б), в) і г), ми не можемо довести справедливість твердження, а). Точніше, знаючи твердження б), в) і г), ми можемо лише зробити висновок, що «Джон» постачає модуль керування пам'яттю для певного проекту (скажімо, проекту ПРz). Певний постачальник (скажімо, постачальник Пx) постачає модуль керування пам'яттю для проекту «Процесор» і що «Джон» постачає певну комплектуючу (скажімо, деталь DY) для проекту «Процесор». Проте ми не можемо точно стверджувати, що постачальник Пx - це «Джон», деталь DY - модуль керування пам'яттю, а проект ПРz - це проект «Процесор». Такі хибні висновки називаються пасткою з'єднання (connection trap). На схемі також є один зв'язок (КК), який пов'язує один тип сутності (комплектуючі) з самим собою. Цей зв'язок означає, що одні комплектуючі містять інші комплектуючі як власні компоненти (так званий зв'язок 7 специфікації матеріалів). Наприклад, конденсатор - це компонент зворотного зв'язку, який теж розглядається як комплектуюча деталь для процесору і, в свою чергу, він може бути компонентом будь-якої більш складної деталі, наприклад блоку живлення. Як видно, цей зв'язок також бінарний; просто він пов'язує дві сутності співпадаючого типу (в даному випадку - сутність комплектуючі). Для заданого набору типів сутностей може існувати будь-яка кількість зв'язків. На рис. 2.3 присутні два різні зв'язки між сутностями Службовці і Проекти: перша (ПрСлУч) представляє той факт, що службовці зайняті в проектах, а друга (ПрСлК) - що службовці керують проектами. Це приклад рекурсивного зв'язку. Як було сказано, сутність - це те, про що необхідно записувати інформацію. Звідси випливає, що сутність (а отже, і зв'язки) мають деякі властивості (properties), тобто дані про них, які ми хочемо записати. Наприклад, у постачальників є місце розташування, у деталей - номінал, у проектів - порядок черговості запуску і т. д. Саме ці властивості мають зберігатися в базі даних. У загальному випадку властивості можуть бути як простими, так і складними, причому настільки, наскільки це потрібно. Наприклад, властивість «місце розташування постачальника» відносно проста, вона складається лише з назви міста і може бути описана, як простий символьний рядок. На противагу цьому сутність «склад» може мати властивість «схема поверхів» з досить складною структурою, що включає архітектурний план будівлі, доповнений відповідним текстовим описом. Реляційні системи засновані на формальній теорії, яку називають реляційна модель даних, яка передбачає наступне: - Дані представлені за допомогою рядків у таблицях, і ці рядки можуть бути безпосередньо інтерпретовані, як істинні висловлювання. - Для обробки рядків даних надаються оператори, які безпосередньо підтримують процес логічного отримання додаткових справжніх висловлювань з існуючих висловлювань.

52.Робота з таблицями бази даних.

Робота з таблицями баз даних. -Наприклад, створимо функцію numcatalogs(), яка підраховує кількість записів у таблиці CATALOGS навчальної бази даних "shop" : CREATE PROCEDURE numcatalogs (OUT total INT) BEGIN SELECT COUNT(*) INTO total FROM CATALOGS; END // Оператор SELECT ... INTO ... FROM дозволяє оперувати відразу декількома стовпцями, як у наступному прикладі: SELECT id, data INTO x, у FROM test LIMIT 1; Приклад виклику новоствореної процедури: CALL numcatalogs(@a); SELECT @a; СКБД підтримує контекст виклику процедури для бази даних за замовчуванням. Це означає, що якщо процедура, створена в базі даних "shop", буде викликана в той момент, коли поточною базою даних є база "test" - СУБД MySQL поверне помилку: 1305: PROCEDURE test.numcatalogs does not exist Процедура успадковує базу даних за замовчуванням від викликаючого оператора тому при зверненні до таблиць інших баз даних необхідно використовувати розширені імена. --Важливо. Використання оператора USE в збережених процедурах заборонено. Наприклад, створимо процедуру catalogname (), яка буде повертати по первинному ключу id_catalog назву каталогу nаmе. Для цього буде потрібно визначити параметр id_catalog з атрибутом IN, a name з атрибутом OUT. CREATE PROCEDURE catalogname (IN id INT, OUT catalog TINYTEXT) BEGIN SELECT name INTO catalog FROM CATALOGS WHERE id_catalog = id; END // SET @id := 5// CALL catalogname(@id, @name)// SELECT @id, @name// CALL catalogname(1, @name)// SELECT @name// Прикладом функції, що використовує параметр типу INOUT, може стати функція count_by_id (), яка, приймаючи в якості параметра первинний ключ каталогу з таблиці CATALOGS, повертає число товарних позицій у даному каталозі: CREATE PROCEDURE count_by_id (INOUT id INT) BEGIN SELECT COUNT(*) INTO id FROM CATALOGS WHERE id_catalog = id; END // SET @id = 3// CALL count_by_id(@id)// SELECT @td// Однак застосування INOUT-параметрів слід всіляко уникати, оскільки при їх використанні виникає бажання порушити правила хорошого стилю програмування, як у прикладі, де змінна @id спочатку містила первинний ключ каталогу, а після виклику містить вже число товарних позицій у даному каталозі. Краще взагалі відмовитися від використання INOUT-змінних навіть у тому випадку, коли функція приймає і повертає однотипні дані (наприклад, первинний ключ), краще створити два параметри: один для вхідного значення, інший - для вихідного: CREATE PROCEDURE numcatalogsview () BEGIN SELECT COUNT(*) FROM CATALOGS; END // CALL numcatalogsview()// COUNT(*) Створимо функцію catalog_by_product (), яка по імені товарної позиції повертає ім'я каталогу, до якого відноситься ця товарна позиція. У ході створення нової функції будемо спиратися на раніше створену функцію catalogname (), яка повертає ім'я каталогу по його первинному ключу id_catalog (приклад вище). CREATE PROCEDURE catalog_by_product (IN product TINYTEXT, OUT catalog TINYTEXT) BEGIN DECLARE id INT; SELECT id_catalog INTO id FROM PRODUCTS WHERE name = product LIMIT 1; CALL catalogname(id, catalog); END // CALL catalog_by_product (' Celeron 1.8', @catalogname) // SELECT @catalogname// Як видно з прикладу, функція catalog_by_product () має два параметри: • product - вхідний параметр, через який передається назва товарної позиції; • catalog - вихідний параметр, через який можна отримати результат роботи функції - назву каталогу, в який входить товарна позиція. -За назвою товарної позиції product при допомозі SELECT-запиту визначається первинний ключ каталогу. Отримане значення поміщається в тимчасову змінну id, яка передається через перший аргумент функції catalogname(), яка повертає назву каталогу і розміщує результат роботи в змінну catalog, що є вихідним параметром функції catalog_by_product(). • У функції catalog_by_product() потрібна тимчасова змінна id. • Для використання будь-якої змінної у функції потрібно її оголошення при допомозі оператора DECLARE, який має наступний синтаксис: DECLARE var_name[,...] type [DEFAULT value] Один оператор DECLARE дозволяє оголосити відразу декілька змінних одного типу, причому необов'язкове слово DEFAULT дозволяє призначити значення: CREATE PROCEDURE declare_var () BEGIN DECLARE id, num INT(11) DEFAULT 0; DECLARE name, hello, temp TINYTEXT; END // Оператор DECLARE може знаходитися тільки всередині блоку BEGIN ... END, область видимості оголошеної змінної також обмежена цим блоком. Це означає, що в різних блоках BEGIN ... END можуть бути оголошені змінні з однаковими іменами і працювати вони будуть тільки в рамках даного блоку, не перетинаючись зі змінними інших блоків. CREATE PROCEDURE declare_var () outer: BEGIN DECLARE var TINYTEXT DEFAULT 'зовнішня змінна'; inner: BEGIN DECLARE var TINYTEXT DEFAULT 'внутрішня змінна'; SELECT var; END inner; SELECT var; END outer // CALL declare_var()// Змінна, оголошена в зовнішньому блоці BEGIN ... END, буде доступна у вкладеному блоці, якщо не буде оголошено екрануючої її змінної (приклад нижче), наприклад: CREATE PROCEDURE one_declare_var () BEGIN DECLARE var TINYTEXT DEFAULT 'зовнішня змінна'; BEGIN SELECT var; END; SELECT var; END // CALL one_declare_var()// Зворотне не вірно, змінна, що оголошена у вкладеному блоці, недоступна в зовнішньому, наприклад: CREATE PROCEDURE inner_declare_var () BEGIN BEGIN DECLARE var TINYTEXT DEFAULT ' внутрішня змінна '; SELECT var; END; SELECT var; END // CALL inner_declare_var()// Слід зазначити, що не допускається і повторне оголошення змінної в рамках одного блоку BEGIN ... END (приклад). Це призводить до виникнення помилки 1331: "Повторне оголошення змінної". CREATE PROCEDURE dbl_declare_var () BEGIN DECLARE var TINYTEXT DEFAULT 'зовнішня змінна'; DECLARE var TINYTEXT DEFAULT 'внутрішня змінна' SELECT var; END // ERROR 1331 (42000): Duplicate variable: var

4. Основні поняття і архітектура. Ролі баз даних. Адміністрування баз даних.

Ролі баз даних. Адміністрування баз даних --Архітектори даних відповідають за загальну макроструктуру систем баз даних та інтерфейси, які вони надають програмам і командам розробників. А також за основні технології та інфраструктуру, необхідну для задоволення потреб організації в даних. Вони відповідають за реалізацію рішень БД, досліджують варіанти, приймають рішення щодо технологій, інтегрують їх з існуючими системами та розробляють комплексну стратегію даних для організації --Адміністратори баз даних (Data Base Administrator) Адміністратори баз даних (DBA) - це особи, які відповідають за безперебійну роботу систем баз даних. Вони відповідають за планування нових систем даних, встановлення та налаштування програмного забезпечення, налаштування систем баз даних для інших сторін та керування продуктивністю. --Розробники додатків (Application developers) Розробники керують структурами даних, пов'язаними з додатками, щоб зберігати дані на диску. Вони повинні створити або використати механізми, які можуть відображати їх програмні дані в системі БД, щоб компоненти могли працювати разом. При зміні додатків розробники повинні підтримувати синхронізацію даних і структур даних в системі БД --SR-інженери та фахівці з експлуатації SR-інженери з надійності сайтів, SREs (Site Reliability Engineers) та спеціалісти з експлуатації (Operations Professionals) взаємодіють із системами БД з точки зору інфраструктури та конфігурації додатків. Можуть нести відповідальність за забезпечення додаткової потужності, підтримки систем БД, забезпечення відповідності конфігурації БД організаційним вимогам, моніторинг часу безвідмовної роботи та управління резервними копіями --Бізнес-аналітики та дата-аналітики Працюють над розробкою аналітичних даних на основі тенденцій та закономірностей у межах даних. Прогнозують майбутні результати, консультувати організацію щодо потенційних змін та відповісти на питання щодо даних для інших підрозділів, таких як маркетинг та продажі. Часто працюють з «репліками» або копіями БД, щоб вони могли виконувати тривалі агреговані запити продуктивності.

21.Проектування схем баз даних. Різні типи та кратність зв'язків.

Різні типи та кратність зв'язків. --Клас сутностей - це позбавлений методів клас об'єктів в сенсі ООП. При переході до фізичного рівня класи сутностей перетворюються в базові відношення реляційних БД для конкретних СКБД. У них, як і власне у базових відносинах, існують власні атрибути. --Класом називається іменований опис сукупності об'єктів із загальними атрибутами, операціями, зв'язками і семантикою. Графічно зазвичай клас зображується у вигляді прямокутника. У кожного класу має бути ім'я (текстовий рядок), яке є унікальним та відрізняє його від всіх інших класів. -Атрибутом класу називається іменована властивість класу, що описує безліч значень, які можуть приймати екземпляри цієї властивості. Клас може мати будь-яке число атрибутів (зокрема, не мати жодного атрибута). Атрибут є абстракцією стану об'єкта. Будь-який атрибут довільного об'єкта класу повинен мати деяке значення. -Так звані зв'язки реалізуються за допомогою оголошення зовнішніх ключів (подібні явища нам вже зустрічалися раніше), тобто у відношеннях оголошуються зовнішні ключі, які посилаються на первинні ключі якихось інших відношень. Зв'язок між відносинами при проектуванні схем баз даних зображується у вигляді ліній, що з'єднують класи сутностей. При цьому кожен з кінців зв'язку може (і взагалі повинен) характеризуватися найменуванням (тобто типом зв'язку) і кратністю ролі класу в зв'язку. -Кратністю (multiplicity) називається характеристика, яка вказує, скільки атрибутів класу сутності з цією роллю може або повинно брати участь в кожному екземплярі зв'язку будь-якого виду. Найбільш поширеним способом задання кратності ролі зв'язку є пряме позначення конкретного числа або діапазону. -Наприклад, позначення «1» говорить про те, що кожен клас з цією роллю повинен брати участь в деякому екземплярі даного зв'язку, причому в кожному екземплярі зв'язку може брати участь рівно один об'єкт класу з цією роллю. Вказування діапазону «0..1» говорить про те, що не всі об'єкти класу з цією роллю зобов'язані брати участь в будь-якому екземплярі даного зв'язку, але в кожному екземплярі зв'язку може брати участь тільки один об'єкт. -Типовими, найпоширенішими кратностями в системах проектування баз даних є наступні кратності: • 1 - кратність зв'язку на відповідному його кінці дорівнює одиниці; • 0 ... 1 - така форма запису означає, що кратність даного зв'язку на відповідному своєму кінці не може перевищувати одиниці; • 0 ...? - така кратність розшифровується просто «багато». Цікаво, що, як правило, «багато» означає «нічого»; • 1 ...? - таке позначення отримала кратність «один або більше». -Згідно цієї діаграми, можна зрозуміти, що кожен лікар має багато пацієнтів, а кожен пацієнт відноситься до якогось одного (і не більше того) лікаря. -Тепер розглянемо найбільш поширені типи або найменування зв'язків. Перерахуємо їх: • 1: 1 - таке позначення отримала зв'язок «один до одного», тобто це ніби взаємнооднозначна відповідність двох множин; • 1: 0 ...? - це позначення зв'язку типу «один до багатьох». Для стислості такий зв'язок називають «1: М». У розглянутої раніше діаграмі, як можна помітити, присутній зв'язок саме з таким найменуванням; • 0 ...? : 1 - це відношення попереднього зв'язку або зв'язку типу «багато до одного»; -• 0 ...? : 0 ...? - це позначення зв'язку типу «багато до багатьох», тобто з кожного кінця зв'язку є багато атрибутів; • 0 ... 1: 0 ... 1 - це зв'язок, аналогічний введеному раніше зв'язку типу «один до одного», він, у свою чергу, називається «не більше одного до не більше одному»; • 0 ... 1: 0 ...? - це зв'язок, аналогічний зв'язку типу «один до багатьох», він називається «не більше одного до багатьох»; • 0 ...? : 0 ... 1 - це зв'язок, в свою чергу, аналогічна зв'язку типу «багато до одного», вона називається «багато до не більше одного».

49.Створення збереженої процедури.

Створення збереженої процедури. -За замовчуванням процедура пов'язана з базою даних, що використовується на даний момент часу. Щоб зв'язати процедуру з конкретною базою даних, необхідно вказати її назву при створенні збереженої процедури: і'мя_бази_даних. ім'я_збереженої_процедури. Повний синтаксис: CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE ім'я_процедури ([параметри_процедури[,...]]) [характеристики ...] тіло_підпрограми Параметри процедури: [ IN | OUT | INOUT ] Ім'я параметра type type: Будь який валідний тип даних MySQL Характеристики: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } Тіло підпрограми: Валідний оператор програми SQL Для того, щоб створити новий рядок в цій таблиці використовуємо наступний запит: INSERT INTO ADS (Id, Category, Part, Units, Money) VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620) Або без вказання імен стовпців: INSERT INTO ADS VALUES (13, 'Недвижимость', 'Гаражи', 22, 4620) Ми з вами будемо використовувати простіший синтаксис: CREATE PROCEDURE sp_name ([parameter [,...]]) [characteristic ...] routine_body CREATE FUNCTION sp_name ([parameter[,...]]) RETURNS type [characteristic ...] routine_body Тут sp_name ім'я збереженої процедури, в дужках, що наступних за ім'ям, передається необов'язковий список параметрів, перерахованих через кому. Кожен параметр parameter дозволяє передати в процедуру, або з неї, вхідні дані або результат роботи процедури і має наступний синтаксис: [ IN | OUT | INOUT ] param_name type Параметру param_name передує одне з ключових слів IN, OUT, INOUT, які дозволяють задати напрямок передачі даних: • IN - дані передаються строго всередину збереженої процедури, але якщо параметру з даним модифікатором всередині функції присвоюється нове значення, після виходу з неї він не зберігається і параметр приймає значення, яке він мав до виклику процедури; • OUT - дані передаються строго із збереженої процедури, навіть якщо параметр має якесь початкове значення, всередині збереженої процедури це значення не береться до уваги. З іншого боку, якщо параметр змінюється всередині процедури, після виклику процедури параметр має значення, присвоєне йому всередині процедури; • INOUT - значення цього параметра приймається до уваги всередині процедури, так і зберігає своє значення після виходу з неї. Важливо. Список аргументів, вкладених у круглі дужки, повинен бути присутнім завжди. Якщо аргументи відсутні, слід використовувати порожній список аргументів (). Оператор CREATE FUNCTION дозволяє задати функцію користувача, тобто такий вид процедури, який повертає єдине значення. Тип цього значення дозволяє задати оператор RETURN. Характеристика characteristic може приймати одне з наступних значень або їх комбінацію: LANGUAGE SQL | [NOT] DETERMINISTIC | SQL SECURITY {DEFINER | INVOKER} | COMMENT 'string'

58.Тригери. Створення тригерів

Створення тригерів -Для створення тригера необхідно бути власником таблиці, для якої тригер створюється, або мати роль db_owner або db_ddladmin, або ж бути адміністратором SQLсервера, тобто входити в фіксовану роль сервера sysadmins. При додаванні тригера до таблиці змінюється тип доступу, відношення до неї інших об'єктів і т. д. Створення DML-тригера: CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH [ ENCRYPTION ] [ ] [ ,...n ] ] { FOR | AFTER | INSTEAD OF } {[INSERT] [,] [UPDATE] [,] [DELETE]} [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [;] [...n] | EXTERNAL NAME } Створення DDL-тригера: CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ENCRYPTION ] [ ] [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [;] }

14.Модель «Сутність-зв'язок». Типи сутностей.

Типи зв'язків між відношеннями Розрізняють три основних типи зв'язків між таблицями в реляційній моделі даних: - один-до-одного (1:1) - кожному кортежу одного відношення відповідає тільки один кортеж іншого відношення; Рис.3.4 - тип зв'язку один-до-одного. - один-до-багатьох (1:М) - одному кортежу головного відношення відповідає декілька кортежів підлеглого відношення (Рис 3.5); 9 Рис. 3.5 - тип зв'язку один-до-багатьох/ - багато-до-багатьох(М:М) - одному кортежу одного відношення відповідає множина кортежів іншого відношення і навпаки (Рис. 3.6). Рис. 3.6 - тип зв'язку багато-до-багатьох. Зв'язок один-до-одного зустрічається на багато рідше зв'язку один-добагатьох, її використовують, якщо не хочуть щоб основна таблиця «розпухала» від другорядної інформації. Крім того, вважається, що БД до складу яких входять такі зв'язки не можуть вважатися повністю нормалізованими. Тип зв'язку один-до-багатьох є найпоширенішим у реляційних БД, він дозволяє моделювати ієрархічні структури даних. 10 Зв'язок багато-до-багатьох зустрічається досить часто, однак реляційні СКБД не підтримують цей тип зв'язку на рівні індексів і цілісності, але дозволяють реалізовувати його неявно. Вважається, що БД завжди можна перебудувати так, щоб будь-який зв'язок багато-до-багатьох, був перетворений в один і більше зв'язків один-до-багатьох (Рис. 3.7). Рис. 5.4 - перетворення зв'язку багато-до-багатьох. Значення проміжної таблиці показують, яка книжка, якого автора. Умови цілісності даних Щоб інформація, що зберігається в БД була однозначною і несуперечливою і зберігала свою інформативність, в реляційній моделі встановлюються деякі обмежувальні умови. Обмежувальні умови - це правила, які визначають можливі значення даних. Вони забезпечують логічну основу для підтримки коректності значень даних і дозволяють звести до мінімуму помилки, що виникають при оновленні та обробці. Найважливішими обмеженнями цілісності даних є: - категорійна цілісність; - посилальна цілісність. Обмеження категорійної цілісності полягає в наступному. Кортежі відношення представляють в БД елементи певних об'єктів реального світу, відповідно до термінології реляційних СКБД, категорій. Первинний ключ таблиці повинен однозначно визначати кожен кортеж і кожен елемент категорії. Порушення категорійної цілісності виникає при спробі внести до відношення 11 кортежі з невизначеним значенням первинного ключа, тобто можна сказати невідомою категорії. Тому кортеж не може бути занесений в БД до тих пір, поки не будуть визначені всі атрибути його первинного ключа. Обмеження, що накладаються на зовнішні ключі для забезпечення цілісності даних, називаються посилальною цілісністю. Іншими словами, якщо дві таблиці пов'язані між собою, то зовнішній ключ дочірньої таблиці повинен містити лише ті значення, які вже є серед значень первинного ключа батьківського таблиці, в іншому випадку буде порушено умову посилальної цілісності даних. Для дотримання посилальної цілісності при вставці нових кортежів або модифікації значень зовнішнього ключа в існуючих кортежах, необхідно стежити за коректністю значень зовнішнього ключа. Зазвичай СКБД стежить за унікальністю значень первинного ключа і коректністю значень зовнішніх ключів, отже, за категорійною і посилальною цілісністю. Правила збереження посилальної цілісності можуть вибиратися розробником в залежності від предметної області, розроблюваної БД і можливостей СКБД. При видаленні кортежів з відношень, на які є посилання, можна використовувати один з трьох варіантів, кожен з яких підтримує посилальну цілісність: - забороняти видалення кортежу, на який існує посилання в підлеглому відношенні (в цьому випадку для видалення кортежу головного відношення, необхідно видалити всі посилаються на нього в кортежах, або змінити значення їх зовнішнього ключа); - при видаленні кортежу, на який є посилання, у всіх посиланнях кортежів значення зовнішніх ключів автоматично стане невизначеним (NULL); - (каскадне видалення) при видаленні кортежу з відношення, на який є посилання, в підпорядкованих відношеннях, автоматично видаляються всі кортежі, що посилаються на нього в підлеглих таблицях. При оновленні значень у первинному ключі кортежу головної таблиці можуть використовуватися подібні правила: - забороняти зміни значень в первинному ключі, на який існують посилання; - (каскадне оновлення) при зміні значень в первинному ключі головної таблиці, на який є посилання, автоматично відбудеться оновлення значень зовнішніх ключів кортежів які посилаються на нього.

18.Теорія нормалізації даних. Третя нормальна форма (3НФ).

Третя нормальна форма (3НФ) --Відношення знаходиться в третій нормальній формі тоді й тільки тоді, коли воно знаходиться в другій нормальній формі і не містить транзитивних залежностей між не ключовими атрибутами. • Функціональна залежність атрибутів X і Y відносини називається транзитивною, якщо існує такий атрибут Z що існують функціональні залежності X→Z і Z→Y, але відсутня функціональна Z→Х --В отриманому відношенні ВИКЛАДАЧ є наступні транзитивні функціональні залежності: • "табельний_номер"→"кафедра"→"телефон" • "табельний_номер"→"посада"→"оклад" --Наявність транзитивних функціональних залежностей викликає аномалії наступного характеру (на прикладі атрибуту "телефон"): 1. Має місце дублювання інформації про телефон для викладача однієї кафедри. 2. Існує проблема контролю надлишковості даних, оскільки зміна номеру телефону кафедри викликає необхідність пошуку і зміни всіх номерів телефонів всіх викладачів кафедри. 3. Не можна додати дані про нову кафедру (назву і номер телефону), якщо на даний момент відсутні викладачі. --Отримаємо три відношення ВИКЛАДАЧ, ПОСАДА і КАФЕДРА, які знаходяться у 3НФ. Приведення до 3НФ можна описати наступним чином. Нехай дана змінна-відношення R(A,B,C) PRIMARY KEY {A} і є функціональна залежність B→C. Процедура нормалізації в 3НФ передбачає заміну цієї змінної-відношення наступними двома проекціями R1 і R2: R1(B,C) PRIMARY KEY {B} R2(A,B) PRIMARY KEY {A} FOREIGN KEY { B } REFERENCES R1 Таким чином, концептуальна модель БД у третій нормальній формі складається з чотирьох взаємопов'язаних відношень: ВИКЛАДАЧ, ПРЕДМЕТ, ПОСАДА і КАФЕДРА

50.Тіло, ім'я та роздільник збережених процедур. Виклик та оператори збережених процедур

Тіло процедури -Тіло процедури routine_body складається з складеного оператора begin ... end, всередині якого можуть розташовуватися інші оператори, у тому числі й інші складові оператори begin ... end. Оператор має наступний синтаксис: [label:] BEGIN statements END [label] Якщо оператор починається з необов'язкової мітки label, в якості якої може виступати будь унікальне ім'я, то він може закінчуватися виразом end label. Оператор begin ... end може виглядати так, як це представлено у прикладі: BEGIN UPDATE tbll SET coll = '1234.56'; UPDATE tbl2 SET col2 = '1234.56'; END В якості одного з операторів всередині складеного оператора BEGIN ... END може виступати інший складений оператор (приклад): BEGIN UPDATE tbll SET coll = '1234.56'; inner: BEGIN UPDATE tbl2 SET col2 = '1234.56'; UPDATE tbl3 SET col3 = '1234.56'; END inner; END --Вибір роздільника При роботі з збереженими процедурами слід перевизначити роздільник запитів за допомогою параметра - DELIMITER = // консольного клієнта mysql (приклад). У цьому випадку для позначення закінчення введення замість крапки з комою необхідно буде використовувати послідовність "//": mysql -u root —delimiter=// Окрім того, можна змінювати роздільник у будь-який момент в консольному клієнті mysql. Для цього необхідно скористатися командою delimiter: DELIMITER // SELECT VERSION ()// DELIMITER ; SELECT VERSION(); --Ім'я процедури Ім'я процедури не може перевищувати 64 символи та не залежить від регістра, тобто імена numcatalogs(), Numcatalogs() і NUMCATALOGS () є еквівалентними. CREATE PROCEDURE ray_version() BEGIN SELECT VERSION(); END // --Виклик збереженої процедури Для того щоб викликати збережену процедуру, необхідно застосувати оператор CALL, після якого міститься ім'я процедури та її параметри в круглих дужках: CALL my_version (); Важливо. При виклику процедури, на відміну від вбудованих функцій, між ім'ям функції і круглими дужками допускається пробіл. При іменуванні функцій слід уникати назв, що збігаються з іменами внутрішніх функцій MySQL, приклад: CREATE PROCEDURE pi () BEGIN SELECT VERSION(); END // У даному прикладі оголошується процедура pi (), що виводить поточну версію сервера MySQL. --Оператори збер. процедур Які оператори припустимі в тілі процедур? Будь-які, включаючи: • INSERT; • UPDATE; • DELETE; • SELECT; • DROP; • REPLACE та ін. Приклад: CREATE PROCEDURE р () DELETE FROM t; // CREATE PROCEDURE p () SET @x = 5; // CREATE PROCEDURE p () DROP TABLE t; // CREATE PROCEDURE p () SELECT 'A' ; // У тілі процедури можна використовувати багаторядковий коментар у стилі мови С, який починається з послідовності "/ *" і закінчується послідовністю "* /", приклад: CREATE PROCEDURE my_version () BEGIN /* Багаторядковий коментар всередині функції my_version() */ SELECT VERSION(); /* Виклик єдиного оперетора */ END //

38.Умови вибірки. ORDER BY. AS.

Умови вибірки. ORDER BY. AS -Команда ORDER BY дозволяє сортувати записи за певним полем при виборі з бази даних. Синтаксис: Сортування по одному полю: SELECT * FROM ім'я_таблиці WHERE умова ORDER BY поле_для_сортування -Можна сортувати не по одному, а по багатьом полях відразу: SELECT * FROM ім'я_таблиці WHERE умова ORDER BY поле1, поле2... За замовчуванням записи сортуються по зростанню, щоб впорядкувати за спаданням - напишіть DESC: SELECT * FROM ім'я_таблиці WHERE умова ORDER BY поле DESC -За замовчуванням буде сортування, ніби поставлено ASC: SELECT * FROM ім'я_таблиці WHERE умова ORDER BY поле ASC Умова WHERE не обов'язкова - якщо її не поставити, будуть вибрані всі записи: SELECT * FROM ім'я_таблиці ORDER BY поле -Так як вибираються всі записи, то блок WHERE можна не вказувати: SELECT * FROM workers ORDER BY age Можна також вказати тип сортування в явному вигляді - ASC - результат від цього не зміниться: SELECT * FROM workers ORDER BY age ASC -Приклад 3. Відсортувати записи одночасно, як по зростанню віку так і за спаданням зарплати. При цьому спочатку записи сортуються за віком, а ті записи, в яких вік однаковий, розташувати за спаданням зарплати: SELECT * FROM workers WHERE id>0 ORDER BY age ASC, salary DESC -Команда AS Команда AS задає нове ім'я полям або таблицям при вибірці з бази (тобто в самій базі зміни не відбуваються). Синтаксис: • Для полів: SELECT поле as його_нове_ім'я FROM ім'я_таблиці • Для таблиць: SELECT * FROM ім'я_таблиці as новое_ім'я_таблиці -Приклад 1. При вибірці перейменувати усі поля: SELECT id as user_id, name as user_name, age as user_age, salary as user_salary FROM workers

31.Вступ до SQL. Функції. Синтаксис

Функції SQL: організація даних - SQL дає користувачу можливість визначити структуру представлення даних, а також встановлювати відношення між елементами бази даних; вибірка даних - SQL дає можливість користувачу чи прикладній програмі отримати із бази даних інформацію, яка в ній міститься; опрацювання даних - SQL дає можливість користувачу чи прикладній програмі змінювати базу даних, тобто додавати нові дані, оновлювати чи вилучати існуючі; 2 управління доступом - за допомогою SQL можна обмежувати можливості користувача щодо вибірки і зміни даних та захистити дані від несанкціонованого доступу; спільне використання даних - SQL координує спільне використання даних користувачами, які працюють паралельно так, щоб вони не заважали один одному; цілісність даних - SQL дозволяє забезпечити цілісність бази даних, захищаючи її від руйнації через неузгоджені зміни чи відмови системи ------- Синтаксис SQL Коментар. Будь-який коментар - це необов'язковий текст, який друкується в окремому рядку програми, щоб пояснити цю програму. Коментар повинен починатися з двох дефісів. Коли СУБД знаходить їх, вона ігнорує те, що стоїть за ними, тобто сам коментар. Коментарі займають цілий рядок. -- Приклад однорядкового коментаря /* Коментар на декілька рядків */ 3 Команда SQL. Будь-яка команда SQL - це допустима комбінація лексем, якій передує ключове слово. Лексемами є ключові слова, ідентифікатори, оператори, літерали і інші символи. Речення. Будь-яка команда SQL включає не менш одну пропозицію. В найзагальнішому випадку всяка пропозиція SQL - це фрагмент команди SQL, який починається з якого-небудь ключового слова, є обов'язковим або необов'язковим і повинен бути записаний в певному порядку. В даному прикладі ми маємо чотири пропозиції, а саме: SELECT, FROM, WHERE, ОRDER. Ключові слова. Довільне ключове слово, іноді зване зарезервованим, - це таке слово, яке в мові SQL має певне значення і застосування якого в SQL строго регламентовано. Слід мати на увазі, що використовування будь-якого ключового слова зовні контексту (наприклад, як ідентифікатора) буде вважатися помилкою. Ідентифікатори. Довільний ідентифікатор - це таке слово, яке розробник бази даних застосовує для того, щоб іменувати об'єкти довільної бази даних, у тому числі таблиці, стовпці, псевдо імена (псевдоніми) і представлення. Ідентифікатор не може бути ключовим словом, і його довжина не може перевищувати 128 знаків. Знаком в SQL може бути будь-який символ алфавіту, включаючи символи латинського алфавіту і латинські ідеограми. В нашому прикладі іменами, зокрема, є au_fname, au_lname, authors і state. Завершальна крапка з комою. Запис кожної команди SQL повинен закінчуватися крапкою з комою.

2. Основні поняття і архітектура. Функції систем керування базами даних

Функції систем керування базами даних --Забезпечення цілісності даних. Дані, що завантажені у БД, повинні мати можливість надійного вилучення без несподіваної зміни, маніпуляцій або видалення. Це вимагає надійних методів завантаження і витягнення даних, а також серіалізації і десеріалізації даних, необхідних для їх зберігання на фізичному носії. Цілісність даних стає складнішою, чим більше розподіленим є сховище даних, оскільки кожна частина системи повинна відображати поточний бажаний стан кожного елемента даних --Забезпечення продуктивності Характеристики операційної продуктивності часто відображають тип використовуваної бази даних, схему або структуру даних та саму операцію. У деяких випадках такі функції, як індексування, що створює альтернативне оптимізоване для продуктивності сховище загальнодоступних даних, може забезпечити швидкий пошук цих елементів --Забезпечення одночасного доступу Це означає, що декілька сторін повинні мати можливість працювати з БД одночасно. Записи повинні бути доступні для читання та запису будь-якій кількості користувачів одночасно, якщо в даний момент вони не заблоковані іншим користувачем. Одночасний доступ зазвичай означає, що БД повинна реалізовувати деякі інші основні функції, такі як облікові записи користувачів, система дозволів та механізми автентифікації та авторизації.