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

КАЗАНСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

НАБЕРЕЖНОЧЕЛНИНСКИЙ ФИЛИАЛ

ФАКУЛЬТЕТ

ПРИКЛАДНОЙ МАТЕМАТИКИ И

ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ

КУРСОВАЯ РАБОТА

на ТЕМУ: Разработка информационно-логической модели

базы данных и программ для автоматизации деятельности

предприятия ООО «АвтоМастер»

студентов II курса

группы 4201

Кириловой Л.В. и

Гильмишариповой Э.Р.

Научный руководитель

к.ф.-м.н., доцент

Товштейн М.Я.

НАБЕРЕЖНЫЕ ЧЕЛНЫ 2004

Оглавление

• Введение
• 1. Организационно-экономическая сущность задачи
• 1.1 Возникновение задачи
• 1.2 Структура предприятия заказчика
• 1.3 Информационные процессы
• 1.4 Входные и выходные документы
• 1.5 Перечень задач, предлагаемых заказчиком для исполнения
• 2. Построение информационно-логической модели предметной области
• 2.1 ER-диаграмма
• 2.2 Разработка нормализованных отношений по диаграмме
• 2.4 Реляционная модель
• 2.5 Построение нормализованных таблиц
• 3. Физическое проектирование базы данных в среде Microsoft Access
• 3.1 Разработка интерфейса
• 3.1.1 Создание главной кнопочной формы
• 3.1.2 Создание форм
• 3.1.3 Создание макросов
• 3.1.4 Создание отчетов
• 4. Решение задач заказчика
• 5. Защита данных от несанкционированного доступа
• 1. Организационно-экономическая сущность задачи

1.1 Возникновение задачи

Заказчиком базы данных является официальный дистрибьютор компании Procter&Gamble в Набережных Челнах ООО “Фирма Смайл”, занимающаяся поставками чистящих-моющих средств (стиральные порошки Ariel, Tide, Миф, кондиционер для белья Lenor, жидкость для мытья посуды Fairy), шампуней и бальзамов для волос Pantene Pro-V, Head&Shoulders, Herbal Essensees и Shamtu. Компания поставляет продукцию в сети магазинов ООО “Впрок” и ТД ”Парфюмцентр”, наряду с этим каждый месяц с 11 по 28 число(по 4 часа в день) проводит промо-акции, в которых может поучаствовать каждый покупатель продукции P&G и получить приятное дополнение к покупке (подарок).

Сотрудники стремятся быть лучшими во всех областях, имеющих для Компании определяющее значение, оценивают свою работу по самым строгим критериям и сопоставляют её с наивысшими показателями как в самой Компании, так и вне ее. ООО “Фирма Смайл” придаёт громадное значение новой перспективной продукции для потребителей, стремится преодолевать стереотипы и творчески подходить к своей деятельности, чтобы добиться успеха на рынке.

Проектировщикам за определённый отрезок времени необходимо создать БД, которая за минимальное время (что позволяют современные компьютеры), при определённом запросе будет выдавать подробную информацию, интересующую работников ООО “Фирма Смайл” , вносить изменения, если это необходимо (добавление, удаление и изменение данных), одним словом, выполнять желания пользователя этой БД. Основные требования к разработчикам: удобство использования, наглядность, минимальные затраты ресурсов компьютерного обеспечения, возможность использования БДпри отсутствии профессиональных навыков.

1.2 Структура предприятия заказчика

ООО “Фирма Смайл” имеет следующую структуру:

Отдел менеджмента по промо-акциям:

ї Менеджер по промо-акциям, мерчендайзер, супервайзер

ї Составляют графики проведения промо-акций в магазинах

ї Распределяют дни работы промоутеров

ї Проверяют, нет ли нарушений при проведении промо-акции

Отдел менеджмента по сбыту:

ї Менеджер по сбыту, менеджер по рекламе, торговые представители:

ї Ведут учёт продаж продукции

ї Ведут учёт расходов заказчиков при приобретении продукции

ї Ведут проверки в магазинах-заказчиках на нарушение условий договора

ї Расширяют область реализации продукции

Финансовый отдел:

§ Гл. бухгалтер

§ Ведёт проверки следующих отделов:

1. Отдел продаж:

ї Гл. экономист, казначей, экономист

ї Ведут учёт обо всей реализованной продукции

ї Ведут учёт о прибылях и убытках

2. Отдел закупок:

ї Гл. экономист, казначей, экономист

ї Ведут учёт обо всей заказанной продукции

ї Ведут учёт отправки заказов

3. Отдел промо-акций:

ї Экономист

ї Ведёт начисление заработной платы промоутерам

ї Ведёт прочие отчисления и начисления

Служба качества:

ї Зам.директор по качеству, инженер по качеству

ї Ведут учёт качества реализуемой продукции

ї Ведут приём жалоб и предложений по качеству реализуемой продукции

Юридический отдел:

ї Гл. юрист, нотариус

ї Ведут составление договоров с заказчиками

ї Ведут прочие юридические услуги

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

1.3 Информационные процессы

1.4 Входные и выходные документы

Входные документы:

1. Прайс-лист Beauty и ЛСП на приобретённую продукцию компанией ООО “Фирма Смайл” (Приложение 1)

2. Информация о предприятиях-заказчиках (Приложение 2)

3. Информация о поставщике (Приложение 3)

4. Информация о промоутерах (Приложение 4)

Выходные документы:

1. Накладная на приобретение товара (Приложение)

2. Накладная на отпуск (Приложение)

3. Отчет о заказах (Приложение)

4. Отчет по заработной плате промоутеров (Приложение)

1.5 Перечень задач, предлагаемых заказчиком для исполнения

1) Просмотр по коду заказа информации об адресе магазина, дате заказа, списка заказов

2) Просмотр диаграммы зависимости кода заказа от количества

3) Поиск заказчика по адресу и по названию магазина

4) Добавление, обновление, удаление заказчиков

5) Просмотр данных о направлениях в Казань и формирование накладной на это направление

6) Просмотр отчёта по направлениям в Казань

7) Просмотр данных о поставках в магазин

8) Формирование накладной на отпуск

9) Просмотр отчёта по доставкам

10) Просмотр диаграммы зависимости кода заказа от общей суммы заказа

11) Просмотр данных о продукции по наименованию, по коду типа, а также вывод всего прайс-листа.

12) Добавление, увольнение, восстановление промоутеров

13) Просмотр промоутеров по дате приёма на работу и по разряду

14) Просмотр прибыли на определённый промежуток времени

15) Просмотр прибыли от определённой продукции за определённую дату

16) Просмотр самых прибыльных продукций по адресу магазина

17) Просмотр рейтинга прибыли от продукции

18) Просмотр информации по промо-акциям за месяц

19) Просмотр информации по рабочим дням промоутера

20) Просмотр информации по промо-акциям в зависимости от кода типа

21) Просмотр информации о промоутерах, работающих в определённый день

22) Проверка правильности составления графика работы промоутеров

23) Начисление заработной платы промоутерам за определённый месяц и просмотр по нему отчёта

24) Просмотр отчёта о начислении заработной платы по дням

25) Просмотр начисления премий, штрафов

26) Просмотр промоутеров с высокой и низкой эффективностью

27) Просмотр информации о заказах с суммой по месяцам

28) Просмотр количества заказанной продукции и количества дупликаций по этой продукции

29) Просмотр подсчёта расходов магазина на определённую дату

30) Просмотр по адресу магазина даты заказов, а по дате заказов - наименования продукций

2. Построение информационно-логической модели предметной области

2.1 ER-диаграмма

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

Для разработки БД была использована модель предметной области «сущность-связь». Выбор данной модели обусловлен ее простотой и наглядностью отображения объектов предметной области и информационных потоков.

На этапе информационно - логического моделирования предметной области часто используют модель "сущность - связь" (Entity - Relationship, ER), которая наглядно изображает структурные блоки информации и логические взаимосвязи между ними. Компонентами модели являются сущности, понятия и связи. Сущность - это класс однотипных объектов, информация о которых должна быть учтена в модели. Связь - это некоторое отношение между двумя сущностями. Одна сущность может быть связана с другой или сама с собой. Связи позволяют по одной сущности находить другие, имеющие отношение с первой.

Рассмотрим сущности РАБОТНИКИ, ПРЕДПРИЯТИЕ, ЗАКАЗЫ, ПРАЙС-ЛИСТ и определим между ними связи.

Для начала рассмотрим связь между сущностями РАБОТНИКИ и ПРЕДПРИЯТИЕ. Один промоутер в течение месяца может работать в нескольких предприятиях, а предприятие должно предоставить работу нескольким промоутерам. Следовательно связь между сущностями РАБОТНИКИ и ПРЕДПРИЯТИЕ много ко многим (М: М).

В свою очередь ПРЕДПРИЯТИЕ может совершать несколько ЗАКАЗОВ, но определённый вид ЗАКАЗА не должен быть совершён несколькими ПРЕДПРИЯТИЯМИ. Поэтому связь между сущностями ПРЕДПРИЯТИЕ и ЗАКАЗЫ один ко многим (1: М).

Теперь рассмотрим связь между сущностями ЗАКАЗЫ и ПРАЙС-ЛИСТ. В одном ЗАКАЗЕ может содержаться несколько наименований (ПРАЙС-ЛИСТ), а один вид продукции (ПРАЙС-ЛИСТ) может содержаться в нескольких ЗАКАЗАХ. Пришли к тому, что и эта связь много ко многим (М: М). экономический заказчик информационный логический

Каждая связь может иметь одну из двух модальностей:

------МОЖЕТ

ОБЯЗАН.

2.2 Разработка нормализованных отношений по диаграмме

В представленной ER-диаграмме имеется 2 связи М:М и 1 связь 1:М. При разработке реляционной модели базы данных связь 1:М так и остается, а связи М:М заменяются связями 1:М.

Рассмотрим связь М:М между сущностями ЗАКАЗЫ и ПРАЙС-ЛИСТ.

В дальнейшем этот тип связи заменяется двумя связями 1:М путем создания промежуточной сущности (Заказы ЛСП+Beauty), которая будет содержать внешние ключи (Код_заказа, Наименование_товара) и атрибут Кол-во. В результате получаем:

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

Аналогично разделяем отношение между сущностями РАБОТНИКИ и ПРЕДПРИЯТИЕ путем создания промежуточной сущности (Март), которая будет содержать внешние ключи ТабНомРаб, Адрес_магазина.



В результате приходим к реляционной модели.

2.4 Реляционная модель

Недостатки иерархической и сетевой моделей привели к появлению новой, реляционной модели данных, созданной Коддом в 1970 году и вызвавшей всеобщий интерес. Реляционная модель была попыткой упростить структуру базы данных. К сожалению, практическое определение понятия "реляционная база данных" оказалось гораздо более расплывчатым, чем точное математическое определение, данное этому термину Коддом в 1970 году.

В ответ на неправильное использование термина "реляционный" Кодд в 1985 году написал статью, где сформулировал 12 правил, которым должна удовлетворять любая база данных, претендующая на звание реляционной. С тех пор двенадцать правил Кодда считаются определением реляционной СУБД. Однако можно сформулировать и более простое определение:

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

Реляционная модель данных имеет следующие свойства:

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

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

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

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

2.5 Построение нормализованных таблиц

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

Различают три вида аномалий:

· Вставка (INSERT)

· Обновление (UpDate)

· Удаление (Delete)

Чтобы избавиться от аномалий нужно сделать так, чтобы каждое отношение представляло одно отдельное понятие (отношение или объект), т.е. соответствовало одной теме. Для этого исходное отношение делят на несколько отношений, чтобы добиться отсутствия аномалий. Этот процесс называется нормализацией.

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

Чтобы нормализовать данное исходное отношение Заказы_поставки, Заказы_ЛСП+Beauty, Прайс-лист_Beauty+ЛСП (см. п. 2.2), необходимо привести его сначала к первой, потом ко второй, а затем и к третьей нормальной форме.

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

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

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

В нашем случае отношение Прайс-лист_Beauty+ЛСП находится во 2ой нормальной форме, но существует функциональная зависимость не ключевых атрибутов:



Чтобы устранить эту зависимость надо разделить отношения так, чтобы вынести в отдельное отношение те не ключевые атрибуты, которые являются зависимыми: атрибут Код типа в отношении ТИПЫ становится потенциальным ключом, а фактически первичным ключом и за счет этого устраняется избыточность: повторение кодов типа.

Рассмотрим сущности Заказы_поставки и Заказы_ЛСП+Beauty. Каждое из вышеуказанных отношений находятся в 1 Н.Ф. и не содержат не ключевых атрибутов, зависящих от части составного ключа. Отсюда можно сделать вывод, что все отношения находятся во второй нормальной форме. Зависимости между не ключевыми атрибутами нигде не наблюдается, следовательно, мы пришли к третьей нормальной форме.

В результате получаем:

Аналогично: чтобы нормализовать данное исходное отношение Работники, Март, Предприятие (см. п. 2.2), необходимо привести его сначала к первой, потом ко второй, а затем и к третьей нормальной форме. Отношения Март, Предприятие находятся в 1НФ, 2НФ и 3НФ. А отношение Работники требует дальнейшего рассмотрения. В нашем случае отношение Работники находится в 1НФ и 2НФ, но существует функциональная зависимость не ключевых атрибутов:

от #ТабНом зависят ФИО, ПаспДанные, Адрес, Телефон, ДатаРожд, Дата_приёма, Дата_увольнения, Дата_восстановл от #Разряд зависит Тарифная_ставка. Чтобы устранить эту зависимость надо разделить отношения так, чтобы вынести в отдельное отношение те не ключевые атрибуты, которые являются зависимыми: атрибут Разряд в отношении Оплата становится потенциальным ключом. В результате приходим к:

3. Физическое проектирование базы данных в среде Microsoft Access

3.1 Разработка интерфейса

3.1.1 Создание главной кнопочной формы

До сих пор приходилось иметь дело с отдельными объектами Access. И с каждым из них нужно работать в интерактивном режиме, открывая в окне базы данных вкладку требуемого объекта.

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

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

Рассмотрим иерархию кнопочной формы:

Главная кнопочная форма

v Данные о:

-------Промоутерах

-------Заказчиках

-------заказах

-------Поставках

-------Направлениях

-------продукции

v Отделы:

-------Отдел менеджмента по промо-акциям

-------Отдел менед менеджмента по сбыту

-------Финансовый отдел

(1) Отдел продаж

(2) Отдел промо-акций

(3) отдел закупок

3.1.2 Создание форм

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

Чтобы лучше представить, как создавать простые формы с помощью Мастера форм, опишем процедуру создания формы «Вывод заказов по магазину». Эта форма предназначена для просмотра информации о дате заказа и по дате заказа вывод наименований заказанных продукций, а также их количества. Для создания этой формы:

1. Дважды щелкнув по ярлыку Создание формы с помощью мастера, мы запускаем Мастер форм.

2. Появилось первое диалоговое окно. В поле со списком Таблицы и запросы отображены имена всех таблиц и запросов базы данных, которые могут использоваться в качестве источника данных для форм. Раскрыв этот список, мы выбираем таблицы: Заказы_поставки, Заказы_ЛСП+Beauty и Предприятие.

3. В списке Доступные поля этого диалогового окна отображаются все поля выбранной таблицы или запроса. Чтобы добавить в создаваемую форму отдельные поля, мы нажимаем кнопку «>».

Добавляем: из Предприятие - Адрес_магазина, ФИО_Админа; из Заказы_поставки - Дата_заказа; из Заказы_ЛСП+Beauty - Наименование_товара, Кол-во. Нажимаем кнопку «Далее» для отображения второго диалогового окна Мастера форм.

4. Во втором диалогом окне мастера можно определить вид представления данных. Мы выбираем Предприятие, а где переключатели выбираем Подчиненная форма.

5. В третьем диалоговом окне мастера можно определить вид формы. Чтобы задать внешний вид формы, мы выбрали один из переключателей: в один столбец. После выбора подходящего режима отображения данных в форме мы нажимаем кнопку «Далее» для отображения следующего диалогового окна.

6. Четвертое диалоговое окно Мастера форм предназначено для выбора стиля оформления новой формы. Мастер предлагает несколько стилей оформления. Мы выбираем диффузный стиль и нажимаем кнопку «Далее».

7. В последнем диалоговом окне Мастера форм требуется указать название формы. В поле «Задайте имя формы» мы вводим название формы: «Вывод заказов по магазину». Чтобы отобразить созданную мастером форму в режиме Формы, выбираем переключатель «Открыть форму» для просмотра и ввода данных и нажимаем кнопку Готово.

8. В результате мастером будет создана форма в соответствии с выбранными параметрами и сохранена с указанным именем, затем эта форма будет открыта в заданном режиме. Созданная с помощью мастера форма «Вывод заказов по магазину» готова к использованию. После редактируем форму в режиме Конструктора. Изменяем макет формы с помощью добавления элементов управления.

Таким образом, мы получаем форму, содержащую в себе две подчинённые формы: в начале, выбрав в раскрывающемся списке адрес магазина, мы узнаём, в каких числах месяца он заказывал товар, а затем, нажимая на интересующую нас дату, выводим список заказанных товаров этим магазином на дату заказа, нажатую нами.

3.1.4 Создание отчетов

Одной из основных задач создания и использования баз данных является предоставление пользователям необходимой информации на основе существующих данных. Для этих целей в Access предназначены формы и отчеты.

Отчет - объект базы данных, основное назначение которого - описание и вывод на печать документов, созданных на основе хранящихся данных.

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

Предусмотрены следующие стандартные способы формирования отчета:

С помощью Автоотчета

С помощью Мастера

С помощью Конструктора

С помощью запросов

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

Рассмотрим создание отчета «Начисление зарпл_раб_по дням_И»:

1. Дважды щелкнув по ярлыку Создание отчета с помощью мастера, мы запускаем Мастер отчетов.

2. Появилось первое диалоговое окно. В поле со списком Таблицы и запросы отображены имена всех таблиц и запросов базы данных, которые могут использоваться в качестве источника данных для отчетов. Раскрыв этот список, мы выбираем запрос «Начисление зарпл_раб_по дням_И».

3. В списке Доступные поля этого диалогового окна отображаются все поля выбранной таблицы или запроса. Чтобы добавить в создаваемый отчет все поля, мы нажимаем кнопку «>>». Нажимаем кнопку «Далее» для отображения второго диалогового окна Мастера отчетов.

4. В третьем диалоговом окне мастера можно определить уровни группировки. Мы выбрали следующие уровни группировки: ФИО, Паспортные_данные,День_месяца, Сумма.

5. В следующем диалоговом окне нажимаем кнопку Итоги и ставим флажок в поле Sum.

6. В следующем диалоговом окне выбираем вид макета для отчета «Ступенчатый».

7. В предпоследнем диалоговом окне выбираем требуемый стиль «Полужирный».

8. Последний шаг - задаем имя отчета «Начисление зарпл_раб_по дням_И».

9. В результате мастером будет создан отчет в соответствии с выбранными параметрами и сохранена с указанным именем. Созданная с помощью мастера отчет «Начисление зарпл_раб_по дням_И» готова к использованию. После редактируем отчет в режиме Конструктора:удаляем поля подсчёта количества встречающихся Паспортные_данные, а сумму по количеству рабочих дней оставляем. В выражении вместо “записей” вставляем слово “работал”. В итоге получаем следующий отчет «Начисление зарпл_раб_по дням_И»:

4. Решение задач заказчика

Любой запрос хранится в базе данных в формате SQL (structured query language) - язык структурированных запросов. Основное достоинство этого языка состоит в том, что он является стандартом для большинства реляционных СУБД. SQL имеет унифицированный набор инструкций, которые можно использовать во всех СУБД, поддерживающих этот язык.

Большую часть запросов составляют запросы на выборку, которые реализуют в SQL командой SELECT. Общий вид ее такой:

SELECT Список столбцов

FROM Список таблиц

[WHERE Условия выбора строк]

[ORDER BY Спецификация сортировки];

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

Для работы в режиме SQL необходимо в меню выбрать команду меню Вид, Режим SQL, после чего откроется диалоговое окно, в котором нужно записать запрос.

Рассмотрим следующие задачи, представленные заказчиком:

1. Вывод промоутеров, работающих в определённый день и месяц

Для решения этой задачи используется таблицы Работники и Март, а так же используется макрос АкцииНаОпрДату_4_4.



Убираем флажок в поле Март, т. к. заказчик попросил не показывать месяц в результате выполненного запроса. Запрос в режиме SQL выглядит следующим образом:

SELECT Март.День_месяца, Март.ТабНомРаб, Работники.ФИО, Март.ЛСП, Март.Beauty, Март.[Адрес магазина], Работники.Телефон

FROM Работники INNER JOIN Март ON Работники.ТабНомРаб = Март.ТабНомРаб

WHERE (((Март.День_месяца)=[Введите день]) AND ((Март.Месяц)=[Введите месяц]));

2. Просмотр информации о заказах

Для решения этой задачи используется таблицы Заказы_ЛСП+Beauty и Зак_поставки.

SELECT Зак_поставки.Код_заказа, Зак_поставки.Адрес_магазина, [Заказы_ЛСП+Beauty].Наименование_товара, [Заказы_ЛСП+Beauty].[Кол-во], Зак_поставки.Дата_заказа

FROM Зак_поставки INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа;

3. Просмотр информации о самых дорогих товарах по коду типа, реализуемых компанией ООО “Фирма Смайл”

Для решения этой задачи используется таблица Прайс-лист_Beauty+ЛСП.

SELECT TOP 10 [Прайс-лист_Beauty+ЛСП].Наименование, [Прайс-лист_Beauty+ЛСП].[Код типа], [Прайс-лист_Beauty+ЛСП].[Цена(без %)]

FROM [Прайс-лист_Beauty+ЛСП]

WHERE ((([Прайс-лист_Beauty+ЛСП].[Код типа]) Like [Введите код типа(ЛСП,Beauty)]))

ORDER BY [Прайс-лист_Beauty+ЛСП].[Цена(без %)] DESC;

4. Просмотр самых прибыльных (продаваемых) продукций

Для решения этой задачи используется таблица Прайс-лист_Beauty+ЛСП, Заказы_ЛСП+Beauty, Зак_поставки.



SELECT TOP 10 [Заказы_ЛСП+Beauty].Наименование_товара, Sum([Цена(с %)]*[Кол-во]) AS [Цена с], Sum([Цена(без %)]*[Кол-во]) AS [Цена без], Sum([Цена(с %)]*[Кол-во])-Sum([Цена(без %)]*[Кол-во]) AS Прибыль

FROM [Прайс-лист_Beauty+ЛСП] INNER JOIN (Зак_поставки INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа) ON [Прайс-лист_Beauty+ЛСП].Наименование = [Заказы_ЛСП+Beauty].Наименование_товара

GROUP BY [Заказы_ЛСП+Beauty].Наименование_товара, Зак_поставки.Адрес_магазина

HAVING (((Зак_поставки.Адрес_магазина) Like [Введите адрес магазина]))

ORDER BY Sum([Цена(с %)]*[Кол-во])-Sum([Цена(без %)]*[Кол-во]) DESC;

5. Просмотр продукции с ценой выше средней на 10%

Для решения этой задачи используется таблица Прайс-лист_Beauty+ЛСП.



SELECT TOP 10 [Прайс-лист_Beauty+ЛСП].Наименование, [Прайс-лист_Beauty+ЛСП].[Цена(без %)]

FROM [Прайс-лист_Beauty+ЛСП]

WHERE ((([Прайс-лист_Beauty+ЛСП].[Цена(без %)])>(SELECT (AVG([Цена(без %)])*1.1) From [Прайс-лист_Beauty+ЛСП])))

ORDER BY [Прайс-лист_Beauty+ЛСП].[Цена(без %)] DESC;

6. Просмотр суммы, необходимой на направление в Казань

Для решения этой задачи используется таблица Прайс-лист_Beauty+ЛСП, Заказы_ЛСП+Beauty, Зак_поставки.

SELECT Зак_поставки.Дата_заказа, Sum([Прайс-лист_Beauty+ЛСП]![Цена(без %)]*[Заказы_ЛСП+Beauty]![Кол-во]) AS Итог

FROM [Прайс-лист_Beauty+ЛСП] INNER JOIN (Зак_поставки INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа) ON [Прайс-лист_Beauty+ЛСП].Наименование = [Заказы_ЛСП+Beauty].Наименование_товара

GROUP BY Зак_поставки.Дата_заказа;

7. Просмотр суммы, необходимой для заказа по Коду_заказа (КодНакладной).

Для решения этой задачи используется таблица Прайс-лист_Beauty+ЛСП, Заказы_ЛСП+Beauty, Зак_поставки.

SELECT Зак_поставки.Код_заказа, Sum([Заказы_ЛСП+Beauty]![Кол-во]*[Прайс-лист_Beauty+ЛСП]![Цена(с %)]) AS Итого

FROM [Прайс-лист_Beauty+ЛСП] INNER JOIN (Зак_поставки INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа) ON [Прайс-лист_Beauty+ЛСП].Наименование = [Заказы_ЛСП+Beauty].Наименование_товара

GROUP BY Зак_поставки.Код_заказа;

9. Просмотр суммы кол-ва и повторов совершения заказов по определённому виду продукции на определённый промежуток времени.

Для решения этой задачи используется таблица Заказы_ЛСП+Beauty, Зак_поставки.

SELECT DISTINCTROW [Заказы_ЛСП+Beauty].Наименование_товара, Sum([Заказы_ЛСП+Beauty].[Кол-во]) AS [Sum-Кол-во], Count([Заказы_ЛСП+Beauty].Наименование_товара) AS [Count-Наименование_товара]

FROM Зак_поставки INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа

WHERE (((Зак_поставки.Дата_заказа) Between [начальная дата] And [Конечная дата]))

GROUP BY [Заказы_ЛСП+Beauty].Наименование_товара

HAVING ((([Заказы_ЛСП+Beauty].Наименование_товара) Like [Введите_наименование товара]));

10. Просмотр промоутеров, зарегистрировавшихся в компании в определённый промежуток времени

Для решения этой задачи используется таблица Работники.



SELECT Работники.ФИО, Работники.ТабНомРаб, Работники.Телефон, Работники.Разряд, Работники.Дата_приёма

FROM Работники

WHERE (((Работники.Дата_приёма) Between [Введите нач_ дату приёма на работу] And [Конечная дата просмотра]));

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

Для решения этой задачи используется таблица Прайс-лист_Beauty+ЛСП, Заказы_ЛСП+Beauty, Зак_поставки.

SELECT [Заказы_ЛСП+Beauty].Наименование_товара, Sum([Цена(с %)]*[Кол-во]) AS [Цена с], Sum([Цена(без %)]*[Кол-во]) AS [Цена без], Sum([Цена(с %)]*[Кол-во])-Sum([Цена(без %)]*[Кол-во]) AS Прибыль

FROM [Прайс-лист_Beauty+ЛСП] INNER JOIN (Зак_поставки INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа) ON [Прайс-лист_Beauty+ЛСП].Наименование = [Заказы_ЛСП+Beauty].Наименование_товара

GROUP BY [Заказы_ЛСП+Beauty].Наименование_товара, Зак_поставки.Дата_заказа

HAVING ((([Заказы_ЛСП+Beauty].Наименование_товара) Like [Введине наименование продукции]) AND ((Зак_поставки.Дата_заказа) Between [Начальная дата] And [Конечная дата]));

12. Просмотр расходов магазина на определённую дату заказа

Для решения этой задачи используется таблица Прайс-лист_Beauty+ЛСП, Заказы_ЛСП+Beauty, Зак_поставки.

SELECT Зак_поставки.Адрес_магазина, Зак_поставки.Дата_заказа, Sum([Цена(с %)]*[Кол-во]) AS Цена

FROM [Прайс-лист_Beauty+ЛСП] INNER JOIN (Зак_поставки INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа) ON [Прайс-лист_Beauty+ЛСП].Наименование = [Заказы_ЛСП+Beauty].Наименование_товара

GROUP BY Зак_поставки.Адрес_магазина, Зак_поставки.Дата_заказа

HAVING (((Зак_поставки.Адрес_магазина) Like [Введите адрес магазина]) AND ((Зак_поставки.Дата_заказа) Like [Введите дату заказа]));

13. Просмотр расходов магазинов за определённый промежуток времени

Для решения этой задачи используются таблицы Прайс-лист_Beauty+ЛСП, Заказы_ЛСП+Beauty, Зак_поставки.

SELECT [Заказы_ЛСП+Beauty].Код_заказа, Зак_поставки.Адрес_магазина, Зак_поставки.Дата_заказа, Sum([Заказы_ЛСП+Beauty]![Кол-во]*[Прайс-лист_Beauty+ЛСП]![Цена(с %)]) AS Сумма

FROM [Прайс-лист_Beauty+ЛСП] INNER JOIN (Зак_поставки INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа) ON [Прайс-лист_Beauty+ЛСП].Наименование = [Заказы_ЛСП+Beauty].Наименование_товара

GROUP BY [Заказы_ЛСП+Beauty].Код_заказа, Зак_поставки.Адрес_магазина, Зак_поставки.Дата_заказа

HAVING (((Зак_поставки.Дата_заказа) Between [Начальная дата] And [Конечная дата]));

14. Суммарные расходы магазина за определённый промежуток времени

Для решения этой задачи используется запрос Расходы магазинов за опр_промежуток времени_Т, рассмотренный выше.

SELECT DISTINCTROW [Расходы магазинов за опр_промежуток времени_Т].Адрес_магазина, Sum([Расходы магазинов за опр_промежуток времени_Т].Сумма) AS [Sum-Сумма]

FROM [Расходы магазинов за опр_промежуток времени_Т]

GROUP BY [Расходы магазинов за опр_промежуток времени_Т].Адрес_магазина;

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

Для решения этой задачи используется таблица Прайс-лист_Beauty+ЛСП, Заказы_ЛСП+Beauty, Зак_поставки, а также запрос Общие Расходы магазина за опр_промежуток времени_У, рассмотренный выше.



SELECT Зак_поставки.Адрес_магазина, ([Sum-Сумма])-Sum([Прайс-лист_Beauty+ЛСП]![Цена(без %)]*[Заказы_ЛСП+Beauty]![Кол-во]) AS Прибыль

FROM [Прайс-лист_Beauty+ЛСП] INNER JOIN (([Общие Расходы магазина за опр_промежуток времени_У] INNER JOIN Зак_поставки ON [Общие Расходы магазина за опр_промежуток времени_У].Адрес_магазина = Зак_поставки.Адрес_магазина) INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа) ON [Прайс-лист_Beauty+ЛСП].Наименование = [Заказы_ЛСП+Beauty].Наименование_товара

GROUP BY Зак_поставки.Адрес_магазина, [Общие Расходы магазина за опр_промежуток времени_У].[Sum-Сумма];

16. Начисление премии промоутерам

Для решения этой задачи используется таблица Работники, Март, Оплата.



SELECT Работники.ФИО, Работники.Паспортные_данные, Март.День_месяца, Март.[Продано_товара(сумма)], Март![Продано_товара(сумма)]*0.003 AS Премия, Оплата!Тарифная_ставка*4 AS Зарплата, Март![Продано_товара(сумма)]*0.003+Оплата!Тарифная_ставка*4 AS Сумма

FROM Оплата INNER JOIN (Работники INNER JOIN Март ON Работники.Паспортные_данные = Март.Паспортные_данные) ON Оплата.Разряд = Работники.Разряд

WHERE (((Март.[Продано_товара(сумма)])>5000) AND ((Март.Месяц)=[Введите месяц]))

ORDER BY Март.[Продано_товара(сумма)] DESC;

17. Начисление щтрафов промоутерам

Для решения этой задачи используется таблица Работники, Март, Оплата.



SELECT Работники.ФИО, Работники.Паспортные_данные, Март.День_месяца, Март.[Продано_товара(сумма)], Март![Продано_товара(сумма)]*0.01 AS Штраф, Оплата!Тарифная_ставка*4 AS Зарплата, Оплата!Тарифная_ставка*4-Март![Продано_товара(сумма)]*0.01 AS Сумма

FROM Оплата INNER JOIN (Работники INNER JOIN Март ON Работники.Паспортные_данные = Март.Паспортные_данные) ON Оплата.Разряд = Работники.Разряд

WHERE (((Март.[Продано_товара(сумма)])<=5000) AND ((Март.Месяц) Like [Введите месяц]));

18. Начисление заработной платы работникам по дням

Для решения этой задачи используются запросы Премия_Ж и Штраф_З. Этот запрос является запросом на объединение, т. к. за каждый день своей работы промоутер либо получаеи премию+з/пл, либо штрафуется, т. е. з/пл.-штраф.

SELECT ФИО,Паспортные_данные,День_месяца,Сумма

FROM Премия_Ж

UNION SELECT ФИО,Паспортные_данные,День_месяца,Сумма

FROM Штраф_З;

19. Просмотр тройки аутсайдеров-промоутеров

Для решения этой задачи используется запрос Штраф_З.

SELECT TOP 3 Штраф_З.ФИО, Штраф_З.Паспортные_данные, Штраф_З.Штраф

FROM Штраф_З

ORDER BY Штраф_З.Штраф DESC;

20. Просмотр тройки лидеров-промоутеров

Для решения этой задачи используется запрос Премия_Ж.

SELECT TOP 3 Премия_Ж.ФИО, Премия_Ж.Паспортные_данные, Премия_Ж.Премия

FROM Премия_Ж

ORDER BY Премия_Ж.Премия DESC;

21. Просмотр заказов и вычисление их стоимости по месяцам.

Этот запрос является перекрёстным запросом и для решения задачи используются таблицы Прайс-лист_Beauty+ЛСП, Заказы_ЛСП+Beauty, Зак_поставки.

TRANSFORM Sum(CCur([Заказы_ЛСП+Beauty]![Кол-во]*[Прайс-лист_Beauty+ЛСП]![Цена(с %)])) AS СуммаПоПрод

SELECT [Заказы_ЛСП+Beauty].Код_заказа, Зак_поставки.Адрес_магазина, [Заказы_ЛСП+Beauty].Наименование_товара

FROM [Прайс-лист_Beauty+ЛСП] INNER JOIN (Зак_поставки INNER JOIN [Заказы_ЛСП+Beauty] ON Зак_поставки.Код_заказа = [Заказы_ЛСП+Beauty].Код_заказа) ON [Прайс-лист_Beauty+ЛСП].Наименование = [Заказы_ЛСП+Beauty].Наименование_товара

GROUP BY [Заказы_ЛСП+Beauty].Код_заказа, Зак_поставки.Адрес_магазина, [Заказы_ЛСП+Beauty].Наименование_товара

PIVOT "Мес" & DatePart("m",Зак_поставки!Дата_заказа,1,1);

5. Защита данных от несанкционированного доступа

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

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

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

Заказчик БД попросил установить пароль для защиты данных, поэтому мы решили написать программу на Visual Basic, задавающую пароль при входе в БД.

Для начала надо создать форму ВводПароля:

Затем открываем вкладку Объекты/Модули и нажимаем кнопку “Создать”. В открывшемся окне пишем программу на VBA. Таким образом, при открытии БД ООО “Фирма Смайл”, будет появляться запрос на ввод пароля.

Однако можно задать пароль и в автоматическом режиме, т. е. не потребуется писать программу на VBA. Для того чтобы задать пароль базы данных необходимо:

· Открыть базу данных монопольно;

· Найти в панели меню Сервис -- Защита - Задать пароль базы данных, в результате появится диалоговое окошко:

В данном диалоговом окошке задаем пароль базы данных.

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