Table

use ShopFlower
go
TRUNCATE TABLE SalesPerson;
GO 
CREATE TABLE SalesPerson (
	PersonID int  PRIMARY KEY,
	[first_name] varchar(20),
	[last_name] varchar(20),
	Salary int,
	Birthdate date,
	Gender varchar(8),
	Hiring_date date ,
	Mobile_number varchar(15),
	Check (gender in  ('Female' , 'Male'))
);
ALTER TABLE SalesPerson
ADD CONSTRAINT UC_SalesPerson_MobileNumber UNIQUE (Mobile_number);

TRUNCATE TABLE Customer;
GO
CREATE TABLE Customer (
	[ID] int IDENTITY(100,1) PRIMARY KEY,
	[FirstName] varchar(20),
	[LastName] varchar(20),
	Addess varchar(50),
	Phone varchar(15),
	BirthDate date,
	Gender varchar(8),
	TotalPurchase int,
	Check (Gender in  ('Female' , 'Male'))	
);
ALTER TABLE Customer
ADD CONSTRAINT UC_Customer_Phone UNIQUE (Phone);

TRUNCATE TABLE Colour;
GO
CREATE TABLE Colour (
	ColorID int IDENTITY(100,1) PRIMARY KEY,
	ColorName varchar(20),
	HexCode varchar(10)
);
ALTER TABLE Colour
ADD CONSTRAINT UC_Colour_ColorName UNIQUE (ColorName);


TRUNCATE TABLE Occasion;
GO
CREATE TABLE Occasion (
	[ID] int IDENTITY(1,1) PRIMARY KEY,
	Title varchar(20) ,
	OccasionDescription varchar(100),
	check (Title in ('Birthday','Anniversary','Valentine','Funeral','Appreciation','Other'))
);

TRUNCATE TABLE GreenHouse;
GO
CREATE TABLE GreenHouse (
	ID int IDENTITY(1,1) PRIMARY KEY,
	[Name] varchar(20),
	[Manager] varchar(20),
	[Owner] varchar(20),
	[Address] varchar(100),
	ManagerPhone int

);

TRUNCATE TABLE Flower;
GO
CREATE TABLE Flower (
	ID int IDENTITY(1,1) PRIMARY KEY,
	FlowerName varchar(30),
	Price int,
	FlowerColorID int,
	GreenHouseID int,
	LastingTime int,
	Number int,
	[TypeID] int,
	FOREIGN KEY (FlowerColorID) REFERENCES Colour(colorID),
	FOREIGN KEY (GreenHouseID) REFERENCES GreenHouse(ID)
);

TRUNCATE TABLE Packaging;
GO
CREATE TABLE Packaging (
	ID int IDENTITY(1,1) PRIMARY KEY,
	PackagType varchar(20),
	PaperColorID int,
	StringColorID int,
	Design varchar(20),
	PackageDescription varchar(100),
	[Card] varchar(4),
	Check ([Card] in ('Yes','No')),
	Check (Design in ('designer','design shop')),
	FOREIGN KEY (PaperColorID) REFERENCES Colour(colorID)
);

TRUNCATE TABLE Buy;
GO
CREATE TABLE Buy (
	ID int IDENTITY(1,1) PRIMARY KEY,
	GreenHouseID int,
	SalesPersonID int,
	TotalPeyment int,
	BuyDate date,
	FOREIGN KEY (GreenHouseID) REFERENCES GreenHouse(ID),
);

TRUNCATE TABLE [Order];
GO
CREATE TABLE [Order] (
	ID int IDENTITY(1,1) PRIMARY KEY,
	CustomerID int,
	ShopDate date,
	OccasionID int,
	PackageID int,
	WrappingPrice int,
	Total int,
	Discount int,
	MoreInfo varchar(200),
	FOREIGN KEY (CustomerID) REFERENCES Customer(ID),
	FOREIGN KEY (PackageID) REFERENCES Packaging(ID),
	FOREIGN KEY (OccasionID) REFERENCES Occasion(ID)
);

TRUNCATE TABLE OrderDetail;
GO
CREATE TABLE OrderDetail (
	OrderID int IDENTITY(1,1) PRIMARY KEY,
	SalesPersonID int,
	FlowerName nvarchar(30),
	CustomerID int,
	GreenHouseID int,
	ShopDate date,
	Number int,
	Quanity int,
	Price int,
	Total int,
	FOREIGN KEY (OrderID) REFERENCES [Order](ID),
	FOREIGN KEY (CustomerID) REFERENCES Customer(ID),
	FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(PersonID)
);