Проектирование базы данных «Стоматология» в MS SQL Server

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

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ  БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

САНКТ-ПЕТЕРБУРГСКИЙ  НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ УНИВЕРСИТЕТ  ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ, МЕХНИКИ  И ОПТИКИ

 

КУРСОВАЯ РАБОТА

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

 

Проектирование  базы данных «Стоматология» в MS SQL Server

 

ВЫПОЛНИЛ

Студент гр.                                                                                                                                                            подпись, дата          фамилия, инициалы

РУКОВОДИТЕЛЬ

Доцент, к.э.н                                                                                                 

должность, уч.                   оценка              подпись, дата          фамилия, инициалы

степень, звание

Санкт-Петербург

2013

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение

Стержневые идеи современных  информационных технологий базируются на концепции баз данных.

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

отображающую состояние объектов и их отношений в рассматриваемой

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

информации в структурированном виде и своевременный доступ к этим

данным.

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

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

Целью выполнения данной курсовой работы является приобретение

практических навыков  проектирования баз данных с помощью  СУБД MS SQL SERVER. В ходе работы необходимо разработать информационную базу стоматологии. Стоматология представляет собой организацию, в которой циркулирует большое количество информационных потоков (информация клиентах, врачах, предоставляемых услугах и т.д.).

Задачами курсовой работы являются:

1. Постановка задачи и  разработка бизнес-правил

2. Построение логической  и физической модели данных

з. описание информационных потребностей пользователей

4. Создание хранимых процедур, триггеров

5. Изучение курсоров

 

 

 

1. Постановка задачи и разработка бизнес-правил

Стоматология занимается лечением зубов

2. Построение логической модели данных

Логическая модель данных описывает сущности предметной области, их свойства (атрибуты) и взаимосвязи  друг с другом. Логическая модель данных стоматологии представлена на рис. 2.1.

Рис. 2.1 Логическая модель данных

Логическая модель строится без привязки к конкретной СУБД. Для ее

построения требуется:

1. Определить сущности исходя из предметной области. В нашем случае

это «Пациент», «Врач», «Лечение», «Прием» и т.п.

2. Определить свойства (атрибуты) сущностей. Каждой сущности

присущи свои атрибуты. Например, сущность «Список услуг» должна

иметь такие атрибуты: номер карты клиента, отродонтия, ортопедия, парадонтология, терапия, хирургия.

З. Определить связи между  сущностями.

 

3. Построение физической  модели данных

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

Рис. 2.2 Физическая модель данных

 

 

 

 

 

 

 

 

 

 

4. Код схемы данных

С помощью САSЕ-средства ERwin Data Modeler можно сгенерировать

код схемы проектируемой  базы данных. Он выглядит следующим  образом:

CREATE TABLE Grafik_raboty

(

Vremya_priema datetime  NOT NULL ,

Vihodnie_dni char(18)  NULL ,

Rabochie_dni char(18)  NULL ,

PRIMARY KEY (Vremya_priema ASC)

)

Go

 

CREATE TABLE Vrach

(

Tabelniy_nomer_vracha CHAR(5)  NOT NULL ,

Familiya char(50)  NULL ,

Imya char(50)  NULL ,

Otchestvo char(50)  NULL ,

№_kabineta CHAR(4)  NULL ,

Vremya_priema datetime  NULL ,

Vid_uslug char varying(50)  NULL ,

Specialnost char(18)  NULL ,

PRIMARY KEY (Tabelniy_nomer_vracha ASC),

FOREIGN KEY (Vremya_priema) REFERENCES Grafik_raboty(Vremya_priema)

)

go

 

 

CREATE TABLE Pacient

(

ID_klienta char(18)  NOT NULL ,

Familiya CHAR(50)  NULL ,

Adress CHAR(50)  NULL ,

Telephone CHAR(15)  NULL ,

Pol char(18)  NULL ,

Data_rozhdenia datetime  NULL ,

Imya CHAR(50)  NULL ,

Otchestvo CHAR(50)  NULL ,

PRIMARY KEY (ID_klienta ASC)

)

go

 

 

CREATE TABLE Priem

(

Data_and_time datetime  NOT NULL ,

Tabelniy_nomer_vracha CHAR(5)  NULL ,

ID_klienta char(18)  NULL ,

Diagnoz char(18)  NULL ,

Stoimost char(18)  NULL ,

PRIMARY KEY (Data_and_time ASC),

FOREIGN KEY (Tabelniy_nomer_vracha) REFERENCES Vrach(Tabelniy_nomer_vracha)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

FOREIGN KEY (ID_klienta) REFERENCES Pacient(ID_klienta)

)

go

 

 

CREATE TABLE Lechenie

(

Nomer_karty_klienta char(5)  NOT NULL ,

Tabelniy_nomer_vracha CHAR(5)  NULL ,

Naimenovanie char(50)  NULL ,

Characteristika varchar(100)  NULL ,

Stoimost money  NULL ,

ID_klienta char(10)  NULL ,

Data_and_time char(10)  NULL ,

PRIMARY KEY (Nomer_karty_klienta ASC),

FOREIGN KEY (Tabelniy_nomer_vracha) REFERENCES Vrach(Tabelniy_nomer_vracha)

FOREIGN KEY (ID_klienta) REFERENCES Pacient(ID_klienta) 

FOREIGN KEY (Data_and_time) REFERENCES Priem(Data_and_time)

)

go

CREATE TABLE Spisok_uslug

(

Kod_uslugi char(18)  NOT NULL ,

Nomer_karty_klienta char(5)  NULL ,

Ortodontia char(18)  NULL ,

Ortopedia char(18)  NULL ,

Paradontologia char(18)  NULL ,

Terapia char(18)  NULL ,

Hirurgia char(18)  NULL ,

PRIMARY KEY (Kod_uslugi ASC),

FOREIGN KEY (Nomer_karty_klienta) REFERENCES Lechenie(Nomer_karty_klienta)

)

go

CREATE TABLE Schet

(

Nomer_scheta CHAR(5)  NOT NULL ,

ID_klienta CHAR(5)  NULL ,

Pometka_ob_oplate bit  NULL ,

Summa money  NULL ,

Data_and_time datetime  NULL ,

Data_vypiski datetime  NULL ,

PRIMARY KEY (Nomer_scheta ASC),

FOREIGN KEY (ID_klienta) REFERENCES Pacient(ID_klienta)

FOREIGN KEY (Data_and_time) REFERENCES Priem(Data_and_time) 

)

go

Создание таблицы осуществляется посредством оператора CREATE

TABLE. После оператора указывается имя таблицы. При определении столбца необходимо задать его имя, тип данных, к которому будут относиться значения этого столбца, а также определить, можно ли в качестве значения рассматриваемого столбца использовать ключевое слово NULL. Ключевым словом NULL помечается такой столбец, который может содержать неопределенные значения. Определения столбцов первичных ключей отношений всегда должны содержать ключевые слова NOT NULL.

 

 

 

5. Диаграмма базы данных в MS SQL Sеrvеr

Диаграмма базы данных обеспечивает визуальное представление

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

 

Рис 5.1. Диаграмма базы данных

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6. Описание информационных  потребностей пользователя

Чтобы определить информационные потребности, необходимо знать

Какие пользователи будут  работать с базой данных, и получать из нее 

информацию. В нашем случае в роли пользователей могут выступать

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

продаж, начальник производства.

Все запросы по выборке  данных в SQL конструируются с помощью

оператора SELECT. Он позволяет выполнять довольно сложные проверки и

обработки данных. При формировании запроса SELECT пользователь

описывает ожидаемый набор  данных: его вид (набор столбцов) и его

содержимое (критерий попадания записи в набор, группировка значений,

порядок вывода записей и  т.п.).

Рассмотрим следующие  выборки данных:

1. Выборка пациентов за 2012 год со счетом на сумму более 2000 рублей (рис.6.1).

SELECT Familiya, Imya, Otchestvo, Pacient.Kod_klienta, Summa

FROM Pacient

          INNER JOIN Schet

                     ON (Pacient.Kod_klienta=Schet.Kod_klienta)

WHERE year(schet.Data_and_time)=2012

             GROUP BY familiya

        HAVING Summa>2000

Рис. 6.1. Количество пациентов за 2012 год со счетом на сумму более 2000 руб.

2. Выборка клиентов за май, оплативших счет (рис. 6.2).

SELECT Familiya, Imya, Otchestvo, Pacient.Kod_klienta, Summa

FROM Pacient

    INNER JOIN Schet

                        ON (Pacient.Kod_klienta=Schet.Kod_klienta)

WHERE month(schet.Data_and_time)=5 and Pometka_ob_oplate= 'true'

             GROUP BY Summa

Рис.6.2. Клиенты за май, оплатившие счет

3. Выборка врачей, у которых выходной день – воскресенье (рис. 6.3).

SELECT Familiya, Imya, Otchestvo, Vremya_raboti, Vyhodniye_dni

FROM Vrach AS V

            INNER JOIN Grafik_raboti AS gr

                                ON(V.Tabelniy_nomer_vracha=gr.Tabelniy_nomer_vracha)

WHERE Vyhodniye_dni = 'ВС'

              ORDER BY Familiya ASC

Рис.6.3. Количество врачей, имеющих  выходной в воскресенье

4. Выборка пациентов, удалявших зубы в клинике (рис. 6.4)

 

SELECT Familiya, Imya, Otchestvo, Naimenovanie, Kod_uslugi

FROM Pacient

            INNER JOIN Lechenie

                      ON (Pacient.Kod_klienta=Lechenie.Kod_klienta)

WHERE Kod_uslugi= '001'

              ORDER BY Familiya ASC

Рис. 6.4. Пациенты, удалявшие зубы в  клинике

5. Количество пациентов, находящихся на лечении у врача с кодом 113 и страдающих пародонтозом (рис. 6.5).

SELECT Familiya, Imya, Otchestvo, Diagnoz, Naimenovanie, Priem.Tabelniy_nomer_vracha

FROM Priem

           INNER JOIN Pacient

                      ON (Pacient.Kod_klienta=Priem.Kod_klienta)

                           INNER JOIN Lechenie

                                     ON (Pacient.Kod_klienta=Lechenie.Kod_klienta)

WHERE  Priem.Tabelniy_nomer_vracha = '113'and Diagnoz ='Пародонтоз'

    ORDER BY Familiya ASC

Рис. 6.5 Пациенты с пародонтозом, лечащиеся  у врача с кодом 113

 

 

 

7. Хранимые процедуры

Хранимые процедуры являются способом, с помощью которого можно

создавать подпрограммы, работающие на сервере и управляющие его

процессами.

В данной курсовой работе рассмотрим следующие хранимые процедуры:

1. Получение информации о пациенте, его лечащем враче и дате и времени приема по номеру его карты (рис. 7.1).

CREATE procedure informatio(@a1 char(20))

AS

SELECT imya, familiya, otchestvo, tabelniy_nomer_vracha, lechenie.data_and_time

FROM Pacient

          INNER JOIN lechenie

                     ON (pacient.kod_klienta=lechenie.kod_klienta)

WHERE @a1=lechenie.Nomer_karty_klienta

             ORDER BY Familiya

 

2. Получение информации о пациентах с диагнозом пародонтоз, сумме их счета (рис. 7.2).

CREATE procedure information_12

AS

SELECT Familiya, Imya, Otchestvo, diagnoz, Schet.Summa, Pacient.Kod_klienta

FROM Priem inner join Pacient

        ON (Pacient.Kod_klienta=Priem.Kod_klienta)

             INNER JOIN Schet

                    ON (Pacient.Kod_klienta=Schet.Kod_klienta)

WHERE Diagnoz='Пародонтоз'

 

3. Получение информации о пациентах врача по его табельному номеру (рис.7.3). Если табельный номер не вводится, выводится информация обо всех пациентах (рис. 7.4).