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