Сущность
|
Свойства
|
Завод
|
№,
наименование, бренд, адрес, страна
|
Бренд
|
№,
название
|
Марки
|
№,
название, год выпуска
|
Страна
|
№,
название
|
Прайс
|
№
Автосалона, Марка, Тип комплектации
|
Автосалон
|
№,
Название, Адрес, Телефон
|
Комплектация
|
№,
Название
|
Для разработки схемы данных необходимо
установить, какие связи имеются между сущностями и их свойствами и какого типа
эти связи.
I. “Завод” - “Бренд”
Каждый автозавод может относиться только к
одному бренду. Вероятно, к каждому бренду может относиться несколько
автозаводов, поэтому между этими сущностями существует связь «один-ко-многим»
(1:M), которую можно
изобразить следующим образом:
II. “Завод” -
“Марка
III. “Завод” -
“Страна”
Каждый автозавод находится в одной стране:
IV. “Автосалон”
- “Марка”
В каждом автосалоне могут продаваться несколько
марок автомобилей разных брендов. Каждая марка автомобиля может продаваться в
нескольких автосалонах:
3. РАЗРАБОТКА СХЕМЫ
ДАННЫХ
Схему реляционной базы данных изобразим в виде
таблиц и связей между ними. При этом таблицы будут являться реализацией
сущностей, а поля таблицы - свойствами сущностей. Помимо этого, выделим из
перечисленных в таблице 1 свойств такие, которые будут уникальным образом
идентифицировать каждый экземпляр сущности (запись в таблице). С учетом выше
изложенного схему данных исследуемой предметной области представим на рисунке
1.
Разработанная схема данных содержит восемь
таблиц и может быть реализована при помощи SQL.
На этапе физического моделирования базы данных описываются типы данных для
каждого вида хранимой информации, а также способы и место их физического
размещения. При этом необходимо для каждого поля таблицы определить тип данных,
который наиболее подходит для хранения соответствующей информации, какие поля не
могут содержать пустые значения (NULL).
Типы integer
not null,
varchar(*) not
null, numeric(*,*)
not null
означают, что поля могут быть длинными целыми числами, не содержащими NULL.
Тип varchar(*)
означает, что поля содержат строку символов переменной длины. Тип numeric(*,*)
означает, что поля содержат масштабируемые целые числа. Тип date
означает, что поля содержат календарную дату. Тип money
означает что поле содержит денежный тип данных.
Поля, выделенные на схеме данных ключом, будут
являться первичными ключами (PRIMARY
KEY) таблиц. Поля
оканчивающиеся на “_id”,
будут являться внешними ключами, и будут иметь связи с другими таблицами.
Рисунок 1 - Логическая схема реляционной базы
данных.
) Таблица Avtozavod(Заводы):
поля id,
br_id,
strana_id
не могут содержать NULL;
поля ID,
br_id,
strana_id
содержат целые числа.
поля id
являются первичным ключом, а br_id,
strana_id
внешним ключем и имеют связь с другими таблицами.
С учетом перечисленных требований оператор SQL,
создающий таблицу буде выглядеть следующим образом:
create table avtozavod
(id integer not null,varchar(30),_id
integer not null,varchar(70),_id integer not null,key (ID),key (br_id)
references brend,key (strana_id) references strana);
2) Таблица brend(Бренды):
поле ID
является обязательным для заполнения и первичным ключом.
поле NAZV
- строки переменной длины.
С учетом перечисленных требований оператор SQL,
создающий таблицу выглядит следующим образом:
create table brend
(ID integer not null,varchar(20) not
null,key (ID));
3) Таблица az_m
(Завод производитель):
все поля таблицы являются обязательными для
заполнения;
поля zavod_id,
marki_id
содержит целые числа и являются внешним ключом.
create table az_m
(zavod_id integer not null,_id
integer not null,key (zavod_id, marki_id),key (zavod_id) references
avtozavod,key (marki_id) references marki)
4) Таблица Marki(Марки):
поле id
является обязательным для заполнения;
- поле id,
god_v
содержит целые числа, nazv
- строки переменной длины.
С учетом перечисленных требований оператор SQL,
создающий таблицу будет выглядеть следующим образом:
create table marki
(id integer not null,varchar(30),_v
integer,key(id));
) Таблица
price (Прайс
Лист):
- Поля as_id,
marki_id,
equipment_id
являются обязательными для заполнения и первичными ключами.
- поля as_id,
marki_id,
equipment_id
содержит целые числа, поле zena
денежный тип данных.
Оператор SQL
создающий таблицу с учетом этих требований выглядит следующим образом:
create table price1
(as_id integer not null,_id integer
not null,_id integer not null,money,key(as_id, marki_id,
equipment_id),key(as_id) references avto_salon,key(marki_id) references
marki,key(equipment_id) references equipment);
6) Таблица Avto_salon
(автосалон):
поле id
является обязательным для заполнения;
- поле id
содержит целые числа, поле nazv,
adres, numer
- строка переменной длины. Следующий оператор SQL
создает эту таблицу:
Create
table avto_salon
(id
integer not
null,
nazv
varchar(30),varchar(70),varchar(30),key (id));
) Таблица
equipment (Комплектация):
- поле id
является обязательным для заполнения;
поле id
содержит целые числа, поле nazv-
строка переменной длины. Следующий оператор
SQL создает
эту
таблицу:table
equipment
(id integer not null,varchar(30),key
(id));
8) Таблица strana
(Страна):
поле id
является обязательным для заполнения;
поле id
содержит целые числа, поле nazv-
строка переменной длины.
Следующий оператор SQL
создает эту таблицу:
Create table strana
(id integer not null,varchar(30),key
(id));
4. ВЕДЕНИЕ БАЗЫ ДАННЫХ
Для использования созданной в предыдущем разделе
структуры базы данных разработаем соответствующие операторы SQL,
при помощи которых будет осуществляться ведение базы данных.
Добавление новых записей в таблицы производится
при помощи оператора INSERT,
удаление существующих записей - оператором DELETE,
изменение - оператором UPDATE.
Для удобства пользователя можно свести эти операторы вместе для каждой таблицы
базы данных. Для удобства пользователя можно свести эти операторы вместе для
каждой таблицы базы данных:
) Таблица AVTOZAVOD:
добавление новой записи
INSERT INTO avtozavod (ID, nazv,
br_id, adres, strana_id) (1, "ao
subaru", 1, "г.
Санкт-Питербург, ул.Заводская, д.17", 1);
удаление существующей записи
DELETE
FROM
avtozavod
WHERE
ID=1;
изменение существующей записи
UPDATE
AVTOZAVOD
SET nazv='ao nissan', 7, “г.Детроит,
ул.1,
д.7”,
10 ID=8;
добавление новой записи
INSERT INTO BREND (ID, NAZV)
(1, “AC”);
удаление существующей записи
DELETE
FROM
BREND
WHERE
ID=10;
изменение существующей записи
UPDATE BRANDNAZV=”NISSAN”
ID=11;
3) Таблица AZ_M:
добавление новой записи
INSERT INTO AZ_M (ZAVOD_ID,
MARKI_ID) (1,1);
удаление существующей записи
DELETE
FROM
AZ_M
WHERE ZAVOD_ID=3, MARKI_ID=4;
- изменение существующей записи
UPDATE
AZ_M
SET ZAOVOD_ID=7ZAVOD_ID=8,
MARKI_ID=10;
4) Таблица MARKI:
добавление новой записи
INSERT INTO MARKI (ID, NAZV, GOD_V)
(1, “Land
Rover Discovery”,
2000);
удаление существующей записи
DELETEMARKI
ID=10;
изменение существующей записи
UPDATE
MARKI
SET
NAZV=”NISSAN
GT-R”,
2012
WHERE ID=7;
5) Таблица
PRICE:
добавление новой записи
INSERT INTO PRICE (AS_ID, MARKI_ID,
EQUIPMENT_ID, ZENA) (1,1,3, 150000);
удаление существующей записи
DELETE
FROM
PRICE
WHERE
ID=10;
изменение существующей записи
UPDATE
PRICE
SET 3, 4, 2, 130000AS_ID=4,
MARKI_ID=3;
6) Таблица AVTO_SALON:
добавление новой записи
INSERT INTO AVTO_SALON (ID, NAZV,
ADRES, NUMER) (1, “AUTO-GERMES”,
“ADRES1”, 55664433);
удаление существующей записи
DELETEAVTO_SALONID=13;
- изменение существующей записи
UPDATE
AVTO_SALON
SET NAZV=”AUTO-GR”ID=10;
7) Таблица EQUIPMENT
добавление новой записи
INSERT INTO EQUIPMENT (ID, NAZV)
( 1, “БАЗА”);
- удаление существующей записи
DELETE
FROM
EQUIPMENT
WHERE ID=3;
8) Таблица STRANA
добавление новой записи
INSERT INTO STRANA (ID, NAZV)
( 1, “РОССИЯ”);
- удаление существующей записи
DELETE
FROM
STRANA
WHERE
ID=3;
5 ВЫБОР ИНФОРМАЦИИ
ИЗ БАЗЫ ДАННЫХ
Для выбора информации из базы данных
используется оператор SELECT.
) Вывести список автомобильных брендов в
алфавитном порядке.
SELECT id, nazvbrendBY nazv asc;
2) Вывести список марок автомобилей,
появившихся после 2010 года. Результаты отсортировать по году создания марки (в
порядке убывания), а затем - по полному наименованию марки.
SELECT id, nazv,
god_vMarki(((god_v)>(2010)))BY god_v DESC , nazv;
3) Вывести список автосалонов, для которых
в базе не указан телефон.
SELECT ID, nazv, adres,
numeravto_salon
WHERE numer is null;
) Вывести список автозаводов, являющихся
акционерными обществами (в названии встречается «АО»).
SELECT avtozavod.id,
avtozavod.nazvavtozavod(((avtozavod.[nazv]) Like "*ao*"));
SELECT z.nazv, m.nazvavtozavod AS z,
marki AS m, az_m AS z1(((z.id)=z1.zavod_id) And ((m.id)=z1.marki_id))BY m.nazv,
z.nazv;
6) Вывести прайс-лист определенного
автосалона на автомобили дешевле 500000. Отсортировать сначала в порядке
убывания цены, а затем - по наименованию марки и комплектации.
SELECT a.nazv, m.nazv, k.nazv,
p.zenaavto_salon AS a, marki AS m, equipment AS k, price AS p(a.id=as_id) And
(k.id=equipment_id) And (m.id=marki_id) And (p.zena<=500000) And
(a.nazv="Auto-germes")BY p.zena DESC , m.nazv, k.nazv;
7) Вывести цены на определенную марку в
комплектации «комфорт» в разных автосалонах.
SELECT p.zena, m.nazv, a.nazv,
k.nazvprice AS p, marki AS m, avto_salon AS a, equipment AS k(k.nazv="Комфорт")
AND (k.id=equipment_id) AND (m.id=marki_id) And (m.nazv="Land Rover
Discovery") and (a.id=as_id)BY a.nazv, m.nazv;
8) Для каждого автосалона вывести список
продаваемых в нем автомобильных брендов.
SELECT DISTINCT a.nazv,
b.nazvavto_salon AS a, brend AS b, price AS p, marki AS m, az_m AS z1,
avtozavod AS z(a.id=p.as_id) and (p.marki_id=m.id) and (m.id=z1.marki_id) and
(z1.zavod_id=z.id) and (z.br_id=b.id)BY a.nazv, b.nazv;
9) Вычислить наименьшую стоимость, за
которую можно купить автомобиль.
SELECT min(zena) AS stprice;
10) Вывести минимальные цены на каждую марку
автомобиля (без учета комплектации).
SELECT m.nazv, min(zena)price AS p,
marki AS mp.marki_id=m.idBY m.nazv, m.id;
11) Вывести количество разных комплектаций
всех марок автомобилей, имеющихся в каждом автосалоне.
SELECT a.nazv, count(*)price AS p,
avto_salon AS aa.id=p.as_id
GROUP BY a.nazv;
) Вывести список автосалонов, торгующих
автомобилями только одного бренда.
SELECT a.nazv, count(*)(SELECT
DISTINCT a.nazv, z.br_id FROM avto_salon AS a, brend AS b, price AS p, marki AS
m, az_m AS z1, avtozavod AS z WHERE (a.id=p.as_id) and (p.marki_id=m.id) and
(m.id=z1.marki_id) and (z1.zavod_id=z.id) and (z.br_id=b.id))BY a.nazvcount(*)=1;
13) Вывести информацию о самой дорогой марке
автомобиля (полное наименование, автосалон, цена).
Для выполнения этого запроса нужно создать
вложенный запрос в основном запросе с конструкцией нахождения максимальной
цены(т.е агрегатной функцией max)
из таблицы PRICE и основным
запросом с выведения марки из таблицы MARKI.
С учетом положений оператора SQL будет выглядеть следующим образом:
SELECT
m.nazv,
a.nazv,
p.zena
FROM price AS p, avto_salon AS a,
marki AS m(a.id=p.as_id) and (m.id=p.marki_id) and (p.zena= (select max(zena)
from price));
14) Вывести информацию об автозаводе,
выпускающем наибольшее количество марок автомобилей.
Для выполнения задания будем использовать два
запроса: запрос SELECT и запрос having
с подзапросом select. Для выведение
информации об автозаводе будем использовать предложение SELECT,
FROM , WHERE
и две таблицы AZ_M
и AVTOZAVOD. В предложение WHERE
создадим связь между этими таблицами. Через предложение group
by осуществим
группировку строк по заводам. Для того что бы вывести информацию об автозаводе,
выпускающем наибольшее количество марок автомобилей нужно в предложении HAVING
сделать подсчет строк автозаводов и с подзапросом вычислить id
завода у которого наибольшее количество строк по маркам. С учетом положений
оператора SQL будет
выглядеть следующим образом:
SELECT
z.nazv,
count(*)
FROM az_m AS z1, avtozavod AS
zz.id=z1.zavod_idBY z.id, z.nazvcount(z1.zavod_id) = ( select max (c) from
(select count( marki_id) as c from az_m group by zavod_id));
15) Вывести список автосалонов, для которых в
базе нет цен на продаваемые автомобили.
Для выполнение этого запроса нужно создать
черный список id автосалонов
в таблице PRICE, и с
помощью конструкции not
in вложить в основной
запрос. С учетом положений оператора SQL будет выглядеть следующим образом:
SELECT
a.nazv
FROM avto_salon AS aid not in
(select distinct as_id from price);
16) Вывести страну, на территории которой
выпускаются автомобили наибольшего количества брендов.
Для выполнения задания будем использовать два
запроса: запрос SELECT и запрос having с подзапросом select. Для выведение
информации об автозаводе будем использовать предложение SELECT, FROM , WHERE и
две таблицы AZ_M
и AVTOZAVOD. В
предложение WHERE “нарисуем” связь между этими таблицами. Через предложение group
by осуществим группировку строк по заводам. Для того что бы вывести информацию
об автозаводе, выпускающем наибольшее количество марок автомобилей нужно в
предложении HAVING сделать подсчет строк автозаводов и с подзапросом вычислить
id завода у которого наибольшее количество строк по маркам. С учетом положений
оператора SQL будет выглядеть следующим образом:
SELECT
s.nazv,
count(*)
FROM (SELECT DISTINCT s.nazv,
z.br_id FROM Strana AS s, avtozavod AS z WHERE z.Strana_id=s.id)BY
s.nazvcount(*)>=all (select count(*) from (select distinct strana_id, br_id
from avtozavod) group by strana_id);
ЗАКЛЮЧЕНИЕ
Эффективное развитие государства немыслимо без
систем управления. Современные системы управления базируются на комплексных
системах обработки информации, на современных информационных технологиях.
Современные системы компьютерного управления
обеспечивают:
1. Определение тенденций изменения важных
показателей.
2. Получение информации во времени без
задержек.
. Выполнение точного и полного анализа
данных.
СПИСОК ИСПОЛЬЗУЕМЫХ ИСТОЧНИКОВ
1. Информатика:
Учебник для вузов / Козырев А.А. - СПб: издательство Михайлова В.А., 2002. -
511 с.
2. Математика
и информатика / Турецкий В.Я. - 3-е изд., испр. И доп. - М.: Инфра-М, 2000. -
560 с.
. Роланд
Фред. Основные концепции баз данных. Вильямс. 2002
4. Ульман
Дж., Уидом Дж. Введение в системы баз данных. М. Лори. 2000.
5. Федоров
Д., Елманова Н. Базы данных для всех. М. Компьютер-пресс, 2001.
6. Хомоненко
А. Базы данных. Учебник для вузов. 2 издание. СПб., 2000.