Hands-On Microsoft Access: A Practical Guide to Improving Your Access Skills

In Chapter 2, "Database Design," you developed tables and fields for the Classic TV database. In Chapter 3, you identified the existing relationships between tables and developed the final field list shown in Table 4.1. (If you haven't read these two chapters, I strongly encourage you to do so before proceeding.)

Table 4.1. Classic TV Database Final Field List

Programs

Broadcasts

Genres

Networks

Actors

Roles

ProgramID PK

ProgramID CPK/FK

GenreID PK

NetworkID PK

ActorID PK

ProgramID CPK/FK

ProgName

NetworkID CPK/FK

GenreName

NetwOfficial Name

ActorFirst Name

ActorID CPK/FK

GenreID FK

BrdYearStart

GenreDescr

NetwPopular Name

ActorLast Name

RoleFirst Name

ProgLocation

BrdYearEnd

 

NetwFounder

ActorGender

RoleLast Name

ProgSynopsis

  

NetwNotes

ActorBio

RoleYear Start

ProgNotes

RoleYearEnd

RoleNotes

RoleOccup

Note: Actual field names contain no spaces (e.g. RoleYear Start is RoleYearStart).

When you identified all the relationships in the database, you found that some tables had many-to-many relationships. Recognizing the problems many-to-many relationships pose, you resolved them by creating linking tables. The new tables comprised two one-to-many relationships.

No tables in the Classic TV database have one-to-one relationships. Thus, all the tables are on either side of one-to-many relationships. Using the Relationships window, you can formally establish these one-to-many relationships.

Table 4.2 is a chart of the one-to-many relationships in the Classic TV database. The matrix shows the relationships the tables on the x-axis have with the tables on the y-axis. For example, viewing the relationship between the Genres and Programs tables in the first row, you can say Genres is on the one side and Programs is on the many. (Don't worry much about understanding this tableyou will soon see a figure that will make these relationships much clearer.)

Table 4.2. Classic TV Database Relationships Matrix

 

Programs

Genres

Networks

Actors

Roles

Broadcasts

Programs

 

1:M

  

M:1

M:1

Genres

M:1

     

Networks

     

M:1

Actors

    

M:1

 

Roles

1:M

  

1:M

  

Broadcasts

1:M

 

1:M

   

Viewing Relationships in the Relationships Window

A tour of all the features of the Relationships window will be much more meaningful to you after you create some relationships. But an overview of a typical relationships scheme should be useful now, just so you can see what you're trying to accomplish.

The Relationships window contains field lists of the various tables in the database (see Figure 4.1). All the relationships of the Classic TV database have been established with referential integrity enforced (I'll explain what that is soon) and are currently displayed. The primary keys of the tables are in bold. The table on the "one" side of the relationship has a 1 next to its primary key. An infinity sign next to the foreign key in the related table indicates that it's on the "many" side.

Figure 4.1. The Relationships window, with all the relationships of the Classic TV database displayed.

As one example, Genres and Programs have a one-to-many relationship. There is a 1 next to GenreID, the primary key of the primary table. In the related table of Programs, there is an infinity sign next to the foreign key GenreID.

All the tables have at least one relationship with one other table in the database. Thus, you can combine values from any of the tables through the nexus of relationships that exists among them. As you will see in succeeding chapters, this capability to bring values together is central to manipulating and massaging data in an Access database.

Q&A

Q1:

You say the primary keys are all in bold. In the Broadcasts table, both ProgramID and NetworkID are bold, yet they both have infinity signs next to them. Doesn't that mean they are foreign keys?

A1:

The primary key of the Broadcasts table is a composite of both the ProgramID and NetworkID fields. So although the two fields individually are foreign keys, together they are the primary key of the Broadcasts table. In other words, a field can be a foreign key and part of a primary key at the same time. These topics were discussed in Chapter 3.

Creating a Relationship in the Relationships Window

Let's start by creating a plain vanilla relationship in the Relationships window. Download the ClassicTVChap4.mdb database from the companion website at www.awprofessional.com/title/0321245458 to a convenient folder on your hard drive and open it.

1.

In the Database window, Tables should be selected. Double-click the Programs table to open it.

The table includes the fields you developed in Chapters 2 and 3, along with a few records I've added. Each record uniquely identifies a separate TV program. Note that the table contains the GenreID field from the Genres table as a foreign key. Each GenreID can appear many times in the Programs table. Close the Programs table.

2.

Open the Genres table.

The table has data about genres. Each record uniquely identifies a different genre. Each GenreID can appear only once in the Genres table. There are a total of eight records; there is a GenreID 8, but no GenreID 9. Close the Genres table. Let's formally create the relationship between the Programs and Genres tables.

3.

Choose Tools, Relationships.

I have eliminated all the relationships you saw in Figure 4.1, so you start from a clean slate.

4.

In the Relationships window, select GenreID in the Genres field list (if it is not already selected). Drag and drop this field directly on the GenreID in the Programs field list. The Edit Relationships dialog box opens (see Figure 4.2).

Figure 4.2. You create relationships in the Edit Relationships dialog box.

In the Edit Relationships dialog box, the GenreID from the Genres table is on the left (the Table/Query column) and the GenreID from the Programs table is on the right (Related Table/Query). GenreID is the primary key in the Genre table and is on the "one" side of the relationship. The GenreID in the Programs table is a foreign key on the many side of the relationship.

Below the small grid at the top are extremely important options I'll discuss a little later. For now, I also ask you to ignore the Join Type button, which opens the Join Properties dialog box. Joins are discussed at length in the section on multitable queries in Chapter 8, "Queries." Finally, note that at the bottom, the relationship type is One-to-Many.

5.

Click Create. There's now a line between the two GenreID fields, showing that a relationship has been created. Close the Relationships window.

Now let's see how one of the available options, referential integrity, affects this relationship.

Related

Категории