For those who have worked in Enterprise environments, documentation is of utmost importance. Documentation can be in the form of a text-based document, or a graphical document (in the form of a diagram).

A data dictionary is a centralized repository of information about data such as meaning, relationships to other data, origin, usage, and format. Data dictionaries are commonly used to circulate copies of the database schema to vendors and technology partners, and once a product is released, this may be made available to the end customers depending upon the need (e.g. to allow for customization or study).

Database diagrams, are a repository of the relationships between the various database objects. Database object relationships are typically described using a graphical representation of the tables, columns, keys, indexes, relationships, and constraints.

For any database, you can create as many database diagrams as you like; each database table can appear on any number of diagrams. Thus, you can create different diagrams to visualize different portions of the database, or to accentuate different aspects of the design. For example, you can create a large diagram showing all tables and columns, and you can create a smaller diagram showing all tables without showing the columns.

Diagram ownership

Database diagrams expose the entire relationship design of a database. This can potentially help someone to reverse engineer the diagrams. Therefore, all diagrams can only be seen by members of the db_owner role.

1. A diagram can have one and only one owner – the creator of the diagram
2. The creator of the diagram has to be a member of the db_owner role
3. A diagram can only be seen by the diagram creator and all members of the db_owner role

Working with database diagrams

A database is not configured to generate and store database diagrams by default. Therefore, before a database diagram is generated, the first task is to prepare the database to enable database diagrams.

1. Right-click the Database Diagrams node of your database in Object Explorer
2. Select Yes when prompted if you want to set up database diagramming
3. Clicking on Yes creates the following objects within the database:
   • Sysdiagrams table
   • sp_alterdiagam stored procedure
   • sp_creatediagram stored procedure
   • sp_dropdiagram stored procedure
   • sp_renamediagram stored procedure
   • sp_helpdiagrams stored procedure
   • sp_helpdiagramsdefinition stored procedure
   • sp_upgraddiagrams stored procedure
   • fn_diagramobjects function

Creating a diagram

Creating a database diagram is now a very easy process, and diagrams can be created in just 4 steps.

1. Right-click the Database Diagrams node of your database in Object Explorer
2. From the drop-down menu, click New Database Diagram
3. In the Add Table dialog box, choose tables to work with in the diagram
4. The Database Diagram menu will be added to the main menu and the designer pane will open

Here’s a graphical representation of these steps:

Parts of a diagram

As you can see in the diagram above, there are multiple parts to a relationship between the multiple objects on a database diagram:

1. Endpoints   The endpoints of the line indicate whether the relationship is one-to-one or one-to-many. If a relationship has a key at one endpoint and a figure-eight at the other, it is a one-to-many relationship. If a relationship has a key at each endpoint, it is a one-to-one relationship
2. Line Style   The line itself (not its endpoints) indicates whether the Database Management System (DBMS) enforces referential integrity for the relationship when new data is added to the foreign-key table. If the line appears solid, the DBMS enforces referential integrity for the relationship when rows are added or modified in the foreign-key table. If the line appears dotted, the DBMS does not enforce referential integrity for the relationship when rows are added or modified in the foreign-key table
3. Related Tables   The relationship line indicates that a foreign-key relationship exists between one table and another. For a one-to-many relationship, the foreign-key table is the table near the line’s figure-eight symbol. If both endpoints of the line attach to the same table, the relationship is a reflexive relationship

Customizing the diagram

The diagram layout, by default, may not be in a consumable format. The database diagramming wizard allows use to customize the diagram to our convenience.

Zooming

One of the most commonly required features when working with database diagrams is the need to zoom in or out depending upon the number of tables and relationships involved. Simply right-click on the diagram and choose the zoom factor suitable to your needs.

Source view	Choosing the zoom factor	Post-zoom view

Customizing the amount of information displayed on the diagram

The default view does not provide sufficient information to qualify as a concise, file-able document. To customize the information displayed on the diagram, one can follow the steps below:

1. Select the tables for which the view needs to be modified
2. On the “Database Diagrams Designer” toolbar, from the shortcut menu “Table View”, choose the required view

Choosing the required Table View (The default “Column Names” view in the background)
Standard View
Key Names
Table Name
Custom View
Customizing the “Custom View” using the column selector window

Adding text annotation

Often, we may need to add custom notes to the diagram. This is achieved easily by adding a text annotation.

Right-click on any empty area of the screen and choose “New Text Annotation”
A text box comes up and allows us to type in text of our choice.

Practice Exercises…

It’s time to practice! Very simple exercises today:

1. Create a database diagram showing all tables of the HumanResources and the Sales schema and also show the relationship between them
2. Customize the view to show only the keys on the tables
3. Use the “auto-arrange” functionality to arrange the tables on the designer
4. Show the relationship names on the diagram

Further reading

1. Working with Database Diagrams
2. Designing Database Diagrams

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql