Введение в стандарты языка баз данных SQL

       

Работа с MicrosoftSQLServer с использованием языка SQL в интерактивном режиме


Мы начнем с описания базы данных pubs, которая будет использоваться на практических занятиях. Эта база данных имитирует информационное хранилище издательской компании и состоит из 11 таблиц: authors (авторы), discounts (скидки), employee (служащие), jobs (задания), pub_info (информация об издательствах), publishers (издательства), roysched (авторские гонорары), sales (продажи), stores (магазины), titleauthor (название-автор), titles (названия).

Таблицы определены следующим образом:

Таблица authors

Имя столбцаТип данныхВозможность содержать NULLЗначение по умолчанию
au_ididнетнет
au_lnamevarchar(40)нетнет
au_fnamevarchar(20)нетнет
phonechar(12)нет'UNKNOWN'
addressvarchar(40)данет
cityvarchar(20)данет
statechar(2)данет
zipchar(5)данет
contractbitнетнет



Первичным ключом объявлен столбец 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Значение по умолчанию
discounttypevarchar(40)нетнет
stor_idchar(4)данет
lowqtysmallintданет
highqtysmallintданет
discountfloatнетнет

Эта таблица представляет интерес тем, что в ней отсутствует первичный ключ (если внимательно посмотреть на требования стандарта SQL, то можно увидеть, что такая ситуация допускается, хотя на практике встречается очень редко). Для таблицы определено ссылочное ограничение: столбец stor_id ссылается на первичный ключ stor_id таблицы stores.

Таблица employee

Имя столбцаТип данныхВозможность содержать NULLЗначение по умолчанию
emp_idempidнетнет
fnamevarchar(20)нетнет
minitchar(1)данет
lnamevarchar(30)нетнет
job_idsmallintнет1
job_lvltinyintнет10
pub_idchar(4)нет'9952'
hire_datedatetimeнет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_idsmallintнетIDENTITY(1,1)
job_descvarchar(50)нет'NewPosition - titlenotformalizedyet'
min_lvltinyintнетнет
max_lvltinyintнетнет

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

Имя столбцаТип данныхВозможность содержать NULLЗначение по умолчанию
pub_idchar(4)нетнет
logoimageданет
pr_infotextданет

Первичный ключ таблицы - pub_id. Этот же столбец является и внешним ключом и ссылается на первичный ключ pub_id таблицы publishers. Типы данных image и text являются нестандартными, и мы не будем использовать столбцы logo и pr_info.
Таблица publishers

Имя столбцаТип данныхВозможность содержать NULLЗначение по умолчанию
pub_idchar(4)нетнет
pub_namearchar(40)данет
cityvarchar(20)данет
statechar(2)данет
countryvarchar(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_idtidнетнет
lorangeintданет
hirangeintданет
royaltyintданет
<


В этой таблице отсутствует первичный ключ. Объявлено одно ссылочное ограничение: столбец title_id ссылается на первичный ключ title_id таблицы titles. Тип tid - синоним char(6), тип int - синоним стандартного типа INTEGER.
Таблица sales

Имя столбцаТип данныхВозможность содержать NULLЗначение по умолчанию
stor_idchar(4)нетнет
ord_numvarchar(20)нетнет
ord_datedatetimeнетнет
qtysmallintнетнет
paytermsvarchar(12)нетнет
title_idtidнетнет

Первичный ключ таблицы образует комбинация полей stor_id, ord_num, title_id. Определены два внешних ключа: столбец stor_id ссылается на первичный ключ stor_id таблицы stores, а столбец title_id - на первичный ключ title_id таблицы titles.
Таблица stores

Имя столбцаТип данныхВозможность содержать NULLЗначение по умолчанию
stor_idchar(4)нетнет
stor_namevarchar(40)данет
stor_addressvarchar(40)данет
cityvarchar(20)данет
statechar(2)данет
zipchar(5)данет

Первичным ключом является столбец stor_id.
Таблица titleauthor

Имя столбцаТип данныхВозможность содержать NULLЗначение по умолчанию
au_ididнетнет
title_idtidнетнет
au_ordtinyintданет
royaltyperintданет

Первичный ключ составляет комбинация столбцов au_id, title_id.
Таблица titles

Имя столбцаТип данныхВозможность содержать NULLЗначение по умолчанию
title_idtidнетнет
titlevarchar(80)нетнет
typechar(12)нет'UNDECIDED'
pub_idchar(4)данет
pricemoneyданет
advancemoneyданет
royaltyintданет
ytd_salesintданет
notesvarchar(200)данет
pubdatedatetimeнетGETDATE()

Первичный ключ - title_id. Внешний ключ pub_id ссылается на первичный ключ pub_id таблицы publishers. Тип money - нестандартный, и мы не будем использовать столбцы price и advance.
Содержимое базы данных можно посмотреть в приложении B руководства по TransactSQL, входящего в оперативно доступную документацию по MicrosoftSQLServer, а также в прилагаемом тексте.
Слушатели будут работать с базой данных pubs с рабочих станций. Для каждого слушателя будет использоваться одно и то же входное имя (admin) и один и тот же пароль (adminnt). Следует учитывать, что пользователь admin обладает привилегиями администратора WindowsNT. Поэтому, чтобы не разрушить систему, слушателям не следует выполнять какие-либо действия, не предусмотренные программой занятий, без согласования с преподавателями.
Для доступа к базе данных будет использоваться программа MicrosoftQuery, позволяющая, в частности, через средства ODBC работать с MSSQLServer. Правила вызова MSQuery с обеспечением общего доступа к базе данных pubs будут продемонстрированы преподавателем.

Содержание раздела