businessContractProject.ddl

-- Generated by Oracle SQL Developer Data Modeler 4.0.0.833
--   at:        2014-03-24 18:33:42 CDT
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g




CREATE TABLE BCDB_ammendment
  (
    item_id     INTEGER NOT NULL ,
    description VARCHAR2 (255) NOT NULL ,
    start_date  DATE ,
    end_date    DATE ,
    base_cost   NUMBER ,
    contract_id INTEGER NOT NULL
  ) ;
ALTER TABLE BCDB_ammendment ADD CONSTRAINT BCDB_item_ordid_itemid_pk PRIMARY KEY ( item_id ) ;

CREATE TABLE BCDB_communication
  (
    communication_id INTEGER NOT NULL ,
    party_id         INTEGER NOT NULL ,
    "date"           DATE ,
    notes            VARCHAR2 (4000)
  ) ;
ALTER TABLE BCDB_communication ADD CONSTRAINT BCDB_communication_PK PRIMARY KEY ( communication_id, party_id ) ;

CREATE TABLE BCDB_contract
  (
    contract_id     INTEGER NOT NULL ,
    agreement_type  VARCHAR2 (100) DEFAULT 'UT as Tenant' ,
    contract_number VARCHAR2 (255) NOT NULL ,
    logged_by       VARCHAR2 (4) ,
    purpose         VARCHAR2 (255) ,
    notes CLOB ,
    base_cost                    NUMBER ,
    renewable                    CHAR (1) ,
    ongoing                      CHAR (1) ,
    received_date                DATE ,
    signed_date                  DATE ,
    signed_by                    VARCHAR2 (30) ,
    reviewed_by                  VARCHAR2 (30) ,
    programmatic_approval        VARCHAR2 (30) ,
    party_dept_contact_id        INTEGER ,
    party_dept_contract_admin_id INTEGER ,
    vendor_id                    INTEGER ,
    department_id                INTEGER ,
    account_type                 NUMBER (2) ,
    service_type                 VARCHAR2 (25) ,
    ogc                          CHAR (1) ,
    bor                          CHAR (1) ,
    evc                          CHAR (1) ,
    docket                       CHAR (1) ,
    docket_date                  DATE ,
    system_notes CLOB
  ) ;
ALTER TABLE BCDB_contract ADD CHECK ( agreement_type IN ('ATM', 'Tower', 'UT as Landlord', 'UT as Tenant')) ;
ALTER TABLE BCDB_contract ADD CONSTRAINT BCDB_ord_id_pk PRIMARY KEY ( contract_id ) ;

CREATE TABLE BCDB_cost
  (
    cost_id       INTEGER NOT NULL ,
    description   VARCHAR2 (100) NOT NULL ,
    amount        NUMBER ,
    date_incurred DATE ,
    status        VARCHAR2 (25) NOT NULL ,
    date_paid     DATE ,
    ammendment_id INTEGER ,
    party_id      INTEGER ,
    contract_id   INTEGER
  ) ;
ALTER TABLE BCDB_cost ADD CONSTRAINT BCDB_cost_PK PRIMARY KEY ( cost_id ) ;

CREATE TABLE BCDB_document
  (
    id            INTEGER NOT NULL ,
    filename      VARCHAR2 (4000 BYTE) ,
    file_mimetype VARCHAR2 (512) ,
    file_charset  VARCHAR2 (512) ,
    file_blob BLOB ,
    file_comments VARCHAR2 (4000) ,
    tags          VARCHAR2 (4000) ,
    contract_id   INTEGER ,
    party_id      INTEGER
  ) ;
ALTER TABLE BCDB_document ADD CONSTRAINT BCDB_document_PK PRIMARY KEY ( id ) ;

CREATE TABLE BCDB_escalation
  (
    escalation_id INTEGER NOT NULL ,
    "date"        DATE ,
    amount        NUMBER ,
    percentage    INTEGER ,
    description   VARCHAR2 (100) NOT NULL
  ) ;
ALTER TABLE BCDB_escalation ADD CONSTRAINT BCDB_Escalation_PK PRIMARY KEY ( escalation_id ) ;

CREATE TABLE BCDB_facility
  (
    facility_id      INTEGER NOT NULL ,
    building         VARCHAR2 (100) NOT NULL ,
    address          VARCHAR2 (100) ,
    city             VARCHAR2 (50) ,
    state            VARCHAR2 (50) ,
    zip_code         VARCHAR2 (25) ,
    building_manager VARCHAR2 (100) ,
    phone            VARCHAR2 (25) ,
    contract_id      INTEGER ,
    item_id          INTEGER
  ) ;
ALTER TABLE BCDB_facility ADD CONSTRAINT BCDB_facility_PK PRIMARY KEY ( facility_id ) ;

CREATE TABLE BCDB_item_esc
  (
    item_esc_id   INTEGER NOT NULL ,
    escalation_id INTEGER NOT NULL ,
    ammendment_id INTEGER NOT NULL
  ) ;
ALTER TABLE BCDB_item_esc ADD CONSTRAINT BCDB_item_esc_PK PRIMARY KEY ( item_esc_id, escalation_id, ammendment_id ) ;

CREATE TABLE BCDB_link
  (
    link_id       INTEGER NOT NULL ,
    link_text     VARCHAR2 (255 BYTE) ,
    link_target   VARCHAR2 (4000 BYTE) ,
    link_comments VARCHAR2 (4000) ,
    tags          VARCHAR2 (4000) ,
    contract_id   INTEGER ,
    party_id      INTEGER
  ) ;
ALTER TABLE BCDB_link ADD CONSTRAINT BCDB_link_PK PRIMARY KEY ( link_id ) ;

CREATE TABLE BCDB_organization
  (
    organization_id INTEGER NOT NULL ,
    name            VARCHAR2 (5) ,
    friendly_name   VARCHAR2 (40) NOT NULL ,
    type            VARCHAR2 (50) NOT NULL
  ) ;
ALTER TABLE BCDB_organization ADD CONSTRAINT BCDB_department_PK PRIMARY KEY ( organization_id ) ;

CREATE TABLE BCDB_party
  (
    party_id             INTEGER NOT NULL ,
    primary_contact_name VARCHAR2 (255) ,
    party_name           VARCHAR2 (100) ,
    title                VARCHAR2 (50) ,
    phone                VARCHAR2 (25) ,
    fax                  VARCHAR2 (25) ,
    email                VARCHAR2 (100) ,
    address              VARCHAR2 (100) ,
    city                 VARCHAR2 (50) ,
    state                VARCHAR2 (50) ,
    zip_code             VARCHAR2 (25) ,
    country              VARCHAR2 (50) ,
    party_id1            INTEGER ,
    organization_id      INTEGER
  ) ;
ALTER TABLE BCDB_party ADD CONSTRAINT BCDB_contact_PK PRIMARY KEY ( party_id ) ;

CREATE TABLE BCDB_payment
  (
    payment_id     INTEGER NOT NULL ,
    amount         NUMBER ,
    description    VARCHAR2 (100) ,
    due_date       DATE ,
    payment_date   DATE ,
    payment_number VARCHAR2 (255) ,
    total_payments NUMBER ,
    ammendment_id  INTEGER ,
    party_id       INTEGER
  ) ;
ALTER TABLE BCDB_payment ADD CONSTRAINT BCDB_payment_PK PRIMARY KEY ( payment_id ) ;

ALTER TABLE BCDB_communication ADD CONSTRAINT BCDB_Relation_10 FOREIGN KEY ( party_id ) REFERENCES BCDB_party ( party_id ) ;

ALTER TABLE BCDB_cost ADD CONSTRAINT BCDB_Relation_19 FOREIGN KEY ( contract_id ) REFERENCES BCDB_contract ( contract_id ) ;

ALTER TABLE BCDB_facility ADD CONSTRAINT BCDB_Relation_20 FOREIGN KEY ( contract_id ) REFERENCES BCDB_contract ( contract_id ) ;

ALTER TABLE BCDB_facility ADD CONSTRAINT BCDB_Relation_21 FOREIGN KEY ( item_id ) REFERENCES BCDB_ammendment ( item_id ) ;

ALTER TABLE BCDB_party ADD CONSTRAINT BCDB_Relation_23 FOREIGN KEY ( organization_id ) REFERENCES BCDB_organization ( organization_id ) ;

ALTER TABLE BCDB_contract ADD CONSTRAINT BCDB_Relation_25 FOREIGN KEY ( department_id ) REFERENCES BCDB_organization ( organization_id ) ;

ALTER TABLE BCDB_contract ADD CONSTRAINT BCDB_Relation_26 FOREIGN KEY ( vendor_id ) REFERENCES BCDB_party ( party_id ) ;

ALTER TABLE BCDB_contract ADD CONSTRAINT BCDB_Relation_41 FOREIGN KEY ( party_dept_contact_id ) REFERENCES BCDB_party ( party_id ) ;

ALTER TABLE BCDB_document ADD CONSTRAINT BCDB_Relation_43 FOREIGN KEY ( party_id ) REFERENCES BCDB_party ( party_id ) ;

ALTER TABLE BCDB_link ADD CONSTRAINT BCDB_Relation_44 FOREIGN KEY ( party_id ) REFERENCES BCDB_party ( party_id ) ;

ALTER TABLE BCDB_cost ADD CONSTRAINT BCDB_Relation_45v1 FOREIGN KEY ( party_id ) REFERENCES BCDB_party ( party_id ) ;

ALTER TABLE BCDB_item_esc ADD CONSTRAINT BCDB_Relation_50 FOREIGN KEY ( escalation_id ) REFERENCES BCDB_escalation ( escalation_id ) ;

ALTER TABLE BCDB_document ADD CONSTRAINT BCDB_Relation_51 FOREIGN KEY ( contract_id ) REFERENCES BCDB_contract ( contract_id ) ;

ALTER TABLE BCDB_item_esc ADD CONSTRAINT BCDB_Relation_51v1 FOREIGN KEY ( ammendment_id ) REFERENCES BCDB_ammendment ( item_id ) ;

ALTER TABLE BCDB_party ADD CONSTRAINT BCDB_Relation_52 FOREIGN KEY ( party_id1 ) REFERENCES BCDB_party ( party_id ) ;

ALTER TABLE BCDB_contract ADD CONSTRAINT BCDB_Relation_53 FOREIGN KEY ( party_dept_contract_admin_id ) REFERENCES BCDB_party ( party_id ) ;

ALTER TABLE BCDB_payment ADD CONSTRAINT BCDB_Relation_54 FOREIGN KEY ( party_id ) REFERENCES BCDB_party ( party_id ) ;

ALTER TABLE BCDB_link ADD CONSTRAINT BCDB_Relation_59 FOREIGN KEY ( contract_id ) REFERENCES BCDB_contract ( contract_id ) ;

ALTER TABLE BCDB_payment ADD CONSTRAINT BCDB_Relation_9 FOREIGN KEY ( ammendment_id ) REFERENCES BCDB_ammendment ( item_id ) ;

ALTER TABLE BCDB_cost ADD CONSTRAINT BCDB_item_cost FOREIGN KEY ( ammendment_id ) REFERENCES BCDB_ammendment ( item_id ) ;

ALTER TABLE BCDB_ammendment ADD CONSTRAINT BCDB_made_of FOREIGN KEY ( contract_id ) REFERENCES BCDB_contract ( contract_id ) ;


-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            12
-- CREATE INDEX                             0
-- ALTER TABLE                             34
-- CREATE VIEW                              0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0