Understanding Relational Databases

1. UnderstandingRelational Databases Basic Concepts and Applications for Qualitative Content Analysis

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

3. 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

4. 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

5. 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

6. 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

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

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

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

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

11. Subtable to Collect One Topic • Multiple ID fields • 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 • 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

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

13. 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 Collection Subtable Lookup Table Case 1A Case 1B Case 1C Case 2A Case 2B Case 3A Case 3B Code 1 Code 2 Code 3

14. Combining in Data Entry Form • Main form holds main table data • form displays one main record at a time • Embedded subforms hold subtable data • form displays multiple records in datasheet view • Lookup table embedded on form • 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

15. 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. • 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.