CREATE TABLE PhoneType( 
    PhoneTypeID int(1) NOT NULL,
    PhoneTypeName varchar(16) NOT NULL UNIQUE,
    PRIMARY KEY (PhoneTypeID)
);
CREATE TABLE Region( 
    RegionID int(2) NOT NULL,
    RegionName varchar(32) NOT NULL UNIQUE,
    PRIMARY KEY (RegionID)
);
CREATE TABLE Phone( 
    PhoneID int(4) NOT NULL,
    PhoneNo varchar(32) NOT NULL UNIQUE,
    PhoneTypeID int(1) NOT NULL,
    PRIMARY KEY (PhoneID),
    FOREIGN KEY (PhoneTypeID) REFERENCES PhoneType (PhoneTypeID)
);
CREATE TABLE Customer( 
    CustomerID int(4) NOT NULL,
    CustomerName varchar(254) NOT NULL UNIQUE,
    AddressID int(4) NOT NULL,
    PhoneID int(4),
    PRIMARY KEY (CustomerID),
    FOREIGN KEY (AddressID) REFERENCES Address (AddressID),
    FOREIGN KEY (PhoneID) REFERENCES Phone (PhoneID)
);
CREATE TABLE City( 
    CityID int(4) NOT NULL,
    CityName varchar(64) NOT NULL UNIQUE,
    RegionID int(2) NOT NULL,
    PRIMARY KEY (CityID),
    FOREIGN KEY (RegionID) REFERENCES Region (RegionID)
);
CREATE TABLE ZIP( 
    ZIPCode varchar(16) NOT NULL,
    CityID int(4) NOT NULL,
    PRIMARY KEY (ZIPCode),
    FOREIGN KEY (CityID) REFERENCES City (CityID)
);
CREATE TABLE Address( 
    AddressID int(4) NOT NULL,
    Street varchar(254) NOT NULL,
    ZIPCode varchar(16) NOT NULL,
    PRIMARY KEY (AddressID),
    FOREIGN KEY (ZIPCode) REFERENCES ZIP (ZIPCode)
);
CREATE TABLE ContactPerson( 
    CustomerID int(4) NOT NULL,
    PersonID int(4) NOT NULL,
    PRIMARY KEY (CustomerID,PersonID),
    FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID),
    FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
);
CREATE TABLE Person( 
    PersonID int(4) NOT NULL,
    PersonChristianName varchar(254) NOT NULL,
    PersonFamilyName varchar(254) NOT NULL,
    PersonTitle varchar(4),
    PhoneID int(4),
    AddressID int(4),
    PersonAge decimal(5, 2),
    PersonPicture blob,
    PRIMARY KEY (PersonID),
    FOREIGN KEY (AddressID) REFERENCES Address (AddressID),
    FOREIGN KEY (PhoneID) REFERENCES Phone (PhoneID)
);
