UnderstandingUnderstandingRelational DatabasesRelational Databases

Basic Concepts and Applications Basic Concepts and Applications for Qualitative Content Analysisfor Qualitative Content Analysis

Databases as Collections of ObjectsDatabases as Collections of Objects

A relational database holds a set of A relational database holds a set of “objects” of different types“objects” of different types TablesTables QueriesQueries FormsForms ReportsReports MacrosMacros

What Database Objects DoWhat Database Objects Do

TablesTables contain the actual data contain the actual data

FormsForms simplify entering and viewing data simplify entering and viewing data

QueriesQueries let you view parts of the data let you view parts of the data

Reports Reports format and print selected dataformat and print selected data

MacrosMacros let you automate command sequences let you automate command sequences

Tables as Database ObjectsTables as Database Objects

One database can hold many tablesOne database can hold many tables

The tables hold different kinds of dataThe tables hold different kinds of data

Data can be linked between tablesData can be linked between tables

The link between tables is a RELATIONThe link between tables is a RELATION

Table A Table B

What is Related?What is Related?

We say the two TABLES are “related”We say the two TABLES are “related”

We really mean certain RECORDS in the We really mean certain RECORDS in the

two tables are relatedtwo tables are related

Ability to relate records in multiple tables Ability to relate records in multiple tables

gives relational databases their powergives relational databases their power

Using the RelationsUsing the Relations

Linked records can be ENTERED using a Linked records can be ENTERED using a

data entry form that combines tables and data entry form that combines tables and

automates the linkageautomates the linkage

Linked records can be viewed together in Linked records can be viewed together in

queries that show only what you wantqueries that show only what you want

Linked records can be combined in reportsLinked records can be combined in reports

One-to-One RelationshipOne-to-One Relationship

Record 1

Record 2

Record 3

Record 4

Table A

Record 1

Record 2

Record 3

Record 4

Table B

One-to-Many RelationshipOne-to-Many Relationship

Record 1

Record 2

Record 3

Record 4

Table ARecord 1ARecord 1BRecord 1C

Record 2ARecord 2B

Record 3ARecord 3BRecord 3CRecord 3D

Record 4ARecord 4B

Table B

Many Tables Can Be LinkedMany Tables Can Be Linked

Table ATable B

Table C

Table D

Lookup Table E

Main Table with Many FieldsMain Table with Many Fields

Main ID field to link records to other tablesMain ID field to link records to other tables

PLUS fields for different pieces of data:PLUS fields for different pieces of data:

Field’s data occurs once in one recordField’s data occurs once in one record

OROR

Field is coded present or absent in recordField is coded present or absent in record

OROR

Field has mutually exclusive codesField has mutually exclusive codes

Subtable to Collect One TopicSubtable to Collect One Topic

Multiple ID fieldsMultiple ID fields Unique ID for each record in subtableUnique ID for each record in subtable Field for ID to link to main tableField for ID to link to main table Sequence field counts records linked to one caseSequence field counts records linked to one case

Fields for one special set of dataFields for one special set of data text field to hold actual terms, uncodedtext field to hold actual terms, uncoded code field to hold the codes for the termscode field to hold the codes for the terms possibly memo field to hold context of term’s usepossibly memo field to hold context of term’s use might add other information related to term’s usemight add other information related to term’s use

Two Common Subtable SituationsTwo Common Subtable Situations

Partial Code KnownPartial Code Known

ID fields (numeric)ID fields (numeric)

Numeric field for Known Codes*Numeric field for Known Codes*

Text field for Uncoded Terms Text field for Uncoded Terms

*May link to lookup table that *May link to lookup table that

holds codesholds codes

No Code Known YetNo Code Known Yet

ID fields (numeric)ID fields (numeric)

Text field for terms Text field for terms

Later add numeric field for codesLater add numeric field for codes

Could have context memo fieldCould have context memo field

Could have other usage fieldsCould have other usage fields

(Could create lookup table later to (Could create lookup table later to

hold the code categories) hold the code categories)

Lookup Table Holds a CodeLookup Table Holds a Code ID field is the numeric codeID field is the numeric code Second field has code namesSecond field has code names Third field can hold descriptions of codesThird field can hold descriptions of codes It holds the CODE but not the actual dataIt holds the CODE but not the actual data

Lookup Table

Code 1

Code 2

Code 3

Collection SubtableCase 1ACase 1BCase 1CCase 2ACase 2BCase 3ACase 3B

Combining in Data Entry FormCombining in Data Entry Form

Main form holds main table dataMain form holds main table data form displays one main record at a timeform displays one main record at a time

Embedded subforms hold subtable dataEmbedded subforms hold subtable data form displays multiple records in datasheet viewform displays multiple records in datasheet view

Lookup table embedded on formLookup table embedded on form as a dropdown box or scrolling listas a dropdown box or scrolling list view the code names on the listview the code names on the list click on choice and CODE is entered on recordclick on choice and CODE is entered on record May be entered on main table or a subtableMay be entered on main table or a subtable

Where Lookup Code Data GoesWhere Lookup Code Data Goes

If the lookup is a code for something that If the lookup is a code for something that occurs ONCE per record, it goes into a field occurs ONCE per record, it goes into a field on that record.on that record.

If the lookup information could occur more If the lookup information could occur more than once you have some choices.than once you have some choices. create a fixed number of fields, each with the create a fixed number of fields, each with the

same lookup and some way to order themsame lookup and some way to order them create a collection sub-table that can accept create a collection sub-table that can accept

multiple rows of data, including the lookup code in multiple rows of data, including the lookup code in each row, linked to one record. each row, linked to one record.