How to Create RelationShip Diagram in AccessDb from SqlDb



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 Microsoft Office Access 2007 with the means by which to bring the divided information back together — you do this by placing common fields in tables that are related. To do this step correctly, however, you must first understand the relationships between your tables, and then specify these relationships in your Office Access 2007 database.

After you have created a table for each subject in your database, you must provide Office Access 2007 with the means by which 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 applies only to a Microsoft Access database (.mdb or .accdb).

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 named "Titles" that stores information about each book, such as the book’s title, date of publication, and publisher. There is also information that you might want to store about the publisher, such as the publisher's telephone number, address, and ZIP Code/Postal Code. If you were to store all this information in the "Titles" table, the publisher’s telephone number would be duplicated for each title that the publisher prints.

A better solution is to store the publisher's information only one time, in a separate table that we will call "Publishers." You would then put a pointer in the "Titles" table that references an entry in the "Publishers" table.

To make sure that you data stays synchronized, you can enforce referential integrity between tables. Referential integrity relationships help make sure 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.

Types of table relationships

There are three types of table relationships.
·         A one-to-many relationshi
Consider an order tracking database that includes a Customers table and an Orders table. A customer can place any number of orders. It follows that for any customer represented in the Customers table, there can be many orders represented in the Orders table. The relationship between the Customers table and the Orders table is, therefore, a one-to-many relationship.
To represent a one-to-many relationship in your database design, take the primary key on the "one" side of the relationship and add it as an additional field or fields to the table on the "many" side of the relationship. In this case, for example, you add a new field — the ID field from the Customers table — to the Orders table and name it Customer ID. Access can then use the Customer ID number in the Orders table to locate the correct customer for each order.
·         A many-to-many relationship
Consider the relationship between a Products table and an Orders table. A single order can include more than one product. On the other hand, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. In addition, for each record in the Products table, there can be many records in the Orders table. This type of relationship is called a many-to-many relationship because, for any product, there can be many orders and, for any order, there can be many products. Note that to detect existing many-to-many relationships between your tables, it is important that you consider both sides of the relationship.
To represent a many-to-many relationship, you must create a third table, often called a junction table, that breaks down the many-to-many relationship into two one-to-many relationships. You insert the primary key from each of the two tables into the third table. As a result, the third table records each occurrence, or instance, of the relationship. For example, the Orders table and the Products table have a many-to-many relationship that is defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders.
·         A one-to-one relationship
In a one-to-one relationship, each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table. This type of relationship is not common because, most often, the information related in this way is stored in the same table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. When you do identify such a relationship, both tables must share a common field.

Set the join type

When you define a table relationship, the facts about the relationship inform your query designs. For example, if you define a relationship between two tables, and you then create a query that employs those tables, Access automatically selects the default matching fields based upon the fields specified in the relationship. You can override these initial default values in your query, but the values supplied by the relationship will often prove to be the correct ones. Because matching and bringing together data from more than one table is something you will do frequently in all but the simplest databases, setting defaults by creating relationships can be time saving and beneficial.

A multiple table query combines information from more than one table by matching the values in common fields. The operation that does the matching and combining is called a join. For instance, suppose you want to display customer orders. You create a query that joins the Customers table and the Orders table on the Customer ID field. The query result contains customer information and order information for only those rows where a corresponding match was found.

One of the values you can specify for each relationship is the join type. The join type tells Access which records to include in a query result. For example, consider again a query that joins the Customers table and the Orders table on the common fields that represent the Customer ID. Using the default join type (called an inner join), the query returns only the Customer rows and the Order rows where the common fields (also called the joined fields) are equal.

However, suppose you want to include all Customers — even those who have not yet placed any orders. To accomplish this, you must change the join type from an inner join to what is called a left outer join. A left outer join returns all of the rows from the table on the left side of the relationship and only those that match from the table on the right. A right outer join returns all of the rows on the right and only those that match on the left.

Note   In this case, "left" and "right" refer to the position of the tables in the Edit Relationships dialog box, not the Relationships window.

You should think about the result you will most often want from a query that joins the tables in this relationship, and then set the join type accordingly.

Set the join type

1.       In the Edit Relationships dialog box, click Join Type.
2.       The Join Properties dialog box appears.
3.       Click your choice, and then click OK.

The following table (using the Customers and Orders tables) shows the three choices that are displayed in the Join Properties dialog box, the type of join they use, and whether all rows or matching rows are included for each table.

Choice
Relational join
Left table
Right table
1. Only include rows where the joined fields from both tables are equal.
Inner join
Matching rows
Matching rows
2. Include ALL records from 'Customers' and only those records from 'Orders' where the joined fields are equal.
Left outer join
All rows
Matching rows
3. Include ALL records from 'Orders' and only those records from 'Customers' where the joined fields are equal.
Right outer join
Matching rows
All rows

When you choose option 2 or option 3, an arrow is shown on the relationship line. This arrow points to the side of the relationship that shows only matching rows.

Enforce referential integrity

The purpose of using referential integrity is to prevent orphan records and to keep references synchronized so that you don't have any records that reference other records that no longer exist. You enforce referential integrity by enabling it for a table relationship. Once enforced, Access rejects any operation that would violate referential integrity for that table relationship. Access rejects updates that change the target of a reference, and also deletions that remove the target of a reference. To have Access propagate referential updates and deletions so that all related rows are changed accordingly

The Enforce Referential Integrity option

If you select the Enforce Referential Integrity option Access will make sure that each record in the Order table refers to an existing record in the Customer table. Selecting this option makes it impossible to create Order records that refer to a non-existent customer. You should select Enforce Referential Integrity by default, because it protects the integrity of your data.

The Cascade Update Related Fields option

If you select the Cascade Update Related Fields option Access will change the foreign key if the primary key it refers to changes. In case of our one-to-many example this means that if the primary key of a customer (one) changes, Access will automatically update the foreign keys that refer to this customer in the Order table (many). The Cascade Update Related Fields option also protects the integrity of your data as it prevents records from becoming detached from their related records.

The Cascade Delete Related Records option

The Cascade Delete Related Records option ensures that whenever a Customer (one) record is deleted, than the related records (many) in the Order table are also deleted. You should select this option if the many-part of the relationship has no use or is not needed anymore without the one-part. In case of our one-to-many example this would mean that when a customer is deleted also his/her orders are deleted from the order table as well.

Now let’s Start Create Relationship in Access Database from Sql Database.

Now Select your Database from Sql which one you want to Export>Select task>Select Export Data.

Now Sql Server Import and Export wizard will open.

Now Click Next Choose Data Source From will open in that Form Select DataSource, Server Name and Database from Different DropDownlist. This is for Exporting Data from Which Database and server.

Now Click Next to Perform Next action. Then destination Form will open form Destination Dropdown select Microsoft Access then Select Browser Button to open your Microsoft Access Database and Select your Access Database.


Now your selected file will be on that Filename Textbox.

Now click next then form will open for where you want to Copy your data. And how many table or views you want to take.

Click Next to Select particular tables or views from your current database.
        

Click Next for Data Mapping from your Current database.

Now run package form will open.

Click next to see your complete form details will open from which database you are taking and where you want to specify your database. If any error will occurs then immediately show In this form.

Now click next to perform operation.

After completing this step you will get successfully complete form to complete this step in Sql.

Now in your Sql all step are completed to take table or view. Now open your Access Database file which one you selected while above action to store your tables or views.

Now go to database tools tab and from that tab select relationships item to create relationship between tables.

Now right click on open relationships form and select Show Table option from open dialog form.

Now Show table form will open and select tables from that from how many you want to open for relationship.

Then click Add button to open it in form. There you can see your selected tables will be on relationship form.

Now if you want to create relationship between field and select field from table and drop in another table’s field which field with you want to relation then Create relationship form will open for create that. Read complete above content for what kind of relationship you can create.

Click on Create and there you can see your relation in that filed from different tables.

Related Posts

Previous
Next Post »

Thanks for comments.....