Introduction to Data Relationships

The Keys to a Relationship

Introduction to Relationships

A relational database is a computer application that contains two or more tables so that information in one table is made available to another table or other tables that need(s) it. The information is entered once in one particular table. If the same information is needed in another table, it is simply identified one way or another. This reduces, and can eliminate, the likelihood of mistakes that result from duplicate data.

A Primary Key

To create a relational database, you must have a way for tables to communicate or relate to each other. To start, for a table to make its information available to the other table(s), the table must have a way to be identified. This is done by creating a field used to refer to that table. This field is called a primary key. The primary key can be represented by one field or it can be a combination of fields.

To make a field a primary key, display its table in the Design View. You have two options:

• You can right-click the field and click Primary Key
• In the Tools section of the Design tab of the Ribbon, you can click the Primary Key button

The field that is made the primary key would then appear with a key icon to its left. When you click a field that is a primary key, the Primary Key button becomes highlighted .

Practical Learning: Introducing Relationships

1. Start Microsoft Access
2. Click Blank Desktop Database
3. Type Ceil Inn1 (this is the name of a hotel) as the File Name of the database
4. Click Create
5. On the default table, double-click ID to put it into edit mode
6. Type MaritalStatusID and press Enter
7. In the menu that appears, click Short Text
8. Type MaritalStatus
9. Click the cell under MaritalStatus and type Single
10. Press the down arrow key and type Married
11. Close the table
12. When asked whether you want to save, click Yes
13. Set the name to MaritalsStatus
14. Click OK
15. On the Ribbon, click File and click Options
16. In the left frame, click Current Database. In the right frame, click the Overlapping Windows radio button
17. Click OK
18. Read the message box and click OK
19. On the Ribbon, click Create
20. In the Tables section, click Table
21. On the table, double-click ID to put it into edit mode
22. Type FilingStatusID and press Tab
23. In the menu that appears, click Short Text
24. Type FilingStatus
25. Click the cell under FilingStatus and type Unknown
26. Press the down arrow key and type Head of Household
27. Press the down arrow key and type Married Filing Jointly
28. Close the table
29. When asked whether you want to save, click Yes
30. Set the name to FilingsStatus
31. Click OK
32. On the Ribbon, click Create
33. In the Tables section, click the Table Design
34. As the caret in blinking under Field Name, type EmployeeNumber and press Tab
35. Click the arrow of the data type and select Number
36. In the Tools section of the Ribbon, click the Primary Key button
37. Click the cell under EmployeeNumber and type FirstName and press the down arrow key
38. Under FirstName, type LastName and press the down arrow key
39. Under LastName, type Title
40. In the left section of the title bar of Microsoft Access, click the Save button
41. Set the name to Employees and press Enter
42. On the Ribbon, click File and click Open
43. In the list of files, click FunDS1 from the previous lesson
44. On the Ribbon, click Create
45. In the Tables section, click Table Design
46. As the caret in blinking under Field Name, type EmployeeNumber
47. Press Tab and type n.
    Notice that number has been selected
48. Click the cell under EmployeeNumber and type FirstName
49. Click the cell under FirstName and type LastName
50. Click the cell under LastName and type Title
51. Right-click EmployeeNumber and click Primary Key
52. To save the table, right-click its tab and click Save
53. Set the name to Employees and click OK
54. On the Ribbon, click Create
55. In the Tables section, click Table Design
56. As the caret in blinking under Field Name, type ItemNumber
57. Click the arrow the Data Type combo box and select Number
58. By typing a field name and pressing the down arrow key, create the following fields:
    Manufacturer
    Category
    SubCategory
    ItemName
    ItemSize
59. Right-click ItemNumber and click Primary Key
60. To save and close the table, right-click its tab and click Close
61. When asked whether you want to save, click Yes
62. Set the name to StoreItems and press Enter

A Primary Key From Many Columns

A primary key can be made from a combination of many fields. To create a primary key of more than one field, display the table in Design View. Select, as a group, the fields that would constitute the primary key. Then:

• You can right-click one of the selected fields of the group and click Primary Key

  

• In the Tools section of the Design tab of the Ribbon, you can click the Primary Key button

The fields would then appear each with a key icon to its left:

A Foreign Key

A primary key makes it possible for a table to make its data available to other tables. If a table A wants to use the data stored in another table B, the first table, A, has to be prepared for it. The first table, A, can be called the child table. The other table can be called the parent table. The child table must have a field that would communicate with the parent table. That field represents the parent table. That field from the child table is called a foreign key. In the same way, any table that wants to use data from a certain table must have a foreign key that can communicate with the primary key of that parent table.

To make it easy to identify a foreign key in a table, it is a good idea, not a requirement, to give to the foreign key the same name as that of the primary key. The only real requirement is that both fields use the same data type.

Practical Learning: Introducing Relationships

1. The Ceil Inn1 database should still be opened.
   In the Navigation Pane, right-click the Employees table and click Design View
2. Complete the table with the following MaritalStatusID and FilingStatusID foregn keys:
   
   

   EmployeeNumber	FirstName
   EmployeeNumber (Primary Key)	Number
   FirstName
   LastName
   Title
   MaritalStatusID	Number
   HourlySalary
   FilingStatusID	Number

3. Close the table
4. When asked whether you want to save, click Yes
5. In the Navigation Pane, double-click Employees to open its table
6. Create the following records:
    

   EmployeeNumber	FirstName	LastName	Title	MaritalStatusId	FilingStatusID
   22958	Andrew	Laskin	General Manager	1	2
   24095	Fred	Barclay	Accounts Associate	2	2
   27049	Harriett	Dovecot	Accounts Associate	2	3

7. Close the Employees table

The Diagram of a Relationship

Introduction

The relationships among tables can be visualized and managed in a special window called the Relationships window. To display it:

• If no table is currently opened, on the Ribbon, click Database Tools:

  

  In the Relationships section, click the Relationships button
• If a table is currently displaying, on the Ribbon, click either Database Tools or Table

  

  In the Relationships section, click the Relationships button

After clicking one of those:

• If no relationship exists among the tables in the current database, the Show Table dialog box would come up, asking you to select the tables whose relationship(s) you want to create. To add a table, select it, click Add and click Close
• If at least one relationship has been created between two tables, the Relationships window would come up and display that relationship or the already existing relationships

When you are working on the relationships of your tables, a window with a tab labeled Relationships displays in Microsoft Access. Also, the Ribbon is equipped with a tab labeled design and that includes two sections:

The Design tab of the Ribbon provides various tools to assist you with creating and managing the relationships. For example, if the Show Table dialog box has been closed and if you want to show it:

• You can click the Show Table button on the Ribbon
• You can right-click the body of the Relationships window and click Show Table...

To establish a relationship that does not yet exist between two tables, you can drag the primary key from the parent table to the foreign key of the desired table. If you drag and drop accurately, the relationship would be acknowledged and you can just click Create to make it formal. If you dropped the primary key on the wrong field, you would have time to select the appropriate fields in the Edit Relationship dialog box.

After working with the Relationships window, you can close it by clicking its Close button . You would be asked to save it in order to keep the relationship(s) created.

Practical Learning: Establishing Tables Relationships

1. The Ceil Inn1 database should still be opened.
   On the Ribbon, click Database Tools
2. In the Relationships section, click the Relationships button .
   The Show Table dialog box should display (otherwise, click the Show Table button on the Ribbon):

   

3. Click MaritalsStatus
4. Click the Add button 
5. In the Show Table dialog box, double-click Employees and FilingsStatus

   

6. In the Show Table dialog box, click Close
7. From the MaritalsStatus table, drag the MaritalStatusID field and drop it on top of the MaritalStatusID field in the Employees table:

   

    
   The Edit Relationship dialog box would come up

   

8. Click Create
9. Now you have a line relating these two tables.
   Drag any field from the FilingsStatus table and drop it on top of any field in the Employees table as if you missed the target

   

10. On the dialog box, under the left Table/Query, click the field that is selected to display its combo box. In that left combo box, select FilingStatusID and press Tab
11. Under Related Table/Query, click the field that is selected. Click the arrow of its combo box and select FilingStatusID

    

12. Click Create

    

13. On the Relationships section if the Ribbon, click the Close button
14. When asked whether you want to save, click Yes
15. On the Ribbon, click Create
16. In the Tables section, click Table Design
17. Under Field Name, type ReceiptNumber
18. Press Tab and type n to select Number
19. On the Ribbon, click the Primary Key button
20. Save the table as Payments and close it
21. On the Ribbon, click Create
22. In the Tables section, click Table Design
23. Type AccountNumber
24. Press Tab and type n to select Number
25. Right-click AccountNumber and click Primary Key
26. Complete the table with the following fields:
     

    Field Name	Data Type	Field Size
    AccountNumber (Primary Key)	Number
    FirstName	Short Text	25
    LastName	Short Text	25
    PhoneNumber	Short Text	30
    EmergencyName	Short Text	50
    EmergencyPhone	Short Text	30

27. Save the table as Customers and close it
28. On the Ribbon, click Create
29. In the Tables section, click Table Design
30. Under Field Name, type RoomNumber
31. Press Tab
32. On the Ribbon, click the Primary Key button
33. Press F6 and type 10 for the Field Size
34. Complete the table with the following fields:
     

    Field Name	Data Type	Field Size
    RoomNumber (Primary Key)	Short Text	10
    RoomType	Short Text	25
    BedType	Short Text	20
    DailyRate	Short Text	10
    RoomStatus	Short Text	25

35. Save the table as Rooms and close it
36. On the Ribbon, click Create
37. In the Tables section, click Table Design
38. Type OccupancyNumber
39. Press Tab and type n to select Number
40. Right-click OccupancyNumber and click Primary Key
41. In the left section of the toolbar of Microsoft Access, click the Save button
42. Set the name of the table as Occupancies
43. Click OK
44. In the Navigation Pane, double-click Customers
45. Create the following records
     

    AccountNumber	FirstName	LastName
    100752	Caroline	Lomey
    946090	Peter	Carney

46. Close the Customers table
47. In the Navigation Pane, double-click Rooms
48. Create the following records
     

    RoomNumber	RoomType	BedType	DailyRate	RoomStatus
    104	Bedroom	Queen	75.85	Available
    105	Bedroom	King	92.75	Occupied
    106	Bedroom	Queen	75.85	Available
    107	Bedroom	King	92.75	Available

49. Close the Rooms table
50. On the Ribbon, click File and click Open
51. In the list of files, click FunDS1 from the previous lesson
52. On the Ribbon, click Create
53. In the Tables section, click Table Design
54. Type ReceiptNumber under Field Name
55. Click the arrow the Data Type combo box and select Number
56. Right-click ReceiptNumber and click Primary Key
57. Click the cell under ReceiptNumber and type EmployeeNumber
58. To save and close the table, click its Close button
59. When asked whether you wan to save, click Yes
60. Set the name to ShoppingSessions and click OK
61. On the Ribbon, click Create
62. In the Tables section, click Table
63. Double-click ID, type SoldItemID and press Enter
64. To save and change the view of the table, right-click its tab and click Design View
65. Set the name to SoldItems and press Enter
66. Click the cell under SoldItemID, type ReceiptNumber and press Tab
67. In the combo box, select Number
68. Click the cell under ReceiptNumber, type ItemNumber, press Tab, and type n
69. By typing a field name and pressing the down arrow key, add the following fields:
    Manufacturer
    Category
    SubCategory
    ItemName
    ItemSize
70. To save and close the table, right-click its tab and click Close
71. When asked whether you want to save, click Yes

Establishing a Relationship With a Lookup Field

Using the Table Design View

There are various ways you can create and manage a relationship between two tables. Once you have a primary key in one table, you can ask Microsoft Access to create and even configure a foreign key for you. You can simply indicate where the data will come from; that is, you must indicate the table that holds the primary key, select the field that holds the actual data to use. For this approach, you use a lookup field.

To create a bound lookup field, you can open the table in Design View, set the data type of the field to Lookup Wizard... This would open the Lookup Wizard:

Since you are creating a field that would get its data from another table or query, you must select the first radio button and click Next. Then follow the wizard.

Practical Learning: Introducing Bound Lookup Fields

1. On the Ribbon, click File and click Open
2. In the list of files, click Ceil Inn1 started earlier in this lesson
3. In the Navigation Pane, right-click the Occupancies table and click Design View
4. Click the field under OccupancyNumber
5. Type EmployeeNumber and press Tab
6. Set its Data Type to Lookup Wizard...
7. In the first page of the wizard, accept the first radio button and click Next
8. In the list of tables of the second page of the wizard, click Table: Employees

   

9. Click Next
10. In the Available fields list of the third page of the wizard, double-click FirstName
11. In the Available Fiels list, while LastName is selected, click the right-pointing arrow button

    

12. Click Next
13. In the fourth page of the wizard, click Next
14. Accept the defaults in the fifth page of the wizard and click Next
15. Click Finish
16. When asked to save the table, click Yes.
    In the bottom section of the table, notice that the Field Size is set to Long Integer
17. Click the empty field under EmployeeNumber and type DateOccupied and press F6
18. Type 20 for the Field Size
19. Click the empty field under DateOccupied and type AccountNumber
20. Press Tab and type loo (to select Lookup Wizard...) and press Tab
21. In the first page of the wizard, accept the first radio button and click Next
22. In the list of tables of the second page of the wizard, make sure Table: Customers is selected and click Next
23. In the Available fields list of the third page of the wizard, double-click FirstName
24. Double-click LastName
25. Click Next
26. In the fourth page of the wizard, click Next
27. Accept the defaults in the fifth page of the wizard and click Next
28. Click Finish
29. When asked to save the table, click Yes
30. Click the cell under AccountNumber
31. Type RoomNumber
32. Press F6 and type 10 for the Field Size
33. Add two more fields to the table as follows:
     

    Field Name	Data Type	Field Size
    OccupancyNumber (Primary Key)
    EmployeeNumber
    DateOccupied	Short Text	20
    AccountNumber
    RoomNumber	Short Text	10
    RateApplied	Short Text	10
    PhoneCharge	Short Text	10

34. Close the Occupancies table
35. When asked whether you want to save, click Yes

Using the Table Datasheet View

You can also create a lookup combo box using the Datasheet View of a table. To do this:

• To insert a new lookup fields between two columns, click the header of a column or an empty cell of the column that will succeed the new one. On the Ribbon, click Fields. In the Data Add & Delete section, click More Fields and click Lookup & Relationship

• To add a new lookup column at the end of the table, click Click to Add and click Lookup & Relationship

Any of these actions would open the Lookup Wizard.

When a column is a lookup field, if you don't like the way it behaves, you can reconfigure it. To do this:

• Click a column header or a cell under it. On the Ribbon, click Fields. In the Properties section, click Modify Lookups
• Right-click the header of the column and click Modify Lookups

Practical Learning: Configuring Lookup Fields

1. In the Navigation Pane, double-click Payments to open its table
2. On the Ribbon, click Fields
3. In the Add & Delete section, click the More Fields
4. Cllick Lookup & Relationship
5. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
6. In the second page of the wizard, click the Table: Employees

   

7. Click Next
8. In the Availaable Fields list box, double-click FirstName
9. Double-click LastName

   

10. Click Next
11. Click Next
12. Click Next
13. Replace the suggested Field1 name with EmployeeNumber
14. Click Finish
15. On the table, click Click to Add
16. On the menu that appears, click Short Text
17. Set the name to PaymentDate and press Enter
18. On the menu that appears, click Lookup & Relationship
19. In the first page of the Lookup Wizard, make sure the first radio button is selected and click Next
20. In the second page of the wizard, make sure Table: Customers is selected and click Next
21. In the third page of the wizard, click FirstName and click the Select One button
22. In the Available Fields, as LastName is selected, click the Select One button
23. Click Next
24. Click Next
25. Click Next
26. Replace Field1 with AccountNumber
27. Click Finish
28. Close the Payments table
29. On the Ribbon, click Database Tools
30. Click the Relationships button
31. On the Ribbon, click Show Table
32. In the Show Table, make sure Customers is selected (otherwise, click it).
    Press and hold Ctrl
33. Click Occupancies
34. Click Payments
35. Click Rooms
36. Release Ctrl
37. In the Show Table dialog box, click Add
38. Close the Show Table dialog box
39. Establish a relationship using the EmployeeNumber from the Employees table to the Occupancies table
40. Establish a relationship using the RoomNumber from the Rooms table to the Occupancies table
41. Establish a relationship using the AccountNumber from the Customers table to the Occupancies table
42. Establish a relationship using the EmployeeNumber from the Employees table to the Payments table
43. Establish a relationship using the AccountNumber from the Customers table to the Payments table

    

44. Close Microsoft Access
45. When asked whether you want to save, press Enter