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

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

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ

МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

ЭКОНОМИКИ, СТАТИСТИКИ И ИНФОРМАТИКИ (МЭСИ)

Курсовая работа

по дисциплине «База данных»

на тему: «Проектирование БД для автосервисной фирмы»

Студент: Савченко Максим Андреевич

Специальность: Бизнес-информатика

Преподаватель: Корнеев Дмитрий Геннадьевич

МОСКВА 2015



Оглавление

• 1. Описание предметной области
• 2. ER-модель в нотации IDEF 1x
• 3. Скрипт на языке SQL на создание объектов: таблиц, ключей, индексов и др. (команды CREATE …, ALTER TABLE …)
• 4. Скрипт для заполнения таблиц данными (команды INSERT)
• 5. SQL-запросы SELECT


1. Описание предметной области

Данная база данных предназначена для организаций, занимающихся любыми видами услуг по техническому обслуживанию автомобилей (Автосервис). БД позволяет вести учет всех автомобилей, когда-либо находящихся в автосервисе, хранит полную информацию о каждом автомобиле (марка, регистрационный знак, цвет, год выпуска, и т.п.), позволяет вести учет владельцев автомобилей, которые когда-либо обращались в автосервис. Программа позволяет также распечатать отчет по всем параметрам, интересующим как владельцев автосервиса (информация о владельцах автомобилей, информация об автомобилях, полный отчет по всем заказам либо по заказам за определенный интервал времени), так и его клиентов (расценки на услуги, новые запчасти, сезонные скидки); это позволяет вести отчетность на бумаге.

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

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



2. ER-модель в нотации IDEF 1x

Описание таблиц:

1. Автомобили (CARS)

ID_CARS (PK) - Код автомобиля (Имеет первичный ключ)

NUMBER - Регистрационный номер автомобиля

YEAR - Год выпуска автомобиля

ID_COUNTRY (FK) - Код страны производителя (Имеет внешний ключ)

ID_COL (FK) - Код цвета автомобиля (Имеет внешний ключ)

ID_CUST (FK) - Код клиента (Имеет внешний ключ)
ID_MARKA(FK) - Код марки автомобиля (Имеет внешний ключ)

2. Клиенты (CUSTOMERS)

ID_CUST (PK) - Код клиента (Имеет первичный ключ)
PHONE - Номер телефона клиента
ID_DISK (FK) - Код дисконтной карты клиента (Имеет внешний ключ)

3. Виды Работ (TYPE_WORK)

ID_WORK (PK) - Код работы (Имеет первичный ключ)

NAME_WORK - Наименование работы

COST - Стоимость работы

TIME_DO - Срок выполнения работы

4. Мастера (MASTER)

ID_MASTER - Код мастера (Имеет первичный ключ)

NAME_MASTER - ФИО мастера

SALARY - Зарплата мастера

ID_TM (FK) - Код должности мастера (Имеет внешний ключ)

5. Должность Мастера (TYPE_MASTER)

ID_TM (PK) - Код должности мастера (Имеет первичный ключ)

NAME_TM - Наименование должности

6. Заказы (ORDERS)

ID_ORDER (PK) - Код заказа (Имеет первичный ключ)

NAME_ORDER - Название заказа

ID_CARS (FK) - Код автомобиля (Имеет внешний ключ)

ID_MASTER (FK) - Код мастера (Имеет внешний ключ)

ID_WORK (FK) - Код работы (Имеет внешний ключ)

7. Дисконтная карта (DISCOUNT)

ID_DISK (PK) - Код дисконтной карты (Имеет первичный ключ)

NUMBER_DISK - Номер дисконтной карты

PERCENTS - Процент скидки

8. Цвет автомобиля (COLOUR)

ID_COL (PK) - Код цвета (Имеет первичный ключ)

NAME_COL - Название цвета

9. Марка автомобиля (MARKA)

ID_MARKA (PK) - Код марки (Имеет первичный ключ)

NAME_MARKA - Название марки

10. Страна производитель (COUNTRY)

ID_COUNTRY (PK) - Код страны (Имеет первичный ключ)

NAME_COUNT - Название страны

Таблицы CARS и COUNTRY связаны между собой не идентифицирующей связью через уникальное поле ID_COUNTRY. Это необходимо, чтобы через базу данных можно было узнать в какой стране был произведен данный автомобиль.

Таблицы CARS и CUSTOMERS связаны между собой не идентифицирующей связью через уникальное поле ID_CUST. Это необходимо, чтобы через базу данных можно было узнать кто владелец данного автомобиля.

Таблицы CUSTOMERS и DISCOUNT связаны между собой не идентифицирующей связью через уникальное поле ID_DISK. Это необходимо, чтобы через базу данных можно было узнать какую дисконтную карту имеет клиент.

Таблицы CARS и MARKA связаны между собой не идентифицирующей связью через уникальное поле ID_MARKA. Это необходимо, чтобы через базу данных можно было узнать какой марки автомобиль.

Таблицы CARS и COLOUR связаны между собой не идентифицирующей связью через уникальное поле ID_COL. Это необходимо, чтобы через базу данных можно было узнать какой цвет имеет автомобиль.

Таблицы CARS и ORDER связаны между собой не идентифицирующей связью через уникальное поле ID_CARS. Это необходимо, чтобы через базу данных можно было узнать какой тип заказа имеет автомобиль.

Таблицы ORDER и TYPE_WORK связаны между собой не идентифицирующей связью через уникальное поле ID_WORK. Это необходимо, чтобы через базу данных можно было узнать какой вид работы, стоимость и срок выполнения имеет заказ

Таблицы ORDER и MASTER связаны между собой не идентифицирующей связью через уникальное поле ID_MASTER. Это необходимо, чтобы через базу данных можно было узнать какой мастер выполняет заказ

Таблицы MASTER и TYPE_MASTER связаны между собой не идентифицирующей связью через уникальное поле ID_TM. Это необходимо, чтобы через базу данных можно было узнать какую должность имеет мастер

3. Скрипт на языке SQL на создание объектов: таблиц, ключей, индексов и др. (команды CREATE …, ALTER TABLE …)

CREATE TABLE CUSTOMERS

(

ID_CUST INTEGER NOT NULL,

NAME_CUST CHAR(50) NULL,

PHONE CHAR(50) NULL,

ID_DISC INTEGER NULL

);

CREATE UNIQUE INDEX IXCUSTOMERS ON CUSTOMERS

(

ID_CUST ASC

);

CREATE TABLE CARS

(

ID_CARS INTEGER NOT NULL,

NUMBER CHAR(50) NULL,

YEAR INTEGER NOT NULL,

ID_COUNTRY INTEGER NULL,

ID_COL INTEGER NULL,

ID_CUST INTEGER NULL,

ID_MARKA INTEGER NULL

); база скрипт запрос автосервис

CREATE UNIQUE INDEX IXCARS ON CARS

(

ID_CARS ASC

);

CREATE TABLE COLOUR

(

ID_COL INTEGER NOT NULL,

NAME_COL CHAR(50) NULL

);

CREATE UNIQUE INDEX IXCOLOUR ON COLOUR

(

ID_COL ASC

);

CREATE TABLE TYPE_WORK

(

ID_WORK INTEGER NOT NULL,

NAME_WORK CHAR(50) NULL,

COST INTEGER NOT NULL,

TIME_DO INTEGER NOT NULL

);

CREATE UNIQUE INDEX IXTYPE_WORK ON TYPE_WORK

(

ID_WORK ASC

);

CREATE TABLE MASTER

(

ID_MASTER INTEGER NOT NULL,

NAME_MAST CHAR(50) NULL,

PHONE_M CHAR(50) NULL,

SALARY INTEGER NOT NULL,

ID_TM INTEGER NULL

);

CREATE UNIQUE INDEX IXMASTER ON MASTER

(

ID_MASTER ASC

);

CREATE TABLE TYPE_MASTER

(

ID_TM INTEGER NOT NULL,

NAME_TM CHAR(50) NULL

);

CREATE UNIQUE INDEX IXTYPE_MASTER ON TYPE_MASTER

(

ID_TM ASC

);

CREATE TABLE COUNTRY

(

ID_COUNTRY INTEGER NOT NULL,

NAME_COUNT CHAR(50) NULL

);

CREATE UNIQUE INDEX IXCOUNTRY ON COUNTRY

(

ID_COUNTRY ASC

);

CREATE TABLE DISCOUNT

(

ID_DISC INTEGER NOT NULL,

NUMBER_DISC CHAR(50) NULL,

PERCENTS INTEGER NOT NULL

);

CREATE UNIQUE INDEX IXDISCOUNT ON DISCOUNT

(

ID_DISC ASC

);

CREATE TABLE ORDERS

(

ID_ORDER INTEGER NOT NULL,

NAME_ORDER CHAR(50) NULL,

ID_CARS INTEGER NULL,

ID_MASTER INTEGER NULL,

ID_WORK INTEGER NULL

);

CREATE UNIQUE INDEX IXORDER ON ORDERS

(

ID_ORDER ASC

);

CREATE TABLE MARKA

(

ID_MARKA INTEGER NOT NULL,

NAME_MARKA CHAR(50) NULL

);

CREATE UNIQUE INDEX IXMARKA ON MARKA

(

ID_MARKA ASC

);

ALTER TABLE CUSTOMERS

ADD PRIMARY KEY (ID_CUST);

ALTER TABLE CARS

ADD PRIMARY KEY (ID_CARS);

ALTER TABLE COLOUR

ADD PRIMARY KEY (ID_COL);

ALTER TABLE TYPE_WORK

ADD PRIMARY KEY (ID_WORK);

ALTER TABLE MASTER

ADD PRIMARY KEY (ID_MASTER);

ALTER TABLE TYPE_MASTER

ADD PRIMARY KEY (ID_TM);

ALTER TABLE COUNTRY

ADD PRIMARY KEY (ID_COUNTRY);

ALTER TABLE DISCOUNT

ADD PRIMARY KEY (ID_DISC);

ALTER TABLE ORDERS

ADD PRIMARY KEY (ID_ORDER);

ALTER TABLE MARKA

ADD PRIMARY KEY (ID_MARKA);

ALTER TABLE CUSTOMERS

ADD FOREIGN KEY (ID_DISC) REFERENCES DISCOUNT;

ALTER TABLE CARS

ADD FOREIGN KEY (ID_COUNTRY) REFERENCES COUNTRY;

ALTER TABLE CARS

ADD FOREIGN KEY (ID_COL) REFERENCES COLOUR;

ALTER TABLE CARS

ADD FOREIGN KEY (ID_CUST) REFERENCES CUSTOMERS;

ALTER TABLE CARS

ADD FOREIGN KEY (ID_MARKA) REFERENCES MARKA;

ALTER TABLE MASTER

ADD FOREIGN KEY (ID_TM) REFERENCES TYPE_MASTER;

ALTER TABLE ORDERS

ADD FOREIGN KEY (ID_CARS) REFERENCES CARS;

ALTER TABLE ORDERS

ADD FOREIGN KEY (ID_MASTER) REFERENCES MASTER;

ALTER TABLE ORDERS

ADD FOREIGN KEY (ID_WORK) REFERENCES TYPE_WORK;

4. Скрипт для заполнения таблиц данными (команды INSERT)

INSERT INTO MARKA VALUES (1,'МЭРСЕДЕС');

INSERT INTO MARKA VALUES (2,'БМВ');

INSERT INTO MARKA VALUES (3,'ВАЗ');

INSERT INTO MARKA VALUES (4,'ФОРД');

INSERT INTO MARKA VALUES (5,'ОПЕЛЬ');

INSERT INTO MARKA VALUES (6,'ХОНДА');

INSERT INTO MARKA VALUES (7,'ТАЙОТА');

INSERT INTO COLOUR VALUES (1,'ЧЕРНЫЙ');

INSERT INTO COLOUR VALUES (2,'БЕЛЫЙ');

INSERT INTO COLOUR VALUES (3,'КРАСНЫЙ');

INSERT INTO COLOUR VALUES (4,'СЕРЫЙ');

INSERT INTO COLOUR VALUES (5,'СИНИЙ');

INSERT INTO DISCOUNT VALUES (1,8467387, 5);

INSERT INTO DISCOUNT VALUES (2,9370122, 10);

INSERT INTO DISCOUNT VALUES (3,8403859, 15);

INSERT INTO DISCOUNT VALUES (4,3390275, 5);

INSERT INTO DISCOUNT VALUES (5,0038572, 5);

INSERT INTO DISCOUNT VALUES (6,9372016, 10);

INSERT INTO DISCOUNT VALUES (7,9472564, 5);

INSERT INTO DISCOUNT VALUES (8,5964930, 10);

INSERT INTO TYPE_WORK VALUES (1,'ПОКРАСКА',5000, 7);

INSERT INTO TYPE_WORK VALUES (2,'РЕМОНТ КПП',20000, 5);

INSERT INTO TYPE_WORK VALUES (3,'ДИАГНОСТИКА',1000, 1);

INSERT INTO TYPE_WORK VALUES (4,'ШИНОМОНТАЖ',800, 1);

INSERT INTO TYPE_WORK VALUES (5,'РЕМОНТ ДВИГАТЕЛЯ', 15000, 7);

INSERT INTO TYPE_WORK VALUES (6,'РЕМОНТ ХОДОВОЙ',2000, 2);

INSERT INTO TYPE_WORK VALUES (7,'МОЙКА',500, 1);

INSERT INTO TYPE_WORK VALUES (8,'ЗАМЕНА МАСЛА',3000, 1);

INSERT INTO CUSTOMERS VALUES (1,'ПАЛЬЧЕНКО ВАСИЛИЙ АНДРЕЕВИЧ',89054171003, 1);

INSERT INTO CUSTOMERS VALUES (2,'ЛЕЩЕНКО ДЕНИС ВИТАЛЬЕВИЧ',89283450000, 2);

INSERT INTO CUSTOMERS VALUES (3,'ХРЫКИН ВЛАДИМИР ИВАНОВИЧ',89166962229, 3);

INSERT INTO CUSTOMERS VALUES (4,'АРЗУМАНОВ СЕРГЕЙ ВАЛЕРЬЕВИЧ',89281193223, 4);

INSERT INTO CUSTOMERS VALUES (5,'КУХТИН ОЛЕГ ГЕОРГИЕВИЧ',89188117755, 5);

INSERT INTO CUSTOMERS VALUES (6,'ШЕРАНОВ ДМИТРИЙ СЕРГЕЕВИЧ', 89292311343, 6);

INSERT INTO CUSTOMERS VALUES (7,'САМОЙЛЕНКО ИЛЬЯ ВЛАДИМИРОВИЧ', 89293444399, 7);

INSERT INTO CUSTOMERS VALUES (8,'ШТЕПА КАРИНА ЕВГЕНЬЕВНА', 89283002375, 8);

INSERT INTO COUNTRY VALUES (1,'ГЕРМАНИЯ');

INSERT INTO COUNTRY VALUES (2,'АМЕРИКА');

INSERT INTO COUNTRY VALUES (3,'РОССИЯ');

INSERT INTO COUNTRY VALUES (4,'ЯПОНИЯ');

INSERT INTO TYPE_MASTER VALUES (1,'МАЛЯР');

INSERT INTO TYPE_MASTER VALUES (2,'МЕХАНИК');

INSERT INTO TYPE_MASTER VALUES (3,'ШИНОМОНТАЖНИК');

INSERT INTO TYPE_MASTER VALUES (4,'АВТОМОЙЩИК');

INSERT INTO TYPE_MASTER VALUES (5,'МАСТЕР ПО РЕМОНТУ ХОДОВОЙ');

INSERT INTO CARS VALUES (1,'К340РМ', 2012, 3, 2, 1, 3);

INSERT INTO CARS VALUES (2,'У555ОТ', 2013, 1, 5, 2, 2);

INSERT INTO CARS VALUES (3,'К277РА', 2009, 4, 4, 3, 6);

INSERT INTO CARS VALUES (4,'К303ТЕ', 2008, 4, 1, 4, 7);

INSERT INTO CARS VALUES (5,'Р771НН', 2009, 1, 2, 5, 5);

INSERT INTO CARS VALUES (6,'А123МР', 2010, 1, 5, 6, 1);

INSERT INTO CARS VALUES (7,'Е750КХ', 2011, 2, 1, 7, 4);

INSERT INTO CARS VALUES (8,'К777КК', 2012, 1, 3, 8, 1);

INSERT INTO MASTER VALUES (1,'ФОМИН ДМИТРИЙ СЕРГЕЕВИЧ', 89183949439, 30000, 2);

INSERT INTO MASTER VALUES (2,'ЗИНКОВСКИЙ ГЛЕБ ЭДУАРДОВИЧ', 89263734774, 35000, 1);

INSERT INTO MASTER VALUES (3,'СВЕРКУНОВ ЕВГЕНИЙ ОЛЕГОВИЧ', 89134444223, 20000, 4);

INSERT INTO MASTER VALUES (4,'БАДИЛЕНКО АНДРЕЙ ВЛАДИМИРОВИЧ', 89150330154, 20000, 3);

INSERT INTO MASTER VALUES (5,'УШКОВ НИКИТА МАКСИМОВИЧ', 89283036423, 30000, 5);

INSERT INTO MASTER VALUES (1,'ЦВЕТКОВ ДМИТРИЙ СЕРГЕЕВИЧ', 89188892819, 27000, 2);

INSERT INTO ORDERS VALUES (1, 423, 8, 3, 7);

INSERT INTO ORDERS VALUES (2, 321, 2, 2, 1);

INSERT INTO ORDERS VALUES (3, 634, 7, 5, 6);

INSERT INTO ORDERS VALUES (4, 989, 1, 1, 3);

INSERT INTO ORDERS VALUES (5, 467, 6, 4, 4);

INSERT INTO ORDERS VALUES (6, 651, 5, 1, 5);

INSERT INTO ORDERS VALUES (7, 342, 3, 1, 2);

INSERT INTO ORDERS VALUES (8, 542, 4, 1, 8);

5. SQL-запросы SELECT

а) (WHERE)

ВЫДАТЬ ФИО КЛИЕНТА, НОМЕР МАШИНЫ И ЕГО МАРКУ

ОТСОРТИРОВАТЬ ПО МАРКЕ МАШИНЫ ПО ВОЗРАСТАНИЮ

SELECT CUSTOMERS.NAME_CUST, CARS.NUMBER, MARKA.NAME_MARKA

FROM CUSTOMERS, CARS, MARKA

WHERE CUSTOMERS.ID_CUST=CARS.ID_CUST AND CARS.ID_MARKA=MARKA.ID_MARKA

ORDER BY MARKA.NAME_MARKA ASC

б) (INNER JOIN)

ВЫДАТЬ НАЗВАНИЕ СТРАНЫ И КОЛ-ВО МАШИН, КОТОРАЯ ТАМ ПРОИЗВЕДЕНА

ПРИ УСЛОВИИ, ЧТО ПРОИЗВЕДЕНА В ГЕРМАНИИ ИЛИ ЯПОНИИ И МАШИН БОЛЬШЕ 1

SELECT COUNTRY.NAME_COUNT, COUNT(*)

FROM (COUNTRY INNER JOIN CARS ON COUNTRY.ID_COUNTRY = CARS.ID_COUNTRY)

INNER JOIN MARKA ON CARS.ID_MARKA = MARKA.ID_MARKA

WHERE COUNTRY.NAME_COUNT IN ('ГЕРМАНИЯ', 'ЯПОНИЯ')

GROUP BY COUNTRY.NAME_COUNT

HAVING COUNT(*)>1

ORDER BY 1 ASC

в) (команда CASE)

ВЫДАТЬ ФИО МАСТЕРА И КОЛ-ВО ЗАКАЗОВ,

УСЛОВИЕ МЕНЬШЕ ДВУХ ЗАКАЗОВ ВЫДАВАТЬ "МАЛО"

БОЛЬШЕ 2 - "МНОГО"

SELECT MASTER.NAME_MAST,

CASE

WHEN COUNT(ORDERS.ID_ORDER)<2 THEN 'МАЛО'

ELSE 'МНОГО'

END 'КОЛИЧЕСТВО', COUNT(ORDERS.ID_ORDER)

FROM (MASTER LEFT OUTER JOIN ORDERS ON ORDERS.ID_MASTER=MASTER.ID_MASTER)

GROUP BY MASTER.NAME_MAST

ORDER BY 1 ASC

г) (HAVING)

ВЫДАТЬ НАЗВАНИЕ ЦВЕТА И КОЛ-ВО МАРОК АВТО, ТАКОГО ЦВЕТА

ПРИ УСЛОВИИ, ЧТО МАШИН БОЛЬШЕ 1

SELECT COLOUR.NAME_COL, COUNT (*)

FROM COLOUR,MARKA,CARS

WHERE (MARKA.ID_MARKA=CARS.ID_MARKA) AND (COLOUR.ID_COL=CARS.ID_COL)

GROUP BY COLOUR.NAME_COL

HAVING COUNT (*)>1

ORDER BY COLOUR.NAME_COL DESC



д) (OUTER JOIN)

ВЫДАТЬ СПИСОК МАСТЕРОВ И КОЛ-ВО ЗАКАЗОВ

КОТОРЫЕ ОНИ СДЕЛАЛИ

ЕСЛИ НЕ ДЕЛАЛИ ЗАКАЗ, ТО ВЫДАТЬ 0

SELECT MASTER.NAME_MAST, COUNT(ORDERS.ID_ORDER)

FROM (MASTER LEFT OUTER JOIN ORDERS ON ORDERS.ID_MASTER=MASTER.ID_MASTER)

GROUP BY MASTER.NAME_MAST

ORDER BY MASTER.NAME_MAST ASC



е) (вложенный SELECT)

1) ВЫДАТЬ НАЗВАНИЕ РАБОТЫ И ЕГО СТОИМОСТЬ

ПРИ УСЛОВИИ, ЧТО РАБОТА СТОИТ ДОРОЖЕ ВСЕГО

SELECT TYPE_WORK.NAME_WORK, TYPE_WORK.COST

FROM TYPE_WORK

WHERE (TYPE_WORK.COST) IN

(SELECT MAX (TYPE_WORK.COST)

FROM TYPE_WORK)

ORDER BY (TYPE_WORK.NAME_WORK)

2) ВЫБРАТЬ ФИО КЛИЕНТОВ И НОМЕРА ИХ ДИСКОНТНЫХ КАРТ

ВЫБРАТЬ КАРТЫ С МАКСИМАЛЬНОЙ СКИДКОЙ

РЕЗУЛЬТАТ ОТСОРТИРОВАТЬ ПО ФИО КЛИЕНТА

SELECT DISCOUNT.NUMBER_DISC, DISCOUNT.PERCENTS

FROM DISCOUNT

WHERE DISCOUNT.PERCENTS IN (SELECT MAX (DISCOUNT.PERCENTS)

FROM DISCOUNT)

ORDER BY DISCOUNT.NUMBER_DISC ASC

ж) Представление VIEW

CREATE VIEW Maste AS

SELECT CUSTOMERS.NAME_CUST, CARS.NUMBER, MARKA.NAME_MARKA

FROM CUSTOMERS, CARS, MARKA

WHERE CUSTOMERS.ID_CUST=CARS.ID_CUST AND CARS.ID_MARKA=MARKA.ID_MARKA

SELECT *

FROM maste



Размещено на Allbest.ru

...