Лабораторна робота №1 на тему Створення таблиць та Робота з даними, Бази даних та інформаційні системи, ЗНТУ

МІНІСТЕРСТВО  ОСВІТИ І НАУКИ УКРАЇНИ

Запорізький національний технічний університет

Методичні вказівки та завдання до лабораторних робіт з курсу Бази даних та інформаційні системи

за темою 

Робота в СУБД Access з використанням SQL

для студентів денної форми навчання спеціальності 

7.080203 Системний аналіз і управління

2006

Методичні вказівки та завдання до лабораторних робіт з курсу “Бази даних та інформаційні системи” за темою “Робота в СУБД Access з використанням SQL” для студентів денної форми навчання спеціальності 7.080203 Системний аналіз і управління / Укл.
Кузіна В.М.– Запоріжжя: ЗНТУ, 2006.– с. 54. 

Містить індивідуальні завдання, теоретичні відомості та при­клади для виконання лабораторних робіт з курсу “Бази даних та інформаційні системи” для студентів денної форми навчання спеціальності 7.080203 Системний аналіз і управління.

Укладачі:                      Кузіна В.М., ст. викладач

Рецензент:                    Біла Н.І., доцент

Відповідальний за випуск                      Корніч Г.В., професор

Зміст

1 ЛАБОРАТОРНА РОБОТА № 1. 5

1.1 Теоретичні відомості 5

1.2 Створення таблиць бази даних. 8

1.3 Контрольні питання. 12

1.4 Індивідуальні завдання. 13

2 ЛАБОРАТОРНА РОБОТА № 2. 19

2.1 Створення запитів на вибірку. 19

2.1.1 Використання умов у запитах. 19

2.1.2 Створення обчислювальних полів. 21

2.1.3 Запити з параметром.. 23

2.1.4 Пошук записів, що не мають зв'язаних в іншій таблиці 24

2.2 Контрольні питання. 25

2.3 Індивідуальні завдання. 25

3 ЛАБОРАТОРНА РОБОТА № 3. 28

3.1 Використання підсумків у запитах. 29

3.2 Перехресні запити. 31

3.3 Контрольні питання. 32

3.4 Індивідуальні завдання. 33

4 ЛАБОРАТОРНА РОБОТА № 4. 35

4.1 Запити на оновлення. 35

4.2 Створення нової таблиці 36

4.3 Вставка даних з іншої таблиці 37

4.4 Вилучення даних з таблиці 37

4.5 Контрольні питання. 38

4.6 Індивідуальні завдання. 38

5 ЛАБОРАТОРНА РОБОТА № 5. 41

5.1 Інструкція  SELECT  мови SQL.. 41

5.1.1 Розділ  SELECT. 42

5.1.2 Розділ  FROM... 42

5.1.3 Розділ  WHERE.. 44

5.1.4 Розділ  GROUP BY. 44

5.1.5 Розділ  HAVING.. 45

5.1.6 Розділ  ORDER BY. 45

5.1.7 Оператор  UNION.. 45

5.1.8 Розділ  INTO.. 46

5.2 Інструкція  INSERT.. 47

5.3 Інструкція  UPDATE.. 48

5.4 Інструкція  DELETE.. 49

5.5 Інструкція TRANSFORM... 49

5.6 Предикати. 50

5.7 Контрольні питання. 51

5.8 Індивідуальні завдання. 51

ЛІТЕРАТУРА.. 54

Лабораторна робота №1 на тему Створення таблиць та Робота з даними

Мета роботи: навчитися створювати таблиці даних в СУБД Access, встановлювати зв’язки між ними та вводити дані.

1.1 Теоретичні відомості

Вся база даних в Access зберігається у вигляді одного файлу з розширенням . mdb. У вікні бази даних знаходиться список об’єктів, які можуть входити до БД: таблиці, запити, форми, звіти, сторінки, макроси та модулі.

Вікно конструктора таблиць складається з двох частин. У верхній частині в стовпчику Имя поля можна записувати унікальні імена полів українською, російською або англійською мовою. Для них рекомендується використовувати короткі слова. Якщо при відобра­женні таблиці потрібний якийсь інший заголовок, його можна ввести в полі Подпись в нижній частині вікна. В стовпчикуОписание запису­ються пояснення. Надавати їх не обов’язково. В стовпчикуТип данных за умовчанням встановлений Текстовый тип. Інші типи даних можна вибрати зі списку.

Ключ (первинний ключ, PrimaryKey) – це одне поле або сукуп­ність полів, які однозначно визначають запис. Значення в полі таб­лиці, для якого встановлений ключ, не можуть повторюватися, їх треба вводити обов’язково, вони не можуть мати значення Null. Пер­винний ключ в кожній таблиці може бути лише один. Крім первин­ного ключа в деяких таблицях можливо існування потенційних клю­чів. Інколи в таблиці немає полів, які можна визнати ключовими. Тоді Access пропонує створити додаткове поле, за звичай типу лічильник. Можна погодитись або відмовитись. Щоб призначити поле ключовим, треба встановити курсор у полі і вибрати кнопку Ключове поле на панелі інструментів.

В нижній частині вікна конструктора можна задавати певні вла­стивості(Свойства поля). Наприклад, для ключового поля власти­вість Индексированное поле повиннамати значенняДа (Совпадения не допускаются), яке вибирається зі списку.

Властивість Маска ввода використовується найчастіше для спрощення вводу дати, номеру телефону та ін. Вона визначає формат значень та полегшує введення даних в поле. При створенні Маски ввода можна використовувати спеціальні знаки. Кожний знак визна­чає, що саме можна ввести. Крім того, визначається які символи треба вводити обов’язково, а які – ні. Деякі символи маски наведені в таблиці 1.1. Їх можна використовувати й для виведення.

Таблиця 1.1 – Символи маски

Властивість Условие на значение не дозволяє вводити в таб­лиці, форми та запити значення, які не задовольняють вказаній умові. Для запису умов можна використовувати звичайні символи відношень (=, <>, <, <=, тощо), логічні оператори AND(логічне І), OR(логічне АБО), NOT(логічне НІ) та оператори порівняння.

Наприклад, умова >= 10 AND  < =20 означає, що значення в полі знаходяться між числами 10 та 20.

Оператор BETWEENперевіряє, що значення поля знаходиться між вказаними числами. Наприклад, вираз BETWEEN 10 AND 20 теж означає, що можна вводити числа від 10 до 20 включно.

Оператор IN(список) перевіряє чи належить введене значення вказаному списку. Наприклад, вираз IN( „ІОТ”; „ФЕУ ”; „ГФ”) озна­чає, що можна вводити тільки вказані факультети.

Оператор LIKEшаблон перевіряє відповідність введених сим­волів вказаному шаблону (див. табл. 1.2).

Таблиця 1.2 – Символи для оператора LIKE

Наприклад, вираз LIKE  „[А – Г]* ” означає, що можна ввести слово, яке починається з літер А, Б, В або Г.

LIKE  „[ !А – Г]* ” – введене слово не повинно починатися з однієї з літер А, Б, В або Г.

LIKE  „К * ” – можна ввестислово, яке починається з літери К.

LIKE  „[ 2 – 5] ” – можна ввестиодну з цифр від 2 до 5 включно.

LIKE  „[А а]* ” означає, що слово може починатися з А або а.

Якщо значення в полі відсутнє, то використовується строкова константа ² ² (подвійні лапки, між якими немає пробілу).

Умова IsNull означає, що значення в полі не визначене.

1.2 Створення таблиць бази даних

Завдання. Створити базу даних Підприємство для обліку робіт­ників та їхньої зарплати.

Створимо базу даних з трьох таблиць: таблиці tViddily (Відділи), таблиці tVidom (Відомості про робітників) і таблиці tZarobitok (Заро­бітна плата).

Викличемо СУБД Access, в меню File (Файл) виберемо пункт Создать, а потім Новая база даннях. Відкриється вікно створення БД, в якому треба обрати папку своєї групи для збереження БД і в полі Имя файла ввести ім’я бази даних – Підприємство.

У вікні бази даних виберемо зі списку об’єктів пункт Таблицы та двічі клікнемо рядок Создание таблицы в режиме конструктора. У вікні конструктора задамо структуру таблиці tViddily (рис. 1.1). Для полів pidrozdil та shefвластивістьРазмер поля встановимо рівною 15. 

Рисунок 1.1 – Структура таблиці  tViddily (Відділи). 

Рисунок 1.2 – Вікно для зберігання таблиці.

Далі встановимо курсор на поле pidrozdil і натиснемо кнопку Ключевое поле на панелі інструментів. Вийдемо з конструктора та збережемо таблицю під іменем tViddily (див. рис. 1.2).

Потім задамо структуру ще двох таблиць:  tVidom – Відомості про робітників та tZarobitok – Заробітна плата (табл. 1.3 та 1.4).

Таблиця 1.3 – Структура таблиці tVidom – Відомості про робітників

Значення в полі pidrozdil в таблицях tViddily та tVidom повинні співпадати. Тому для визначення типу цього поля виберемо зі списку пункт Мастер подстановок. На першому етапі роботи майстра треба вибрати один з перемикачів:

• Объект будет использовать значения из таблицы или запроса.

• Будет введен фиксированный набор значений.

Виберемо перший варіант. Далі вкажемо таблицю, з якої треба вибирати значення, тобто tViddily. Потім визначимо поле pidrozdil, з якого треба буде брати значення і встановимо ширину стовпчика.

Після цього на вкладці Подстановка автоматично в рядку Источник строк запишеться вираз:

SELECT tviddily.pidrozdil FROM tviddily;

Для поля Стать аналогічним чином введемо можливі варіанти: чоловік та жінка. Можна задати Маску ввода для поля Дата народ­женняу вигляді99.99.9999. Встановимо ключ на поле Табельний номер та збережемо таблицю.

Таблиця 1.4 – Структура таблиці  tZarobitok – Заробітна плата

Для таблиці tZarobitok тип даних в полі tn визначимо за допомо­гою Мастера подстановок. Якщо при виборі полів вказати два поля tnта fioз таблиці tVidom і встановити прапорець Скрыть ключевой столбец, то при заповненні даними таблиці tZarobitok в списку будуть відображуватися прізвища робітників, а записуватись до таблиці бу­дуть їх табельні номери (див. рис. 1.4).

В цій таблиці немає ключового поля.

Тепер встановимо зв’язки між таблицями. На панелі інструмен­тів База данных натиснемо на кнопку Схема данных та мишкою протягнемо лінії від головної таблиці до підпорядкованої як на ри­сунку 1.3. Відкриється вікно Изменение связей, в якому треба встано­вити прапорець Обеспечение целостности данных. Між таблицями встановлюється зв’язок типу один – до – багатьох. 

Рисунок 1.3 – Вікно схеми даних

Спочатку заповнимо даними таблицю tViddily (Відділи), потім таблицю tVidom (Відомості про робітників). Дані для них знаходяться в таблицях 1.5 та 1.6.

Таблиця 1.5 – Вміст таблиці  tViddily

Таблиця 1.6 – Вміст таблиці tVidom

Декілька записів таблиці tZarobitok (Заробітна плата) разом з прізвищами робітників зображені на рис. 1.4. Треба пам’ятати, що в таблиці зберігається тільки числове поле Табельний номер, а прізвище відображується в списку для зручності роботи користувача і до складу таблиці не входить (див. задачу 2.1). 

Рисунок 1.4 – Частина вмісту таблиці tZarobitok

Завдання. Відсортувати таблицю tZarobitok за табельним номе­ром та датою роботи.

Для сортування даних в полі за зростанням або за спаданням ви­користовуються відповідні кнопки на панелі інструментів. Для сорту­вання даних по двох полях, треба в режимі перегляду таблиці переста­вити поля так, щоб вони були розташовані поруч в потрібному поряд­ку, виділити їх мишкою та натиснути кнопку Сортировка.

Якщо зробити все так, як написано вище, то сортування буде виконано не за табельним номером, а за прізвищем. Існує й інший спосіб сортування по декількох полях. Використаємо його.

Відкриємо таблицю tZarobitok в режимі перегляду. В меню Записи виберемо пункт Фильтр а потім Расширенный фильтр. Від­криється вікно (див. рис. 1.5). В цьому вікні треба вказати поля для сортування tn та dat і в рядку Сортировка  вибрати зі списку варіант по возрастанию. 

Рисунок 1.5 – Сортування по двох полях

Щоб переглянути результати сортування, треба не виходячи з цього вікна, в меню Фильтр обрати пункт Применить фильтр. таблиці tZarobitok.

Для фільтрування даних використовуються кнопки на панелі інструментів або відповідні пункти меню Записи – Фильтр.

1.3 Контрольні питання

1. Визначте основні поняття: база даних, таблиця, поле, запис.

2. Індекси, типи зв’язків між таблицями.

3. Поняття цілісності даних. Як працює контроль цілісності даних в Access?

4. Типи полів і властивості полів.

5. Використання умов на значення полів. Оператори BETWEEN, LIKEтаIN.

6. Сортування даних.

7. Створення і застосування фільтрів.

1.4 Індивідуальні завдання

Створити таблиці бази даних для свого варіанту в Access:

• задати структуру таблиць та підписи полів на українській або російській мові.;

• використати Мастер подстановок там, де це можливо;

• визначити ключові поля;

• встановити зв’язки між таблицями;

• там, де потрібно, використовувати маски для введення даних;

• заповнити таблиці узгодженими даними. Кількість записів у головній таблиці повинна бути не менш, ніж 5, а в зв’язаних – не менш, ніж 10. Встановити зв’язок між таблицями, здійснити контроль цілісності даних.

Варіант 1

Фірма – постачальник добрив працює з певними замовниками. Фірма надає такі пільги: за замовлення у розмірі від 50 тон до 100 тон включно – сплачується 90% вартості, від 100 до 200 тон – 85%, за 200 тон та більше – 83%. Для реєстрації замовлень, збереження інформації про замовників, про асортимент добрив та обробки цієї інформації необхідно розробити базу даних “Постачання добрив”, яка склада­ється з трьох таблиць.

“Добрива”.

Поля: назва добрива; код добрива; виробник; норма використання на один гектар; вартість однієї тони; дата виготовлення; термін збері­гання в місяцях.

“Господарства”.

Поля: код замовника; назва господарства – замовника; область; теле­фон; прізвище голови господарства.

“Замовлення”.

Поля: код замовлення; код замовника; дата замовлення; код добрива; категорія пільг; площа для обробки; дата постачання. Поле категорія пільг на етапі створення таблиці вільне і заповнюється значеннями за допомогою запиту.

Варіант 2

Фірма надає транспортні послуги. Вона використовує різні види транспорту. При реєстрації замовлення фіксуються дані про замов­ників та вимоги до перевезення. На перевезення існують такі пільги: 

для перевезень на відстань від 100 до 499 км – знижка на 5%;

для перевезень на відстань від 500 до 999 км – 10%;

для перевезень на відстань більше 1000 км – 15%.

Для зберігання та обробки цієї інформації розробити базу даних ”Перевезення”, що містить три таблиці.

”Транспорт”.

Поля: код транспортного засобу; його назва; вартість т/км; максималь­ний об’єм ван­тажу; максимальна маса вантажу.

”Замовники”.

Поля: код замовника, назва або прізвище замовника, адреса, телефон, розрахунковий рахунок.

”Замовлення на перевезення”.

Поля: номер замовлення; дата перевезення; відстань; маса вантажу; об’єм ван­тажу; код транспортного засобу; код замовника.

Варіант 3

Туристична фірма розміщує гостей міста у готелях. Фірма надає пільги для гостей з дітьми. При поселені їх у двох або трьохмісних номерах сплачується 80% від вартості проживання. Для отримання та обробки інформації про вільні місця у готелях міста та їх мешканцях розробити базу даних “Готелі міста” з трьох таблиць.

“Готелі”.

Поля: назва готелю; код готелю; рівень сервісу (вибір із списку від 1 до 5 зірок); адреса; район міста; кількість номерів люкс; кількість одномісних номерів; кількість двомісних номерів; кількість трьохміс­них номерів; вартість проживання у номері люкс; вартість проживання в одномісних номерах; вартість проживання у двомісних номерах; вартість проживання у трьохмісних номерах.

“Гості міста”.

Поля: код гостя; прізвище, ім’я та по батькові гостя; країна постійного проживання; з дітьми чи ні.

“Замовлення”.

Поля: код замовлення; код гостя; код готелю; тип номеру; дата посе­лення; кількість діб.

Варіант 4

Для обслуговування потреб будівельного майданчика розробити базу даних “Будівництво”, що складається з трьох таблиць. Таблиця “Матеріали” містить дані про всі ті будівельні матеріали, які можуть бути поставлені на майданчик. Необхідно передбачити, що матеріали можуть мати однакові назви, але різні коди, тому що відрізняються по інших атрибутах, а різні виробники можуть встановлювати різні ціни.

“Матеріали”.

Поля: код матеріалу; назва матеріалу; марка; виробник; код постачаль­ника; одиниця виміру; вартість одиниці; мінімальна партія.

“Постачальники”.

Поля: код постачальника; назва постачальника; адреса; телефон; прі­звище начальника.

“Замовлення”.

Поля: код замовлення; дата замовлення; код матеріалу; замовлена кількість; дата постачання.

Варіант 5

Для ведення підрахунків оплат за використану електроенергію споживачами розробити базу даних “Сплати за електроенергію ”, що містить три таблиці. Таблиця “ Пільги” відображає відсотки пільг, які надаються при сплаті споживачам деяких категорій (ветерани ВВВ, інваліди, чорнобильці та т. і.).

Таблиця “Платежі” – це журнал оплат споживачів протягом року. Будемо вважати, що на початку кожного року підсумовуються внесені платежі за рік, робиться перерахунок, і сума заборгованості вноситься до таблиці “Споживачі”.

“Пільги”.

Поля: код пільг; назва пільгової категорії; відсоток сплати.

“Споживачі”.

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

“Платежі”.

Поля: код споживача; дата платежу; попередній показник лічильника; останній показник лічильника; внесена сума.

Вартість 1 Квт/год визначити самостійно. Поле внесена сума на етапі створення таблиці вільне і заповнюється за допомогою запиту.

Варіант 6

Міський молокозавод виконує замовлення магазинів на поста­чання своєї продукції. Для реєстрації замовлень, збереження інформа­ції про замовників, про асортимент продукції, що виробляється, та ор­ганізації поставок необхідно розробити базу даних “Молокозавод”, яка складається з трьох таблиць.

“Продукція”.

Поля: код продукції; назва продукції; жирність; виробник; вартість одиниці продукції; мінімальна партія; термін зберігання в днях.

“Магазини”.

Поля: код магазина – замовника; назва магазину; адреса; телефон; прізвище директора магазину.

“Замовлення магазинів”.

Поля: код замовлення; дата замовлення; код магазина – замовника; код продукції; обсяг замовленої партії.

Варіант 7

Автомобільний завод випускає різні моделі автомобілів. Для за­безпечення потреб складального цеху автозаводу розробити базу да­них “Автозавод”, яка містить три таблиці.

Таблиця “Вузли” містить дані про вузли автомобілю, що постав­ляються на завод. Необхідно передбачити, що вузли можуть мати од­накові назви, але відрізняться по інших атрибутах.

“Постачальники”.

Поля: код постачальника; назва постачальника; адреса; телефон; прі­звище начальника.

“Вузли”.

Поля: назва вузла; код вузла; виробник; вартість одиниці продукції; мінімальна партія; код постачальника.

“Замовлення”.

Поля: код замовлення; дата замовлення; код вузла; замовлена кіль­кість; дата постачання, на яку потрібно отримати замовлення.

Варіант 8

Для автоматизації роботи у касах аеропорту розробити базу да­них “Продаж авіаквитків”, що містить три таблиці. При сплаті за кви­тки надаються такі пільги: ветеранам війн – 20% від вартості квитка; дітям – 50%; працівникам авіа сервісу – безкоштовно (100%). Таблиця “Продаж” – це журнал продажу квитків у касі.

“Рейси”.

Поля: номер рейсу; бортовий номер; аеропорт вильоту; аеропорт при­значення; пункти посадок поміж ними. Будемо вважати, що один номер рейсу обслуговує один бортовий номер літака.

“Авіалайнери”.

Поля: бортовий номер; тип літака; кількість місць в бізнес–класі; вар­тість цих квитків; кількість місць першого класу; вартість цих квитків; кількість місць другого класу; вартість цих квитків.

“Продаж”.

Поля: номер рейсу; дата вильоту; тип салону; кількість квитків; розмір пільг; дата продажу. Тип салону та розмір пільг вибирати зі списку.

Варіант 9

Розробити базу даних “Комерційна хірургічна лікарня”, що складається з трьох таблиць. Розрахунки робити за такими правилами. Усі пацієнти поділяються на категорії:

ü  перша – пільгова, пацієнти обслуговуються безкоштовно (вете­рани війн, діти до 5 років, чорнобильці);

ü  друга – 50% сплати (пенсіонери, інваліди );

ü  третя – повна сплата.

“ Хірурги”.

Поля: код лікаря; прізвище, ім’я та по батькові; дата народження; категорія; стать; домашній телефон .

“ Тарифи”.

Поля: код операції; назва операції; вартість операції; вартість лікуван­ня за добу післяопераційної реабілітації.

“Пацієнти”

Поля: прізвище, ім’я та по батькові; дата народження; стать; категорія пацієнта; дата операції; код операції; термін лікування; код хірурга, що робив операцію.

Варіант 10

Видавництво книг виконує замовлення магазинів на поставку книг для продажу. Від кількості замовлених книг залежить розмір пільг, що надає видавництво: 1000 примірників та більше – 90% вар­тості, 2000 примірників та більше – 87%, більше 3000 – 85%. Для ре­єстрації замовлень, збереження інформації про замовників, про книж­кові видання та організації поставок необхідно розробити базу даних “Видавництво”, яка складається з трьох таблиць.

“Книжкові видання”.

Поля: код книги; автор; назва книги; тираж; кількість сторінок; рік видання;отпускна ціна.

“Магазини”.

Поля: код магазина–замовника; назва магазину; адреса; район міста; телефон; прізвище директора магазину.

“Замовлення магазинів”.

Поля: номер замовлення; дата замовлення; код замовника; код книги; замовлена кількість примірників; відправлена кількість; дата поставки.

Варіант 11

Розробити довідкову систему “Облік продукції” по асортименту продукції, що виробляється на промисловому підприємстві різними цехами та перевозиться до складу. Необхідно передбачити, що про­дукція може бути різного ґатунку. При передачі партії продукції на склад вона реєструється. Для цього створіть базу даних, що містить три таблиці. В таблицю “Продукція” заносяться дані про продукцію, що виробляється підприємством. Таблиця ”Склад” містить дані про партію продукції, що розміщується на складі.

”Цехи”.

Поля: номер цеха, прізвище начальника цеха, телефон.

“Продукція”.

Поля: код продукції; назва продукції, вартість одиниці продукції ви­щого ґатунку, вартість одиниці продукції першого ґатунку, вартість одиниці продукції другого ґатунку, мінімальна партія.

”Склад”.

Поля: код партії; дата реєстрації; код продукції; номер цеха; показник якості (ґатунок); об’єм партії продукції.

Варіант 12

Розробити базу даних “Сплати за телефон” для ведення розра­хунків за телефон абонентами телефонної мережі. База даних склада­ється з трьох таблиць. Таблиця “Абоненти” містить інформацію про абонентів. Будемо вважати, що першого числа кожного місяця до зна­чень полів “кількість несплачених хвилин” автоматично заноситься кількість хвилин за розмови минулого місяця.

Таблиця “Платежі” містить відомості про платежі за поточний місяць і щомісячно оновлюється. Плата за розмови повинна вноситися до 15 числа. Пеня, в розмірі 1% від вартості розмов, стягується, якщо сплачено не вчасно. Вартість однієї хвилини для кожного виду розмов (міських, міжміських та міжнародних) встановити самостійно.

Таблиця “Пільги” відображує відсоток пільг на всі розмов, які надаються абонентам певних категорій (інваліди, чорнобильці та ін.).

“Абоненти”.

Поля: прізвище, ім’я та по батькові абонента; номер телефону; код пільг; кількість несплачених хвилин за міські розмови; кількість не­сплачених хвилин за міжміські розмови; кількість несплачених хвилин за міжнародні розмови.

“Пільги”.

Поля: код пільг; назва пільгової категорії; відсоток сплати.

“Платежі”.

Поля: номер телефону; дата платежу; сплачена сума (це поле на етапі створення таблиці вільне і заповнюється за допомогою запиту).

З повагою ІЦ "KURSOVIKS"!