Other DBMS

• I assume that MySQL/MariaDB reasonable examples of interacting with other databases.
• First you need to install some software
  • This is an extension to excel to allow it to talk to the DBMS
  • It is supplied by the database vendor.
  • The MYSQL Connector/Net seems to be the thing needed here.
  • Austin installed this for us last week.
• Next on the Data tab, select New Query on the Get & Transform workgrup.
  • Select From Database
  • And From MySQL Database
  • 
  • Fill in the server and the database
    • In this case, use mirkwood.cs.edinboro.edu, chinook
    • Notice under the advance options you could do query.
    • 
  • Next we need to provide credentials.
    • Select Database
    • Then fill in the dsci user name and password.
    • Make sure you select the chinook database.
    • 
    • I don't have it running on a secure port, so tell it to go ahead without encryption
    • 
  • This should bring up a familiar screen.
    • 
    • In this case, select the Track and Album tables.
  • Notice the Query shows up on the right.
    • 
    • But we can always get them back from Data Show Queries
• Modifying the Query
  • Double click on the query to bring up the Query Editor
  • 
  • This will allow us to edit the query to produce the table we want.
  • And it will save this information so we can reload the table in the future.
  • Scroll to the right until you see Chinook.Genre
    • 
    • Notice that excel is telling us there is a relation here.
    • Let's add in the Genre Name.
    • So click on the double arrow
    • And select only the name for this field.
    • 
  • In the album we want to bring in two fields
    • The Album Title and the ArtistID
  • Remove all the unneeded fields.
• I don't see a way to get the artist name, so let's add another query to bring in the artist name.
  • On the Data tab, select Recent Sources
  • Select the database again
  • Load the Artist Table.
  • 
• I want to combine these two queries.
  • So on the Data tab again, select New Query
  • But this time, select Combine Queries
  • 
  • Select the Track table as the first table
  • and Artist table as the second.
  • Keep the Join kind as Left Outer.
    • We want all the artists put into the track table.
  • Select the two matching fields.
  • 
  • Select the name only
  • Clicking on Close and Load will create the table.
  • 
  • Notice, I could have avoided most of this if I had just downloaded the artist table as well.
• Let's put the track query into a table.
  • Right click on each query and select Load To
  • 
  • Select load to table
  •