text: USE GordenDB
  GO
  CREATE OR ALTER PROC NewDatabase
  AS
  --------------------------------------------------------------------------------------------------
  /*Query Create Table*/
  -- Permission 0 - admin, Permission 1 - user
  CREATE TABLE Users
  (
  	Id_User INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Nama VARCHAR(20) NOT NULL,
  	Password CHAR(32) NOT NULL,
  	Permission INT NOT NULL
  )
  
  CREATE TABLE Kain
  (
  	Id_Kain INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	NomorKarung INT NOT NULL,
  	Meter FLOAT NOT NULL,
  	TanggalMasuk DATE NOT NULL,
  	TanggalKeluar DATE,
  	Status TINYINT NOT NULL,
  )
  
  CREATE TABLE Pembeli
  (
  	Id_Pembeli INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Nama VARCHAR(50) NOT NULL,
  	Alamat VARCHAR(50) NOT NULL
  )
  
  CREATE TABLE Penjual
  (
  	Id_Penjual INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Nama VARCHAR(50) NOT NULL,
  	Kode VARCHAR(50) NOT NULL
  )
  
  CREATE TABLE JenisKain
  (
  	Id_JenisKain INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Nama VARCHAR(50) NOT NULL
  )
  
  CREATE TABLE Warna
  (
  	Id_Warna INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Nama VARCHAR(50) NOT NULL,
  	NomorWarna INT
  )
  
  CREATE TABLE Sampel
  (
  	Id_Sampel INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Nama VARCHAR(50) NOT NULL,
  	Id_JenisKain INT NOT NULL FOREIGN KEY REFERENCES JenisKain(Id_JenisKain)
  )
  
  CREATE TABLE KainSampelWarna
  (
  	Id_Kain INT NOT NULL FOREIGN KEY REFERENCES Kain(Id_Kain),
  	Id_Sampel INT NOT NULL FOREIGN KEY REFERENCES Sampel(Id_Sampel),
  	Id_Warna INT NOT NULL FOREIGN KEY REFERENCES Warna(Id_Warna),
  	PRIMARY KEY (Id_Kain,Id_Sampel,Id_Warna)
  )
  
  CREATE TABLE SampelWarna
  (
  	Id_Sampel INT NOT NULL FOREIGN KEY REFERENCES Sampel(Id_Sampel),
  	Id_Warna INT NOT NULL FOREIGN KEY REFERENCES Warna(Id_Warna),
  	PRIMARY KEY (Id_Sampel,Id_Warna)
  )
  
  CREATE TABLE SuratOrder
  (
  	No_SO INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Tanggal DATE NOT NULL,
  	Id_Penjual INT NOT NULL FOREIGN KEY REFERENCES Penjual(Id_Penjual),
  	Id_Pembeli INT NOT NULL FOREIGN KEY REFERENCES Pembeli(Id_Pembeli),
  	Keterangan VARCHAR(500),
  	Status TINYINT NOT NULL,
  )
  
  CREATE TABLE ListSampelSO
  (
  	No_SO INT NOT NULL FOREIGN KEY REFERENCES SuratOrder(No_SO),
  	Id_Sampel INT NOT NULL FOREIGN KEY REFERENCES Sampel(Id_Sampel),
  	Id_Warna INT NOT NULL FOREIGN KEY REFERENCES Warna(Id_Warna),
  	Total_Pcs INT NOT NULL,
  	-- PRIMARY KEY (No_SO,Id_Sampel,Id_Warna)
  )
  
  CREATE TABLE PurchaseOrder
  (
  	No_PO INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Tanggal DATE NOT NULL,
  	Id_Penjual INT NOT NULL FOREIGN KEY REFERENCES Penjual(Id_Penjual),
  	Id_Pembeli INT NOT NULL FOREIGN KEY REFERENCES Pembeli(Id_Pembeli),
  	Total_Pcs INT,
  	Total_Meter FLOAT,
  	Status INT,
  	No_SO INT NOT NULL FOREIGN KEY REFERENCES SuratOrder(No_SO),
  	KeteranganPO VARCHAR(500)
  )
  
  CREATE TABLE SuratJalan
  (
  	No_SJ INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Tanggal DATE NOT NULL,
  	No_PO INT NOT NULL FOREIGN KEY REFERENCES PurchaseOrder(No_PO),
  	Keterangan VARCHAR(500)
  )
  
  /* table retur */
  CREATE TABLE ListKainPO
  (
  	No_PO INT NOT NULL FOREIGN KEY REFERENCES PurchaseOrder(No_PO),
  	Id_Kain INT NOT NULL FOREIGN KEY REFERENCES Kain(Id_Kain),
  	StatusRetur TINYINT NOT NULL,
  	PRIMARY KEY	(No_PO,Id_Kain)
  )
  
  CREATE TABLE Retur
  (
  	No_Retur INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  	Tanggal DATE NOT NULL,
  	No_PO INT FOREIGN KEY REFERENCES PurchaseOrder(No_PO),
  	Keterangan VARCHAR(500),
  	Total_Pcs INT ,
  	Total_Meter FLOAT
  )
  
  GO
  
  --exec NewDatabase