One of the goals of good database design is to remove data redundancy (duplicate data). To achieve that goal, you divide your data into many subject-based tables so that each fact is represented only once. You then provide Access with a way to bring the divided information back together — you do this by placing common fields in tables that are related. To do this step correctly, though, you have to understand the relationships between your tables, and then specify these relationships in your database. Show
In this article
IntroductionAfter you have created a table for each subject in your database, you have to give Access a way to bring that information back together again when needed. You do this by placing common fields in tables that are related, and by defining relationships between your tables. You can then create queries, forms, and reports that display information from several tables at once. For example, the form shown here includes information drawn from several tables:
1. Information in this form comes from the Customers table... 2. ...the Orders table... 3. ...the Products table... 4. ...and the Order Details table. The customer name in the Bill To box is retrieved from the Customers table, the Order ID and the Order Date values come from the Orders table, the Product name comes from the Products table, and the Unit Price and Quantity values come from the Order Details table. These tables are linked to each other in a variety of ways to bring information from each into the form. In the preceding example, the fields in the tables must be coordinated so that they show information about the same order. This coordination is accomplished by using table relationships. A table relationship works by matching data in key fields — often a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table. For example, employees can be associated with orders for which they are responsible by creating a table relationship between the EmployeeID fields in the Employees and the Orders tables.
1. EmployeeID appears in both tables — as a primary key ... 2. ... and as a foreign key. Top of Page Types of table relationshipsThere are three types of table relationships in Access.
Top of Page Why create table relationships?You can create table relationships explicitly by using the Relationships window, or by dragging a field from the Field List pane. Access uses table relationships to decide how to join tables when you need to use them in a database object. There are several reasons why you should create table relationships before you create other database objects, such as forms, queries and reports.
Top of Page Understanding referential integrityWhen you design a database, you divide your database information into many subject-based tables to minimize data redundancy. You then give Access a way to bring the data back together by placing common fields into related tables. For example, to represent a one-to-many relationship you take the primary key from the "one" table and add it as an additional field to the "many" table. To bring the data back together, Access takes the value in the "many" table and looks up the corresponding value in the "one" table. In this way the values in the "many" table reference the corresponding values in the "one" table. Suppose you have a one-to-many relationship between Shippers and Orders and you want to delete a Shipper. If the shipper you want to delete has orders in the Orders table, those orders will become "orphans" when you delete the Shipper record. The orders will still contain a shipper ID, but the ID will no longer be valid, because the record that it references no longer exists. The purpose of referential integrity is to prevent orphans and keep references in sync so that this hypothetical situation never occurs. You enforce referential integrity by enabling it for a table relationship. Once enforced, Access rejects any operation that violates referential integrity for that table relationship. This means Access will reject both updates that change the target of a reference, and deletions that remove the target of a reference. It’s possible you might have a perfectly valid need to change the primary key for a shipper that has orders in the Orders table. For such cases, what you really need is for Access to automatically update all the effected rows as part of a single operation. That way, Access ensures that the update is completed in full so that your database is not left in an inconsistent state, with some rows updated and some not. For this reason Access supports the Cascade Update Related Fields option. When you enforce referential integrity and choose the Cascade Update Related Fields option, and you then update a primary key, Access automatically updates all fields that reference the primary key. It's also possible you might have a valid need to delete a row and all related records — for example, a Shipper record and all related orders for that shipper. For this reason, Access supports the Cascade Delete Related Records option. When you enforce referential integrity and choose the Cascade Delete Related Records option, and you then delete a record on the primary key side of the relationship, Access automatically deletes all records that reference the primary key. Top of Page View table relationshipsTo view your table relationships, click Relationships on the Database Tools tab. The Relationships window opens and displays any existing relationships. If no table relationships have been defined and you are opening the Relationships window for the first time, Access prompts you to add a table or query to the window. Open the Relationships window
A table relationship is represented by a relationship line drawn between tables in the Relationships window. A relationship that does not enforce referential integrity appears as a thin line between the common fields supporting the relationship. When you select the relationship by clicking its line, the line thickens to indicate it is selected. If you enforce referential integrity for this relationship, the line appears thicker at each end. In addition, the number 1 appears over the thick portion of the line on one side of the relationship, and the infinity symbol (∞) appears over the thick portion of the line on the other side. When the Relationships window is active, you can select from the following commands on the ribbon: On the Design tab, in the Tools group:
On the Design tab, in the Relationships group:
Top of Page What is data inconsistency quizlet?Data inconsistency. Data inconsistency is when different versions of the same data appear in different places.
What type of data you should not store in a database?Finally, you shouldn't store credit card information in your database unless you absolutely need to. This includes credit card owner names, numbers, CVV numbers, and expiration dates.
Is a set of one or more computer programs that serves as an intermediary between the user and the DBMS?A database is a set of one or more computer programs that serves as an intermediary between the users and the database management system (DBMS). Although users use database systems, they are not considered part of a database system. A relational database can be defined as a self-describing collection of related tables.
Which of the following is not an advantage of database systems?Therefore, High acquisition costs are not the advantage of a database management system.
|