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!


36 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
    Replies
    1. Thanks for pointing that out, Amy Jo! I'll fix that as soon as I can. I'm glad you are enjoying my blog!

      Delete
  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. Thanks for Sharing.
    It's great to be here and to learn more about software testing.
    Software testing training and placement in chennai

    ReplyDelete
  6. 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
  7. Your blog is absolutely fantastic and great android apps development tutorial for beginners. Good work.

    DevOps Training in Chennai
    DevOps Training in Chennai


    ReplyDelete
  8. 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

  9. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also.

    Best Data Science courses in Hyderabad

    ReplyDelete
  10. Thanks for sharing such a Knowledgeable blog. Well-written and informative blog. Keep sharing!
    AI Patasala-Data Science Course in Hyderabad

    ReplyDelete
  11. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.

    best data science institute in hyderabad

    ReplyDelete
  12. This is really very nice post you shared, i like the post, thanks for sharing..
    business analytics course

    ReplyDelete
  13. Great job, I liked your blog. Keep maintain this work in further blogs. Thank you.
    AI Patasala Data Science Courses

    ReplyDelete
  14. Interesting post. I Have Been wondering about this issue, so thanks for posting. Pretty cool post.It 's really very nice and Useful post.Thanks data science course in delhi with placement

    ReplyDelete
  15. I was browsing the internet for information and found your blog. I am impressed with the information you have on this blog
    Data Science Course Fees

    ReplyDelete
  16. Hello! I just wish to give an enormous thumbs up for the nice info you've got right here on this post. I will probably be coming back to your weblog for more soon! data scientist course in noida

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. Really, this article is truly one of the best in the article. And this one that I found quite fascinating and should be part of my collection. Very good work!.Data Science Training in Amritsar

    ReplyDelete
  19. Great post, Thanks for sharing a this post filled with a lot of information. keep up the good work.
    Data Analytics Course in Indore

    ReplyDelete
  20. 360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.

    ReplyDelete
  21. If you don"t mind proceed with this extraordinary work and I anticipate a greater amount of your magnificent blog entries data science course in surat

    ReplyDelete
  22. I think about it is most required for making more on this get engaged data scientist course in kanpur

    ReplyDelete
  23. This is a great post I saw thanks to sharing. This is really what I wanted to see, I hope they continue to share such a great article in the future.

    Data Science Course in Gorakhpur

    ReplyDelete
  24. 360DigiTMG offers the best Data Analytics courses in the market with placement assistance. Enroll today and fast forward your career.

    <a href="https://360digitmg.com/india/business-analytics-training-in-patna''>business analytics course in patna</a>

    ReplyDelete
  25. that is in intention of reality exceptional to answer..informative pronounce is completely exceptional to entre..thank you loads! Virtual Audio Cable Crack is a powerful multimedia driver containing input, output designed to connect one or more audio applications. Virtual Audio Cable Crack Full Version Free

    ReplyDelete
  26. interesting challenge for a weblog. i've been searching the net for fun and got here re your internet site. gorgeous pronounce. thank you a ton for sharing your knowledge!Pandora One Free Cracked Apk Latest 2022. Pandora also let you obtain your music offline.! Pandora One Free

    ReplyDelete
  27. Learn to build powerful models to solve business problems by generating useful insights and discover the various scientific processes and methods used to transform the information available in huge datasets into meaningful results. master all the tools and techniques in Data Science and gain domain-specific knowledge which will help you to add more value to your profile. Sign up for the Data Science course in Bangalore with Placements and multiple your chances of working across all industries and job functions.
    Data Science Course in Jaipur

    ReplyDelete
  28. Develop technical skills and become an expert in analyzing large sets of data by enrolling for the Best Data Science course in Bangalore. Gain in-depth knowledge in Data Visualization, Statistics, and Predictive Analytics along with the two famous programming languages and Python. Learn to derive valuable insights from data using skills of Data Mining, Statistics, Machine Learning, Network Analysis, etc, and apply the skills you will learn in your final Capstone project to get recognized by potential employers.
    Best Data Science Training institute in Bangalore

    ReplyDelete
  29. Are you searching for the best Data Science certification online with placement assistance then 360DigiTMG is the best institute for you. Enroll today!

    Data Scientist Course in Delhi

    ReplyDelete
  30. Get Data Science Certification from+ top-ranked universities UTM, Malaysia, and IBM. We provide extensive training for the future-ready workforce.
    data analytics courses in hyderabad with placements

    ReplyDelete


  31. An outstanding job, many thanks!
    https://crackguru.net/digidna-imazing-crack-keygen-working-full-download/

    ReplyDelete
  32. I think I have never seen such blogs ever before that has complete things with all details which I want. So kindly update this ever for us.
    full stack developer course

    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&#...