З початком вересня я повернувся після літньої перерви на Steemit і помітив тут Steemit Engagement Challenge аж Season 20 вже проходить.
Мені пощастило брати участь як викладач та ще я беру участь як учень. Вчора я ледве завершив домашнє завдання з розробки мобільних застосунків, а сьогодні ось в останній день дійшла черга до SQL. Якщо з розробкою для Android я не стикався зовсім, то з SQL кілька команд довелося задавати. Але то було дуже давно. І завдяки Steemit Engagement Challenge я можу повчитися, бо без них явсе відкладав на потім. А тут хочеться прийняти участь саме зараз коли йдуть замгання.

Отже моя домашня робота.

Вправа 1:

Для кожного з наступних тверджень перевірте кожне положення, поставивши літеру V, якщо воно правильне, або F, якщо воно неправильне.

Стовпець відповідає:

- ❌індекс 
- ✅елементарна властивість об'єкта реального світу
- ❌б'єкт реального світу

Позначте літеру V перед правильним словом, щоб завершити наступне речення:
A 𐄂𐄂𐄂𐄂𐄂𐄂 – це набір даних, пов’язаних з одним предметом (або сутністю), структурованих у форматі таблиці.

- ❌База даних
- ✅Таблиця
- ❌рядок

Які з наступних пропозицій відповідають первинному ключу?

- ✅Поле, яке не приймає дублікатів
- ✅Дозволяє ідентифікувати інформацію для її отримання
- ❌Перший запис у таблиці

У SQL, щоб забезпечити цілісність даних у дочірній таблиці після зміни значення первинного ключа батьківської таблиці, ми використовуємо пункт:

- ❌CHECK
- ✅ON UPDATE CASCADE
- ❌ON DELETE CASCADE

Обмеження цілісності для стовпця:

- ✅PRIMARY KEY
- ✅CHECK
- ✅FOREIGN KEY
- ✅REFERENCES

Які речення в SQL, розміщені після типу даних поля, забезпечують унікальність вмісту цього поля?

- ✅PRIMARY KEY
- ✅UNIQUE
- ❌NOT NULL
- ❌CONSTRAINT

У SQL команда Drop Table дозволяє:

- ❌Видалення обмежень цілісності лише для таблиці
- ✅Видалення таблиці
- ❌Видалення одного або кількох рядків із таблиці
- ❌Видалення одного або кількох стовпців із таблиці

Вправа 2

Для опису співробітників компанії та їх розподілу за різними функціями була створена наступна таблиця:

codemp	nomemp	prenemp	dnemp	niveau	codfct	intfct
E001	TOUNSI	Safa	1980-01-10	Master	20	Finance
E002	KEFI	Ali	1981-09-12	High School	10	Administrative
E003	BEJI	Mohamed	1977-04-15	High School	20	Finance
E004	TOUATI	Lamia	1965-06-21	Technician	20	Security
E005	SOUSSI	Leila	1982-11-28	Master	10	Administrative
E006	SFAXI	Lotfi	1988-06-09	Master	30	Legal

Опис колонок

Column	Description	Column	Description
codemp	Employee code	dnemp	Employee's date of birth
nomemp	Employee's last name	niveau	Recruitment level
prenemp	Employee's first name	codfct	Function code
intfct	Function title

Ця таблиця страждає від аномалій від її створення. Визначте аномалії цієї структури та поясніть їх походження.

Відповідь:

Щоб попрактикуватися в створенні SQL таблиць та запитів я завантажив
DB Browser for SQLite https://sqlitebrowser.org/

та створив таблицю з завданння

Аномалії оновлення:

Якщо нам потрібно оновити 'Recruitment level' або 'Function title' то це доведеться зробити по всій таблиці.
Виправлення займе багато часу, може призвести до помилок і невідповідностей.
А в великію БД зберігання однакового тексту сильно збільший розмір БД.
Рішення: Для цих назв треба зробити окремі таблиці.

Аномалії видалення:

Якщо ми видалимо працівника, наприклад з Function title = Security, то інформація про Security теж не буде присутня в БД.
Рішення: знов приходимо до того що для цих назв треба зробити окремі таблиці.

Аномалія додавання:

Наприклад у нас з'явилося нове значення для 'Recruitment level' або 'Function title', але співробітника з такими характеристиками ще нема. То ці значення нема де зберегти.
Рішення: знов приходимо до того що для цих назв треба зробити окремі таблиці.

Щоб виправити аномалії, ми пропонуємо наступні правила управління:

R1: Співробітник призначений лише для однієї функції, і функція може включати кількох працівників.
R2: Працівник має лише один рівень набору, а рівень набору стосується кількох працівників.

Для цього створимо три незалежні/самостійні таблиці

Щоб СУБД врахувала, що працівник може займати посаду в компанії протягом визначеного періоду та може займати ту саму посаду в різний час,

R1 стає таким: працівник може виконувати одну або кілька функцій, і для кожного призначення записуються дати початку та закінчення призначення.

Так, попередня реалізація БД ще й не могла розповідати про історію - хто коли і на якій посаді був. А відображала лиш поточний момент.

Те що ми зробили БД з чотирьох таблиць вийшло дуже зручним. Для даної реалізації не слід змінювати структуру трьох таблиць. Лише до четвертої слід додати два поля з датою. Перше обов'язкове, а друге може бути постим - коли працівник ще працює.

Ми пропонуємо додати таблицю під назвою «бонус» для запису бонусів за продуктивність, нарахованих кожному працівнику протягом року, з полями AnneePrime як роком бонусу та PrimeRend як значенням бонусу. Для цього ми повинні дотримуватись нового правила менеджменту R3: працівник може отримувати бонуси за результат протягом того самого року.

Вправа 3:

Розглянемо базу даних «GestElections», яка містить інформацію про вибори членів ради закладів середньої школи. Це описано таким спрощеним текстовим представленням:
| Electeur (NumElv, NomPrenElv, AgeElv) |
| Кандидат (NumCand, NumList#, Appelation) |
| Liste (NumList, DesList, NbrCand) |
| Виборець (NumCand#, NumList#, NumElv#) |

Column	Description	Data Type	Size	Mandatory	Table
NumElv	Number of an elector student	Auto-increment	3	Yes	Electeur
NomPrenElv	Elector student's full name	Text	20	Yes	Electeur
AgeElv	Elector student’s age [12..20]	Numeric	2	No	Electeur
NumCand	Number of a candidate student	Numeric	2	Yes	Candidat
NumList	List number	Numeric	1	Yes	Liste
Appelation	Candidate's designation	Text	20	Yes	Candidat
DesList	Unique designation of a list	Text	20	Yes	Liste
NbrCand	Number of candidates [1..6]	Numeric	2	No	Liste

Створимо БД і таблиці в них

Додайте стовпець «Datevote», що представляє поточну дату, до таблиці «Виборець».
ALTER TABLE Viborec ADD COLUMN Datevote DATE DEFAULT CURRENT_DATE;

Розширити колонку «Назва» таблиці «Кандидат» до 30 символів.
ALTER TABLE Candidat MODIFY Appelation VARCHAR(30) NOT NULL; - це можна зробити однією командою

Розділіть стовпець «NomPrenElv» таблиці «Electeur» на «LastName» та «FirstName», кожен із 15 символів (обидва поля є обов’язковими).

ALTER TABLE Electeur
ADD COLUMN LastName VARCHAR(15) NOT NULL,
ADD COLUMN FirstName VARCHAR(15) NOT NULL;

UPDATE Electeur
SET LastName = SUBSTRING_INDEX(NomPrenElv, ' ', 1),
    FirstName = SUBSTRING_INDEX(NomPrenElv, ' ', -1);
ALTER TABLE Electeur DROP COLUMN NomPrenElv;

тут ми додамо два стовбці та розіб'ємо пробілом ім'я та прізвище в них
старий стовбець з іменем слід видалити.

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

Що йому можна запропонувати?
Напишіть свою пропозицію на SQL.

Звісно не може найти результатів адже в таблиці нема відповідного поля.
його слід додати

ALTER TABLE Electeur
ADD COLUMN Gender VARCHAR(6) NOT NULL;

але його тепер слід заповнити
-- Припустимо, у вас вже є дані про стать, які потрібно вставити

UPDATE Electeur
SET Gender = 'Male'  -- або 'Female'
WHERE NumElv = ...; -- тут за якоюсь ознакою, є наприклад чоловічі та жіночі імена

Після того як дане поле у всих буде заповнене
можна зібрати статистику

SELECT Gender, COUNT(*) AS NumberOfVotes
FROM Viborec
JOIN Electeur ON Viborec.NumElv = Electeur.NumElv
GROUP BY Gender;

Перший тиждень майже минув, але можна підключитися з наступного тижня @jen0revision @mahadisalim @creeping
Мережі Х у мене нажаль немає.

Сподіваюся наступного разу завдання будуть не набагато складніші.