Создание БД в Microsoft SQL Server

              Таблицы Клиент, Договор, Объект, Организация, Сотрудники, Экипаж, Вызов находятся в 3НФ, т.к. они приведены ко 2НФ. и все неключевые атрибуты взаимнонезависимы и полностью зависят от ключа (ФЗ1-ФЗ6).

              Клиент (Код клиента, номер договора, номер клиента, адрес клиента, ФИО клиента, телефон клиента);

             Договор (Код договора, номер договора, код клиента, начало действия договора, окончание действия договора, стоимость оплаты в месяц, компенсация при краже);

             Объект  (Код объекта, адрес объекта, тип дома, наличие кодового замка, количество этажей, этаж, наличие балкона, план квартиры);

             Организация (Код организации, название, ИНН, адрес, телефон, код договора, код сотрудники);

             Сотрудники  (Код сотрудники, ФИО, дата приема на работу, ИНН сотрудника, адрес сотрудника, телефон сотрудника);

             Экипаж  (Код экипажа, код сотрудники, код вызова);

             Вызов  (Код вызова, номер вызова, код экипажа, дата, код объекта).

 

                  3.2. Связи

Для преобразования бинарных связей между объектами  в реляционную схему используется механизм первичных и внешних  ключей:

1. из двух таблиц одной предоставим статус родительский, а другой- дочерний;
2. дочерняя таблица содержит копию первичного ключа родительской таблицы, которая называется внешний ключ;
3. соединить родительскую и дочернюю таблицы по одноименному полю.

 

 

Таблица 4. Связи.

 

Название связи	Таблица	Статус таблицы	Ключи
Заключает	Клиент Договор	Родительская Дочерняя	Код_клиента(ПК) Код_договора(ПК) Код_клиента(ВК)
Имеет	Клиент Объект	Родительская Дочерняя	Код_клиента(ПК) Код_объекта(ПК) Код_клиента(ВК)
Принимает	Организация Договор	Родительская Дочерняя	Код_организации(ПК) Код_договора(ПК) Код_договора(ВК)
Работают	Организация Сотрудники	Родительская Дочерняя	Код_организации(ПК) Код_сотрудники(ПК) Код_сотрудники(ВК)
Входят	Сотрудники Экипаж	Родительская Дочерняя	Код_сотрудники(ПК) Код_экипаж(ПК) Код_сотрудники(ВК) Код_вызова(ВК)
Исполняет	Экипаж Вызов	Родительская Дочерняя	Код_экипаж(ПК) Код_вызова(ПК) Код_экипаж(ВК) Код_объекта(ВК)

 

3.4. Определение целостности данных

 

 При проектировании БД необходимо создать наиболее эффективную структуру данных. Признаками эффективности структуры БД считаются:

1. Обеспечение быстрого доступа  к данным в таблицах.

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

3. Обеспечение целостности данных  таким образом, чтобы при изменении  одних объектов автоматически  происходило соответствующее изменение  других, связанных с ними объектов.

3.4.1. Доменная целостность

Ограничение домена- ограничения на значения атрибутов из множества значений домена. Если значения атрибута неизвестно, то опция NULL- это отсутствие атрибута.

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

Все остальные  атрибуты принимают значения в соответствии с типом данных.

3.4.2. Категорийная целостность

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

Первичные ключи: код клиента, код договора, код объекта, код сотрудника, код организации, код экипажа, код вызова.

Все первичные  ключи должны быть обязательно заполнены  и индексированы.

3.4.2. Ссылочная целостность

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

Внешние ключи: код клиента, код договора, код организации, код объекта, код сотрудники, код экипажа.

Внешние ключи  так же не могут быть пустыми.

 

 

 

4. Разработка пользовательского интерфейса

 Интерфейс пользователя разработан таким образом, что работать в программе было очень легко и просто.

  При запуске ИС охрана появляется основная форма показанная на рис. 4. При нажатии на кнопку «База клиентов», появляется форма  базой клиентов рис. при нажатии на кнопку «База объектов» появляется база объектов рис.5 , при нажатии на кнопку «База вызовов» появляется форма с базой вызовов рис. 6. Технология работы с отчетами показана ни рис. 7.   Технология работы с кнопками «Адреса на которые не выезжали», «Просмотр вызовов по регистрационному номеру клиента» и «Количество выездов помесячно» показано на рис. 8. Технология работы с диаграммами приведена на рис. 9.

 

Рис.4 Основная форма

Рис.5 База клиентов и объектов

 

Рис.6 База вызовов и экипажей

Рис.7 Работа с отчетами

 

Рис. 8 Технология работы с другими кнопками  

Рис. 9 Технология работы с диаграммами

 

 

6. Создание БД в Microsoft SQL Server

6.1 Разработка физической модели  БД

 

Прежде чем разрабатывать БД в MS SQL Server необходимо разработать физическую модель БД с соответствующими названиями (на английском языке) и типами столбцов(рис. 22). При этом в MS SQL Server существуют следующие основные типы данных[3]:

1. BIT (тип данных SQL 2003: BOOLEAN). Хранит значения 1, 0 или NULL, которое обозначает «unknown». В одном байте может храниться до 8 значений из столбцов типа BIT таблицы. В еще одном байте можно разместить дополнительные 8 значений типа BIT. Столбцы типа BIT нельзя индексировать.

2.CHAR[(n)], CHARACTER[(n)] (тип данных SQL 2003: CHARACTER(n)).Хранит символьные данные фиксированной длины от 1 до 8000 символов. Все неиспользованное место по умолчанию заполняется пробелами. (Автоматическое заполнение пробелами можно отключить.) Тип занимает п байт.

3.DATETIME (тип данных SQL 2003: TIMESTAMP).Хранит значение даты и времени в диапазоне с 01-01-1753 00:00:00 до 31-12-9999 23:59:59. Для хранения требуется 8 байт.

4.FLOAT[(n)] (тип данных SQL 2003: FLOAT, FLOAT(n)).Хранит значения с плавающей точкой в диапазоне от-1.79Е + 308 до 1.79Е + 308. Точность, определяемая параметром п, может изменяться в пределах от 1 до 53. Для хранения 7 цифр (и - от 1 до 24) требуется 4 байта. Значения, превышающие 7 цифр, занимают 8 байт.

5. IMAGE (тип данных SQL 2003: BLOB).Хранит двоичное значение переменной длины до 2 147 483 647 байт. Этот тип данных часто используется для хранения графики, звука и файлов, таких, как документы Microsoft Word и электронные таблицы Microsoft Excel. Значениями типа IMAGE нельзя свободно манипулировать. Столбцы типа  

6.INT [IDENTITY [(seed, increment)] (тип данных SQL 2003: INTEGER).Хранит целые числа со знаком или без знака в диапазоне от -2 147 483 648 до 2 147 483 647. Занимает 4 байта. Все целочисленные типы данных, а также типы, хранящие десятичные дроби, поддерживают свойство IDENTITY, identity - это автоматически инкрементируемый идентификатор строки. Обращайтесь к разделу «Инструкция CREATE/ALTERTABLE».

7.MONEY (тип данных SQL 2003: отсутствует). Хранит денежные значения в  диапазоне от -922337203685477.5808 до 922337203685477.5807. Значение занимает 8 байт.

8.VARBINARY[(п)] (тип данных SQL 2003: BLOB). Представляет собой двоичное значение переменной длины, до 8000 байт. Занимаемое место соответствует размеру вставленных данных плюс 4 байта.

9.VARCHARf[(n)], CHAR VARYING [(п)], CHARACTER VARYING f(n)J (тип данных SQL 2003: CHARACTER VARYING [(n)]. Хранит символьные данные фиксированной длины размером от 1 до 8000 символов. Занимаемое место равно реальному размеру введенного значения в байтах, а не значению n.

 

 

 

Рис. 20  Физическая схема БД в MS SQL Server 

6.2 Создание БД в MS SQL Server

Прежде всего необходимо создать  БД в MS SQL Server для этого будем использовать команду СREATE DATABASE, которая выглядит следующим образом:

create database database_name

[on

{ [primary] (name = logical_file_name,

filename = 'os_file_name'

[, size = size]

[, maxsize = max_size]

[, filegrowth = growth_increment] )

} [,...n]

]

[log on

{ ( name = logical_file_name,

filename = 'os_file_name'

[, size = size] )

} [,...n]

] 
 [for restore]

filename - полный путь и имя файла для размещения БД, должен указывать на локальный диск компьютера, на котором установлен sql server.

size - начальный размер каждого файла в Мб.

maxsize - максимальный размер файла в Мб, если не указана размер не ограничивается.

filegrowth - единица увеличения файла, указывается в Мб (по умолчанию) или в процентах (т.е. к числу добавляется %), значение 0 запрещает увеличение файла.

Теперь посмотрим как выглядит создание нашей БД:

create database sqlohrana

on 
primary (name= sqlovodata,

filename='c:\mssql\data\sqlovo_data.mdf',  
size=5,  
maxsize=10,

filegrowth=10% )

log on (

name=sqlovolog, 
filename='c:\mssq\data\sqlovo_log.ldf',  
size=1,  
maxsize=5, 
filegrowth=1 
)

6.3 Создание  таблиц в MS SQL Server

После создания БД в MS SQL Server необходимо создать таблицы нашей БД. Для этого используем команду:

create table table_name

(column_name data_type [null | not null]

[,...n])

Для модификации таблиц используется команда ALTER TABLE.

Первичный ключ обозначается как PRIMARY KEY (имя поля)

Вторичный ключ создается в дочерней таблице следующим образом:  FOREIGN KEY (имя поля)    REFERENCES имя родительской таблицы  (имя поля)

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

CREATE TABLE Klient (

        ID_klient            int NOT NULL,

        Nom_klient           int NULL,

        Adr_kl               varchar(20) NULL,

        FIO_klient           varchar(20) NULL,

        Tel_kl               varchar(20) NULL,

        Nom_Dog              int NULL,

        Adr_kv               varchar(20) NULL,

        Kod_z                char(18) NULL,

        Et_dom               int NULL,

        Et_kv                int NULL,

        Nal_bal              bit NULL,

        Plan_kv              image NULL,

        St_opl               money NULL,

        Komp                 money NULL,

        Nac_dog              datetime NULL,

        Kon_dog              datetime NULL,

        Prod_dog             datetime NULL,

        Dop_usl              varbinary NULL,

        Id_type_bal          int NULL,

        ID_Type_dver         int NULL,

        ID_Type_dom          int NULL

)

go

ALTER TABLE Klient

        ADD PRIMARY KEY (ID_klient ASC)

go

  CREATE TABLE T_Auto (

        ID_auto              int NOT NULL,

        Marka                varchar(20) NULL

)

go

  ALTER TABLE T_Auto

        ADD PRIMARY KEY (ID_auto ASC)

go

  CREATE TABLE T_Dokument (

        Id_dok               int NOT NULL,

        Naz_dok              varchar(20) NULL

)

go

  ALTER TABLE T_Dokument

        ADD PRIMARY KEY (Id_dok ASC)

go

  CREATE TABLE T_Sotr (

        Tab_nom              int NOT NULL,

        FIO_sotr             varchar(20) NULL

)

go

  ALTER TABLE T_Sotr

        ADD PRIMARY KEY (Tab_nom ASC)

go

  CREATE TABLE T_Type_bal (

        Id_type_bal          int NOT NULL,

        Type_bal             varchar(20) NULL

)

go

  ALTER TABLE T_Type_bal

        ADD PRIMARY KEY (Id_type_bal ASC)

go

  CREATE TABLE T_Type_dom (

        ID_Type_dom          int NOT NULL,

        Type_dom             varchar(20) NULL

)

go

ALTER TABLE T_Type_dom

        ADD PRIMARY KEY (ID_Type_dom ASC)

go

  CREATE TABLE T_Type_dver (

        ID_Type_dver         int NOT NULL,

        Type_dver            varchar(20) NULL

)

go

  ALTER TABLE T_Type_dver

        ADD PRIMARY KEY (ID_Type_dver ASC)

go

  CREATE TABLE T_Vizov (

        Id_viz               int NOT NULL,

        Nom_viezd            int NULL,

        Nom_ek               int NULL,

        Dat_vr               datetime NULL,

        Viz_log              bit NULL,

        Straf                money NULL,

        Comm                 varbinary NULL,

        ID_klient            int NULL,

        Id_dok               int NULL,

        ID_auto              int NULL,

        Tab_nom              int NULL

)

go

ALTER TABLE T_Vizov

        ADD PRIMARY KEY (Id_viz ASC)

go

 

 

ALTER TABLE Klient

        ADD FOREIGN KEY (ID_Type_dom)

                              REFERENCES T_Type_dom  (ID_Type_dom)

go

ALTER TABLE Klient

        ADD FOREIGN KEY (ID_Type_dver)

                              REFERENCES T_Type_dver  (ID_Type_dver)

go

ALTER TABLE Klient

        ADD FOREIGN KEY (Id_type_bal)

                              REFERENCES T_Type_bal  (Id_type_bal)

go

  ALTER TABLE T_Vizov

        ADD FOREIGN KEY (Tab_nom)

                              REFERENCES T_Sotr  (Tab_nom)

go

ALTER TABLE T_Vizov

        ADD FOREIGN KEY (ID_auto)

                              REFERENCES T_Auto  (ID_auto)

go

  ALTER TABLE T_Vizov

        ADD FOREIGN KEY (Id_dok)

                              REFERENCES T_Dokument  (Id_dok)

go

  ALTER TABLE T_Vizov

        ADD FOREIGN KEY (ID_klient)

                              REFERENCES Klient  (ID_klient)

Go

6.4 Создание триггеров в MS SQL Server

Ссылочная целостность в MS SQL Server в отличии от MS Access обеспечивается при помощи триггеров.

Триггер – это откомпилированная SQL-процедура, исполнение которой обусловлено  наступлением определенных событий  внутри реляционной базы данных. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто  связано с дополнительными затратами  ресурсов на операции ввода/вывода. В  том случае, когда тех же результатов (с гораздо меньшими непроизводительными  затратами ресурсов) можно добиться с помощью хранимых процедур или  прикладных программ, применение триггеров  нецелесообразно.