Проектирование реляционной базы данных

Размещено на http://www.allbest.ru/

ТГТУ.220501.021

Пояснительная записка

К курсовой работе

«Проектирование реляционной базы данных»

Ларин В.В.,

группа СМК-31

Тамбов 2012

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

1 ЗАДАНИЕ НА РАЗРАБОТКУ

2 АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ

3 РАЗРАБОТКА СХЕМЫ ДАННЫХ

4 ВЕДЕНИЕ БАЗЫ ДАННЫХ

5 ВЫБОР ИНФОРМАЦИИ ИЗ БАЗЫ ДАННЫХ

ЗАКЛЮЧЕНИЕ

СПИСОК ИСПОЛЬЗУЕМЫХ ИСТОЧНИКОВ

Размещено на http://www.allbest.ru/

ВВЕДЕНИЕ

реляционная база данные автомобильный

Современная жизнь немыслима без эффективного управления. Важной категорией являются системы обработки информации, от которых во многом зависит эффективность работы любого предприятия или учреждения. Цель таких систем управления базами данных - это уметь справляться со следующими задачами:

1. Определение тенденции изменения важнейших показателей.

2. Обеспечение получение информации, критической по времени, без существенных задержек.

3. Выполнение точного и полного анализа данных.

Современные СУБД в основном являются приложениями Windows, так как данная среда позволяет более полно использовать возможности персональной ЭВМ, нежели среда DOS. Снижение стоимости высокопроизводительных ПК обусловил не только широкий переход к среде Windows, где разработчик программного обеспечения может в меньше степени заботиться о распределении ресурсов, но также сделал программное обеспечение ПК в целом и СУБД в частности менее критичными к аппаратным ресурсам ЭВМ.

Среди наиболее ярких представителей систем управления базами данных можно отметить: Lotus Approach, Microsoft Access, Borland dBase, Borland Paradox, Microsoft Visual FoxPro, а также баз данных Microsoft SQL Server и Oracle, используемые в приложениях, построенных по технологии «клиент-сервер». Фактически, у любой современной СУБД существует аналог, выпускаемый другой компанией, имеющий аналогичную область применения и возможности, любое приложение способно работать со многими форматами представления данных, осуществлять экспорт и импорт данных благодаря наличию большого числа конвертеров.

1. ЗАДАНИЕ НА РАЗРАБОТКУ

I. Произвести анализ предметной области и разработать схему реляционной базы данных, содержащей информацию о следующей предметной области.

Информацию об автомобильных брендах, автозаводах и выпускаемых марках автомобилей. Необходимо вести списки автомобильных брендов (например, Toyota, Audi, BMW и т.п.), автозаводов, марок автомобилей. Для каждого автозавода необходимо хранить полное наименование, бренд, страну, адрес, список выпускаемых марок автомобилей. Для каждой марки автомобиля необходимо хранить наименование, список заводов, выпускающих данную марку. Для каждого автосалона необходимо указывать название, адрес, телефон, прайс-лист на автомобили. В прайс-листе необходимо указывать автосалон, марку и комплектацию автомобиля, цену.

II. Реализовать разработанную схему данных при помощи SQL (подраздел DDL - «язык определения данных»). Реализация схемы данных должна содержать необходимые ограничения целостности.

III. Составить операторы SQL (подраздел DML - «язык манипулирования данными»), производящие добавление новой информации в базу данных, удаление или изменение существующей информации.

IV. Составить операторы SQL, осуществляющие выбор из базы данных следующей информации:

1) Вывести список автомобильных брендов в алфавитном порядке.

2) Вывести список марок автомобилей, появившихся после 2010 года. Результаты отсортировать по году создания марки (в порядке убывания), а затем - по полному наименованию марки.

3) Вывести список автосалонов, для которых в базе не указан телефон.

4) Вывести список автозаводов, являющихся акционерными обществами (в названии встречается «АО»).

5) Вывести список марок автомобилей с указанием автозаводов.

6) Вывести прайс-лист определенного автосалона на автомобили дешевле 500000. Отсортировать сначала в порядке убывания цены, а затем - по наименованию марки и комплектации.

7) Вывести цены на определенную марку в комплектации «комфорт» в разных автосалонах.

8) Для каждого автосалона вывести список продаваемых в нем автомобильных брендов.

9) Вычислить наименьшую стоимость, за которую можно купить автомобиль.

10) Вывести минимальные цены на каждую марку автомобиля (без учета комплектации).

11) Вывести количество разных комплектаций всех марок автомобилей, имеющихся в каждом автосалоне.

12) Вывести список автосалонов, торгующих автомобилями только одного бренда.

13) Вывести информацию о самой дорогой марке автомобиля (полное наименование, автосалон, цена).

14) Вывести информацию об автозаводе, выпускающем наибольшее количество марок автомобилей.

15) Вывести список автосалонов, для которых в базе нет цен на продаваемые автомобили.

16) Вывести страну, на территории которой выпускаются автомобили наибольшего количества брендов.

2. АНАЛИЗ ПРЕДМЕТНОЙ ОБЛАСТИ

При анализе предметной области можно выделить следующие основные сущности: завод, страна, бренд, марки, изготовление.

Каждая из этих сущностей обладает набором свойств, часть из которых является важной при разработке схемы и базы данных, а часть - второстепенной группой свойств, которые можно не учитывать. К второстепенным свойствам можно отнести адрес владельца автотранспорта, масса, объем двигателя и некоторые другие. Свойства сущностей, которые необходимо учитывать при разработке можно для удобства объединить в таблицу:

Таблица 1.

Сущность	Свойства
Завод	№, наименование, бренд, адрес, страна
Бренд	№, название
Марки	№, название, год выпуска
Страна	№, название
Прайс	№ Автосалона, Марка, Тип комплектации
Автосалон	№, Название, Адрес, Телефон
Комплектация	№, Название

Для разработки схемы данных необходимо установить, какие связи имеются между сущностями и их свойствами и какого типа эти связи.

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 - Логическая схема реляционной базы данных.

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,

nazv varchar(30),

br_id integer not null,

adres varchar(70),

strana_id integer not null,

primary key (ID),

foreign key (br_id) references brend,

foreign key (strana_id) references strana);

2) Таблица brend(Бренды):

- поле ID является обязательным для заполнения и первичным ключом.

- поле NAZV - строки переменной длины.

С учетом перечисленных требований оператор SQL, создающий таблицу выглядит следующим образом:

create table brend

(ID integer not null,

nazv varchar(20) not null,

primary key (ID));

3) Таблица az_m (Завод производитель):

- все поля таблицы являются обязательными для заполнения;

- поля zavod_id, marki_id содержит целые числа и являются внешним ключом.

create table az_m

(zavod_id integer not null,

marki_id integer not null,

primary key (zavod_id, marki_id),

foreign key (zavod_id) references avtozavod,

foreign key (marki_id) references marki)

4) Таблица Marki(Марки):

- поле id является обязательным для заполнения;

- поле id, god_v содержит целые числа, nazv - строки переменной длины.

С учетом перечисленных требований оператор SQL, создающий таблицу будет выглядеть следующим образом:

create table marki

(id integer not null,

nazv varchar(30),

god_v integer,

primary key(id));

5) Таблица price (Прайс Лист):

- Поля as_id, marki_id, equipment_id являются обязательными для заполнения и первичными ключами.

- поля as_id, marki_id, equipment_id содержит целые числа, поле zena денежный тип данных.

Оператор SQL создающий таблицу с учетом этих требований выглядит следующим образом:

create table price1

(as_id integer not null,

marki_id integer not null,

equipment_id integer not null,

zena money,

primary key(as_id, marki_id, equipment_id),

foreign key(as_id) references avto_salon,

foreign key(marki_id) references marki,

foreign 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),

adres varchar(70),

numer varchar(30),

primary key (id));

7) Таблица equipment (Комплектация):

- поле id является обязательным для заполнения;

- поле id содержит целые числа, поле nazv- строка переменной длины. Следующий оператор SQL создает эту таблицу:

Create table equipment

(id integer not null,

Nazv varchar(30),

Primary key (id));

8) Таблица strana (Страна):

- поле id является обязательным для заполнения;

- поле id содержит целые числа, поле nazv- строка переменной длины.

Следующий оператор SQL создает эту таблицу:

Create table strana

(id integer not null,

nazv varchar(30),

primary key (id));

4. ВЕДЕНИЕ БАЗЫ ДАННЫХ

Для использования созданной в предыдущем разделе структуры базы данных разработаем соответствующие операторы SQL, при помощи которых будет осуществляться ведение базы данных.

Добавление новых записей в таблицы производится при помощи оператора INSERT, удаление существующих записей - оператором DELETE, изменение - оператором UPDATE. Для удобства пользователя можно свести эти операторы вместе для каждой таблицы базы данных. Для удобства пользователя можно свести эти операторы вместе для каждой таблицы базы данных:

1) Таблица AVTOZAVOD:

- добавление новой записи

INSERT INTO avtozavod (ID, nazv, br_id, adres, strana_id)

VALUES (1, "ao subaru", 1, "г. Санкт-Питербург, ул.Заводская, д.17", 1);

- удаление существующей записи

DELETE

FROM avtozavod

WHERE ID=1;

- изменение существующей записи

UPDATE AVTOZAVOD

SET nazv='ao nissan', 7, “г.Детроит, ул.1, д.7”, 10

WHERE ID=8;

2) Таблица BREND:

- добавление новой записи

INSERT INTO BREND (ID, NAZV)

VALUES (1, “AC”);

- удаление существующей записи

DELETE

FROM BREND

WHERE ID=10;

- изменение существующей записи

UPDATE BRAND

SET NAZV=”NISSAN”

WHERE ID=11;

3) Таблица AZ_M:

- добавление новой записи

INSERT INTO AZ_M (ZAVOD_ID, MARKI_ID)

VALUES (1,1);

- удаление существующей записи

DELETE

FROM AZ_M

WHERE ZAVOD_ID=3, MARKI_ID=4;

- изменение существующей записи

UPDATE AZ_M

SET ZAOVOD_ID=7

WHERE ZAVOD_ID=8, MARKI_ID=10;

4) Таблица MARKI:

- добавление новой записи

INSERT INTO MARKI (ID, NAZV, GOD_V)

VALUES (1, “Land Rover Discovery”, 2000);

- удаление существующей записи

DELETE

FROM MARKI

WHERE 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)

VALUES (1,1,3, 150000);

- удаление существующей записи

DELETE

FROM PRICE

WHERE ID=10;

- изменение существующей записи

UPDATE PRICE

SET 3, 4, 2, 130000

WHERE AS_ID=4, MARKI_ID=3;

6) Таблица AVTO_SALON:

- добавление новой записи

INSERT INTO AVTO_SALON (ID, NAZV, ADRES, NUMER)

VALUES (1, “AUTO-GERMES”, “ADRES1”, 55664433);

- удаление существующей записи

DELETE

FROM AVTO_SALON

WHERE ID=13;

- изменение существующей записи

UPDATE AVTO_SALON

SET NAZV=”AUTO-GR”

WHERE ID=10;

7) Таблица EQUIPMENT

- добавление новой записи

INSERT INTO EQUIPMENT (ID, NAZV)

VALUES ( 1, “БАЗА”);

- удаление существующей записи

DELETE

FROM EQUIPMENT

WHERE ID=3;

8) Таблица STRANA

- добавление новой записи

INSERT INTO STRANA (ID, NAZV)

VALUES ( 1, “РОССИЯ”);

- удаление существующей записи

DELETE

FROM STRANA

WHERE ID=3;

5 ВЫБОР ИНФОРМАЦИИ ИЗ БАЗЫ ДАННЫХ

Для выбора информации из базы данных используется оператор SELECT.

1) Вывести список автомобильных брендов в алфавитном порядке.

SELECT id, nazv

FROM brend

ORDER BY nazv asc;

2) Вывести список марок автомобилей, появившихся после 2010 года. Результаты отсортировать по году создания марки (в порядке убывания), а затем - по полному наименованию марки.

SELECT id, nazv, god_v

FROM Marki

WHERE (((god_v)>(2010)))

ORDER BY god_v DESC , nazv;

3) Вывести список автосалонов, для которых в базе не указан телефон.

SELECT ID, nazv, adres, numer

FROM avto_salon

WHERE numer is null;

4) Вывести список автозаводов, являющихся акционерными обществами (в названии встречается «АО»).

SELECT avtozavod.id, avtozavod.nazv

FROM avtozavod

WHERE (((avtozavod.[nazv]) Like "*ao*"));

5) Вывести список марок автомобилей с указанием автозаводов.

SELECT z.nazv, m.nazv

FROM avtozavod AS z, marki AS m, az_m AS z1

WHERE (((z.id)=z1.zavod_id) And ((m.id)=z1.marki_id))

ORDER BY m.nazv, z.nazv;

6) Вывести прайс-лист определенного автосалона на автомобили дешевле 500000. Отсортировать сначала в порядке убывания цены, а затем - по наименованию марки и комплектации.

SELECT a.nazv, m.nazv, k.nazv, p.zena

FROM avto_salon AS a, marki AS m, equipment AS k, price AS p

WHERE (a.id=as_id) And (k.id=equipment_id) And (m.id=marki_id) And (p.zena<=500000) And (a.nazv="Auto-germes")

ORDER BY p.zena DESC , m.nazv, k.nazv;

7) Вывести цены на определенную марку в комплектации «комфорт» в разных автосалонах.

SELECT p.zena, m.nazv, a.nazv, k.nazv

FROM price AS p, marki AS m, avto_salon AS a, equipment AS k

WHERE (k.nazv="Комфорт") AND (k.id=equipment_id) AND (m.id=marki_id) And (m.nazv="Land Rover Discovery") and (a.id=as_id)

ORDER BY a.nazv, m.nazv;

8) Для каждого автосалона вывести список продаваемых в нем автомобильных брендов.

SELECT DISTINCT a.nazv, b.nazv

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)

ORDER BY a.nazv, b.nazv;

9) Вычислить наименьшую стоимость, за которую можно купить автомобиль.

SELECT min(zena) AS st

FROM price;

10) Вывести минимальные цены на каждую марку автомобиля (без учета комплектации).

SELECT m.nazv, min(zena)

FROM price AS p, marki AS m

WHERE p.marki_id=m.id

GROUP BY m.nazv, m.id;

11) Вывести количество разных комплектаций всех марок автомобилей, имеющихся в каждом автосалоне.

SELECT a.nazv, count(*)

FROM price AS p, avto_salon AS a

WHERE a.id=p.as_id

GROUP BY a.nazv;

12) Вывести список автосалонов, торгующих автомобилями только одного бренда.

SELECT a.nazv, count(*)

FROM (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))

GROUP BY a.nazv

HAVING count(*)=1;

13) Вывести информацию о самой дорогой марке автомобиля (полное наименование, автосалон, цена).

Для выполнения этого запроса нужно создать вложенный запрос в основном запросе с конструкцией нахождения максимальной цены(т.е агрегатной функцией max) из таблицы PRICE и основным запросом с выведения марки из таблицы MARKI. С учетом положений оператора SQL будет выглядеть следующим образом:

SELECT m.nazv, a.nazv, p.zena

FROM price AS p, avto_salon AS a, marki AS m

WHERE (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 z

WHERE z.id=z1.zavod_id

GROUP BY z.id, z.nazv

HAVING count(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 a

WHERE id 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)

GROUP BY s.nazv

HAVING count(*)>=all (select count(*) from (select distinct strana_id, br_id from avtozavod) group by strana_id);

ЗАКЛЮЧЕНИЕ

Эффективное развитие государства немыслимо без систем управления. Современные системы управления базируются на комплексных системах обработки информации, на современных информационных технологиях.

Современные системы компьютерного управления обеспечивают:

1. Определение тенденций изменения важных показателей.

2. Получение информации во времени без задержек.

3. Выполнение точного и полного анализа данных.

СПИСОК ИСПОЛЬЗУЕМЫХ ИСТОЧНИКОВ

1. Информатика: Учебник для вузов / Козырев А.А. - СПб: издательство Михайлова В.А., 2002. - 511 с.

2. Математика и информатика / Турецкий В.Я. - 3-е изд., испр. И доп. - М.: Инфра-М, 2000. - 560 с.

3. Роланд Фред. Основные концепции баз данных. Вильямс. 2002

4. Ульман Дж., Уидом Дж. Введение в системы баз данных. М. Лори. 2000.

5. Федоров Д., Елманова Н. Базы данных для всех. М. Компьютер-пресс, 2001.

6. Хомоненко А. Базы данных. Учебник для вузов. 2 издание. СПб., 2000.

Размещено на www.allbest.ru