Wednesday, January 30, 2013

A Relational PMP Study Aid, Part 2

The other week I posted a SQL schema and data to help PMP candidates prepare for the certification test. The data primarily follows the PMBOK, along with other well-known concepts, most of which are referred to in the PMBOK.  

The database is a great learning tool and may be useful for other purposes.  As well as for exam prep, we also use it to help catalog how MetaTeam follows standards.  For those who don't know it, Altova MetaTeam is our project management, collaborative decision-making and team performance management tool.

The MetaTeam logo. Altova MetaTeam is our project management, collaborative decision-making and team performance management tool

We had requests for an Microsoft Access version of the database.  Something that could be used without any setup.  That seemed like a good idea so I ginned one up.  

(For those who are impatient, the links to the goods are at the bottom of this post).

Access has a slightly different syntax from MySQL. The main changes were to the auto increment primary keys.

But after making those minor adjustments I ran into something I hadn’t expected: Access didn’t like the multi-insert statements.  For example:

insert into deliverable_or_asset 

(id, name, type, component_of_id )

('project_charter', 'Project Charter', 'Project document', null),
('project_mgmt_plan', 'Project Management Plan', 'Plan', null),
('change_mgmt_plan', 'Change Management Plan', 'Plan', 'project_mgmt_plan'),
('communications_mgmt_plan', 'Communications Management Plan', 'Plan', 'project_mgmt_plan'),


That may sound like a simple problem, but changing hundreds of inserts totally didn’t appeal to me — obviously!  

On the one hand, that’s a lot of cutting and pasting.  And on the other, afterwards I would have two SQL files, not one.

But then I hit on a very simple solution.  Typically enough the solution involved Altova's DatabaseSpy!  

DatabaseSpy came to the rescue with its schema comparison and data merge capabilities—in 2 minutes I had my new database. 

Heres how it worked…

Same note as last time: the image is of the 3rd edition, but the database follows the 4th edition.  How'd that happen?

As you probably guessed, my solution was:

  • First create the "pmbok" schema in Access by executing the DDL (data definition language) statements.  (Not to tell you what you already know, but for clarity, DDL is the part of SQL that sets up the table structure).  
  • Second, once the tables were in place I used DatabaseSpy to replicate the data from MySQL to Access.  

Here are the steps.

To start, I opened DatabaseSpy and connected to the MySQL that we setup in the first blog post about the PMBOK schema.

Next, I opened Access and created a new database.  I’m using Access 2003.  This step involved: 

  • Clicking the New icon (or ctrl-N or use New… on the File menu) 
  • In the New File window I clicked Blank database…
  • For a file location I picked my Desktop and gave the new database “pmbok.mdb”

At that point I had an open database. It was time to create the tables.  

To do this I:
  • Separated out all the CREATE TABLE statements (the DDL) from the original SQL file 
  • Clicked Queries in the Objects panel of my database (on the left-hand side)
  • Double clicked Create query in Design View
  • Shut the Show Table dialog that opened automatically, I didn’t need that
  • Clicked the SQL icon (or SQL View on the View menu)
  • When the SQL editor opened I deleted the “SELECT;” that Access automatically added, and
  • Pasted in my DDL and hit the Run icon (or Run on the Query menu) to create the tables

Running the SQL statements created all the tables in just a second – this is a small schema. 

Now, if you are following along, turn back to DatabaseSpy.  Job one is to connect to the Access database using the Connection Wizard.  Before you do that you need to close Access.

To open the Connection Wizard right-click Data Sources in the Project window and select Add a New Data Source…

In the Connection Wizard click Microsoft Access and hit Next.  Then select your Access database file ending in .mdb – if you’re following along it will be “pmbok.mdb”.  Give your connection a name, and you’re done.

You now have a "pmbok" database schema in Access.  However, it has no data.

Next comes the fun part! 

To get the data from MySQL into Access I did two steps: first I diffed the schemas to make sure the table and relations were the same, then I did a data compare to copy everything over.

Differencing the table structure is an example of something DatabaseSpy does tremendously well.  We start with both databases connected. 

Click the new Data Comparison icon, or click the File menu and select New and then Data Comparison.

A data comparison document opens with two boxes, one for each set of tables.  Click the […] icon in the top right corner of the left box.  That opens an Objects For Comparison dialog.  Notice that the bottom of the dialog has two buttons and the left side button is selected.

In the dialog, check the root of the MySQL database.


Then click the right hand side button at the bottom of the dialog.  Select the Access database using the drop down menu at the top of the dialog. In the right hand side selection check the root of the Access database. 

Click Submit to start the comparison. 

We are starting with equivalent schemas so what is happening is that we are checking the consistency of the data.  Since we have no data in the Access database the DatabaseSpy tells us that there are differences.

If you like, sort the tables by right-clicking the header of one of the data sources and selecting Sort tables.

All we need to do now is click the merge icon that merges from left (MySQL) to right (Access).  DatabaseSpy then copies the data to Access and gives a report in the Message window at the bottom of the screen.

This is a very basic merge so I would expect Messages to give the all-clear, and it did. 

That’s it!  I was super happy to not have to create a more Access-specific SQL file.  Perfect!  

In a follow-up post I’ll talk about how we use the "pmbok" database internally.  

And I’ll tie in how you can use some of those concepts in MetaTeam.

Meanwhile, you can download the Access database or the Access-friendly DDL here

Go for it, and let me know what you think.

A quick word of warning, as you may know, Access will give you a series of warnings about any database you open, including this one.  Please use good judgement about opening any file! 

And one more thing, please remember the database is provided for non-commercial use only, as explained at the bottom of Part 1, with no support and with no promises of fitness for any task.

No comments: