Проектирование базы данных книжной библиотеки университета

Размещено на http://www.allbest.ru/

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

Вербальное описание

Тема моей курсовой работы - проектирование базы данных книжной библиотеки университета. Сперва необходимо решить, какие сущности будет присутствовать в такой модели.

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

Если это библиотека, то в ней должны быть читатели со своими читательскими билетами. Если это университетская библиотека, то такими читателями будут студенты и преподаватели. Студенты имеют Ф.И.О., курс, факультет, группу. Преподаватели имеют Ф.И.О., телефон (может быть и нет), электронный адрес (может быть и нет), кафедру (может быть и нет, если преподаватель не из нашего университета). Кафедры относятся к определенному факультету.

Также в библиотеке есть сотрудники со своими именами и должностями. Кстати они и будут работать с этой базой данных, но об этом позже. Сотрудники работают в определенную смену.

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

Цель разработки.

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

Пользователи базы данных.

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

На какие вопросы отвечает система

Система отвечает на довольно большое количество вопросов, например:

1) кто и когда взял книгу из библиотеки;

2) кто является автором определенной книги;

3) в какую смену работает определенный сотрудник;

4) кто принимал книги в определенную смену какого-то дня;

5) какие книги написал определенный автор;

6) какие кафедры принадлежал какому-то факультету;

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

2. Описание бизнес-логики (ER-Win)

Таблица 1. Название сущности-признаки

Имя сущности	Признаки
Книга (Book)	Код, название, код автора, код издательства, код стеллажа
Стеллаж (Stellazh)	Код, тематика
Автор (Author)	Код, фамилия, имя, отчество
Издательство (Izdatelstva)	Код, наименование издательства
Сотрудники (Employees)	Код, фамилия, имя, отчество, должность, смена
Смена (Smena)	Код, время начала смены, время окончания смены
Студенты (Students)	Код, фамилия, имя, отчество, курс, код факультета, группа, код читательского билета
Преподаватели (Teachers)	Код, фамилия, имя, отчество, телефон, E-mail, код кафедры, код читательского билета
Факультет (Fakultet)	Код, название факультета
Кафедра (Kafedra)	Код, название кафедры, код факультета
Читатели (Chitateli)	Код, профессия читателя (студент или преподаватель)
Выдача книги (Vidacha)	Код, дата, код книги, код читателя, код сотрудника
Возврат книги (Vozvrat)	Код, дата, код книги, код читателя, код сотрудника

Таблица 2. Соотношение между сущностями

Сущность №1	отнош.	действие	Сущность №2
Книга	М: 1	Лежит на	стеллаже
Книга	М: 1	Имеет	автора
Книга	М: 1	Имеет	издательство
Сотрудники библиотеки	М: 1	Работают в	Определенную смену
Сотрудники библиотеки	М: М	выдают	Книги
Сотрудники библиотеки	М: М	принимают	Книги
Студенты	М: 1	учатся	На определенном факультете
Студенты	М: 1	Обладают	Читательским билетом
Кафедры	М: 1	находятся	На определенном факультете
Преподаватели	М: 1	обладают	Читательским билетом
Преподаватели	М: 1	Могут относиться	К определенной кафедре
Читатели	М: М	Могут брать	Книги
Читатели	М: М	Могут отдавать	Книги

3. Формулировка бизнес правил

1) на каждом стеллаже лежат книги; каждая книга может лежать только на одном стеллаже.

Аналогично строятся бизнес правила для отношений [Книги - Автор] и [Книги - Издательства].

2) в определенную смену могут работать несколько сотрудников; каждый сотрудник может работать только в одну смену.

3) сотрудники выдают книги студентам, также они их принимают у студентов. Эти операции агрегируются в сущностях «Возврат» и «Выдача».

4) на каждом факультете есть кафедры. Кафедра может принадлежать только 1 факультету

3. Построение базы данных

1. SQL описание (ER-Win).

После составления логической и физической моделей в программе ER-Win была произведена генерация SQL описания. Оно представлено ниже:

CREATE TABLE Author (

AVT_FAM char(20) NOT NULL,

AVT_KOD int NOT NULL,

AVT_NAME char(20) NOT NULL,

AVT_OTCH char(20) NULL

)

go

ALTER TABLE Author

ADD PRIMARY KEY NONCLUSTERED (AVT_KOD)

go

CREATE TABLE Book (

B_NAME char(30) NOT NULL,

B_KOD int NOT NULL,

AVT_KOD int NOT NULL,

IZ_KOD int NOT NULL,

ST_KOD int NOT NULL

)

go

ALTER TABLE Book

ADD PRIMARY KEY NONCLUSTERED (B_KOD)

go

CREATE TABLE Chitateli (

CH_PROF char(20) NOT NULL,

CH_KOD int NOT NULL

)

go

ALTER TABLE Chitateli

ADD PRIMARY KEY NONCLUSTERED (CH_KOD)

go

CREATE TABLE Employees (

E_FAM char(20) NOT NULL,

E_KOD int NOT NULL,

E_NAME char(20) NOT NULL,

E_OTCH char(20) NULL,

E_RANK char(20) NOT NULL,

SM_KOD int NOT NULL

)

go

ALTER TABLE Employees

ADD PRIMARY KEY NONCLUSTERED (E_KOD)

go

CREATE TABLE Fakultet (

FAK_NAME char(30) NOT NULL,

FAK_KOD int NOT NULL

)

go

ALTER TABLE Fakultet

ADD PRIMARY KEY NONCLUSTERED (FAK_KOD)

go

CREATE TABLE Izdatelstva (

IZ_NAME char(20) NOT NULL,

IZ_KOD int NOT NULL

)

go

ALTER TABLE Izdatelstva

ADD PRIMARY KEY NONCLUSTERED (IZ_KOD)

go

CREATE TABLE Kafedra (

KAF_NAME char(35) NOT NULL,

FAK_KOD int NOT NULL,

KAF_KOD int NOT NULL

)

go

ALTER TABLE Kafedra

ADD PRIMARY KEY NONCLUSTERED (KAF_KOD)

go

CREATE TABLE Smena (

SM_NACH datetime NOT NULL,

SM_KOD int NOT NULL,

SM_KONEC datetime NOT NULL

)

go

ALTER TABLE Smena

ADD PRIMARY KEY NONCLUSTERED (SM_KOD)

go

CREATE TABLE Stellazh (

ST_TEMA char(30) NULL,

ST_KOD int NOT NULL

)

go

ALTER TABLE Stellazh

ADD PRIMARY KEY NONCLUSTERED (ST_KOD)

go

CREATE TABLE Students (

CH_KOD int NOT NULL,

STUD_KOD int NOT NULL,

STUD_FAM char(20) NOT NULL,

STUD_NAME char(20) NOT NULL,

STUD_OTCH char(20) NULL,

STUD_KURS int NOT NULL,

STUD_GROUP int NOT NULL,

FAK_KOD int NOT NULL

)

go

ALTER TABLE Students

ADD PRIMARY KEY NONCLUSTERED (STUD_KOD)

go

CREATE TABLE Teachers (

CH_KOD int NOT NULL,

T_KOD int NOT NULL,

T_FAM char(20) NOT NULL,

T_NAME char(20) NOT NULL,

T_OTCH char(20) NULL,

T_TEL char(17) NULL,

T_MAIL char(30) NULL,

KAF_KOD int NULL

)

go

ALTER TABLE Teachers

ADD PRIMARY KEY NONCLUSTERED (T_KOD)

go

CREATE TABLE Vidacha (

V_DATE datetime NOT NULL,

VD_KOD int NOT NULL,

B_KOD int NOT NULL,

CH_KOD int NOT NULL,

E_KOD int NOT NULL

)

go

ALTER TABLE Vidacha

ADD PRIMARY KEY NONCLUSTERED (VD_KOD)

go

CREATE TABLE Vozvrat (

V_DATE datetime NULL,

VZ_KOD int NOT NULL,

B_KOD int NOT NULL,

CH_KOD int NOT NULL,

E_KOD int NOT NULL

)

go

ALTER TABLE Vozvrat

ADD PRIMARY KEY NONCLUSTERED (VZ_KOD)

go

ALTER TABLE Book

ADD FOREIGN KEY (IZ_KOD)

REFERENCES Izdatelstva

go

ALTER TABLE Book

ADD FOREIGN KEY (AVT_KOD)

REFERENCES Author

go

ALTER TABLE Book

ADD FOREIGN KEY (ST_KOD)

REFERENCES Stellazh

go

ALTER TABLE Employees

ADD FOREIGN KEY (SM_KOD)

REFERENCES Smena

go

ALTER TABLE Kafedra

ADD FOREIGN KEY (FAK_KOD)

REFERENCES Fakultet

go

ALTER TABLE Students

ADD FOREIGN KEY (FAK_KOD)

REFERENCES Fakultet

go

ALTER TABLE Students

ADD FOREIGN KEY (CH_KOD)

REFERENCES Chitateli

go

ALTER TABLE Teachers

ADD FOREIGN KEY (KAF_KOD)

REFERENCES Kafedra

go

ALTER TABLE Teachers

ADD FOREIGN KEY (CH_KOD)

REFERENCES Chitateli

go

ALTER TABLE Vidacha

ADD FOREIGN KEY (E_KOD)

REFERENCES Employees

go

ALTER TABLE Vidacha

ADD FOREIGN KEY (B_KOD)

REFERENCES Book

go

ALTER TABLE Vidacha

ADD FOREIGN KEY (CH_KOD)

REFERENCES Chitateli

go

ALTER TABLE Vozvrat

ADD FOREIGN KEY (CH_KOD)

REFERENCES Chitateli

go

ALTER TABLE Vozvrat

ADD FOREIGN KEY (E_KOD)

REFERENCES Employees

go

ALTER TABLE Vozvrat

ADD FOREIGN KEY (B_KOD)

REFERENCES Book

Go

Диаграммы БД (MS Access, MS SQL Server).

После генерации данного кода был произведен его ввод в СУБД MS Access и в СУБД MS SQL Server. Ниже представлены диаграммы баз данных в обеих СУБД.

При вводе данных в MS Access выше представленное SQL описание немного не подходило, а именно: для создания таблицы нужно было не писать 2 запроса:

CREATE TABLE Stellazh (

ST_TEMA char(30) NULL,

ST_KOD int NOT NULL

);

ALTER TABLE Stellazh

ADD PRIMARY KEY NONCLUSTERED (ST_KOD);

а надо было написать 1 запрос такого вида:

CREATE TABLE Stellazh (

ST_TEMA char(30) NULL,

ST_KOD int PRIMARY KEY NOT NULL

);

1) Диаграмма БД в MS SQL_Server.

4. Построение запросов к базе данных в MS SQL Server

Перед тем, как начать писать запросы, я произвел её заполнение данными.

Пример:

Запросы:

Запрос 1. Вывод всей информации о студентах: SELECT * FROM Students

Запрос 2. Вывод Ф.И.О. и телефона всех преподавателей SELECT T_FAM AS Фамилия, T_NAME AS Имя, T_OTCH AS Отчество, T_TEL AS телефон FROM Teachers

Запрос 3. Вывод всей информации о книгах, лежащих на 4 стеллаже SELECT * FROM Book WHERE ST_KOD=4

Запрос 4. Вывод информации о писателях (авторах), чей номер выше или равен 3 SELECT * FROM Author WHERE AVT_KOD>=3

Запрос 5 Вывод списка сотрудников, который отсортирован по смене SELECT * FROM Employees ORDER BY SM_KOD

Запрос 6. Выведем список книг, причем в результате будет отображаться имя автора и издательство. SELECT Book.B_NAME AS Название_книги, Author.AVT_FAM AS Автор, Izdatelstva.IZ_NAME AS Издательство FROM Author Inner Join (Book Inner join Izdatelstva on Book.IZ_KOD=Izdatelstva.IZ_KOD) ON Author.AVT_KOD=Book.AVT_KOD

Запрос 7. Опять же выведем список книг с автором и названием издательства, но с условием, что издательство имеет название «экономическое-изд» или «иностранный-язык»

SELECT Book.B_NAME AS Название_книги, Author.AVT_FAM AS Автор, Izdatelstva.IZ_NAME AS Издательство FROM Author Inner Join (Book Inner join Izdatelstva on Book.IZ_KOD=Izdatelstva.IZ_KOD) ON Author.AVT_KOD=Book.AVT_KOD WHERE Izdatelstva.IZ_NAME='экономическое-изд' OR Izdatelstva.IZ_NAME='иностранный-язык'



Запрос 8. Выведем фамилии преподавателей и к какой кафедре они относятся. SELECT Teachers.T_FAM AS Фамилия, Teachers.T_NAME AS Имя, Kafedra.KAF_NAME AS Кафедра FROM Teachers inner join Kafedra On Teachers.KAF_KOD=Kafedra.KAF_KOD

Запрос 9. Выведем список студентов с факультетами, на которых они учатся, причем курс, на котором учатся студенты будет 2. SELECT Students.STUD_FAM AS Фамилия, Students.STUD_KURS AS Курс, Fakultet.FAK_NAME AS Факультет FROM Students inner join Fakultet ON Students.FAK_KOD=Fakultet.FAK_KOD WHERE Students.STUD_KURS=2

Запрос 10. Выведем список студентов с факультетами, на которых они учатся, причем факультет, на котором учатся студенты будет Бизнес-Информатика. SELECT Students.STUD_FAM AS Фамилия, Students.STUD_KURS AS Курс, Fakultet.FAK_NAME AS Факультет FROM Students inner join Fakultet ON Students.FAK_KOD=Fakultet.FAK_KOD WHERE Fakultet.FAK_NAME='Бизнес-Информатика'

Запрос 11. Выберем работников, у которых отчество не занесено в БД. SELECT * FROM Employees WHERE E_OTCH IS NULL

Запрос 12. Выберем всех студентов, фамилии которых начинаются на букву «Г». SELECT * FROM Students WHERE STUD_FAM LIKE ('Г % ')

Запрос 13. Покажем все книги, издательства которых «иностранный-язык» или «Прог-издательство». SELECT Book.B_NAME, Izdatelstva.IZ_NAME FROM Book inner join Izdatelstva on Book. Iz_KOD=Izdatelstva.IZ_KOD WHERE Izdatelstva.IZ_NAME IN ('иностранный-язык', 'Прог-издательство')

Запрос 13. Выведем информацию о возврате книг 3.05.11 и 5.05.11. SELECT Vozvrat.V_DATE, Book.B_NAME, Vozvrat.CH_KOD as [Номер читательского билета], Employees.E_FAM as [Сотрудник] FROM Book inner join (Vozvrat inner join Employees on Vozvrat.E_KOD=Employees.E_KOD) on Book.B_KOD=Vozvrat.B_KOD WHERE Vozvrat.V_DATE in ('2011-05-03', '2011-05-05').



Запрос 14. Выясним даты, когда человек с читательским билетом №2 возвращал книги. SELECT Vozvrat.V_DATE, Book.B_NAME, Vozvrat.CH_KOD as [Номер читательского билета], Employees.E_FAM as [Сотрудник] FROM Book inner join (Vozvrat inner join Employees on Vozvrat.E_KOD=Employees.E_KOD) on Book.B_KOD=Vozvrat.B_KOD Vozvrat.CH_KOD=2

Запрос 15. Добавим нового сотрудника библиотеки. INSERT INTO Employees VALUES ('Балашова', 5, 'Екатерина', null, 'сотрудник', 2)

Запрос 16. Изменим отчество студента (точнее добавим, т.к. у него не записано в БД отчество) под номером 3. UPDATE Students SET STUD_OTCH='Иванович' WHERE STUD_KOD=3

Запрос 17. Изменим имя и отчество студента под номером 2. UPDATE Students SET STUD_OTCH='Александрович', STUD_NAME='Константин' WHERE STUD_KOD=2.

5. Работа с базой данных через MS Excel 2007

база книга библиотека excel

1. Импорт таблиц.

Сначала я продемонстрирую простой импорт таблиц в Excel из MS SQL Server. Для этого заходим в MS Excel, выбираем вкладку «Данные» и в этой вкладке нажимаю из других источников > с сервера SQL Server. Откроется следующее окно:

В этом окне нам нужно правильно указать имя своего сервера и нажать кнопку «Далее».

Откроется следующее диалоговое окно:

Здесь мы выбираем БД и таблицу, которую хотим импортировать в Excel. Откроется ещё одно окно, но я думаю, что в нем ничего менять не нужно.

Нажимаем кнопку «Готово» и Excel просит нас указать область, в которую будет записана таблица.

Нажимаем «ОК», получаем результат:



2. Создание макросов

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

Последовательность действий:

1) начинаем запись макроса в Excel

2) загружаем таблицу в Excel, как было написано в 1 части

3) завершаем запись макроса

4) заходим в макрос, редактируем там код на VBA, добавляем форму, связываем всё это. Главное, что меня тормозило и из-за чего не работало всё, это то, что в свойстве «CommandType» нужно поменять параметр с xlCmdTable на xlCmdSql.

После выполнения всех этих действий всё готово. Далее я покажу демонстрацию работы с созданным мною макросом. Заходим в документ «Книга1», в котором находится макрос. Сразу при открытии система нам предлагает ввести запрос в форму, которая изображена ниже:

В этой форме можно вводить любые запросы к таблице Author. Простейший запрос SELECT * FROM Author. Вводим его в форму, и если все нормально, то система выдает сообщение, показанное ниже:

Это сообщение означает, что всё выполнено успешно. Результат отображен на рисунке ниже:

Также в форме запроса можно написать запросы и сложнее, например SELECT * FROM Author WHERE AVT_KOD>.

Результат:

Приложение 1

Код макроса на VBA

Sub Auto_Open()

UserForm1. Show

End Sub

Sub мой_макрос(SQLstr)

'

' мой_макрос Макрос

' Смоленцев Александр 273 (1) группа. 26.05.2011

'очистка листа

'-

'

Cells. Select

Sheets («Лист3»).Select

Cells. Select

Selection. ClearContents

Range («A1»).Select

'

'-

'

With ActiveSheet. ListObjects. Add (SourceType:=0, Source:=Array (_

«OLEDB; Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=True; Data Source=ACER-PC\SQLEXPRESS; Use Procedure for Prepare» _

_

«=1; Auto Translate=True; Packet Size=4096; Workstation ID=ACER-PC; Use Encryption for Data=False; Tag with column collation when poss» _

«ible=False; Initial Catalog=University_Library»), Destination:=Range («$A$1») _

).QueryTable

CommandType = xlCmdSql

CommandText = SQLstr

RowNumbers = False

FillAdjacentFormulas = False

PreserveFormatting = True

RefreshOnFileOpen = False

BackgroundQuery = True

RefreshStyle = xlInsertDeleteCells

SavePassword = False

SaveData = True

AdjustColumnWidth = True

RefreshPeriod = 0

PreserveColumnInfo = True

SourceConnectionFile = _

«C:\Пользователи\Александр\Мои документы\Мои источники данных\ACER-PC_SQLEXPRESS University_Library Author.odc»

ListObject. DisplayName = _

«Лист3»

Refresh BackgroundQuery:=False

End With

MsgBox «Запрос выполнен»

End Sub

Размещено на Allbest.ru