CREATE TABLE PUBLIC.TEL_TYPE (
 TYPE VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 TYPE
 ),
 UNIQUE (
 TYPE
 )
 );
 CREATE INDEX IDX_TEL_TYPETYPE ON PUBLIC.TEL_TYPE (TYPE);
 ;
CREATE TABLE PUBLIC.ADR_TYPE (
 TYPE VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 TYPE
 ),
 UNIQUE (
 TYPE
 )
 );
 CREATE INDEX IDX_ADR_TYPETYPE ON PUBLIC.ADR_TYPE (TYPE);
 ;
CREATE TABLE PUBLIC.ORGANIZATION (
 DESCRIPTION VARCHAR(255),
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID
 )
 );
 CREATE INDEX IDX_ORGANIZATIONID ON PUBLIC.ORGANIZATION (ID);
 ;
CREATE TABLE PUBLIC.EMAIL_TYPE (
 TYPE VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 TYPE
 ),
 UNIQUE (
 TYPE
 )
 );
 CREATE INDEX IDX_EMAIL_TYPETYPE ON PUBLIC.EMAIL_TYPE (TYPE);
 ;
CREATE TABLE PUBLIC.ROLODEX (
 DESCRIPTION VARCHAR(255),
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID
 )
 );
 CREATE INDEX IDX_ROLODEXID ON PUBLIC.ROLODEX (ID);
 ;
CREATE TABLE PUBLIC.TITLE (
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID
 )
 );
 CREATE INDEX IDX_TITLEID ON PUBLIC.TITLE (ID);
 ;
CREATE TABLE PUBLIC.ROLE (
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID
 )
 );
 CREATE INDEX IDX_ROLEID ON PUBLIC.ROLE (ID);
 ;
CREATE TABLE PUBLIC.VCARD (
 AGENT VARCHAR(255),
 BIRTH_DAY TIMESTAMP,
 ORGANIZATION_ID INTEGER,
 ROLE_ID INTEGER,
 ROLODEX_ID INTEGER,
 TITLE_ID INTEGER,
 UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 UNIQUE_NAME
 ),
 UNIQUE (
 AGENT,
 ORGANIZATION_ID,
 ROLE_ID,
 ROLODEX_ID,
 TITLE_ID,
 UNIQUE_NAME
 ),
 FOREIGN KEY (AGENT) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE ,
 FOREIGN KEY (ORGANIZATION_ID) REFERENCES ORGANIZATION (ID) ,
 FOREIGN KEY (ROLE_ID) REFERENCES ROLE (ID) ,
 FOREIGN KEY (ROLODEX_ID) REFERENCES ROLODEX (ID) ,
 FOREIGN KEY (TITLE_ID) REFERENCES TITLE (ID) 
 );
 CREATE INDEX IDX_VCARDAGENT ON PUBLIC.VCARD (AGENT);
 CREATE INDEX IDX_VCARDORGANIZATION_ID ON PUBLIC.VCARD (ORGANIZATION_ID);
 CREATE INDEX IDX_VCARDROLE_ID ON PUBLIC.VCARD (ROLE_ID);
 CREATE INDEX IDX_VCARDROLODEX_ID ON PUBLIC.VCARD (ROLODEX_ID);
 CREATE INDEX IDX_VCARDTITLE_ID ON PUBLIC.VCARD (TITLE_ID);
 CREATE INDEX IDX_VCARDUNIQUE_NAME ON PUBLIC.VCARD (UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.GIVEN_NAME (
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 VCARD_UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 VCARD_UNIQUE_NAME
 ),
 FOREIGN KEY (VCARD_UNIQUE_NAME) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE 
 );
 CREATE INDEX IDX_GIVEN_NAMEID ON PUBLIC.GIVEN_NAME (ID);
 CREATE INDEX IDX_GIVEN_NAMEVCARD_UNIQUE_NAME ON PUBLIC.GIVEN_NAME (VCARD_UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.H_PREFIX_NAME (
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 VCARD_UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 VCARD_UNIQUE_NAME
 ),
 FOREIGN KEY (VCARD_UNIQUE_NAME) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE 
 );
 CREATE INDEX IDX_H_PREFIX_NAMEID ON PUBLIC.H_PREFIX_NAME (ID);
 CREATE INDEX IDX_H_PREFIX_NAMEVCARD_UNIQUE_NAME ON PUBLIC.H_PREFIX_NAME (VCARD_UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.NICK_NAME (
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 VCARD_UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 VCARD_UNIQUE_NAME
 ),
 FOREIGN KEY (VCARD_UNIQUE_NAME) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE 
 );
 CREATE INDEX IDX_NICK_NAMEID ON PUBLIC.NICK_NAME (ID);
 CREATE INDEX IDX_NICK_NAMEVCARD_UNIQUE_NAME ON PUBLIC.NICK_NAME (VCARD_UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.EMAIL (
 ADDRESS VARCHAR(255),
 ID INTEGER NOT NULL
 IDENTITY,
 VCARD_UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 VCARD_UNIQUE_NAME
 ),
 FOREIGN KEY (VCARD_UNIQUE_NAME) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE 
 );
 CREATE INDEX IDX_EMAILID ON PUBLIC.EMAIL (ID);
 CREATE INDEX IDX_EMAILVCARD_UNIQUE_NAME ON PUBLIC.EMAIL (VCARD_UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.R_EMAIL_EMAIL_TYPE (
 EMAIL_ID INTEGER NOT NULL,
 EMAIL_TYPE_TYPE VARCHAR(255) NOT NULL,
 ID INTEGER NOT NULL
 IDENTITY,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 EMAIL_ID,
 EMAIL_TYPE_TYPE,
 ID
 ),
 FOREIGN KEY (EMAIL_ID) REFERENCES EMAIL (ID) ON DELETE CASCADE ,
 FOREIGN KEY (EMAIL_TYPE_TYPE) REFERENCES EMAIL_TYPE (TYPE) ON DELETE CASCADE 
 );
 CREATE INDEX IDX_R_EMAIL_EMAIL_TYPEEMAIL_ID ON PUBLIC.R_EMAIL_EMAIL_TYPE (EMAIL_ID);
 CREATE INDEX IDX_R_EMAIL_EMAIL_TYPEEMAIL_TYPE_TYPE ON PUBLIC.R_EMAIL_EMAIL_TYPE (EMAIL_TYPE_TYPE);
 CREATE INDEX IDX_R_EMAIL_EMAIL_TYPEID ON PUBLIC.R_EMAIL_EMAIL_TYPE (ID);
 ;
CREATE TABLE PUBLIC.ADDITIONAL_NAME (
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 VCARD_UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 VCARD_UNIQUE_NAME
 ),
 FOREIGN KEY (VCARD_UNIQUE_NAME) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE 
 );
 CREATE INDEX IDX_ADDITIONAL_NAMEID ON PUBLIC.ADDITIONAL_NAME (ID);
 CREATE INDEX IDX_ADDITIONAL_NAMEVCARD_UNIQUE_NAME ON PUBLIC.ADDITIONAL_NAME (VCARD_UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.ADR (
 COUNTRY_NAME VARCHAR(255),
 EXTENDED_ADDRESS VARCHAR(255),
 ID INTEGER NOT NULL
 IDENTITY,
 LOCALITY VARCHAR(255),
 POSTAL_CODE VARCHAR(255),
 P_O_BOX VARCHAR(255),
 REGION VARCHAR(255),
 STREET_ADDRESS VARCHAR(255),
 VCARD_UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 VCARD_UNIQUE_NAME
 ),
 FOREIGN KEY (VCARD_UNIQUE_NAME) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE 
 );
 CREATE INDEX IDX_ADRID ON PUBLIC.ADR (ID);
 CREATE INDEX IDX_ADRVCARD_UNIQUE_NAME ON PUBLIC.ADR (VCARD_UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.R_ADR_ADR_TYPE (
 ADR_ID INTEGER NOT NULL,
 ADR_TYPE_TYPE VARCHAR(255) NOT NULL,
 ID INTEGER NOT NULL
 IDENTITY,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ADR_ID,
 ADR_TYPE_TYPE,
 ID
 ),
 FOREIGN KEY (ADR_ID) REFERENCES ADR (ID) ON DELETE CASCADE ,
 FOREIGN KEY (ADR_TYPE_TYPE) REFERENCES ADR_TYPE (TYPE) ON DELETE CASCADE 
 );
 CREATE INDEX IDX_R_ADR_ADR_TYPEADR_ID ON PUBLIC.R_ADR_ADR_TYPE (ADR_ID);
 CREATE INDEX IDX_R_ADR_ADR_TYPEADR_TYPE_TYPE ON PUBLIC.R_ADR_ADR_TYPE (ADR_TYPE_TYPE);
 CREATE INDEX IDX_R_ADR_ADR_TYPEID ON PUBLIC.R_ADR_ADR_TYPE (ID);
 ;
CREATE TABLE PUBLIC.FAMILY_NAME (
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 VCARD_UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 VCARD_UNIQUE_NAME
 ),
 FOREIGN KEY (VCARD_UNIQUE_NAME) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE 
 );
 CREATE INDEX IDX_FAMILY_NAMEID ON PUBLIC.FAMILY_NAME (ID);
 CREATE INDEX IDX_FAMILY_NAMEVCARD_UNIQUE_NAME ON PUBLIC.FAMILY_NAME (VCARD_UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.H_SUFFIX_NAME (
 ID INTEGER NOT NULL
 IDENTITY,
 NAME VARCHAR(255),
 VCARD_UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 VCARD_UNIQUE_NAME
 ),
 FOREIGN KEY (VCARD_UNIQUE_NAME) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE 
 );
 CREATE INDEX IDX_H_SUFFIX_NAMEID ON PUBLIC.H_SUFFIX_NAME (ID);
 CREATE INDEX IDX_H_SUFFIX_NAMEVCARD_UNIQUE_NAME ON PUBLIC.H_SUFFIX_NAME (VCARD_UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.TEL (
 AREA_CODE DECIMAL,
 COUNTRY_CODE DECIMAL,
 EXTENSION VARCHAR(255),
 ID INTEGER NOT NULL
 IDENTITY,
 VCARD_UNIQUE_NAME VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 VCARD_UNIQUE_NAME
 ),
 FOREIGN KEY (VCARD_UNIQUE_NAME) REFERENCES VCARD (UNIQUE_NAME) ON UPDATE CASCADE ON DELETE CASCADE 
 );
 CREATE INDEX IDX_TELID ON PUBLIC.TEL (ID);
 CREATE INDEX IDX_TELVCARD_UNIQUE_NAME ON PUBLIC.TEL (VCARD_UNIQUE_NAME);
 ;
CREATE TABLE PUBLIC.R_TEL_TEL_TYPE (
 ID INTEGER NOT NULL
 IDENTITY,
 TEL_ID INTEGER NOT NULL,
 TEL_TYPE_TYPE VARCHAR(255) NOT NULL,
 PRIMARY KEY (
 ID
 ),
 UNIQUE (
 ID,
 TEL_ID,
 TEL_TYPE_TYPE
 ),
 FOREIGN KEY (TEL_ID) REFERENCES TEL (ID) ON DELETE CASCADE ,
 FOREIGN KEY (TEL_TYPE_TYPE) REFERENCES TEL_TYPE (TYPE) ON DELETE CASCADE 
 );
 CREATE INDEX IDX_R_TEL_TEL_TYPEID ON PUBLIC.R_TEL_TEL_TYPE (ID);
 CREATE INDEX IDX_R_TEL_TEL_TYPETEL_ID ON PUBLIC.R_TEL_TEL_TYPE (TEL_ID);
 CREATE INDEX IDX_R_TEL_TEL_TYPETEL_TYPE_TYPE ON PUBLIC.R_TEL_TEL_TYPE (TEL_TYPE_TYPE);
 ;
CREATE VIEW PUBLIC.TEL_WORK_VOICE_PREF (AREA_CODE, COUNTRY_CODE, NAME, TEL_EXTENSION) AS SELECT DISTINCT TEL.VCARD_UNIQUE_NAME, TEL.COUNTRY_CODE, TEL.AREA_CODE, TEL.EXTENSION FROM R_TEL_TEL_TYPE LEFT JOIN TEL ON R_TEL_TEL_TYPE.TEL_ID=TEL.ID  LEFT JOIN R_TEL_TEL_TYPE AS R_TEL_TEL_TYPE2 ON R_TEL_TEL_TYPE.TEL_ID=R_TEL_TEL_TYPE2.TEL_ID LEFT JOIN R_TEL_TEL_TYPE AS R_TEL_TEL_TYPE3 ON R_TEL_TEL_TYPE2.TEL_ID=R_TEL_TEL_TYPE3.TEL_ID WHERE R_TEL_TEL_TYPE.TEL_TYPE_TYPE='pref' AND R_TEL_TEL_TYPE2.TEL_TYPE_TYPE='work' AND R_TEL_TEL_TYPE3.TEL_TYPE_TYPE='voice';
CREATE VIEW PUBLIC.TEL_WORK_FAX_PREF (AREA_CODE, COUNTRY_CODE, NAME, TEL_EXTENSION) AS SELECT DISTINCT TEL.VCARD_UNIQUE_NAME, TEL.COUNTRY_CODE, TEL.AREA_CODE, TEL.EXTENSION FROM R_TEL_TEL_TYPE LEFT JOIN TEL ON R_TEL_TEL_TYPE.TEL_ID=TEL.ID  LEFT JOIN R_TEL_TEL_TYPE AS R_TEL_TEL_TYPE2 ON R_TEL_TEL_TYPE.TEL_ID=R_TEL_TEL_TYPE2.TEL_ID LEFT JOIN R_TEL_TEL_TYPE AS R_TEL_TEL_TYPE3 ON R_TEL_TEL_TYPE2.TEL_ID=R_TEL_TEL_TYPE3.TEL_ID WHERE R_TEL_TEL_TYPE.TEL_TYPE_TYPE='pref' AND R_TEL_TEL_TYPE2.TEL_TYPE_TYPE='work' AND R_TEL_TEL_TYPE3.TEL_TYPE_TYPE='fax';
CREATE VIEW PUBLIC.TEL_HOME_VOICE_PREF (AREA_CODE, COUNTRY_CODE, NAME, TEL_EXTENSION) AS SELECT DISTINCT TEL.VCARD_UNIQUE_NAME, TEL.COUNTRY_CODE, TEL.AREA_CODE, TEL.EXTENSION FROM R_TEL_TEL_TYPE LEFT JOIN TEL ON R_TEL_TEL_TYPE.TEL_ID=TEL.ID  LEFT JOIN R_TEL_TEL_TYPE AS R_TEL_TEL_TYPE2 ON R_TEL_TEL_TYPE.TEL_ID=R_TEL_TEL_TYPE2.TEL_ID LEFT JOIN R_TEL_TEL_TYPE AS R_TEL_TEL_TYPE3 ON R_TEL_TEL_TYPE2.TEL_ID=R_TEL_TEL_TYPE3.TEL_ID WHERE R_TEL_TEL_TYPE.TEL_TYPE_TYPE='pref' AND R_TEL_TEL_TYPE2.TEL_TYPE_TYPE='home' AND R_TEL_TEL_TYPE3.TEL_TYPE_TYPE='voice';
