Populating, or adding records to a database, can be done several ways. We're still using KISS, so be prepared to do a lot of typing.
Surprisingly, the command you use to add a record (to a table) is: insert.
Our books database has 3 tables available and we need to add relevant data about a book into each of them.
I'm going to use data from my library as dummy data but you should be entering data about a digital book you have.
The book we are going to add is:
From the information above we can also determine the Title and Format.
Some people might wish to enter author/s as lastname,firstname initial, or you could even split the name up into firstname, lastname initial/s, or even put multiple authors in their own record. That is determined by who and how you will be using the database.
What about the Authorid and TitleID?
Since we are the ones creating this DB, we have control over what to choose for these 2 elements. I've chosen to start the index at 1001 for each.
If you look back at our table creations for the table titles, you will notice we have a field 'id integer primary key autoincrement'. The autoincrement part should give you a clue: sqlite will be taking care of that.
That means this first record titleID will be '1001'. Any titles (and authors) we add later will need to have their ID incremented, but we can do that easily.
Now that we have all the information needed for our first record, let's do this!
Enter the following at the sqlite prompt, each line separarted as shown by hitting 'return / enter':
insert into titles
(title,titleid,author,authorid,pubyear,isbn,format,filename)
values
("Laptops For Seniors For Dummies 5E",
1001,
"Muir,Nancy C.",
1001,
2017,
"978-1119420262",
"epub",
"Laptops For Seniors For Dummies 5E.epub");
Pay particular attention to the order in which you are entering the data: the values you specified in the values section MUST match the fields you specified in the insert section.
Also note that the fields we defined as text we put in double-quotes, even the ISBN. A hyphen is NOT an integer. We defined the ISBN field as text purposely to allow this. But you could define yours as integer, but remember that, when you enter the actual number.
The pubyear is an integer, so no quotes required around that.
As administrator we took the liberty of entering the author name as lastname,firstname initial. But again you choose whatever suits your purposes.
To add an author, we insert fields again, in the exact order we specify.
Recall that we made the authorid in the authors table autoincrement, as we did with the titleid in titles. Thus we don't need to worry about that.
insert into authors (author,authorid) values ("Muir,Nancy C.",1001);
Last, we need the subjects. You can get a pretty good guess at what one of those might be:
insert into subjects (titleid,authorid,subject) values (1,1,"Computer Technology");
OK, but that's only 1 subject. What other subject might you use to find this book?
insert into subjects (titleid,authorid,subject) values (1,1,"Educatiom");
Yes, that is spelled wrong, but for a good reason. Just do as I say for now.
And maybe 1 more:
insert into subjects (titleid,authorid,subject) values (1,1,"Seniors");
You can also add several records in one command:
For example, in the subjects table, we have 'titleid, authorid, subject'. To add 3 subjects to 1 book:
insert into subjects(titleid,authorid,subject) values(1,1,"Seniors"), values(1,1,"Education"), values(1,1,"Computer Technology");
You might be asking 'Why not put all the subjects into 1 record in the subjects table?'.
That sounds like a logical thing to do, but would go against well-developed database design principles.
One of the principles in database design is known as Elements of the Ideal Field. Basically it says that a field should contain only 1 single value.
If you put several subjects in 1 field, that is a multi-valued field, and will create problems later - trust me.
Now that you've added the data for your first book, what can you do now?
update