樹木草花データベース

自宅を新築することになりました．その際に外構の植栽について勉強する機会があり，樹木および草花の種類についてマスタを作成したので備忘録として記します．

作成するテーブルは以下のとおりです．

M_Colors 色彩マスタ
M_Direction 方角マスタ
M_FLOWER 草花マスタ
M_SEASON 季節マスタ
M_TREE 樹木マスタ
T_FlowerColors 草花色彩テーブル
T_FlowerMonth 開花月テーブル

USE ExteriorDesign;

GO
IF OBJECT_ID('M_Colors') IS NOT NULL DROP TABLE M_Colors;

GO

CREATE TABLE M_Colors

(

	ColorName	nvarchar(10)	NOT NULL,

	CONSTRAINT PK_M_Colors PRIMARY KEY (ColorName)

);

GO
IF OBJECT_ID('M_Direction') IS NOT NULL DROP TABLE M_Direction;

GO

CREATE TABLE M_Direction

(

	Direction	nvarchar(2)		NOT NULL,

	CONSTRAINT PK_M_Direction PRIMARY KEY (Direction)

);

GO
IF OBJECT_ID('M_FLOWER') IS NOT NULL DROP TABLE M_FLOWER;

GO

CREATE TABLE M_FLOWER

(

	FlowerName	nvarchar(20)	NOT NULL,

	Evergreen	nchar(3)	NOT NULL,

	Overwinter	nchar(3)	NOT NULL,

	Sunshine	nchar(3)	NOT NULL,

	CONSTRAINT PK_M_FLOWER PRIMARY KEY (FlowerName)

);

GO

ALTER TABLE M_FLOWER ADD CONSTRAINT CK_Evergreen

	CHECK (Evergreen='落葉性' OR Evergreen='常緑性');

GO

ALTER TABLE M_FLOWER ADD CONSTRAINT CK_Overwinter

	CHECK (Overwinter='多年草' OR Overwinter='一年草' OR Overwinter='球根類');

GO

ALTER TABLE M_FLOWER ADD CONSTRAINT CK_Sunshine

	CHECK (Sunshine='好陽種' OR Sunshine='半陽種' OR Sunshine='耐陰種');

GO
IF OBJECT_ID('M_SEASON') IS NOT NULL DROP TABLE M_SEASON;

GO

CREATE TABLE M_SEASON

(

	TwentyfourSeason	nchar(2)	NOT NULL,

	Season	nchar(1)	NOT NULL,

	MonthDay	nchar(4)	NOT NULL,

	CONSTRAINT PK_M_SEASON PRIMARY KEY (TwentyfourSeason)

);

GO
IF OBJECT_ID('M_TREE') IS NOT NULL DROP TABLE M_TREE;

GO

CREATE TABLE M_TREE

(

	TreeName	nvarchar(20)	NOT NULL,

	Hight	nvarchar(3)	NOT NULL,

	Evergreen	nchar(3)	NOT NULL,

	Leafwidth	nchar(3)	NOT NULL,

	Sunshine	nvarchar(3)	NULL,

	CONSTRAINT PK_M_TREE PRIMARY KEY (TreeName)

);

GO

ALTER TABLE M_TREE  WITH CHECK ADD  CONSTRAINT CK_Evergreen 

	CHECK  (Evergreen='落葉樹' OR Evergreen='常緑樹');

GO

ALTER TABLE M_TREE  WITH CHECK ADD  CONSTRAINT CK_Hight 

	CHECK  (Hight='低木' OR Hight='中木' OR Hight='中高木' OR Hight='高木');

GO

ALTER TABLE M_TREE  WITH CHECK ADD  CONSTRAINT CK_Leafwidth 

	CHECK  (Leafwidth='針葉樹' OR Leafwidth='広葉樹');

GO

ALTER TABLE M_TREE  WITH CHECK ADD  CONSTRAINT CK_Sunshine 

	CHECK  (Sunshine='中庸樹' OR Sunshine='陰樹' OR Sunshine='陽樹');

GO
IF OBJECT_ID('T_FlowerColors') IS NOT NULL DROP TABLE T_FlowerColors;

GO

CREATE TABLE T_FlowerColors

(

	FlowerName	nvarchar(20) NOT NULL,

	ColorName	nvarchar(10) NOT NULL,

	CONSTRAINT	PK_T_FlowerColors PRIMARY KEY (FlowerName, ColorName)

);

GO

ALTER TABLE T_FlowerColors  WITH CHECK ADD  CONSTRAINT FK_FLOWER FOREIGN KEY(FlowerName)

	REFERENCES M_FLOWER (FlowerName);

GO

ALTER TABLE T_FlowerColors  WITH CHECK ADD CONSTRAINT FK_Colors FOREIGN KEY(ColorName)

	REFERENCES M_Colors (ColorName);

GO
IF OBJECT_ID('T_FlowerMonth') IS NOT NULL DROP TABLE T_FlowerMonth;

GO

CREATE TABLE T_FlowerMonth

(

	FlowerName	nvarchar(20)	NOT NULL,

	[Month]	nchar(2)	NOT NULL,

	CONSTRAINT PK_T_FlowerMonth PRIMARY KEY (FlowerName, [Month])

);

GO

ALTER TABLE T_FlowerMonth  WITH CHECK ADD CONSTRAINT FK_FlowerName FOREIGN KEY(FlowerName)

	REFERENCES M_FLOWER (FlowerName);

GO

ALTER TABLE T_FlowerMonth  WITH CHECK ADD CONSTRAINT CK_Month 

	CHECK  ([Month]>='01' AND [Month]<='12');