nESh4FzC

1DROP DATABASE if exists petespaintWO;
2
3CREATE DATABASE petespaintWO;
4
5USE petespaintWO;
6
7DROP TABLE if exists customer;
8
9CREATE TABLE customer (
10custId INT NOT NULL AUTO_INCREMENT,
11first VARCHAR( 30 ) NOT NULL,
12last VARCHAR( 30 ) NOT NULL,
13email VARCHAR( 50 ) UNIQUE NOT NULL,
14constraint custid_pk primary key (custId),
15CONSTRAINT CHECK (email LIKE '%@%')
16) ENGINE = InnoDB;
17
18DROP TABLE if exists quote;
19
20CREATE Table quote(
21quoteId INT NOT NULL AUTO_INCREMENT,
22custId INT NOT NULL,
23typeJob VARCHAR( 50 ) NOT NULL ,
24typePaint VARCHAR( 50 ) NOT NULL ,
25color VARCHAR( 50 ) NOT NULL ,
26time INT NOT NULL,
27gallons INT NOT NULL,
28area INT NOT NULL,
29totalCost decimal(10,2) NOT NULL,
30constraint quoteId_pk primary key (quoteId),
31constraint custId_fk foreign key (custId) references customer(custId)
32) ENGINE = InnoDB;
33
34DROP TABLE if exists wall;
35
36CREATE TABLE wall(
37wallId INT NOT NULL AUTO_INCREMENT,
38quoteId INT NOT NULL,
39length INT NOT NULL,
40width INT NOT NULL,
41constraint wallId_pk primary key (wallId),
42constraint quoteId_fk foreign key (quoteId) references quote(quoteId),
43CONSTRAINT CHECK (length>0),
44CONSTRAINT CHECK (width>0)
45) ENGINE = InnoDB;