CLR110 PRACTICE EXAM TEST QUESTIONS AND ANSWERS
GRADED A+
✔✔Define the Cardinality of a relationship - ✔✔Numerical relationship between tables
✔✔What would be the result set of linking PAT_ENC_HSP to HSP_ATND_PROV on
their PAT_ID columns - ✔✔For every attending provider the query would return each of
the hospital encounters the patient had even if they were not the attending provider for
that encounter. TOO Many results
✔✔What column should be used as the foreign key from HSP_ATND_PROV to
PAT_ENC_HSP? - ✔✔PAT_ENC_CSN_ID
✔✔What is the primary key of PAT_ADDRESS? - ✔✔PAT_ID and LINE
✔✔What is the value stored in the database for I EPT 80150? - ✔✔Only the ID number
✔✔Which part of the join does the Chronicles networked item map to? - ✔✔The foreign
key of the source table
✔✔Which column becomes the destination column for the join? - ✔✔The primary key of
the destination table
✔✔What is a common Clarity naming convention for columns that extract from
networked items? - ✔✔The column name ends in ID
✔✔If an item has an add type of Look Back and a response type of Single what would
be the primary key of the table that stores this data? - ✔✔The primary key would be the
CSN or Record ID and CDR (Contact Date Real)
✔✔Extracted tables - ✔✔Standard Tables: Tables populated using KB_SQL queries or
M routines
Typically follow the Add Type and Response Type primary key pattern
✔✔Views - ✔✔Queries stored in the database.
Do not store results
Typically start with V_
✔✔Derived Tables - ✔✔Populated by stored procedures (Stored SQL statement run
during ETL)
These are materialized views
Typically start with F_ or D_
,✔✔Are there any visual indicators in the ER Diagram that set views apart from other
database objects, like extracted tables? - ✔✔Yes Views show up with a
brownish/orangish background whereas extracted tables show up with a white
background. This draws your attention toward the views.
✔✔D_PROV_PRIMARY_HIERARCHY What information is stored in this derived table?
- ✔✔A providers primary department as well as that departments location and service
area. It also calculates "name with ID" columns for provider, department, location and
service area.
✔✔D_PROV_PRIMARY_HIERARCHY Why wouldnt a single extracted table be able to
supply the same information as this table? - ✔✔Extracted tables store data from a
single master file. Derived tables pull together data from SER, DEP LOC and EAF
✔✔What item stores the providers primary department in
D_PROV_PRIMARY_HIERARCHY - ✔✔I SER 40 stores the departments in which
users can schedule time with a provider. The first department listed is considered the
providers primary department
✔✔I SER 40 What are the add type and response type of this item? - ✔✔No Add,
Related/Multiple
✔✔I SER 40 What Clarity table extracts this item? - ✔✔CLARITY_SER_DEPT
✔✔Views and Derived tables - ✔✔Built to meet specific reporting needs.
Can simplify your report
✔✔Clarity Compass References - ✔✔Type
Scripts Tab
Dependent Database Objects
✔✔What type of database object are each of these?
CLARITY_TDL
OB_HISTORY
MU_EP_PROV_SUMMARY - ✔✔CLARITY_TDL : View
OB_HISTORY: View
MU_EP_PROV_SUMMARY: Derived Table
✔✔Which takes up more space on your Clarity server a Derived Table or a View? -
✔✔Derived tables or materialized vies are saved on the server but only the views query
is saved on the server. Derived tables take up more memory (space).
✔✔What is the add-type and response type of I EPT 130? - ✔✔No Add, Single
Response
, ✔✔When CAN an audit report be done using Caboodles type 2 data? - ✔✔No other
audit trail
A type 2 column exists
Dont need full change history
Dont need to know much about the change
An end of day value is sufficient
✔✔When CANT an audit report be done using Caboodles type 2 data? - ✔✔the Type 2
column is too new
ETL disruptions at critical points
Someone wants to know the source of the change:
EX: who, where, exactly when
Someone wants a full list of changes
EX: if we need to know what a provider saw at an exact time as part of a negligence
case
✔✔Audit Data only found in Clarity - ✔✔Tables that begin with AUDIT_ITM
Track changes to specific items in a master file
Record relevant audit information related to that change
ACCESS_LOG and ACCESS_WRKF
Record which users are accessing what information
✔✔True or False: Every single item in Chronicles is audited the exact same way -
✔✔False: There is no one way that information is audited. Dont forget to complete the
research in Chronicles step to find the big pictures
✔✔All data in a single extracted table in Clarity typically come from the same Chronicles
- ✔✔Master File
✔✔All data in a single extracted table in Clarity come from items with the same
___________ and ____________________ - ✔✔Add type and Response type
✔✔Multiple response Chronicles items typically extract to ___________
Clarity tables unless they are part of the same related group - ✔✔Separate
✔✔ADD TYPE - ✔✔Indicates if data is stored on a record or on a contact in the record.
Categorized as either No-ADD or Overtime.
No Add stored on the record. Always considered "current"
Overtime stored on a contact
✔✔RESPONSE TYPE - ✔✔Indicates how many of lines a data an item can hold
Categorized as either single or multiple.
Single holds only one line of data
GRADED A+
✔✔Define the Cardinality of a relationship - ✔✔Numerical relationship between tables
✔✔What would be the result set of linking PAT_ENC_HSP to HSP_ATND_PROV on
their PAT_ID columns - ✔✔For every attending provider the query would return each of
the hospital encounters the patient had even if they were not the attending provider for
that encounter. TOO Many results
✔✔What column should be used as the foreign key from HSP_ATND_PROV to
PAT_ENC_HSP? - ✔✔PAT_ENC_CSN_ID
✔✔What is the primary key of PAT_ADDRESS? - ✔✔PAT_ID and LINE
✔✔What is the value stored in the database for I EPT 80150? - ✔✔Only the ID number
✔✔Which part of the join does the Chronicles networked item map to? - ✔✔The foreign
key of the source table
✔✔Which column becomes the destination column for the join? - ✔✔The primary key of
the destination table
✔✔What is a common Clarity naming convention for columns that extract from
networked items? - ✔✔The column name ends in ID
✔✔If an item has an add type of Look Back and a response type of Single what would
be the primary key of the table that stores this data? - ✔✔The primary key would be the
CSN or Record ID and CDR (Contact Date Real)
✔✔Extracted tables - ✔✔Standard Tables: Tables populated using KB_SQL queries or
M routines
Typically follow the Add Type and Response Type primary key pattern
✔✔Views - ✔✔Queries stored in the database.
Do not store results
Typically start with V_
✔✔Derived Tables - ✔✔Populated by stored procedures (Stored SQL statement run
during ETL)
These are materialized views
Typically start with F_ or D_
,✔✔Are there any visual indicators in the ER Diagram that set views apart from other
database objects, like extracted tables? - ✔✔Yes Views show up with a
brownish/orangish background whereas extracted tables show up with a white
background. This draws your attention toward the views.
✔✔D_PROV_PRIMARY_HIERARCHY What information is stored in this derived table?
- ✔✔A providers primary department as well as that departments location and service
area. It also calculates "name with ID" columns for provider, department, location and
service area.
✔✔D_PROV_PRIMARY_HIERARCHY Why wouldnt a single extracted table be able to
supply the same information as this table? - ✔✔Extracted tables store data from a
single master file. Derived tables pull together data from SER, DEP LOC and EAF
✔✔What item stores the providers primary department in
D_PROV_PRIMARY_HIERARCHY - ✔✔I SER 40 stores the departments in which
users can schedule time with a provider. The first department listed is considered the
providers primary department
✔✔I SER 40 What are the add type and response type of this item? - ✔✔No Add,
Related/Multiple
✔✔I SER 40 What Clarity table extracts this item? - ✔✔CLARITY_SER_DEPT
✔✔Views and Derived tables - ✔✔Built to meet specific reporting needs.
Can simplify your report
✔✔Clarity Compass References - ✔✔Type
Scripts Tab
Dependent Database Objects
✔✔What type of database object are each of these?
CLARITY_TDL
OB_HISTORY
MU_EP_PROV_SUMMARY - ✔✔CLARITY_TDL : View
OB_HISTORY: View
MU_EP_PROV_SUMMARY: Derived Table
✔✔Which takes up more space on your Clarity server a Derived Table or a View? -
✔✔Derived tables or materialized vies are saved on the server but only the views query
is saved on the server. Derived tables take up more memory (space).
✔✔What is the add-type and response type of I EPT 130? - ✔✔No Add, Single
Response
, ✔✔When CAN an audit report be done using Caboodles type 2 data? - ✔✔No other
audit trail
A type 2 column exists
Dont need full change history
Dont need to know much about the change
An end of day value is sufficient
✔✔When CANT an audit report be done using Caboodles type 2 data? - ✔✔the Type 2
column is too new
ETL disruptions at critical points
Someone wants to know the source of the change:
EX: who, where, exactly when
Someone wants a full list of changes
EX: if we need to know what a provider saw at an exact time as part of a negligence
case
✔✔Audit Data only found in Clarity - ✔✔Tables that begin with AUDIT_ITM
Track changes to specific items in a master file
Record relevant audit information related to that change
ACCESS_LOG and ACCESS_WRKF
Record which users are accessing what information
✔✔True or False: Every single item in Chronicles is audited the exact same way -
✔✔False: There is no one way that information is audited. Dont forget to complete the
research in Chronicles step to find the big pictures
✔✔All data in a single extracted table in Clarity typically come from the same Chronicles
- ✔✔Master File
✔✔All data in a single extracted table in Clarity come from items with the same
___________ and ____________________ - ✔✔Add type and Response type
✔✔Multiple response Chronicles items typically extract to ___________
Clarity tables unless they are part of the same related group - ✔✔Separate
✔✔ADD TYPE - ✔✔Indicates if data is stored on a record or on a contact in the record.
Categorized as either No-ADD or Overtime.
No Add stored on the record. Always considered "current"
Overtime stored on a contact
✔✔RESPONSE TYPE - ✔✔Indicates how many of lines a data an item can hold
Categorized as either single or multiple.
Single holds only one line of data
