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));
/*********************************************************************************/