Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

[J T]

The questions you have to ask is

Are the rows I'm returning identifiable by a unique id -- typically the row id, 
but also unique identifiers, like ISBN for books, Employee ID for employees, 
etc. If you find duplicates of what should be a unique id in a table then its 
probably a sign the data is bad. (Two books with the same ISBN, two employees 
with the same Employee ID.) Of course, the other possibility is that the 
database wasn't normalized and the standard operations (Create, Read, Update, 
Delete) weren't used in a logical fashion (for instance leaving out a way to 
update or delete employees and thus making it impossible to rename an employee 
who's changed their name.)

When I search for this author are the books returned normalized against the 
author's table? That is, is there an identifier shared between the tables that 
allows one table to be searched in relation to the other? (The relational part 
of databases.)

The other thing is finding external sources to verify against, or performing 
tests as mentioned by other members of this list.

Create a test author.
create test books by the test author.
do you get only the books you entered for that author?
If not, why not?
If so, then can you repeat the results?

Another thing to look at are your queries.

Select [fields] from [table] where [condition]
Insert into [table] ([columns]) values ([value for each column])
Delete from [table] where [condition]
Update [table] set [field]=[value], [field2]=[value2] ... where [condition]
Select [fields] from [table] where [condition] limit [rows to skip],[rows to 
return]


If you have doubts about the accuracy of the tool you're using there are free 
SQLite Managers out there.

And then there's always the last option which is reading the file manually. 
This last requires a bit more understanding of the database engine itself as 
you need to be able to identify what type is supposed to be where by the bytes 
of the file, and would probably have to write a program to do this 
programmatically instead of trying to do it manually.



 

 

 

-Original Message-
From: Dwight Harvey <dharv...@students.northweststate.edu>
To: sqlite-users <sqlite-users@sqlite.org>
Sent: Sun, Dec 7, 2014 9:24 pm
Subject: [sqlite] How to Verify (know that they are correct) Queries in SQLite 
GUI Manager Firefox add-on?


I am a student with no Tech or IT background what so ever.

I am new to Databases and IT in general.

I am taking an accelerated class in database basics and within the last
three weeks I just learned what databases were.

I know very little and Databases are complex and intimidating.

I figured out how to run queries but I don't know if they are
correct/accurate, as in what I requested from the 'RUN' results?

How do you 'VERIFY' your query results?


My instructor wants me to explain how do I KNOW that the records are
accurate. Here is an example of what is expected in the assignment...

 *VERIFICATION:  *What is verification?  Each time you retrieve data, you
should ask yourself, "How do I know I selected the correct data?".   For
example, if you were asked to pull all records written by an author named
Fred Smith, your query might be based on last name equal to Smith.
However, if you might get records for someone with the first name of Fred,
Mary and Ginger.   What would you do to insure you are pulling only Fred?
The person who has requested the data will always want assurance from you
that you are 100% positive you pulled the correct records.  Look at the
records returned and always as yourself, did I pull the correct records?
How would I verify it?

"Capture each query, number of records returned and *explain your
validation of the query.*" Example:

/** First query 1. List all employees **/
SELECT dbo.Firstname, dbo.Lastname
FROM dbo.employees
--records returned: 24
*--Validation:  I did a quick listing of top 200 records and 4 were
returned*.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

[Jim Callahan]

The question is ambiguous does he want all the Fred Smiths or just a
specific Fred Smith?

For example, do we have a case of mistaken identity? How many Fred Smiths
are out there?

Or does he want All the records for a specific person named Fred Smith?

If it is a specific person, is there a (unique) ID number for the person (a
primary key)? If there is an ID number you can query for that specific
number.

On the other hand if they want all the "Fred Smiths"; do they want
Frederick Smith or Fredrica Smith or some named Theodore Fredrick Smith,
but goes by Fred?

Hope this helps,

Jim Callahan
 On Dec 7, 2014 9:24 PM, "Dwight Harvey" <
dharv...@students.northweststate.edu> wrote:

> I am a student with no Tech or IT background what so ever.
>
> I am new to Databases and IT in general.
>
> I am taking an accelerated class in database basics and within the last
> three weeks I just learned what databases were.
>
> I know very little and Databases are complex and intimidating.
>
> I figured out how to run queries but I don't know if they are
> correct/accurate, as in what I requested from the 'RUN' results?
>
> How do you 'VERIFY' your query results?
>
>
> My instructor wants me to explain how do I KNOW that the records are
> accurate. Here is an example of what is expected in the assignment...
>
>  *VERIFICATION:  *What is verification?  Each time you retrieve data, you
> should ask yourself, "How do I know I selected the correct data?".   For
> example, if you were asked to pull all records written by an author named
> Fred Smith, your query might be based on last name equal to Smith.
> However, if you might get records for someone with the first name of Fred,
> Mary and Ginger.   What would you do to insure you are pulling only Fred?
> The person who has requested the data will always want assurance from you
> that you are 100% positive you pulled the correct records.  Look at the
> records returned and always as yourself, did I pull the correct records?
> How would I verify it?
>
> "Capture each query, number of records returned and *explain your
> validation of the query.*" Example:
>
> /** First query 1. List all employees **/
> SELECT dbo.Firstname, dbo.Lastname
> FROM dbo.employees
> --records returned: 24
> *--Validation:  I did a quick listing of top 200 records and 4 were
> returned*.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

[Stephen Chrzanowski]

You didn't provide the data set, so it'll be a guess.

You'll need to look at the data and do some mental work on comparing what
you expect versus what your query result comes back with, and prove what is
in the database is what your query is EXACTLY asking for.  (Returning on
last names of Smith?)

In the case of your query, your table apparently has 24 records or rows.
Your validation compared to the query is incorrect (Unless the exercise is
to take the given validation result and you provide the full query, in
which case, you WILL have to look at the raw data).  You're validation
statement should also show what you queried for.  Saying you queried 200
records (Which can be true) and got 4 results doesn't show what the query
is asking for, which isn't enough information.

Things to look out for;

- Assuming a larger table, if you queried for 200 rows, who's to say that
row 201-205 didn't include your conditional?  In this case, this validation
statement IS valid, but since searching 200 of 24 rows is going to give you
only 24 rows anyways, the other 176 rows don't exist.  This statement WON'T
be a valid check the moment you hit 201 rows unless you provide additional
sorting and possibly conditionals.
- What statement was used to return just 4 results?

To try and ease the intimidation off, you can compare the Structured Query
Language to act like pulling data out of any kind of spreadsheet.  At the
base of any database, the lowest level of usable and desirable information
is stored in tables.  Thinking of just a single table, each row going
across is a group of data, or using HSL (Human Structured Language), a row
in a table is comparable to being a sentence.  Each field/cell/column is a
piece of data relevant to that row in some way, or for HSL, a
field/cell/column is a word in a sentence.  At the most basic intro level,
in SQL, the SELECT statement tells the engine "I want this data", WHERE
says "But whatever is returned has to meet this criteria", and ORDER BY
means "I want the results ordered by these conditions and/or values in the
specified field".

IE: SELECT dbo.Firstname, dbo.Lastname from dbo.Employees where
dbo.Lastname='Smith' order by dbo.FirstName;

(dbo = Microsoft stuffs.  I'm assuming you're using SQL2k8 and this relates
to Northwind?  If this question doesn't makes sense to you, ignore my
question)

If you break down what a database is down to its elements, the concepts of
what a database IS is really easy.  Once you start building your own tables
and inserting your own data from scratch, you'll start to see how
elementary getting basic results are and how the pieces fit together.  The
tricky part is the knowing how to take what you've got in your head, seeing
the results you want in your minds eye, and converting it to a standard
language.  At the level you're at, all SQL engines have the same syntax.
Later on you're get into nuances of the language between engines, but you
can treat that like to spoken languages like Polish and Ukrainian, or US
English to UK English -- Pretty close to the same thing, but, different
accents and words to get the same thing or idea.

On a personal note, honestly, I do see that there is merit to being
provided a database and then have students try to work with that data, but,
sometimes to some people, that'd be like building a house without a
foundation.  The whole Microsoft Northwind database (At least back in
SQL2K) is nice to play with, but being told "Here is your table, here's the
language, get me results" doesn't jive for the people who don't see what a
constitutes a table, cell, row, or column actually is.

(Just for comedy relief)

For me, when I have to think about different things, I sometimes THINK in
SQL.  So something like "When I was in Toronto last month, how many parking
lots did I pass that cost more than $20/day?" and (sadly) I'll start seeing
flashbacks of each parking lot I remember, and quite often the wife will
have to slap me out of that coma due to so many results. A query like
that would be [ select ParkingLotImage from Locations_Details_ParkingLot
where Cost>20 and month(SeenDate)=month(date(now(),"-1 month")) and
City="Toronto" ] -- And yes, SQL Fanatics, I can normalize that further to
Locations.DetailID = Details.DetailsID and and have "ParkingLot" as a
result in Details, but I've only got so much memory storage and get
distracted easily, I'll EASILY get corrupted. ;)



On Sun, Dec 7, 2014 at 9:24 PM, Dwight Harvey <
dharv...@students.northweststate.edu> wrote:

> I am a student with no Tech or IT background what so ever.
>
> I am new to Databases and IT in general.
>
> I am taking an accelerated class in database basics and within the last
> three weeks I just learned what databases were.
>
> I know very little and Databases are complex and intimidating.
>
> I figured out how to run queries but I don't know if they are
> correct/accurate, as in what I requested from the 'RUN' results?
>
> How do you 'VERIFY' your 

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

[Keith Medcalf]

First, you have to "verify" that the database itself is sufficiently normalized 
and that it is anomoly free.  This is a purely algebraic / mathematical 
operation and is a pre-requisite to any further verification.  If the database 
is not properly normalized, or has other anomolous behaviour, then it may be 
quite impossible to proceed further.

Second, you mathematically / algabraically verify that the query you have 
"asked" is in fact the question you were supposed to be asking (for example, as 
in your example, "select * from table where surname='Smith'" is an obviously 
incorrect phrasing of the problem "select the records where the surname is 
smith and the forename is fred" and cannot return the required result except by 
divine intervention) and that when it is applied as a transformation against 
the database model (verification 1) it has "no choice" but to return the 
desired result.

Third, you can execute the query to test that the database implementation is 
working as it should and that the results returned are those that are expected 
given the data contained in the database and the query.  This is the only step 
that requires anything more than pencil and paper.


---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Dwight Harvey
>Sent: Sunday, 7 December, 2014 19:25
>To: sqlite-users@sqlite.org
>Subject: [sqlite] How to Verify (know that they are correct) Queries in
>SQLite GUI Manager Firefox add-on?
>
>I am a student with no Tech or IT background what so ever.
>
>I am new to Databases and IT in general.
>
>I am taking an accelerated class in database basics and within the last
>three weeks I just learned what databases were.
>
>I know very little and Databases are complex and intimidating.
>
>I figured out how to run queries but I don't know if they are
>correct/accurate, as in what I requested from the 'RUN' results?
>
>How do you 'VERIFY' your query results?
>
>
>My instructor wants me to explain how do I KNOW that the records are
>accurate. Here is an example of what is expected in the assignment...
>
> *VERIFICATION:  *What is verification?  Each time you retrieve data, you
>should ask yourself, "How do I know I selected the correct data?".   For
>example, if you were asked to pull all records written by an author named
>Fred Smith, your query might be based on last name equal to Smith.
>However, if you might get records for someone with the first name of
>Fred,
>Mary and Ginger.   What would you do to insure you are pulling only Fred?
>The person who has requested the data will always want assurance from you
>that you are 100% positive you pulled the correct records.  Look at the
>records returned and always as yourself, did I pull the correct records?
>How would I verify it?
>
>"Capture each query, number of records returned and *explain your
>validation of the query.*" Example:
>
>/** First query 1. List all employees **/
>SELECT dbo.Firstname, dbo.Lastname
>FROM dbo.employees
>--records returned: 24
>*--Validation:  I did a quick listing of top 200 records and 4 were
>returned*.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

[Simon Slavin]

On 8 Dec 2014, at 2:24am, Dwight Harvey  
wrote:

> I know very little and Databases are complex and intimidating.
> 
> I figured out how to run queries but I don't know if they are
> correct/accurate, as in what I requested from the 'RUN' results?
> 
> How do you 'VERIFY' your query results?

I bet if you read over your notes or your textbook you'll find that your 
instructor explained how to do this sometime during your instruction.  But 
without knowing what you instructor intended you to do we might be able to take 
some guesses.

If you could list every row of the tables in your query you could look down 
them yourself and see what you think the result of the query should be.  So you 
can do that, just use

SELECT * FROM dbo.employees

Obviously in real life tables get huge and it's not practical to do this.  
That's why when you test your code you make up small dummy tables to check that 
things are working correctly.  And you make up appropriate sample data to test 
two kinds of errors:

false positive: returning a row you didn't mean to return
false negative: not returning a row you should have returned

Good luck with your course.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to Verify (know that they are correct) Queries in SQLite GUI Manager Firefox add-on?

[Dwight Harvey]

I am a student with no Tech or IT background what so ever.

I am new to Databases and IT in general.

I am taking an accelerated class in database basics and within the last
three weeks I just learned what databases were.

I know very little and Databases are complex and intimidating.

I figured out how to run queries but I don't know if they are
correct/accurate, as in what I requested from the 'RUN' results?

How do you 'VERIFY' your query results?


My instructor wants me to explain how do I KNOW that the records are
accurate. Here is an example of what is expected in the assignment...

 *VERIFICATION:  *What is verification?  Each time you retrieve data, you
should ask yourself, "How do I know I selected the correct data?".   For
example, if you were asked to pull all records written by an author named
Fred Smith, your query might be based on last name equal to Smith.
However, if you might get records for someone with the first name of Fred,
Mary and Ginger.   What would you do to insure you are pulling only Fred?
The person who has requested the data will always want assurance from you
that you are 100% positive you pulled the correct records.  Look at the
records returned and always as yourself, did I pull the correct records?
How would I verify it?

"Capture each query, number of records returned and *explain your
validation of the query.*" Example:

/** First query 1. List all employees **/
SELECT dbo.Firstname, dbo.Lastname
FROM dbo.employees
--records returned: 24
*--Validation:  I did a quick listing of top 200 records and 4 were
returned*.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users