database

USE Flower
Go

CREATE TABLE SalesPerson (
	PersonnelID varchar(10),
	[FirstName] varchar(20),
	[LastName] varchar(20),
	WorkingHour varchar(20),
	[Role] varchar(20) ,
	Salary int,
	BirthDate date,
	Gender varchar(8),
	HiringDate date ,
	PhoneNumber varchar(15),
	PRIMARY KEY (PersonnelID),
	Check ([Role] in ('florist','cashier','purchaser','ShopPerson','trainee','manager')),
	Check (Gender in  ('Female' , 'Male'))
);

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

CREATE TABLE FlowerType (
	[ID] int IDENTITY(1,1) PRIMARY KEY,
	Title varchar(20),
	WaterDescription varchar(200),
	LightDescription varchar(200),
	MoreInfo varchar(200)
);

select * from FlowerType

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 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 GreenHouse (
	ID int IDENTITY(1,1) PRIMARY KEY,
	[Name] varchar(20),
	[Manager] varchar(20),
	[Owner] varchar(20),
	[Address] varchar(100)
);

CREATE TABLE GreenHousePhones (
	GreenHouseID int ,
	ManagerPhone varchar(15),
	phone1 varchar(15),
	phone2 varchar(15),
	phone3 varchar(15),
	PRIMARY KEY (GreenHouseID,ManagerPhone),
	FOREIGN KEY (GreenHouseID) REFERENCES GreenHouse(ID)
);

CREATE TABLE Buy (
	ID int IDENTITY(1,1) PRIMARY KEY,
	GreenHouseID int,
	SalesPersonID varchar(10),
	TotalPeyment int,
	BuyDate date,
	FOREIGN KEY (GreenHouseID) REFERENCES GreenHouse(ID),
	FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(PersonnelID)
);
select * from Buy

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 ('I design myself','I trust on florist')),
	FOREIGN KEY (PaperColorID) REFERENCES Colour(colorID),
	FOREIGN KEY (StringColorID) REFERENCES Colour(colorID)
);

CREATE TABLE Flower (
	ID int IDENTITY(1,1) PRIMARY KEY,
	FlowerName varchar(30),
	Price int,
	FlowerColorID int,
	LastingTime int,
	Number int,
	[TypeID] int,
	FOREIGN KEY ([TypeID]) REFERENCES FlowerType(ID),
	FOREIGN KEY (FlowerColorID) REFERENCES Colour(colorID)
);
CREATE TABLE [Order] (
	ID int IDENTITY(1,1) PRIMARY KEY,
	CustomerID int,
	OrderType varchar(10),
	ShopDate date,
	OccasionID int,
	PackageID int,
	WrappingPrice int,
	TotalCost int,
	Discount int,
	FinalCost int,
	MoreInfo varchar(200),
	Check (Ordertype in ('Online','Not_Online')),
	FOREIGN KEY (CustomerID) REFERENCES Customer(ID),
	FOREIGN KEY (PackageID) REFERENCES Packaging(ID),
	FOREIGN KEY (OccasionID) REFERENCES Occasion(ID)
);

select * from [Order]

CREATE TABLE FlowersInOrder (
	OrderID int,
	FlowerID int,
	Number int,
	Price int,
	PRIMARY KEY (OrderID,FlowerID),
	FOREIGN KEY (FlowerID) REFERENCES Flower(ID),
);

create table BoughtFlower
(
	BuyID int,
	FlowerID int,
	Number int,
	Price int
	PRIMARY KEY (BuyID,FlowerID,Number),
	FOREIGN KEY (BuyID) REFERENCES Buy(ID),
	FOREIGN KEY (FlowerID) REFERENCES Flower(ID)
);

select * from Buy