The "Parcels" Database Schema

These are the SQL statements needed to create the tables and indexes in the "Parcels" database (without the rows containing the data).

DROP TABLE "PARCELS" CASCADE CONSTRAINTS;

CREATE TABLE "PARCELS" (
        "PARCELID"  NUMBER,
        "PID"       NUMBER(5, 0) NOT NULL ENABLE,
        "WPB"       NUMBER(5, 0) NOT NULL ENABLE,
        "ADD1"      NUMBER(5, 0),
        "ADD2"      VARCHAR2(17),
        "ZIP"       VARCHAR2(5),
        "SQFT"      NUMBER(10, 0),
        "ONUM"      NUMBER(5, 0),
        "LANDUSE"   VARCHAR2(3)
    )
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    LOGGING
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS";

GRANT SELECT
    ON "PARCELS"
    TO "PUBLIC";

CREATE UNIQUE INDEX "PARCELS"
    ON "PARCELS" ("PARCELID")
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS"
    LOGGING;

CREATE UNIQUE INDEX "PARCELS$PID$WPB"
    ON "PARCELS" ("PID", "WPB")
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS"
    LOGGING;

ALTER TABLE "PARCELS"
    ADD CONSTRAINT "PARCELS"
    PRIMARY KEY ("PARCELID")
    USING INDEX
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS"
    ENABLE;

ALTER TABLE "PARCELS"
    ADD CONSTRAINT "PARCELS$PID$WPB"
    UNIQUE ("PID", "WPB")
    USING INDEX
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS"
    ENABLE;

DROP TABLE "FIRES" CASCADE CONSTRAINTS;

CREATE TABLE "FIRES" (
        "PARCELID"   NUMBER,
        "FDATE"      DATE,
        "IGNFACTOR"  NUMBER(5, 0),
        "ESTLOSS"    NUMBER(8, 0)
    )
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    LOGGING
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS";

GRANT SELECT
    ON "FIRES"
    TO "PUBLIC";

CREATE UNIQUE INDEX "FIRES"
    ON "FIRES" ("PARCELID", "FDATE")
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS"
    LOGGING;

ALTER TABLE "FIRES"
    ADD CONSTRAINT "FIRES"
    PRIMARY KEY ("PARCELID", "FDATE")
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        STORAGE (
            INITIAL 131072
            NEXT 16384
            MINEXTENTS 1
            MAXEXTENTS 16
            PCTINCREASE 50
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT
        )
    TABLESPACE "USERS"
    ENABLE;

DROP TABLE "TAX" CASCADE CONSTRAINTS;

CREATE TABLE "TAX" (
        "PARCELID"  NUMBER,
        "PRPTYPE"   NUMBER(5, 0),
        "LANDVAL"   NUMBER(10, 0),
        "BLDVAL"    NUMBER(10, 0),
        "TAX"       NUMBER(10, 0)
    )
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    LOGGING
    STORAGE (
        INITIAL 131072
        NEXT 16384 MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS";

GRANT SELECT
    ON "TAX"
    TO "PUBLIC";

CREATE UNIQUE INDEX "TAX"
    ON "TAX" ("PARCELID")
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT)
    TABLESPACE "USERS"
    LOGGING;

ALTER TABLE "TAX"
    ADD CONSTRAINT "TAX"
    PRIMARY KEY ("PARCELID")
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        STORAGE (
            INITIAL 131072
            NEXT 16384 MINEXTENTS 1
            MAXEXTENTS 16
            PCTINCREASE 50
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT
        )
    TABLESPACE "USERS"
    ENABLE;

DROP TABLE "OWNERS" CASCADE CONSTRAINTS;

CREATE TABLE "OWNERS" (
        "OWNERNUM"  NUMBER(5, 0) NOT NULL ENABLE,
        "ONAME"     VARCHAR2(23),
        "ADDRESS"   VARCHAR2(15),
        "CITY"      VARCHAR2(13),
        "STATE"     VARCHAR2(9),
        "ZIP"       VARCHAR2(5)
    )
    PCTFREE 10 PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    LOGGING
    STORAGE (
        INITIAL 131072
        NEXT 16384 MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS";

GRANT SELECT
    ON "OWNERS"
    TO "PUBLIC";

CREATE UNIQUE INDEX "OWNERS"
    ON "OWNERS" ("OWNERNUM")
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1 FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS"
    LOGGING;

ALTER TABLE "OWNERS"
    ADD CONSTRAINT "OWNERS"
    PRIMARY KEY ("OWNERNUM")
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        STORAGE (
            INITIAL 131072
            NEXT 16384
            MINEXTENTS 1
            MAXEXTENTS 16
            PCTINCREASE 50
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT
        )
        TABLESPACE "USERS"
    ENABLE;

DROP TABLE "ZONING" CASCADE CONSTRAINTS;

CREATE TABLE "ZONING" (
        "PARCELID"  NUMBER,
        "ADDRESS"   VARCHAR2(15),
        "BOARDDEC"  VARCHAR2(11),
        "EXSZONE"   VARCHAR2(8),
        "PRPZONE"   VARCHAR2(8),
        "HEARDATE"  DATE
    )
    PCTFREE 10 PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    LOGGING
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS";

GRANT SELECT
    ON "ZONING"
    TO "PUBLIC";

CREATE UNIQUE INDEX "ZONING"
    ON "ZONING" ("PARCELID", "HEARDATE")
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS"
    LOGGING;

ALTER TABLE "ZONING"
    ADD CONSTRAINT "ZONING"
    PRIMARY KEY ("PARCELID", "HEARDATE")
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        STORAGE (
            INITIAL 131072
            NEXT 16384
            MINEXTENTS 1
            MAXEXTENTS 16
            PCTINCREASE 50
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT
        )
        TABLESPACE "USERS"
    ENABLE;

DROP TABLE "PERMITS" CASCADE CONSTRAINTS;

CREATE TABLE "PERMITS" (
        "PID" NUMBER(5, 0) NOT NULL ENABLE,
        "WPB" NUMBER(5, 0) NOT NULL ENABLE,
        "PTYPE" NUMBER(5, 0),
        "APPDATE" DATE,
        "ISSUDATE" DATE,
         "ESTCOST" NUMBER(10, 0),
         "FEE" NUMBER(6, 0))
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    LOGGING
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 121
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS";

GRANT SELECT
    ON "PERMITS"
    TO "PUBLIC";

CREATE UNIQUE INDEX "PERMITS"
    ON "PERMITS" ("PID", "WPB", "ISSUDATE")
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS"
    LOGGING;

ALTER TABLE "PERMITS"
    ADD CONSTRAINT "PERMITS"
    PRIMARY KEY ("PID", "WPB", "ISSUDATE")
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        STORAGE (
            INITIAL 131072
            NEXT 16384
            MINEXTENTS 1
            MAXEXTENTS 16
            PCTINCREASE 50
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT
        )
    TABLESPACE "USERS"
    ENABLE;

DROP TABLE "SALES" CASCADE CONSTRAINTS;

CREATE TABLE "SALES" (
        "PARCELID"  NUMBER,
        "SDATE"     DATE,
        "GRANTOR"   VARCHAR2(20) NOT NULL ENABLE,
        "GRANTEE"   VARCHAR2(22) NOT NULL ENABLE,
        "SPRICE"    NUMBER(10, 0),
        "BKNUM"     NUMBER(5, 0),
        "PGNUM"     NUMBER(5, 0)
    )
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    LOGGING
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS";

GRANT SELECT
    ON "SALES"
    TO "PUBLIC";

CREATE UNIQUE INDEX "SALES"
    ON "SALES" ("PARCELID", "SDATE")
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
        INITIAL 131072
        NEXT 16384
        MINEXTENTS 1
        MAXEXTENTS 16
        PCTINCREASE 50
        FREELISTS 1
        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT
    )
    TABLESPACE "USERS"
    LOGGING;

ALTER TABLE "SALES"
    ADD CONSTRAINT "SALES"
    PRIMARY KEY ("PARCELID", "SDATE")
    USING INDEX
        PCTFREE 10
        INITRANS 2
        MAXTRANS 255
        STORAGE (
            INITIAL 131072
            NEXT 16384
            MINEXTENTS 1
            MAXEXTENTS 16
            PCTINCREASE 50 FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT
        )
        TABLESPACE "USERS"
    ENABLE;

ALTER TABLE "PARCELS"
    ADD CONSTRAINT "PARCELS#ONUM"
    FOREIGN KEY ("ONUM")
    REFERENCES "OWNERS" ("OWNERNUM")
    ENABLE
    NOVALIDATE;

ALTER TABLE "FIRES"
    ADD CONSTRAINT "FIRES#PARCELID"
    FOREIGN KEY ("PARCELID")
    REFERENCES "PARCELS" ("PARCELID")
    ENABLE
    NOVALIDATE;

ALTER TABLE "TAX"
    ADD CONSTRAINT "TAX#PARCELID"
    FOREIGN KEY ("PARCELID")
    REFERENCES "PARCELS" ("PARCELID")
    ENABLE
    NOVALIDATE;

ALTER TABLE "ZONING"
    ADD CONSTRAINT "ZONING#PARCELID"
    FOREIGN KEY ("PARCELID")
    REFERENCES "PARCELS" ("PARCELID")
    ENABLE
    NOVALIDATE;

ALTER TABLE "PERMITS"
    ADD CONSTRAINT "PERMITS#PID#WPB"
    FOREIGN KEY ("PID", "WPB")
    REFERENCES "PARCELS" ("PID", "WPB")
    ENABLE
    NOVALIDATE;

ALTER TABLE "SALES"
    ADD CONSTRAINT "SALES#PARCELID"
    FOREIGN KEY ("PARCELID")
    REFERENCES "PARCELS" ("PARCELID")
    ENABLE
    NOVALIDATE;

ALTER TABLE "PARCELS"
    ENABLE CONSTRAINT "PARCELS";

ALTER TABLE "PARCELS"
    ENABLE CONSTRAINT "PARCELS$PID$WPB";

ALTER TABLE "FIRES"
    ENABLE CONSTRAINT "FIRES";

ALTER TABLE "TAX"
    ENABLE CONSTRAINT "TAX";

ALTER TABLE "OWNERS"
    ENABLE CONSTRAINT "OWNERS";

ALTER TABLE "ZONING"
    ENABLE CONSTRAINT "ZONING";

ALTER TABLE "PERMITS"
    ENABLE CONSTRAINT "PERMITS";

ALTER TABLE "SALES"
    ENABLE CONSTRAINT "SALES";

ALTER TABLE "PARCELS"
    ENABLE CONSTRAINT "PARCELS#ONUM";

ALTER TABLE "FIRES"
    ENABLE CONSTRAINT "FIRES#PARCELID";

ALTER TABLE "TAX"
    ENABLE CONSTRAINT "TAX#PARCELID";

ALTER TABLE "ZONING"
    ENABLE CONSTRAINT "ZONING#PARCELID";

ALTER TABLE "PERMITS"
    ENABLE CONSTRAINT "PERMITS#PID#WPB";

ALTER TABLE "SALES"
    ENABLE CONSTRAINT "SALES#PARCELID";

For more information about this page, please contact the 11.521 Staff Mailing List <11.521staff@MIT.EDU>.
Last modified: 12 Feb 2001 [thg]