How to Design Database Using Microsoft Access

Implementing a Database Design

Print

Implementing a Database Design

There is no one correct design for the music scenario posed on the last page. The figure below depicts one design. Note the FK notation beside some of the fields.  FK stands for foreign key and indicates a field whose values uniquely identify rows in another table.  There is no way to designate a field as a foreign key in MS-Access.  For our purposes, you should just remember that building foreign keys into your database is what will enable you to create the table joins needed to answer the questions you envision your database answering.

Note also the 1 and * labels at the ends of the relationship lines, which indicate the number of times any particular value will appear in the connected field.  As shown in the diagram, values in the Albums table's Album_ID field will appear just once; the same values in the associated field in the Tracks table may appear many times (conveyed by the * character).  For example, an album with 10 tracks will have its Album_ID value appear in the Albums table just once.  That Album_ID value would appear in the Tracks table 10 times.  This uniqueness of values in fields is referred to as cardinality.

Figure 2.2: Instructor's homemade entity-relationship diagram for his version of the music album review database design.

If you have questions about this design or see ways to improve it, please share your thoughts in the Lesson 2 Discussion Forum.

In this part of the lesson, we'll create and populate some of the tables depicted in the diagram above to give you a sense of how to move from the design phase to the implementation phase of a database project. Table creation is something that can be accomplished using SQL, as we'll see later in the course. Most RDBMSs also make it possible to create tables through a GUI, which is the approach we will take right now.

An integral part of creating a table is defining its fields and the types of data that can be stored in those fields. Here is a list of the most commonly used data types available in Access:

• Text - for strings of up to 255 characters
• Memo - for strings greater than 255 characters
• Number - for numeric data
• AutoNumber - for automatically incremented numeric fields; more on this below
• Yes/No - for holding values of 'Yes' or 'No'; equivalent to the Boolean type in other RDBMSs
• Date/Time - for storing dates and times

Beyond the data type, fields have a number of other properties that are sometimes useful to set. Among these are Default Value which specifies the value the field takes on by default, and Required, which specifies whether or not the field is allowed to contain Null values. It is also possible to specify that a field is the table's primary key (or part of a multi-field primary key).

Perhaps the most commonly set field property is the Field Size. For Text fields this property defaults to a value of 255 characters. This property should be set to a lower value when appropriate, for a couple of reasons. First, it will reduce the size of the database, and secondly, it can serve as a form of data validation (e.g., assigning a Field Size of 2 to a field that will store state abbreviations will ensure that no more than 2 characters are entered).

When dealing with a Number field, the Field Size property is used to specify the type of number:

• Byte - for integers ranging from 0 to 255
• Integer - for integers in the range of roughly +/- 32,000
• Long Integer - for integers in the range of roughly +/- 2 billion
• Single - for real numbers in the range of roughly +/- 10³⁸
• Double - for real numbers in the range of roughly +/- 10³⁰⁸

As with Text fields, it is good practice to choose the smallest possible Field Size for a Number field.

With this background on fields in mind, let's move on to implementing the music database.

A. Create a new table

1. Open Access and from the opening screen click on the Blank Database icon.
2. In the right-hand panel, browse to your course folder and give the database the name music.accdb.
3. Click the Create button to create the new empty database. Access will automatically create and open a blank table called Table1.
4. Select View > Design View to begin modifying this table to meet your needs.
5. Give the table the name Albums.

   The Design View provides a grid for you to specify the names, data types and other properties for the fields (columns) you want to have in your table. Note that the table automatically has a field called ID with a data type of AutoNumber. The field is also designated as the table's Primary Key.

   Note: Most RDBMSs offer an auto-incrementing numeric data type like this. It's common for database tables to use arbitrary integer fields as their primary key. If you create a field as this AutoNumber type (or its equivalent in another RDBMS), you need not supply a value for it when adding new records to the table. The software will automatically handle that for you.

6. Rename the field from ID to Album_ID.
7. Beneath that field, add a new one called Title.

   With the 2013 version of MS Access the interface has changed some when it comes to the next two settings. The list of data types has become more streamlined and the way Field Size is set for numeric fields is different. I will indicate below what the differences are.

8. Set the Title field's Data Type to Text.

   In Access 2013 - set the Title field's Type to Short Text.

9. Set its Field Size to 200.  (This and several other options are found in the Field Properties section at the bottom of the window, under the General tab.  Note that the maximum value allowed for this property is 255 characters.)

   In Access 2013 - no need to set this, the Short Text type holds up to 255 characters.

   FYI, the "Long Text" option in Access 2013 ("Memo" option in older Access versions) can hold up to a Gigabyte and can be configured to hold Rich Text formatted information.

10. Repeat these steps to add the other fields defined for the Albums table:

    In Access 2013 - To set the type and size for numeric data, you first chose Number from the Type list then under the General tab click the FieldSize property box. Click the arrow and chose the desired field size (Integer, Long Integer, Float, etc.).

    Table 2.8: Albums Table

    Name	Type
    Album_ID	AutoNumber
    Title	Text (200)
    Artist_ID	Long Integer
    Release_Year	Integer
    Label_ID	Long Integer

    Note:

    Try adding a field with a name of Year to see why we used the name Release_Year instead.

11. When finished adding fields to the Albums table, click the Save button.
12. To add the next table to the database design, click the Create tab, then Table Design.
13. Repeat the steps outlined above to define the Artists table with the following fields:

    Table 2.9: Artists Table

    Name	Type
    Artist_ID	AutoNumber
    Artist_Name	Text (200)
    Year_Begin	Integer
    Year_End	Integer

14. Before closing the table, click in the small gray area to the left of the Artist_ID field to select that field then click on the Primary Key button on the ribbon.
15. Create the Labels table with the following fields:

    Table 2.10: Labels Table

    Name	Type
    Label_ID	AutoNumber
    Label_Name	Text (200)

16. Set Label_ID as the primary key of the Labels table.

B. Establish relationships between the tables

While it's not strictly necessary to do so, it can be beneficial to spell out the relationships between the tables that you envisioned during the design phase.

1. Click on Database Tools > Relationships.
2. From the Show Table dialog double-click on each of the tables to add them to the Relationships layout.
3. Arrange the tables so that the Albums table appears between the Artists and Labels tables.
4. Like you did when building queries earlier, click on the Artist_ID field in the Artists table and drag it to the Artist_ID field in the Albums table. You should see a dialog titled "Edit Relationships".
5. Check each of the three boxes: Enforce Referential Integrity, Cascade Update Related Fields, and Cascade Delete Related Records.

   Checking these boxes tells Access that you want its help in keeping the values in the Artist_ID field in sync. For example:

   • Let's say your Artists table has only three records (with Artist_ID values of 1, 2 and 3). If you attempted to add a record to the Albums table with an Artist_ID of 4, Access would stop you since that value does not exist in the related Artists table.
   • If you change an Artist_ID value in the Artists table, all records in the Albums table will automatically have their Artist_ID values updated to match.
   • If you delete an artist from the Artists table, the related records in the Albums table will be deleted as well.
   Without implementing these referential integrity settings, you run the risk of inaccuracies creeping into your database over time.
6. Click the Create button to establish the relationship between Artists and Albums.
7. Follow the same steps to establish a relationship between Albums and Labels based on the Label_ID field.
8. Save and Close the Relationships window.

With your database design implemented, you are now ready to add records to your tables.

How to Design Database Using Microsoft Access

Source: https://www.e-education.psu.edu/spatialdb/l2_p5.html

Share this post