SET ECHO ON;

DROP TABLE usageitem;

DROP TABLE envstat;

DROP TABLE property;

DROP TABLE contitem;

DROP TABLE devsearchresult;

DROP TABLE taxinfo;

DROP TABLE fininfo;

DROP TABLE empactivity;

DROP TABLE usage;

DROP TABLE type;

DROP TABLE broker;

DROP TABLE zoning;

DROP TABLE owner;

DROP TABLE employee;

DROP TABLE developer;


DROP TABLE contaminants;
DROP TABLE jurisdiction;
DROP TABLE streetdesig;
DROP TABLE zipadd;

/*********************************************************************************/
 
rem Table               : zipadd
rem Description         : Lookup table for City, State, Zip
rem zipid               : Artifical PK since certain zips map to multiple cities 
rem zip                 : The Zip Code 
rem state               : The State 
rem city                : The City 

create table zipadd (
    zipid           number (6) not null,
    zip             char (5) not null,
    state           char (2) not null,
    city            varchar2 (20) not null,
constraint pk_zipadd primary key (zipid));
 
/*********************************************************************************/

rem Table               : streetdesig
rem Description         : Street Designation
rem desid               : Artifical PK  
rem description         : Description (e.g., arterial, freeway, etc.) 

create table streetdesig (
    desid           number (3) not null,
    description     varchar2 (25) not null,
constraint pk_streetdesig primary key (desid));
 
/*********************************************************************************/
 

rem Table               : jurisdiction
rem Description         : Jurisdiction that property is in
rem jurisid             : Artifical PK 
rem jurisname           : Name of Jurisdiction 
rem lastname            : Last Name 
rem firstname           : First Name 
rem middlei             : Middle Initial 
rem street              : Street Address 
rem phone               : Phone Number 
rem email               : Email 

create table jurisdiction (
    jurisid         number (6) not null,
    jurisname       varchar2 (20) not null,
    lastname        varchar2 (20),
    firstname       varchar2 (20),
    middlei         char (1),
    street          varchar2 (20) not null,
    phone           char (10),
    email           varchar2 (20),
zipid           number (6) ,
constraint pk_jurisdiction primary key (jurisid),
constraint fk_jurisdiction foreign key (zipid)
    references zipadd (zipid));

/*********************************************************************************/ 
 
rem Table               : usage
rem Description         : Property Usage (e.g., school, gas station, etc.)
rem usageid             : Artificial PK 
rem usage               : Description (e.g., school, gas station, etc.) 

create table usage (
    usageid         number (3) not null,
    usage           varchar2 (25) not null,
constraint pk_usage primary key (usageid));
 
/*********************************************************************************/
 
rem Table               : type
rem Description         : Usage Type (e.g., current, subsequent, etc.)
rem typeid              : Artificial PK 
rem type                : Description (e.g., current, historical, etc.) 

create table type (
    typeid          number (2) not null,
    type            varchar2 (15) not null,
constraint pk_type primary key (typeid));
 
/*********************************************************************************/

rem Table               : contaminants
rem Description         : The contaminants
rem contid              : Contaminant ID (artificial PK) 
rem description         : Description of contaminant code 
rem
create table contaminants (
    contid          number (3) not null,
    description     varchar2 (15) not null,
constraint pk_contaminants primary key (contid));
 
/*********************************************************************************/
 
rem Table               : zoning
rem Description         : Zoning Information for Property
rem zoneid              : Artifical PK 
rem code                : Zone code (not unique across jurisdictions) 
rem description         : Code Description 
rem jurisid             : Artifical PK (Foreign Key)

create table zoning (
    zoneid          number (6) not null,
    code            varchar2 (10) not null,
    description     varchar2 (15),
    jurisid         number (6),
constraint pk_zoning primary key (zoneid),
constraint fk_zoning foreign key (jurisid)
    references jurisdiction (jurisid));
 
/*********************************************************************************/
 
rem Table               : broker
rem Description         : Broker Information
rem brokerid            : Artificial PK 
rem lastname            : Last Name 
rem firstname           : First Name 
rem middlei             : Middle Initial 
rem email               : Email 
rem phone               : Phone Number 
rem street              : Street Address 
rem zipid               : Foreign Key

create table broker (
    brokerid        number (3) not null,
    lastname        varchar2 (20) not null,
    firstname       varchar2 (20) not null,
    middlei         char (1),
    email           varchar2 (20),
    phone           char (10) not null,
    street          varchar2 (20) not null,
    zipid           number (6),
constraint pk_broker primary key (brokerid),
constraint fk_broker foreign key (zipid)
    references zipadd (zipid));
 
/*********************************************************************************/
 
rem Table               : owner
rem Description         : Owner Information
rem ownerid             : Artificial PK 
rem lastname            : Last Name 
rem firstname           : First Name 
rem middlei             : Middle Initial 
rem email               : Email 
rem phone               : Phone Number 
rem street              : Street Address 
rem zipid               : Foreign Key

create table owner (
    ownerid         number (6) not null,
    lastname        varchar2 (20) not null,
    firstname       varchar2 (20) not null,
    middlei         char (1),
    email           varchar2 (20),
    phone           char (10) not null,
    street          varchar2 (20) not null,
    zipid           number (6),
constraint pk_owner primary key (ownerid),
constraint fk_owner foreign key (zipid)
    references zipadd (zipid));
 
/*********************************************************************************/
 
rem Table               : employee
rem Description         : Employee Information
rem employeeid          : Artificial PK 
rem lastname            : Last Name 
rem firstname           : First Name 
rem middlei             : Middle Initial 
rem email               : Email 
rem phone               : Phone Number 
rem street              : Street Address 
rem ssn                 : Social Security Number 
rem zipid               : Foreign Key

create table employee (
    employeeid      number (3) not null,
    lastname        varchar2 (20) not null,
    firstname       varchar2 (20) not null,
    middlei         char (1),
    email           varchar2 (20),
    phone           char (10) not null,
    street          varchar2 (20) not null,
    ssn             char (9) not null,
    zipid           number (6),
constraint pk_employee primary key (employeeid),
constraint fk_employee foreign key (zipid)
    references zipadd (zipid));

/*********************************************************************************/
 
rem Table               : developer
rem Description         : Developer Information
rem developerid         : Artificial PK 
rem lastname            : Last Name 
rem firstname           : First Name 
rem middlei             : Middle Initial 
rem email               : Email 
rem phone               : Phone Number 
rem street              : Street Address 
rem zipid               : Foreign Key

create table developer (
    developerid     number (5) not null,
    lastname        varchar2 (20) not null,
    firstname       varchar2 (20) not null,
    middlei         char (1),
    email           varchar2 (20),
    phone           char (10) not null,
    street          varchar2 (20) not null,
    zipid           number (6),
constraint pk_developer primary key (developerid),
constraint fk_developer foreign key (zipid)
    references zipadd (zipid));
 
/*********************************************************************************/
 
rem Table               : property
rem Description         : 
rem propid              : PropertyID is the artificial PK 
rem sold                : Was Property Sold? 
rem phase1              : Is it in Phase 1 of development? 
rem phase2              : Is it in Phase 2 of development? 
rem phase3              : Is it in Phase 3 of development? 
rem railaccess          : Rail Access for property 
rem apn                 : Parcel Number for Property 
rem mapcoords           : Mapping Coordinates 
rem propsize            : Size of land/property (in acres or sq ft) 
rem assesnum            : Assessor Number for Property 
rem sitename            : Name of the Property 
rem gpd                 : General Plan Designation 
rem devpot              : Development Potential (percentage) 
rem incentives          : Redevelopment Incentives 
rem comments            : Additional Comments for Property 
rem zipid               : Foreign Key
rem desid               : Foreign Key
rem zoneid              : Foreign Key
rem ownerid             : Foreign Key

create table property (
    PropID          number (6) not null,
    Sold            char (1),
    Phase1          char (1),
    Phase2          char (1),
    Phase3          char (1),
    RailAccess      char (1),
    APN             char (10),
    MapCoords       char (10),
    PropSize        char (10),
    AssesNum        number (10),
    sitename        varchar2 (25),
    GPD             varchar2 (25),
    devpot          number (5,2),
    Incentives      varchar2 (30),
    Comments        varchar2 (30),
StreetAddress varchar2 (20),
    zipid           number (6),
    desid           number (3),
    zoneid          number (6),
    ownerid         number (6),
constraint pk_property primary key (propid),
constraint fk_property1 foreign key (zipid)
    references zipadd (zipid),
constraint fk_property2 foreign key (desid)
    references streetdesig (desid),
constraint fk_property3 foreign key (zoneid)
    references zoning (zoneid),
constraint fk_property4 foreign key (ownerid)
    references owner (ownerid));
 
/*********************************************************************************/
 
rem Table               : envstat
rem Description         : Environmental Status
rem propid              : Foreign Key
rem contlevel           : Contamination Level 
rem contamination       : What is contaminated? 

create table envstat (
    PropID          number (6) not null,
    ContLevel       char (1) not null,
    Contamination   varchar2 (25) not null,
constraint pk_envstat primary key (propid),
constraint fk_envstat foreign key (propid)
    references property (propid)
    on delete cascade);

/*********************************************************************************/
 
rem Table               : usageitem
rem Description         : Usage Item
rem propid              : Foreign key from PropID; part of Composite PK 
rem typeid              : Foreign key from Type; part of compositePK 
rem usageid             : Foreign Key

create table usageitem (
    PropID          number (6),
    TypeID          number (2),
    usageid         number (3),
constraint fk_usageitem1 foreign key (usageid)
    references usage (usageid),
constraint fk_usageitem2 foreign key (TypeID)
    references type (typeid),
constraint fk_usageitem3 foreign key (PropID)
    references property (propid),
constraint pk_usageitem primary key (propid, typeid));
 
/*********************************************************************************/

rem Table               : contitem
rem Description         : Contaminant Item
rem propid              : Foreign key from Property; part of composite PK 
rem contid              : Foreign key from Contaminants; part of composite PK 

create table contitem (
    PropID          number (6),
    ContID          number (3),
constraint fk_contitem1 foreign key (contid)
    references contaminants (contid),
constraint fk_contitem2 foreign key (propid)
    references property (propid),
constraint pk_contitem primary key (propid, contid)); 

/*********************************************************************************/
 
rem Table               : devsearchresult
rem Description         : Developer Search Result Item
rem devsearchresid      : Developer Search Result ID (artificial PK) 
rem searchdate          : Date Search was carried out 
rem developerid         : Foreign Key
rem propid              : Foreign Key

create table devsearchresult (
    devsearchresid  number (6),
    searchdate      date not null,
    developerid     number (5),
    PropID          number (6),
constraint pk_devsearchresult primary key (devsearchresid),
constraint fk_devsearchresult1 foreign key (developerid)
    references developer (developerid),
constraint fk_devsearchresult2 foreign key (propid)
    references property (propid));

/*********************************************************************************/
 
rem Table               : taxinfo
rem Description         : Property Tax Info
rem taxid               : Artificial PK 
rem taxyear             : Tax year 
rem assessed            : Assessed Value 
rem percentage          : Percentage Improvement 
rem taxamount           : Tax Amount 
rem status              : Tx Status 
rem tra                 : TRA Number 
rem exempt              : Tax Exempt? 
rem improvement         : Improvement Amount 
rem landamount          : Land Amount 
rem propid              : Foreign Key

create table taxinfo (
    taxid           char (7) not null,
    taxyear         number (4),
    assessed        number (10,2),
    percentage      number (3),
    taxamount       number (9,2),
    status          char (1),
    tra             number (6),
    exempt          char (1),
    improvement     number (9,2),
    landamount      number (9,2),
    PropID          number (6) not null,
constraint pk_taxinfo primary key (taxid),
constraint fk_taxinfo foreign key (propid)
    references property (propid));
 
/*********************************************************************************/

rem Table               : fininfo
rem Description         : Property Financial Info
rem finid               : Artificial PK 
rem buyer               : Buyer 
rem seller              : Seller 
rem lender              : Lender 
rem lasttrans           : Last Transaction Particulars 
rem saledate            : Sale Date 
rem saleamnt            : Sale Amount 
rem firsttd             : First Trust Deed 
rem addl                : Additional Fin? 
rem docno               : Document Number 
rem dolsqft             : Dollars Per Square Foot 
rem loantype            : Loan Type 
rem prevdate            : Previous Date 
rem prevamnt            : Previous Amount 
rem propid              : Foreign Key

create table fininfo (
    finid           char (7) not null,
    buyer           varchar2 (25),
    seller          varchar2 (25),
    lender          varchar2 (25),
    lasttrans       varchar2 (25),
    saledate        date,
    saleamnt        number (9,2),
    firsttd         number (9,2),
    addl            number (9,2),
    docno           number (9),
    dolsqft         number (9,2),
    loantype        varchar2 (20),
    prevdate        date,
    prevamnt        number (9,2),
    PropID          number (6) not null,
constraint pk_fininfo primary key (finid),
constraint fk_fininfo foreign key (propid)
    references property (propid));
 
/*********************************************************************************/ 

rem Table               : empactivity
rem Description         : Employee Activity
rem actid               : Artificial PK 
rem actdate             : Date of Activity 
rem employeeid          : Foreign Key
rem propid              : Foreign Key

create table empactivity (
    actid           number (6),
    actdate         date not null,
    employeeid      number (3),
    PropID          number (6),
constraint pk_empactivity primary key (actid),
constraint fk_empactivity1 foreign key (employeeid)
    references employee (employeeid),
constraint fk_empactivity2 foreign key (propid)
    references property (propid));
 
/*********************************************************************************/