Collection of Slides for Presentation

 Use of SQL – The

Patricia database

•

Try to think of the Patricia database in such a

way that each table, corresponds to a

window/section of Patricia.

•

This is not the full truth always, as there

sometimes are more related tables holding

some of the information from the

view/windows/section of Patricia that you are

looking at.

Case Related Tables

•

Primary case table: PAT_CASE

–

Primary column: CASE_ID

•

CASE_ID is the unique identifier for any case

in Patricia

•

You can find the Case ID of any case by using

Ctrl + Left Click on the Close button on a case

Case Related Tables

•

Case ID can be found in all tables that have

information stored related to a case.

•

Primary Case Related Tables:

–

CASTING - The database representation of the

Parties tab

–

DIARY_LINE – Generic table which holds

identifiers for which diary fields are on a case

•

DIARY_DATE – Holds all the Diary Dates of a case

•

DIARY_TEXT – Holds all non date Diary Fields of a case

Case Related Tables

•

Primary Case Related tables (cont.):

–

DESIGNATED_STATES – Contains each designation of

a case

–

PAT_DOC_LOG – Contains entries for documents on

a case

–

EVENT – Holds the term information of a case

–

PAT_CASE_PICTURE – Device related information on

a case

–

VW_CASE_NUMBER – Holds the full Case Number

and Matter ID of a case. This is the table used to find

cases using Case Find.

Case Related Tables

•

All the afore mentioned tables contain the

CASE_ID column

•

This can then be used to identify entries in any

of the tables and link them to a certain case

•

Many of these tables have a so called “foreign

key” placed on the CASE_ID column.

•

This means that there cannot exist an entry in

e.g. CASTING table, that holds a value in it’s

CASE_ID column that does not exist in the

PAT_CASE table

Name Related Tables

•

The main table for name records is called

PAT_NAMES

•

The main identifier is the NAME_ID column

•

NAME_ID column holds the actual Name

Number used in Patricia

Name Related Tables

•

Primary Name Related tables:

–

PAT_NAMES_ENTITY – Holds the “actual” name

information of a name record

–

PAT_NAMES_ADDRESS – Holds the address

information of a name record

–

NAME_ADD_FIELDS – Holds the Extended Name

Information details

–

PAT_NAMES_REF – Holds connection entries of a

name record

Maintenance Related Tables

•

Many Case/Name related tables have a

corresponding Maintenance table.

•

This is where all the Maintenance

configuration are stored.

Maintenance Related Tables

•

Important Maintenance Tables:

–

PAT_CONFIGURATION – This contains all the Basic

Configuration value setup

•

PAT_CONF_COMPUTER/GROUP/PERSON_VALUES –

These three different tables holds any Basic

Configuration setting done on either Computer,

Group or Person

–

DIARY_FIELD – All basic information regarding

created Diary Fields are stored in this table

Maintenance Related Tables

•

Important Maintenance tables (Cont.):

–

CASE_ROLE_TYPE – The Name Type setup is

stored here.

–

STATE_NAME – The Country Label setup is stored

here

–

PERSON – This table contains all the Patricia

Logins

–

EVENT_SCHEME – General Term setup details are

stored here

Maintenance Related Tables

•

Important Maintenance tables (Cont.):

–

TEAM – Team Setup

–

ROLES – Available Roles

Relations

•

Since the Patricia database is built on a so

called Relational Database, we are not storing

everything in a single table, but instead have

split up information across multiple tables

and are using relations to identify the

connections between these two.

Relations

•

Consider that we want to find all terms of a

case, where the term has a certain category

and where a certain name number is the

applicant.

•

To achieve this, we would need to look into

multiple tables in order to find a matching

criteria.

Relations

•

If we have a look at the below query.

Relations

•

Let us break the query down row by row:

All SQL Queries start with the keyword SELECT and

this defines the information that we want to get.

This line means “Find us the value of column

case_number and the value of column

event_scheme_id”

Which in Patricia-speak means the actual Case

Number and the Term Number.

Relations

•

The next row starts with the keyword FROM.

•

This section defines the tables that we want

to use in our query. Whether it is to get

information from, or to be used for filtering.

Relations

•

The next section is five rows. They all start

with keyword combination of INNER JOIN.

•

This means that we are joining in other

tables, based of a common value existing in

any already specified table

Relations

•

Let us go through the joins line by line

Here we are joining in the table called EVENT

based off of the case_id column, existing in

both table VW_CASE_NUMBER as well as

EVENT.

So only records where these two values

matches will be returned

Relations

Here we are joining in the EVENT_SCHEME

table, which is the table containing the

maintenance setup for terms.

And we are joining this on the

EVENT_SCHEME_ID, i.e. Term Number, column

existing in both tables

Relations

Here we are now joining in the

EVENT_CATEGORY table.

This table holds the maintenance setup for

available Event Categories, that we then can

apply on to terms.

And here we are then joining this together on

the EVENT_CATEGORY_ID column existing in

both tables.

Relations

Now we are joining in the CASTING table,

which holds parties information from a case.

This is then joined in on the CASE_ID column

existing in both tables.

Relations

Here we are joining in the table

CASE_ROLE_TYPE, which is the maintenance

table containing name type setup.

This is then done by matching the

ROLE_TYPE_ID column from the CASTING table

on the case.

Relations

The next line starts with the keyword WHERE, and

this defines the section where we will be starting

to filter out items.

As you might noticed, we joined in far more tables

than we were fetching information from, this is

because it is necessary if you want to filter on

them as well.

Here we are now saying, only find me information

where the Role Type label is “Applicant”

Relations

The next line starts with the keyword AND.

This can be used after you started the so called

WHERE clause and allows you to add more

things to filter on.

Here we are saying that we only want

information where the Name Number from the

parties tab is 50042

Relations

And the final line, says that we only want rows

where the Term Category Label is “DEADLINE”.

Relations

•

So this is how relations between the different

tables work.

•

This was just a short example of relations in

the Patricia database, there are many many

more.