table

use ShopFlower
go

CREATE TABLE SalesPerson (
	PersonID int  PRIMARY KEY,
	[first_name] nvarchar(20),
	[last_name] nvarchar(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] nvarchar(20),
	[LastName] nvarchar(20),
	Addess nvarchar(50),
	Phone nvarchar(15),
	BirthDate date,
	Gender varchar(8),
	TotalPurchase int,
	Check (Gender in  ('Female' , 'Male'))	
);

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

CREATE TABLE Occasion (
	[ID] int IDENTITY(1,1) PRIMARY KEY,
	Title nvarchar(20) ,
	OccasionDescription nvarchar(100),
	check (Title in ('Sinh Nhật','Valentine','Chúc Mừng','Other'))
);

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

);

CREATE TABLE Flower (
	ID int IDENTITY(1,1) PRIMARY KEY,
	FlowerName nvarchar(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 Product (
  ID int PRIMARY KEY,
  FlowerName NVARCHAR(255),
  Linkimage VARCHAR(255),
  Quantity int,
  Price int,
  [Description] NVARCHAR(255),
  FlowerNameID int,
  FOREIGN KEY (FlowerNameID) REFERENCES Flower(ID),
);

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

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

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