Реляционная база данных

Разбор темы

База данных и СУБД 9–10 класс

База данных — это не просто файл с данными, а структурированное хранилище: все записи устроены одинаково, их можно быстро искать, сортировать, отбирать по условиям. Примеры: электронный журнал Kundelik, база ЕНТ с результатами, eGov, каталог интернет-магазина, база клиентов банка.

СУБД — программа, которая создаёт БД, хранит её, следит за целостностью данных и выполняет запросы пользователей. Настольные СУБД: MS Access, LibreOffice Base. Серверные: MySQL, PostgreSQL, Oracle, MS SQL Server. Встраиваемая: SQLite (в телефонах и браузерах). Язык запросов почти всех реляционных СУБД — SQL.

Основные объекты СУБД (на примере Access): таблицы (хранят данные), запросы (отбирают и обрабатывают данные), формы (удобный ввод и просмотр данных), отчёты (оформленный вывод на печать). Простую однотабличную БД можно вести и в электронной таблице: строки — записи, столбцы — поля, первая строка — имена полей (цель 9.2.2.2).

Модели данных: иерархическая, сетевая, реляционная 10 класс

Иерархическая модель — дерево: один корневой элемент, у каждого элемента ровно один «родитель» и сколько угодно «потомков». Примеры: файловая система (диск → папки → файлы), структура школы (школа → классы → ученики), классификация животных. Минус: трудно описать объект, который подчиняется сразу двум «родителям».

Сетевая модель — граф: каждый элемент может быть связан с любым числом других. Пример: связи между учениками и кружками (ученик ходит в несколько кружков, в кружке много учеников), сайты со ссылками. Гибко, но сложно в обработке.

Реляционная модель (Э. Кодд, 1970) — данные хранятся в двумерных таблицах-отношениях, связи задаются общими значениями полей (ключами). Это самая распространённая модель. Существуют и нереляционные (NoSQL) базы: документные, «ключ–значение», графовые — их используют для больших данных (Big Data).

Таблица: поля и записи 9–10 класс

Поле — столбец, описывает одно свойство объекта (имя, класс, город, балл). Запись — строка, описывает один объект целиком. На пересечении — значение (ячейка). Наша учебная таблица students имеет 5 полей (id, name, grade, city, score) и 8 записей.

Свойства реляционной таблицы: имена полей в таблице не повторяются; все значения одного поля одного типа; в ячейке одно (атомарное) значение; нет двух одинаковых записей; порядок записей и полей не важен — его задают при выводе сортировкой.

Размер таблицы задают как «число записей × число полей»: если в таблице 30 строк и 6 столбцов, в ней 30 записей и 6 полей, всего 180 значений. Добавление ученика — новая запись; добавление свойства «телефон» — новое поле.

Типы данных полей 10 класс

Для каждого поля задаётся тип данных — он определяет, какие значения можно хранить и какие операции с ними допустимы. В SQL (цель 10.3.2.1): INTEGER / INT — целое число; REAL, FLOAT, DECIMAL — дробное (деньги — DECIMAL); TEXT, VARCHAR(n) — строка до n символов, CHAR(n) — строка ровно n символов; DATE, TIME, DATETIME — дата и время; BOOLEAN — логическое (истина/ложь); BLOB — двоичные данные (картинки).

В Access типы называются: Короткий текст (до 255 символов), Длинный текст (MEMO), Числовой, Денежный, Дата/время, Логический (Да/Нет), Счётчик (автоматически увеличивающееся число — удобен для ключа), Поле объекта OLE, Вложение, Гиперссылка.

Как выбирать тип: если с полем считают — число; номер телефона, ИИН, почтовый индекс — текст (не считаем, могут начинаться с нуля и содержать «+»); дата рождения — дата; «сдал/не сдал» — логический; фамилия — текст.

Первичный и внешний ключи, индексы 10 класс

Первичный ключ (PRIMARY KEY) — поле, значения которого уникальны и не пусты, поэтому однозначно определяют запись (цель 10.3.1.3). Хорошие ключи: id-счётчик, ИИН, номер студенческого билета, номер паспорта. Плохие: фамилия, город, класс, балл — они могут повторяться.

Составной ключ — набор из нескольких полей, который уникален только вместе. В таблице enroll(student_id, course_id, mark) ни student_id, ни course_id по отдельности не уникальны, но пара (student_id, course_id) уникальна — один ученик записан на курс один раз.

Внешний ключ (FOREIGN KEY) — поле, которое ссылается на первичный ключ другой таблицы: enroll.student_id → students.id, enroll.course_id → courses.id. СУБД следит за целостностью данных: нельзя поставить оценку несуществующему ученику.

Индекс — дополнительная упорядоченная структура по полю (цель 10.3.1.2). Он ускоряет поиск и сортировку по этому полю, но занимает место и замедляет вставку. По первичному ключу индекс создаётся автоматически.

Связи между таблицами и нормализация 10 класс

Один к одному (1:1): одной записи первой таблицы соответствует не больше одной записи второй. Пример: гражданин — паспорт, ученик — личное дело.

Один ко многим (1:М) — самая частая связь: класс — ученики, учитель — курсы, страна — города. Внешний ключ ставится на стороне «многих»: в таблице учеников хранится номер класса.

Многие ко многим (М:М): ученик посещает много курсов, на курсе много учеников. Напрямую М:М в реляционной БД не хранится — создают третью таблицу-связку (у нас enroll), и связь распадается на две связи 1:М: students 1:М enroll и courses 1:М enroll.

Нормализация — разбиение данных на таблицы так, чтобы не было избыточности (повторов) и аномалий при изменении. 1НФ: в каждой ячейке одно значение, нет повторяющихся групп. 2НФ: каждое неключевое поле зависит от всего ключа. 3НФ: неключевые поля не зависят друг от друга. Признак плохой структуры: одно и то же (например, фамилия учителя) записано в десятках строк, и при смене учителя приходится исправлять всё.

Поиск, сортировка и фильтрация 9–10 класс

Сортировка — упорядочивание записей по одному или нескольким полям: по возрастанию (А→Я, 0→9, ранние даты сначала) или по убыванию. При сортировке по двум полям второе поле упорядочивает записи только внутри групп с одинаковым значением первого поля. Сортировка не меняет количество записей.

Фильтрация (отбор) — показ только записей, удовлетворяющих условию; остальные скрываются, но не удаляются. В электронной таблице это автофильтр, в СУБД — фильтр или запрос (цель 9.2.2.3).

Условия строятся из операций сравнения =, <> (не равно), >, <, >=, <= и логических операций: AND (И — должны выполняться оба условия, отбор сужается), OR (ИЛИ — достаточно одного, отбор расширяется), NOT (НЕ — отрицание). Приоритет: сначала NOT, потом AND, потом OR; скобки меняют порядок.

Пример на таблице students: grade = 11 AND score > 100 → 2 записи (Айгерим, Мадина); city = 'Алматы' OR score >= 120 → 4 записи; NOT city = 'Астана' → 5 записей. Строки сравниваются посимвольно по алфавиту: 'Дана' < 'Ерлан'.

SELECT name, score FROM students
WHERE grade = 11 AND score > 100
ORDER BY score DESC;