INFORMATION TECHNOLOGY DATABASE MANAGEMENT

1. INFORMATION TECHNOLOGY DATABASE MANAGEMENT

2. MODIFYING A TABLE STRUCTURE Adding a new field Right click the table name and select design view Type the field information at the end of the existing field list Save Inserting a new field Right click and open table in design view Click the row where you want to insert new field. Type the field information in the blank row inserted Save

3. MODIFYING A TABLE STRUCTURE Deleting an existing field. Right click the table name and select design view Right click the field row you want to delete and select DELETE ROWS. Save Changing field attributes Right click and open table in design view Click the field row you want to modify. Erase the field name and retype correct field name. Make any other modification necessary Save

4. MODIFYING RECORDS IN A TABLE Using the find replace feature. Open the table in datasheet view (Right click table name and click OPEN) On the HOME ribbon, click replace.

5. MODIFYING RECORDS IN A TABLE Type the item of data you want to change in the Find What Box. Type the item of data you want to change it to in the Replace With Box. Click Find Next until the required record is located Click replace OR Click replace All to change all occurrences.

6. DELETING A RECORD Open the table in DATASHEET VIEW Click the row to delete Click DELETE on the HOME Ribbon

7. CREATING RELATIONSHIPS 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.

8. 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.

9. 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.

10. 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.

11. 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).

12. 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:

13. CREATING THE RELATIONSHIPS 1 Start 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. 2 Click 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."

14. CREATING THE RELATIONSHIPS 3 Select 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. 4 Drag a field from one table to the common field on another table. The "Edit Relationships" dialog box will come up at this point. 5 Verify 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.