Now that we have some good ideas about what we want to be able to do, we need to ensure our database contains that information.
When you think of a book, the main things that determine that specific book are
Those 3 items will be used to make 3 tables in our database.
If that wrinkles your forehead, then you might want to check out the Resources page, and bone up on some fundamentals of database design.
In particular pay attention to Normalization, where we strive to eliminate redundant and duplicate data.
We've said that SQLite is a relational database. That means that things in the database are related.
Related means one thing is connected to another thing, but what is the connecton, and how do we represent that?
In a RDBMS we relate things to each other by using a unique identifying attribute, typically a number.
SQLite and all relational databases have a way to create these unique numbers, as we will see in the following pages.
These are the connections between items, as we will learn.
It is a popular misconception that a spreadsheet is like a database - keyword: misconception.
Yes, they have a similar look - rows and columns. But that is about the ONLY similarity.
A spreadsheet is merely managing a list of values. In database terminology, a spreadsheet only has 1 table with everything in it.
Using that analogy, the columns of a table are objects, events, or transactions and the rows are attributes of those objects.
The key to database design are the relations (connections) BETWEEN the tables.
These relations are accessed by way of keys, which are a fundamental concept you must get familar with.
Another aspect of database design is determining the type of relation (cardinality) between a pair of tables in your database.
Relationships are not a property of the data.
There are 3 types of relation:
Instructors table [name|ID] Scott,M. 2090 Fitzgerald,S. 2100 Memphis,R. 2213 Rate table [instructorID|$] 2090 36.00 2100 43.50 2213 37.90
Students [name|ID] McAdams,A 3980 Fletcher,S. 3002 Simpson,K. 3774 Instruments [instrument|ID|studentID] Guitar 5000 3980 Guitar 5000 3002 Saxophone 5300 3774 Saxophone 5300 3002 Drums 5467 3980
Students Fletcher,S. 3002 McAdams,A 3980 Simpson,K. 3774 Classes Political Science 1001 Music Theory 1004 Mathematics 1110
To see which students attend a specific class is difficult, but may be solved using a linking table (associative table).
Students Fletcher,S. 3002 McAdams,A 3980 Simpson,K. 3774 Classes Political Science 1001 Music Theory 1004 Mathematics 1110 Linking Table 3002 1004 3002 1110 3980 1001 3980 1110 3980 1004
Relations between tables are sometimes represented using an Entity Relationship (ER) diagram.
The Students and Classes tables are both 1:1, since each student or class has a unique ID attribute.
This is represented like this:
A student may play several instruments ...
As noted above, these types of relations are not feasible in SQLite. We have to reduce it to a 1:N (one-to-many) form with the aid of a linking table.
For example, a school database contains a table of students, and a table of classes. One student may be in several classes, and a single class may have several students.
To determine which classes a particular student is in, requires the use of a linking table, which contains the primary key from each table.
Normalization