73
12. Вендров А.М. CASE-технологии. Современные методы и
средства проектирования информационных систем. М.: Финансы и
статистика, 2012. 360с.
13. Голицина О.Л., Максимов Н.В., Попов И.И. Базы данных:
Учебное пособие. – М.: ФОРУМ, 2013. 344с.
14. Горев А., Макашарипов С., Ахаян Р. Эффективная работа с
СУБД. – М.: Финансы и статистика, 2014. 289с.
15. Грабер М. Введение в SQL / М. Грабер. – М.: Издательство Лори,
2013. 281с.
16. Дэвид А. Марка и Клемент МакГоуэн Методология структурного
анализа и проектирования. SADT. – СПб.: Питер, 2012. 399с.
17. Карпова Т.С. Базы данных: модели, разработка, реализация: учеб.
пособие / Т.С. Карпова. – СПб.: Питер, 2012. 272с.
18. Кириллов В.В. Основы проектирования реляционных баз данных.
Учебное пособие. – СПб.: ИТМО, 2016. 480с.
19. Коннолли Т., Бегг К., Страчан А. Базы данных: проектирование,
реализация и сопровождение. Теория и практика, 2-е изд.: Пер с англ.:
Уч.пос. – М. : Изд.дом «Вильямс», 2011. 220с.
20. Кузин А.В. Базы данных. – М.: Академия, 2013. 190с.
21. Кузнецов С.Д. SQL: Язык реляционных баз данных. – М.: Майор,
2011. 540с.
74
Приложение А. Листинг скрипта создания базы данных
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Employer') and o.name = 'FK_EMPLOYER_EMPLOYER-
_POST')
alter table Employer
drop constraint "FK_EMPLOYER_EMPLOYER-_POST"
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('"Order"') and o.name = 'FK_ORDER_EMPLOYER-
_EMPLOYER')
alter table "Order"
drop constraint "FK_ORDER_EMPLOYER-_EMPLOYER"
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('Penalty') and o.name = 'FK_PENALTY_EMPLOYER-
_EMPLOYER')
alter table Penalty
drop constraint "FK_PENALTY_EMPLOYER-_EMPLOYER"
go
if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('TimeSheet') and o.name = 'FK_TIMESHEE_EMPLOYER-
_EMPLOYER')
alter table TimeSheet
drop constraint "FK_TIMESHEE_EMPLOYER-_EMPLOYER"
go
75
if exists (select 1
from sysindexes
where id = object_id('Employer')
and name = 'Relationship_1_FK'
and indid > 0
and indid < 255)
drop index Employer.Relationship_1_FK
go
if exists (select 1
from sysobjects
where id = object_id('Employer')
and type = 'U')
drop table Employer
go
if exists (select 1
from sysindexes
where id = object_id('"Order"')
and name = 'Employer-Order_FK'
and indid > 0
and indid < 255)
drop index "Order"."Employer-Order_FK"
go
if exists (select 1
from sysobjects
where id = object_id('"Order"')
and type = 'U')
drop table "Order"
go
if exists (select 1
from sysindexes
76
where id = object_id('Penalty')
and name = 'Employer-Penalty_FK'
and indid > 0
and indid < 255)
drop index Penalty."Employer-Penalty_FK"
go
if exists (select 1
from sysobjects
where id = object_id('Penalty')
and type = 'U')
drop table Penalty
go
if exists (select 1
from sysobjects
where id = object_id('Post')
and type = 'U')
drop table Post
go
if exists (select 1
from sysindexes
where id = object_id('TimeSheet')
and name = 'Employer-TimeSheet_FK'
and indid > 0
and indid < 255)
drop index TimeSheet."Employer-TimeSheet_FK"
go
if exists (select 1
from sysobjects
where id = object_id('TimeSheet')
and type = 'U')
drop table TimeSheet
77
go
/*==============================================================*/
/* Table: Employer */
/*==============================================================*/
create table Employer (
id_Emp int not null,
id_Post int null,
emp_FirstName varchar(100) not null,
emp_LastName varchar(100) not null,
emp_MidName varchar(100) not null,
emp_ID varchar(20) not null,
emp_Account varchar(50) not null,
emp_Birthday datetime not null,
constraint PK_EMPLOYER primary key (id_Emp)
)
go
if exists(select 1 from sys.extended_properties p where
p.major_id = object_id('Employer')
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and
c.name = 'id_Emp')
)
begin
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description',
'user', @CurrentUser, 'table', 'Employer', 'column', 'id_Emp'
end
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'Идентификатор сотрудника',
78
'user', @CurrentUser, 'table', 'Employer', 'column', 'id_Emp'
go
/*==============================================================*/
/* Index: Relationship_1_FK */
/*==============================================================*/
create nonclustered index Relationship_1_FK on Employer (id_Post ASC)
go
/*==============================================================*/
/* Table: "Order" */
/*==============================================================*/
create table "Order" (
id_Order int not null,
id_Emp int null,
or_Year int not null,
or_Month smallint not null,
or_Money money null,
or_Duration decimal(1,1) null,
constraint PK_ORDER primary key (id_Order)
)
go
if exists(select 1 from sys.extended_properties p where
p.major_id = object_id('"Order"')
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and
c.name = 'id_Emp')
)
begin
declare @CurrentUser sysname
select @CurrentUser = user_name()
79
execute sp_dropextendedproperty 'MS_Description',
'user', @CurrentUser, 'table', 'Order', 'column', 'id_Emp'
end
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'Идентификатор сотрудника',
'user', @CurrentUser, 'table', 'Order', 'column', 'id_Emp'
go
/*==============================================================*/
/* Index: "Employer-Order_FK" */
/*==============================================================*/
create nonclustered index "Employer-Order_FK" on "Order" (id_Emp ASC)
go
/*==============================================================*/
/* Table: Penalty */
/*==============================================================*/
create table Penalty (
id_Penalty int not null,
id_Emp int null,
pen_Year int not null,
pen_Month smallint not null,
pen_Money money not null,
constraint PK_PENALTY primary key (id_Penalty)
)
go
80
if exists(select 1 from sys.extended_properties p where
p.major_id = object_id('Penalty')
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and
c.name = 'id_Emp')
)
begin
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description',
'user', @CurrentUser, 'table', 'Penalty', 'column', 'id_Emp'
end
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'Идентификатор сотрудника',
'user', @CurrentUser, 'table', 'Penalty', 'column', 'id_Emp'
go
/*==============================================================*/
/* Index: "Employer-Penalty_FK" */
/*==============================================================*/
create nonclustered index "Employer-Penalty_FK" on Penalty (id_Emp ASC)
go
/*==============================================================*/
/* Table: Post */
/*==============================================================*/
create table Post (
id_Post int not null,
81
p_Name varchar(100) not null,
p_Salary money not null,
constraint PK_POST primary key (id_Post)
)
go
/*==============================================================*/
/* Table: TimeSheet */
/*==============================================================*/
create table TimeSheet (
id_TimeSheet int not null,
id_Emp int null,
ts_Data datetime not null,
ts_StartTime datetime not null,
ts_StopTime datetime not null,
ts_Duration decimal(1,1) null,
constraint PK_TIMESHEET primary key (id_TimeSheet)
)
go
if exists(select 1 from sys.extended_properties p where
p.major_id = object_id('TimeSheet')
and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and
c.name = 'id_Emp')
)
begin
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description',
'user', @CurrentUser, 'table', 'TimeSheet', 'column', 'id_Emp'
end
select @CurrentUser = user_name()
82
execute sp_addextendedproperty 'MS_Description',
'Идентификатор сотрудника',
'user', @CurrentUser, 'table', 'TimeSheet', 'column', 'id_Emp'
go
/*==============================================================*/
/* Index: "Employer-TimeSheet_FK" */
/*==============================================================*/
create nonclustered index "Employer-TimeSheet_FK" on TimeSheet (id_Emp ASC)
go
alter table Employer
add constraint "FK_EMPLOYER_EMPLOYER-_POST" foreign key (id_Post)
references Post (id_Post)
go
alter table "Order"
add constraint "FK_ORDER_EMPLOYER-_EMPLOYER" foreign key (id_Emp)
references Employer (id_Emp)
go
alter table Penalty
add constraint "FK_PENALTY_EMPLOYER-_EMPLOYER" foreign key (id_Emp)
references Employer (id_Emp)
go
alter table TimeSheet
add constraint "FK_TIMESHEE_EMPLOYER-_EMPLOYER" foreign key (id_Emp)
references Employer (id_Emp)
go