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

 

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

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

Microsoft Access является настольной СУБД (система управления базами данных) реляционного типа. Достоинством Access является то, что она имеет очень простой графический интерфейс, который позволяет не только создавать собственную базу данных, но и разрабатывать приложения, используя встроенные средства. В отличие от других настольных СУБД, Access хранит все данные в одном файле, хотя и распределяет их по разным таблицам, как и положено реляционной СУБД. К этим данным относится не только информация в таблицах.

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

Проектирование базы данных можно реализовать двумя способами: с помощью конструктора таблиц программного обеспечения Microsoft Office Access или путем составления SQL-запросов для создания таблиц. В курсовой работе используется второй метод.

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

· CREATE TABLE <Название таблицы> (<Название поля> <Тип поля>, …, <Название поля> <Тип поля>) – команда для создания таблицы;

· <Название поля> COUNTER CONSTRAINT PrimaryKey PRIMARY KEY – команда для создания ключа таблицы;

· FOREIGN KEY (<Название поля>) REFERENCES <Название внешней таблицы> (<Название внешнего ключа>) – команда для создания внешнего ключа таблицы.

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

1. CREATE TABLE Сотрудники

([Код сотрудника] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, ФИО text, Оклад int, Должность text, [Пароль сотрудника] char(5));

Рис. 9 – Таблица «Сотрудники»

2. CREATE TABLE Залы

([Код зала] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, Наименование text, [Код ответственного] int, Площадь int, FOREIGN KEY ([Код ответственного]) REFERENCES Сотрудники ([Код сотрудника]));

Рис. 10 – Таблица «Залы»

3. CREATE TABLE Экспонаты (

[Код экспоната] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, Наименование text, [Код зала] int, [Дата поступления] DATE, Автор text, Материал text, Техника text, FOREIGN KEY ([Код зала]) REFERENCES Залы ([Код зала]));

Рис. 11 – Таблица «Экспонаты»

4. CREATE TABLE Экскурсии (

[Код экскурсии] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, Наименование text, [Код ответственного] int, Стоимость CURRENCY, FOREIGN KEY ([Код ответственного]) REFERENCES Сотрудники ([Код сотрудника]));

Рис. 12 – Таблица «Экскурсии»

5. CREATE TABLE Расписание

([Код проведения экскурсии] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, [Код экскурсии] int, [Время проведения] date, График text, FOREIGN KEY ([Код экскурсии]) REFERENCES Экскурсии ([Код экскурсии]));

Рис. 13 – Таблица «Расписание»

6. CREATE TABLE Продажи

([Код продажи] COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, [Дата продажи] date, [Код экскурсии] int, [Код кассира] int, FOREIGN KEY ([Код экскурсии]) REFERENCES Экскурсии ([Код экскурсии]), FOREIGN KEY ([Код кассира]) REFERENCES Сотрудники ([Код сотрудника]));

Рис. 14 – Таблица «Продажи»

 

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

 

2.5.2 Sql запросы для взаимодействия с приложением

 

В качестве иллюстрации работы SQL-запросов приложения взято несколько запросов, а также ниже приведены скриншоты результатов выполнения запросов в приложении.

 

1. SELECT * FROM Сотрудники;

Рис. 16 - Получение всех данных таблицы «Сотрудники»

 

2. "UPDATE Сотрудники SET ФИО = '" + maskedTextBox1.Text + "', Оклад = " + maskedTextBox2.Text + ", Должность = '" + maskedTextBox3.Text + "', [Пароль сотрудника] = '" + maskedTextBox4.Text + "' WHERE[Код сотрудника] = " + idS

Рис. 17 - Обновление изменений одной любой записи таблицы "Сотрудники"

Рис. 18 - Обновление изменений одной любой записи таблицы "Сотрудники" (продолжение)

3. INSERT INTO Сотрудники (ФИО, Оклад, Должность, [Пароль сотрудника]) VALUES ('" + maskedTextBox1.Text + "', '" + maskedTextBox2.Text + "', '" + maskedTextBox3.Text + "', '" + maskedTextBox4.Text + "')"

Рис. 19 - Вставка новой записи в таблицу "Сотрудники"

Рис. 20 - Вставка новой записи в таблицу "Сотрудники" (Продолжение)

4. "DELETE * FROM Сотрудники WHERE(ФИО LIKE '" + maskedTextBox1.Text + "' AND Оклад LIKE '" + maskedTextBox2.Text + "' AND Должность LIKE '" + maskedTextBox3.Text + "' AND [Пароль сотрудника] LIKE '" + maskedTextBox4.Text + "');"

Рис. 21 - Удаление записи из таблицы "Сотрудники"

Рис. 22 - Удаление записи из таблицы "Сотрудники" (Продолжение)

5. SELECT * FROM Экскурсии WHERE(((Экскурсии.График) = '" + comboGrafic + "')) AND (((Экскурсии.Стоимость) >= " + maskedTextBox1.Text + ")) AND (((Экскурсии.Стоимость) <= " + maskedTextBox2.Text + ")) ORDER BY Экскурсии.Стоимость; "

Рис. 23 - Получение всех данных таблицы с условием и сортировкой по полю

Рис. 24 - Получение всех данных таблицы с условием и сортировкой по полю (Продолжение)

6. "SELECT Сотрудники.ФИО, Залы.Наименование, Залы.Площадь, Сотрудники.Должность FROM(Залы INNER JOIN Сотрудники ON Залы.[Код ответственного] = Сотрудники.[Код сотрудника]) WHERE (Залы.Площадь " + compare + check + ") ORDER BY Залы.Площадь";

Рис. 25 - Получение заданных полей двух таблиц с условием и сортировкой по полю, где compare – это логический знак сравнения, check – число для сравнения со значением полей таблицы

7. SELECT Экскурсии.Наименование, Count(Продажи.id_продажи) AS [Количество продаж]

FROM Экскурсии INNER JOIN (Расписание INNER JOIN Продажи ON Расписание.[Код проведения экскурсии] = Продажи.id_экскурсии) ON Экскурсии.[Код экскурсии] = Расписание.[Код экскурии]

GROUP BY Экскурсии.Наименование ORDER BY Count(Продажи.id_продажи) DESC;

Рис. 26 – Выполнение запроса №7, получение всех проданных экскурсий за все время (их наименование и количество проданных билетов)

Рис. 27 - График купленных экскурсий за все время работы музея по возрастанию

8. SELECT Экскурсии.Наименование, Count(Продажи.id_продажи) AS [Count-id_продажи] FROM Экскурсии INNER JOIN (Расписание INNER JOIN Продажи ON Расписание.[Код проведения экскурсии] = Продажи.id_экскурсии) ON Экскурсии.[Код экскурсии] = Расписание.[Код экскурсии] WHERE (((Month([Продажи.Дата продажи])) = Month(Now()))) GROUP BY Экскурсии.Наименование ORDER BY Count(Продажи.id_продажи) DESC;

Рис. 28 - Выполнение запроса №8, получение всех проданных экскурсий за последний месяц работы музея, сортированные по быванию (их наименование и количество проданных билетов)

Приложение

Основные задачи приложения

Необходимо разработать приложение, для работы с базой данных ''Деятельность музея'', которое будет выполнять следующие задачи:

1. Возможность добавления и удаления данных сотрудников.

2. Изменение данных сотрудников.

3. Создание функции для легкого просмотра экскурсий.

4. Ведение стастистики музейной деятельности.