CREATE DATABASE transportation;
USE transportation;
CREATE TABLE customers (
ID int NOT NULL AUTO_INCREMENT,
customerName varchar(50) NOT NULL,
contactLastName varchar(50) NOT NULL,
contactFirstName varchar(50) NOT NULL,
phone char(10) NOT NULL,
address varchar(50) NOT NULL,
city varchar(50) NOT NULL,
state char(2) NOT NULL,
postalCode char(5) NOT NULL,
creditLimit decimal(10,2) DEFAULT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE employees (
ID int NOT NULL AUTO_INCREMENT,
firstName varchar(50) NOT NULL,
lastName varchar(50) NOT NULL,
extension char(10) NOT NULL,
email varchar(1000) NOT NULL,
jobTitle varchar(50) NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE offices (
ID int NOT NULL AUTO_INCREMENT,
city varchar(50) NOT NULL,
phone char(10) NOT NULL,
address varchar(50) NOT NULL,
state char(2) NOT NULL,
postalCode char(5) NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE orders (
ID int NOT NULL AUTO_INCREMENT,
orderDate date NOT NULL,
requiredDate date NOT NULL,
shippedDate date DEFAULT NULL,
status ENUM ('Shipped', 'Resolved', 'Cancelled', 'On Hold', 'Disputed', 'In Process') NOT NULL,
comments text,
PRIMARY KEY (ID)
);
CREATE TABLE productlines (
ID int NOT NULL AUTO_INCREMENT,
productLine varchar(50) NOT NULL,
textDescription varchar(4000) DEFAULT NULL,
htmlDescription text,
PRIMARY KEY (ID)
);
CREATE TABLE products (
ID int NOT NULL AUTO_INCREMENT,
productCode varchar(15) NOT NULL,
productName varchar(70) NOT NULL,
productScale varchar(10) NOT NULL,
productVendor varchar(50) NOT NULL,
productDescription text NOT NULL,
quantityInStock smallint NOT NULL,
buyPrice decimal(10,2) NOT NULL,
MSRP decimal(10,2) NOT NULL,
PRIMARY KEY (ID)
);