December 2, 2007 at 3:09 pm
Can i use an ERD diagram to display a database schema with its primary key, foreign key and other constraints, once the table names and column details are know?
December 3, 2007 at 6:25 am
Absolutely. Well, you can use one to show the primary key & foreign key stuff. Not sure about the other constraints. But I'm pretty sure you can at least show DEFAULTS.
December 3, 2007 at 6:38 am
Can I use PK in brackets beside the column name that needs to have a primary key?
December 3, 2007 at 6:40 am
B_Boy,
What software are you using for your diagram? Erwin?
December 3, 2007 at 7:43 am
MS Visio
December 3, 2007 at 7:48 am
Ah. Unfortunately, I have never done a diagram on MS Visio before so I can't answer your question.
Has anyone else used Visio for diagramming dbs?
December 3, 2007 at 7:51 am
Can you kindly let me kniow which one you are using?
December 3, 2007 at 8:15 am
b_boy,
A few Questions:
Are you using Visio to Reverse Engineer an existing database, or designing a database?
What version of SQL Server are you using? (IE: 8.00.2187, 9.00.1399.06, etc.)
I am using Visio 2003 and Visio 2007.
Note that you need to have Visio Professional to have access to the Database Modeling Tools.
There are a couple of tricks to getting PK/FK and datatypes to show up in the drawings.
I can go into more detail when I know what you are working with.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
December 3, 2007 at 8:19 am
B_Boy,
I use Erwin by Computer Associates.
December 3, 2007 at 9:13 am
I am using Visio to design a new database with an existing schema,and am using SQL server version 9.0.1406.
Am also using visio 2007, which is the professional one.
December 3, 2007 at 10:11 am
b_boy,
Warning:
With the Office 2007 family of products, you *may* have some issues with Visio 2007 being able to communicate with SQL Server 2005 pre Service Pack 2 (9.00.3043).
SQL Server 2005 SP 2 makes SQL Server 2005 compatible with Office 2007 products.
--
You will need to:
1) Pull your existing schema (tables, views, constraints, PKs and FKs).
Make a System DSN ODBC Connection pointing to the schema you need to "copy", with at least Datareader or like privs.
Start Visio 2007
Right Column: Template Categories
Choose: Software and Databases
Choose: Database Model Diagram (Ensure to choose US units or Metric at the right)
Click Create Button
This should open you to a blank piece of "graph paper".
Database Menu
Choose: "Reverse Engineer ..."
Reverse Engineer Wizard Screen
Installed Visio drivers: Choose: Microsoft SQL Server
Data Sources:
Click: Next Button
Connect Data Source Screen
Give Data source, Use and Password
Click: OK Button
Object Types
Check (at a minimum):
Tables
Primary Keys
Foreign Keys
(check options more if desired)
Click: Next Button
Select table and/or views to reverse engineer.
(Select desired objects to include in your drawing. The Select All Button and unchecking a handful of objects will save your wrist!)
Click: Next Button
Shapes Screen
make sure that the "Yes, add the shapes to the current page." is chosen (it is by default).
Click: Next Button
Review selections before you press Finish
Just a screen to confirm all your choices before it goes and does all this work for you.
(Eyeball everything ... if satisfactory)
Click: Finish Button
You will now see the Windows Hourglass, and when done, you should see some objects (hopefully) connected with some lines when the processing is finished.
This where you discover if you database has referential integrity at the database level.
**SAVE your work now!**
IF you want the datatypes to show up along side the column names:
Database Menu -->
Options -->
Document -->
Table Tab --> Data Types: Choose: Show Physical (default is "Don't show).
Click: OK Button
**SAVE again!**
--
I recommend saving often as it is easy to "mess up" a connection.
Personally, I save many, many times, with an incrementing numbering system in the name, so that I can go back to any point I need to, at least during the design phase.
You can now begin step 2 ...
2) Expand the existing database structure to fit the needs of a new project.
From here you will need to disconnect your drawing from the "production" database and then make the changes to the schema.
When you have your schema in the structure you want, you will need to make a new System DSN ODBC Connection, pointing to the "new" database, and have Visio "publish" the schema to the database.
Best of Luck and keep us posted on your progress!
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
December 3, 2007 at 10:59 am
Thanks very much for this it was really helpful, do you have any idea how i can show the relationship, in terms of one to many and many to many?
December 3, 2007 at 12:04 pm
b_boy,
Hopefully my notes will get you started, there is a LOT of work involved in modeling. I personally view it as an art form.
--
If lines are drawn between objects the default type is "Relationship" as little lines with arrow heads like -> or <- .
You can switch to "Crow's feet" which will display the relationship better by going to
Database Menu -->
Options -->
Document -->
Relationship Tab
Show Column
Relationships should be checked, leave it checked.
Choose Crow's Feet
Choose Cardinality
Click: OK
Note: You can access the help system, which will explain the functionality for you with the little "?" surrounded by a cartoon bubble.
--
For anyone needing a quick reference for "Crow's foot" notation:
Crow's Foot Notation
http://www2.cs.uregina.ca/~bernatja/crowsfoot.html
--
Happy SQL'ing!
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply