tạo bảng

USE FlowerShop
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),
	More_Info varchar(200)
);

select * from FlowerType

CREATE TABLE Occasion (
	[ID] int IDENTITY(1,1) PRIMARY KEY,
	Title varchar(20) ,
	Occasion_Description 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),
	total_purchase 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,
	Buy_date 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,
	[Type_ID] int,
	FOREIGN KEY ([Type_ID]) 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,
	More_info 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