Разработка и реализация базы данных библиотеки

ВВЕДЕНИЕ

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

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

Второй раздел посвящён проектированию базы данных в прикладной программе Enterprise Architect (создание таблиц, полей и связей между таблицами), а также экспорт БД в целевую СУБД, в нашем случае это MS SQL Server 2008 R2, построение диаграммы базы средствами MS SQL Server 2008 R2 и заполнение данными базы.

Третий раздел посвящен созданию запросов к БД на языке T-SQL. Используются простые запросы, включающие работу как с одной таблицей, так и продвинутые запросы, которые работают с несколькими таблицами в БД. Определяются запросы на выборку данных, обновление, удаление, создание данных.

В заключении приводятся основные результаты и выводы по работе.

Приложения содержат схему БД, полный скрипт её создания и код запросов в среде MS SQL Server 2008 R2.

1. АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ

 

1.1 Общие сведения

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

 

1.2 Основные сведения о предметной области

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

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

Основными функциональными возможностями являются:

учет книжного фонда

учет посетителей

учет выдачи книг

учет платных услуг

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

учет инвентаря.

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

 

1.3 Перечень хранимой информации: таблицы, поля, типы

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

Таблица 1 - Таблица данных

Название таблицы	Поле	Тип
Книга	Идентификационный номер	число
	Автор	текст
	Название	текст
	Код по классификатору	текст
	Год издания	число
	Объявленная стоимость	число
Посетители	Идентификационный номер	Число
	Фамилия	Текст
	Имя	Текст
	Отчество	Текст
	Год рождения	Число
	Домашний адрес	Текст
	Телефон	Текст
Выдача книг	Номер записи	Число
	Номер книги	Число
	Номер посетителя	Число
	Дата выдачи	Дата
	Дата возврата	Дата
Платные услуги	Идентификационный номер	Число
	Название услуги	Текст
	Цена	Число
Выручка по платным услугам	Идентификационный номер
	Номер услуги	Число
	Номер клиента	Число
	Количество	Число
	Итого	Число
Инвентарь	Идентификационный номер	Число
	Название	Текст
	Объявленная стоимость	Число

1.4 Выделение справочных и оперативных данных

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

Таблица 2 - Справочные и оперативные данные

Справочные таблицы	Инвентарь
	Выручка по платным услугам
Оперативные таблицы	Книги
	Посетители
	Выдача книг
	Платные услуги

1.5 Необходимые предполагаемые запросы к БД

Исходя из анализа возможных запросов к базе данных, составим список предполагаемых запросов:

. Поиск устаревших книг

. Поиск посетителей

. Поиск платных услуг

. Поиск и создание сводки по платным услугам за определенное время

1.6 Выводы по разделу

В данном разделе была проанализирована предметная область. Определена информация, хранимая в БД. Обозначены таблицы, поля и типы полей. Разделены таблицы на справочные и оперативные. Составлены необходимые запросы, которые нужно будет реализовать в БД.

запрос таблица база данные

2. МОДЕЛИРОВАНИЕ ПРЕДМЕТНОЙ ОБЛАСТИ

 

2.1 Общие сведения

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

 

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

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

Таблица 3 - Типы полей и данных БД

№	Сущность	Атрибут	Тип данных
1	Books	book_ID	bigint
		Author	varchar
		Name	Varchar
		Klass_kod	Varchar
		PressData	DateTime
		Cost	Decimal
2	Clients	Client_ID	Bigint
		Family	Varchar
		Name	Varchar
		LastName	Varchar
		Birthday	Datetime
		Adress	Varchar
		Phone	Varchar
3	BookOut_ID	Bigint
		Book_ID	Bigint
		Client_ID	Bigint
		DateOut	Datetime
		DateIN	Datetime
4	PlatUslug	plUsl_ID	Bigint
		Name	Varchar
		Cost	Decimal
5	plUslPrib	plUslP_ID	Bigint
		plUsl_ID	Bigint
		Client_ID	BigInt
		Count	Float
		Itogo	Float
6	Invent	Invent_ID	Bigint
		Name	Varchar
		Cost	decimal

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

Таблица 4 - Выделение первичного(PK) и внешнего(FK) ключей

№	Наименование таблицы	Первичный ключ(РК)	Внешний ключ(FK)
1	Books	Book_ID	-
2	Clients	Client_ID	-
3	BookOut	BookOut_ID	Book_ID, Client_ID
4	PlatUslug	plUsl_ID	-
5	plUslPrib	plUslP_ID	plUsl_ID, Client_ID
6	Invent	Invent_ID	-

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

 

2.3 Проектирование диаграммы сущность - связь в ЕА

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

1	BookOut	BookOut_ID(PK)	Bigint
		Book_ID(FK)	Bigint
		Client_ID(FK)	Bigint
		DateOut	Datetime
		DateIN	Datetime

В данной выше таблице наглядно видны связи между таблицами с помощью ключей. Далее нужно построить БД. Для этого собираем схему в Enterprise Architect <https://www.google.ru/search?newwindow=1&client=firefox-a&hs=Xcu&rls=org.mozilla:ru:official&channel=fflb&q=Enterprise+Architect&spell=1&sa=X&ei=9M5lVOPlOKrMyAPBqIC4Aw&ved=0CBoQvwUoAA>, указываем связи между таблицами. Получившаяся схема представлена в приложении А.

 

2.4 Подготовка диаграммы сущность-связь в ЕА к переносу на целевую СУБД и автоматизированная генерация кода SQL

Чтобы экспортировать схему, нужно сгенерировать код. Делается это путём следующих операций: нажатия на Tools-Database Engineering-Generate Package DDL.

Рисунок 1 - Генерация кода

Далее задаётся название БД и место сохранения. В данном окне выбирается несколько пунктов, сначала ставится галочка напротив Create Primary/Foreign Key Constraints для того, чтобы в базе данных в SQL были созданы ключи PK и FK. Дальше ставится галочка напротив Generate Index/Constraints для того, чтобы в БД были сгенерированы индексы. Индексы требуются для увеличения скорости работы БД. Generate Table Owner - для какого наименования БД будет использован полученный код. Дальше нажимаем Generate.

 

2.5 Создание БД в MS SQL Server. Перенос схемы из ЕА

Затем код экспортируется в среду SQL. Для начала создадим пустую БД.

Рисунок 2 - создание БД в среде SQL

После этого создадим пустой запрос и в него вставляем получившийся код, запускаем его.

Код удачно экспортирован. Теперь можно просмотреть результат применения кода.

Рисунок 3 - результат применения кода

Наблюдаем таблицы и ключи, ранее созданные в среде ЕА.

 

.6 Создание диаграммы средствами MS SQL Server и MS SQL Server Management Studio

Для простоты восприятия БД в среде SQL создадим диаграмму средствами SQL.

Рисунок 4 - схема БД в SQL

Построенная диаграмма аналогична диаграмме в ЕА.

 

2.7 Начальное заполнение данными БД

Для заполнения БД используется оператор Insert Into или можно использовать средства Management Studio для ручного ввода, заполняются несколько первых строк для того, чтобы продолжить работу с БД.

Рисунок 5 - заполнение БД

Выводы по разделу

В данном разделе была спроектирована БД в среде EA и визуально представлена, затем перенесена в среду SQL, где была заполнена данными и представлена в виде схемы средствами SQL.

3. СОЗДАНИЕ И ЗАПУСК БАЗОВЫХ ЗАПРОСОВ SQL

 

3.1 Общие сведения

В данном разделе будет проводиться создание и запуск простых запросов к базе данных в среде SQL. SQL запрос - это универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL является, прежде всего, информационно-логическим языком <#"868071.files/image006.gif">

Рисунок 6 - результат запроса

b)      Выбрать что-то и условие:

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

*[Library].[dbo].[Book] as aa.Author Like '%Толстой%'

Листинг 2 - запрос SQL

Рисунок 7 - результат запроса

3.3 Запрос на добавление и удаление записи

В БД записи добавляются с помощью оператора Insert. Значения можно вставлять перечислением с помощью слова values или перечислением их в круглых скобках через запятую или оператором select. Ниже представлен запрос SQL на вставку данных в таблицы и результат выполнения.

Пример добавления в таблицу строки с помощью команды INSERT. В данном случае это добавление нового автора.

INTO [Library].[dbo].[Book]

([Author]

,[Name]

,[Klass_kod]

,[cost]

,[PressData])

('Пушкин А.С.'

,'Руслан и Людмила'

,'Р1'

,200.5

,'01.01.2010')

Листинг 3 - добавление строки

Рисунок 8 - результат добавления

Если данные в БД необходимо обновить, то используется оператор UPDATE:

[Library].[dbo].[Book]

[cost] = 300

Author like '%Чехов%'

Листинг 4 - обновление записи

Рисунок 9 - результат обновления

Рисунок 10 - данные после обновления

Чтобы удалить запись из БД, необходимо использовать оператор Delete.

Для удаления акции, которая давно закончилась, воспользуемся оператором Delete.

Листинг 5 - удаление записи

Рисунок 11 - результат удаления

 

Выводы по разделу

В данном разделе созданы и запущены основные запросы по работе с одной таблицей, были добавлены новые книги, также проведено удаление строки из таблицы и обновление стоимости книги.

ЗАКЛЮЧЕНИЕ

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

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

Второй раздел посвящён проектированию базы данных в прикладной программе Enterprise Architect (создание таблиц, полей и связей между таблицами), а также экспорту БД в целевую СУБД, в нашем случае это MS SQL Server 2008 R2, построению диаграммы базы средствами MS SQL Server 2008 R2 и заполнение данными базы.

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

В заключении приводятся основные результаты и выводы по работе.

Приложения содержат схему БД и полный скрипт её создания в среде MS SQL Server 2008 R2. В ходе курсового проектирования все поставленные задачи были успешно выполнены.

СПИСОК ЛИТЕРАТУРЫ

Основная литература:

.       Джеймс Р.Грофф, Пол Н. Вайнберг. SQL: Полное руководство . - К.: BHV, 1998. - 608 c.

2.      Ладани Ханс. SQL. Энциклопедия пользователя. - М.: ДиаСофт, 1998. - 624 с

Дополнительная литература:

.       Уанкуп Д. MS SQL Server 2008 в подлиннике. - М.: Издательский дом “Вильямс”, 2009. - 912 с.

.       Бек, Кент Шаблоны реализации корпоративных приложений; М.: Вильямс, 2008. -369 c.

5.      Роберт Сигнор, Михаэль О. Стегман. Использование ODBC для доступа к базам данных. - М.: БИНОМ, 1995. - 384 с.

.        Кузнецов С.Д. Стандарты языка реляционных баз данных SQL: краткий обзор // СУБД. - N 2, 1996. - C.6-36

.        Венкатрао М., Пиццо М. SQL/CLI - новый стиль связывания для SQL // СУБД. - N 2, 1996. - C.37-44

.        Боуман Дж., Эмерсон С., Дарновски М. Практическое руководство по SQL. - М.: Диалектика, 1997. - 320 с.

.        Райан Стивенс, Рональд Р. Плю. SQL. - М.: БИНОМ, 1997. - 400с.

СПИСОК СОКРАЩЕНИЙ

. EA - Enterprise Architect

.        БД - база данных

.        СУБД - система управления базами данных

12.    PK - первичный ключ

13.    FK - вторичный ключ

14.    SQL - Structured Query Language (Структурированный язык запросов)

ПРИЛОЖЕНИЕ А

 

Схема БД

 

ПРИЛОЖЕНИЕ Б

 

Листинг БД

[Library]ANSI_NULLS ONQUOTED_IDENTIFIER ONANSI_PADDING ON

//Создание таблицы КнигиTABLE [dbo].[Book](

[book_ID] [bigint] IDENTITY(1,1) NOT NULL,

[Author] [varchar](max) NOT NULL,

[Name] [varchar](max) NOT NULL,

[Klass_kod] [varchar](50) NOT NULL,

[cost] [decimal](10, 2) NOT NULL,

[PressData] [datetime] NOT NULL,[PK_Book] PRIMARY KEY CLUSTERED

(

[book_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]ANSI_PADDING OFF[Library]ANSI_NULLS ONQUOTED_IDENTIFIER ONANSI_PADDING ON

//Создание таблицы КлиентыTABLE [dbo].[Clients](

[Client_ID] [bigint] IDENTITY(1,1) NOT NULL,

[Family] [varchar](max) NOT NULL,

[Name] [varchar](max) NOT NULL,

[LastName] [varchar](max) NULL,

[birthday] [datetime] NOT NULL,

[Adress] [varchar](max) NOT NULL,

[Phone] [varchar](max) NULL,[PK_Clients] PRIMARY KEY CLUSTERED

(

[Client_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]ANSI_PADDING OFF[Library]ANSI_NULLS ONQUOTED_IDENTIFIER ON

//Создание таблицы Выдача книгTABLE [dbo].[BookOut](

[BookOut_id] [bigint] IDENTITY(1,1) NOT NULL,

[Book_ID] [bigint] NULL,

[DataOut] [datetime] NOT NULL,

[DateIN] [datetime] NOT NULL,

[Client_ID] [bigint] NOT NULL,[PK_BookOut] PRIMARY KEY CLUSTERED

(

[BookOut_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]TABLE [dbo].[BookOut] WITH CHECK ADD CONSTRAINT [FK_BookOut_Book] FOREIGN KEY([Book_ID])[dbo].[Book] ([book_ID])TABLE [dbo].[BookOut] CHECK CONSTRAINT [FK_BookOut_Book]TABLE [dbo].[BookOut] WITH CHECK ADD CONSTRAINT [FK_BookOut_Clients] FOREIGN KEY([Client_ID])[dbo].[Clients] ([Client_ID])TABLE [dbo].[BookOut] CHECK CONSTRAINT [FK_BookOut_Clients][Library]ANSI_NULLS ONQUOTED_IDENTIFIER ONANSI_PADDING ON

//Создание таблицы ИнвентарьTABLE [dbo].[Invent](

[Invent_ID] [bigint] IDENTITY(1,1) NOT NULL,

[Cost] [decimal](10, 2) NOT NULL,

[Name] [varchar](max) NOT NULL,[PK_Invent] PRIMARY KEY CLUSTERED

(

[Invent_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]ANSI_PADDING OFF[Library]ANSI_NULLS ONQUOTED_IDENTIFIER ONANSI_PADDING ON

//Создание таблицы Платные услугиTABLE [dbo].[PlatUslug](

[Name] [varchar](max) NOT NULL,

[Cost] [decimal](10, 2) NOT NULL,[PK_PlatUslug] PRIMARY KEY CLUSTERED

(

[plUsl_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]ANSI_PADDING OFF[Library]ANSI_NULLS ONQUOTED_IDENTIFIER ON

//Создание таблицы Прибыль по платным услугамTABLE [dbo].[plUslPrib](

[plUslP_ID] [bigint] IDENTITY(1,1) NOT NULL,

[plUsl_ID] [bigint] NOT NULL,

[Client_ID] [bigint] NOT NULL,

[Count] [float] NOT NULL,

[Itogo] [float] NOT NULL,[PK_plUslPrib] PRIMARY KEY CLUSTERED

(

[plUslP_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]TABLE [dbo].[plUslPrib] WITH CHECK ADD CONSTRAINT [FK_plUslPrib_Clients] FOREIGN KEY([Client_ID])[dbo].[Clients] ([Client_ID])TABLE [dbo].[plUslPrib] CHECK CONSTRAINT [FK_plUslPrib_Clients]TABLE [dbo].[plUslPrib] WITH CHECK ADD CONSTRAINT [FK_plUslPrib_PlatUslug] FOREIGN KEY([plUslP_ID])[dbo].[PlatUslug] ([plUsl_ID])TABLE [dbo].[plUslPrib] CHECK CONSTRAINT [FK_plUslPrib_PlatUslug]