Databases with SQLite

DB Pre-conditions

If you're not at all familiar and knowledgeable about databases, hopefully you've gone through some of the material available in the Resources page. It will save you a lot of aggravation and frustration.

Ask yourself the following questions:

• what is it you want to manage? (books, accounts, inventory)
• what format does the original information exist in? (print, digital, other)
• is this for a business, or just personal use?
• who is going to be maintaining it (adding, modifying, deleting)? You or someone else?
• what kinds of information do you want to be able to produce? What do you want to be able to do with the DB?
• does the database need to be web-enabled, or just a single-user (yourself)?

For our purposes, we're going to use a collection of digital books. I have a large collection of digital books, and have built and been using the database to maintain it for several years. It will cover most of the things you need to know about databases in general, and SQLite in particular.

So we've answered two of the questions above: a digital book collection.

Now, we need to ask 'What types of information does a book collection consist of?'.

Obviously we have an author, a title, a publication year. Since this is a digital collection, the filename is essential.

What about the ISBN? Yes, we should have that. Anything else?

Some subject headings would be useful, so we can search and locate books.

If you are the only person who will be accessing the database, this will make things easier. However, if we plan on making the database available on the web, that raises several other concerns.

For now, let's keep it simple: you are the only person going to use the database, and it isn't going to be web-enabled. Two more questions answered.

The last thing we need to know is 'what kinds of information will be required to produce?'.
Again, let's use the KISS priniciple, and say that we will want to be able to do the following things:

• fiction or non-fiction
• search for a specific or partial title (maybe we can't remember the complete title)
• search for a specific or partial author (for the same reason)
• search for books in a publication year
• search for books with a particular subject heading
• search for books with a particular (or partial) ISBN

That is a reasonable set of conditions we would expect to be able to satisfy, which also answers all the questions we had to begin with, so now we can design the database.

There are other pieces of information (attributes) about a book we could include if desired:

• number of pages
• publisher
• fiction or non-fiction

If your book library is all fiction or all non-fiction, then we can lose that attribute. Or you might decide to have 2 databases - one for each.

Tutorial Database

There is a freely available database you can download and use for learning various operations.
Go to SQLite Tutorial and download the Chinook database.