Телефонная компания

Телефонная компания

КУРСОВОЕ ПРОЕКТИРОВАНИЕ ПО БАЗАМ ДАННЫХ

ТЕМА: "Телефонная компания"

Одесса 2010

Аннотация

Объектом исследования является развитие навыков проектирования информационных систем и закрепление знаний, полученных за время изучения дисциплины "Базы данных".

Цель работы - разработка и создание базы данных для предметной области Аварийная служба.

Метод исследования - изучение видов деятельности в служба рекламы, составление и отладка программы, для работы с базой данных (программа написана на языке С++).

Содержание

Введение

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

1.1 Рабочие цели системы

1.2 Этапы проектирования БД

1.3 Концептуальная модель

2. Преобразование модели ER в реляционную модель

3. Запросы к базе данных системы

4. Описание групп пользователей ИС

5. Текст программы

Заключение

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

Введение

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

Задание: предметная область "Реклама в коммерческом издании". Возможные виды деятельности: учет клиентов с возможными льготами; учет совершенных ими заказов по различным направлениям; расчет стоимости заказа с учетом типа, даты продолжительности; оплата клиентами услуг.

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

1.1 Рабочие цели системы

Согласно заданию, программный продукт должен фиксировать такие данные как:

- клиентов с возможными льготами;

- заказ клиентов по различным типам;

- стоимость заказа с учетом типа, даты и продолжительности;

- оплата клиентом услуг.

В связи с этим были сформированы следующие таблицы в базе данных:

Клиенты;

Услуги;

Звонки;

Льготы.

А также таблицы, связывающие данные таблицы.

Так же ПП должен выполнять следующие функции:

Вывод списка услуг клиента;

Вывод списка заказов клиента;

Вывод списка клиентов по первым буквам;

Вывод списка заказов за данный промежуток;

Вывод списка заказов в данный промежуток;

Вывод количества клиентов с данным типом услуги;

Вывод количества неоплаченых счетов;

Вывод количества клиентов по услугам;

Вывод количества клиентов по льготам;

Вывод клиента с наибольшим долгом;

Для каждой льготы определить клиента с наименьшим сроком льготы;

Для каждой услуги определить а клиента с наименьшим сроком услуги;

Вывод клиентов, не имеющих льгот;

Вывод клиентов, не имеющих услуг;

Вывод состояний счетов;

Вывод состояний сроков услуг;

Обновить наличие долга;

Обновить отсутствие долга.

1.2 Этапы проектирования БД

После анализа предметной области были сформированы ранее перечисленные таблицы.

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

В таблице "Услуги" и "Льготы" были также выбраны первичные ключи "Идентификатор", т.к. данных полей достаточно для уникального определения данных в таблицах.

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

1.3 Концептуальная модель

2. Преобразование модели ER в реляционную модель

3. Запросы к базе данных системы

Вывод списка услуг клиента

SELECT A.id_service, A.opisanie, C.surname FROM services AS A, ys_ab AS B, Klient AS C WHERE A.id_service=B.id_service and B.id_klient=C.id_klient and C.surname=(Фамилия_Пользователя)

Вывод списка звонков клиента

SELECT A.id_call, A.data, A.time_beg, A.time_and, C.opisanie, B.surname FROM Call AS A, Klient AS B, Napravlenie AS C WHERE A.id_klient=B.id_klient And A.id_napravlenie=C.id_napravlenie And B.surname=(Фамилия_Пользователя)

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

SELECT * FROM Klient WHERE surname like Первые_буквы%

Вывод списка клиентов по первым цифрам

SELECT * FROM Klient WHERE num like Первые_цифры%

Вывод списка звонков за данный промежуток

SELECT * FROM call WHERE data Between Начальная_Дата and Конечная_Дата

Вывод списка услуг в данный промежуток

SELECT A.Data, A.Srok, B.Surname FROM ys_ab AS A, Klient AS B WHERE A.id_klient=B.id_klient and A.Data between Начальная_Дата and Конечная_Дата

Вывод количества клиентов с данной услугой

SELECT Count(*) FROM services WHERE services.id_service=Идентификато_услуги

Вывод количества отрицательных счетов

SELECT count(*) FROM Klient WHERE bill<0;

Вывод количества клиентов по услугам

SELECT A.id_service, B.opisanie, Count(*) FROM ys_ab AS A, services AS B WHERE A.id_service=B.id_service GROUP BY A.id_service, B.opisanie;

Вывод количества клиентов по льготам

SELECT A.id_lgot, B.opisanie, Count(*)FROM lg_ab AS A, lgot AS B WHERE A.id_lgot=B.id_lgot GROUP BY A.id_lgot, B.opisanie;

Вывод клиента с наибольшим долгом

SELECT * FROM klient AS A WHERE bill<= ALL (select bill from klient );

Вывод клиентов, имеющих услуги

SELECT * FROM klient AS A WHERE A.id_klient =ANY (select B.id_klient from ys_ab AS B);

Для каждой льготы определить клиента с наименьшим сроком льготы

SELECT A.opisanie, B.surname, B.ab_name FROM lgot AS A, klient AS B, lg_ab AS C WHERE A.id_lgot=C.id_lgot AND B.id_klient=c.id_klient AND C.srok<=All (select Srok from lg_ab where lg_ab.id_lgot=A.id_lgot);

Для каждой услуги определить клиента с наименьшим сроком услуги

SELECT A.opisanie, B.surname, B.ab_name FROM services AS A, klient AS B, ys_ab AS C WHERE A.id_service=C.id_service AND B.id_klient=c.id_klient AND C.srok<=All (select Srok from ys_ab where ys_ab.id_service=A.id_service);

Вывод клиентов, не имеющих льгот

SELECT A.surname FROM Klient AS A WHERE A.id_klient not IN (select id_klient from lg_ab);

Вывод клиентов, не имеющих услуг

SELECT A.surname FROM Klient AS A WHERE A.id_klient not IN (select id_klient from ys_ab);

Вывод состояний счетов

SELECT id_klient,surname,ab_name,'norm' from Klient where bill>0 UNION SELECT id_klient,surname,ab_name,'minus' from Klient where bill<0;

Вывод состояний сроков услуг

SELECT *,Srok-Now() FROM ys_ab where (Srok-Now())>0 union SELECT *,Srok-Now() FROM ys_ab where (Srok-Now())<0;

Обновить наличие долга

update klient set [More info]='imeet dolg' where id_klient in (select id_klient from klient where bill<0);

Обновить отсутствие долга

update klient set [More info]='ne imeet dolg' where id_klient in (select id_klient from klient where bill>0);

4. Описание групп пользователей ИС

В данной области были выделены две группы пользователей.

Первая группа - менеджер-оператор:

- возможность просмотра таблиц "Клиенты", "Рекламы", "Льготы-Клиенты", "Услуги-Клиенты", "Оплаты".

- возможность выполнения всех перечисленных в анализе требований действий.

- возможность внесения оплаты.

Вторая группа - администратор:

- возможность просмотра таблиц "Клиенты", "Услуги", "Льготы-Клиенты", "Услуги-Клиенты", "Оплаты".

- возможность выполнения всех перечисленных в анализе требований действий.

- возможность внесения оплаты.

- возможность просмотра журнала событий, произошедших в системе.

- возможность изменения всех таблиц (кроме журнала событий):

-редактирование записей.

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

- удаление записей.

- возможность создания нового пользователя.

Интерфейс ИС

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

1. Первоначальное окно программы:

2. Окно программы, которое видит менеджер-оператор после ввода своего логина и пароля:

3. Окно программы, которое видит менеджер-оператор после нажатия кнопки "Таблицы":

Окно программы, которое видит менеджер-оператор после нажатия кнопки "Функции":

4. Окно программы, которое видит менеджер-оператор после нажатия кнопки "Оплата":

При этом, пользователь вводит номер в поле ввода номера и автоматически вписывается фамилия в поле фамилии, которою в последствии пользователь должен согласовать с клиентом.

5. Окно программы, которое видит менеджер-оператор после выбора пункта в меню "Таблицы":

6. Окно программы, которое видит менеджер-оператор после выбора пункта в меню "Функции":

7. Окно программы, которое видит администратор после ввода своего логина и пароля:

8. Окно программы, которое видит администратор после нажатия кнопки "Изменить Клиент-Льгота":

9. Окно программы, которое видит администратор после нажатия кнопки "Добавить запись":

10. Окно программы, которое видит администратор после нажатия кнопки "Добавить льготу":

11. Окно программы, которое видит администратор после нажатия кнопки "Изменить Клиент-Услуга":

12. Окно программы, которое видит администратор после нажатия кнопки "Добавить запись":

13. Окно программы, которое видит администратор после нажатия кнопки "Добавить услугу":

Сообщения программы, причины, их вызывающие, и реакция пользователя на сообщения

ь Сообщение о неверном имени пользователя или пароля.

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

ь Сообщение о неверном внесении оплаты.

При возникновении данной ошибки пользователю выводится сообщение о возникновении данной ситуации, очищаются все поля ввода. Требуется ввести верные данные.

ь Сообщение о неверном внесении нового пользователя.

При возникновении данной ошибки пользователю выводится сообщение о возникновении данной ситуации, очищаются все поля ввода. Требуется ввести верные данные.

5. Тексты программы

#pragma once

int zap=0;//переменная для определения текущего запроса

int id=0;//переменная для определения текущего пользователя в системе

int level=0;//переменная для определения выдаваемых функций пользователю

int op_new=-1;//переменная для определения действий кнопки "добавить"

int ChoiseToLV2=0;//переменная, определяющая выводимую и вводимую информацию в функциях.

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

private: System::Void listBox1_SelectedIndexChanged(System::Object^ sender, System::EventArgs^ e) {

this->listView1->Visible=true;

this->ClientSize = System::Drawing::Size(960, 580);

this->listView1->Items->Clear();

cn->Open();

int choise=this->listBox1->SelectedIndex;

if(choise==0)

{

String ^q="Select * from Klient;";

System::Data::OleDb::OleDbCommand^cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

System::Data::OleDb::OleDbDataReader ^rdr=cmd->ExecuteReader();

int k=0;

while(rdr->Read())

{

this->columnHeader1->Text=L"ид";

this->listView1->Items->Add(Convert::ToString(rdr[0]));

this->columnHeader2->Text=L"Фамилия";

this->listView1->Items[k]->SubItems->Add(Convert::ToString(rdr[1]));

this->columnHeader3->Text=L"Имя";

this->listView1->Items[k]->SubItems->Add(Convert::ToString(rdr[2]));

this->columnHeader4->Text=L"Номер";

this->listView1->Items[k]->SubItems->Add(Convert::ToString(rdr[3]));

this->columnHeader5->Text=L"Счет";

this->listView1->Items[k]->SubItems->Add(Convert::ToString(rdr[4]));

this->columnHeader6->Text=L"Доп.инф.";

this->listView1->Items[k]->SubItems->Add(Convert::ToString(rdr[5]));

this->columnHeader7->Text=L"";

k++;

}

rdr->Close();

cn->Close();}}

//действия при нажатии на кнопку "Внести" в меню "Оплата".

private: System::Void button11_Click(System::Object^ sender, System::EventArgs^ e) {

String ^q;

System::Data::OleDb::OleDbCommand ^cmd;

System::Data::OleDb::OleDbDataReader ^rdr;

cn->Open();

if(op_new==1)

{

double summ_ins=Convert::ToDouble(this->textBox8->Text);

double summ;

int chosen_id;

q="Select * from klient";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

while(rdr->Read())

{

if(Convert::ToString(rdr[1])==this->comboBox1->Text && Convert::ToInt32(rdr[3])==Convert::ToInt32(this->comboBox2->Text))

{

chosen_id=Convert::ToInt32(rdr[0]);

summ=summ_ins+Convert::ToDouble(rdr[4]);

q="update klient set bill ='"+Convert::ToString(summ)+"' where id_klient="+chosen_id+";";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

q="INSERT INTO Oplata ( Data, summ, id_klient )values (Date(),'"+Convert::ToString(summ_ins)+"','"+chosen_id+"'); ";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

}

}

rdr->Close();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Внесение суммы "+Convert::ToString(summ_ins)+" на счет клиента с ID "+chosen_id+"');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

}

//действия при нажатии на кнопку "Изменить"

private: System::Void button13_Click(System::Object^ sender, System::EventArgs^ e) {

cn->Open();

if(ChoiseToLV2==1)

{

String ^q="update lg_ab SET Data='"+this->textBox10->Text+"',Srok='"+this->textBox11->Text+"',id_lgot="+Convert::ToInt32(this->textBox12->Text)+",id_klient="+Convert::ToInt32(this->textBox13->Text)+" where id_lg_ab="+Convert::ToInt32(this->textBox9->Text);

System::Data::OleDb::OleDbCommand ^cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

System::Data::OleDb::OleDbDataReader ^rdr=cmd->ExecuteReader();

rdr->Close();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Изменение записи в таблице клиент-льгота с ID "+Convert::ToInt32(this->textBox9->Text)+"');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

if(ChoiseToLV2==4)

{

String ^q="update ys_ab SET Data='"+this->textBox10->Text+"',Srok='"+this->textBox11->Text+"',id_service="+Convert::ToInt32(this->textBox12->Text)+",id_klient="+Convert::ToInt32(this->textBox13->Text)+" where id_ys_ab="+Convert::ToInt32(this->textBox9->Text);

System::Data::OleDb::OleDbCommand ^cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

System::Data::OleDb::OleDbDataReader ^rdr=cmd->ExecuteReader();

rdr->Close();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Изменение записи в таблице клиент-услуга с ID "+Convert::ToInt32(this->textBox9->Text)+"');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

cn->Close();

}

//действия при нажатии на кнопку "Удалить"

private: System::Void button14_Click(System::Object^ sender, System::EventArgs^ e) {

cn->Open();

System::Data::OleDb::OleDbCommand ^cmd;

System::Data::OleDb::OleDbDataReader ^rdr;

if(ChoiseToLV2==1)

{

String ^q="delete from lg_ab where id_lg_ab="+Convert::ToInt32(this->textBox9->Text);

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Удаление записи в таблице клиент-льгота с ID "+Convert::ToInt32(this->textBox9->Text)+"');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

if(ChoiseToLV2==2)

{

String ^q="delete from lgot where id_lgot="+Convert::ToInt32(this->textBox9->Text);

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Удаление записи в таблице льгота с ID "+Convert::ToInt32(this->textBox9->Text)+"');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

if(ChoiseToLV2==3)

{

String ^q="delete from Klient where id_klient="+Convert::ToInt32(this->textBox9->Text);

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Удаление записи в таблице Клиент с ID "+Convert::ToInt32(this->textBox9->Text)+"');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

if(ChoiseToLV2==4)

{

String ^q="delete from ys_ab where id_ys_ab="+Convert::ToInt32(this->textBox9->Text);

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Удаление записи в таблице клиент-услуга с ID "+Convert::ToInt32(this->textBox9->Text)+"');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

if(ChoiseToLV2==5)

{

String ^q="delete from services where id_service="+Convert::ToInt32(this->textBox9->Text);

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Удаление записи в таблице услуга с ID "+Convert::ToInt32(this->textBox9->Text)+"');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

rdr->Close();

cn->Close();

}

//Действия при нажатии на кнопку "Добавить"

private: System::Void button16_Click(System::Object^ sender, System::EventArgs^ e) {

cn->Open();

System::Data::OleDb::OleDbCommand ^cmd;

System::Data::OleDb::OleDbDataReader ^rdr;

if(ChoiseToLV2==1)

{

String ^q="insert into lg_ab (Data,Srok,id_lgot,id_klient) VALUES('"+this->textBox10->Text+"','"+this->textBox11->Text+"',"+Convert::ToInt32(this->textBox12->Text)+","+Convert::ToInt32(this->textBox13->Text)+")";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Добавление записи в таблицу клиент-льгота');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

if(ChoiseToLV2==2)

{

String ^q="insert into Lgot (Opisanie,Stoimost)VALUES('"+this->textBox10->Text+"',"+Convert::ToInt32(this->textBox11->Text)+")";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Добавление записи в таблицу Льготы');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

if(ChoiseToLV2==3)

{

String ^q="insert into Klient (Surname,ab_name,Num,bill)VALUES('"+this->textBox10->Text+"','"+this->textBox11->Text+"',"+Convert::ToInt32(this->textBox12->Text)+","+Convert::ToDouble(this->textBox13->Text)+")";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Добавление нового клиента');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

if(ChoiseToLV2==4)

{

String ^q="insert into ys_ab (Data,Srok,id_service,id_klient) VALUES('"+this->textBox10->Text+"','"+this->textBox11->Text+"',"+Convert::ToInt32(this->textBox12->Text)+","+Convert::ToInt32(this->textBox13->Text)+")";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Добавление записи в таблицу клиент-услуга');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

if(ChoiseToLV2==5)

{

String ^q="insert into services (Opisanie,Stoimost)VALUES('"+this->textBox10->Text+"',"+Convert::ToInt32(this->textBox11->Text)+")";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

q="insert into changes(id_user,data,Opisanie) values("+id+",Now(),'Добавление записи в таблицу услуги');";

cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

rdr=cmd->ExecuteReader();

rdr->Close();

}

rdr->Close();

cn->Close();

}

//Действия, происходящие при внесении данных в поле ввода номера

private: System::Void comboBox2_TextUpdate(System::Object^ sender, System::EventArgs^ e) {

cn->Open();

String ^q="SELECT surname FROM Klient WHERE num like '"+this->comboBox2->Text+"%';";

System::Data::OleDb::OleDbCommand^cmd=gcnew System::Data::OleDb::OleDbCommand(q,cn);

System::Data::OleDb::OleDbDataReader ^rdr=cmd->ExecuteReader();

while(rdr->Read())

{

this->comboBox1->Text=Convert::ToString(rdr[0]);

}

rdr->Close();

cn->Close();

}

Заключение

Данная курсовая работа заключала в себе анализ и создание базы данных для аварийной службы.

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

Цели и задачи были полностью выполнены. Это означает, что материал по дисциплине "База данных" был полностью усвоен и принят за основу моему дальнейшему развитию.

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

1. Дейт, К. Дж. Введение в системы Баз Данных, 7-е издание.: Пер. с англ. Ю.Г. Гордиенко; Под ред. А.В. Слепцова / К. Дж. Дейт - М.: Издательский дом "Вильямс", 2001. - 1072с.

2. Роб, П., Коронел, К. Системы баз данных: проектирование, реализация и управление.- 5-е изд., перераб. и доп./ Пер. с англ. А. Никифоров; Под ред. Е. Кондукова / П. Роб, К. Коронел - СПб.: БХВ - Петербург, 2004. - 1040 с.

3. Вендров, A. M. Проектирование программного обеспечения информационых систем: Учебник / A. M. Вендров. - М.: "Финансы и статистика", 2000. - 352 с.

4. Калянов, Г. Н. CASE. Структурный системный анализ / Г.Н. Калянов. - М.: Лори, 1996. - 437 с.