Tutorial: Create a Database Diagram to Use as a Query Template
|
Scenario Your company creates several daily and weekly reports regarding customer sales. You need to create a Database Diagram that can be used as a template for these queries. |
To create a Database Diagram to use as a query template
-
Connect to the Toad Sample (Access) database in the Navigation Manager.
- Select Tools | Diagram.
-
Drag the CONTACT table in the Object Explorer to the Diagram pane.
-
Right click the Diagram pane and clear Show Dependencies to hide the Referenced Objects at the bottom of each table/view, as illustrated in the following screen capture.
- Right-click the ORDERS table and select Notes.
-
Enter the following note in the window for other users:
The shipping and billing ADDRESS_ID foreign key relations are not needed in most queries and should be hidden when building a query.
-
Because it is against company policy to display sex and a home phone number in reports, right click on each of these columns in the CONTACT table and select Hide Column.
Tip: If you have a large number of columns you need to hide, you can right-click a table and select Manage Hidden Columns to quickly select them from a list.
- Click Add to Project from the wizard bar at the bottom of the window and enter DBDiagram_Test asthe name for the file. This adds the file to the current project in the Project Manager under a Diagrams folder.
- Close the Database Diagram window and open the Project Manager window.
- Expand the Diagrams folder and double-click DBDiagram_Test to reload the file in a new Database Diagram window.
- To share your project, hidden columns, and notes with colleagues, complete the following:
- Select Tools | Options | Environment | Network Share, and navigate to a shared drive where you want to store the Project Manager file.
- Select Database | Cache from the open Options window and set the following options:
Object An notation Cache Description Share object annotations Select this checkbox and enter a shared drive location where users can access this file. Share hidden column settings Select this checkbox. - Provide the location of the Network Share and Object Annotation Cache settings to colleagues who need to use this diagram.
