1 / 28

Data Model Basics

Data Model Basics. Overview of Basic Data Model Concepts for using Reporting Tools for Custom Queries. Data Model Subject Areas: Business Associates (Name & Address) File Definition Participants DOMAIN- Burden Groups . Data Model Subject Areas: Obligations LEASEDATA-Payments/Billing

mieko
Download Presentation

Data Model Basics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Model Basics

  2. Overview of Basic Data Model Concepts for using Reporting Tools for Custom Queries

  3. Data Model Subject Areas: Business Associates (Name & Address) File Definition Participants DOMAIN- Burden Groups

  4. Data Model Subject Areas: Obligations LEASEDATA-Payments/Billing Cross References Legal Descriptions Depth

  5. Data Model Subject Areas: Provisions Documents Lease Cost Checks/Invoices/1099s Properties

  6. Database Concepts - Logical vs Physical DATA MODEL LOGICAL DATA MODEL: Represents business information and defines business rules PHYSICAL DATA MODEL: Reflects the physical implementation of the model in the database Note: There is not necessarily a one-to-one relationship between entities/tables and attributes/columns. Entity Group (set) of related Table data elements Attribute Individual data Column element

  7. Database Concepts - Columns & Rows Columns Rows Row = Single line of related data elements with a unique key. (Key may be comprised of multiple columns). Standard typed format for a data attribute = AREA.FILE_KEY

  8. Normalized Database: Each table contains related attributes for one entity. Attributes are not duplicated in any other table. Database Concepts - Normalized Database

  9. NAME NAME_KEY SHRT_ALFA NAME_TYPE_CODE NAME_LINE_1 TAX_ID_NO COMPANY_NUM BUS_ASSOC_NO • • • Database Concepts - Normalized Database • PARTICIPANT • PART_KEY • FILE_KEY • PART_TYPE_CODE • NAME_KEY • NAME_ADDR_KEY • • • • • •

  10. Sequence: Sequential numeric values that uniquely identify a row of data within a table, and are often used as primary keys. Oracle provides a mechanism to keep tract of and assign these sequences. Referential Integrity: Requires that the relationships between the data be maintained so that inaccurate and unbalanced conditions do not occur. For instance, codes must be valid on supporting tables and when data is deleted, no orphaned data remains. Database Concepts - Sequences & Referential Integrity

  11. Database Concepts - Referential Integrity Participant_Interest • FILE_KEY • PART_KEY • PART_INT_KEY • PART_TYPE_INT • EXCPT_CODE • SUSP_CODE • TAKE_CODE • • • • • • Code_Except_Code Excpt_Code FK_PARTICIPANT_INT$EXCPT_CODE Code_Susp_Code Susp_Code FK_PARTICIPANT_INT$SUSP_CODE Code_Take_Code Take_Code FK_PARTICIPANT_INT$TAKE_CODE

  12. Code Tables: Allows users to define multiple values for specific attributes that may be validated. In TLS, code tables typically contain a 3 character, alphanumeric code and a textual description. Descriptions are displayed in the applications as a result of joins to the code tables. Rule Tables: Used to limit the selection of codes within TLS by application, group code or other variable. Rule tables are set up to enforce application business rules. Relation Tables: Support a many-to-many relationship between tables. Relation tables typically carry just the sequential keys for the two tables being related. Database Concepts - Code, Rule, Relation Tables

  13. Database Concepts - Code, Rule, Relation Tables Interest Type Rules (Rule_Part_Int_Type) Interest Types (Code_Part_Type_Int) Relation Table (Doc_File_Rltn)

  14. “Flat” Files (RPT*_Tables): Denormalized tables populated via triggers used for reporting to enhance performance. Database Concepts - “Flat” Files

  15. Database Concepts - “Flat” Files Rpt_Area OTHER REPORTING TABLES RPT_ACCT_GRP RPT_ALLOC_DATA RPT_AREA RPT_CALENDAR_EVENT RPT_COL_HEAD RPT_ORGANIZATION RPT_SURVEY_ACREAGE

  16. Table Joins: Tables are joined together by linking their primary keys, or other values. Joins establish the path from one table to another, or the link between the tables. The path for table joins may also create a hierarchy of joins. Some unique categories of joins that occur in the TLS model are: Joining tables via primary keys Joining tables via relation tables Joining tables to code tables Joining tables to remarks tables (one example of a hierarchy) Database Concepts - Table Joins

  17. Joining tables via primary keys Database Concepts - Table Joins

  18. Joining tables via relation tables Database Concepts - Table Joins

  19. Joining tables to code tables DatabaseConcepts - Table Joins

  20. Joining tables to remark tables Database Concepts - Table Joins This join is not required

  21. PROVISION DEFINITIONS CODE_PROVISION_DEFINITION DEFN_KEY DOCUMENTS DOC_FIL_RLTN FILE_KEY DOC_KEY RELATED_DOCUMENT DOC_KEY ORGANIZATION ORG_KEY PROVISION FILE_KEY PROV_KEY DEFN_KEY FILE_ORGANIZATION_RLTN FILE_KEY ORG_KEY DEPTH FILE_KEY DPTH_KEY CONTRACT, LEASE, DIV ORDER FILE_DEFINITION FILE_KEY FILE_PRNT_KEY FILE_LEVEL = 1 Lease/DO, 2 Tracts XREF_KEY GRP_CODE ACREAGE AREA FILE_KEY AREA_KEY COST FILE_KEY COST_KEY CROSS REFERENCES CROSS_REF_OBJECT_RLTN XREF_KEY (OR) XREF_KEY2 CROSS_REF_OBJECT XREF_KEY DATES CALENDAR_EVENT FILE_KEY EVENT_KEY OBL_KEY

  22. LEGAL RELATIONSHIPS CONTRACT, LEASE, DIV ORDER FILE_DEFINITION FILE_KEY FILE_PRNT_KEY FILE_LEVEL = 1 Lease/DO, 2 Tracts XREF_KEY GRP_CODE SURV_OBJ is primary key for the following legal tables: SURVEY_NON_JEFF SURVEY_TWSP_RANGE SURVEY_METS_BOUNDS SURVEY_LAT_LONG SURVEY_X_Y_COORD SURVEY_FILE_RLTN FILE_KEY SURV_OBJ SURVEY_ACREAGE FILE_KEY SURV_OBJ AREA_KEY LEGAL DESCRIPTIONS SURVEY_AREA (For State, County & Survey Name) SURV_TYPE, ST_CODE CNTY_CODE, ABST_CODE

  23. PARTICIPANTS, PAYEES, BILLING INTERESTS, BURDENS OBLIGATION / PAYMENTS OBLIGATION_STIPULATION FILE_KEY OBL_KEY STIP_CATG_CODE = PAY or OBL PAYEES / BILLING INTERESTS PAYEE FILE_KEY OBL_KEY PART_KEY DPSY_PART_KEY CONTRACT, LEASE, DIV ORDER FILE_DEFINITION FILE_KEY FILE_PRNT_KEY FILE_LEVEL = 1 Lease/DO, 2 Tracts XREF_KEY GRP_CODE PARTICIPANT_INTEREST FILE_KEY PART_KEY PART_INT_KEY BURDEN_GROUP_NUM PARTICIPANT FILE_KEY PART_KEY NAME_KEY NAME_ADDR_KEY PART_TYPE_CODE = PAY, BIL, DPS, PRT PARTICIPANT_HISTORY FILE_KEY PART_KEY PART_INT_KEY BURDEN_GROUP_NUM NAMES & ADDRESSES NAME NAME_KEY ADDRESS NAME_ADDR_KEY USER_ADDR_ID NAME_ADDR_RLTN NAME_KEY NAME_ADDR_KEY BURDEN_GROUP_HEADER DO_FILE_KEY BG_HEADER_KEY BURDEN_GROUP BURDEN_GROUP_MEMBERS BG_HEADER_KEY NAME_KEY NAME_ADDR_KEY

  24. Triggers: Internal programs the system uses to populate denormalized tables. Triggers fire upon insert, update or delete. Stored Procedures: Programs that run in Oracle to perform tasks. Views: Read only versions of the database that support queries and reports regarding a broad category of data by joining multiple tables into a single more easily used table. Database Concepts - Triggers, Stored Procedures, Views

  25. Database Concepts - Triggers Source File for File_Definition Triggers Oracle (User_Triggers) Compile Creates an Object that gets stored SQLPlus

  26. Database Concepts - Stored Procedures and Packages Source File for Activate/Inactivate Procedure Oracle (User_Source) Compile Creates an Object that gets stored SQLPlus

  27. Database Concepts - Views Source File for Participant_View Oracle (User_Views) Compile Creates an Object that gets stored SQLPlus

More Related