Remote DBA Plans
Remote DBA Service

EnterpriseDB: Create Traker Database Objects

Oracle Tips by Burleson Consulting

* chap9_edb_create_traker_db_objects.sql

CREATE ROLE tt_user_role;
CREATE ROLE tt_pm_role;
CREATE ROLE tt_admin_role;

CREATE SEQUENCE traker.tt_general_sequence;

CREATE TABLE TRAKER.TT_SCHEDULES
(
SCHEDULE_ID    NUMBER(12)                     NOT NULL,
SCHEDULE_TYPE VARCHAR2(10),
MONDAY         VARCHAR2(1),
TUESDAY        VARCHAR2(1),
WEDNESDAY      VARCHAR2(1),
THURSDAY       VARCHAR2(1),
FRIDAY         VARCHAR2(1),
SATURDAY       VARCHAR2(1),
SUNDAY         VARCHAR2(1),
PRIMARY KEY
(SCHEDULE_ID),
CONSTRAINT TT_SCHEDULES_UK1
UNIQUE (SCHEDULE_TYPE)
);

CREATE TABLE TRAKER.TT_XML_INTERFACE
(
INTERFACE_ID   NUMBER(12)                     NOT NULL,
DATE_RECEIVED TIMESTAMP(6),
XML_DATA       TEXT,
PRIMARY KEY
(INTERFACE_ID)
);

CREATE TABLE TRAKER.TT_TASKS
(
TASK_ID      NUMBER(12)                       NOT NULL,
TASK_NAME    VARCHAR2(100),
TASK_DESC    VARCHAR2(4000),
ACTIVE_FLAG VARCHAR2(1),
PRIMARY KEY
(TASK_ID),
CONSTRAINT TT_TASKS_UK1
UNIQUE (TASK_NAME)
);

CREATE TABLE TRAKER.TT_PROJECTS

(
PROJECT_ID NUMBER(12) NOT NULL,
PROJECT_NAME VARCHAR2(100),
PROJECT_DESC VARCHAR2(4000),
ACTIVE_FLAG VARCHAR2(1),

PRIMARY KEY
(PROJECT_ID),
CONSTRAINT TT_PROJECTS_UK1
UNIQUE (PROJECT_NAME)
);

CREATE TABLE TRAKER.TT_CALENDAR
(
DATE_PK            DATE                       NOT NULL,
QUARTER_IND        NUMBER(2),
HOLIDAY_FLAG       VARCHAR2(1),
BUSINESS_DAY_FLAG VARCHAR2(1),
PRIMARY KEY
(DATE_PK)
);

CREATE TABLE TRAKER.TT_RESOURCES
(
RESOURCE_ID       NUMBER(12)                  NOT NULL,
SCHEDULE_ID       NUMBER(12)                  NOT NULL,
RESOURCE_NAME     VARCHAR2(100),
ALLOCATION_PCT    NUMBER(3),
HOURS_PER_DAY     NUMBER(6,4),
JOB               VARCHAR2(100),
ASSIGNABLE_FLAG   VARCHAR2(1),
XML_FEED_USER_ID VARCHAR2(100),
ACTIVE_FLAG       VARCHAR2(1),
PRIMARY KEY
(RESOURCE_ID),
CONSTRAINT TT_RESOURCES_UK1
UNIQUE (RESOURCE_NAME),

UNIQUE (XML_FEED_USER_ID),
FOREIGN KEY (SCHEDULE_ID)
REFERENCES TRAKER.TT_SCHEDULES (SCHEDULE_ID)
);

CREATE TABLE TRAKER.TT_PROJECTS_TASKS
(
PROJECT_ID         NUMBER(12)                 NOT NULL,
TASK_ID            NUMBER(12)                 NOT NULL,
PRIORITY           NUMBER(5),
ESTIMATED_HOURS    NUMBER(12),
PROJECT_TASK_NAME VARCHAR2(100),
ACTIVE_FLAG        VARCHAR2(1),
PRIMARY KEY
(PROJECT_ID, TASK_ID),
FOREIGN KEY (TASK_ID)
REFERENCES TRAKER.TT_TASKS (TASK_ID),
FOREIGN KEY (PROJECT_ID)
REFERENCES TRAKER.TT_PROJECTS (PROJECT_ID)
);

CREATE TABLE TRAKER.TT_TASKS_RESOURCES
(
TASK_ID      NUMBER(12)                       NOT NULL,
RESOURCE_ID NUMBER(12)                       NOT NULL,
ACTIVE_FLAG VARCHAR2(1),
PRIMARY KEY
(TASK_ID, RESOURCE_ID),
FOREIGN KEY (RESOURCE_ID)
REFERENCES TRAKER.TT_RESOURCES (RESOURCE_ID),
FOREIGN KEY (TASK_ID)
REFERENCES TRAKER.TT_TASKS (TASK_ID)
);

CREATE TABLE TRAKER.TT_TASK_ITEM_UPDATE
(
TASK_ID       NUMBER(12)                      NOT NULL,
RESOURCE_ID   NUMBER(12)                      NOT NULL,
ITEM_DATE     DATE                            NOT NULL,
HOURS_WORKED NUMBER(6,4),
ACTIVE_FLAG   VARCHAR2(1),
PRIMARY KEY
(TASK_ID, RESOURCE_ID, ITEM_DATE),
FOREIGN KEY (TASK_ID, RESOURCE_ID)
REFERENCES TRAKER.TT_TASKS_RESOURCES (TASK_ID,RESOURCE_ID)
);

CREATE TABLE TRAKER.TT_TASK_DEPENDS
(
PROJECT_ID         NUMBER(12)                 NOT NULL,
TASK_ID            NUMBER(12)                 NOT NULL,
DEPEND_PROJECT_ID NUMBER(12)                 NOT NULL,
DEPEND_TASK_ID     NUMBER(12)                 NOT NULL,

PRIMARY KEY
(PROJECT_ID, TASK_ID),
FOREIGN KEY (DEPEND_PROJECT_ID, DEPEND_TASK_ID)
REFERENCES TRAKER.TT_PROJECTS_TASKS (PROJECT_ID,TASK_ID),

REFERENCES TRAKER.TT_PROJECTS_TASKS (PROJECT_ID,TASK_ID)
);

CREATE INDEX TT_CALENDAR_QTR_NDEX ON TRAKER.TT_CALENDAR
(QUARTER_IND);

CREATE INDEX TT_CALENDAR_HOL_NDX ON TRAKER.TT_CALENDAR
(HOLIDAY_FLAG);

CREATE INDEX TT_CALENDAR_BUS_NDX ON TRAKER.TT_CALENDAR
(BUSINESS_DAY_FLAG);

CREATE UNIQUE INDEX TT_PROJECTS_TASKS_NAME_NDX ON TRAKER.TT_PROJECTS_TASKS
(PROJECT_TASK_NAME, ACTIVE_FLAG);

CREATE INDEX TT_TASK_DEPENDS_ALL_NDX ON TRAKER.TT_TASK_DEPENDS
(DEPEND_PROJECT_ID, DEPEND_TASK_ID, PROJECT_ID, TASK_ID);

CREATE INDEX TT_XML_INTERFACE_DT_NDX ON TRAKER.TT_XML_INTERFACE
(DATE_RECEIVED, INTERFACE_ID);

~production directive: code file: chap9_edb_traker_views.sql

CREATE OR REPLACE VIEW traker.hours_worked_by_resources AS
SELECT tt_projects_tasks.project_task_name,
        tt_projects_tasks.estimated_hours,
        tt_resources.resource_name,
        nvl(tt_task_item_update.hours_worked, (0)::numeric) AS hours_worked,
        tt_task_item_update.item_date
FROM ((((traker.tt_tasks JOIN traker.tt_projects_tasks
           ON ((tt_tasks.task_id = tt_projects_tasks.task_id)))
JOIN traker.tt_tasks_resources
           ON ((tt_tasks.task_id = tt_tasks_resources.task_id)))
JOIN traker.tt_resources ON ((tt_resources.resource_id = tt_tasks_resources.resource_id)))

LEFT JOIN traker.tt_task_item_update
           ON (((tt_tasks_resources.task_id = tt_task_item_update.task_id)
              AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id))));

CREATE OR REPLACE VIEW traker.proj_hours_remain_by_resource AS
SELECT foo.project_task_name,
        foo.resource_name,
        foo.estimated_hours,
        foo.hours_worked_by_resource,
        1 AS rn
FROM (
         SELECT tt_projects_tasks.project_task_name,
                tt_resources.resource_name,
                tt_projects_tasks.estimated_hours,
                nvl(sum(tt_task_item_update.hours_worked), (0)::numeric) AS
hours_worked_by_resource
           FROM ((((traker.tt_tasks
           LEFT JOIN traker.tt_projects_tasks
                ON ((tt_tasks.task_id = tt_projects_tasks.task_id)))
           LEFT JOIN traker.tt_tasks_resources
                ON ((tt_tasks.task_id = tt_tasks_resources.task_id)))
           LEFT JOIN traker.tt_resources
                ON ((tt_resources.resource_id = tt_tasks_resources.resource_id)))
           LEFT JOIN traker.tt_task_item_update
                ON (((tt_tasks_resources.task_id = tt_task_item_update.task_id)
                AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id))))
           GROUP BY tt_projects_tasks.project_task_name, tt_resources.resource_name,
tt_projects_tasks.estimated_hours) as foo;

CREATE OR REPLACE VIEW traker.proj_hours_remaining AS
SELECT tt_projects_tasks.project_task_name, |
        nvl(tt_projects_tasks.estimated_hours, (0)::numeric) AS estimated_hours,
        nvl((tt_projects_tasks.estimated_hours -
            sum(tt_task_item_update.hours_worked)), (0)::numeric) AS
hours_remaining_in_project
  FROM ((((traker.tt_tasks
JOIN traker.tt_projects_tasks
           ON ((tt_tasks.task_id = tt_projects_tasks.task_id)))
JOIN traker.tt_tasks_resources
           ON ((tt_tasks.task_id = tt_tasks_resources.task_id)))
JOIN traker.tt_resources

         ON ((tt_resources.resource_id = tt_tasks_resources.resource_id)))
LEFT JOIN traker.tt_task_item_update
           ON (((tt_tasks_resources.task_id = tt_task_item_update.task_id)
              AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id))))
GROUP BY tt_projects_tasks.project_task_name, tt_projects_tasks.estimated_hours;

CREATE OR REPLACE VIEW traker.project_resources AS
SELECT tt_projects_tasks.project_task_name,
        tt_projects_tasks.estimated_hours,
        tt_tasks.task_name,
        tt_resources.resource_name,
        tt_resources.allocation_pct,
        tt_resources.hours_per_day,
        tt_resources.job
FROM (((traker.tt_tasks
JOIN traker.tt_projects_tasks
           ON ((tt_tasks.task_id = tt_projects_tasks.task_id)))
JOIN traker.tt_tasks_resources
           ON ((tt_tasks.task_id = tt_tasks_resources.task_id)))
JOIN traker.tt_resources
           ON ((tt_resources.resource_id = tt_tasks_resources.resource_id)));

CREATE OR REPLACE VIEW traker.project_tasks AS
SELECT tt_projects.project_name,
        tt_projects.project_desc,
        tt_tasks.task_name,
        tt_tasks.task_desc,
        tt_projects_tasks.project_task_name,
        tt_projects_tasks.estimated_hours

FROM ((traker.tt_projects
JOIN traker.tt_projects_tasks
ON ((tt_projects.project_id = tt_projects_tasks.project_id)))
JOIN traker.tt_tasks
ON ((tt_tasks.task_id = tt_projects_tasks.task_id)));

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.