Understanding Relational Databases Basic Concepts and Applications for

Understanding Relational Databases Basic Concepts and Applications for Qualitative Content Analysis

Databases as Collections of Objects Ø A relational database holds a set of “objects” of different types l l l Tables Queries Forms Reports Macros

What Database Objects Do Ø Tables contain the actual data Ø Forms simplify entering and viewing data Ø Queries let you view parts of the data Ø Reports format and print selected data Ø Macros let you automate command sequences

Tables as Database Objects Ø One database can hold many tables Ø The tables hold different kinds of data Ø Data can be linked between tables Ø The link between tables is a RELATION Table A Table B

What is Related? Ø We say the two TABLES are “related” Ø We really mean certain RECORDS in the two tables are related Ø Ability to relate records in multiple tables gives relational databases their power

Using the Relations Ø Linked records can be ENTERED using a data entry form that combines tables and automates the linkage Ø Linked records can be viewed together in queries that show only what you want Ø Linked records can be combined in reports

One-to-One Relationship Table A Table B Record 1 Record 2 Record 3 Record 4

One-to-Many Relationship Table A Record 1 Record 2 Record 3 Record 4 Table B Record 1 A Record 1 B Record 1 C Record 2 A Record 2 B Record 3 A Record 3 B Record 3 C Record 3 D Record 4 A Record 4 B

Many Tables Can Be Linked Table B Table A Table D Table C Lookup Table E

Main Table with Many Fields Main ID field to link records to other tables PLUS fields for different pieces of data: l Field’s data occurs once in one record OR l Field is coded present or absent in record OR l Field has mutually exclusive codes

Subtable to Collect One Topic Ø Multiple ID fields l l l Unique ID for each record in subtable Field for ID to link to main table Sequence field counts records linked to one case Ø Fields for one special set of data l l text field to hold actual terms, uncoded code field to hold the codes for the terms possibly memo field to hold context of term’s use might add other information related to term’s use

Two Common Subtable Situations Partial Code Known No Code Known Yet Ø ID fields (numeric) Ø Numeric field for Known Codes* Ø Text field for terms Ø Text field for Uncoded Terms Ø Later add numeric field for codes Ø Could have context memo field *May link to lookup table that Ø Could have other usage fields holds codes Ø (Could create lookup table later to Ø hold the code categories)

Lookup Table Holds a Code Ø ID field is the numeric code Ø Second field has code names Ø Third field can hold descriptions of codes Ø It holds the CODE but not the actual data Lookup Table Code 1 Code 2 Code 3 Collection Subtable Case 1 A Case 1 B Case 1 C Case 2 A Case 2 B Case 3 A Case 3 B

Combining in Data Entry Form Ø Main form holds main table data l form displays one main record at a time Ø Embedded subforms hold subtable data l form displays multiple records in datasheet view Ø Lookup table embedded on form l l as a dropdown box or scrolling list view the code names on the list click on choice and CODE is entered on record May be entered on main table or a subtable

Where Lookup Code Data Goes Ø If the lookup is a code for something that occurs ONCE per record, it goes into a field on that record. Ø If the lookup information could occur more than once you have some choices. l l create a fixed number of fields, each with the same lookup and some way to order them create a collection sub-table that can accept multiple rows of data, including the lookup code in each row, linked to one record.