Работа с MicrosoftSQLServer с использованием языка SQL в интерактивном режиме
Мы начнем с описания базы данных pubs, которая будет использоваться на практических занятиях. Эта база данных имитирует информационное хранилище издательской компании и состоит из 11 таблиц: authors (авторы), discounts (скидки), employee (служащие), jobs (задания), pub_info (информация об издательствах), publishers (издательства), roysched (авторские гонорары), sales (продажи), stores (магазины), titleauthor (название-автор), titles (названия).
Таблицы определены следующим образом:
Таблица authors
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| au_id | id | нет | нет |
| au_lname | varchar(40) | нет | нет |
| au_fname | varchar(20) | нет | нет |
| phone | char(12) | нет | 'UNKNOWN' |
| address | varchar(40) | да | нет |
| city | varchar(20) | да | нет |
| state | char(2) | да | нет |
| zip | char(5) | да | нет |
| contract | bit | нет | нет |
Первичным ключом объявлен столбец au_id. Определены два ограничения целостности на столбцах au_id (au_idLIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]') и zip (zipLIKE '[0?9][0-9][0-9][0-9][0-9]').
К типу данных id нужно относиться как к типу символьных строк (CHAR(9)). Тип varchar - аналог CHARACTERVARYINGSQL/92. Тип bit - нестандартный, и мы не будем использовать столбец contract в наших примерах.
Таблица discounts
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| discounttype | varchar(40) | нет | нет |
| stor_id | char(4) | да | нет |
| lowqty | smallint | да | нет |
| highqty | smallint | да | нет |
| discount | float | нет | нет |
Эта таблица представляет интерес тем, что в ней отсутствует первичный ключ (если внимательно посмотреть на требования стандарта SQL, то можно увидеть, что такая ситуация допускается, хотя на практике встречается очень редко). Для таблицы определено ссылочное ограничение: столбец stor_id ссылается на первичный ключ stor_id таблицы stores.
Таблица employee
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| emp_id | empid | нет | нет |
| fname | varchar(20) | нет | нет |
| minit | char(1) | да | нет |
| lname | varchar(30) | нет | нет |
| job_id | smallint | нет | 1 |
| job_lvl | tinyint | нет | 10 |
| pub_id | char(4) | нет | '9952' |
| hire_date | datetime | нет | GETDATE() |
Первичным ключом объявлен столбец emp_id. Имеются два ссылочных ограничения: столбец job_id ссылается на первичный ключ job_id таблицы jobs, столбец pub_id ссылается на первичный ключ pub_id таблицы publishers. Определено ограничение для столбца emp_id: (emp_idLIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' ORemp_idLIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'). Тем самым, видно, что тип emp_id на самом деле есть varchar(9). Тип данных tinyint является нестандартным, и мы не будем использовать столбец job_lvl. Функция GETDATE() является аналогом стандартной функции CURRENT_DATE, т.е. значением по умолчанию столбца hire_date является текущая дата.
Таблица jobs
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| job_id | smallint | нет | IDENTITY(1,1) |
| job_desc | varchar(50) | нет | 'NewPosition - titlenotformalizedyet' |
| min_lvl | tinyint | нет | нет |
| max_lvl | tinyint | нет | нет |
Первичный ключ - job_id. Значение по умолчанию этого столбца вырабатывается нестандартной функцией IDENTITY, генерирующей уникальные целые значения.
Таблица pub_info
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| pub_id | char(4) | нет | нет |
| logo | image | да | нет |
| pr_info | text | да | нет |
Первичный ключ таблицы - pub_id. Этот же столбец является и внешним ключом и ссылается на первичный ключ pub_id таблицы publishers. Типы данных image и text являются нестандартными, и мы не будем использовать столбцы logo и pr_info.
Таблица publishers
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| pub_id | char(4) | нет | нет |
| pub_name | archar(40) | да | нет |
| city | varchar(20) | да | нет |
| state | char(2) | да | нет |
| country | varchar(30) | да | 'USA' |
Первичный ключ - pub_id. Для этого столбца, кроме того, определено следующее ограничение: (pub_id = '1622' ORpub_id = '0877' ORpub_id = '0736' ORpub_id = '1389' ORpub_idLIKE '99[0-9][0-0]').
Таблица roysched
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| title_id | tid | нет | нет |
| lorange | int | да | нет |
| hirange | int | да | нет |
| royalty | int | да | нет |
В этой таблице отсутствует первичный ключ. Объявлено одно ссылочное ограничение: столбец title_id ссылается на первичный ключ title_id таблицы titles. Тип tid - синоним char(6), тип int - синоним стандартного типа INTEGER.
Таблица sales
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| stor_id | char(4) | нет | нет |
| ord_num | varchar(20) | нет | нет |
| ord_date | datetime | нет | нет |
| qty | smallint | нет | нет |
| payterms | varchar(12) | нет | нет |
| title_id | tid | нет | нет |
Первичный ключ таблицы образует комбинация полей stor_id, ord_num, title_id. Определены два внешних ключа: столбец stor_id ссылается на первичный ключ stor_id таблицы stores, а столбец title_id - на первичный ключ title_id таблицы titles.
Таблица stores
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| stor_id | char(4) | нет | нет |
| stor_name | varchar(40) | да | нет |
| stor_address | varchar(40) | да | нет |
| city | varchar(20) | да | нет |
| state | char(2) | да | нет |
| zip | char(5) | да | нет |
Первичным ключом является столбец stor_id.
Таблица titleauthor
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| au_id | id | нет | нет |
| title_id | tid | нет | нет |
| au_ord | tinyint | да | нет |
| royaltyper | int | да | нет |
Первичный ключ составляет комбинация столбцов au_id, title_id.
Таблица titles
| Имя столбца | Тип данных | Возможность содержать NULL | Значение по умолчанию |
| title_id | tid | нет | нет |
| title | varchar(80) | нет | нет |
| type | char(12) | нет | 'UNDECIDED' |
| pub_id | char(4) | да | нет |
| price | money | да | нет |
| advance | money | да | нет |
| royalty | int | да | нет |
| ytd_sales | int | да | нет |
| notes | varchar(200) | да | нет |
| pubdate | datetime | нет | GETDATE() |
Первичный ключ - title_id. Внешний ключ pub_id ссылается на первичный ключ pub_id таблицы publishers. Тип money - нестандартный, и мы не будем использовать столбцы price и advance.
Содержимое базы данных можно посмотреть в приложении B руководства по TransactSQL, входящего в оперативно доступную документацию по MicrosoftSQLServer, а также в прилагаемом тексте.
Слушатели будут работать с базой данных pubs с рабочих станций. Для каждого слушателя будет использоваться одно и то же входное имя (admin) и один и тот же пароль (adminnt). Следует учитывать, что пользователь admin обладает привилегиями администратора WindowsNT. Поэтому, чтобы не разрушить систему, слушателям не следует выполнять какие-либо действия, не предусмотренные программой занятий, без согласования с преподавателями.
Для доступа к базе данных будет использоваться программа MicrosoftQuery, позволяющая, в частности, через средства ODBC работать с MSSQLServer. Правила вызова MSQuery с обеспечением общего доступа к базе данных pubs будут продемонстрированы преподавателем.
