Сущность №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