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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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Могут относитьсяК определенной кафедреЧитателиМ: ММогут братьКнигиЧитателиМ: ММогут отдаватьКниги

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

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

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

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

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

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

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

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

После составления логической и физической моделей в программе ER-Win была произведена генерация SQL описания. Оно представлено ниже:TABLE Author (_FAM char(20) NOT NULL,_KOD int NOT NULL,_NAME char(20) NOT NULL,_OTCH char(20) NULL

)TABLE AuthorPRIMARY KEY NONCLUSTERED (AVT_KOD)TABLE Book (_NAME char(30) NOT NULL,_KOD int NOT NULL,_KOD int NOT NULL,_KOD int NOT NULL,_KOD int NOT NULL

)TABLE BookPRIMARY KEY NONCLUSTERED (B_KOD)TABLE Chitateli (_PROF char(20) NOT NULL,_KOD int NOT NULL

)TABLE ChitateliPRIMARY KEY NONCLUSTERED (CH_KOD)TABLE Employees (_FAM char(20) NOT NULL,_KOD int NOT NULL,_NAME char(20) NOT NULL,_OTCH char(20) NULL,_RANK char(20) NOT NULL,_KOD int NOT NULL

)TABLE EmployeesPRIMARY KEY NONCLUSTERED (E_KOD)TABLE Fakultet (_NAME char(30) NOT NULL,_KOD int NOT NULL

)TABLE FakultetPRIMARY KEY NONCLUSTERED (FAK_KOD)TABLE Izdatelstva (_NAME char(20) NOT NULL,_KOD int NOT NULL

)TABLE IzdatelstvaPRIMARY KEY NONCLUSTERED (IZ_KOD)TABLE Kafedra (_NAME char(35) NOT NULL,_KOD int NOT NULL,_KOD int NOT NULL

)TABLE KafedraPRIMARY KEY NONCLUSTERED (KAF_KOD)TABLE Smena (_NACH datetime NOT NULL,_KOD int NOT NULL,_KONEC datetime NOT NULL

)TABLE SmenaPRIMARY KEY NONCLUSTERED (SM_KOD)TABLE Stellazh (_TEMA char(30) NULL,_KOD int NOT NULL

)TABLE StellazhPRIMARY KEY NONCLUSTERED (ST_KOD)TABLE Students (_KOD int NOT NULL,_KOD int NOT NULL,_FAM char(20) NOT NULL,_NAME char(20) NOT NULL,_OTCH char(20) NULL,_KURS int NOT NULL,_GROUP int NOT NULL,_KOD int NOT NULL

)TABLE StudentsPRIMARY KEY NONCLUSTERED (STUD_KOD)TABLE Teachers (_KOD int NOT NULL,_KOD int NOT NULL,_FAM char(20) NOT NULL,_NAME char(20) NOT NULL,_OTCH char(20) NULL,_TEL char(17) NULL,_MAIL char(30) NULL,_KOD int NULL

)TABLE TeachersPRIMARY KEY NONCLUSTERED (T_KOD)TABLE Vidacha (_DATE datetime NOT NULL,_KOD int NOT NULL,_KOD int NOT NULL,_KOD int NOT NULL,_KOD int NOT NULL

)TABLE VidachaPRIMARY KEY NONCLUSTERED (VD_KOD)TABLE Vozvrat (_DATE datetime NULL,_KOD int NOT NULL,_KOD int NOT NULL,_KOD int NOT NULL,_KOD int NOT NULL

)TABLE VozvratPRIMARY KEY NONCLUSTERED (VZ_KOD)TABLE BookFOREIGN KEY (IZ_KOD)IzdatelstvaTABLE BookFOREIGN KEY (AVT_KOD)AuthorTABLE BookFOREIGN KEY (ST_KOD)StellazhTABLE EmployeesFOREIGN KEY (SM_KOD)SmenaTABLE KafedraFOREIGN KEY (FAK_KOD)FakultetTABLE StudentsFOREIGN KEY (FAK_KOD)FakultetTABLE StudentsFOREIGN KEY (CH_KOD)ChitateliTABLE TeachersFOREIGN KEY (KAF_KOD)KafedraTABLE TeachersFOREIGN KEY (CH_KOD)ChitateliTABLE VidachaFOREIGN KEY (E_KOD)EmployeesTABLE VidachaFOREIGN KEY (B_KOD)BookTABLE VidachaFOREIGN KEY (CH_KOD)ChitateliTABLE VozvratFOREIGN KEY (CH_KOD)ChitateliTABLE VozvratFOREIGN KEY (E_KOD)EmployeesTABLE VozvratFOREIGN KEY (B_KOD)Book

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

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

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

CREATE TABLE Stellazh (_TEMA char(30) NULL,_KOD int NOT NULL

);TABLE StellazhPRIMARY KEY NONCLUSTERED (ST_KOD);

а надо было написать 1 запрос такого вида:TABLE Stellazh (_TEMA char(30) NULL,_KOD int PRIMARY KEY NOT NULL

);

) Диаграмма БД в 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. Опять же выведем список книг с автором и названием издательства, но с условием, что издательство имеет название «экономическое-изд» или «иностранный-язык»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 части приложения я покажу, как связать таблицу Excel с базой данных SQL Server. В результате будет возможен ввод запроса в форму и запись результата на лист в Excel. Все действия я не буду описывать подробно, как в первой части.

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

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

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

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

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

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

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

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

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

Результат:

Приложение 1

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

Auto_Open(). ShowSubмой_макрос(SQLstr)

'

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

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

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

'-

'. Select(«Лист3»).Select. Select. ClearContents(«A1»).Select

'

'-

'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= xlCmdSql= SQLstr= False= False= True= False= True= xlInsertDeleteCells= False= True= True= 0= True= _

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

«Лист3»BackgroundQuery:=FalseWith«Запрос выполнен»Sub

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

Больше работ по теме: