Работа с базами данных 1 страница

База данных – это поименованная совокупность взаимосвязанных данных, находящихся под управлением системы управления базами данных (СУБД). Различают иерархические, сетевые и реляционные СУБД. В C++ Builder используется несколько механизмов и инструментов для работы с базами данных: BDE (Borland Database Engine), ADO (ActiveX Data Object) позволяет коннектится к базам данных, используя объекты ActiveX, позволяет напрямую коннектится к базам данных поддерживающих InterBase.

5.1. Основные концепции реляционных баз данных

Реляционная база данных представляет собой одну или совокупность взаимосвязанных таблиц, состоящих из записей. С позиции реляционной алгебры БД это совокупность отношений над n множествами M1, M2,..., Mn. Например, это множества студентов, преподавателей, экзаменов. Отношение представляет собой таблицу. При этом наборы вида <m1, m2,...,mn>, где mk – элемент из Mк, называются кортежами (строками таблицы или записями). Запись состоит из атрибутов, являющихся столбцами таблицы (полями записи). Каждый столбец имеет уникальное в таблице имя, которое записывается в верхней части таблицы и является именем поля. Атрибут, который может быть использован для однозначной идентификации конкретной записи, называется первичным ключом. Для таблицы Student (табл. 9) первичным ключом может быть номер студенческого билета. Для ускорения доступа к данным по первичному ключу в СУБД имеется механизм, называемый индексированием. Индекс представляет собой древовидный список, указывающий на местоположение записи для каждого первичного ключа. Возможно индексирование отношения с использованием атрибутов, отличных от первичного ключа (вторичного ключа), например фамилии студента.

Таблица 9 (Student)

Stud_Id	StudName	Kurs	Address	Predmet	Prepod
	Котов П.А.		Витебск, Ленина 1	Prog	Романчик
	Павлов А.И.		Минск, Репина 2	Matan	Примачук
	Коваль Н.К.		Гомель,Пушкина 3	Difur	Громак

Для поддержания ссылочной целостности данных в нескольких взаимосвязанных таблицах используется механизм внешних ключей. При этом некоему атрибуту одного отношения назначается ссылка на первичный ключ другого отношения; тем самым закрепляются связи подчиненности между этими отношениями. При этом отношение, на первичный ключ которого ссылается внешний ключ другого отношения, называется master-отношением, а отношение, от которого исходит ссылка, называется detail-отношением, или подчиненным отношением. После назначения такой ссылки СУБД имеет возможность автоматически отслеживать вопросы “ненарушения“ связей между отношениями. Если делается попытка вставить в подчиненную таблицу запись, для внешнего ключа которой не существует соответствия в главной таблице, СУБД сгенерирует ошибку. Если попытаться удалить из главной таблицы запись, на первичный ключ которой имеется ссылка из подчиненной таблицы, или изменить первичный ключ, СУБД также сгенерирует ошибку. Существуют два подхода к удалению и изменению записей из главной таблицы:

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

2) Распространить всякие изменения в первичном ключе главной таблицы на подчиненную таблицу.

5.2. Проектирование баз данных

Проектирование базы данных (БД) можно представить в виде следующей последовательности шагов:

1. Разработка модели БД, которая включает в себя: идентификацию функциональной деятельности предметной области; идентификацию объектов, которые осуществляют эту деятельность; идентификацию взаимосвязей между объектами.

2. Определение атрибутов, которые уникальным образом идентифицируют каждый объект (первичный ключ). Первичный ключ гарантирует, что в таблице не будет содержаться двух одинаковых строк.

3. Устанавливание связи между объектами и проведение операции исключения избыточности данных – нормализация таблиц.

Таким образом, для проектируемой базы данных сначала определяются таблицы, поля, индексы и связи между таблицами

Существует несколько типов связей между таблицами: “один-к-одному”, “один-ко-многим”, “многие-ко-многим”. Связь “один-к-одному” представляет собой простейший вид связи данных, когда первичный ключ таблицы является внешним ключом, ссылающимся на первичный ключ другой таблицы. Связь “один-ко-многим” отражает реальную взаимосвязь в предметной области. Эта связь описывает механизм классификаторов или кодов. Например, 01.01 – математика. Связь “многие-ко-многим” в явном виде в реляционных базах данных не поддерживается.

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

На практике используется не более трех первых нормальных форм – следует учитывать время, необходимое для “соединения” таблиц при отображении на экране. После применения правил нормализации логические группы данных располагаются не более чем в одной таблице. На пересечении каждой строки и столбца таблицы всегда находится единственное значение.

Приведение к первой нормальной форме. Если поле в данной записи содержит более одного значения, такие группы данных называются повторяющимися. Первая нормальная форма не допускает наличия таких многозначных полей. Рассмотрим пример БД, содержащей таблицу Student (табл. 10; номер студенческого билета является первичным ключом). Для приведения таблицы к первой нормальной форме мы должны поле Address разбить на два поля, содержащие город и улицу, если в дальнейшем будет выполняться обработка данных по регионам.

Приведение ко второй нормальной форме. Следующий шаг в процессе нормализации состоит в удалении всех неключевых атрибутов, которые зависят только от части первичного ключа. Такие атрибуты называются частично зависимыми. Для приведения таблицы ко второй нормальной форме удалим из нее атрибуты Predmet и Prepod и создадим две таблицы (табл. 11 и 12), которые будут содержать только эти атрибуты, и они же будут составлять их первичный ключ.

Таблица 10 (Student)

Stud_Id	StudName	Kurs	City	Street
	Котов П.А.		Витебск	Ленина 1
	Павлов А.И.		Минск	Репина 2
	Коваль Н.К.		Гомель	Пушкина 3

Таблица 11 (Subject)

Subj_Id	Subj_Name	Hour
	Prog
	Matan
	Difur

Таблица 12 (Prepod)

Prepod_Id	Prepod_Name
	Романчик
	Примачук
	Громак

Кроме этого будем использовать также таблицу Exams (табл. 13).

Таблица 13 (Exams)

Exam _Id	Stud_Id	Subj_Id	Exammark	ExamDate
				6/01/06
				10/01/06
				20/01/06

Приведение к третьей нормальной форме. Третий этап процесса приведения таблиц к нормальной форме состоит в удалении всех неключевых атрибутов, которые зависят от других неключевых атрибутов или вычисляются по ним. Каждый неключевой атрибут должен быть логически связан с атрибутом, являющимся первичным ключом. Например, из таблицы можно удалить атрибут Stipend. Получим табл. 14.

Таблица 14 (Stipend)

Stud_Name	Summa
Котов П.А.
Павлов А.И.
Коваль Н.К.

Добавим таблицу, связывающую преподавателей и предметы (табл. 15).

Таблица 15 (Sub_Prepod)

Prepod_Id	Subj_Id

Разбиение информации на мелкие единицы способствует повышению надежности базы данных, но снижает ее производительность.

На последнем шаге разработки БД необходимо спланировать вопросы безопасности и конфиденциальности информации и определить права на использование и на модификацию данных.

Теперь можно приступать к созданию приложений, работающих с базами данных. Рассмотрим сначала средства для разработки таблиц.

5.3. Утилита Database Desktop

Database Desktop – это старая утилита, которая поставляется для создания таблиц и интерактивной работы с таблицами различных форматов. При этом могут использоваться таблицы для локальных баз данных типа Paradox, dBase, MSAccess а также распределенных и SQL-серверных баз данных InterBase, Oracle, Informix, Sybase.

После запуска Database Desktop выберите команду меню File|New|Table для создания новой таблицы. Появится диалоговое окно выбора типа таблицы, например Paradox, DB2, dBase, MSAccess, MSSQL. После выбора типа таблицы появляется диалоговое окно, в котором можно определить поля таблицы и их тип. Таблицы создаются в online – режиме в рабочем пространстве DataBase Desktop, связанном с каталогом, который необходимо предварительно установить.

Для таблиц Paradox можно определить поля, находящиеся в начале записи и составляющие первичный ключ. Достаточно дважды щелкнуть мышкой по этому полю. С таблицей можно связать некоторые свойства.

Validity Checks (проверка правильности) – относится к полю записи и определяет минимальное и максимальное значение поля, а также значение по умолчанию.

Table Lookup (таблица для “подсматривания”) – позволяет вводить значение в таблицу, используя уже существующее значение в другой таблице.

В табл. 16 приведены типы полей записей для БД Paradox.

Таблица 16

Alpha	Строка длиной от 1 до 255 байт
Number	Числовое поле длиной 8 байт для чисел от 10^-308 до 10^308 с 15 значащими цифрами
Money	Форматированное числовое поле для отображения денежного знака
Short	Числовое поле длиной 2 байта для целых чисел от -32768 до 32767
Long Integer	Числовое поле длиной 4 байта для целых чисел от -2147483648 до 2147483648
BCD	Числовое поле данных в формате BCD (Binary Coded Decimal). Может иметь от 0 до 32 цифр после десятичной точки
Date	Поле даты длиной 4 байта
Time	Время в миллисекундах от полуночи
Timestamp	Обобщенное поле даты длиной 8 байт. Содержит дату и время
Memo	Поле для хранения символов, суммарное число которых превышает 255
Graphic	Поле для графической информации
OLE	Содержит OLE-данные: образы, звук, видео
Logical	Содержит значения T(true) или F(false)
Autoincrement)	Содержит значение типа integer, которое автоматически увеличивается на 1, начиная с 1
Binary	Cодержит любую двоичную информацию
Bytes	Строка длиной от 1 до 255 байт

Secondary Indexes (вторичные индексы) – обеспечивают доступ к данным в порядке, отличном от задаваемого первичным ключом. Вторичные ключи могут использоваться в подчиненной таблице для указания ссылки на главную таблицу.

Referential Integrity (ссылочная целостность) – позволяет задать связи между таблицами и поддерживать эти связи на уровне ядра.

Password Security (парольная защита) – позволяет защитиь таблицу паролем.

Table Language – позволяет задать языковый драйвер.

Поля таблиц формата dBase описаны в табл. 17.

В таблицах dBase не существует первичных ключей. Однако, это обстоятельство можно преодолеть путем определения уникальных (Unique) и поддерживаемых (Maintained) индексов (Indexes).

Отметим, что использование утилиты Database Desktop является одним из способов создания таблиц. Например, таблицы можно создавать средствами конкретной СУБД.

Таблица 17

Character	Строка
Float (numeric)	Числовое поле размером от 1 до 20 байт
Number (BCD)	Числовое поле размером от 1 до 20 байт, содержащее данные в формате BCD
Date	Поле даты длиной 8 байт
Logical	Поле может содержать только значения “истина” – T, t, Y, y или ложь – F, f, N, n
Memo	Поле для хранения последовательности символов, длина которой превышает 255 байт
OLE	Поле, содержащее OLE-данные
Binary	Поле, содержащее двоичную информацию

5.4. Структурированный Язык Запросов SQL

Язык Запросов SQL – основной язык для работы с реляционнми базами данных. Состав языка SQL следующий:

Язык манипулирования данными состоит из команд: SELECT (выбрать), INSERT (вставить), UPDATE (обновить), DELETE (удалить).

Язык определения данных используется для создания и изменения структуры БД и ее составных частей – таблиц, индексов, представлений (виртуальных таблиц). Основными его командами являются: CREATE DATABASE (создать базу данных), CREATE TABLE(создать таблицу), CREATE INDEX(создать индекс), CREATE PROCEDURE (создать сохраненную процедуру), ALTER DATABASE (модифицировать базу данных), ALTER TABLE(модифицировать таблицу), ALTER INDEX(модифицировать индекс), ALTER PROCEDURE (модифицировать сохраненную процедуру), DROP DATABASE (удалить базу данных), DROP TABLE(удалить таблицу), DROP INDEX(удалить индекс), DROP PROCEDURE(удалить сохраненную процедуру).

Язык управления данными (управления доступом) состоит из двух основных команд: GRANT (дать права), REVOKE (забрать права).

5.5. Команды языка манипулирования данными

Наиболее важной командой языка манипулирования данными является команда SELECT. Формат команды SELECT в языке SQL:

SELECT поля FROM таблицы WHERE условие;

Базовыми операциями являются: выборка, проекция, соединение, объединение.

Операция выборки позволяет получить все либо часть строк таблицы.

SELECT * FROM Student; – Получить все строки таблицы Student

SELECT * FROM Student WHERE Kurs=2 – Получить подмножество строк таблицы, удовлетворяющих условию Kurs=2. Точка с запятой является стандартным признаком конца команды, который вставляется автоматически.

Операция проекции позволяет выделить подмножество столбцов таблицы.

SELECT StudName FROM Student WHERE Kurs=2; – Получить имена студентов второго курса.

Операция соединения позволяет соединять строки из более чем одной таблицы:

SELECT StudName, Exammark FROM Students, Exams WHERE Students.Stud_Id =Exams.Stud_Id – Получить список студентов и экзаменационных оценок.

Операция объединения позволяет объединять результаты отдельных запросов. Предложение UNION объединяет вывод двух или более SQL-запросов.

SELECT name FROM employee WHERE country = "Беларусь" UNION SELECT contact_nаме, FROM customer WHERE country = "Беларусь"; – Получить список работников и заказчиков, проживающих в Беларуси.

Простейшие конструкции команды SELECT. Список выбираемых элементов может содержать: имена полей, символ ‘*’, вычисления, литералы, функции, агрегирующие конструкции.

Вычисления:

SELECT StudName, Summa, Summa * 1.15 FROM Stipend – Получить список студентов и их стипендию, в том числе увеличенную на 15%.

Литералы. Для наглядности результата в запросах можно использовать литералы – строковые константы, заключенные в одинарные или двойные кавычки. Например:

SELECT StudName, "получает", Summa, " в месяц" FROM Stipend

Два или более столбца, имеющих строковый тип, можно соединять друг с другом, а также с литералами с помощью операции конкатенации (||). Например:

SELECT "сотрудник " || first_name || " " || last_name FROM Prepods

Работа с датами. В языке SQL имеются возможности конвертирования дат в строки и работы с датами. Внутренне дата содержит значения даты и времени. Внешне дата может быть представлена строками различных форматов, например:

· “October 27, 2005”

· “10/27/2005”

Дата может неявно конвертироваться в строку (из строки), если имеет один из допустимых форматов. Например:

SELECT StudName, ExamDate FROM Student, Exams WHERE ExamDate > '6/01/06' – получить список студентов, сдававших экзамен после 6/06/06.

Значения дат можно сравнивать, а также вычитать одну дату из другой.

Агрегатные функции. К агрегатным функциям относятся функции вычисления суммы (SUM), максимального (MAX) и минимального (MIN) значений столбцов, среднего арифметического (AVG), количества строк, удовлетворяющих заданному условию. Например: SELECT count(*),sum(budget),avg (budget), min(budget), max(budget) FROM department WHERE head_dept = 100

– вычислить количество отделов, являющихся подразделениями отдела 100, их суммарный, средний, минимальный и максимальный бюджеты.

Условия отбора. Директива WHERE содержит условия отбора (предикат). Запрос возвращает только строки, для которых предикат имеет значение true. Типы предикатов, используемых в предложении WHERE:

Сравнение: = (равно); <> (не равно);!= (не равно); > (больше); < (меньше); >= (больше или равно); <= (меньше или равно); BETWEEN, IN, LIKE, CONTAINING, IS NULL, EXIST, ANY, ALL.

Предикат BETWEEN задает диапазон значений, для которого истинно значение выражения. Например:

SELECT StudName, Stipend FROM Student WHERE Stipend BETWEEN 120 AND 200 – получить список студентов стипендия которых больше 120 и меньше 200.

Тот же запрос с использованием операторов сравнения будет выглядеть следующим образом:

SELECT StudName, Stipend FROM Student WHERE Stipend>=120000 AND Stipend<=200000

Предикат IN (NOT IN) проверяет, входит ли заданное значение, предшествующее ключевому слову “IN”, в указанный в скобках список. Например:

SELECT name FROM employee WHERE job_code IN ("VP", "Admin", "Finan") – получить список сотрудников, занимающих должности “вице-президент”, “администратор”, “финансовый директор”.

Предикат LIKE проверяет, соответствует ли данное символьное значение строке с указанной маской. В качестве маски используются все разрешенные символы (с учетом верхнего и нижнего регистров), а также специальные символы: % – замещает любое количество символов, _ – замещает только один символ. Например:

SELECT StudName FROM Student WHERE StudName LIKE "Ф%" – получить список студентов, фамилии которых начинаются с буквы ‘Ф’.

Предикат CONTAINING аналогичен предикату LIKE, однако он не чувствителен к регистру букв.

Предикат IS NULL принимает значение true только тогда, когда выражение слева от “IS NULL” имеет значение null (пусто, не определено).

Логические операторы. К логическим операторам относятся NOT, AND, OR.В одном предикате логические операторы выполняются в указанном порядке.

Преобразование типов. В SQL имеется возможность преобразовать значение к другому типу для выполнения операций сравнения. Для этого используется функция CAST.

Изменение порядка выводимых строк. Порядок выводимых строк может быть изменен с помощью предложения ORDER BY в конце SQL-запроса. Это предложение имеет вид: ORDER BY [ASC | DESC]

Способом по умолчанию является упорядочивание “по возрастанию” (ASC). Если указано “DESC”, упорядочивание производится “по убыванию”. Например:

SELECT StudName, Stipend FROM Student ORDER BY StudName – получить список в алфавитном порядке.

Операция соединения. Используется в языке SQL для вывода связанной информации, хранящейся в нескольких таблицах. Операции подразделяются на внутренние и внешние. Связывание производится, как правило, по первичному ключу одной таблицы и внешнему ключу другой таблицы. Предложение WHERE может содержать множественные условия соединений.

Внутренние соединения. Внутреннее соединение возвращает только те строки, для которых условие соединения принимает значение true. Рассмотрим пример запроса:

SELECT StudName, ExamMark FROM Student, Exams WHERE Kurs=2 AND ExamMark=5 – получить список студентов 2-го курса, сдававших экзамен на 5.

В запросе можно использовать способ непосредственного указания таблиц или указания таблиц с помощью алиасов (псевдонимов).

Внешние соединения. Внутреннее соединение возвращает только строки, для которых условие соединения принимает значение true. Внешнее соединение возвращает все строки из одной таблицы и те строки из другой таблицы, для которых условие соединения принимает значение true. Существуют два вида внешнего соединения: в левом соединении (LEFT JOIN) запрос возвращает все строки изтаблицы, стоящей слева от LEFT JOIN и только те из правой таблицы, которые удовлетворяют условию соединения. Для правого соединения – все наоборот. Например:

SELECT name, department FROM employee e LEFT JOIN department d ON e.dept_no = d.dept_no – получить список сотрудников и название их отделов, включая сотрудников, еще не назначенных ни в какой отдел.

5.6. Выполнение инструкций SQL

Для выполнения инструкции SQL создается содержащая ее строка и передается свойству SQL компонента TQuery. Компонента TQuery, должна быть помещена на форму, ее свойство DatabaseName настроено на нужный алиас (если базы данных не существует, можно создать ее в SQL Explorer).

Для создания статического запроса можно ввести SQL-предложение в свойство SQL компонента TQuery.

Способ создания динамического запроса состоит в создании строки и добавлении ее в свойство SQL при выполнении приложения. Для выполнения запроса, изменяющего структуру данных, вставляющего или обновляющего данные на сервере, нужно вызвать метод ExecSQL() компонента TQuery. Например:

Query1->Close();

Query1->SQL->Clear();

Query1->SQL->Add("Delete * from Country where Name = 'Blr' ");

Query1->ExecSQL();

Приведем упрощенный синтаксис SQL-предложения для создания таблицы на SQL-сервере:

CREATE TABLE table (<col_def> [, <col_def> | <tconstraint>...]);

где table – имя создаваемой таблицы, <col_def> – описание поля, <tconstraint> – описание ограничений и/или ключей (квадратные скобки [] означают необязательность, вертикальная черта | означает “или”).

Приведем несколько примеров создания таблиц с помощью SQL.

Пример. Простая таблица с конструкцией PRIMARY KEY на уровне поля:

CREATE TABLE REGION (REGION REGION_NAME NOT NULL PRIMARY KEY, POPULATION INTEGER NOT NULL);

Предполагается, что в базе данных определен домен REGION_NAME, например, следующим образом:

CREATE DOMAIN REGION_NAME AS VARCHAR(40) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

Для выполнения запроса на получение данных с помощью оператора SELECT, нужно вызвать метод Open() компонента TQuery.

Queryl->Close ();

Queryl->SQL->Clear ();//Очистить свойство SQL от запроса

Queryl->SQL->Add(str); //Присвоить текст свойству SQL

Query1->Open(); // выполнить команду SQL

5.7. Разработка приложений баз данных

Механизм BDE. Механизм BDE (Borland Database Engine), обеспечивающий работу визуальных компонентов баз данных, действует как интерфейс между приложением и базой данных. BDE обращается к драйверам для баз данных указанного типа, возвращая запрошенные данные. Используя BDE, можно получить доступ к локальным стандартным базам данных, к источникам данных ODBC и к SQL-серверам баз данных. Чтобы получить доступ к содержимому базы данных, приложению необходимо знать только ее алиас.

Использование визуальных компонентов. C++Builder предоставляет разработчикам компоненты для работы с базами данных из VCL:

1. Компоненты управления данными на вкладке Data Control (такие как TDBEdit, сетка TDBGrid или DBNavigator) для отображения и редактирования записей на форме.

2. Компоненты доступа к данным на вкладке Data Access. Компонент источника TDataSource служит как интерфейс межкомпонентной связи между таблицей TTable или запросом Tquery и компонентой управления.

C++Builder поддерживает трехступенчатую модель разработки приложения баз данных. В этой модели компонент управления связан с компонентом источника TDataSource, а тот, в свою очередь, получает фактические данные из таблицы или запроса посредством механизма BDE.

Рассмотрим работу компонента доступа.

Источники данных. Невидимый компонент TDataSource действует как интерфейс между некоторым объектом набора данных (таблица, запрос) и визуальным компонентом управления. С одной стороны, все наборы данных должны быть ассоциированы с некоторым источником. С другой стороны, каждый компонент управления должен быть ассоциирован с источником, чтобы получать данные для отображения и редактирования.

Свойство DataSet компонента TDataSource определяет имя конкретного набора данных (таблицы или запроса), который питает данный источник. С помощью этого свойства можно переключаться с одного набора данных на другой во время выполнения программы. Следующий код реализует попеременное подключение объекта источника DataSource1 к таблице заказчиков "Заказчики" или к таблице "Заказы":

if (DataSourcel->DataSet == "Заказчики")

DataSourcel->DataSet = "Заказы";

Чтобы синхронизировать работу компонентов управления на двух формах, достаточно установить свойство DataSet на один и тот же набор данных:

void__fastcall TForm2::FormCreate (TObject *Sender) {

DataSource1->DataSet = Form1->Table1;}

С компонентом TDataSource связаны три события. Событие OnDataChange возникает при перемещении курсора на новую запись. Событие OnStateChange возникает при изменении свойства State наборов данных. Например, следующий обработчик события будет отслеживать изменения состояния таблицы MyTable.

void__fastcall TForm1::StateChange(TObject *Sender)

{String s;;

switch (MyTable->State) {

case dsInactive: s="Таблица неактивна"; break;

case dsBrowse: s = "Идет просмотр"; break;

case dsEdit: s = "Идет редактирование"; break;

case dsInsert: s = "Идет вставка записи"; break;

}

// Вывод текстовой строки s

Form1->Caption=s;

}

Событие OnUpdateData возникает перед фактическим обновлением текущей записи.

3. Компоненты DDE.

Таблицы. Компонент TTable устанавливает прямую связь с таблицей базы данных посредством BDE, причем все записи или столбцы этой таблицы становятся доступными для приложения.

Свойство Active компонента разрешает или запрещает режим просмотра "живых данных" таблицы на этапе проектирования. Значение true или метод Open() открывают просмотр таблицы. Значение false или метод Close() закрывают просмотр.

Свойство DatabaseName содержит псевдоним базы данных или путь к ее каталогу. Использование псевдонима всегда предпочтительнее: вы можете переназначить физический носитель данных. Перекомпиляция приложения не требуется – просто измените путь на вкладке Aliases в утилите конфигурации BDE.

Дата добавления: 2014-12-27