Week 6 (Feb 21, 23):

Tuesday From Spreadsheets to Relational Databases

  • Learning Objectives:
    • Understand the concept, strengths, and structure of relational databases
  • Readings:

Study Guide:

Today we look more closely at the data structure of relational databases. Schreibman et al. (2004) discusses the design and implementation of a simple relational database in general terms, and Fuller (2013), one of the designers of CBDB, lays out the rationale for relational databases as a more effective way of organizing data with specific references to CBDB.

Consider the following questions:

  • What is a relational database?
  • Compared to Excel spreadsheets, what advantages does a relational database have in organizing complex data?
  • What issues do we need to consider first when creating a relational database?

Thursday Exploring Change: Time-Enabled GIS

Study Guide:

  1. Review the basics of creating a relational database in Microsoft Access and building queries. Watch the following tutorials, if necessary, on the Microsoft website: Access 2013 videos and tutorials. Each video is a few minutes long.
    • On building a relational database: “Design and build tables for a database (Access basics, part 1),” “Create table relationships (Access basics, part 2),” and “Create your first Access 2013 database”
    • On creating queries: “Introduction to queries (Access basics, part 3 )” [The first three videos cover the basics we learned on Tuesday]
    • To review what we learned in class, you may also download the Access database we created. Click here.
  2. Exercise. Now test your skills by completing the following exercise. Download KinshipAndPostings v1_Tables Only.accdb from Moodle (click here). Create a query that reports who held which office(s) when and where. Please complete this exercise before Thursday’s class. Bring your results (and questions) to class. This exercise will help you develop the skills needed for completing the end-of-the-week assignment of this week.
  3. Explore one or two of the websites listed above. Consider: What data does each site collect and share? How does each site organize its data? What types of queries does it allow? The following presenters should prepare a 5-minute presentation on the assigned site. In your presentation, introduce briefly what data each website hosts and give one or two demonstrations of how to query data from it. No Powerpoint slides necessary.

End-of-the-Week Assignment: Basic CBDB Queries

1. Use the online querying system or Microsoft Access edition of CBDB to find the basic biographical information regarding a person named “Fan Zhongyan” (Song dynasty).

  • Where did Fan hail from? Where did he live later on in his life?
  • How and at what age did he enter government service?

Report your answers to the above questions in a Word or Text document.

2. Now open the Microsoft Access edition of the China Biographical Database (CBDB). Complete the following queries. The CBDB User’s Guide provides detailed instructions that help you complete this assignment. The Appendix in the User’s Guide also gives instructions on how to install the database properly (i.e., how to manage linked tables).

    • Find all persons who entered government service through the jinshi examination [i.e., “examination: jinshi (general)”] AND were from Chengdufu Lu [N.B. Lu means “circuit”]. Click the “Save to GIS” button to save your results. (Section A in Chapter 3 of the User’s Guide provides detailed instructions.)
    • Find all persons who held “Grand Councilor Offices” in the Song dynasty. Click “Save People to GIS” button to save your results. (Section C in Chapter 3 of the User’s Guide provides detailed instructions, which uses the “Vice Grand Councilor” as an example to illustrate the procedure.)

After you finish, you will have a Word document and two .tab files. Compress them into one zipped file. Submit it the drop box on Moodle.