INFORMATION TECHNOLOGY

DATABASE MANAGEMENT

Adding a new field1 Right click the table name and select design

view2 Type the field information at the end of the

existing field list3 Save

Inserting a new field

4 Right click and open table in design view5 Click the row where you want to insert new

field.6 Type the field information in the blank row

inserted7 Save

MODIFYING A TABLE STRUCTURE

Deleting an existing field.1 Right click the table name and select design

view2 Right click the field row you want to delete and

select DELETE ROWS.3 Save

Changing field attributes

4 Right click and open table in design view5 Click the field row you want to modify.6 Erase the field name and retype correct field

name.7 Make any other modification necessary8 Save

MODIFYING A TABLE STRUCTURE

Using the find replace feature.1 Open the table in datasheet view (Right click

table name and click OPEN)2 On the HOME ribbon, click replace.

MODIFYING RECORDS IN A TABLE

3 Type the item of data you want to change in the Find What Box.

4 Type the item of data you want to change it to in the Replace With Box.

5 Click Find Next until the required record is located6 Click replace OR Click replace All to change all

occurrences.

MODIFYING RECORDS IN A TABLE

1 Open the table in DATASHEET VIEW2 Click the row to delete3 Click DELETE on the HOME Ribbon

DELETING A RECORD

What Are Table Relationships

In a relational database, relationships enable you to prevent redundant data. For example, if you are designing a database that will track information about books, you might have a table called Titles that stores information about each book, such as the book’s title, date of publication, and publisher. There is also information you might want to store about the publisher, such as the publisher's phone number, address, and zip code. If you were to store all of this information in the titles table, the publisher’s phone number would be duplicated for each title that the publisher prints.

A better solution is to store the publisher information only once in a separate table, Publishers. You would then put a pointer in the Titles table that references an entry in the Publishers table.

CREATING RELATIONSHIPS

What Are Table Relationships (continue...)

To make sure that your data is not out of sync, you can enforce referential integrity between the Titles and Publishers tables. Referential integrity relationships help ensure that information in one table matches information in another. For example, each title in the Titles table must be associated with a specific publisher in the Publishers table. A title cannot be added to the database for a publisher that does not exist in the database.

CREATING RELATIONSHIPS

Types of Table Relationships

A relationship works by matching data in key columns, usually columns with the same name in both tables. In most

cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table. For example,

sales can be associated with the specific titles sold by creating a relationship between the title_id column in the

Titles table (the primary key) and the title_id column in the Sales table (the foreign key).

There are three types of relationships between tables. The type of relationship that is created depends on how the

related columns are defined.

CREATING RELATIONSHIPS

One-To-Many Relationships

A one-to-many relationship is the most common type of relationship. In this type of relationship, a row in table A can

have many matching rows in table B, but a row in table B can have only one matching row in table A. For example,

the Publishers and Titles tables have a one-to-many relationship: each publisher produces many titles, but each

title comes from only one publisher.

A one-to-many relationship is created if only one of the related columns is a primary key or has a unique constraint.

In Access, the primary key side of a one-to-many relationship is denoted by a key symbol. The foreign key side of a relationship is denoted by an infinity symbol.

CREATING RELATIONSHIPS

Many-To-Many Relationships

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. You create

such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign

keys from both table A and table B.

For example, the Authors table and the Titles table have a many-to-many relationship that is defined by a one-to-many

relationship from each of these tables to the TitleAuthors table. The primary key of the TitleAuthors table is the combination of the au_id column (the authors table’s primary key) and the title_id column (the Titles table’s

primary key).

CREATING RELATIONSHIPS

One-To-One Relationships

In a one-to-one relationship, a row in table A can have no more than one matching row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique constraints.

This type of relationship is not common because most information related in this way would be all in one table. You might use a one-to-one relationship to:

CREATING RELATIONSHIPS

1Start Access 2007 and click on the Microsoft Office Button, then click on the "Open" line item to bring up the "Open"

dialog box. Select and open the database in which you wish to create a relationship.

2Click on the "Database Tools" tab and look in the "Show-Hide" group for the "Relationships" icon. Click this icon to

bring up the "Relationships" window. This should automatically bring up the "Show Tables" dialog box if you have not defined any relationships for this database. If it

doesn't, click on "Show Table."

CREATING THE RELATIONSHIPS

3Select all of the tables or queries for which you wish to create a

relationship and click the "Add" button. When you have added all of the tables and queries you need relationships for, click the "Close"

button.

4Drag a field from one table to the common field on another table.

The "Edit Relationships" dialog box will come up at this point.

5Verify that the field names are correct and check the "Enforce

Referential Integrity" box, if desired. "Click the Create" button to establish the relationship. Note that a relationship line is now

drawn between the two tables.

CREATING THE RELATIONSHIPS