KCL • CCH • Minor programme • AV1000 • Relational analysis

AV1000
Fundamentals of the digital humanities
Books and borrowers: a two-table database

  1. The problem
  2. Creating and filling the database
    1. The structure
    2. The relationships
    3. The data
  3. Queries
    1. Composing simple queries
    2. Help with more complex queries

I. The problem

This is an exercise in creating a simple relational database from data already supplied in tabular form. It thus does not involve designing the database, rather you are asked to implement an existing design in Access. Two tables are given, one of books, the other of borrowers, such as a very simple library system might use. See the Data for the “Books and Borrowers” database page now to get an idea, but working from a printed copy is best.

Design

Entity diagram for BOOK

In database terms there are clearly two entities. The first is BOOK, expressed by this formula:

Book (ID, ISBN, title, authors, year, publisher, city, series)

The second entity is BORROWER, expressed by this formula:

Borrower (Borrower ID, Name, Department, Status, Telephone)

Note, however, that the relationship between these entities is in the real world one-to-many, i.e. one borrower to many books. Therefore, according to the rules of relational design, the primary key of the borrower must be included among the attributes of the book. Hence the complete specification is represented as follows:

Book (ID, Author, Title, City, Publisher, Year, ISBN, Borrower ID)

Borrower (Borrower ID, Name, Department, Status, Telephone)

with the resulting diagram:

Entity diagram for BOOK and BORROWER

In the above, the field in the Book table corresponding to the primary key of the Borrower table has been marked with double underlining. This fields is a Foreign Key—i.e. the Primary Key of a different entity.

II. Creating and filling the database

To create a database you must first create the structure, identify the relationships connecting the tables, then fill each table with data.

A. The structure

Follow these steps to create two tables called Book and Borrower:

  1. In Access choose to create a new, blank database through the cue-card, selecting Blank Database; alternatively, do it directly by clicking on the first icon on the toolbar or by choosing the New… option under the File menu, and then Blank database. Then specify that it be stored on your hard disk. You should see the database window; as a default the Tables tab will be selected—though of course the window will not show any tables.
  2. Create a new table for the entity Book: click on New, then select Design View. You will then see a table creation form in the top half of which are blanks for field names and data types to be specified, and at the bottom an area for Field Properties.
  3. Click in the first Field Name blank and following the design enter a short but descriptive name for your first field, e.g. “Author”.
  4. Click then in the corresponding Data Type field to specify the type. Text will be assumed as the default—which is in fact what you want. (If you click on the drop-down menu arrow that appears when you are in the Data Type field, you will see the other options.) Note that below, under Field Properties, you can specify the maximum length of the field (default is 50 characters for text). Adjust this to your probable maximum, which you can estimate by looking at the data provided. (You can always expand this later without any danger to the data.) Also pay attention to the option for specifying that a value is to be Required for that field (i.e. that it cannot be left blank).
  5. Continue filling out the form until you have all the fields named, data types and field properties specified for the Book table. Before you exit, identify which field is your Primary Key, highlight the entire row of this field by clicking in the space immediately to the left of the field name and select the Primary Key option under the Edit menu. (Another way to get to this option is to right-click on the field.)
  6. Dismiss the table creation form and save the newly structured (but still empty) table under the name “Book”.
  7. Repeat the above for the Borrower entity.

B. The relationships

To this point you have created the tables, one for each entity in your design, and identified the key for each. The next step is to specify how the tables are related to each other. (This is not a required step—it can be done while you are constructing a query—but taking care of the relationships explicitly at this point is a good idea.)

  1. With the database window on screen, select Relationships under the Tools menu and select both tables to be added to the display. Each will then be represented graphically as a rectangle. Note that you can drag the boundaries of these rectangles so that all the field names are displayed simultaneously.
  2. To specify a relationship between two tables, place the cursor on the field in the first table (e.g. Book) to be related, then hold down the mouse and drag into the second table to the corresponding field to be related and release. Click on Create to establish the relationship.

    Note that at any time you can Edit the relationship you have created by right-clicking the mouse while the cursor is touching the line representing the relationship.

  3. Once you have the relationship defined, dismiss the window, choosing to save your changes.

C. The data

  1. From the Database window, select the Borrower table and click on Open. Click in the first box under the Name field, and copying data from that which is provided on the linked page, Data for the “Books and Borrowers” database, fill out the database. (Note that you can copy-and-paste from the Web page directly, though this may be more work than is worth the trouble.) When you are done, dismiss the window—the data will automatically be saved.
  2. Now select the Book table. Again fill it out using the data provided (and, again, note the possibility of copying-and-pasting from the Web page). While you are entering data for the books, randomly assign them as having been borrowed by borrowers from the other table. Be careful here to enter the foreign composite keys carefully. Dismiss the window when you are done.

III. Queries

A question asked of a database is known as a “query”. The distinction is a useful one because the kinds of questions we ask, in English or another natural language, must be translated into a specific format for a given database program, taking into account the structure of the database, the relationships between tables and the information likely to be of use in the answer. Even an apparently well-defined question will sometimes require significant interpretation when translated into a query.

A. Composing simple queries

  1. First think through your possible queries. There are two parts: the questions you want to ask (e.g. which books have been borrowed by whom? which by those attached to the French Department?) and the information you want to be displayed for successful hits (e.g. Author, Title, Name and Telephone number (of the borrower)).
  2. From the Database window, click on the Queries tab. To create a new query, click on New. You will then see the New Query dialogue box; select Design View, click on OK.
  3. Select both tables to include in your query. Click on Add for each selection, then when you are done, on Close. You will see a graphical representation of the tables at the top of the Select Query dialogue box with the relationship among the tables represented by connecting lines.
  4. A query is constructed in the bottom half of the Select Query dialogue box. Begin with a simple one: a listing of all books by Author and Title with the Name, Department and Telephone number for each. Begin by clicking on the first Field specification and using the drop-down menu, select Books. Author. Move to the second Field specification and select Books. Title, the third for Borrowers. Surname, the fourth for Borrowers. Forename, the fifth for Borrowers. Title, the sixth for Borrowers. Department and finally the seventh for Borrowers. Telephone number. When you are done, run the query by clicking on the Run button (marked by an exclamation point) on the toolbar. You should see a complete listing of books and borrowers.
  5. Next try a query with a selection criterion. Save the first query, then reselect it using the Design button on the Database window—NOT the Open button (which will run the query). In the Criteria box under the Department field in the bottom half of the Select Query window, enter “French”. Run the query. You should see a listing of only those books borrowed by those associated with French. (If you do not get anything from the query, it means that you have made a mistake.) Note that since you are selecting only French department examples, you do not need the contents of the Department field reported to you. Save the current query, reopen it with the Design button, then click on the Show box in the Department column so that a tic-mark no longer appears there. How are the results different?
  6. Save this query, then reopen it with the Design button. You will note that Access has put quotation marks around “French” to indicate it is the search term. These quotation marks are needed when you construct an either/or query, such as “French” or “English”. Try that query now to see what happens (but before you do, click on the Show box under the Department column so that the tic-mark appears). Then try “French” or “German”; why does this query get no results?

IV. Help with more complex queries

  1. Access supplies a Query Builder to help compose more complex queries than the above. If you right-click the mouse in a Criteria field and select the Build option, you will get the Expression Builder. Try that now, again for the Department field.
  2. Double-click Tables and you will see the two listed. Click on Borrower and the fields in it will appear to the right. Double-click now on the field name Department—it appears in the expression window above. Now click on the “is not equal to” button (denoted by “<>”), then type in “French” (without the quotation marks) and click on OK. The constructed query will appear in the Criteria field. Now run the query and you should see a listing of all those books borrowed by other than those associated with French.
  3. Try now building a query with the Expression Builder to discover all books borrowed by people in either of two departments from among the borrowers you assigned.

revised February 2008