З початком вересня я повернувся після літньої перерви на Steemit і помітив тут Steemit Engagement Challenge аж Season 20 вже проходить.
Мені пощастило брати участь як викладач та ще я беру участь як учень. Вчора я ледве завершив домашнє завдання з розробки мобільних застосунків, а сьогодні ось в останній день дійшла черга до SQL. Якщо з розробкою для Android я не стикався зовсім, то з SQL кілька команд довелося задавати. Але то було дуже давно. І завдяки Steemit Engagement Challenge я можу повчитися, бо без них явсе відкладав на потім. А тут хочеться прийняти участь саме зараз коли йдуть замгання.
Отже моя домашня робота.
Для кожного з наступних тверджень перевірте кожне положення, поставивши літеру 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 дозволяє:
- ❌Видалення обмежень цілісності лише для таблиці
- ✅Видалення таблиці
- ❌Видалення одного або кількох рядків із таблиці
- ❌Видалення одного або кількох стовпців із таблиці
Для опису співробітників компанії та їх розподілу за різними функціями була створена наступна таблиця:
| 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: працівник може отримувати бонуси за результат протягом того самого року.
Розглянемо базу даних «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
Мережі Х у мене нажаль немає.
Сподіваюся наступного разу завдання будуть не набагато складніші.