Методичні вказівки до виконання контрольної роботи та індивідуальні завдання з дисципліни «Створення програмних систем засобами багатокористувальницьких субд»



Скачати 365.58 Kb.
Дата конвертації05.03.2017
Розмір365.58 Kb.


МІНІСТЕРСТВО ОСВІТИ Й НАУКИ, молоді та спорту УКРАЇНИ

НАЦІОНАЛЬНА МЕТАЛУРГІЙНА АКАДЕМІЯ УКРАЇНИ




РОБОЧА ПРОГРАМА,

методичні вказівки та індивідуальні завдання

до вивчення дисципліни «Створення програмних систем засобами багатокористувальницьких СУБД» для студентів спеціальності

8. 03050201, 7.03050201 – економічна кібернетика

Дніпропетровськ НМетАУ 2012
МІНІСТЕРСТВО ОСВІТИ Й НАУКИ, молоді та спорту УКРАЇНИ

НАЦІОНАЛЬНА МЕТАЛУРГІЙНА АКАДЕМІЯ УКРАЇНИ

РОБОЧА ПРОГРАМА,

методичні вказівки та індивідуальні завдання

до вивчення дисципліни «Створення програмних систем засобами багатокористувальницьких СУБД» для студентів спеціальності

8. 03050201, 7.03050201 – економічна кібернетика
ЗАТВЕРДЖЕНО

на засіданні кафедри

економічної інформатики

Протокол № 11 від 02.02.2012



Дніпропетровськ НМетАУ 2012

Робоча програма, методичні вказівки та індивідуальні завдання до вивчення дисципліни «Створення програмних систем засобами багатокористувальницьких СУБД» для спеціальності 7(8).03050201 – економічна кібернетика / Укл. Т.О. Климкович. – Дніпропетровськ: НМетАУ, 2012. – 29 с.





Викладені робоча програма, методичні вказівки до виконання контрольної роботи та індивідуальні завдання з дисципліни «Створення програмних систем засобами багатокористувальницьких СУБД», література, що рекомендується для вивчення дисципліни, а також наведено приклад виконання контрольної роботи.

Призначена для студентів спеціальності 8.03050201, 7.050201 – економічна кібернетика заочної форми навчання.

Укладач Т.О. Климкович, ст. викладач

Національна металургійна академія України

49600, Дніпропетровськ-5, пр. Гагаріна, 4



ВСТУП
Сегодня организации сталкиваются с многочисленными проблемами по обработке информации; например, с потребностью в более быстрых и основанных на управлении данными решениях, с потребностью увеличить производительность и мобильность штата разработчиков, а также с настойчивыми требованиями уменьшить общий бюджет отдела информационных технологий, увеличивая инфраструктуру в соответствии со строго возрастающими требованиями. Microsoft SQL Server 2005 призван помочь предприятиям справиться с этими проблемами.

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

По модели данных SQL Server 2005 относится к реляционным базам данных (БД). Эти модели характеризуются простотой структуры данных, удобным для пользователя табличным представлением и возможностью использования формального аппарата алгебры отношений и реляционного исчисления для обработки данных.

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


1РОБОЧА ПРОГРАМА ДИСЦИПЛІНИ «Створення програмних систем засобами багатокористувальницьких СУБД»

1.1Мета вивчення дисципліни


Мета вивчення дисципліни «Створення програмних систем засобами багатокористувальницьких СУБД» – придбання знань в області теорії реляційних баз даних, , а також практичних навичок проектування та створення реляційних баз даних у середовищі SQL Server 2005, і їхнього використання для пошуку, обробки й аналізу даних, необхідних для прийняття ефективних управлінських рішень.

У результаті вивчення дисципліни студент повинен:



знати:

  • сутність моделі баз даних «клієнт/сервер»;

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

  • зв'язки, що підтримуються між об'єктами в базі даних;

  • архітектуру СУБД SQL Server 2005;

  • мову визначення даних у SQL Server 2005;

  • мову маніпулювання даними у SQL Server 2005.

уміти:

  • виконувати проектування реляційної бази даних;

  • створювати базу даних у SQL Server 2005;

  • створювати запити у SQL Server 2005.



1.2Розподіл годин за навчальним планом


Дисципліна вивчається в 1 семестрі. Відповідно до навчального плану на вивчення дисципліни «Створення програмних систем засобами багатокористувальницьких СУБД» для всіх форм навчання заплановано 72 години, які для студентів заочної форми навчання розподілені по видах занять у такий спосіб:

аудиторні заняття – 8 годин;

з них:

лекції – 4 години;



лабораторні заняття – 4 години;

самостійна робота – 64 години.


1.3Зміст дисципліни


ТЕМА1. Введення в бази даних и SQL.

Клієнт/серверна модель бази даних. Основні поняття реляційні бази даних. Нормалізація. Нормальні форми. Ключі. Зв’язки між об'єктами в базі даних. Мова SQL.



ТЕМА2. Мова маніпулювання даними у SQL Server 2005.

Мова Т-SQL. Типи даних у SQL Server 2005. Запит. Структура запиту. Читання даних (SELECT). Вибір джерел даних (FROM). Умови відбору (WHERE). Сортування даних. Агрегування даних з використанням конструкції GROUP BY. Функції для агрегування: SUM, СОUNT, AVG, MIN і MAX. Видалення даних (DELETE). Вставка даних (INSERT, INSERT INТО…SELECT). Оновлення даних (UPDATE).



ТЕМА3. Мова програмування у SQL Server 2005.

Вирази. Оператор конкатенації рядків (+). Оператор ділення по модулю (%). Оператори AND (&), OR (|). Конструкції begin … end, if … else, case … end, while … break … continue.



1.4Рекомендована література


  1. Виейра Роберт. Программирование баз данных Microsoft SQL-Server 2005. Базовый курс. Диалектика. Москва, Санкт - Петербург, Киев. 2007, - 924 с.

  2. Нильсен Пол. Microsoft SQL-Server 2005. Библия пользователя. :Пер. с англ. - М.:ООО «И.Д. Вільямс», 2008. – 1232 с.

  3. Ржеуцкая С.Ю. Базы данных. Язык SQL: учеб. пособие / С.Ю. Ржеуцкая. – Вологда: ВоГТУ, 2010. – 159 с. [Електронний ресурс]. Режим доступу: http://depositfiles.com/files/5pvx3q33r

  4. Фленов М.Е. Transact-SQL. – СПб.:БХВ-Петербург, 2006. – 576 с. [Електронний ресурс]. Режим доступу: http://depositfiles.com/files/zi2hu78xk

  5. Андон Ф., Резниченко В. Язык запросов SQL. Учебный курс. – СПб.: Питер; Киев: Издательская группа ВHV, 2006. – 416 с. [Електронний ресурс]. Режим доступу: http://depositfiles.com/files/7leqf6of1

2МЕТОДИЧНІ ВКАЗІВКИ ДО ВИКОНАННЯ КОНТРОЛЬНОЇ РОБОТИ

2.1Порядок підготовки та захисту контрольної роботи


Навчальним планом з дисципліни «Створення програмних систем засобами багатокористувальницьких СУБД» передбачено виконання однієї контрольної роботи студентами заочного факультету. Контрольна робота виконується протягом семестру після установчих занять. Робота подається особисто або надсилається в деканат заочного факультету для реєстрації. Потім робота передається на кафедру економічної інформатики для перевірки викладачем. Контрольна робота, що виконана з помилками й відхиленнями від вимог методичних вказівок, повертається студентові для доопрацювання. Захист контрольної роботи здійснюється під час екзаменаційної сесії.

2.2 Загальні вимоги до пояснювальної записки


Контрольну роботу виконують з використанням комп'ютера і уручну (завдання №2) на аркушах білого паперу формату А4 з одного боку аркуша. Розмір полів аркуша: верхнє та нижнє – 2см, ліве – 2,5см, праве – 1см. Сторінки нумерують арабськими цифрами, додержуючись наскрізної нумерації. Номер друкують у верхньому куті сторінки без крапки в кінці. Текст роботи вирівнюється «по ширине», абзацний відступ – 1,5см, міжрядковий інтервал – «полуторный», тип шрифту – Times New Roman, накреслення – «обычный», розмір – 14, колір – чорний.

Студент зобов’язаний надати електронний варіант виконаної контрольної роботи у якості додатка до контрольної роботи.



2.3Завдання і методичні вказівки до виконання контрольної роботи


Контрольна робота складається з 3 завдань. Кожне завдання повинне бути виконано відповідно до одного з варіантів, наведених у додатку А. Вибір варіанта для кожного завдання здійснюється відповідно до номера студента в журналі групи. При оформленні контрольної роботи слід обов’язково наводити завдання. Приклад виконання контрольної роботи наведено у додатку Б.

Завдання №1 на тему «СУБД SQL Server 2005» є теоретичною частиною контрольної роботи. Виконується на основі вивчення літературних джерел по запропонованій темі відповідно до одного з варіантів. Обсяг завдання – 5-6 сторінок формату А4 документа Word.

Завдання №2 на тему «Нормалізація даних» є практичною частиною контрольної роботи. Розгляньте систему, опис якої наведено у додатку А, відповідно до одного з варіантів. Для даної системи виконайте приведення даних до третьої нормальної форми (поетапно). Приведіть структуру нормалізованої бази даних із вказівкою ключів і зв'язків між таблицями. Приведіть структуру кожної таблиці. Завдання виконується уручну.

Завдання №3 на тему «Створення бази даних і запитів у середовищі СУБД SQL Server 2005» також є практичною частиною контрольної роботи.

У процесі виконання цього завдання студент повинен:

створити базу даних (таблиці і діаграму), яка була спроектована у другому завданні, у середовищі СУБД SQL Server 2005;

увести у таблиці дані (по 5-7 записів у таблиці на стороні зв’язку «один» і по 25-30 у таблиці на стороні зв’язку «багато»);

створити запити відповідно до одного з варіантів (задавати стовбцям псевдоніми), виконати запити, результати виконання запитів вивести у файл;

створити самостійно 3 запита на вибірку даних з двох таблиць з використанням фільтрування (WHERE), групування (GROUP BY), сортування даних, а також використання стовбців з даними, які розраховуються. Виконати їх і результати виконання запитів вивести у файл;

створити самостійно 2 запита на вставку даних у таблиці (оператори INSERT і INSERT INТО…SELECT), виконати запити, показати результати виконання запитів;

створити самостійно 2 запита на модифікацію даних у таблицях (оператор UPDATE), виконати запити, показати результати виконання запитів.

У контрольній роботі спочатку надається формулювання запиту (завдання), а потім запит на мові Т-SQL і результати виконання запитів. Базу даних, запити і результати виконання запитів обов’язково зберігати на диску і мати його при захисті контрольної роботи.

Додаток А


Варіанти контрольних завдань

А.1 Варіанти до завдання №1


  1. Бази даних архітектури «клієнт/сервер».

  2. Архітектура СУБД SQL Server 2005.

  3. Огляд найбільш важливих об'єктів SQL Server 2005

  4. Об'єднання даних (внутрішні, зовнішні, перехресні) в мові Т-SQL.

  5. Способи перенесення баз даних SQL Server 2005 з одного сервера на іншій.

  6. Резервування баз даних SQL Server 2005

  7. Відновлення баз даних SQL Server 2005.

  8. Робота з журналом транзакцій у СУБД SQL Server 2005.

  9. Система безпеки СУБД SQL Server 2005.

  10. Огляд утиліти Management Studio СУБД SQL Server 2005.

  11. Оглядач об'єктів (Object Explorer) утиліти Management Studio.

  12. Створення таблиць і діаграм у середовищі Management Studio.

  13. Конструктор запитів утиліти Management Studio.

  14. Створення сценаріїв та пакетів у СУБД SQL Server 2005.

  15. Процедури, що зберігаються, у SQL Server 2005.



А.2 Варіанти до завдання №2


  1. Опис системи оперативного аналізу виробництва прокату:

Дані плану виробництва прокату: місяць, номер цеху, найменування цеху, найменування продукції, кількість прокату за планом у тонах.

Дані про фактичний випуск прокату: дата виробництва прокату; найменування цеху; найменування продукції; кількість фактично виробленого прокату у тонах. У кінці місяця розраховується відхилення від плану виробництва прокату у тонах та у відсотках.



  1. Опис системи нарахування заробітної платні робітникам:

ПІБ робітника; табельний номер, цех в якому він працює, місяць; найменування деталі; план виробітку деталі на місяць; дата вироблення деталі; фактично вироблено деталей за зміну; вартість виробництва деталі. На початку наступного місяця робітнику нараховується зарплатня і премія. Розрахунок зарплатні: кількість вироблених деталей за місяць помножену на вартість виробництва деталі. При виконанні плану за місяць робітникові нараховується премія у розмірі 10% від його заробітної плати за місяць.

  1. Опис системи обліку договорів на постачання продукції:

Інформація про договори: № договору, дата оформлення договору, дата закінчення договору, найменування замовника, адреса замовника, банківський рахунок замовника, найменування продукції, ціна продукції, кількість продукції, сума згідно договору.

Дані про оплату за договором: № договору, дата сплати, сума сплати, залишок по оплаті (розраховується як різниця між сумою згідно договору та сумою оплат).



  1. Опис системи обліку і нарахування відсотків по вкладах банку:

ПІБ вкладника, адреса вкладника, телефон вкладника, дата відкриття рахунку, вид вкладу, розмір річної процентної ставки, термін зберігання, номер рахунку, сума вкладу, дата закриття рахунку, сума процентів. Термін зберігання вкладу та розмір річної процентної ставки залежить від виду вкладу. Нарахування процентів відбувається тільки після закінчення терміну зберігання вкладу.

  1. Опис системи нарахувань доплат на дітей для працівників:

ПІБ робітника; табельний номер; підрозділ; посада; заробітна платня, місяць, кількість дітей віком до 6 років; кількість дітей віком до 12 років, доплата на дітей віком до 6 років, доплата на дітей віком до 12 років. Доплата на дітей здійснюється в залежності від віку дитини і складає 3% від заробітної плати для дітей віком до 6 років і 5% - для дітей віком до 12 років.

  1. Опис системи обліку і аналізу успішності студентів:

Інформація про результати складання заліків: дисципліна, група, ПІБ студента, номер залікової книжки, відмітка про залік. Перелік дисциплін, за якими складаються заліки у кожній групі студентів: група, залікова дисципліна, ПІБ викладача.

  1. Опис системи обліку реалізації продукції підприємством:

Дані про закупівлю товарів підприємством: код товару, найменування товару, одиниця виміру, ціна закупівлі, кількість, дата закупівлі, виробник.

Дані про реалізацію товарів: найменування товару, одиниця виміру, ціна реалізації, кількість, дата реалізації, сума реалізації. Ціна реалізації (на 30% більше ціни закупівлі) та сума реалізації розраховується.



  1. Опис системи обліку і аналіз виконання замовлень:

Інформація про замовлення на продукцію підприємства, № замовлення, замовник, адреса, телефон, найменування продукції, кількість тон, термін поставки, штраф за кожну тону в грн.

Дані про фактичне відвантаження продукції за замовленнями: дата відвантаження, найменування продукції, кількість тон.



  1. Опис системи облік успішності студентів:

Інформація про студентів: прізвище, ім'я, по батькові студента, адреса, телефон, курс, шифр групи.

Інша інформація: дисципліна, шифр та назва кафедри, яка викладає дисципліну, дата здачі екзамену, оцінка.



  1. Опис системи обліку витрат часу на виробництво продукції:

Дані про замовлення на виробництво продукції: № замовлення, дата замовлення, замовник, телефон, найменування продукції, кількість за замовленням.

Інформація про нормативи витрат часу (у хвилинах) на виробництво кожного найменування продукції та розцінки на виробництво.



  1. Опис системи нарахування заробітної плати робітникам підприємства:

Дані обліку виробітку кожним робітником за зміну: ПІБ робітника, найменування деталі, фактично вироблено за зміну.

Інформація про план виробітку за зміну кожним робітником.

Дані про нормативи на виробництво кожного найменування деталей.

Правила штрафування та начислення премії згідно з якими, при виконанні плану виробітки за зміну робітникові належить премія у розмірі 10% від його змінного заробітку, при невиконанні плану – штраф 5%.



  1. Опис системи розрахунку ціни на реалізовану продукцію

Дані про закупівлю товарів: найменування товару, одиниця виміру, ціна закупки за одиницю, кількість, вартість закупки, дата доставки.

Дані товарно-транспортної накладної: найменуванню товару, одиниці виміру, кількість доставленого товару, вартість доставки, дати доставки.

Ціна реалізації = (ціна закупки + вартість доставки/ кількість доставленого товару)*(1+ плановий % прибутку/100).


  1. Опис системи обліку витрат на відрядження.

Інформація про відрядження: ПІБ співробітника, дата виїзду у відрядження, термін відрядження, пункт призначення, аванс, а також дані звіту по фактичних витратах у відрядженні. Норми на витрати за добу за категорією: Україна, країна СНД, дальнє зарубіжжя. Пункт призначення складається з категорії, до якої він належить, и назва населеного пункту.

  1. Опис системи обліку реалізації готової продукції.

Дані про виробництво готової продукції: код продукції, найменування продукції, одиниця виміру, собівартість одиниці продукції, кількість виробленої продукції, дата виготовлення.

Дані про реалізацію цієї продукції: найменування продукції, одиниці виміру, ціна реалізації, кількість реалізованої продукції.

Розрахунок прибутку від реалізації готової продукції = ціна реалізації * кількість реалізованої продукції – собівартість*кількість кількість виробленої продукції.


  1. Опис системи складського обліку товарів.

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

Інформація про списання товарів: група товару, найменування товару, одиниця виміру, кількість списаних товарів, ціни за одиницю.


А.2 Варіанти до завдання №3


  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. список цехів по алфавіту з вказівкою їх номерів,

  2. дані про фактично вироблену продукцію з 15.02.10 по 22.02.10,

  3. план виробництва продукції по цеху №5 на січень місяць,

  4. звіт про виконання плану у наступному вигляді: номер цеху, найменування продукції, кількість прокату за планом, кількість фактично виробленого прокату, відхилення від плану виробництва прокату у тонах, процент виконання плану.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. табельні номера та ПІБ робітників якогось цеху;

  2. Напишіть запит, який виведе план виробництва деталей на березень місяць по кожному робітнику, з вказівкою найменування деталей і кількості деталей за планом. Дані відсортувати по табельному номеру за збільшенням і по кількості деталей за зменшенням.

  3. Напишіть запит, який виведе дані фактичного виробництва деталей з 1.01.10 по 20.01.10. з підсумком по кожному дню, та вказівкою найменшої кількості.

  4. звіт про нарахування зарплатні: табельний номер, ПІБ, кількість вироблених деталей за місяць, вартість виробництва деталі, зарплатня і премія.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. найменування, адреса та банківський рахунок замовників, найменування яких починається з літери М;

  2. дані про продукцію, що має ціну в межах від 50 до 100 грн. у порядку убування ціни;

  3. всі дані, у том числі про оплату, за договором №25;

  4. звіт по кожному договору за станом на поточну дату: № договору, найменування замовника, сума згідно договору, всього сплачено, залишок по оплаті.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. номери рахунків та ПІБ вкладників, що відкрили рахунки до 01.02.12;

  2. дані про вклади у порядку убування сум вкладів із вказівкою дати відкриття рахунку, виду вкладу, розміру річної процентної ставки;

  3. загальні суми вкладів по видах вкладів з процентною ставкою вище 10%;

  4. звіт по нарахуванням відсотків на поточну дату: ПІБ вкладника, дата закриття рахунку, сума вкладу, розмір річної процентної ставки, сума процентів.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. ПІБ, табельний номер та посаду всіх працівників по підрозділам по зростанню табельних номерів.

  2. список працівників, що мають дітей до 6 років.

  3. дані про працівників із зарплатою >2000 грн. із вказівкою ПІБ робітників, підрозділу, посади, заробітної плати з упорядкуванням даних по підрозділу за збільшенням і по заробітній платні по убуванню.

  4. звіт по доплатам на дітей: підрозділ, сума доплат.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. список дисциплін по алфавіту;

  2. список однієї з груп з вказівкою номера залікової книжки;

  3. список дисциплін, по яких не здано заліків, з вказівкою їхньої кількості в порядку убування;

  4. звіт по залікам: група, кількість студентів, кількість незданих заліків, питома вага незданих заліків.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. список закуплених товарів одного з виробників;

  2. дані реалізації за якийсь період;

  3. ціни реалізації у порядку убування ціни;

  4. звіт по закупівлям: код товару, найменування товару, сума закупівлі.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. список замовників по алфавіту з телефонами;

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

  3. дані про відвантаження продукції у порядку убування кількості;

  4. звіт по простроченим замовленням: № замовлення, замовник, найменування продукції, кількість тон, термін поставки, дата відвантаження, штраф.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. список кафедр по убуванню шифру;

  2. оцінки по усіх дисциплінах визначенного студента.

  3. список студентів по групах, на одержання стипендії (середній бал більш 4) з вказівкою середнього бала студента; в порядку убування балу.

  4. звіт про успішність: група, кількість студентів у групі, кількість студентів-відмінників та студентів-боржників;

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. нормативи витрат часу (по убуванню) по кожному найменуванню продукції;

  2. дані по замовленням за період;

  3. найменування продукції і найбільший заказ цієї продукції;

  4. звіт про вартість замовлення: № замовлення, необхідний час на виконання замовлення у годинах, вартість роботи.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. план на зміну одного робітника;

  2. нормативи на виробництво по двом деталям;

  3. список робітників, що не виконали план;

  4. звіт про нарахування заробітної плати: ПІБ робітника, найменування деталі, фактично вироблено за зміну, план виробітку, заробітна платня, премія або штраф, всього до виплати;

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. список доставлених товарів за період;

  2. дата і вартість доставки одного з товарів;

  3. найбільшу вартість доставки;

  4. звіт про розрахунок ціни одиниці реалізованої продукції: найменування товару, кількість, ціна закупки, вартість доставки, сума прибутку, ціна реалізації.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. фактичні витрати у відрядженні по кожному співробітнику;

  2. список співробітників, що були у відряджені за межами України;

  3. кількість відряджень у період;

  4. звіт про затрати на відрядження: ПІБ співробітника, аванс, сума за звітом, відхилення.

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. дані про собівартість продукції по убуванню собівартості;

  2. ціна реалізації якоїсь продукції;

  3. кількість виготовленої продукції за період;

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

  1. Напишіть на мові Т-SQL у середовищі SQL Server 2005 запити для отримання наступної інформації:

  1. відомості про залишки товарів у порядку убування залишку;

  2. список товарів з ціною в будь-якому діапазоні;

  3. інформація про списання товарів однієї з груп товарів;

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

Додаток Б

Приклад виконання завдання №2


на тему «Нормалізація даних»
Завдання. Розгляньте систему збуту товарів, опис якої наведено нижче. Для даної системи виконайте приведення даних до третьої нормальної форми (поетапно). Приведіть структуру нормалізованої бази даних із вказівкою ключів і зв'язків між таблицями. Приведіть структуру кожної таблиці.

Опис системи збуту товарів.

Дані, що містяться в замовленнях на товари: номер замовлення, дата замовлення, ПІБ клієнта, адреса клієнта, телефон клієнта, рядок замовлення (найменування товару, ціна, кількість), загальна сума замовлення.

Насамперед важливо визначити основні сутності, застосовувані в системі. Виявлено одна сутність — замовлення. Тепер виконаємо процес нормалізації.

Заповнимо таблицю «Замовлення» даними.
Таблиця «Замовлення» з даними


Номер замовлення

Дата замов-лення

ПІБ клієнта

Адреса клієнта

Телефон клієнта

Рядок замовлення (найменування, ціна, кількість)

Загальна сума замовлення

100

10.12.09

Іванов

Кірова, 45

35-45-88

Мило, 3,50 грн., 10;

Шампунь, 12,50 грн., 30



410,00

101

11.12.09

Петров

Пушкіна, 43

42-85-72

Мило, 3,50 грн., 15;

Порошок, 8,70грн., 10



139,50

102

15.12.09

Іванов

Кірова, 45

35-45-88

Шампунь, 12,50 грн., 20

250,00

Приведемо дані до першої нормальної форми (1 NF).

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

На підставі аналізу переліку стовпців задамо первинний ключ (первинний ключ представляє унікальний ідентифікатор для кожного рядка). У деяких випадках не вдається знайти навіть один-єдиний потенційний ключ, тому виникає необхідність увести додатковий стовпець, призначений для розв'язку цього завдання. Кожне замовлення повинне мати унікальний порядковий номер, тому стовпець, що містить номер замовлення, будемо використовувати у якості стовпця первинного ключа.

Виключимо з таблиці «Замовлення» три стовпці про клієнтів: «ПІБ клієнта», «Адреса клієнта» і «Телефон клієнта» і помістимо їх у таблицю «Клієнти». Для нової таблиці «Клієнти» необхідно передбачити первинний ключ, щоб забезпечити унікальність даних у кожному рядку. Для таблиці «Клієнти» з даними про замовників може бути запропонований ключ «ПІБ клієнта». Але не можна виключити таку можливість, що в стовпці «ПІБ клієнта» з'явиться інформація про замовників-однофамільцях, тому, щоб однозначно ідентифікувати клієнта введемо для нього номер, який буде унікальним для кожного клієнта. Тому стовпець «Номер клієнта» буде використовуватися в якості стовпця первинного ключа.

Таким чином, частина даних з таблиці «Замовлення» успішно вилучена, але нам необхідно забезпечити можливість звертатися з таблиці «Замовлення» до даних нової таблиці «Клієнти». Саме тому в таблицю «Замовлення» теж додамо стовпець «Номер клієнта». Надалі, після формування необхідних зв'язків, буде створене обмеження зовнішнього ключа, що дозволяє забезпечити наявність у всіх замовленнях дійсних номерів клієнтів.

Нова таблиця з даними про замовників «Клієнти»


Номер клієнта (ПК)

Ф. В. О. клієнта

Адреса клієнта

Телефон клієнта

1

Іванов

Кірова, 45

35-45-88

2

Петров

Пушкіна, 43

42-85-72

Модифікована таблиця «Замовлення»




Номер замовлення (ПК)

Дата замовлення

Номер клієнта

Рядок замовлення (найменування, ціна, кількість)

Вартість замовлення

100

10.12.09

1

Мило, 3,50 грн., 10;

Шампунь, 12,50 грн., 30



410,00

101

11.12.09

2

Мило, 3,50 грн., 15;

Порошок, 8,70 грн., 10



139,50

102

15.12.09

1

Шампунь, 12,50 грн., 20

250,00

Тепер можна перейти до усунення другого порушення вимог до першої нормальної форми, щоб добитися застосування тільки нерозривних даних. Зокрема, вивчення стовпця «Рядок замовлення» показує, що в ньому фактично є присутнім кілька різних фрагментів даних: інформація про найменування замовленого товару, кількості й ціні товару. Окремо взяті фрагменти даних про найменування, кількість і ціні товарів є нерозривними, але втрачають цю властивість після їхнього об'єднання в багатозначні значення.

Здійснимо розбивку багатозначного значення. У ході цього виявляється, що доцільно також увести новий фрагмент інформації, що представляє собою вартість одного рядка замовлення (вартість товару), а вартість усього замовлення виключити.

Після розбивки багатозначного значення з даними про замовлення на кілька фрагментів інформації, виявляється проблема, пов'язана з тим, що первинний ключ більше не може служити унікальним ідентифікатором для рядків; незважаючи на те, що в самих рядках утримується унікальна інформація, значення первинного ключа в деяких з них повторюються. Варіант таблиці «Замовлення», у якім виключені багатозначні значення, але не забезпечується однозначна ідентифікація рядків, наведено нижче.




Номер замовлення

Дата замовлення

Номер клієнта

Найменування товару

Ціна товару, грн.

Кількість товару

Вартість товару

100

10.12.09

1

Мило

3,50

10

35,00

100

10.12.09

1

Шампунь

12,50

30

375,00

101

11.12.09

2

Мило

3,50

15

52,50

101

11.12.09

2

Порошок

8,70

10

87,00

102

15.12.09

1

Шампунь

12,50

20

250,00

Для усунення зазначеної проблеми введемо в таблицю стовпець із номерами рядків замовлення, щоб знову одержати можливість однозначно ідентифікувати рядки таблиці (по двом стовпцям «Номер замовлення» і «Номер рядка замовлення», що й буде становити первинний ключ, називаний також складовий ключ).




Номер замовлення (ВК)

Номер рядка замовлення (ВК)

Дата замовлення

Номер клієн-та

Наймену-вання товару

Ціна товару, грн.

Кіль-кість товару

Вар-тість товару

100

1

10.12.09

1

Мило

3,50

10

35,00

100

2

10.12.09

1

Шампунь

12,50

30

375,00

101

1

11.12.09

2

Мило

3,50

15

52,50

101

2

11.12.09

2

Порошок

8,70

10

87,00

102

1

15.12.09

1

Шампунь

12,50

20

250,00

Тепер усі вимоги до першої нормальної форми виконані. Повторювані групи даних відсутні, і значення у всіх стовпцях є нерозривними (однозначними). Безумовно, виникають проблеми, пов'язані з тим, що деякі дані доводиться повторювати в тому самому стовпці (оскільки всі ці дані ставляться до рядків з однаковим значенням первинного ключа).

Наступний етап нормалізації полягає в переході до другої нормальної форми (2 NF). Застосування другої нормальної форми сприяє подальшому скороченню кількості повторюваних даних (які не повинні обов'язково становити групи). Друга нормальна форма визначається відповідно до двох наведених нижче правил.


  1. Таблиця повинна відповідати вимогам до першої нормальної форми.

  2. Кожний стовпець повинен залежати від усього ключа.

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

Відповіді на ці два питання є відповідно негативною і позитивною. У таблиці «Замовлення» є два стовпці, які залежать тільки від стовпця «Номер замовлення», але не від стовпця «Номер рядка замовлення». Такими є стовпці «Дата замовлення» і «Номер клієнта»: значення в обох стовпцях залишаються однаковими для всього замовлення, незалежно від того, скільки окремих позицій є в замовленні. Для усунення зазначеного недоліку потрібно ввести ще одну таблицю.

Як показує розглянутий приклад, іноді на практиці неможливо представити одну сутність за допомогою однієї таблиці й доводиться розбивати одну таблицю на дві; у зв'язку із цим одна сутність розділяється на дві сутності. Виникає зв'язок двох таблиць, у якій таблиця заголовка відіграє роль свого роду батьківської таблиці стосовно таблиці розшифрування. При цьому таблиця заголовка зберігає інформацію, яку досить представити один раз, а таблиця розшифрування містить інформацію, яка може існувати в декількох екземплярах.

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

Розіб'ємо таблицю «Замовлення» на дві: таблицю «Замовлення» і таблицю «Розшифрування замовлення». Отже, після цих перетворень вимоги до другої нормальної форми виконані. Зокрема, усі стовпці залежать від усього ключа. Проте, ще не всі недоліки усунуті.

Таблиця «Розшифрування замовлення»




Номер замовлення

(ВК)


Номер рядка замовлення (ВК)

Найменування товару

Ціна товару, грн.

Кількість товару

Вартість товару

100

1

Мило

3,50

10

35,00

100

2

Шампунь

12,50

30

375,00

101

1

Мило

3,50

15

52,50

101

2

Порошок

8,70

10

87,00

102

1

Шампунь

12,50

20

250,00

Таблиця «Замовлення»




Номер замовлення (ВК)

Дата замовлення

Номер клієнта

100

10.12.09

1

101

11.12.09

2

102

15.12.09

1

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



  1. Таблиця повинна відповідати вимогам до другої нормальної форми.

  2. У жодному стовпці не повинні проявлятися які-небудь залежності від іншого не ключового стовпця.

  3. Наявність у таблиці похідних даних не допускається.

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

Спочатку спробуємо з'ясувати, чи є які-небудь стовпці, що залежать від стовпця, відмінного від стовпця первинного ключа. Відповідь на це питання є позитивним. Фактично в таблиці «Розшифрування замовлення» є стовбець, який залежать від стовпця найменування товару не менше, а, можливо, навіть більше, чим від стовпця первинного ключа розглянутої таблиці. Зокрема, від стовпця «Найменування товару» повністю залежить стовбець «Ціна товару», тому необхідно знову виконати розбивку однієї таблиці на дві.

Насамперед, необхідно створити нову таблицю (назвемо її «Товари»), призначену для зберігання інформації про товари. У цій новій таблиці буде зберігатися та інформація, що містилась в таблиці «Розшифрування замовлення», яка більшою мірою залежить від значень у стовпці «Найменування товару», чому від значень у стовпці «Номер замовлення» або «Номер рядка замовлення».
Таблиця «Товари»


Найменування товару (ПК)

Ціна товару, грн.

Мило

3,50

Шампунь

12,50

Порошок Gala

8,70

Після цього ми одержуємо можливість вилучити з таблиці «Розшифрування замовлення» усі зазначені стовпці, крім стовпця зовнішнього ключа «Найменування товару»


Таблиця «Розшифрування замовлення»


Номер замовлення

(ВК)


Номер рядка замовлення

(ВК)


Найменування товару

Кількість товару

Вартість товару

100

1

Мило

10

35,00

100

2

Шампунь

30

375,00

101

1

Мило

15

52,50

101

2

Порошок Gala

10

87,00

102

1

Шампунь

20

250,00

У результаті цього усувається перший недолік (залежність між стовпцями), але залишається також необхідність виключити похідні дані (таблиця «Розшифрування замовлення» містить стовпець «Вартість товару», дані якого можуть бути отримані шляхом перемножування значень стовпців «Ціни товару» і «Кількість товару»). Наявність подібних стовпців повністю суперечить вимогам нормалізації.

Іноді розроблювачі свідомо йдуть на порушення вимог нормалізації, причому найчастіше такі порушення допускаються стосовно до похідних даних. Це пов'язане з тим, що застосування похідних даних дозволяє суттєво підвищити швидкодію. Якщо якийсь стовпець стає дуже важливим з погляду продуктивності (наприклад, якщо критерії, у яких ураховуються значення із цього стовпця, застосовуються в багатьох запитах), то може знадобитися задати індекс на цьому новому обчисленому стовпці.

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

Приведемо структуру нормалізованої бази даних із вказівкою ключів і зв'язків між таблицями.


Структура таблиці «Клієнти» (Clients)


Назва стовпця

Ідентифікатор

Тип даних

Довжина

Номер клієнта

NmClient

Числовий (цілий)

3

ПІБ клієнта

PIB

Символьний

25

Адреса клієнта

Adres

Символьний

40

Телефон клієнта

Telephon

Символьний

10

Структура таблиці «Товари» (Tovar)




Назва стовпця

Ідентифікатор

Тип даних

Довжина

Найменування товару

NameTov

Числовий (цілий)

20

Ціна товару

Cena

Числовий (нецілий)

8

Структура таблиці «Замовлення» (Orders)




Назва стовпця

Ідентифікатор

Тип даних

Довжина

Номер замовлення

NmOrders

Числовий (цілий)

5

Дата замовлення

Data

Дата

8

Номер клієнта

NmClient

Числовий (цілий)

3

Структура таблиці «Розшифрування замовлення» (StrOrders)




Назва стовпця

Ідентифікатор

Тип даних

Довжина

Номер замовлення

NmOrders

Числовий (цілий)

5

Номер строки замовлення

NmStrOrders

Числовий (цілий)

2

Найменування товару

NameTov

Символьний

20

Кількість товару

KolTov

Числовий (цілий)

4



Приклад виконання завдання №3


на тему «Створення бази даних і запитів у середовищі СУБД SQL Server 2005»

Завдання:



  1. створити базу даних (таблиці і діаграму), яка була спроектована у другому завданні, у середовищі СУБД SQL Server 2005;

  2. увести у таблиці дані (по 5-7 записів у таблиці на стороні зв’язку «один» і по 25-30 у таблиці на стороні зв’язку «багато»);

  3. створити запити відповідно до одного з варіантів (задавати стовбцям псевдоніми), виконати запити, результати виконання запитів вивести у файл;

  1. список товарів по алфавіту;

  2. дані будь-якого клієнту;

  3. суму кожного замовлення;

  4. звіт про замовлення товарів за період: найменування товару, кількість товару, ціна, сума.

  1. створити самостійно 3 запита на вибірку даних з двох таблиць з використанням фільтрування (WHERE), групування (GROUP BY), сортування даних, а також використання стовбців з даними, які розраховуються. Виконати їх і результати виконання запитів вивести у файл;

  2. створити самостійно 2 запита на вставку даних у таблиці (оператори INSERT і INSERT INТО…SELECT), виконати запити, показати результати виконання запитів;

  3. створити самостійно 2 запита на модифікацію даних у таблицях (оператор UPDATE), виконати запити, показати результати виконання запитів.

Виконання завдання у середовищі СУБД SQL Server 2005»


Діаграма бази даних OrderTovars

Уведення даних у таблицю Clients

Уведення даних у таблицю Tovar

Уведення даних у таблицю Orders



Уведення даних у таблицю StrOrders

Створення запитів відповідно до варіанту




  1. список товарів по алфавіту;

SELECT NameTov AS "наименование товара"

FROM Tovar ORDER BY NameTov





  1. дані будь-якого клієнту;

SELECT NmClient, PIB, Adres, Telephon FROM Clients

WHERE (PIB = 'Самсонов А.А.')





  1. суму кожного замовлення;

SELECT Orders.NmOrders AS [Номер заказа], SUM(Tovar.Cena * StrOrders.KolTov) AS Сума FROM Orders INNER JOIN

StrOrders ON Orders.NmOrders = StrOrders.NmOrders INNER JOIN

Tovar ON StrOrders.NameTov = Tovar.NameTov

GROUP BY Orders.NmOrders




  1. звіт про замовлення товарів за період: найменування товару, кількість товару, ціна, сума.

SELECT Tovar.NameTov AS "Товар", StrOrders.KolTov AS "Кол-во", Tovar.Cena AS "Цена", Orders.Data AS "Дата",

Tovar.Cena * StrOrders.KolTov AS "Сумма"

FROM Tovar INNER JOIN

StrOrders ON Tovar.NameTov = StrOrders.NameTov INNER JOIN

Orders ON StrOrders.NmOrders = Orders.NmOrders

WHERE (Orders.Data BETWEEN '20.02.2012' AND '27.02.2012')

Запит на вставку даних у таблицю (оператори INSERT)
Додати у таблицю Tovar ще один товар – «Изюм», ціна якого 15,49.
INSERT INTO Tovar VALUES ('Изюм',15.49)

Запит на модифікацію даних у таблиці (оператор UPDATE)


У клієнта с кодом 20 змінився номер телефону. Оновити його номер телефону у таблиці Clients.
UPDATE Clients SET Telephon = '0631234567'

WHERE NmClient = 20





База даних захищена авторським правом ©lecture.in.ua 2016
звернутися до адміністрації

    Головна сторінка