База данных проката автомобилей

Курсовой проект “БД по прокату автомобилей” Вариант 20. по курсу "Базы данных" Оглавление Оглавление 2 Задание на курсовой проект 3 Инфологическое проектирование 3 Анализ предметной области 3 Анализ информационных задач и круга пользователей системы 4 Определение требований к операционной обстановке 6

Выбор СУБД и других программных средств 6 Логическое проектирование реляционной БД 7 Преобразование ER–диаграммы в схему базы данных 7 Составление реляционных отношений 9 Нормализация полученных отношений 10 Определение дополнительных ограничений целостности 12 Описание групп пользователей и прав доступа 12 Реализация проекта базы данных 13

Список литературы 14 Задание на курсовой проект Вариант 20. БД по прокату автомобилей. Задача - информационная поддержка деятельности пункта по прокату легковых автомобилей. БД должна осуществлять:  ведение списка автомобилей;  ведение списка клиентов;  поиск автомобилей по марке, вместимости, цвету, году выпуска;  учет времени проката;  учет стоимости проката (цена проката зависит от марки автомобиля); &

#61630; предоставление скидок для постоянных клиентов: величина скидки зависит от стажа водителя, от того, сколько раз данный клиент брал автомобили напрокат и от степени аварийности его езды. Готовые запросы:  Список автомобилей, которые в настоящее время не сданы напрокат.  Список моделей легковых автомобилей с ценой не более 1600 рублей в день.  Список автомобилей, пользующихся наибольшим спросом в течение последнего месяца. 

Список автомобилей, не пользующихся спросом.  Список клиентов, которые брали напрокат одновременно более одного автомобиля.  Список постоянных клиентов с указанием того, сколько раз они брали напрокат автомобили (в разное время).  Расчет суммы, которую должен за прокат определенный клиент. Инфологическое проектирование Анализ предметной области

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

 Каждый клиент может вноситься в базу данных только один раз;  Каждый автомобиль может вноситься в базу данных только один раз;  Каждый клиент может брать в прокат несколько автомобилей одновременно;  Каждый автомобиль не может быть взят на прокат несколькими клиентами одновременно;  Каждый клиент может находиться в очереди за несколькими автомобилями; 

В очереди может быть несколько клиентов; Выделим базовые сущности этой предметной области: Клиенты. Атрибуты клиентов – уникальный ID, ФИО, адрес, номер телефона, дата выдачи прав. Автомобили. Атрибуты автомобилей – уникальный ID автомобиля, марка, вместимость, цвет, год выпуска, модель, пробег. Выдачи автомобилей и очередь на получение автомобиля будем рассматривать как связи между клиентами и автомобилями. Выдачи автомобилей.

Атрибуты выдач – дата выдачи, срок выдачи, стоимость, дата возвращения, состояние автомобиля, возмещение ущерба, скидка. Очередь на получение автомобиля. Атрибуты очереди – дата постановки в очередь. рис.1 “ER – диаграмма базы данных по прокату автомобилей ” Анализ информационных задач и круга пользователей системы Система создаётся для обслуживания следующих групп пользователей:  администрация (дирекция);

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

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

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

Определим границы информационной поддержки пользователей: 1) Функциональные возможности:  ведение БД (запись, чтение, модификация, удаление в архив);  обеспечение логической непротиворечивости БД;  обеспечение защиты данных от несанкционированного или случайного доступа (определение прав доступа);  реализация наиболее часто встречающихся запросов в готовом виде; 

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

Список клиентов, которые брали напрокат одновременно более одного автомобиля.  Список постоянных клиентов с указанием того, сколько раз они брали напрокат автомобили (в разное время).  Расчет суммы, которую должен за прокат определенный клиент. Определение требований к операционной обстановке Для выполнения этого этапа необходимо знать (хотя бы ориентировочно) объём работы фирмы по прокату автомобилей (т.е. количество автомобилей и клиентов),

а также иметь представление о характере и интенсивности запросов. Объём внешней памяти, необходимый для функционирования системы, складывается из двух составляющих: память, занимаемая модулями СУБД (ядро, утилиты, вспомогательные программы), и память, отводимая под данные (МД). Наиболее существенным обычно является МД. Объём памяти МД, требуемый для хранения данных, можно приблизительно оценить по формуле где li –

длина записи в i-й таблице (в байтах), Ni – примерное (максимально возможное) количество записей в i-й таблице, Na – количество записей в архиве i-й таблицы. Коэффициент 2 перед суммой нужен для того, чтобы выделить память для хранения индексов, промежуточных данных, для выполнения объёмных операций (например, сортировки) и т.п. Посчитаем приблизительно, какой объём внешней памяти потребуется для хранения данных.

Примем ориентировочно, что: • в наличии находится 35 автомобилей (по 0.2К); • у фирмы 30 клиентов (по 0.2К); • в день выдается около 10 автомобилей (по 0.1К); • в день в очереди находится около 3 человек (по 0,1К); Тогда объём памяти для хранения данных за первый год примерно составит: Mc = 2(35*0,2+30*0,2+250(10*0,1)+ 250(3*0,1)) = 675 К » 0,7 М, где 250 – количество рабочих дней в году.

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

требуемых функций подходят почти все СУБД для ПЭВМ (FoxPro, Clipper, MS Access и др.). Все они поддерживают реляционную модель данных и предоставляют разнообразные возможности для работы с данными. Объём внешней и оперативной памяти, требующийся для функционирования СУБД, обычно указывается в сопроводительной документации. Для того чтобы в учебном примере не привязываться к конкретной

СУБД, выполним описание логической схемы БД на MySQL 5.1. Логическое проектирование реляционной БД Преобразование ER–диаграммы в схему базы данных База данных создаётся на основании схемы базы данных. Для преобразования ER–диаграммы в схему БД приведём уточнённую ER–диаграмму, содержащая атрибуты сущностей. Уточнённая

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

Полученная схема реляционной базы данных (РБД) Составление реляционных отношений Все отношения данной БД не имеют потенциальных ключей, поэтому в качестве первичных ключей будем использовать суррогатные ключи: • A_id – суррогатный ключ отношения АВТОМОБИЛИ; • C_id – суррогатный ключ отношения КЛИЕНТЫ; Отношения приведены в табл. 1-4. Для каждого отношения указаны атрибуты с их внутренним названием,

типом и длиной. Типы данных обозначаются так: N – числовой, C – символьный, D – дата (последний имеет стандартную длину, зависящую от СУБД, поэтому она не указывается). Таблица 1.Схема отношения АВТОМОБИЛИ(auto) Содержание поля Имя поля Тип, длина Примечания Идентификатор автомобиля A_id N(4) суррогатный первичный ключ

Марка A_brand C(20) обязательное поле Модель A_model C(30) обязательное поле Вместимость A_capacity N(1) обязательное поле Цвет A_color C(20) обязательное поле Год выпуска A_year C(4) обязательное поле Пробег A_mileage N(8) обязательное поле Стоимость проката A_price N(4) обязательное поле Таблица 2.Схема отношения

КЛИЕНТЫ(clients) Содержание поля Имя поля Тип, длина Примечания Идентификатор клиента C_id N(4) суррогатный первичный ключ ФИО C_name C(50) обязательное поле Адрес C_address C(50) Номер телефона C_phone C(30) многозначное поле Дата выдачи прав C_experience D Обязательное поле Таблица 3.Схема отношения

ВЗЯТЬ НА ПРОКАТ(rent) Содержание поля Имя поля Тип, длина Примечания Идентификатор автомобиля A_id N(4) внешний ключ (к auto); составной первичный ключ Идентификатор клиента C_id N(4) внешний ключ (к clients); составной первичный ключ Дата выдачи R_date D составной первичный ключ Срок выдачи R_term N(3) обязательное поле Стоимость R_price N(6,2) обязательное поле; стоимость с учетом всех скидок

Дата возвращения R_rdate D дата фактического возвращения автомобиля клиентом Состояние автомобиля A_state C(200) состояние автомобиля после возвращения Возмещение ущерба R_crash N(8) стоимость поломки Скидка R_skidk N(2) вычисляемое поле; скидка в процентах Таблица 4.Схема отношения ОЧЕРЕДЬ(turn) Содержание поля Имя поля Тип, длина Примечания

Идентификатор автомобиля A_id N(4) внешний ключ (к auto); составной первичный ключ Идентификатор клиента C_id N(4) внешний ключ (к clients); составной первичный ключ Дата постановки в очередь T_date D составной первичный ключ Нормализация полученных отношений 1НФ. Для приведения таблиц к 1НФ требуется составить прямоугольные таблицы (один атрибут – один столбец) и разбить сложные атрибуты на простые, а многозначные атрибуты

вынести в отдельные отношения. Примечание. В реальных БД сложные атрибуты разбиваются на простые, если: а) этого требует внешнее представление данных; б) в запросах поиск может осуществляться по отдельной части атрибута. Разделим атрибут ФИО на два атрибута Фамилия и Имя, отчество. Многозадачный атрибут Телефоны клиентов следует выделить в отдельное отношение

ТЕЛЕФОНЫ. 2НФ. В нашем случае составные первичные ключи имеют отношение ВЗЯТЬ НА ПРОКАТ и ОЧЕРЕДЬ. Неключевые атрибуты этих отношений функционально полно зависят от первичных ключей. 3НФ. В отношении ВЗЯТЬ НА ПРОКАТ атрибут Скидка зависит от атрибутов Дата выдачи прав, Стоимость ущерба про предыдущим выдачам и от количества выдач, а не от первичного ключа, поэтому Скидку следует вынести в отдельное отношение.

Но если мы выделим в отдельное отношение, то получившиеся связи будут иметь тип 1:1. Следовательно, декомпозиция нецелесообразна. В отношении АВТОМОБИЛИ атрибуты Марка и Вместимость зависят от атрибута Модель, а не от первичного ключа, поэтому выносим в отдельную таблицу. Содержание поля Имя поля Тип, длина Примечания Модель

A_model C(30) первичный ключ Марка A_brand C(20) обязательное поле Вместимость A_capacity N(1) обязательное поле 4НФ. Отношения данного примера не нарушают 4НФ, т.к. не содержат нетривиальных многозначных зависимостей. В реальных базах данных после нормализации может проводиться денормализация. Она проводится с одной целью – повышение производительности

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

приведены в таблицах: Таблица 5.Схема отношения АВТОМОБИЛИ(auto) Содержание поля Имя поля Тип, длина Примечания Идентификатор автомобиля A_id N(4) суррогатный первичный ключ Модель A_model C(30) внешний ключ (к models) Цвет A_color C(20) обязательное поле Год выпуска A_year C(4) обязательное поле Пробег A_mileage

N(8) обязательное поле Стоимость проката A_price N(4) обязательное поле Таблица 6.Схема отношения КЛИЕНТЫ(clients) Содержание поля Имя поля Тип, длина Примечания Идентификатор клиента C_id N(4) суррогатный первичный ключ Фамилия C_fname C(20) обязательное поле Имя, отчество C_lname C(30) обязательное поле

Адрес C_address C(50) Номер телефона C_phone C(30) многозначное поле Дата выдачи прав C_experience D Обязательное поле Таблица 7.Схема отношения ВЗЯТЬ НА ПРОКАТ(rent) Содержание поля Имя поля Тип, длина Примечания Идентификатор автомобиля A_id N(4) внешний ключ (к auto); составной первичный ключ Идентификатор клиента C_id N(4) внешний ключ (к clients); составной первичный ключ

Дата выдачи R_date D составной первичный ключ Срок выдачи R_term N(3) обязательное поле Стоимость R_price N(6,2) обязательное поле; стоимость с учетом всех скидок Дата возвращения R_rdate D дата фактического возвращения автомобиля клиентом Состояние автомобиля A_state C(200) состояние автомобиля после возвращения Возмещение ущерба R_crash N(8) стоимость поломки Скидка

R_skidk N(2) вычисляемое поле; скидка в процентах Таблица 8.Схема отношения ОЧЕРЕДЬ(turn) Содержание поля Имя поля Тип, длина Примечания Идентификатор автомобиля A_id N(4) внешний ключ (к auto); составной первичный ключ Идентификатор клиента C_id N(4) внешний ключ (к clients); составной первичный ключ Дата постановки в очередь T_date D составной первичный ключ

Таблица 9.Схема отношения МОДЕЛИ(models) Содержание поля Имя поля Тип, длина Примечания Модель A_model C(30) первичный ключ Марка A_brand C(20) обязательное поле Вместимость A_capacity N(1) обязательное поле Определение дополнительных ограничений целостности Перечислим ограничения целостности, которые не указаны в табл.

4–8. 1. Значения всех числовых атрибутов – больше 0 (или null, если атрибут необязателен). 2. В отношении ВЗЯТЬ НА ПРОКАТ значение поля “дата возвращения” не может быть меньше значения поля “даты выдачи” Описание групп пользователей и прав доступа User Table clients auto rent turn auto models DB_admin suid suid suid suid suid admin s suid suid suid suid manager suid s s s sid worker s su s sui sid Реализация проекта базы данных

Мы условились не привязываться к конкретной СУБД и выполнять описание логической схемы БД на MySQL 5.1. Список литературы 1. Проектирование реляционных баз данных: Метод. указания к курсовому проектированию по курсу “Базы данных”/ Московский государственный институт электроники и математики; Сост.: И.П. Карпова. – М 2003. – 31 с. 2. Введение в базы данных.

Учебное пособие. – Московский Государственный институт электроники и математики. – М 2003. – 75 с. 3. Изучение основ языка SQL: Методические указания к лабораторным работам по курсу “Базы данных”/ Московский государственный ин-т электроники и математики; Сост.: И.П. Карпова. – М 2003. – 31 с. 4. Лекции по дисциплине “Базы данных”, Карпова И.П. 5. http://www.bilnik.ru/tr/park 6. http://www.ele-ment.ru/