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

Постановка задачі.

База даних – Персональні комп’ютери. Відомості, які необхідно фіксувати: тип процесора; тактова частота; об'єм ОЗУ; об'єм жорсткого диска; дата складання; кількість usb-портів; розмір монітора; тип монітора; встановлена ОС; дата продажу; гарантія; магазин; адреса магазину; телефон магазину; прізвище та ініціали власника магазину.

Завдання

Спроектувати структуру бази даних.

Написати SQL-запити для створення бази даних.

Заповнити базу даних, використовуючи SQL-оператори.

Виконати запити :

Запит 1. Знайти в якому магазині скільки було продано комп’ютерів.

Запит 2. Вивести параметри комп’ютерів, які ще на гарантії, та назви магазинів, де їх могли купити, відсортовані в алфавітному порядку.

Запит 3. Знайти на якій вулиці найбільше комп’ютерних магазинів та їх кількість.

Запит 4. Написати трансакцію, в якій виконати наступні дії:

-            змінити номер телефону в магазині Спрайт;

-            змінити всі двоцифрові номери будинків, що закінчуються п’ятіркою на 1;

-            відмінити останню дію;

-            вивести назви магазинів прописними літерами, їх адреси та телефони.

Побудова концептуальної моделі бази даних

В предметній області можна виділити два об’єкти – комп’ютери та магазини, тому логічно представити базу даних у вигляді відповідних відношень поділивши вхідну інформацію наступним чином:

Магазин		Комп’ютер
назва		тип процесора
адреса		тактова частота
телефон		об’єм ОЗУ
прізвище та ініціали власника		об'єм жорсткого диска
		дата складання
		кількість usb-портів
		розмір монітора
		тип монітора
		операційна система
		дата продажу
		термін гарантії
		в якому магазині куплений

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

В одному магазині може продаватися довільна кількість комп’ютерів і однакові комп’ютери можуть продаватися в різних магазинах, тому тут має місце зв’язок «багато-до-багатьох»:

Напрями стрілок вказують, який із об’єктів повинен вказувати на зв’язаний з ним об’єкт. Для зв’язків такого типу потрібно створити додатковий проміжний об’єкт, який міститиме дані про обидва зв’язані об’єкти і зв’язок «багато-до-багатьох» таким чином замінимо парою зв’язків «один-до-багатьох»:

Цю схему й покладемо в основу нашої бази даних.

Зведемо отриману нами інформацію в таблиці.

 

 

 

Отже, концептуальна модель бази даних матиме вигляд:

Створення та заповнення бази даних

Створення бази даних:

create database pk

DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci;

1) таблиця «Магазин»

Створення таблиці:

USE pk;

CREATE TABLE shop(

id_shop INT(5) AUTO_INCREMENT NOT NULL PRIMARY KEY,

name_shop VARCHAR(100) DEFAULT NULL,

street VARCHAR(50) DEFAULT NULL,

house INT(3) DEFAULT '0',

phone INT(10) DEFAULT '0',

vlasnuk VARCHAR(30) DEFAULT NULL

)

ENGINE=InnoDB DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci;

Заповнення таблиці:

INSERT INTO shop (name_shop,street,house,phone,vlasnuk)

VALUES ('Спрайт','Лесі Українки', 15,123456,'Морозов І.А.');

INSERT INTO shop (name_shop,street,house,phone,vlasnuk)

VALUES ('КВ','Лесі Українки', 25,456789,'Іванов К.Ю.');

INSERT INTO shop (name_shop,street,house,phone,vlasnuk)

VALUES ('Ентер','Лесі Українки', 18,789456,'Морозов І.А.');

INSERT INTO shop (name_shop,street,house,phone,vlasnuk)

VALUES ('ЦУМ','п-т Волі', 154,45682, 'Ткаченко В.В.');

INSERT INTO shop (name_shop,street,house,phone,vlasnuk)

VALUES ('Світ ПК','Кравчука', 45,954681,'Абрамчук В.Г.');

2) таблиця «Комп’ютер»

Створення таблиці:

CREATE TABLE komp(

id_komp INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,

proc VARCHAR(50) DEFAULT NULL,

t_ch int(5) DEFAULT '0',

OZU INT(5) DEFAULT '0',

vint INT(5) DEFAULT '0',

skad DATE,

kil_usb int(2) DEFAULT '0',

mon_size int(2) DEFAULT '0',

mon_type varchar(10) DEFAULT NULL,

OS varchar(15) DEFAULT NULL,

prod_data DATE,

garant int(2) DEFAULT '0'

)

ENGINE=InnoDB DEFAULT CHARSET=cp1251;

Заповнення таблиці:

INSERT INTO komp

VALUES (1,'Celeron',2400,512,80,'2005-08-25',2,15,'CRT','Windows','2006-01-11',3);

INSERT INTO komp

VALUES (2,'Athlon',1100,512,120,'2007-05-05',2,19,'CRT','LINUX','2007-07-13',2);

INSERT INTO komp

VALUES (3,'Celeron',1700,1024,160,'2008-11-15',2,22,'LCD','UNIX','2008-12-21',3);

INSERT INTO komp

VALUES (4,'Pentium IV',2400,2048,250,'2009-02-05',2,19,'LCD','UNIX','2009-07-01',1);

INSERT INTO komp

VALUES (5,'Duron',1800,1024,120,'2007-03-11',2,22,'LCD','Windows','2008-03-05',2);

3) таблиця «Зв’язок»

Створення таблиці:

create table zvjaz (

id_komp int(5),

id_shop int(5)

)ENGINE=InnoDB;

Заповнення таблиці:

INSERT INTO zvjaz VALUES (1,1);

INSERT INTO zvjaz VALUES (1,2);

INSERT INTO zvjaz VALUES (1,3);

INSERT INTO zvjaz VALUES (2,1);

INSERT INTO zvjaz VALUES (3,1);

INSERT INTO zvjaz VALUES (4,2);

INSERT INTO zvjaz VALUES (5,3);

INSERT INTO zvjaz VALUES (4,5);

SQL-запити

Запит 1. Знайти в якому магазині скільки було продано комп’ютерів.

Запит 2. Вивести параметри комп’ютерів, які ще на гарантії, та назви магазинів, де їх могли купити, відсортовані в алфавітному порядку.

Віртуальні таблиці

Запит 3. Знайти на якій вулиці найбільше комп’ютерних магазинів та їх кількість.

Трансакції

Запит 4. Написати трансакцію, в якій виконати наступні дії:

-            змінити номер телефону в магазині Спрайт;

-            змінити всі двоцифрові номери будинків, що закінчуються п’ятіркою на 1;

-            відмінити останню дію;

-            вивести назви магазинів прописними літерами, їх адреси та телефони.