База данных страховой компании (курсовая)

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

Московский институт электроники и математики

Национального исследовательского университета

«ВЫСШАЯ ШКОЛА ЭКОНОМИКИ»

Кафедра «Вычислительные Системы и Сети»

ОТЧЕТ

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

по курсу «Базы данных»

Тема: «БД Страховой компании»

Исполнитель:

студентка группы С-53

Голенок Е.А.

Руководитель:

Карпова И.П.

Москва, 2013

Содержание работы

1. Инфологическое проектирование

1.1 Анализ Предметной области

1.2 Анализ информационных задач и круга пользователей системы

2. Определение требований к операционной обстановке
3. Выбор СУБД и других программных средств
4. Логическое проектирование БД

4.1 Преобразование ER-диаграммы в базу данных

4.2 Схема реляционной базы данных

4. 3 Составление реляционных отношений

4.4 Нормализация полученных отношений (до 4НФ)

4.5 Схема реляционной базы данных

4.6 Дополнительные ограничения целостности

4.7 Схема реляционной базы данных

5. Реализация проекта базы данных

5.1 Создание таблиц

5.2 Создание представлений, готовых запросов

5.3 Назначение прав доступа

5.4 Создание индексов

5.5 Разработка стратегии резервного копирования

Список литературы

1. Инфологическое проектирование

1.1 Анализ Предметной области

– Страховщик – один – это Страховая компания (юридическое лицо) может заключать несколько разных типов договоров Страхования со Страхователями (их может быть несколько)

– В случае заключения Договора страхования Страховщик должен оформить Полис;

– Каждый Страхователь может заключить Договор на нескольких человек (Застрахованных), Страхователей может быть несколько на 1 застрахованного и наоборот (если выбраны разные виды Страхования), но на 1 полис – 1 Страхователь;

– Каждый Застрахованный вправе назначить Выгодоприобретателей (например, в случае смерти Застрахованного вся страховая сумма (или %) достается определенному Выгодоприобретателю, им же может служить Страхователь);

– Различные полисы включают в себя Различные страховые суммы и выплачиваются Застрахованному в случае, описанном в Договоре Страхования;

Для создания ER-диаграммы необходимо выделить сущности предметной области:

• Страхователь: Атрибуты: ФИО Страхователя, Серия, номер паспорта, кем и когда выдан, код подразделения, дата рождения, профессия, место работы, контактные данные, Адрес Фактический, Адрес Регистрации, наличие судимостей, серия, номер Полиса;
• Страховщик: Атрибуты: Наименование юр. лица, ИНН, БИК, к/c, КПП, Адрес;
• Полис: Атрибуты: Дата оформления, серия, номер полиса, срок действия (начало и конец срока действия), вид страхования жизни, Статус (выплачена компенсация, пока не пригодился), ФИО представителя Страховой Компании, заключающего договор страхования и должность сотрудника;
• Застрахованный: Атрибуты: ФИО застрахованного лица, Серия, номер паспорта, кем и когда выдан, код подразделения, дата рождения, профессия, место работы, контактные данные, Адрес Фактический, Адрес Регистрации, наличие судимостей, серия, номер Полиса;
• Выгодоприобретатель: тип (по дожитию, по смерти), ФИО, Серия, номер паспорта, кем и когда выдан, код подразделения, дата рождения, профессия, место работы, контактные данные, Адрес Фактический, Адрес Регистрации, наличие судимостей, доля страховых выплат, Серия, номер полиса;
• Страховые расчеты: Атрибуты: Страховая сумма, Страховой взнос, Страховые выплаты в случае смерти, инвалидности, болезни, несчастных случаев, серия, номер полиса, которому принадлежат расчеты (в данном случае клад может внести любой, а выплаты получить – застрахованный или выгодоприобретатель);

1.2 Анализ информационных задач и круга пользователей системы

Определим основные группы пользователей, их основные задачи и запросы к БД

1 Должностные лица СК:

Оформление нового договора

Получение сведений о текущих полисах

Продление или расторжение договора (изменение срока действия в полисе)

Получение сведений об оформленных полисах за определенный срок

2 Начальство СК:

Получение сведений о текущих полисах

Получение сведений об оформленных полисах за определенный срок

Статистика финансовых расчетов

3 Страхователь

Получение сведений об оформленном полисе

4 Застрахованный, Выгодоприобретатель

Получение сведений об оформленном полисе

5 Бухгалтерия СК:

Просмотр текущих Полисов

Внесение изменений в таблицу страховых расчетов

Статистика финансовых расчетов

6 Администратор БД

Все права

2. Определение требований к операционной обстановке

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

Для реальных баз данных обычно наиболее существенным является МД. На основе результатов анализа ПрО можно приблизительно оценить объём памяти, требуемой для хранения данных.

Примем ориентировочно, что:

• одновременно действуют около сотни полисов, действие полиса продолжается в среднем 2 года (по 1К на каждый полис);
• оформление каждого полиса состоит в среднем из трех этапов (Заключение договора, Оформление полиса, Расчет страховых выплат) (по 0,5К на этап);
• в компании работают 50 сотрудников (по 0,5К на каждого сотрудника);
• в оформлении полиса в среднем участвуют 3 человека (по 0,2К);
• устаревшие данные переводятся в архив (накапливаются в архиве БД).

Тогда объём памяти для хранения данных за первый год примерно составит:

Mд= 2(100*1+10*3*0,5+50*0,5+(100*3*0,2))= 400К,

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

3. Выбор СУБД и других программных средств

Анализ информационных задач показывает, что для реализации требуемых функций подходят почти все СУБД для ПЭВМ (MS Access, Firebird, MySQL и др.). Все они поддерживают реляционную модель данных и предоставляют разнообразные возможности для работы с данными.

В качества СУБД я выбрала MySQL 6.0. MySQL – это одна из самых популярных и самых распространенных СУБД в интернете. Она не предназначена для работы с большими объемами информации, но ее применение идеально подходит для интернет сайтов, как небольших, так и достаточно крупных.

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

Немаловажным фактором является ее бесплатность. MySQL распространяется на условиях общей лицензии GNU (GPL, GNU Public License).

4. Логическое проектирование БД

4.1 Немного о преобразовании ER-диаграммы в схему БД

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

4.2 Схема реляционной базы данных

полученная из ER–диаграммы:

4.3 Составление реляционных отношений

Таблица Страховщик – InsurerOrg

Таблица Страхователь – Insurer

база данных страховой компания

Полис – Policy

Вложения – put_money

Выплаты – Payment

Застрахованный – Insured

Выгодоприобретатель – Beneficiary

4.4 Нормализация полученных отношений (до 4НФ)

1НФ.

Для приведения таблиц к 1НФ требуется составить прямоугольные таблицы (одно значение атрибута – одна ячейка таблицы) и разбить сложные атрибуты на простые.

Разделим атрибут Адрес (регистрации и фактический) на атрибуты: индекс, область, район, город (или поселение, деревня), дом, строение, квартира;

Разделим атрибут Место рождения на атрибуты: индекс, область, район, город (или поселение, деревня);

Разделим атрибут Работа на атрибуты: город, компания, название офиса;

Разделим атрибут ФИО должностного лица, Застрахованного, Страхователя, Выгодоприобретателя на: Фамилия, Имя, Отчество;

Разделим атрибут Должность представителя СК на: Номер подразделения, название должности, рабочий кабинет;

2НФ.

В нашем случае составные первичные ключи отсутствуют.

3НФ.

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

4НФ.

Отношения Страховщик, Застрахованный, Страховые расчеты, Страхователь, Выгодоприобретатель, Вложения, Выплаты, Доля выплат не нарушают 4НФ.

Отношение Полис содержит многозначную зависимость – Представитель СК, Должность представителя СК от ID полиса (серии и номера), так одним полисом могут заниматься несколько человек (оформление, бухгалтерия и так далее).

В результате выделения должности сотрудника – в новой таблице появится транзитивная зависимость Полис =>Представитель СК =>Должность представителя СК. В данном случае вернемся к 3НФ и разобьем таблицу Представителей СК на 2: Представитель СК, Сотрудники

Получили:

Таблица Страховщик – InsurerOrg

Таблица Страхователь – Insurer

Полис – Policy

Представитель СК – Work_policy

Сотрудники СК – worker_SK

Вложения – put_money

Выплаты – Payment

Застрахованный – Insured

Выгодоприобретатель – Beneficiary

4.5 Схема реляционной базы данных

полученная в результате нормализации отношений:

4.6 Определение дополнительных ограничений целостности

Перечислим ограничения целостности, которые не указаны в таблицах отношений:

Дата рождения людей > 1950 года

Дата оформления = текущий день

Начало действия полиса >= Текущий день + 1

Конец действия полиса >=Текущий день +6

4.7 Описание групп пользователей и прав доступа

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

s – чтение данных (select);

i – добавление данных (insert);

u – модификация данных (update);

d – удаление данных(delete).

5. Реализация проекта базы данных

5.1 Создание таблиц

1 Отношение Страховщик

CREATE TABLE InsurerOrg

(

Name varchar(30) not null,

INN varchar(12) not null,

BIK char(9) primary key,

Ks varchar(20) not null,

Kpp char(9) not null,

Schet varchar(20) not null,

Index_ad char(6) not null,

Area_ad varchar(20) not null,

Town_ad varchar(20) not null,

House_ad varchar(10) not null,

Building_ad varchar(10) not null default(’empty’),

Flat_ad varchar(5) not null default(’empty’)

);

2 Отношение Страхователь

CREATE TABLE Insurer

(

Surname varchar(15) not null,

FirstName varchar(15) not null,

Patronomic varchar(15) not null,

Pass char(10) primary key,

WhoPass varchar(50) not null,

Birthdate date not null check (Birthdate>’1949-12-31′),

Index_Birth char(6) not null,

Area_Birth varchar(20) not null,

Town_Birth varchar(20) not null,

Prof varchar(20) not null,

Sex char(1) not null check (sex IN(‘w’,’m’)),

WorkCurr varchar(100) not null,

Phone varchar(20) not null,

Mail varchar(30),

Index_reg char(6) not null,

Area_reg varchar(20) not null,

Town_reg varchar(20) not null,

House_reg varchar(10) not null,

Building_reg varchar(10) not null default(’empty’),

Flat_reg varchar(5) not null default(’empty’),

Index_fact char(6) not null,

Area_fact varchar(20) not null,

Town_fact varchar(20) not null,

House_fact varchar(10) not null,

Building_fact varchar(10) not null default(’empty’),

Flat_fact varchar(5) not null default(’empty’),

Conviction char(3) not null check (Conviction IN(‘yes’.’ no’))

);

3 Отношение Полисы

CREATE TABLE Policy

(

NumberSer char(25) primary key,

DateAcceptation Date not null default curdate(),

EffectiveDate Date not null check (EffectiveDate > curdate()),

ExpirationDate Date not null check (ExpirationDate > (curdate() + 5)),

Stat varchar(20) not null check (Stat IN(‘payments used’, payment until useful)),

TypeIns varchar(10) not null check (TypeIns IN(‘travel’, ‘life’, ‘student’ ‘home’)),

BIK_SK char(9) references IsurerOrg,

PassR char(10) references Insurer on UPDATE cascade,

PassD char(10) references Insured on UPDATE cascade,

SumPolicy float(20) unsigned not null

);

4 Отношение Представитель СК

CREATE TABLE Work_policy

(

Id char(16) references worker_SK,

SerNum char(25) references Policy

);

5 Отношение Сотрудники СК

CREATE TABLE worker_SK

(

Surname varchar(15) not null,

FirstName varchar(15) not null,

Patronimic varchar (15) not null,

Work_dep varchar(30) not null,

Id char(16) Primary Key

);

6 Отношение Вложения

CREATE TABLE put_money

(

Fee Float(20) UNSIGNED not null,

NumberSer char(25) references Policy

);

7 Отношение Выплаты

CREATE TABLE Payment

(

NumberSer char(25) references Policy,

WhoPay char(1) default(‘I’) check (WhoPay IN ‘I’,’B’),

PaymentDeath float(20) unsigned not null,

PaymentSurv float(20) unsigned not null

);

8 Отношение Застрахованный

CREATE TABLE Insured

(

Surname varchar(15) not null,

FirstName varchar(15) not null,

Patronomic varchar(15) not null,

Pass char(10) primary key,

WhoPass varchar(50) not null,

Birthdate date not null check (Birthdate>’1949-12-31′),

Index_Birth char(6) not null,

Area_Birth varchar(20) not null,

Town_Birth varchar(20) not null,

Prof varchar(20) not null,

Sex char(1) not null check (sex IN(‘w’,’m’)),

WorkCurr varchar(100) not null,

Phone varchar(20) not null,

Mail varchar(30),

Index_reg char(6) not null,

Area_reg varchar(20) not null,

Town_reg varchar(20) not null,

House_reg varchar(10) not null,

Building_reg varchar(10) not null default(’empty’),

Flat_reg varchar(5) not null default(’empty’),

Index_fact char(6) not null,

Area_fact varchar(20) not null,

Town_fact varchar(20) not null,

House_fact varchar(10) not null,

Building_fact varchar(10) not null default(’empty’),

Flat_fact varchar(5) not null default(’empty’),

Conviction char(3) not null check (Conviction IN(‘yes’.’ no’))

);

9 Отношение Выгодоприобретатель

CREATE TABLE Beneficiary

(

Surname varchar(15) not null,

FirstName varchar(15) not null,

Patronomic varchar(15) not null,

Pass char(10) primary key,

WhoPass varchar(50) not null,

Birthdate date not null check (Birthdate>’1949-12-31′),

Index_Birth char(6) not null,

Area_Birth varchar(20) not null,

Town_Birth varchar(20) not null,

Prof varchar(20) not null,

Sex char(1) not null check (sex IN(‘w’,’m’)),

WorkCurr varchar(100) not null,

Phone varchar(20) not null,

Mail varchar(30),

Index_reg char(6) not null,

Area_reg varchar(20) not null,

Town_reg varchar(20) not null,

House_reg varchar(10) not null,

Building_reg varchar(10) not null default(’empty’),

Flat_reg varchar(5) not null default(’empty’),

Index_fact char(6) not null,

Area_fact varchar(20) not null,

Town_fact varchar(20) not null,

House_fact varchar(10) not null,

Building_fact varchar(10) not null default(’empty’),

Flat_fact varchar(5) not null default(’empty’),

Conviction char(3) not null check (Conviction IN(‘yes’.’ no’)),

Type_Ben varchar(5) not null check (Type_Ben IN (‘Surv’, ‘Death’)),

percent float(6) unsigned not null,

NumberSer char(25) references Policy

);

5.2 Создание представлений, готовых запросов

1 Список действующих полисов:

Create view curr_policy as

Select *

From Policy

Where (EffectiveDate<=curdate()) and (ExpirationDate>=curdate());

2 Список оформленных полисов за последние 30 дней:

Create view Days_policy as

Select *

From Policy

Where (DateAcceptation <=curdate()) and (DateAcceptation >=curdate()-30);

3 Просмотр всех страховых взносов, внесенных по полисам за последние 30 дней:

Create view Days_Fee as

Select p.Fee, i.Surname, i.FirstName, i.Patronimic, pol. DateAcceptation

From put_money p, Policy pol

Where ((pol.DateAcceptation <=curdate()) and (pol.DateAcceptation >=curdate()-30)) and (p.NumberSer=pol.NumberSer) ;

4 Просмотр всех страховых выплат, рассчитанных за последние 30 дней:

Create view Days_Pay as

Select p. PaymentDeath, p. PaymentSurv, i.Surname, .FirstName, i.Patronimic, pol. DateAcceptation

From Payment p, Insured i, Policy pol

Where ((pol.DateAcceptation <=curdate()) and (pol.DateAcceptation >=curdate()-30)) and (p.NumberSer=pol.NumberSer) and (pol.PassD=i.Pass);

5 Список действующих полисов, по которым уже произведены выплаты:

Create view Pays_policy as

Select *

From Policy

Where Stat= ‘payments used’ and (EffectiveDate<=curdate()) and (ExpirationDate>=curdate());

6 Сколько полисов оформили сотрудники за 30 дней:

Create view Num_Policy (Name, department, NumPol) as

Select w.Surname + ‘ ‘ + w.FirstName + ‘ ‘ + w.Patronimic, dep.Work_dep,

count(case when p.NumberSer=dep.SerNum then 1 else null end) Pol

From Work_policy dep, worker_SK w, Policy p

Where (w.id =dep.id)

Group by dep.Work_dep;

7 Просмотр своего полиса (Страхователь, Застрахованный, Выгодоприобретатель)

Create view My_Policy as

Select p.*

From Policy p, Insurer r, Insured d, Beneficiary b

Where ((r.Pass=user) and (p.PassR=r.Pass)) or( (d.Pass=user) and (p.PassD=d.Pass)) ) or ((b.Pass=user) and (b. NumberSer=p. NumberSer) );

8 Список полисов, по которым уплачены взносы № (действующих):

Create view Sum_FeeYes as

Select p.NumberSer, p.SumPolicy, Sum(put.Fee)

From Policy p, put_money put

Where ((p.NumberSer=put.NumberSer) and (Sum(put.Fee)=p.SumPolicy)) and ((EffectiveDate<=curdate()) and (ExpirationDate>=curdate()));

9 Список полисов, по которым НЕ уплачены взносы № (действующих):

Create view Sum_FeeNo as

Select p.NumberSer, p.SumPolicy, Sum(put.Fee)

From Policy p, put_money put

Where ((p.NumberSer=put.NumberSer) and (Sum(put.Fee)<p.SumPolicy)) and ((EffectiveDate<=curdate()) and (ExpirationDate>=curdate()));

5.3 Назначение прав доступа

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

s – чтение данных (select);

i – добавление данных (insert);

u – модификация данных (update);

d – удаление данных(delete).

для администратора:

• grant select, insert, update, delete on InsurerOrg to admin;
• grant select, insert, update, delete on Insurer to admin;
• grant select, insert, update, delete on worker_SK to admin;
• grant select, insert, update, delete on put_money to admin;
• grant select, insert, update, delete on Payment to admin;
• grant select, insert, update, delete on percent to admin;
• grant select, insert, update, delete on Insured to admin;
• grant select, insert, update, delete on Work_policy to admin;
• grant select, insert, update, delete on policy to admin;
• grant select, insert, update, delete on Beneficiary to admin;
• grant select on Curr_Policy to admin;
• grant select on Days_Policy to admin;
• grant select on Days_Fee to admin;
• grant select on Days_Pay to admin;
• grant select on Num_Policy to admin;
• grant select on My_Policy to admin;
• grant select on Pays_Policy to admin;
• grant select on Sum_FeeYes to admin;
• grant select on Sum_FeeNo to admin;

для Должностных лиц СК:

• grant select on InsurerOrg to WorkSK;
• grant select, insert, update, delete on Insurer to WorkSK;
• grant select on worker_SK to WorkSK;
• grant select on put_money to WorkSK;
• grant select on Payment to WorkSK;
• grant select on percent to WorkSK;
• grant select, insert, update, delete on Insured to WorkSK;
• grant select, insert, update on Work_policy to WorkSK;
• grant select, insert, update, delete on policy to WorkSK;
• grant select, insert, update, delete on Beneficiary to WorkSK;
• grant select, insert, update, delete on Curr_Policy to WorkSK;
• grant select on Days_Policy to WorkSK;
• grant select on Days_Fee to WorkSK;
• grant select on Days_Pay to WorkSK;
• grant select on Num_Policy to WorkSK;
• grant select on My_Policy to WorkSK;
• grant select on Pays_Policy to WorkSK;
• grant select on Sum_FeeYes to WorkSK;
• grant select on Sum_FeeNo to WorkSK;

для Начальства СК:

• grant select, insert, update, delete on InsurerOrg to Director;
• grant select on Insurer to Director;
• grant grant select, insert, update, delete on worker_SK to Director;
• grant select on put_money to Director;
• grant select on Payment to Director;
• grant select on percent to Director;
• grant select, on Insured to Director;
• grant select, insert, update, delete on Work_policy to Director;
• grant select on policy to Director;
• grant select on Beneficiary to Director;
• grant select on Curr_Policy to Director;
• grant select on Days_Policy to Director;
• grant select on Days_Fee to Director;
• grant select on Days_Pay to Director;
• grant select on Num_Policy to Director;
• grant select on My_Policy to Director;
• grant select on Pays_Policy to Director;
• grant select on Sum_FeeYes to Director;
• grant select on Sum_FeeNo to Director;

для Страхователя, Застрахованного, Выгодоприобретатель:

• grant select on My_Policy to InsurerPerson, InsuredPerson, BenPerson;
• grant select on InsurerOrg to PaySK;

для Бухгалтерии СК:

• grant select on InsurerOrg to PaySK;
• grant select on Insurer to PaySK;
• grant select on worker_SK to PaySK;
• grant select, insert, update, delete on put_money to PaySK;
• grant select, insert, update, delete on Payment to PaySK;
• grant select, insert, update, delete on percent to PaySK;
• grant select on Insured to PaySK;
• grant select on Work_policy to PaySK;
• grant select on policy to PaySK;
• grant select on Beneficiary to PaySK;
• grant select on Curr_Policy to PaySK;
• grant select on Days_Policy to PaySK;
• grant select on Days_Fee to PaySK;
• grant select on Days_Pay to PaySK;
• grant select on Num_Policy to PaySK
• grant select on My_Policy to PaySK;
• grant select on Pays_Policy to PaySK;
• grant select on Sum_FeeYes to PaySK;
• grant select on Sum_FeeNo to PaySK;

5.4 Создание индексов

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

Create index Insurer_Pass on Policy (PassR);

Create index Insured_Pass on Policy (PassD);

Create index NumberSer1 on put_money (NumberSer);

Create index NumberSer2 on Payment (NumberSer);

Create index NumberSer3 on Beneficiary (NuberSer);

Create index NumberSer4 on Work_policy (SerNum);

5.5 Разработка стратегии резервного копирования

Интенсивность обновления базы данных – средняя, поэтому для обеспечения сохранности вполне достаточно производить инкрементное копирование раз в 4 часа и полное резервное копирование раз в неделю (в конце рабочей недели). Для разработанной БД есть необходимость держать сервер включенным круглосуточно, поэтому необходимо иметь «запасной сервер», где будут храниться резервные копии. Перезагружать основной сервер необходимо 1 раз в 2 недели, для обновления ПО, также перед этим необходимо делать резервную копию всей БД.

Список литературы

1. Карпова И.П. «Новые методические указания по лабораторным работам для студентов МИЭМ»
2. Карпова И.П. «Методические указания к курсовому проектированию»
3. http://www.spravkaweb.ru/mysql «Справочник по MySql»

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

Нашли опечатку? Выделите и нажмите CTRL+Enter