База данных фирмы, проводящей аукционы

Содержание

Приложения 1 – 15……………………………………………………………. 42

Введение

SQL Server 2000 является довольно сложным продуктом, работу с которым можно рассматривать с разных сторон. В частности, можно выделить два основных раздела работы с сервером, каждый из которых при ближайшем рассмотрении может быть легко разделен на более мелкие блоки: О администрирование; О программирование.

Администрирование SQL Server 2000 в свою очередь можно разделить на две части: администрирование собственно сервера и администрирование баз данных. Таким образом, администрирование баз данных представляет собой отдельную область работы с SQL Server 2000. Оно включает разработку структуры базы данных, ее реализацию, проектирование системы безопасности, создание пользователей базы данных, предоставление им прав доступа, создание объектов и т. д. Кроме того, администратор базы данных должен периодически создавать резервные копии, выполнять проверку целостности данных и следить за размером файлов как самой базы данных, так и журнала транзакций. Указанный список можно легко продолжить

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

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

1. Анализ предметной области

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

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

2. Концептуальное проектирование базы данных

2.1 Разработка схемы базы данных

2.2 Разработка структуры таблиц

Описание таблиц базы данных

2.3 Описание атрибутов таблиц

Описание атрибутов  таблицы Продавцы

Описание атрибутов таблицы Изделия

Описание атрибутов  таблицы Аукционы

Описание атрибутов  таблицы Покупателя

Описание атрибутов  таблицы Изделия на аукционах

3. Реализация базы данных  в среде СУБД MS SQL Server 2000

Структурой хранения данных в SQL Server 2000 является база данных (database). Вся работа SQL Server 2000 сводится к управлению базами данных (БД). Системные  данные сервера, отвечающие за его функционирование, также хранятся в базах данных. Базу данных SQL Server 2000 можно рассматривать с двух сторон: физической и логической. При работе с любой базой данных SQL Server 2000 – пользовательской или системной – действуют одни и те же механизмы.

Физическая  база данных представляет собой набор файлов, расположенных на  диске. С этими файлами можно выполнять любые операции, разрешенные для обычных файлов: копирование, переименование, удаление и т. д. Конечно, делать этого не стоит, но все же выполнение перечисленных операций в случае необходимости возможно. Физическая структура базы данных описывает количество файлов данных и журнала транзакций, из которых состоит база данных, их первоначальный и текущий размер, положение на диске, имя, расширение, шаг приращения и некоторые другие параметры. Эти параметры необходимы только для правильного восприятия SQL Server 2000 базы данных. Для пользователей, работающих с базой данных, в подавляющем большинстве случаев ее физическая структура не имеет значения.

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

3.1 Создание базы данных

Создание базы данных возможно несколькими способами:

• средствами языка Transact-SQL;
• с помощью графической утилиты Enterprise Manager
• с помощью мастера создания базы данных Create Database Wizard.

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

Помимо этого, в системной  таблице sysdatabases системной базы данных master SQL Server создается новая строка, которая описывает новую базу данных. В столбце filename этой строки содержится полный путь и имя первичного файла базы данных. Всю остальную информацию о параметрах базы данных, в том числе о количестве и размещении файлов данных и журнала транзакций, сервер получает из системных таблиц базы данных, размещенных в первичном файле.

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

Рисунок 1. Содержимое папки Databases в SQL Server 2000.

Создание новой базы данных выполняется с помощью  окна Database Properties (рисунок 2). Открыть это окно можно разными способами:

• выбрав в контекстном меню папки Databases команду New Database;
• щелкнув правой кнопкой мыши на пустом пространстве правой части и выбрав в открывшемся контекстном меню команду New Database;
• нажав в панели инструментов Enterprise Manager кнопку New Database;
• выбрав в меню Action (Действия ) команду New Database.

Окно свойств базы данных Database Properties имеет три вкладки. Первая вкладка General (рисунок 2) предназначена для указания имени базы данных и сопоставления, которое будет использоваться для базы данных. Остальные элементы управления вкладки General предназначены для предоставления пользователю различной информации о базе данных. На момент создания базы данных этой информации еще не существует, и поэтому указываются значения Unknown (неизвестно) и None (нет).

Рисунок 2. Окно создания новой базы данных, вкладка General.

При выборе имени базы данных, которое должно быть введено в поле Name, следует придерживаться тех же правил, которые используются при непосредственной работе с командой Create Database. Сопоставление, которое будет иметь база данных, выбирается с помощью раскрывающегося списка Collation name. По умолчанию список содержит значение (Server default), что предписывает применять для базы данных то же сопоставление, которое было указано на уровне сервера при установке SQL Server 2000. Однако можно выбрать и любое другое сопоставление.

Вкладка Data Files (рисунок 3) предназначена для определения файлов данных, из которых будет состоять создаваемая база данных. В верхней части вкладки Data Files расположена таблица Database files, с помощью которой собственно и определяются файлы базы данных. В столбце File Name указывается логическое имя файла, в столбце Location задается полный путь и имя файла операционной системы. Отметим, что указанный в столбце Location файл не должен существовать на момент создания базы данных. Путь и имя файла могут быть введены вручную или выбраны с помощью окна Locate Database File, открыть которое можно с помощью кнопки, расположенной в левой части столбца Location.

Рисунок 3. Окно создания новой базы данных, вкладка Data Files.

В столбце Initial size (MB) находится первоначальный размер, который файл будет иметь непосредственно после создания базы данных. Если отсутствует какой-либо суффикс, то подразумевается, что значение указано в мегабайтах.

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

Помимо сведений, указываемых  в таблице Database files, файлы базы данных имеют дополнительные свойства, такие, как максимальный размер и шаг прироста. Управление этими свойствами осуществляется с помощью группы элементов управления File properties, расположенной в нижней части вкладки Data Files.

Вкладка Transaction Log (рисунок 4) предназначена для управления файлами журнала транзакций. Эта вкладка в значительной степени напоминает вкладку Data Files. Единственное различие между ними состоит в том, что при определении файлов журнала транзакций нельзя работать с группами файлов.

Рисунок 4. Окно создания новой базы данных, вкладка Transaction Log.

После того как все  файлы базы данных будут определены, а также указано имя базы данных и сопоставление, остается только нажать кнопку ОК и Enterprise Manager приступит к непосредственному созданию базы данных. Для этого он сгенерирует код команды create database на основе введенных пользователем значений и выполнит его. Рассмотрение создания базы данных средствами Enterprise Manager можно считать оконченным.

3.2 Основные принципы создания  таблиц

Вначале таблицы необходимо создать. Во время этой операции пользователь определяет имя таблицы, имена столбцов, тип хранимых в них данных, значения по умолчанию, возможность хранения неопределенных значений, первичный и внешний ключи и некоторые другие свойства. Создание таблиц в SQL Server 2000 возможно либо с помощью графического интерфейса Enterprise Manager, либо с помощью команд Transact-SQL. К сожалению, мастера создания таблиц в SQL Server 2000 нет.

При создании таблиц пользователь может для столбцов, помимо задания  базовых свойств, таких, как имя, тип данных, размер и точность, указать ограничения целостности. Ограничения целостности (constraints) – это механизм контроля значений, которые могут храниться в полях строки. В SQL Server 2000 поддерживаются следующие ограничения целостности:

• Check – с помощью логических условий налагает ограничение на значения,  которые могут храниться в столбце;
• Null – задает возможность хранения неопределенных значений;
• Default – определяет значение по умолчанию;
• Unique – гарантирует уникальность значений в столбце;
• Primary Key – определяет первичный ключ;
• Foreign Key – определяет внешний ключ;
• No Action – предписывает не выполнять в зависимой таблице никаких действий при удалении или обновлении строк в главной таблице;
• Cascade – в данном случае будет осуществляться каскадное изменение значений в зависимой таблице при внесении изменений в главную таблицу.

Каждая база данных имеет  свой собственный набор таблиц, посмотреть который можно средствами Enterprise Manager, открыв папку Tables. При открытии папки Tables базы данных в правом окне Enterprise Manager появляется список таблиц со следующими атрибутами: имя таблицы, владелец (обычно владелец базы данных data base owner-dbo), тип (пользовательская или системная) и дата создания таблицы. Для просмотра самой таблицы следует дважды щелкнуть по ней или в ее контекстном меню исполнить команду Properties.

Рисунок 5. Окно свойств таблицы (Table Properties) базы данных

В открывшемся окне представлены все свойства таблицы и ее столбцов. Кнопка Permission позволяет просмотреть и отредактировать права доступа к таблице: SELECT, INSERT, UPDATE, DELETE, EXES и DRI. Щелкнув по столбцу таблицы, можно просмотреть и отредактировать права доступа к столбцу. Если установлена на сервере служба MSSearch, то с помощью вкладки Full-text Indexing можно отредактировать параметры этой службы.

Создание таблицы выполняется  с помощью окна New Table (рисунок 8), для открытия которого достаточно в контекстном меню папки Tables выбрать команду New Table.

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

Рисунок 6. Окно New Table для создания таблицы базы данных

Создание таблицы выполняется  с помощью окна New Table (рисунок 6), для открытия которого достаточно в контекстном меню папки Tables выбрать команду New Table.

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

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

В SQL Server 2000 имеется средство, облегчающее понимание структуры базы данных, позволяющее наглядно представлять структуру таблиц и связей между ними. Это средство называется диаграммой. Диаграммы определены только на уровне Enterprise Manager и являются надстройкой над объектами базы данных.

При выборе первого из них столбец конфигурируется  в качестве уникального глобального  идентификатора строки, что соответствует  указанию ключевого слова rowguidcol при  описании столбца в команде create table. Таким образом, при добавлении в таблицу новой строки в соответствующий столбец будет автоматически помешаться уникальное значение. Только один столбец в таблице может быть сконфигурирован как уникальный глобальный идентификатор строки. Так же можно задавать ограничения с помощью команды Check Constrains

Рисунок 7. Ограничения для таблицы «Аукционы»

После того как будут  сконфигурированы параметры всех столбцов, необходимо сохранить сконфигурированную таблицу. Для этого достаточно нажать кнопку Save, расположенную в панели инструментов. При этом будет выведено окно Choose Name, с помощью которого следует ввести имя, которое будет присвоено сконфигурированной таблице. На этом работу по созданию таблиц можно закончить. Однако можно определить в таблице ограничения целостности, проиндексировать те или иные столбцы, а также указать в какой группе файлов должна быть расположена таблица. Для управления этими свойствами используется окно Properties (Свойства), которое открывается нажатием кнопки Table and Index Properties (Свойства таблиц и индексов) на панели инструментов.

Для просмотра созданных  диаграмм необходимо в окне Enterprise Manager выбрать требуемую базу данных и открыть папку Diagram. Диаграмма не имеет свойств, для просмотра диаграммы необходимо дважды щелкнуть на ней левой клавишей мыши.(Рисунок 8)

Рисунок 8. Окно просмотра диаграммы.

4. Доступ и обработка данных с помощью утилиты Enterprise Manager

4.1 Создание представлений для доступа к данным

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

Рисунок 9. Окно списка представлений базы данных.

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

Создание представлений  в SQL Server 2000 можно выполнить различными методами:

• с помощью Enterprise Manager;
• с помощью мастера Create View Wizard;
• средствами Transact-SQL.

Чаще для создания представлений используется графический  интерфейс утилиты Enterprise Manager. Это  обеспечивает быстрое и наглядное  создание представлений любой сложности. Пользователи, не имеющие опыта в  создании представлений, могут воспользоваться мастером, что максимально упрощает создание представления за счет разбиения процесса создания на несколько шагов, каждый из которых снабжает пользователя подсказками.

Рисунок 10. Создание представлений

При работе с Enterprise Manager список представлений, имеющихся в базе данных, находится в папке Views (Представления). Как видно из рисунка 11, для каждого представления указывается его Имя (столбец Name), владелец (столбец Owner), тип (type) и дата создания (Create Date). Представление может быть отнесено к одному из двух типов – системному или пользовательскому.

Для создания нового представления  достаточно выбрать в контекстном  меню папки Views команду New View (Новое представление) или нажать на кнопку New (Создать) на панели инструментов.

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

4.2 Создание и управление индексами

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

Индекс – это своего рода предметные указатели для таблиц баз данных.

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

В SQL Server 2000 реализованы эффективные алгоритмы поиска нужного значения в упорядоченной последовательности данных. Одним из таких алгоритмов является алгоритм деления пополам.

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

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