Code

DROP DATABASE if exists petespaintWO;

CREATE DATABASE petespaintWO;

USE petespaintWO;

DROP TABLE if exists customer;

CREATE TABLE customer (
custId INT NOT NULL AUTO_INCREMENT,
first VARCHAR( 30 ) NOT NULL,
last VARCHAR( 30 ) NOT NULL,
email VARCHAR( 50 ) UNIQUE NOT NULL,
constraint custid_pk primary key (custId),
CONSTRAINT CHECK (email LIKE '%@%')
) ENGINE = InnoDB;

DROP TABLE if exists quote;

CREATE Table quote(
quoteId INT NOT NULL AUTO_INCREMENT,
custId INT NOT NULL,
typeJob VARCHAR( 50 ) NOT NULL ,
typePaint VARCHAR( 50 ) NOT NULL ,
color VARCHAR( 50 ) NOT NULL ,
time INT NOT NULL,
gallons INT NOT NULL,
area INT NOT NULL,
totalCost decimal(10,2) NOT NULL,
constraint quoteId_pk primary key (quoteId),
constraint custId_fk foreign key (custId) references customer(custId)
) ENGINE = InnoDB;

DROP TABLE if exists wall;

CREATE TABLE wall(
wallId INT NOT NULL AUTO_INCREMENT,
quoteId INT NOT NULL,
length INT NOT NULL,
width INT NOT NULL,
constraint wallId_pk primary key (wallId),
constraint quoteId_fk foreign key (quoteId) references quote(quoteId),
CONSTRAINT CHECK (length>0),
CONSTRAINT CHECK (width>0)
) ENGINE = InnoDB;