CREATE TABLE CUSTOMER (
	CustomerID	int			NOT NULL IDENTITY (1000,1),
	Name			char(25)	NOT NULL,
	Street			char(30)	NULL,
	City			char(35)	NULL,
	State			char(2)		NULL,
	ZipPostalCOde	char(9)		NULL,
	Country			varchar(50)	NULL,
	AreaCode		char(3)		NULL,
	PhoneNumber		char(8)		NULL,
	Email			char(100)	NULL,

	CONSTRAINT CustomerPK PRIMARY KEY (CustomerID),
	CONSTRAINT EmailAK1 UNIQUE (Email)
);

CREATE TABLE [TRANS] (
	TransactionID		int				NOT NULL IDENTITY (100,1),
	DateAcquired		datetime		NOT NULL,
	AcquisitionPrice	numeric(8,2)	NOT NULL,
	PurchaseDate		datetime		NULL,
	SalesPrice			numeric(8,2)	NULL,
	AskingPrice			numeric(8,2)	NULL,
	CustomerID			int				NULL,
	WorkID				int				NOT NULL,

	CONSTRAINT TransactionPK Primary KEY (TransactionID),
	CONSTRAINT TransactionWorkFK FOREIGN KEY (WorkID) REFERENCES Work (WorkID)
		ON UPDATE NO ACTION
		ON DELETE NO ACTION,
	CONSTRAINT TransactionCustomerFK FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID)
		ON UPDATE NO ACTION
		ON DELETE NO ACTION,
	CONSTRAINT SalesPriceRange CHECK
		((SalesPrice > 1000) AND (SalesPrice <= 200000)),
	CONSTRAINT ValidTransDate CHECK (PurchaseDate >= DateAcquired)
);

CREATE TABLE CUSTOMER_ARTIST_INT (
	ArtistID	int		NOT NULL,
	CustomerID	int		NOT NULL,

	CONSTRAINT CustomerArtistPK	PRIMARY KEY (ArtistID, CustomerID),
	CONSTRAINT Customer_Artist_Int_ArtistFK FOREIGN KEY (ArtistID) REFERENCES ARTIST (ArtistID)
		ON UPDATE NO ACTION
		ON DELETE CASCADE,
	CONSTRAINT Customer_Artist_Int_CustomerFK FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID)
		ON UPDATE NO ACTION
		ON DELETE CASCADE
);