Лекция 6 Основы создания и использования списков баз

Лекция 6 Основы создания и использования списков (баз данных) в электронной таблице 1

Вопросы 1. Общие сведения, понятия и определения 2. Создание и ведение баз данных в электронной таблице 3. Анализ данных 4. Консолидация 2

6. 1. Общие сведения, понятия и определения n Базами данных называют совокупность n n данных, совместно используемых различными задачами в рамках единой автоматизированной информационной системы. Основными понятиями теории баз данных являются: предметная область – часть реального мира, подлежащая изучению с целью организации управления в этой сфере и последующей автоматизации процесса управления; объект – элемент информационной системы, сведения о котором хранятся в базе данных. Иногда объект называют сущностью; 3

Общие сведения, понятия и определения n атрибут – это информационное отображение свойств объекта; n ключевой атрибут – это такой атрибут (или группа атрибутов), который позволяет определить значения других атрибутов; n запись данных – совокупность значений связанных элементов данных; n первичный ключ это атрибут (или группа атрибутов), который уникальным образом идентифицирует каждый экземпляр объекта (запись). 4

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

Общие сведения, понятия и определения n Программное обеспечение, осуществляющее операции над базами данных получило название система управления базами данных (СУБД). n Основными задачами СУБД являются: обеспечение хранения и использования данных; обеспечение пользователей языковыми средствами (прикладные программы) описания и манипулирования данными; обеспечение поддержки логических моделей данных. Модель данных определяет логическое представление физических данных; 6

Общие сведения, понятия и определения обеспечение операций создания и манипулирования логическими данными (выбор, вставка, обновление, удаление и т. п. ) и одновременное отображение (выполнение) этих операций над физическими данными; обеспечение защиты и целостности (согласованности) данных при их коллективном использовании. n При разработке баз данных разрабатывается информационная и физическая модель данных. 7

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

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

Модели баз данных n Возможны три модели баз данных: сетевые; иерархические; реляционные. 10

Иерархические базы данных n В иерархических СУБД данные представляются в виде древовидной структуры. В этой модели все записи, агрегаты и атрибуты образуют иерархически организованный набор, то есть такую структуру, в которой все элементы связаны отношением подчиненности, и при этом любой элемент может подчиняться только одному какому нибудь элементу. 11

Схема иерархической модели данных Рис. 6. 1. 12

Схема сетевой модели данных n Сетевые СУБД используют модель представления данных в виде произвольного графа. Сетевой подход является развитием иерархической модели. В сетевой модели запись потомок может зависеть не от одного прародителя, а от многих. Рис. 6. 2 13

Реляционная модель n Реляционная модель ориентирована на представление данных в виде таблицы. Таблица реляционной БД представляет собой двухмерный массив и обладает следующими свойствами: каждый элемент таблицы это один элемент данных, повторяющиеся группы отсутствуют; все столбцы (колонки) в таблице однородные. Это означает, что все элементы одного столбца имеют одинаковую природу. Например: марка бензонасоса или раз мер заработной платы; столбцам присвоены уникальные имена; в таблице нет одинаковых строк; в операциях с таблицей ее строки и столбцы могут просматриваться в лю бом порядке и в любой последовательности безотносительно к их информа ционному содержанию и смыслу 14

Монитор Прикла дная програ мма База данных Команды СУБД Файл БД Таблица на экране Имена полей Запи си Рис. 6. 3. Логическая и физическая структура СУБД 15

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

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

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

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

Мощность базы данных n Если таблица включает n столбцов, то она представляет собой отношение степени n. Количество кортежей в отношении (число строк в таблице) определяют его мощность m. Тогда общее количество элементов данных в отношении степени n будет равно n х m. 20

К понятию “КЛЮЧ” n Атрибут, значение которого идентифицирует кортеж, то есть позволяет однозначно выделить его из других кортежей данного отношения, называется ключевым атрибутом или просто ключом. Ключ может включать несколько атрибутов составной ключ или представлять собой только часть значения атрибута частичный ключ. 21

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

2. Создание базы даных

Создание базы данных 24

Команды для работы с базой данных n Для работы со списками используются команды: n - Форма n- Сортировка, n - Фильтр, которые находятся в пункте главного меню Данные. 25

Окно диалога команды Форма 26

Окно диалога команды Сортировка 27

Простой критерий Имя Поля Значение 28

Составные критерии Составной критерий по схеме ИЛИ Имя Поля Значение 1 Значение 2 … Значение. N 29

Составные критерии n Составной критерий по схеме И Имя Поля 1 Значение 1 Имя Поля 2 Значение 2 30

Составные критерии n Составной критерий по схеме ИЛИ-И Имя Поля 1 Значение 1 2 Имя Поля 2 Значение 2 1 Значение 2 2 31

База данных, режим Автофильтра Наименование Цена Количество Бумага 22500 Ватман 15600 Цемент 13600 Клей столярный 11000 Картон 12000 Все) (Первые десять) (Условие) 15 20 34. . . (Пустые) (Не пустые) ( 45 31 27 22 48 32

Пользовательский автофильтр 33

Пользовательский автофильтр 34

Пользовательский автофильтр 35

Блоки критериев 36

Расширенный фильтр 37

Функции для работы с базами данных n БСЧЕТ() количество записей в базе данных, удовлетворяющих заданному критерию; n ДМАКС() – максимальное значение записей, соответствующих критерию, заданному в поле; n ДМИН() минимальное значение записей, соответствующих критерию, заданному в поле; 38

n БДСУМ() – сумма значений записей, соответствующих критерию, заданному в поле и другие функции. n Все функции базы данных имеют одинаковый синтаксис: <Имя_функции>(база_данных, поле, критерий) 39

6. 3. Анализ данных n Итоги n Команда Итоги позволяет получать сводные данные по числовым параметрам: сумму, минимум, максимум, среднее значение и другие статистические данные. Предварительно необходимо определить по какому параметру требуется группировать итоги и отсортировать данные по этому параметру. 40

Итоги 41

Электронная таблица в режиме Промежуточные итоги 42

Сводные таблицы n Команда Сводные таблицы позволяет формировать новые таблицы на базе существующих таблиц в различных разрезах и проводить анализ полученных результатов. Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц за четыре шага. 43

Мастер сводных таблиц шаг 1 44

Мастер сводных таблиц шаг 2 45

Мастер сводных таблиц шаг 3 46

Мастер сводных таблиц - Макет 47

Сводная таблица 48

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

Консолидация n Консолидация может проводиться двумя способами: n 1. Консолидация по расположению ячеек – в этом случае состав и порядок расположения данных во всех диапазонах одинаков; n 2. Консолидация по категориям – в этом случае консолидация проводится на основании одинаковых подписей строк и столбцов диапазонов. 50

Окно диалога Консолидация 51

Найти среднее значение n Алгоритм работы: n выделите диапазон ячеек, равный высоте столбца А, без учета заголовков; n введите команду Данные, Консолидация; n активизируйте строку ввода “Ссылка”, выделите первый диапазон в столбце А и щелкните по кнопке Добавить; n 52

n активизируйте строку ввода “Ссылка”, выделите второй диапазон в столбце В и щелкните по кнопке Добавить; n выберите функцию “Среднее” в списке Функция; n щелкните по кнопке ОК. 53

Листинг 6. 6 А В С 1 2 1, 5 2 3 2, 5 3 4 3, 5 4, 5 5 6 5, 5 6 7 6, 5 7 8 7, 5 8 9 8, 5 54