VII Международная студенческая научная конференция Студенческий научный форум - 2015

1 РАЗРАБОТКА КОНЦЕПТУАЛЬНОЙ МОДЕЛИ БАЗЫ ДАННЫХ

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

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

Должно обеспечиваться хранение и регулярное обновление в базе данных как минимум следующей информации:

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

- информация, характеризующая клиентов, в частности: ФИО клиента, паспортные данные, адрес клиента;

- информация, характеризующая менеджеров, в частности: ФИО менеджера, паспортные данные, адрес менеджера;

- информация, характеризующая процесс продажи автомобилей, в частности: название автомобиля, дата продажи, стоимость автомобиля, ФИО клиента, ФИО менеджера.

Менеджер имеет право на внесение данных в отчете по продаже, но не имеет право на удаление и редактирование записей связанных с автомобилями и клиентами.

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

2 ИНФОЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ

К сущностям предметной области относятся: Автомобили, Клиенты, Менеджер.

Сущность Автомобили имеет следующие основные атрибуты:

- id_автомобиля

- Фирма

- Марка

Сущность Клиенты имеет следующие основные атрибуты:

- id_клиента

- Фамилия

- Имя

- Отчество

- Паспортные_данные

- Адрес

Сущность Менеджер имеет следующие основные атрибуты:

- id_менеджера

- Паспортные_данные

- Адрес

- Фамилия

- Имя

- Отчество

Выделим из сущности Автомобили сущность-обозначение Поставщик.

Сущность-обозначение Поставщик имеет следующие основные атрибуты:

- id_фирмы

- Фирма

Между сущностью Автомобили и сущностью Фирма есть смысловая связь: «Автомобили поступают от фирмы».

Между сущностью Клиенты и сущностью Автомобили есть смысловая связь: «Автомобили продаются клиентам».

Поступление автомобилей от фирмы можно представить ассоциацией Поставки.

Связь Продаются имеет размерность M:N, то есть «многие-ко-многим», так как автомобиль может продаваться многим клиентам и один клиент может купить много автомобилей. Представим данную связь двумя связями «один-ко-многим» и ассоциацией Продажи.

Получаем окончательный вариант ER-диаграммы.

3 ЛОГИЧЕСКОЕ ПРОЕКТИРОВАНИЕ

Для разработки базы данных для компании по продаже автомобили была выбрана СУБД MS SQL Server, так как она позволяет создавать распределенную базу данных, обеспечивает сохранение базы данных в целостном состоянии, позволяет управлять пользователями.

Проанализировав разработанную инфологическую модель, можно сделать вывод, что база данных находится в третьей нормальной форме и дальнейшая нормализация не требуется.

Ниже в таблице №1 представлены свойства полей.

Таблица №1. Свойства полей.

Наименование поля	Ключ	Формат	Размерность	Уникальность
Id_фирмы	PK	int	-	Not NULL
Фирма	-	char	20	Not NULL
Фирма	FK	int	-	Not NULL
Марка	-	char	20	Not NULL
id_поставки	PK	int	-	Not NULL
Автомобиль	FK	int	-	Not NULL
Цена_поставки	-	money	-	Not NULL
Дата_поставки	-	date	-	Not NULL
id_клиента	PK	int	-	Not NULL
Фамилия	-	char	20	Not NULL
Имя	-	char	20	Not NULL
Отчество	-	char	30	Not NULL
Паспортные_данные	-	varchar	10	Not NULL
Адрес	-	char	100	Not NULL
id_мененджера	PK	int	-	Not NULL
Фамилия	-	char	25	Not NULL
Имя	-	char	25	Not NULL
Отчество	-	char	25	Not NULL
Паспортные_данные	-	varchar	10	Not NULL
Адрес	-	char	100	Not NULL
id_продажи	PK	int	-	Not NULL
Клиент	FK	int	-	Not NULL
Автомобиль	FK	int	-	Not NULL
Цена	-	money	-	Not NULL
Дата_продажи	-	date	-	Not NULL
Продавец	FK	int	-	Not NULL

4 ФИЗИЧЕСКОЕ ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ

Создание базы данных с помощью запроса:

create database Rotanov_IFST_31 on

(name='Rotanov_IFST_31',

filename = D:ERROR3Третий курсБДпрактикакурсовая Rotanov_IFST_31.mdf', size = 5 MB)

log on

(name= 'Rotanov_IFST_31_log',

filename = D:ERROR3Третий курсБДпрактикакурсовая Rotanov_IFST_31_log.ldf', size=1 MB)

Результат обработки запроса представлен на рисунке 1.

Рисунок 1. База данных

Создание таблиц базы данных с помощью запроса:

create table Поставщик (id_фирмы int primary key, Фирма char(20) not null)

create table Автомобили (id_автомобиля int primary key, Фирма int not null, Марка char(20) not null)

create table Поставки (id_поставки int primary key, Автомобиль int not null, Количество int not null, Цена_поставки Money not null, Дата_поставки Date not null)

create table Клиенты (id_клиента int primary key, Фамилия char(20) not null, Имя char(20) not null, Отчество char(30) not null, Паспортные_данные int not null, Адрес char(100) not null)

create table Мененджер (id_мененджера int primary key, Паспортные_данные int not null, Адрес char(100) not null)

create table Продажи (id_продажи int primary key, Клиент int not null, Автомобиль int not null, Цена Money not null, Дата_продажи Date not null, Продавец int not null)

Результат обработки запроса представлен на рисунке 2.

Рисунок 2. Таблицы базы данных

Добавление внешних ключей в таблицах базы данных:

alter table Автомобили add constraint a1 foreign key (фирма) references Поставщик (id_Фирмы)

alter table Поставки add constraint a2 foreign key (Автомобиль) references Автомобили (id_автомобиля)

alter table Продажи add constraint a3 foreign key (Клиент) references Клиенты (id_клиента)

alter table Продажи add constraint a4 foreign key (Автомобиль) references Автомобили (id_автомобиля)

alter table Продажи add constraint a5 foreign key (Продавец) references Мененджер (id_мененджера)

Результат обработки запроса представлен на рисунке 3.

Рисунок 3. Внешние ключи

Добавление записей в таблицу Поставщик при помощи запросов:

insert into Поставщик values (1,'Audi')

insert into Поставщик values (2,'BMW')

insert into Поставщик values (3,'Chevrolet')

insert into Поставщик values (4,'Ford')

insert into Поставщик values (5,'Lada')

insert into Поставщик values (6,'Lexus')

insert into Поставщик values (7,'Mazda')

insert into Поставщик values (8,'Mitsubishi')

insert into Поставщик values (9,'Nissan')

insert into Поставщик values (10,'Porsche')

Результат обработки запроса представлен на рисунке 4.

Рисунок 4. Данные таблицы Поставщик

Добавление записей в таблицу Автомобили при помощи запросов:

insert into Автомобили values (1,1,'Audi RS 5')

insert into Автомобили values (2,1,'Audi TTS')

insert into Автомобили values (3,1,'Audi R 8')

insert into Автомобили values (4,2,'BMW 7')

insert into Автомобили values (5,2,'BMW M5')

insert into Автомобили values (6,2,'BMW i8')

insert into Автомобили values (7,3,'Chevrolet Aveo')

insert into Автомобили values (8,3,'Chevrolet Camaro')

insert into Автомобили values (9,3,'Chevrolet Corvette')

insert into Автомобили values (10,4,'Ford Focus')

insert into Автомобили values (11,4,'Ford Mondeo')

insert into Автомобили values (12,4,'Ford Mustang')

insert into Автомобили values (13,5,'Lada 2131')

insert into Автомобили values (14,5,'Lada Granta')

insert into Автомобили values (15,5,'Lada Priora')

insert into Автомобили values (16,6,'Lexus LS')

insert into Автомобили values (17,6,'Lexus RC')

insert into Автомобили values (18,6,'Lexus RC F')

insert into Автомобили values (19,7,'Mazda MX-5')

insert into Автомобили values (20,7,'Mazda 3')

insert into Автомобили values (21,7,'Mazda 6')

insert into Автомобили values (22,8,'Mitsubishi Lancer')

insert into Автомобили values (23,8,'Mitsubishi Outlander')

insert into Автомобили values (24,8,'Mitsubishi Pajero')

insert into Автомобили values (25,9,'Nissan Qashqai')

insert into Автомобили values (26,9,'Nissan GT-R')

insert into Автомобили values (27,9,'Nissan Teana')

insert into Автомобили values (28,10,'Porsche 911')

insert into Автомобили values (29,10,'Porsche Cayman')

insert into Автомобили values (30,10,'Porsche Panamera')

Результат обработки запроса представлен на рисунке 5.

Рисунок 5. Данные таблицы Автомобили

Запрос на вывод информации фирмы и принадлежащего ей автомобиля:

select Марка, Поставщик.Фирма from Автомобили inner join Поставщик on (Автомобили.Фирма=Поставщик.id_фирмы)

Результат обработки запроса представлен на рисунке 6.

Рисунок 6. Автомобиль и его фирма

Запрос на вывод информации номера автомобиля, его марки и количество автомобилей, отсортированного по возрастанию:

select Поставки.Автомобиль, Марка, Количество from Поставки inner join Автомобили on (Поставки.Автомобиль=Автомобили.id_автомобиля)order by Количество asc

Результат обработки запроса представлен на рисунке 7.

Рисунок 7. Автомобиль и его количество

Запрос на вывод информации номера автомобиля, его марки и цены на продажу:

select Поставки.Автомобиль, Марка, (Цена_поставки*2) as Цена_автомобиля from Поставки inner join Автомобили on (Поставки.Автомобиль=Автомобили.id_автомобиля)

Результат обработки запроса представлен на рисунке 8.

Рисунок 8. Автомобиль и его цена на продажу

Запрос на вывод информации менеджеров и проданных ими автомобилей с указание цены, отсортированного по фамилии:

select Фамилия, Имя, Отчество, Продажи.Автомобиль, Цена, Дата_продажи from Мененджер inner join Продажи on (id_мененджера=Продавец) order by Фамилия desc

Результат обработки запроса представлен на рисунке 9.

Рисунок 9. Менеджеры и их продажи

Запрос на вывод информации о клиентах и их покупке, отсортированного по возрастанию даты покупки:

select Фамилия, Имя, Отчество, Продажи.Автомобиль, Цена as Цена_покупки, Дата_продажи from Клиенты inner join Продажи on (id_клиента=Клиент) order by Дата_продажи asc

Результат обработки запроса представлен на рисунке 10.

Рисунок 10. Клиенты и их покупки

Запрос на вывод информации об автомобилях, цена которых не меньше 100000 и не больше 1000000:

select Марка, (Цена_поставки*2) as Цена_автомобиля from Поставки inner join Автомобили on (Поставки.Автомобиль=Автомобили.id_автомобиля) where Цена_поставки*2 between 100000 and 1000000

Результат обработки запроса представлен на рисунке 11.

Рисунок 11. Автомобили стоимостью от 100000 до 1000000

Запрос на вывод информации о менеджерах, где в одной строке будут их фамилия, имя, отчество, а в другой их сумма выручки за продажи автомобилей, сгруппированному по первому столбцу:

select Фамилия + ' ' + Имя + ' ' + Отчество as Продавец, sum(Цена) as Общая_выручка from Мененджер inner join Продажи on (Продавец=id_мененджера) group by Фамилия + ' ' + Имя + ' ' + Отчество

Результат обработки запроса представлен на рисунке 12.

Рисунок 12. Менеджеры и выручка

Запрос на вывод информации об автомобилях, цена которых ниже средней цены поставки:

select Марка, Цена_поставки*2 as Цена_автомобиля from Поставки inner join Автомобили on (Поставки.Автомобиль=Автомобили.id_автомобиля) where Цена_поставки*2

Код для цитирования: Скопировать