Розробка бази даних "Автосалон" в середовищі MSAccess

При операції декомпозиції з одного відношення отримується два або більше відношень, кожне з яких містить частину атрибутів вихідного відношення. В отриманих нових відношеннях необхідно видалити дублікати рядків, якщо такі виникли. Це означає, що декомпозиція відношень є не що інше, як взяття однієї або декількох проекцій вихідного відношення так, щоб ці проекції в сукупності містили (можливо, з повтореннями) всі атрибути вихідного відношення. Тобто, при декомпозиції не повинні губитися атрибути відношень. Але при декомпозиції також не повинні загубитися і самі дані. Дані можна вважати не втраченими в тому випадку, якщо можлива зворотна операція - по декомпозованому відношенню можна відновити вихідне відношення в точності попередньому вигляді. Операцією, зворотною до операції проекції, є операція з'єднання відношень. Існує велика кількість видів операції з'єднання: при відновленні вихідного відношення шляхом з'єднання проекцій не повинні з'явитися нові атрибути, а отже необхідно використовувати природне з'єднання [5].

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

Для отримання відношення в НФБК проведемо послідовність операцій декомпозиції на основі правил декомпозиції.

Розглянемо попереднє універсальне відношення R:

R: (<Код автомобіля,Код покупця, Код виробника, Код продавця>,Марка автомобіля, Колір, Дата виготовлення, Вартість автомобіля, Дата продажу, ПІБ покупця, Паспорт, Адреса покупця, Назва виробника, Країна виробник, ПІБ продавця, Посада продавця).

Спроектуємо відношення R, яке породжується всіма функціональними залежностями, що містять атрибут Код покупця. В результаті отримаємо відношення R1 і R2:

R1: (<Код автомобіля,Код виробника, Код продавця>,Марка автомобіля, Колір, Дата виготовлення, Вартість автомобіля, Дата продажу, Назва виробника, Країна виробник, ПІБ продавця, Посада продавця);

R2: (<Код покупця>,ПІБ покупця, Паспорт, Адреса покупця);

Виконаємо декомпозицію R1 шляхом проектування, породжуваного всіма функціональними залежностями, що містять атрибут Код виробника. Отримаємо відношення R3, R4.

R3: (<Код автомобіля,Код продавця>,Марка автомобіля, Колір, Дата виготовлення, Вартість автомобіля, Дата продажу, ПІБ продавця, Посада продавця);

R4: (<Код виробника>, Назва виробника, Країна виробник).

Виконаємо декомпозицію R3 шляхом проектування, породжуваного всіма функціональними залежностями, що містять атрибут Код продавця. Отримаємо відношення R5, R6.

R5: (<Код автомобіля>,Марка автомобіля, Колір, Дата виготовлення, Вартість автомобіля, Дата продажу);

R6: (<Код продавця>, ПІБ продавця, Посада продавця).

Відношення R2, R4, R5, R6знаходяться в НФБК, що свідчить про закінчення процедури декомпозиції.

Кінцеві відношення знайдені за допомогою методу декомпозиції, мають наступний вигляд:

R2: (<Код автомобіля>,ПІБ покупця, Паспорт, Адреса покупця);

R4: (<Код виробника>, Назва виробника, Країна виробник);

R5: (<Код автомобіля>,Марка автомобіля, Колір, Дата виготовлення, Вартість автомобіля, Дата продажу);

R6: (<Код продавця>, ПІБ продавця, Посада продавця).

Розглянемо функціональні залежності, які присутні між атрибутами універсального відношення, що описує предметну область «Автосалон» (рис. 2.5).

Рисунок 2.5 - Діаграма функціональної залежності

2.6 Оцінка спроектованих НФБК відношень

Розглядаючи отримані при проектуванні бази даних “Автосалон” відношення, необхідно переконатись, що :

- жодна функціональна залежність не повторюється більше одного разу;

- цей набір функціональних залежностей є мінімальним.

Аналіз відношень показує, що не можна вказати серед них жодного, всі атрибути якого були би підмножиною атрибутів іншого відношення. Крім того, неможливо об'єднати будь-які три відношення так, щоб у результаті були отримані всі атрибути четвертого відношення. Отже, жодне з відношень не є надлишковим.

Кінцеві НФБК відношення є такими:

R2: (Код автомобіля,ПІБ покупця, Паспорт, Адреса покупця);

R4: (Код виробника, Назва виробника, Країна виробник);

R5: (Код автомобіля,Марка автомобіля, Колір, Дата виготовлення, Вартість автомобіля, Дата продажу);

R6: (Код продавця, ПІБ продавця, Посада продавця).

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

При роботі запиту дані можуть упорядковуватися (сортуватися), фільтруватися (відсіюватися), об'єднуватися, розділятися, змінюватися, і при цьому ніяких змін у базових таблицях може не відбуватися [6].

В Access для побудови запиту до БД використовується метод QBE (query-by-example - запит за зразком) - метод створення запитів, винайдений IBM ще в 1970-ті роки. Запити забезпечують простий доступ до визначеної підмножини полів і записів однієї або кількох таблиць.

Запит-вибірка - запит, який вибирає потрібні дані, а результати вибірки відтворює у формі динамічного набору. Даний запит зовні схожий на таблицю, але фактично є динамічним (або віртуальним) набором записів, побудованим за структурою запиту. Записів у динамічному наборі фактично не існує, тому, коли цей набір стає не активним, записи зникають (однак дані, на яких був побудований набір, залишаються у вихідних таблицях). Запит можна зберегти, але дані, що відтворюються під час його виконання, в ньому не зберігаються. При збереженні запиту у вигляді оператора SQL зберігається тільки структура запиту [6].

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

Під час розробки бази даних «Автосалон» розроблено наступні запити: «Автомобілі за маркою», «Дохід за вказаний день», «Завершені продажі», «Продані автомобілі за вказаний період».

Один з вище наведених запитів розроблено з використанням QBE-запиту та три запити розроблено з використанням SQL-інструкцій. Використання SQL-інструкцій допомагає розробити складні, багатофункціональні запити, в яких можлива фільтрація даних за кількома параметрами, а також виконання елементарних математичних операцій: знаходження суми, різниці параметрів, які знаходяться у різних таблицях.

Запит «Автомобілі за маркою» вимагає від користувача ввести назву марки автомобіля у відповідне поле і натиснути клавішу виконання запиту. Після перерахованих дій запит виконує вибірку в таблиці «Автомобілі» та виводить на екран всі автомобілі заданої марки. Запит створено за допомогою конструктора запитів з використанням QBE-запиту. Створено відповідний зв'язок між таблицями Автомобілі та Виробники (рис. 4.1) та обрано поля, які будуть виводитись на екран при виконанні запиту.

Рисунок 4.1 - Конструктор запиту «Автомобілі за маркою»

Запит «Дохід за вказаний день» дозволяє виводити на екран суму проданих за вказаний користувачем день автомобілів. Запит створено за допомогою інструкцій SQL. Вікно конструктора запиту із SQL-кодом зображено на рис. 4.2.

Рисунок 4.2 - SQL-код запиту «Дохід за вказаний день»

Запит «Завершені продажі» вимагає від користувача ввести ПІБ продавця, початкову, кінцеву дату та виводить на екран дані про всі завершені успішні продажі продавця за певний період. Запит створено за допомогою конструктора запитів з використанням інструкцій SQL:

SELECT Продавці.[ПІБ продавця], Продавці.[Посада продавця], Покупці.[ПІБ покупця], Автомобілі.[Марка Автомобіля], Автомобілі.[Вартість автомобіля], Автомобілі.[Дата продажу]

FROM Покупці INNER JOIN (Продавці INNER JOIN Автомобілі ON Продавці.[Код продавця] = Автомобілі.[Код продавця]) ON Покупці.[Код покупця] = Автомобілі.[Код покупця]

WHERE (((Продавці.[ПІБ продавця])=[Введіть ПІБ продавця] AND (Автомобілі.[Дата продажу])>=[Введіть початкову дату] And (Автомобілі.[Дата продажу])<=[Введіть кінцеву дату] ));

Результат роботи запиту для продавця ПІБ якого: Іванов Іван Іванович за період з 01.09.2013 по 01.12.2013 показано на рис. 4.3.

Рисунок 4.3 - Демонстрація роботи запиту «Завершені продажі»

Запит «Продані автомобілі за вказаний період» дозволяє виводити на екран дані про всі продані автомобілі за певний термін. Користувач повинен ввести початкову та кінцеву дату терміну, який його цікавить.

Запит створено за допомогою інструкцій SQL:

SELECT Автомобілі.[Дата продажу], Автомобілі.[Марка Автомобіля], Автомобілі.[Колір автомобіля], Автомобілі.[Вартість автомобіля],

Покупці.[ПІБ покупця], Покупці.[Паспорт], Продавці.[ПІБ продавця]

FROM Продавці INNER JOIN (Покупці INNER JOIN Автомобілі ON Покупці.[Код покупця]=Автомобілі.[Код покупця]) ON Продавці.[Код продавця]=Автомобілі.[Код продавця]

WHERE (((Автомобілі.[Дата продажу])>=[Введіть початкову дату] And (Автомобілі.[Дата продажу])<=[Введіть кінцеву дату]));

В результаті виконання курсової роботи було розроблено запити: «Автомобілі за маркою», «Дохід за вказаний день», «Завершені продажі», «Продані автомобілі за вказаний період». Запити «Дохід за вказаний день» та «Продані автомобілі за вказаний період» створені за допомогою інструкцій SQL. Запити «Автомобілі за маркою» та «Завершені продажі», розроблено з використанням QBE-запиту. Усі запити показано у додатку В, усі розроблені таблиці подано у додатку Б.

5. РОЗРОБКА ЗВІТІВ