Раб.кабинет ФИО работника Таб. № Начислено Удержано Итого

МИНИСТЕРСТВО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ

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

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

 

 

Информатика

 

УХТА 2003

 

 

Содержание.

 

Введение.

1. Пример выполнения задания

2. Варианты заданий.

3. Контрольные вопросы к зачёту.

Литература.

 

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

 

Введение.

 

Хранение информации – одна из важнейших функций компьютера. Одним из распространённых средств такого хранения являются базы данных. База данных - это файл специального формата, содержащий информацию, структурированную заданным образом.

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

СУБД MS Access (начиная с версии 7.0) является 32-разрядной системой управления реляционной базой данных нового поколения, работающей как в среде персональной операционной системы Windows 9х, так и в сетевых операционных системах на базе Windows NT.

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

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

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

Большое количество т.н. «мастеров» – встроенных в СУБД средств разработки объектов базы данных (таблиц, запросов, форм, отчетов), позволяет достаточно легко разрабатывать необходимые БД большому числу пользователей ПК, не имеющих специальной подготовки.

Значительным достоинством СУБД Access является возможность создания экранных форм, совпадающих по структуре с реальными документами–источниками данных, для использования их при загрузке и корректировке, а также просмотре информации.

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

Тем не менее, встроенный в СУБД Access язык программирования Visual Basic For Application (VBA), являющийся общим для всего пакета офисных программ от Microsoft, позволяет более «продвинутым» пользователям легко объединять, казалось бы, разрозненные данные, созданные в различных средах: текстовом редакторе Word, электронных таблицах Excel.

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

В заключение отметим тот факт, что в настоящее время существует достаточное количество программных средств, способных создавать достаточно развитые и сложные БД. Так, посредством Excel можно создать некие взаимосвязанные таблицы, с возможность поиска, выборки и другой обработки информации. Можно даже вывести полученные результаты работы на печать. Обработка больших массивов информации, особенно в виде электронных таблиц, ее пересчет, будет гораздо более эффективной в специализированном приложении типа MS Excel, чем в MS Access. Посредством языков программирования высокого уровня также можно создавать некие самостоятельно функционирующие приложения для работы с базами данных, и эти приложения будут боле эффектно и эффективно работать с базами данных. Но все эти альтернативные варианты имеют наряду с преимуществами целый ряд недостатков. Не говоря о соответствующем уровне подготовки специалистов, работающими над созданием таких БД, отметим следующее: все таблицы базы данных, а также другие объекты Access – формы, запросы, отчеты, макросы и модули, построенные для этой базы, – и внедренные объекты могут размещаться в одном файле на диске. При этом упрощается технология модификации и ведения базы данных и ее приложений. Обеспечивается высокая компактность размещения всех объектов СУБД на диске и эффективность обработки данных.

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

База данных является организованной на машинном носителе совокупностью взаимосвязанных данных и содержит сведения о различных объектах. В общем виде, некая база данных может содержать миллионы записей, организованных в строки информации, каждая из которых характеризует в полной мере тот или иной объект. Такая база данных может содержать повторяющиеся записи, записи об одном и том же объекте. Естественно, в этом случае информация в БД не оптимизирована, имеет избыточный объем, что весьма заметно затрудняет ее обработку. С ростом мощностей компьютеров такая избыточность теряет свою значимость, тем не менее, оптимизированная база данных дает весьма существенный прирост эффективности работы пользователя. Крайним выражением оптимизированной БД является так называемая «реляционная база данных».

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

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

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

Связи между таблицами дают возможность совместного использования данных из разных таблиц. Связь каждой пары таблиц обеспечивается одинаковыми полями в них – ключом связи. Ключом связи всегда является уникальный ключ главной таблицы. Связи между таблицами в реляционной базе данных могут быть только типа один-к-одному (1:1) или один-ко-многим (1:М).

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

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

Access ориентирована на работу с объектами базы данных, к которым относятся: таблицы, формы, запросы, отчеты, макросы и модули.

Для типовых процессов обработки данных – просмотра, обновления, поиска по заданным критериям, получением отчетов – в Access имеются средства конструирования таких объектов как формы, запросы и отчеты. Эти объекты состоят из графических элементов, называемых элементами управления. Основные элементы управления служат для связи объектов с записями таблиц, являющимися источниками данных.

При создании приложений пользователя могут использоваться объекты другого типа – макросы и модули на языке программирования Visual Basic.

Целью данной работы будет создание довольно упрощенной БД, созданной посредством «мастеров» Access, в которой имеют место лишь таблицы, запросы, формы и отчеты. Проектирование макросов и модулей в данной работе не рассматривается

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

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

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

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

Данные в БД могут быть внесены непосредственно в таблицы путем заполнения самих таблиц (на вкладке «Таблицы»), могут быть внесены в таблицы при работе с созданной на основе этой таблицы формой. Таблицы могут быть созданы посредством их формирования через запрос.

Также существует возможность импортировать в таблицы данные, сформированные другими, отличными от Access, приложениями: электронные таблицы Excel, файлы базы данных (dbf-файлы), текстовые файлы различных форматов и т.д.

Данные из таблиц также могут быть экспортированы в другой формат, отличный от формата Access.

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

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

Отчеты используются для формирования выходного документа, предназначенного для вывода на печать. Отчеты не модифицируют исходные данные, хотя в отчетах могут и присутствовать новые вычисляемые поля. Посредством отчетов исходные данные в таблицах не изменяются. Данные из отчетов могут не только распечатываться на бумажный носитель, но передаваться в другие приложения офисного пакета: MS Word, MS Excel, а также Web-страницы.

 

 

2. Пример выполнения задания

СУБД “Microsoft Access” даёт возможность пользователю вводить, хранить, осуществлять быстрый поиск и обрабатывать большие массивы информации.

Цель работы: изучить принципы использования БД, команд системы управления базами данных (СУБД) на основе рассмотренного примера. Выполнить предлагаемый вариант.

 

Пример. Салон красоты «Клеопатра» осуществляет свою работу с помощью СУБД «Access». Задание:

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

2. На основе таблиц составить запросы:

A. на выборку

B. с вычисляемыми полями

C. с параметром

3. Для удобства работы отдела кадров создать формы по учёту работников.

4. Создать на основе запросов отчеты:

ü Ведомость по заработной плате

ü Расчётные листы

 

 

1. Всю информацию разбиваем на несколько таблиц в БД, между которыми устанавливаются связи с помощью совпадающих полей в разных таблицах. При этом каждый столбец таблицы соответствует полю данных, а каждая строка - записи данных. Создание таблицы: Таблицы – Создать – Конструктор. В режиме Конструктора вводим имена полей, типы данных (можно использовать Мастер подстановок для того, чтобы быстро вставлять повторяющиеся записи из одной таблицы в другую). В сноске Свойства поля при необходимости устанавливаем Формат поля (н-р, денежный), Маску ввода (н-р, для даты, телефона) и другие параметры. Для уникальных полей (Уникальное поле – поле, значения в котором не повторяются) обязательно устанавливаем в графе Индексированное поле – «Совпадения не допускаются». При закрытии задаём имя таблицы.

 

Данные о работниках салона красоты «Клеопатра» хранятся в таблицах:

Таблица1. Должности

ФИО работника	Должность	Рабочий кабинет	Время работы
Апрелин Андрей Егорович	Стилист		чётные числа
Власов Петр Петрович	Парикмахер		ежедневно с 10.00 до 19.00
Демидова Ольга Юрьевна	Косметолог		нечётные числа
Кравцова Марина Степановна	Визажист		ежедневно с 10.00 до 19.00
Медведева Анна Павловна	Мастер маникюра и педикюра		чётные числа
Орлова Нина Григорьевна	Диспетчер		ежедневно с 9.00 до 20.00, кроме воскресенья
Седых Анжела Валерьевна	Парикмахер		нечётные числа
Фёдорова Светлана Олеговна	Массажистка		чётные числа
Якимова Дарья Николаевна	Массажистка		нечётные числа

 

Таблица 2. Работники.

№ п\п	Табель-ный номер	ФИО работника	Дата рождения	Домашний адрес	Телефон
		Апрелин Андрей Егорович	30.08.70	Октябрьская 4-79	1-89-47
		Власов Петр Петрович	03.09.67	Дзержинская 15-3	1-02-56
		Демидова Ольга Юрьевна	15.10.71	Пушкина 1-8	3-57-00
		Кравцова Марина Степановна	22.01.73	Ленина 12-57	3-42-58
		Медведева Анна Павловна	11.02.70	Сенюкова 23-21	1-06-71
		Орлова Нина Григорьевна	23.10.70	Мира, 56-9	3-08-95
		Седых Анжела Валерьевна	16.10.65	Строителей 4-143	1-32-14
		Фёдорова Светлана Олеговна	10.10.71	Ленина 15-86	6-12-01
		Якимова Дарья Николаевна	17.06.68	Кремса 2-6	3-69-10

 

 

Таблица 3. Сведения о работниках

Табельный номер	Стаж	Количество иждивенцев	Оклад
			1 650,00р.
			1 500,00р.
			1 920,00р.
			1 960,00р.
			1 170,00р.
			1 460,00р.
			1 235,00р.
			1 467,00р.
			1 350,00р.

 

 

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

 

 

2. Применение запросов является наиболее быстрым способом получения информации в БД.

А. Запрос на выборку. Цель его – это создание результирующей таблицы, в которой отображаются только нужные по условию запроса данные для таблиц. Создание запроса:

§ Запросы-Создать-Конструктор.

§ Выбираем таблицы, включающие нужные поля. Щелкните на кнопке Закрыть.

§ Сделайте двойной щелчок мышью на именах полей, содержащих искомую информацию

§ Для каждого поля, по которому будут сортироваться записи, щелкните мышью в строке Сортировка и выберите нужное значения

§ В строке Условия отбора укажите условия отбора записей.

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

 

ФИО работника	Должность	Домашний адрес	Стаж	Телефон
Демидова Ольга Юрьевна	Косметолог	Пушкина 1-8		3-57-00
Якимова Дарья Николаевна	Массажистка	Кремса 2-6		3-69-10
Кравцова Марина Степановна	Визажист	Ленина 12-57		3-42-58
Власов Петр Петрович	Парикмахер	Дзержинская 15-3		1-02-56
Седых Анжела Валерьевна	Парикмахер	Строителей 4-143		1-32-14

 

В. Запрос с вычисляемыми полями.

Создание:

§ В строке Поле для каждого нового вычисляемого значения вводим формулу.

§ Перед формулой указываем название поля, после которого ставим двоеточие.

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

В нашем примере были созданы 3 запроса такого типа:

Начисление Заработной платы

Табельный номер	ФИО работника	Оклад	Северные	Районные	Начислено
	Демидова Ольга Юрьевна	1 650,00р.	825,00р.	495,00р.	2 970,00р.
	Кравцова Марина Степановна	1 920,00р.	960,00р.	576,00р.	3 456,00р.
	Апрелин Андрей Егорович	1 460,00р.	730,00р.	438,00р.	2 628,00р.
	Власов Петр Петрович	1 960,00р.	980,00р.	588,00р.	3 528,00р.
	Фёдорова Светлана Олеговна	1 235,00р.	617,50р.	370,50р.	2 223,00р.
	Седых Анжела Валерьевна	1 467,00р.	733,50р.	440,10р.	2 640,60р.
	Якимова Дарья Николаевна	1 500,00р.	750,00р.	450,00р.	2 700,00р.
	Орлова Нина Григорьевна	1 350,00р.	675,00р.	405,00р.	2 430,00р.

Где:

Северные: [Оклад]*0,5

Районные: [Оклад]*0,3

Начислено: [Оклад]+[Северные]+[Районные]

 

Удержания из заработной платы

ФИО работника	Начислено	ПФ	Подоходный налог	Удержано
Апрелин Андрей Егорович	2 628,00р.	26,28р.	338,22р.	364,50р.
Власов Петр Петрович	3 528,00р.	35,28р.	402,57р.	437,85р.
Демидова Ольга Юрьевна	2 970,00р.	29,70р.	347,92р.	377,62р.
Кравцова Марина Степановна	3 456,00р.	34,56р.	427,63р.	462,19р.
Орлова Нина Григорьевна	2 430,00р.	24,30р.	278,42р.	302,72р.
Седых Анжела Валерьевна	2 640,60р.	26,41р.	322,69р.	349,09р.
Фёдорова Светлана Олеговна	2 223,00р.	22,23р.	268,94р.	291,17р.
Якимова Дарья Николаевна	2 700,00р.	27,00р.	330,33р.	357,33р.

Где:

Отчисления в Пенсионный Фонд (ПФ) ПФ: [Начислено]*0,01

Подоходный налог: ([Начислено]-[ПФ]-([Количество иждивенцев]*132))*0,13

Удержано: [ПФ]+[Подоходный налог]

 

Заработная плата к выдаче

ФИО работника	Начислено	Удержано	Итого
Апрелин Андрей Егорович	2 628,00р.	364,50р.	2 263,50р.
Власов Петр Петрович	3 528,00р.	437,85р.	3 090,15р.
Демидова Ольга Юрьевна	2 970,00р.	377,62р.	2 592,38р.
Кравцова Марина Степановна	3 456,00р.	462,19р.	2 993,81р.
Орлова Нина Григорьевна	2 430,00р.	302,72р.	2 127,28р.
Седых Анжела Валерьевна	2 640,60р.	349,09р.	2 291,51р.
Фёдорова Светлана Олеговна	2 223,00р.	291,17р.	1 931,83р.
Якимова Дарья Николаевна	2 700,00р.	357,33р.	2 342,67р.

Где:

Итого: [Начислено]-[Удержано]

На основе запроса ЗП к выдаче был создан запрос Выдача ЗП, с включением в него поля Рабочий кабинет с целью создания на основе данного запроса отчёта с группировкой.

 

С. Запрос с параметром.

Это специальный вид запроса, который предоставляет возможность выбора данных по определённому параметру из таблиц базы. Создание: в строке Условия отбора тех полей, для которых вы хотите использовать параметры, введите в квадратных скобках текст подсказки

В данном примере был создан Запрос на работника. Параметр «Введите, пожалуйста, ФИО работника». При открытии запроса автоматически выскакивает диалоговое окно с текстом параметра. После введения ФИО появляется следующая информация:

 

Табель-ный номер	ФИО работника	Дата рожде-ния	Должность	Рабочий кабинет	Время работы	Стаж	Оклад	Домашний адрес	Теле-фон
	Седых Анжела Валерьевна	16.10.65	Парикмахер		нечётные числа		1 467,00р.	Строителей д.4 кв.143	1-32-14

 

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

ü Код клиента

ü Начальная дата поставки

ü Конечная дата поставки

В строке Условия отбора вводится следующий параметр: Between [Введите начальную дату поставки] end [Введите конечную дату поставки]. Сначала появится 1-ое диалоговое окно с просьбой ввести начальную дату поставки, а затем 2-ое с просьбой ввода конечной даты поставки.

Примечание: если необходимо вывести данные о параметре с одинаковой начальной буквой (например, о работниках, фамилии которых начинаются на букву А), следует в строке Условия отбора ввести с клавиатуры необходимую букву и поставить три звездочки (например, А***)

 

3. Формы – это великолепное средство для ввода и изменения данных в базе. Ввод информации через формы проще и это позволяет избежать многих ошибок. Формы можно сделать точной копией бумажных бланков с которых осуществляется ввод имеющихся данных. Для создания форм запускаем Мастер форм. Выбираем запросы или таблицы, на основе которых получаем форму, выбираем необходимые поля, внешний вид формы, требуемый стиль. Даём имя форме и нажимаем кнопку Готово.

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

 

 

 

 

 

 

 

 

 

4. Отчёты позволяют получать результаты запросов на бумажных носителях. Отчёт обычно применяется для предоставления руководству предприятия. В данном примере были выполнены 3 отчёта через Мастер отчётов: 2 (Расчётные листы и Ведомость по ЗП) на основе запроса ЗП к выдаче и 1 (Выдача ЗП) – на основе Выдача ЗП, в котором была произведена группировка по рабочему кабинету и подведён итог по полям: Начислено, Удержано, Итого. В отчёте Ведомость по ЗП была произведена группировка по полю ФИО работника и подведён итог по полю Итого, макет – Блок. В отчёте Расчётные листы итог подвёден не был, был выбран макет ступенчатый. Затем выбираем требуемый стиль и называем отчёт. Редактирование отчёта производится в режиме Конструктор.

 

 

Выдача ЗП.

Раб.кабинет ФИО работника Таб. № Начислено Удержано Итого

Орлова Нина Григорьевна 54 2 430,00р. 302,72р. 2 127,28р.

Итого по 2 430,00р. 302,72р. 2 127,28р.

5 Якимова Дарья Николаевна 14 2 700,00р. 357,33р. 2 342,67р.

Итого по 2 700,00р. 357,33р. 2 342,67р.

9 Седых Анжела Валерьевна 26 2 640,60р. 349,09р. 2 291,51р.

Итого по 2 640,60р. 349,09р. 2 291,51р.

11 Власов Петр Петрович 17 3 528,00р. 437,85р. 3 090,15р.

Кравцова Марина 16 3 456,00р. 462,19р. 2 993,81р.

Итого по 6 984,00р. 900,04р. 6 083,96р.

32 Демидова Ольга Юрьевна 11 2 970,00р. 377,62р. 2 592,38р.

Итого по 2 970,00р. 377,62р. 2 592,38р.

120 Фёдорова Светлана 22 2 223,00р. 291,17р. 1 931,83р.

Итого по 2 223,00р. 291,17р. 1 931,83р.

ИТОГО 19947,6 2 577,97р. 17 369,63р.