dqXgGp6x

1USE MASTER GO 
2 
3------------------------------------------------------------- 
4 
5IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Island')  DROP DATABASE Island 
6 
7------------------------------------------------------------- 
8 
9CREATE DATABASE Island 
10 
11------------------------------------------------------------- 
12 
13USE Island GO 
14 
15------------------------------------------------------------- 
16 
17create table MAN (  PK_Man int UNIQUE NOT NULL,  Sername nvarchar(20) NOT NULL,  Name nvarchar(20) NULL,  FatherName nvarchar(20) NULL,  Sex nvarchar(8) NOT NULL,  DateBirth date NOT NULL,  DateDeath date NULL,    constraint PK_Man_Man primary key (PK_Man),    constraint CH_Sex_Man check (sex in ('м','ж','муж','жен',          'мужской','женский','мужчина','женщина',        'М','Ж','Муж','Жен',         'Мужской','Женский','Мужчина','Женщина')),  constraint CH_DB_Man check (DateBirth<=GetDate()),  constraint CH_DD_Man check (DateDeath<=GetDate()),  constraint CH_DB_DD_Man check (DateBirth<=DateDeath) ) 
18 
19------------------------------------------------------------- 
20 
21create table HOUSE (  PK_House int UNIQUE NOT NULL,  DateBuild date NOT NULL,  DateDestroy date NULL,  HouseAddress nvarchar(30) NOT NULL,  HousePlan varbinary(max) NULL,    constraint PK_House_House primary key (PK_House),  constraint CH_DB_House check (DateBuild<=GetDate()),  constraint CH_DD_House check (DateDestroy<=GetDate()),  constraint CH_DB_DD_House check (DateBuild<=DateDestroy) ) 
22 
23------------------------------------------------------------- 
24 
25create table LEAVING (  FK_Man int NOT NULL,  FK_House int NOT NULL,  DateBeginLeaving date NOT NULL,  DateEndLeaving date NULL, 
268 
27 
28   constraint FK_Man_Leaving foreign key (FK_Man) references MAN (PK_Man),  constraint FK_House_Leaving foreign key (FK_House) references HOUSE (PK_House),  constraint CH_DBL_Leaving check (DateBeginLeaving<=GetDate()),  constraint CH_DEL_Leaving check (DateEndLeaving<=GetDate()),  constraint CH_DBL_DEL_Leaving check (DateBeginLeaving<=DateEndLeaving) ) 
29 
30------------------------------------------------------------- 
31 
32create table PARENTS (  FK_Man int NOT NULL,  FK_Parent int NOT NULL,  ParentStatus nvarchar(5) NOT NULL,    constraint FK_Man_Parents foreign key (FK_Man) references MAN (PK_Man),  constraint FK_Parent_Parents foreign key (FK_Parent) references MAN (PK_Man),  constraint CH_PS_Parents check (ParentStatus in ('мать','отец','Мать','Отец')), ) 
33 
34------------------------------------------------------------- 
35 
36create table BUSINESS (  PK_Business int UNIQUE NOT NULL,  NameBusiness nvarchar(50) NOT NULL,  AboutBusiness nvarchar(max) NULL,    constraint PK_Business_Business primary key (PK_Business) ) 
37 
38------------------------------------------------------------- 
39 
40create table WORK (  PK_Work int UNIQUE NOT NULL,  NameWork nvarchar(50) NOT NULL,  AboutWork nvarchar(max) NULL,    constraint PK_Work_Work primary key (PK_Work) ) 
41 
42------------------------------------------------------------- 
43 
44create table BUSINESSMAN (  PK_Businessman int UNIQUE NOT NULL,  FK_Business int NOT NULL,  FK_Man int NOT NULL,  DateBeginBusiness date NOT NULL,  DateEndBusiness date NULL,  AboutActivity nvarchar(max) NULL,  WhyEnd nvarchar(2000) NULL, 
45 
46 constraint PK_Businessman_Businessman primary key (PK_Businessman),  constraint FK_Business_Businessman foreign key (FK_Business) references BUSINESS (PK_Business),   constraint FK_Man_Businessman foreign key (FK_Man) references MAN (PK_Man),  constraint CH_DBB_Businessman check (DateBeginBusiness<=GetDate()),  constraint CH_DEB_Businessman check (DateEndBusiness<=GetDate()), 
479 
48 
49 constraint CH_DBB_DEB_Businessman check (DateBeginBusiness<=DateEndBusiness) ) 
50 
51------------------------------------------------------------- 
52 
53create table CONTRACTS (  PK_Contract int UNIQUE NOT NULL,  FK_Businessman int NOT NULL,  FK_Worker int NOT NULL,  FK_Work int NOT NULL,  DateBeginContract date NOT NULL,  DateEndContract date NULL, 
54 
55 constraint PK_Contract_Contracts primary key (PK_Contract),  constraint FK_Businessman_Contracts foreign key (FK_Businessman) references BUSINESSMAN (PK_Businessman),   constraint FK_Worker_Contracts foreign key (FK_Worker) references MAN (PK_Man),  constraint FK_Work_Contracts foreign key (FK_Work) references WORK (PK_Work),    constraint CH_DBC_Contract check (DateBeginContract<=GetDate()),  constraint CH_DEC_Contract check (DateEndContract<=GetDate()),  constraint CH_DBC_DEC_Contract check (DateBeginContract<=DateEndContract) ) 
56 
57------------------------------------------------------------- 
58 
59create table COOPERATIVE (  PK_Cooperative int UNIQUE NOT NULL,  NameCooperative nvarchar(50) NOT NULL,  AboutCooperative nvarchar(max) NULL,  DateBeginCooperative date NOT NULL,  DateEndCooperative date NULL,    constraint PK_Cooperative_Cooperative primary key (PK_Cooperative),  constraint CH_DBC_Cooperative check (DateBeginCooperative<=GetDate()),  constraint CH_DEC_Cooperative check (DateEndCooperative<=GetDate()),  constraint CH_DBC_DEC_Cooperative check (DateBeginCooperative<=DateEndCooperative) ) 
60 
61------------------------------------------------------------- 
62 
63create table MEMBERS (  FK_Businessman int NOT NULL,  FK_Cooperative int NOT NULL,  DateBeginMember date NOT NULL,  DateEndMember date NULL,    constraint FK_Businessman_Members foreign key (FK_Businessman) references BUSINESSMAN (PK_Businessman),  constraint FK_Cooperative_Members foreign key (FK_Cooperative) references COOPERATIVE (PK_Cooperative),  constraint CH_DBM_Members check (DateBeginMember<=GetDate()),  constraint CH_DEM_Members check (DateEndMember<=GetDate()),  constraint CH_DBM_DEM_Members check (DateBeginMember<=DateEndMember)