Miscellany

CREATE TABLE contacts (	contactID	mediumint unsigned not null,	firstName	varchar(30),	lastName	varchar(30),	primaryPhone	varchar(30),	cellPhone	varchar(30),	email		varchar(30),	discount	float default (0),	bornOn		datetime,	knownSince	datetime,	lastContact	datetime,	locationID	mediumint unsigned references locations(locationID),primary key (contactID))CREATE TABLE businesses (	bizID		smallint unsigned not null,	businessName	varchar(30) not null,	businessDesc	varchar(30),	primaryPhone	bigint unsigned,	bURL		varchar,	contactID	int unsigned unsigned references contacts(contactsID),	locationID	mediumint unsigned references locations(locationID),primary key (bizID))CREATE TABLE suppliers,	supID		smallint unsigned,	supplierName	varchar(30) not null,	supplierType	varchar(30),	primaryPhone	varchar(30),	orderByPhone	varchar(30),	orderURL	varchar,	ourAccountInfo	varchar,	contactID	mediumint unsigned references contacts(contactsID),	locationID	mediumint unsigned references locations(locationID),primary key (supID))CREATE TABLE publishers (	pubID		smallint unsigned,not null,	publisherName	varchar not null,	publisherType	varchar(30),	publisherNum	varchar(30),	contactID	mediumint unsigned references contacts(contactsID),	locationID	mediumint unsigned references locations(locationID),primary key(pubID))CREATE TABLE employees (	employeeID	smallint unsigned not null,	contactID	mediumint unsigned not null references contacts(contactsID),	title		varchar(30) not null,	wage		float(8,2) unsigned not null,	overtimeRate	float(5,2) unsigned,	hiredOn		datetime not null,	s		char(1) not null check (s='f' or s='p' or s='o' or s='t') default('f'),primary key (employeeID))CREATE TABLE locations (	locationID	mediumint unsigned not null,	street		varchar(30) not null,	zipCode		varchar(30) not null,	state		char(2) not null default('nj').	locPhone	varchar(30),	wantsMail	boolean default(0),primary key (locationID))CREATE TABLE art (	artID		mediumint unsigned not null,	sellPrice	float(9,2) unsigned not null,	cid		tinyint unsigned not null,	did		mediumint unsigned not null references demension(did),	qty		tinyint unsigned not null default (1),	pic		image,	artInfoID	mediumint unsigned,primary key (artID))CREATE TABLE categories (	cid		tinyint unsigned not null,	cName		varchar(30) not null,primary key (cid))CREATE TABLE displayGallery (	dispID		smallint unsigned not null,	artID		mediumint unsigned null references art(artID),	onDate		datetime,	offdate		datetime,	forSale		boolean not null default(1),	frameID		smallint unsigned references frames(frameID),primary key (dispID))CREATE TABLE artists (	artistID	mediumint unsigned not null,	firstName	varchar(30),	middleName	varchar(30),	surName		varchar(30) not null,	alias		varchar(30),	cid		tinyint unsigned not null references categories(cid),	bornOn		datetime,	diedOn		datatime,	biodesc		text,primary key (artistID))CREATE TABLE artInfo (	artInfoID	mediumint unsigned not null,	artistID	mediumint unsigned references artists(artistID),	commonTitle	varchar(30),	aliasTitle	varchar(3),	artDesc		varchar(30),	medium		varchar(30) not null,	publicationDate	datetime,	publisherID	smallint unsigned references publishers(publisherID),	condition	varchar(30) default('new'),primary key (artInfoID))CREATE TABLE frames (	frameID		smallint unsigned not null,	frameStyle	varchar(8) not null,	did		int not null references demensions(did),	qtyOnHand	tinyint unsigned,	pricePer	float(8,2) not null,primary key (frameID))CREATE TABLE dimensions (	did		mediumint not null,	heightInches	float(5,2) unsigned,	lengthInches	float(5,2) unsigned,	widthInches	float(5,2) unsigned,	weightLbs	float(5,2) unsigned,primary key (did))CREATE TABLE sales (	saleID		mediumint unsigned not null,	saleDate	datetime not null,	employeeid	smallint unsigned not null references employees(employeeID),	artID		mediumint unsigned references art(artID),	frameID		smallint unsigned references frames(frameID),	qty		tinyint unsigned default (1),	contactID	mediumint unsigned references contacts(contactID),	saleTotal	float(9,2) unsigned not null,primary key (saleID))CREATE TABLE purchaseOrders (	poNum		smallint unsigned not null,	employeeID	smallint unsigned not null references employees(employeeID),	purchaseDate	datetime not null,	supID		smallint unsigned references suppliers(supID),	purchaseDesc	varchar,	qty		tinyint unsigned not null,	totalCost	float(9,2) unsigned not null,	confirmNum	varchar,	trackingNum	varchar,	s		char(1) check(s='p' or s='r' or s='c') not null default('p'),primary key (poNum))CREATE TABLE jobs (	jobID		smallint unsigned not null,	saleID		mediumint unsigned,	dueDate		datetime not null,	type		varchar,	locationID	mediumint unsigned references locations(locationID),	servicedocsDIR	varchar,	notes		tinytext,primary key (jobID, saleID))