table

use ShopFlower
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'))
);

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'))	
);

CREATE TABLE Colour (
	ColorID int IDENTITY(100,1) PRIMARY KEY,
	ColorName varchar(20),
	HexCode varchar(10)
);

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'))
);

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

);

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)
);

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)
);
CREATE TABLE Buy (
	ID int IDENTITY(1,1) PRIMARY KEY,
	GreenHouseID int,
	SalesPersonID int,
	TotalPeyment int,
	BuyDate date,
	FOREIGN KEY (GreenHouseID) REFERENCES GreenHouse(ID),
	FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(PersonID)
);

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)
);

CREATE TABLE [OrderDetail] (
	OrderID int IDENTITY(1,1) PRIMARY KEY,
	SalesPersonID int,
	FlowerID int,
	CustomerID int,
	Quanity int,
	Price int,
	Total int,
	FOREIGN KEY (OrderID) REFERENCES [Order](ID),
	FOREIGN KEY (FlowerID) REFERENCES Flower(ID),
	FOREIGN KEY (CustomerID) REFERENCES Customer(ID),
	FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(PersonID)
);