Email Subscription Form

Saturday, June 29, 2019

Testing With Relational Databases

In last week's post, I discussed various ways to test your application's database.  In order to verify that your data has been saved correctly, you'll need to query the database, and the way to query the database will depend on what type of database you have.  In the past, most databases were relational, but in recent years there has been a trend towards using non-relational databases.  In this week's post, I'll address relational databases, and in next week's post, I'll talk about non-relational databases.



Relational databases, such as MySQL and Microsoft SQL Server, are based on tables.  Each table relies on a schema, which defines what columns will be in the table, what data types they will have, and which columns will accept null values.  Here's an example of a typical SQL table:


contactId
firstName
lastName
email
phone
city
state
10000
Prunella
Prunewhip
pprunewhip@fake.com
8005551000
Phoenix
AZ
10001
Joe
Schmoe
jschmoe@alsofake.com
NULL
NULL
RI

Note that there are seven different columns in the table.  The first column in the table, contactId, is the primary key for the table. This will be a unique value; there will never be two contactIds with the same value. 

With a relational database, the schema remains unchangeable, so when Joe Schmoe is added to the database without a phone or city, those places in the table need to be filled with NULL.

Tables in a relational database can connect to each other.  Here is a table in the same database that shows the contact's favorite foods:


foodId
contactId
food
1
10000
Pizza
2
10000
Ice cream
3
10001
Sushi


In this table the primary key is the foodId.  But notice that the contactId is present in this table.  The contactId here is the same as the contactId in the first table.  So we can see in this table that Prunella has two different favorite foods, pizza and ice cream, and Joe's favorite food is sushi.

When testing a relational database, you can use SQL query language to verify that the values you are looking for are present in the database.  For example, if you had just added a new contact with the name of Amy Smith to the Contacts table, you could query the database to see if it had been added, like this:

select * from Contacts where lastName = 'Smith' and firstName = 'Amy'

and the query would return a table row in response:


contactId
firstName
lastName
email
phone
city
state
10003
Amy
Smith
amysmith@faketoo.com
8885551001
Boise
ID

In the above query, the asterisk * tells SQL that we want all of the columns for the record returned.

Because this is a relational database, you could also do a query with a join.  A SQL join combines the data from two tables, joining on a column that they have in common.  

In the example above, both columns have the contactId column.  Let's say that you have given your new contact Amy a favorite food (chocolate), and you want to verify that it saved to the database correctly, but you don't know what Amy's contactId is.  You can't just query the Food table for "Amy Smith" because her first and last names aren't in there.  And you can't query the Contacts table for the food, because it's not in that table.  But you could query the Contacts table with that information, get the contactId from that, and then use the contactId to query the Food table for the favorite food.  

This is what such a query would look like:

select food from Foods 
inner join on Contacts 
where Foods.contactId = Contacts.contactId 
and Contacts.firstName  = 'Amy'
and Contacts.lastName = 'Smith' 

and the query will return this response:

food
Chocolate
  

Let's walk through what happens in the query.
select food from Foods - this tells SQL to return just the food column from the Foods table
inner join on Contacts - this tells SQL that the query will be joining information from the Foods table with information from the Contacts table
where Foods.contactId = Contacts.contactId - this is instructing SQL to find the contactIds in the Foods table and match them up with the contactIds from the Contacts table
and Contacts.firstName  = 'Amy' and Contacts.lastName = 'Smith'  - these last two lines are telling SQL that we are only interested in the record with the first name Amy and the last name Smith 

There are many more complicated ways to query a relational database, but with these two query types you will be able to do most of your data validation.  

Be sure to check out next week's post, where I'll talk about how to test with non-relational databases!


20 comments:

  1. Hi Kristen,
    I enjoy reading and sharing your articles. I wanted to point out that the contact id in the food table houses one extra 0 in the contactid numbers. Thanks.
    Amy Jo

    ReplyDelete
  2. Good job! Fruitful article. I like this very much. It is very useful for my research. It shows your interest in this topic very well. I hope you will post some more information about the software. Please keep sharing!



    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery



    ReplyDelete
  3. A debt of gratitude is in order for sharing the information, keep doing awesome... I truly delighted in investigating your site. great asset...

    Data Science Course

    ReplyDelete
  4. I truly like you're composing style, incredible data, thank you for posting.

    Data Science Training

    ReplyDelete
  5. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    Best Data Science Courses in Bangalore

    ReplyDelete
  6. I'd love to thank you for the efforts you've made in composing this post. I hope the same best work out of you later on too. I wished to thank you with this particular sites! Thank you for sharing. Fantastic sites!
    Data Science Course in Bangalore

    ReplyDelete
  7. This is a great post. This post gives a truly quality information. I am certainly going to look into it. Really very helpful tips are supplied here. Thank you so much. Keep up the great works
    Data Science Training in Bangalore

    ReplyDelete
  8. Really nice and intriguing post. I was trying to find this sort of advice and appreciated reading this one. Keep posting. Thank you for sharing.
    360DigiTMG Data Science Training Institute in Bangalore

    ReplyDelete
  9. Very wonderful article. I liked reading your article. Very wonderful share. Thanks ! .
    Data Science Course In Bangalore With Placement

    ReplyDelete
  10. Fantastic post found to be very impressive to come across such an awesome blog. I really felt enthusiast while reading and enjoyed every bit of your content. Certainly, since this blog is being more informative it is an added advantage for the users who are going through this blog. Once again nice blog keep it up.

    360DigiTMG IoT Course

    ReplyDelete
  11. Wonderful blog found to be very impressive to come across such an awesome blog. I should really appreciate the blogger for the efforts they have put in to develop such an amazing content for all the curious readers who are very keen of being updated across every corner. Ultimately, this is an awesome experience for the readers. Anyways, thanks a lot and keep sharing the content in future too.

    360DigiTMG Artificial Intelligence Course

    ReplyDelete
  12. Thanks for Sharing.
    It's great to be here and to learn more about software testing.
    Software testing training and placement in chennai

    ReplyDelete
  13. Useful article which was very helpful. also interesting and contains good information.
    to know about python training course , use the below link.

    Python Training in chennai

    Python Course in chennai

    ReplyDelete
  14. Very interesting , good job and thanks for sharing such a good information. This blog is very helpful.

    Java Training in Chennai

    Java Course in Chennai

    ReplyDelete
  15. Your blog is absolutely fantastic and great android apps development tutorial for beginners. Good work.

    DevOps Training in Chennai
    DevOps Training in Chennai


    ReplyDelete
  16. Thanks for this Wonderful Blog. It Was very useful to enhance my knowledge. This is what I exactly need an totally enjoying
    this blog, I Really appreciate everything That you do.
    Data Science Training In Chennai

    Data Science Course In Chennai

    ReplyDelete

New Blog Location!

I've moved!  I've really enjoyed using Blogger for my blog, but it didn't integrate with my website in the way I wanted.  So I&#...