ALTER TABLE Имя_таблицы [DROP [COLUMN] Имя_поля] [SET CHECK Правило_корректности_поля [ERROR ALTER TABLE Russian ADD COLUMN INFO M добавляем в таблицу новый столбец для хранения некоторой дополнительной информации.


© Н.Р.Бухараев. ПРАКТИКУМ ПО СУБД

От составителя.

Данное методическое пособие предназначено для преподавателей, ведущих практические занятия по курсу «Введение в СУБД», читаемого для студентов второго курса факультета ВМК. Пособие содержит небольшое теоретическое введение в терминологию СУБД, краткое описание языка SQL, встроенного в язык СУБД Visual FoxPro и – не зависящие от выбранного языка СУБД - примеры типовых заданий.

При подборе заданий основной целью было ставилась не столько отработка синтаксиса или примеров использования конструкций достаточно обозримого и скромного по объему языка SQL, сколько – обучение приемов использования основных понятий СУБД как аппарата моделирования, т.е. – часто, весьма нетривиального формального уточнения содержательных понятий.

Автор заранее благодарен за любые критические замечания, дополнения и пожелания коллег и студентов, направленных на улучшение данного пособия как по содержанию, так и стилю изложения.

Краткое введение в СУБД.

Принципиальная особенность и цель СУБД - полное и точное формальное описание ( моделирование) объектов и процессов некоторой реальной ситуации (предметной области) в виде совокупности таблиц. Стандартная реализация таблиц - файлы, т.е. постоянные и "большие" наборы данных на внешних носителях, поэтому такое описание должно быть, по возможности, минимальным и эффективным, в смысле экономии памяти и скорости обработки.

Точнее, таблица (table) - n-арное отношение Т, т.е. произвольное множество n-ок - строк или записей (record) таблицы, компоненты которых в СУБД принято называть столбцами или полями (field) записи.

TABLE={RECORD: RECORD=,
FIELD1(D1, FIELDn(Dn}

Поля записи могут содержать лишь значения базовых, или - скалярных типов и, в частности, общее для всех таких типов неопределенное значение NULL. Заметим, что, в отличие от описания типов в процедурных языках, определение таблицы не рекурсивно – одна таблица не может входить в определение другой таблицы.

Для описания нетривиальных сложно структурированных объектов используется механизм ссылок или связей между таблицами.

Если PK(r) - некоторое выражение над полями таблицы PT, можно считать, что PK различает две записи r1,r2(PT, если PK(r1)(PK(r2). PK называется (первичным) ключом (primary key) таблицы, если оно, позволяет идентифицировать каждую запись таблицы, отличить ее других:

( r1,r2 ( PT ( r1=r2 ( PK(r1)=PK(r2))

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

Далее, пусть FK(r) - выражение над полями другой таблицы CT. Запись r1, r1(CT ссылается (references) на запись r2, r2(PT, если FK(r1)=PT(r2). FK называется внешним ключом (foreign key) таблицы CT, если каждая запись в CT имеет соответствие в PT:

( r1( CT (r2(PT (FK(r1)=PT(r2))

В этом случае таблица PT называется родительской (parent table), таблица CT - дочерней (child table), а соответствуюшее отношение - связью "многие-ко-многим". Если, к тому же, PT - первичный ключ таблицы PT и, соответственно, каждая запись CT ссылается на единственную запись PT, то получающееся функциональное отношение называют связью "многие-к-одному". Наконец, если эта функция инъективна, т.е. не принимает одинаковых значений на разных аргументах, соответствующую связь называют связью "один-к-одному".

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

Функциональные связи наиболее популярны в СУБД. Связи "многие-ко-многим", т.е. отношения общего вида, трудны для понимания и обработки - их обычно стараются представить в виде нескольких отношений "один-ко-многим". Как правило, наличие связи "один-к-одному" означает, что удобнее иметь дело не с двумя, а с одной таблицей - их композицией по этому соответствию (как правило, но не всегда ; естественный пример связи “один-к-одному” – отношения “быть подмножеством”, точнее – отношение тождественного вложения).

Базой данных (database) называется формальная модель некоторой предметной области в виде совокупности таблиц, связанных отношениями.

Для того, чтобы в процессе модификации - вставки (insert), обновлении (update) и удаления (delete) записей - БД не перестала адекватно отражать реальное содержание предметной области, необходимо выполнение следующих требований:

таблицы БД не должны содержать нереальных данных, которые могут появиться как следствие ошибок программ обработки БД или некорректного ввода. Для этого в определение таблиц БД добавляют правила проверки (validation rules) значений поля или группы полей, определяющие необходимые условия их корректности;

БД должна удовлетворять условию ссылочной целостности (refrential integrity), т.е. таблицы БД, - в результате тех же причин - не должны попадать записи-"сироты". Следовательно, модификация связанных таблиц должна быть согласованной. Логически, возможны несколько вариантов такого согласования, а именно - модификация записи родительской таблицы может быть
а) каскадной (cascades), т.е. продолжаться на соответствующие записи дочерней таблицы;
b) нулевой (nulls), т.е. устанавливать ключи дочерних записей в NULL;
c) ограниченной (restricted), т.е. исполняемой лишь в тех случаях, когда у нее нет ни одной дочерней записи.

Как и правила проверки, варианты сохранения справочной целостности БД также являются частью ее определения.

Все основные команды СУБД можно рассматривать как преобразователи одной или нескольких таблиц, результатом которых снова является таблица (новая либо обновленная старая):

T=((T1,,Tn)

В отличие от команд модификации БД, результатом команды запроса (select) к БД является чисто логическая, виртуальная таблица, не существующая физически в виде постоянно хранимого файла, но лишь отражающей текущее состояние физически хранимых реально, или – базовых таблиц БД. Цель использования таких таблиц ясна – отобразить в развернутом, полном и логичном, с точки зрения конечного пользователя, виде информацию о реальных объектах предметной области по компактно закодированной информации, содержащейся в базовых таблицах.

Представления (view) – тип логических таблиц, определение (но не содержимое!) которых храниться в виде именованных команд запроса как часть БД. Подобно базовым таблицам, представления могут служить аргументами других запросов и представлений, позволяя таким образом пользователю БД определять родо-видовые иерархии объектов предметной области. “Содержимое” модифицируемых представлений можно редактировать с помощью команд модификации – но поскольку такового, в реальности, не существует, последнее означает модификацию базовых таблиц представления, ведущее к требуемому содержимому представления. Даже теоретически, нужная модификация существует далеко не всегда; помимо этого различные СУБД накладывают дополнительные ограничения на критерий модифицируемости, исходя из соображения эффективности генерации требуемой модификации.

Безопасность данных играет в СУБД первостепенную роль. Помимо не рассматриваемого в данном пособии и часто достаточно изощренного аппарата администрирования БД (к которому относяться, в частности, вопросы архивации и ограничения прав доступа пользователей БД), безопасность данных обеспечивается механизмом поддержки трансакций. Трансакция (transaction) - логически единая операция по модификации базовых таблиц, состоящая из одной или нескольких команд СУБД, переводящая БД из одного корректного, с точки зрения предметной области, состояния в другое, также логически корректное. Поддержка трансакций гарантирует, что входящие в трансакцию команды либо выполнятся полностью, либо – если последнее невозможно по какой-либо причине (сбой энергоснабжения, конфликт действий пользователей в многопользовательской/ сетевой БД и пр.) – полное восстановление состояния БД на момент начала трансакции. Типичный пример автоматической трансакции – процедуры-триггеры (trigger), выполняющие описанные выше действия по сохранению ссылочной целостности.

SQL FoxPro – КОМАНДЫ И ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ

СУБД FoxPro, за многие годы своей эволюции, накопила колоссальное количество стандартных операций, функций и условий (предикатов), с которыми программист знакомится по мере освоения системы. В принципе, все эти возможности можно использовать во вложенном SQL (при соблюдении некоторых мер осторожности, что особенно относиться к использованию пользовательских функций).

С другой стороны, SQL образует вполне изолированный подъязык FoxPro, отличным от него по духу и синтаксису: в отличие от "собственно" языка FoxPro, ведущего свое происхождение от семейства чисто процедурных языков СУБД Xbase, SQL является декларативным языком преобразований таблиц в целом, а не процедурным языком обработки их записей. Синтаксис команд SQL замкнут и не содержит ссылок на текущее состояние окружения, столь многочисленных в командах FoxPro.

Щадя читателя и следуя принципу "разумной достаточности", мы далее немного упрощаем синтаксис - в основном, за счет исключения синонимии и редко используемых опций. Более полный синтаксис, как всегда, можно найти в справочной системе FoxPro. Отсутствующие в стандарте (и, возможно - в Вашей версии!) SQL отмечены символом *

Очевидно (но часто забывается), что, если программист использует FoxPro SQL "по прямому назначению" - именно, для связи с другими СУБД в рамках технологии "клиент-сервер", то он просто должен отказаться от многочисленных возможностей FoxPro и обязан использовать исключительно описанный ниже набор функций, предикатов и команд стандартного SQL.

Команды SQL FoxPro образуют замкнутый язык СУБД, достаточный для выполнения основных задач обработки локальных данных, а также обработки удаленных данных - в рамках технологии удаленных представлений (remote view). Главное достоинство последней - простота; от программиста скрыты все детали реализации обработки данных на удаленном источнике (сервере - server) в терминах его собственной версии SQL.

Выходящий за рамки данного пособия, но также поддерживаемый FoxPro аппарат "сквозного SQL" , или технологии SPT (SQL-pass-through) более эффективен, но и более изощрен - кроме знания достаточно обширного дополнительного набора функций SQL, он предполагает и знание версии SQL, "работающей" на сервере. Любознательный читатель, как всегда, найдет более полную информацию в справочной системе FoxPro.


Выражения и предикаты в SQL.

Итак, в рамках данного пособия достаточно знания лишь "классического набора” выражений SQL, а именно - ариметических выражений и предикатов следующего вида:

а) Сравнения

Аргумент1 Знак_сравнения Аргумент2

где знак сравнения - один из символов отношений = (равенство), <>, !=, # (неравенство) и >, >=,<,<=, а также, для символьных строк, == (точное равенство) и [NOT] LIKE (сравнение по маске),

Заметьте, что, если для числовых типов и типа "дата" такие сравнения имеют обычный смысл, то для символьных строк имеется ввиду лексикографическое (словарное) сравнение, а “равенство строк” =, в отличие от стандартного SQL, несимметрично и понимается в FoxPro как вхождение второго аргумента в первый как начального подслова (см. документацию о зависимости такого сравнения от установок системных переменных ANSI и EXACT).

Примеры.
1. Предикаты 0#1, “AB”<“ABC”, “AB”!=”ABC”, “ABC”==”ABC” равны .T. (т.е. истинны), предикаты 0=1, ”AB”>=”ABC” равны .F. (т.е. ложны)
2. Зависимость символьного сравнения = от установок системной переменной EXACT
EXACT=OFF EXACT=ON

“AB”=”ABC” .F. .F.
“ABC”=”AB” .T. .F.
“ABC”=”AB “ .F. .T.
“AB “=”AB” .T. .T.
“AB”=”” .T. .F.
“”=”AB” .F. .F.
“ “=”” .T. .T.

Предикат сравнения по маске имеет вид

Аргумент [NOT] LIKE МАСКА

где МАСКА - произвольная символьная строка, (возможно) содержащая специальные символы кратной замены % и одиночной замены _. Предикат [NOT] LIKE считается истинным, если маску можно превратить в стоящий слева аргумент подстановкой некоторых слов вместо символа % и одиночных символов - вместо символов _.
Примеры.

“bit” LIKE “b_t” =.T.
“boat” LIKE “b_t” =.F.
“boat” LIKE “b__t” = .T.
“boat” LIKE “b%t” =.T.

б) Булевские формулы, как обычно, образуются с помощью операций конъюнкции AND, дизъюнкции OR и отрицания NOT
Примеры.
(1=0) OR (0<1) = .T.
(1=0) AND (0<1) = .F.
NOT (1=0) =.T.

в) "Синтаксический сахар" - группа предикатов, выразимых через булевские комбинации сравнений, но более кратких и привычных по форме (для англоязычного пользователя)

Аргумент1 [NOT] BETWEEN Аргумент2 AND Аргумент3
- краткая форма записи предиката
[NOT] (Аргумент2 (Аргумент1 AND Аргумент1(Аргумент3)

например,

5 BETWEEN 10 AND 20 =.F.
B’ BETWEEN 'A' AND 'G'=.T.


Аргумент [NOT] IN (список_значений)
- краткая форма записи предиката
[NOT] (Аргумент=Аргумент1 OR Аргумент(Аргумент2 OR Аргумент=АргументN)

например,
'Ваня’ IN (Петя’,’Маша’,’Ваня’) =.T.
'Маня’ IN (Петя’,’Маша’,’Ваня’) =.F.

г) Предикаты, использующие выборку – см. далее команду SELECT


Создание таблиц


Как следует из названия, Visual FoxPro – язык визуального программирования, представляющий пользователю богатые возможности выполнения многих стандартных функций без непосредственного текстового ввода; к такого рода функциям относяться и описываемые ниже возможности создания и модификации БД. На практике необходимость их программного выполнения возникает достаточно редко.

CREATE TABLE Имя_создаваемой_таблицы [NAME* Длинное - до 128 символов - имя_таблицы] [FREE*]
[(ОПИСАНИЕ ПОЛЯ 1 [ПРАВИЛА КОРРЕКТНОСТИ ПОЛЯ 1)],
[(ОПИСАНИЕ ПОЛЯ 2] [ПРАВИЛА КОРРЕКТНОСТИ ПОЛЯ 2])],
..
[(ОПИСАНИЕ ПОЛЯ n] [ПРАВИЛА КОРРЕКТНОСТИ ПОЛЯ n])],
[ПРАВИЛА КОРРЕКТНОСТИ ЗАПИСИ]

(опция FREE - свободная таблица - используем в том случае, если мы не хотим включать создаваемую таблицу ни в одну из существующих БД; в противном случае, по умолчанию, таблица будет включена в открытую на момент исполнения команды CREATE TABLE БД)

где

ОПИСАНИЕ ПОЛЯ -
Имя_поля
Тип_поля
[(Ширина_поля [,Точность (число знаков после запятой - для числовых типов])]

Перечислим допустимые в FoxPro скалярные типы, с указанием имени типа и допустимости для соответствующего типа, опций указания ширины поля (Ш) и точности (Т).

Тип Ш Т Комментарий
C n - Character - cимвольная строка некоторой длины n
D - - Date - Дата
T - - dateTime - Дата-время
N n d Numeric - вешественное длины n , d знаков после
запятой
F n d Floating numeric - вещественное длины n, d знаков
после запятой, в форме с плавающей точкой
I - - Integer - целое
B - d douBle - целое двойной точности
Y - - currencY - денежная сумма
L - - Logical - логический
M - - Memo - строка неопределенной длины
G - - General - ссылка на внешний объект

Замечание. Скалярные типы данных могут различаться как по синтаксису, так и по семантике в различных версиях SQL.

ПРАВИЛА КОРРЕКТНОСТИ ПОЛЯ - одна или несколько опций вида

[NULL | NOT NULL] - значением поля может (не может) быть неопределенное значение NULL; по умолчанию, значение опции определяется значением системной (SET-)переменной NULL; также по умолчанию, значение NULL не допустимо для первичных ключей и уникальных (UNIQUE) полей;

[CHECK lУсловие корректности поля [ERROR* Текст сообщения о нарушении условия]] - проверяется при каждой модификации, а также при добавлении "пустой" записи; сообщение об ошибке появляется лишь при работе в интерактивном режиме


[DEFAULT Значение поля по умолчанию]

[PRIMARY KEY | UNIQUE] - значение поля является (единственным) первичным ключом записи | должно быть уникальным для каждой записи таблицы; для поддержки соответствующего правила создается индекс с именем, совпадающим с именем поля:

[REFERENCES Имя_родительской_таблицы [TAG Имя (тег) индекса]] - значение поля является внешним ключом указанной родительской таблицы [по данному индексу]

ПРАВИЛА КОРРЕКТНОСТИ ЗАПИСИ - список из одной или несколько опций вида

[PRIMARY KEY Выражение TAG* Имя (тег) индекса |, UNIQUE Выражение TAG* Имя индекса] - указанное выражение определяет (единственный составной) первичный ключ записи | обязано быть уникальным для каждой записи; для поддержки соответствующего правила создается индекс с указанным именем (тегом);

[, FOREIGN KEY Выражение TAG* Имя_индекса
REFERENCES Имя_родительской таблицы [TAG* Имя индекса родительской таблицы]] - указанное выражение определяет (составной) внешний ключ записи, поддерживаемым родительским индексом с указанным тегом

[, CHECK Условие корректности записи [ERROR* Текст сообщения о нарушении условия]]) - проверяется при каждой модификации, а также при добавлении "пустой" записи; сообщение об ошибке появляется лишь при работе в интерактивном режиме

Пример.
Для демонстрации действия команды CREATE, а также других вводимых ниже команд SQL создадим простейшую БД – англо-франко-немецко-русский словарь, состоящую из двух таблиц: RUSSIAN, в которой будем хранить слова русского языка, с числовым ключом, и EUROPEAN, предназначенной для хранения иностранных слов, с указанием кода языка ('ENG’ –английский, FRA ’- французский, GER’ - немецкий). Без ограничения общности можем полагать, что ни в одном иностранном языке нет синонимов (добавляя, если нужно, в запись слова номер значения, как это обычно делают составители словарей – например, - Коса,1 (сельскохозяйственный инструмент), Коса,2 – (песчаная отмель) и т.п.)

Create Table RUSSIAN
(id integer Primary Key,
word char(20) Not Null
)
Create Table European
(
word char(20) Not Null,
language char(3) Default ENG’ CHECK (language in (ENG’,’FRA’,’GER’) ERROR Недопустимый код языка ! ’),
rus_id integer References Russian,
UNIQUE (word,language)
)

Замечание. В текущей версии FoxPro команда Create Table не имеет стандартных опций установки режимов поддержки справочной целостности, однако, все они могут легко установлены через интерфейс (см. документацию)

МОДИФИКАЦИЯ СТРУКТУРЫ БД В SQL

УДАЛЕНИЕ ТАБЛИЦЫ

DROP TABLE Имя_таблицы | Имя_файла | ? [RECYCLE]
удаляет таблицу из текущей БД; опция ? выдает диалоговое окно выбора таблицы; подопция RECYCLE указывает на то, что удаленную таблицу нужно поместить в "корзину" ОС Windows c возможностью последующего восстановления, в противном случае восстановление невозможно.

УДАЛЕНИЕ ПРЕДСТАВЛЕНИЯ

DROP VIEW имя_представления

МОДИФИКАЦИЯ СТРУКТУРЫ ТАБЛИЦЫ

ALTER TABLE Имя_таблицы
ADD | ALTER [COLUMN] Имя_поля
Тип_поля [(Ширина_поля [, Точность])]
[NULL | NOT NULL]
[CHECK Правило_корректности_поля
[ERROR Текст_сообщения_о_нарушении_правила]]
[DEFAULT Значение_по_умолчанию]
[PRIMARY KEY | UNIQUE]
[REFERENCES Имя_родительской_таблицы
[TAG Тег(имя)_индекса]]

или

ALTER TABLE Имя_таблицы
ALTER [COLUMN] Имя_поля
[NULL | NOT NULL]
[SET DEFAULT Значение_по_умолчанию]
[SET CHECK Правило_корректности_поля
[ERROR Текст_сообщения_о_нарушении_правила]]
[DROP DEFAULT]
[DROP CHECK]
или

ALTER TABLE Имя_таблицы
[DROP [COLUMN] Имя_поля]
[SET CHECK Правило_корректности_поля
[ERROR Текст_сообщения_о_нарушении_правила]]
[DROP CHECK]
[ADD PRIMARY KEY Выражение_первичного_ключа
TAG Тег(имя)_индекса_первичного_ключа]
[DROP PRIMARY KEY]
[ADD UNIQUE Выражение [TAG Тег_индекса]]
[DROP UNIQUE TAG Тег_индекса]
[ADD FOREIGN KEY [Выражение_внешнего_ключа]
TAG Тег_индекса
REFERENCES Имя_родительской_таблицы
[TAG Тег_родительского_индекса]]
[DROP FOREIGN KEY TAG Тег_индекса]
[RENAME COLUMN Старое_имя_поля TO Новое_имя_поля]

Несмотря на устрашающий синтаксис, семантика команды ALTER TABLE легко выводиться из пояснений к команде CREATE TABLE и значения английских слов ALTER - изменить, ADD - добавить, DROP - удалить, SET - положить равным, RENAME - переименовать.

Примеры.

ALTER TABLE Russian ADD COLUMN INFO M
добавляем в таблицу новый столбец для хранения некоторой дополнительной информации.

ALTER TABLE EUROPEAN ALTER COLUMN Language CHECK (Language in ('ENG','FRA','GER','ITA') )
изменяем правило корректности кода языка – для поддержки хранения итальянского слов.

Замечание. Пожалуй, само заметное упущение текущей версии FoxPro по сравнению с другими популярными реализациями SQL - отсутствие команды создания пользовательского индекса Create Index (и, соответственно команды удаления индекса Drop Index). Правда, это упущение – чисто синтаксическое, поскольку имеется более мощная команда-аналог самого FoxPro – а именно, команда Index (см.документацию).

МОДИФИКАЦИЯ ТАБЛИЦ В SQL. КОМАНДЫ и ПРИМЕРЫ


Добавление записи.

INSERT INTO имя_таблицы [(список_имен_полей])]
VALUES (список_выражений)

где
имя_таблицы - имя dbf-файла (возможно - с указанием пути) или алиас рабочей области; может быть задано строковым выражением.
Если список имен полей задан, то он, очевидно, должен быть согласован по типу со списком выражений, задающим значения соответствующих полей в добавляемой записи; если список имен полей отсутствует, то, по умолчанию, подразумевается список имен всех полей таблицы. В любом случае, необъявленные значения полей либо берутся равным значению по умолчанию (см. опцию DEFAULT в команде CREATE TABLE), либо - равными NULL (если системная SET-переменная NULL имеет значение ON)

Замечания.
Текущая версия FoxPro не содержит второй стандартной формы команды - INSERT INTO имя_таблицы (команда SELECT), которая, впрочем легко реализуется – например, проходом с помощью цикла SCAN по соответствующему команде выборки представлению или курсору (см. документацию)
С другой стороны, FoxPro содержит свои собственные, достаточно многочисленные и изощренные способы генерации записей - см., например APPEND, INSERT в справочной системе FoxPro.

Примеры.
INSERT INTO Russian VALUES (1,’Кресло’)
INSERT INTO European (word, rus_id,language) VALUES (Chair’,1,’ENG’)

Редактирование записей

UPDATE [Имя_базы_данных!]Имя_таблицы
SET Список выражений вида Имя_поля=Выражение
[WHERE Условие_обновления]

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

Пример.
Update European Set word='Armchair' where Russ_id=1

Логическое удаление записей

DELETE FROM [Имя_базы_данных!]Имя_таблицы
[WHERE Условие_удаления]

помечает записи [,удовлетворяющие заданному условию,] как удаленные.
Пример.
Delete from European where Language==’ENG’
удаление всех английских слов из таблицы иностранных слов.

Замечание. Как обычно в СУБД, логическое удаление записей не означает их фактического, т.е. физического удаления - производимого в FoxPro командой упаковки таблицы PACK; до выполнения последней помеченные как удаленные записи могут быть восстановлены командой RECALL. Помеченные, но фактически не удаленные записи могут включаться или не включаться в последующую обработку в зависимости от значения системной SET-переменная DELETED.


ЗАПРОСЫ К БД - КОМАНДА SELECT. ПРЕДСТАВЛЕНИЯ


SELECT
[* | ALL | DISTINCT]
[TOP числовое_выражение [PERCENT]]*
Список выборки

FROM Список_имен_базовых_таблиц

[[INTO Приемник_выборки]
| [TO FILE Имя_файла [ADDITIVE]
| TO PRINTER [PROMPT] | TO SCREEN]]*
[NOCONSOLE]*

[WHERE Условия_связи_и_условия_фильтрации]

[GROUP BY Список_столбцев_группировки]

[HAVING Условия_на_группу]

[UNION [ALL] команда SELECT]

[ORDER BY Список_столбцев_упорядочения]

Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]


где


ALL | DISTINCT - запрос на выборку всех(опция по умолчанию)| только различных значений списка выборки; последние считаются различными, если они различаются по значению хотя бы одной компоненты;

Пример. Перечисление без повторений.
Select Distinct word from European
осуществляет выборку (создает воображаемую неименованную таблицу) с одним столбцем с именем word, значениями которого являются все различные по написанию иностранные слова таблицы European.
TOP числовое_выражение [PERCENT]* - запрос на выборку лишь нескольких первых различных строк выборки; количество различных строк (или процент от общего количества) задается значением числового выражения; используется только при задания порядка выборки - т.е. при использовании опции ORDER BY;

Пример.
Select Top 10 word from Russian order by word
выбирает первые 10 слов таблицы Russian, упорядоченной лексикографически.
Замечание. В реальности, это порядок зависит от установки системной переменной Collate – при неожиданных результатах, установите порядок символьного сравнения совпадающим с порядком в текущей кодовой страницы ОС командой SET COLLATE TO MACHINE.

СПИСОК ВЫБОРКИ - список выражений вида

Компонента_выборки [AS Пользовательское имя поля]

Компонентой выборки может быть произвольное - в том числе константное - выражение над полями базовых таблиц; чаще всего это - просто имя поля базовой таблицы. Если базовые таблицы содержат одинаковые имена полей, во избежание коллизии имен необходимо использовать полные, или - квалифицированные имена полей вида

[Имя или алиас таблицы.] Имя_поля

Имена полей в результирующей таблице (т.е. самой выборке) генерируются автоматически; опция AS позволяет давать им свои - более осмысленные имена.

Пример. Пользовательское именование столбцев

Select Upper(word) as slovo from Russian

создает выборку с единственным столбцем slovo, из всех русских слов таблицы Russian, переведенных в верхний регистр; поскольку список выборки содержит не имя поля, но выражение, без применения опции AS единственный столбец выборки получил бы автоматически сгенерированное имя.

FROM Список_имен_базовых таблиц

список имен таблиц, "опрашиваемых" данной выборкой, вида

[[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN *
[Имя_БД!] Имя_таблицы [Локальный алиас]
[ON Условие_связи]

В том случае, когда базовые таблицы принадлежат разным (или - неоткрытой) БД, необходимо использовать полные имена таблиц вида Имя_БД!Имя_таблицы. В случае, когда команда SELECT ссылается на одну базовую таблицу несколько раз (например, в случае вложенных запросов, т.е. использования одной выборки в предикатах другой выборки) во избежание коллизии имен необходимо использовать псевдонимы/алиасы таблиц; последние задают временные имена (копий) таблицы, действительные только на момент выполнения данной выборки;

Выборка из нескольких базовых таблиц означает (с точки зрения логики, но не реализации исполнения) выборку из их декартова произведения - т.е. выборку всевозможных комбинаций строк базовых таблиц. Такая полная выборка редко бывает осмысленной, поэтому ее ограничивают наложением дополнительных условий (отношений, связей, объединения) на пары базовых таблиц - в FoxPro условия связи представляют собой сравнения полей связываемых таблиц (см. выше), на практике чаще всего используются равенства первичного и внешнего ключей. В стандартном SQL связи реализуются с помощью опций WHERE и UNION.

Пример
Декартово произведение таблиц
Select European.word Russian.word from European,Russian
создает выборку из двух столбцев, со сгенерированными именами; значениями столбцев будут всевозможные пары иностранных и русских слов.

Декартов квадрат таблицы
Select a.word b.word from Russian a, Russian b
создает выборку из двух столбцев, со сгенерированными именами; значениями столбцев будут всевозможные пары русских слов.

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

“Перевод с иностранного”
Select European.word Russian.word from European,Russian where rus_id=id
создает выборку из двух столбцев, со сгенерированными именами; значениями столбцев будут не все, но связанные пары иностранных и русских слов – точнее, каждое иностранное слово и его перевод. Заметим, что квалифицировать принадлежность имен полей rus_id и id к соответствующим таблицам необязательно, поскольку это не вызывает коллизии (поле russ_id есть только в таблице European, а поле id - только в таблице Russian).

Использование опции where для заданий условия связи таблиц “отвлекает” от ее главной функции - фильтрации (см. далее), поэтому FoxPro предлагает расширенный синтаксис (и более оптимальную реализацию) наиболее популярных видов связей.

Внутреннее объединение (INNER JOIN) предполагает отбор из обеих таблиц только записей, удовлетворяющих соответствующему условию связи; кроме того, в левое внешнее объединение (LEFT [OUTER] JOIN) отбираются дополнительно все те строки из таблицы, стоящей слева от слова JOIN, в правое внешнее объединение (RIGHT [OUTER] JOIN) - все те строки из таблицы, стоящей справа от слова JOIN, и в полное внешнее объединение (FULL [OUTER] JOIN) - все те строки обеих таблиц, для которых соответствия в другой таблице не существует.

Примеры. При создании таблиц European и Russian мы не оговоривали наличия полной связи между ними – для некоторых иностранных слов может не существовать перевода (поле rus_id может иметь значение NULL), наоборот, в таблице Russian могут встретиться русские слова, не являющиеся переводом ни одного слова из таблицы European.

Select European.word Russian.word from European INNER JOIN Russian
эквивалентен предыдущему примеру – выборка выдает все пары “иностранное слово- его перевод на русский”.
Select European.word Russian.word from European LEFT JOIN Russian
– выдаются все пары “иностранное слово- его перевод на русский”, а также все иностранные слова, для которых перевода нет.
Select European.word Russian.word from European LEFT JOIN Russian
– выдаются все пары “иностранное слово- его перевод на русский”, а также все русские слова, которые не являются переводом ни одного иностранного слова.
Select European.word Russian.word from European FULL JOIN Russian
– выдаются все пары “иностранное слово- его перевод на русский”, а также - все иностранные слова, для которых перевода нет и все русские слова, которые не являются переводом ни одного иностранного слова.

Во всех примерах мы опускали указание условия связи ON rus_id=id, поскольку оно явно указано при определении БД, а потому – подразумевается.

INTO Приемник_выборки| [TO FILE Имя_файла [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN]]*

По умолчанию, результат выборки показывается в стандартном (BROWSE-) экранном окне. По желанию, можно указать другое место хранения результирующей таблицы · массив (опция INTO ARRAY имя_массива), временную таблицу "только для чтения", или - курсор (опция INTO CURSOR имя_курсора), таблица (т.е. DBF-файл - опция INTO DBF | TABLE Имя_таблицы [DATABASE Имя_БД [NAME Длинное_имя_таблицы]]), текстовый ASCII-файл (опция TO FILE имя_файла, подопция ADDITIVE указывается при дописывании в уже существующий файл), принтер ( опция TO PRINTER, подопция PROMPT временно останавливает вывод для подготовки принтера), или главное экранное окно (опция TO SCREEN). Опция NOCONSOLE при этом подавляет "эхо"-вывод на экран

Примеры.
Select * from Russian into array A
переписывает содержимое таблицы в двумерный массив (очевидно, подобная команда может использоваться только для очень небольших по количеству записей таблиц)

Select * from Russian into cursor C
переписывает содержимое таблицы во временную таблицу-курсор C

Select * from Russian to printer
распечатывает содержимое таблицы

Select * from Russian to file C:\Temp\Russian.txt’ additive
дописывает содержимое таблицы, в символьном виде, к тексту, уже хранящемуся в текстовом файле.

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

Пример.
Select word from European where language==’ENG’
выбирает английские слова из таблицы European

Select word from European where language==’FRA’ and IsNull(rus_id)
- выбирает французские слова, для которых нет перевода на русский язык в таблице Russian

Select word from European where (language==’FRA’ or language==’ENG’) and not IsNull(rus_id)
- выбирает французские и английские слова, для которых существует перевод на русский язык в таблице Russian

GROUP BY список_полей_группировки

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

COUNT(Выражение) - количество значений заданного выражения в группе, не равных NULL;
COUNT(*) - число строк в группе;
MIN(Выражение) - минимальное значение данного выражения по группе;
MAX(Выражение) - максимальное значение данного выражения по группе;
SUM(Числовое_выражение) - сумма значений заданного выражения по группе;
AVG(Числовое_выражение), - среднее значение заданного выражения по группе;

Примечание. Использование агрегатных функций возможно и без применения опции GROUP BY - в этом случае группой считается вся базовая таблица (декартово произведение базовых таблиц, если их несколько)

HAVING Условие_на_группу

Эта опция похожа на (и часто путается с) опцией WHERE, но, в отличие от последней, фильтрует не одиночные записи, а группы. Используется только совместно с GROUP BY. Задаваемое условие на группу не может содержать предикатов с подзапросами и ссылается только на поля из списка группировки. Как правило, это условие является проверкой значений некоторых агрегатных функций - для проверки значений полей эффективнее использовать опцию WHERE.

Примеры.
Select Count(*) from European
подсчитывает общее количество иностранных слов (записей в таблице European)

Select Count(rus_id) from European
подсчитывает количество иностранных слов (записей в таблице European), для которых есть русский перевод (NULL-значения поля rus_id в это количество не попадают)

Select Count(rus_id), language from European group by language
- подсчитывает и выдает 3 числа - количество иностранных слов (записей в таблице European), для которых есть русский перевод, отдельно для каждого языка.

Select Count(word), language from European where language in (ENG’,’FRA’) group by language
- подсчитывает и выдает 2 числа – общее количество иностранных слов (записей в таблице European), отдельно для английского и французского языка.
Select Count(rus_id), language from European where group by language having Count(word)=0
выдает название языков (и число 0), для слов которых в таблице Russain не существует ни одного перевода на русский язык

[UNION [ALL] команда SELECT] Объединяет результирующие таблицы двух или более выборок (совпадающих по структуре, т.е. количеству и типу компонентов выборки) в одну; в случае объединения более двух выборок необходимы расставить скобки. Заметьте, что тип поля результирующих таблиц в FoxPro определяется по типу первого выбранного значения, что не всегда удобно, т.к. первое значение может оказаться короче последующих. По умолчанию, опция UNION убирает дубликаты из окончательной результирующей таблицы - используйте подопцию ALL (все), если такое положение Вас не устраивает.
Отметим также, что
UNION нельзя использовать в подзапросах;
При использовании UNION разрешается использовать опцию упорядочения ORDER BY лишь последней объединяемой команды SELECT, со ссылкой на номер (но не имя) столбца; в этом случае упорядочение относится ко всей результирующей таблице-объединению.

Пример.
Select word, language from European
union
Select word,’РУС’ from Russian
выдается полный список хранящихся в БД иностранных и русских слов, с указанием кода языка.

ORDER BY список_полей_упорядочения [ASC|DESC]
как и в опции GROUP BY, список полей может состоять из имен полей, указанных явно либо выражением, значением которого является имя поля или его номер в списке выборки. В любом случае, подразумевается лексикографическое упорядочение по полям из заданного списка - либо по возрастанию (ASC - опция по умолчанию), либо по убыванию (DESC).

Пример

Select word from European where language==”ENG” order by 1
- выборка всех английских слов таблицы European, упорядоченных лексикографически (реальная упорядоченность символов в FoxPro определяется системной переменной COLLATE, см. замечание выше и документацию)

Вложенные запросы. Предикаты, использующие выборку.

Команды SELECT могут быть вложенными в другие команды (именно, команды модификации и другой команды SELECT) как часть следующих предикатов:

Имя_поля Знак_сравнения ALL (команда SELECT)

истинно, если сравнение выполняется для стоящего слева значения поля и всех значений, выбранных командой SELECT - при этом предполагается, что последняя выдает таблицу с одной колонкой ( т.е. список значений), соместимой по типу со значением поля. Условие считается истинным также, если подвыборка пуста.
Примерю
Select word from Russian where ENG’==ALL(Select language from European where rus_id=id)
выборка русских слов, для которых не существует иностранных слов с тем же смыслом – кроме, возможно, английских.

Имя_поля Знак_сравнения ANY | SOME (команда SELECT)

истинно, если сравнение выполняется для стоящего слева значения поля и хотя бы одного из значений, выбранных командой SELECT - снова предполагается, что последняя список значений, соместимых по типу со значением поля. Если подвыборка пуста, условие считается ложным.
Пример.
Select word from Russian where ENG’==SOME(Select language from European where rus_id=id)
выборка русских слов, которые являются переводом некоторого анллийского слова.

[NOT] EXISTS (команда_SELECT)

истинно, если подвыборка (не) пуста, т.е. (не) содержит по крайней мере одну строку.
Пример.
Select word from Russian where Exists(Select word from European where rus_id=id)
выборка русских слов, у которых есть хотя бы одно иностранное слово с тем же смыслом.

Имя_поля [NOT] IN (команда_SELECT)

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

Пример.
Select word from European this where word IN (Select word from European that where this.word=that.word and this.language!=that.language)

- выборка иностранных слов, у которых найдутся слова-синонимы в другом иностранном языке.

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

СОЗДАНИЕ ПРЕДСТАВЛЕНИЯ

CREATE SQL VIEW [Имя_представления ] [REMOTE]
[CONNECTION Имя_соединения_с_удаленным_источником [SHARE]
| CONNECTION Имя_удаленного_источника_данных]
[AS команда SELECT]

REMOTE опция создания удаленного представления, выбирающего данные от источника данных, отличного от локальных таблиц FoxPro. При этом имя удаленного источника может быть задано либо явно - опция CONNECTION Имя_удаленного_источника_данных, либо указанием на именованное соединение - опция CONNECTION Имя_соединения_с_удаленным_источником. Подопция SHARE указывает на распределенное соединение, обслуживающее несколько представлений. Наиболее простой способ определить именованное соединение в диалоговом режиме обеспечивается командой CREATE CONNECTION ? (другие подопции этой команды см. в документации).

AS команда_SELECT
собственно определение представления

Замечание.
В FoxPro допустимы
параметризованные представления, ссылающиеся на переменные FoxPro - при использовании параметров нужно поставить знак ? перед именем переменной.
редактируемые/модифицируемые представления, любое изменение содержимого таких представлений в реальности означает изменение базовых таблиц соответствующей представлению выборки ( достаточно обширное описание точного критерия модифицируемости - см. документацию ).

Примеры
Create English as Select word,rus_id from European where language==’ENG’
создание виртуальной, но теперь уже – именованной таблицы английских слов
Create Language as Select word,rus_id from European where language==?ThisLanguage
создание виртуальной таблицы слов некоторого (заданного параметром - кодом ) иностранного языка.

СОЗДАНИЕ КУРСОРА

CREATE CURSOR имя_курсора
(список_описания_полей_курсора)

где описание поля курсора имеет вид
(имя_поля тип_поля [(ширина_поля [, точностьe])
[NULL | NOT NULL]
[CHECK Правило_корректности_поля
[ERROR Текст_сообщения_о_нарушении_правила]]
[DEFAULT значение_по_умолчанию]
[UNIQUE]

См. описание опций в команде CREATE TABLE.

Курсор - временная таблица, с которой можно обращаться точно также, как с обычной таблицей БД, за одним, но важным исключением - в отличие от таблиц и представлений, курсор не считается частью БД; его содержимое и само определение действительно только в текущем сеансе работы и "теряется" немедленно по его закрытии.

КОМАНДЫ ПОДДЕРЖКИ ТРАНСАКЦИЙ

Как уже отмечалось выше, трансацией называется единое, с точки зрения логики БД как модели предметной области, действие по переводу БД из одного логически корректного состояния в другое корректное состояние; при этом неважно, одну или несколько команд SQL реализуют такое действие. Важнейшее свойство трансакции, с точки зрения реализации - принцип "все или ничего" - составляющие трансакцию команды модификации БД либо выполняются полностью, либо не выполняются вообще (причиной чему могут служить аппаратный сбой, действия другого пользователя и пр.) В реальности это означает, что внутри трансакции модификация содержимого таблиц БД производиться над их копиями в буфере, который записывается в собственно таблицы БД лишь при корректном завершении трансакции.

Начало трансакции оформляется в SQL командой

BEGIN TRANSACTION

и закачивается либо командой корректного завершения трансакции

END TRANSACTION

либо командой "отката", т.е. возврата к состоянию БД к моменту начала трансакции

ROLLBACK

FoxPro поддерживает до 5 уровней вложенности трансакций. Однако, поскольку другие пользователи сети не имеют доступа к записи таблиц БД, модифицируемым во время исполнения трансакции, необходимо минимизировать время ее выполнения.

Некоторые команды - в основном, это команды модификации структуры БД (такие, например, как ALTER TABLE и CREATE TABLE) не могут быть включены в трансакцию - см. документацию.

Пример трансакции.
Допустим, мы хотим удалить из БД понятие, заданное русским словом – например, стол’. Очевидно, это означает удаление всех слов всех языков, совпадающих по смыслу с этим словом.
Begin Transaction
create cursor ThisId as select id from Russian where word==’стул’
delete from Russian where id=Some(select id from ThisId)
delete from European where rus_id=Some(select id from ThisId)
End transaction

Замечание. Мы могли бы автоматизировать выполнение этой трансации, затребовав при определении таблицы Russian каскадное удаление дочерних записей, однако, быть может, в данном случае менее деструктивный вариант установки дочерних ключей в NULL будет более удачным.
Примерные типы заданий.

Пример простой БД «Учет заказов».

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

Сведения о покупателях:
УЧЕТный №;
ФАМИЛИЯ;
КРЕДИТ (сумма, на которую покупатель имеет право покупать товары у данной фирмы)
ДАТА рождения
ГОРОД проживания
АДРЕС
ТЕЛЕФОН
ТАБЕЛЬный № обслуживающего продавца;

Сведения о продавцах:
ТАБЕЛЬный №;
ФАМИЛИЯ;
КОМИССИЯ - число из интервала [0,1] - доля от суммы заказа, составляющая выручку продавца;
ДАТА рождения
ГОРОД проживания

Сведения о заказах:
ПРОДАВЕЦ - табельный № продавца;
ПОКУПАТЕЛЬ - учетный № покупателя;
ТОВАР - артикул (идентификатор) товара
ЗАКАЗАН (дата заказа);
ПОСТАВЛЕН (дата поставки)
СУММА заказа, в рублях;
КОЛИЧЕСТВО поставляемого товара ( в штуках или других подходящих единицах)

Упр.

Определите правила корректности для полей и записей таблиц, делая разумные допущения о том, что такое:
реальная дата заказа, если фирма основана 1.01.1990;
реальная сумма заказа, если фирма продает автомобили ценой от 10000 до 100000 рублей, в текущих ценах;
корректные сведения о заказе, если учитывать произошедшую 1.01.1998 деноминацию - смену масштаба цен.

Определите правила целостности в БД, предполагая, в частности, что:
при увольнении продавца информация о его заказах сохраняется;
при разрыве отношений с покупателем информация о его заказах далее не хранится

Определите БД "Учет заказов" в вашей СУБД

Измените БД, для того, чтобы отразить старшинство членов фирмы, полагая, что у каждого продавца, кроме главы фирмы, есть непосредственный начальник.

Дополните модель сведениями о продаваемых фирмой товарах – а именно, их

АРТИКУЛ,
НАИМЕНОВАНИЕ,
код ТИПа товара (пример кодировки - 0101 - 'Автомобили', 0903 - 'Канцелярские товары' и пр.),
коде предприятия-ИЗГОТОВИТЕЛЯ,
коде ЕДИНИЦЫ измерения (метры, кв. метры, килограммы, штуки),
ЦЕНЕ за единицу
и имеющемся на складе КОЛИЧЕСТВЕ.

Как изменится БД, если допустить, что

Каждый покупатель всегда покупает только один товар?
Каждый продавец продает постоянно лишь один товар?
В общем (и подразумеваемом далее) случае неверно ни то, ни другое?


ЗАПРОСЫ К БАЗЕ ДАННЫХ.

Введем несколько определений, полезных для компактной формулировки задач. Каждая строка таблицы БД описывает некоторый объект предметной области (или отношение между объектами, которое снова удобно считать объектом). Аттрибутами объекта назовем значения одного или нескольких полей соответствующей ему таблицы. Свойством или категорией объектов назовем некоторую естественно определяемое, в терминах предметной области, множество объектов. Категории объектов разделим на простые и сложные, в зависимости от того, содержится ли необходимая для определения категории информация в одной или нескольких таблицах БД. Примеры аттрибутов и категорий для объектов БД «Учет заказов» приведены ниже.

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

Определите

(аттрибуты покупателей:)

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

13 LINK \l "Покупатели" 14покупателей15,

( простые категории покупателей:)

живущих в данном городе (например, Казани)
живущих в данных городах (например - Казани, Самаре или Москве )
(не) совершеннолетних, старых, молодых
(не) кредитоспособных

Определите
(аттрибуты продавцев:)

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

13 LINK \l "Продавцы" 14продавцев15,

(простые категории продавцев:)

живущих в данном городе (например, Казани)
живущих в данных городах (например - Казани, Самаре или Москве)
низко-, средне- высокооплачиваемых
старых, молодых

Определите

(аттрибуты заказов:)

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

13 LINK \l "заказов" 14заказов15

(простые категории заказов:)

на трехзначную сумму, по возрастанию суммы
сделанных сегодня (вчера, на прошлой неделе, этим летом, в прошлом году), по возрастанию суммы заказа
(не) выгодных для фирмы
просроченных, по возрастанию даты заказа
мелких, средних, крупных

Определите

(аттрибуты товаров:)

артикулы
цену и названия, по возрастанию цены

13 LINK \l "товаров" 14товаров15

(простые категории товаров:)

дорогих (дешевых)
имеющихся на складе в (не)достаточном количестве, отсутствующих на складе

Выборка из нескольких таблиц.

Фильтрация декартового произведения. Определите аттрибуты двух и более объектов, связанных следующими отношениями:

(не) этот продавец (не) обслуживает (не) этого покупателя
(не) этот продавец (не) обслуживает (не) этот заказ
(не) этот покупатель (не) сделал (не) этот заказ
этот заказ - на (не) этот товар
(не) этот покупатель (не) сделал (не этот) заказ (не) этому продавцу
(не этот) покупатель (не) сделал (не этот) заказ (не этому) продавцу на (не) этот товар
эти продавец и покупатель - однофамильцы
эти продавец и покупатель живут в одном городе
этот продавец старше (младше, ровесник) этого покупателя

Рефлексивные связи. Определите аттрибуты двух и более объектов, связанных следующими отношениями

эти покупатели - однофамильцы
эти покупатели живут в одном городе
этот покупатель старше (младше, ровесник) другого покупателя

эти продавцы - однофамильцы
эти продавцы живут в одном городе
этот продавцы старше (младше, ровесник) другого продавца

этот заказ - на более крупную сумму, чем другой
этот заказ сделан раньше, чем другой
эти заказы обслуживает один продавец
эти заказы - одного покупателя
эти заказы - на один товар

это товар - дороже (дешевле, стоит столько же), чем другой
эти товары (не) произведены на одном предприятии

Декодирование.

Определите аттрибуты объектов по свойствам других (одного или нескольких) связанных с ними объектов.

Варианты задания

Определите фамилии продавцев,
обслуживавших дорогие заказы
обслуживавших кредитоспособных покупателей

Определите цену и наименования товаров

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

Группировка и групповые вычисления.
Определите значения групповых функций для заданного определения группы

Подсчитайте
средний, максимальный, минимальный возраст (дата рождения, сумма кредита) покупателя и количество покупателей

по всем покупателям
по всем категориям покупателям, перечисленных выше
для каждого города

Подсчитайте
средний, максимальный, минимальный процент комиссионных (возраст, дата рождения) продавца и количество продавцев

по всем продавцам
по всем категориям продавцев, перечисленных выше
для каждого города

Подчитайте
среднюю, максимальную, минимальную, совокупную сумму заказов и количество заказов

по всем заказам
по всем категориям заказов, перечисленных выше
данного покупателя
данного продавца
для каждого покупателя
для каждого продавца
для каждой пары покупатель-продавец

Подчитайте

минимальную, максимальную, среднюю цену товара
минимальное, максимальное, среднее, совокупное количество единиц товара, хранящихся на складе
количество единиц товара, по каждому типу товара

по всем товарам
по всем категориям товаров, перечисленным в

Фильтрация групп. Подсчитайте значения групповых функций, выводя лишь значения, попадающие в заданный интервал.

Вариант задания. Определить количество товаров, которых мало на складе (т.е. это количество меньше некоторого заданного числа - скажем, 100)

Предикаты, использующие выборку. Вложенные запросы

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

Варианты задания.
Выведите аттрибуты сравнительно молодых покупателей - т.е. покупателей, возраст которых меньше среднего значения по всем покупателям.

Выведите аттрибуты сравнительно дорогих товаров, по каждому типу товаров.

Отношение принадлежности. Определите аттрибуты объектов таких, что связанные с ними объекты принадлежат данной категории.

Варианты задания.

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

Кванторные предикаты. Определите аттрибуты объектов, для которых категория связанных с ними объектов (не)пуста.

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

(Связанные запросы).

(Внешние) объединения. Определите совпадающие аттрибуты объектов из разных таблиц.

Вариант задания. Определите фамилии и возраст покупателей и продавцев, проживающих в данном городе (например, Казани).

МОДИФИКАЦИЯ ТАБЛИЦ

Введите (если вы уже это не сделали раньше) по 5-10 записей в таблицы БД.


Удалите из таблиц заданные категории объектов.

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

Измените значения аттрибутов объектов заданной категории, выбирая в каждом случае разумные значения констант.

Вариант задания.

Повысьте кредит кредитоспособным покупателям
Увеличьте комиссионные низкооплачиваемым продавцам
Снизьте цены на просроченные товары


Задачи-многоходовки. Представления и трансакции.

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

ФАМИЛИЯ (30 символов)
ПАСПОРТ - № паспорта, 10 символов (очевидно, уникальный для каждого человека)
МАТЬ - № паспорта матери (NULL, если нет сведений)
ОТЕЦ - № паспорта отца (NULL, если нет сведений)
СУПРУГ - № паспорта жены/мужа (NULL, если нет сведений)
ДАТА - дата рождения (тип дата)
ДОХОД - ежемесячный доход (зарплата, стипендия и т.п.), вещественное 99.999.999,99
ГОРОД - город проживания (30 символов)
НАЛОГ - сумма подоходного налога, в % (целое)

Кроме того, в информацию о студентах дополнительно включаются сведения

ВУЗ - сокращенное название ВУЗа, 10 символов - например, КГУ’, КГМУ’ и т.п.
ПОЛ - один из символов М’,’Ж’

БД включает таблицы СТУДЕНТЫ, ОТЦЫ и МАТЕРИ, включающую информацию о студентах и их родителях, соответственно.
ПРИМЕЧАНИЕ. Как всегда, считаем, что допустимо применение имен на кириллице.


Выдать список фамилий студентов, с указанием фамилий и дохода родителей, упорядоченный по возрасту студентов. Студентов, чьи доходы ниже 100 руб., в список не включать.
Выдать упорядоченный по фамилиям список живущих в Казани, Москве и Самаре родителей, с указанием - мать это или отец, чей доход не превышает 300 руб.
Выдать все пары студентов-однофамильцев из КГУ, с указанием фамилии и, для обоих, паспортных данных. Дублирующиеся пары не включать.
Выбрать всю информацию об отцах, чьи доходы выше среднего по городу, в котором они живут.
Выдать список живущих вне Казани матерей, имеющих не менее - двух сыновей-студентов, учащихся в Казани.
Выбрать всю информацию об студентах, чьи доходы выше среднего для казанских студентов.

«Многоходовки» и трансакции (проще решать, используя редставления)

оформьте в виде трансакций все разумные, на Ваш взгляд, процедуры обработки справочной целостности для каждой связи в БД. Так, например, изменение ключа в базе СТУДЕНТ должно влечь соответствующее изменение ссылок в таблицах ОТЕЦ и МАТЬ.

Удалить информацию о студентах ВУЗА 'АГУ' а также их родителях - в случае, если у тех нет других детей-студентов (вариант посложнее - включить в число родителей студентов их бабушек и дедушек)

Вариант решения:
запомнить студентов нужного ВУЗа и ссылки на их отцов,
запомнить отцов, у которых все дети (если вообще есть таковые) учатся в нужном ВУЗе
удалить отцов, входящих в первый и второй список
удалить студентов нужного ВУЗа


Снизить на 5% налог родителям, имеющим более 3 детей-студентов, обучающихся в Казани

Вариант решения:
Разбив казанских студентов на группы детей одних родителей, отобрать и запомнить ссылки на тех отца и мать, для которых количество их детей больше 3
Изменить сведения о запомненных матерях
Изменить сведения о запомненных отцах

- Повысить на 50 руб. стипендию (т.е. доход) студентов, у которых нет хотя бы одного из родителей либо совокупный доход родителей не превышает 1000 руб.

Вариант решения:
Подсчитать и запомнить совокупный доход всех супружеских пар (вместе со ссылками на отца и мать), если он не более заданной суммы,
Изменить соответственно информацию о студентах, для которых ссылки на родителей либо пусты, либо попали в запомненный список

- Повысить на 5% налог отцам, у которых доход жены не ниже среднего для матерей, а совокупный доход детей превышает 1000 руб.

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


Проектирование не тривиальной БД.

Информационная система "Сборочное предприятие".

НЕФОРМАЛЬНАЯ МОДЕЛЬ. БАЗОВЫЕ ПОНЯТИЯ.

После тщательного изучения проблемной области и опроса экспертов - специалистов предприятия, вы обладаете следующей информацией о его структуре и функционировании.

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


Упражнение. Попробуйте сами выделить базовые понятия, их свойства и взаимосвязи на основе неформального и неполного описания. Разумеется, такое выделение далеко не однозначно. Обоснуйте (защитите) свой вариант, сравнив его с предложенным выше – в описании предметной области выделены слова, которые - на наш взгляд – соответствуют базовым понятиям этой области.

ВЗАИМОСВЯЗЬ ПОНЯТИЙ

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

Одно ПОДРАЗДЕЛЕНИЕ может включать в себя другие ПОДРАЗДЕЛЕНИЯ (цеха состоят из участков)
Каждый РАБОТНИК обладает некоторой ПРОФЕССИЕЙ
У каждого РАБОТНИКа (кроме директора) имеется непосредственный начальник (другой РАБОТНИК)
МАТЕРИАЛЫ участвуют в ОПЕРАЦИЯХ в качестве компонент
ИЗДЕЛИЯ также участвуют в ОПЕРАЦИЯХ в качестве компонент
ИЗДЕЛИЯ являются также результатом ОПЕРАЦИЙ
ОПЕРАЦИИ производится некоторым УЧАСТКОМ (их исполнителем )
РАСХОД всегда связан с некоторым МАТЕРИАЛОМ
РАСХОД материала осуществляется при выполнении некоторой ОПЕРАЦИИ
РАСХОД материала осуществляется некоторым участком - исполнителем операций
МАТЕРИАЛЫ поставляются по ДОГОВОРУ
ДОГОВОР заключается с ПОСТАВЩИКОМ
ПОСТАВКА осуществляется ПОСТАВЩИКОМ
ПОСТАВКА осуществляется на СКЛАД


МАТЕРИАЛЫ ( исходные материалы и детали производства)

- уникальный код материалы;
- наименование;
характеристика;
единица измерения
цена за единицу.


ИЗДЕЛИЯ ( готовые изделия и сборочные единицы собственного производства)

- уникальный код изделия;
- наименование;
- характеристика;
- (ненулевая) цена

СБОРКА (пооперационный процесс изготовления изделий и сборочных единиц из более простых компонент)

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

- номер участка исполнителя;

РАСХОД (нормы расхода материалов )

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

ЗАТРАТЫ ( нормы затрат труда)

- код изделия ;
- номер операции;
- номер цеха (исполнителя работ);
- номер участка;
- код профессии рабочего;
- код условий труда и тарифный разряд работы;
- дополнение - время на подготовку и заключение работы, в мин.;
- время штучное, в мин.

ТАРИФЫ

- уникальный код условий труда и тарифный разряд работы;
- часовая тарифная ставка, в рублях

ПРОФЕССИИ

- уникальный код профессии;
- наименование профессии.

ПОДРАЗДЕЛЕНИЯ

- номер цеха;
- наименование цеха или участка.



ПЛАН (производственный план предприятия)

- код изделия;
- распределение плана по всем месяцам - выпуск , в штуках, в январе, феврале и т.д.;
- дата начала действия плана.


ДОГОВОРА (договора на поставку компонент)

- код поставщика;
- уникальный номер договора;
- код компонента - материала или покупной детали;
- единица измерения;
- план поставки на год, в шт.;
- распределение плана по всем месяцам - поставки в январе, феврале и т.д.;;
- дата начала действия договора.

РАБОТНИКИ (личный состав, штат предприятия)

- номер цеха;
- табельный номер рабочего;
- код профессии;
- разряд рабочего;
- часовая тарифная ставка;
- семейное положение;
- фамилия с инициалами.

ВЫРАБОТКА (учет выработки работников)

- номер цеха;
- номер участка;
- код изделия;
- номер операции;
- табельный номер работника;
- количество годных деталей;
- количество бракованных деталей;
- процент оплаты брака;
- дата выполнения работ.


ПОСТАВЩИКИ

- уникальный код поставщика;
- наименование поставщика;
- адрес поставщика.

ПОСТАВКА компонент

- номер склада;
- код поставщика;
- код компоненты;
- единица измерения;
- количество ;
- дата поступления;
- уникальный номер документа.


ОТГРУЗКА готовой продукции

- номер склада;
- код покупателя;
- код готового изделия;
- единица измерения;
- количество;
- дата отгрузки;
- уникальный номер документа.

ПОКУПАТЕЛИ

- уникальный код покупателя;
наименование;
город;
- почтовый адрес.


СКЛАДЫ

- номер склада;
- фамилия материально ответственного лица;
- код детали - компоненты или изделия;
- единица измерения;
- количество, имеющееся на складе;
- дата последней операции.


ЗАРПЛАТА (бухгалтерский учет начисления и удержания по зарплате)

- табельный номер работника;
- сумма начисления;
- сумма удержания;
- дата выдачи.




ЗАКАЗЫ (договора на отгрузку готовой пpодукции покупателям)

- код покупателя;
- уникальный номер заказа;
- код изделия;
- единица измерения;
- план поставки на год, в шт.;
- распределение плана по всем месяцам - поставки в январе, феврале и т.д.;
дата начала действия договора.


Упражнение. Постройте формальную модель предприятия в форме базы данных, определив таблицы КОМПОНЕНТЫ, ИЗДЕЛИЯ и т.д., используя выделенные слова (код, наименование, характеристика, единица, цена и т.д.) в качестве имен полей (здесь и далее мы для удобства используем кириллические имена произвольной длины; если ваша СУБД не поддерживает соответствующие идентификаторы, используйте латинскую транскрипцию и сокращения). Выясните, какие поля (или группа полей) являются первичными и внешними ключами. Правила целостности и корректности значений полей и записей таблиц (в частности, допустимость неопределенных значений) определите самостоятельно, исходя из содержательного смысла таблиц и отношений.

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

Заказчик - директору: "По нашему договору №, от . ваше предприятие недопоставило изделий "". Если Вы не поставите требуемые изделия в течении дней, мы обратимся в суд"
Директор - заказчику "Минутку-минутку, сейчас уточнимУ меня почему-то стоит другая дата"
Директор - кладовщику "Сколько у нас на складе изделий ""? Не хватает?"
Директор - начальнику цеха "Сможем в течении дней собрать недостающие .. штук изделий ""?

Далее следуют обращения

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

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

Приложенные файлы

  • doc 7066459
    Размер файла: 216 kB Загрузок: 0

Добавить комментарий