This is by far the most difficult stage of the process and one which may involve a certain amount of trial and error. The more you think through the structure of the database, the fewer alterations you will need to make at later stages. The main points you need to bear in mind are:

• the purpose of the database and the study it will support
• the nature and size of information available to you
• what information you need about each entry
• how many tables you will need

Each table holds information about one ‘entity’ which in turn can have a number of characteristics. For example, in the database on Student choices used in week 9, students are an entity having a number of characteristics and therefore have one table. Courses are also an entity having a number of characteristics and hence have a separate table. The relationship between the tables/entities lies in the fact that students take courses. The relationship is reflected in the structure of the database by the field which the two tables share: the course code Ccode.

These relationships within the database can be ‘one to one’ or ‘one to many’. Since we are concerned here mainly with using rather than designing a database, the distinctions have not been explained. A full explanation can be found in Lewis and Lloyd-Jones (1996) pp. 135-6 or in Weatherill and Hemingway (1994) which has an excellent section on designing your own database, §5.1-5.17.

For the purposes of example we are going to construct a partial database of nineteenth- and twentieth-century British novelists and their major writings. You will need one table for authors and one for their works. You will also find it helpful to refer to Saints 1150-1300: Background to the Data which contains a description of the different types of fields used in the Saints dataset you will be using for your assignment.
 

Table design

To create a new database, first select File/New database and then save it to your floppy disk as detailed in the instructions for exercise 1 of week 10.

Next, to create a new table, select the Table button and then New. The table design grid then appears, as below

 This is the stage at which you specify the structure of the table i.e. which fields of information are required and what form they take. In this case the fields for the first table will be Author, Date of birth, Date of death and Gender.

For each field you will need to define the type of data (text, number or date). ACCESS automatically selects text which is appropriate for two of the fields.

If your data were a number, you could specify its format by clicking on Field size, then on the button to the right of the cell and selecting a format from the list. Do this for the fields containing Date of death and Date of birth.

If you think that entries in a particular field will need more than 50 characters, increase the field size in the cell towards the bottom left of the window.

A field may also be defined as bivariate, that is as requiring an entry of Yes or No. If the field is defined in this way then only the terms ‘Yes’ or ‘No’ can be entered in that field.

For examples of these types of field, see Saints 1150-1300: Background to the Data where the various fields in Saints are described.

Enter all the fields. You must then choose a primary key field. This will be the unique identifier for the table i.e. you could not enter two records with the same data in that field, in this case two authors of identical names. To select the Author field, move the cursor to the left of the row until it becomes a black arrow. Click on that row and then click on the Primary Key button (the button on the tool bar with a small key on it).

If you do not select a primary key, ACCESS will not allow you to save the table and will offer to insert a primary key field for you. This will take the form of a new field which numbers each record with a unique ID. Table design is now completed so press the datasheet view button to see the table and enter your records. You will be prompted to save the design and to name the table. Call it Novels.

Entering data

Remember to be accurate when entering data as otherwise the value of the database and any conclusions derived from it will be totally undermined.

The cursor will be in the first cell of the table. Type in Collins William Wilkie. You will notice that the column is not wide enough. Widen it using the mouse as you would in EXCEL by placing the cursor on the edge of the column and dragging it to the right.

Use TAB or Enter to move to the next cell and type in 1824 as Date of birth, 1889 in the next cell as Date of death and finally M in the Gender field. When you press Enter in the final field the table will expand and add another row for the next record.

Because the author field is the primary key field you must enter data in that field before moving on to the next record. You do not have to enter data in the other fields. For example, if you were unsure of the date of birth of an author, you could leave that cell blank and add the information later.

Enter all the data in Novels Data: table 1. Close and save the table by clicking in the box in the upper left corner of the table and selecting Close.

Construct the second table in the same way using the information in Novels Data: table 2. Make the Title and Author fields the primary key fields by selecting both rows and then clicking on the primary key button. Put in as many entries as necessary for you to feel fully confident about entering data and for you to do some test queries on the database.

Further Tips

You can cut, copy and paste text and numbers in the table in much the same way as in WORD, although you cannot drag and drop. Use either Ctrl + X, C or V or the Edit menu or the buttons on the toolbar

You can sort any field alphabetically by selecting the column, then clicking on the Sort Ascending button on the tool bar.

You can make changes to the table design after you have entered the data, although it is best to put some thought into the design of the table beforehand to avoid these changes which can be inconvenient and run the risk of losing data. In Design view you can alter the field size, add or remove a field.

To add a field, select the row below the position of the new field and then select Edit/Insert Row. To remove a field, select the row and then select Edit/Delete Row. This will also delete the information contained in that field in the table.

 
 
 
To IT-MA home page
To Department of Historical Studies home page.

(c)R. Middleton 1997. Last modified 30 June 1998.