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

 









ТГТУ.220501.021


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

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


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






Ларин В.В.,

группа СМК-31









Тамбов 2012

СОДЕРЖАНИЕ


ВВЕДЕНИЕ

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

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

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

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

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

ЗАКЛЮЧЕНИЕ

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


ВВЕДЕНИЕ

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

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

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

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

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

Современные СУБД в основном являются приложениями 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, осуществляющие выбор из базы данных следующей информации:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


) Таблица 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;

)Таблица BREND:

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

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-GRID=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*"));

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

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.


ТГТУ.220501.021 Пояснительная записка К курсовой работе «Проектирование реляционной базы данных»

Больше работ по теме:

КОНТАКТНЫЙ EMAIL: [email protected]

Скачать реферат © 2017 | Пользовательское соглашение

Скачать      Реферат

ПРОФЕССИОНАЛЬНАЯ ПОМОЩЬ СТУДЕНТАМ