USE master
--DROP DATABASE AddressDB
CREATE DATABASE AddressDB

USE AddressDB
IF OBJECT_ID('AddrStreetTypes') IS NOT NULL DROP TABLE AddrStreetTypes

CREATE TABLE AddrStreetTypes
(
    ID int primary key IDENTITY(1,1) NOT NULL,
	StreetType varchar(10) NOT NULL
)
Insert into AddrStreetTypes values ('RUE')
Insert into AddrStreetTypes values ('AVE')
Insert into AddrStreetTypes values ('BLVD')
Insert into AddrStreetTypes values ('PL')
Insert into AddrStreetTypes values ('DR')
Insert into AddrStreetTypes values ('CRES')
Insert into AddrStreetTypes values ('ST')
Insert into AddrStreetTypes values ('RD')
Insert into AddrStreetTypes values ('ROAD')
Insert into AddrStreetTypes values ('CH')
Insert into AddrStreetTypes values ('STREET')
Insert into AddrStreetTypes values ('CRT')
Insert into AddrStreetTypes values ('WAY')
Insert into AddrStreetTypes values ('AV')
Insert into AddrStreetTypes values ('BOUL')
Insert into AddrStreetTypes values ('LANE')
Insert into AddrStreetTypes values ('CIR')
Insert into AddrStreetTypes values ('SQ')
Insert into AddrStreetTypes values ('CLSE')
Insert into AddrStreetTypes values ('RD.')

Select * from AddrStreetTypes

IF OBJECT_ID('AddrStreetDirections') IS NOT NULL DROP TABLE AddrStreetDirections

CREATE TABLE AddrStreetDirections
(
    ID int primary key IDENTITY(1,1) NOT NULL,
	StreetDirection varchar(5) NOT NULL
)
Insert into AddrStreetDirections values ('E')
Insert into AddrStreetDirections values ('W')
Insert into AddrStreetDirections values ('N')
Insert into AddrStreetDirections values ('S')
Insert into AddrStreetDirections values ('NE')
Insert into AddrStreetDirections values ('NW')
Insert into AddrStreetDirections values ('SE')
Insert into AddrStreetDirections values ('SW')

Select * from AddrStreetDirections

IF OBJECT_ID('AddrSuites') IS NOT NULL DROP TABLE AddrSuites

CREATE TABLE AddrSuites
(
    ID int primary key IDENTITY(1,1) NOT NULL,
	Suite varchar(10) NOT NULL
)
Insert into AddrSuites values ('APT')
Insert into AddrSuites values ('UNIT')
Insert into AddrSuites values ('SUITE')

Select * from AddrSuites

IF OBJECT_ID('AddrProvinces') IS NOT NULL DROP TABLE AddrProvinces

CREATE TABLE AddrProvinces
(
    ID int primary key IDENTITY(1,1) NOT NULL,
	Province varchar(10) NOT NULL
)
Insert into AddrProvinces values ('AB')
Insert into AddrProvinces values ('BC')
Insert into AddrProvinces values ('MB')
Insert into AddrProvinces values ('NB')
Insert into AddrProvinces values ('NL')
Insert into AddrProvinces values ('NT')
Insert into AddrProvinces values ('NS')
Insert into AddrProvinces values ('NU')
Insert into AddrProvinces values ('ON')
Insert into AddrProvinces values ('PE')
Insert into AddrProvinces values ('QC')
Insert into AddrProvinces values ('SK')
Insert into AddrProvinces values ('YT')
Insert into AddrProvinces values ('ONT')
Insert into AddrProvinces values ('ONTARIO')
Insert into AddrProvinces values ('QUEBEC')
Insert into AddrProvinces values ('QUE')

Select * from AddrProvinces

