Introduction to SQLite3
This document shows and example of using a database to work with data. We are starting from a comparison with the last structured data format that we’ve looked at, CSV formatted files.
Motivations for structuring data beyond CSV format
Here is an example of tracking students grades for class.
Students in a course have multiple course requirements to complete throughout the semester (Labs, Quizzes, Finals), where their score is limited to the maximum points possible for that assessment type. If we stored the information in a comma separated values (CSV) file with all the possible data fields, the potential number of rows to track would be number of students (40) * number of course requirements (10) * number of fields (40x10x9) or 3600 cells in a matrix.
Example header: Emplid, Course Requirement Name, Course Requirement Type, Score, Date Submitted, Status, Max Points, Due Date, OS
The problem with this design is that is substantially more challenging to maintain, particularly as the size of data scales exponentially larger. For example, if we add a new student then it would be 10 more rows. If we add a new assignment type and a new student, the data to track would scale by a multiple factor. You can imagine that if you had to change the Course Requirement Name from “Lab 1” to “Homework 1”, then you must ensure every single affected row is modified.
Normalized Form
Instead, we can handle this with a normalized database structure through use of several linked tables to reduce redundancies and scale management of data in a more manageable way. This makes it easier to update fields to mitigate potential errors due to human intervention. The figure below is an example of normalized database design, where related fields are grouped into their own tables. This reduces the amount of overall data storage utilized and is also easier to manage scalability issues. In the example below we can consider the active management of the STUDENT_SCORES table with 40students*10course requirements*5 fields (2000 cells) + 3 assessment types + 10 course requirements + 40 students for an overall total of 2129 cells to maintain. As more fields are added to a particular table, it will only pertain to that table, which means the overall design allows reusability and thus easier maintenance.
For example, if we add a new student to the class, it would require adding a student record to only the Students table and the Student_Scores table (52 cells increased instead 90 cells). If we change the name of an assessment, such as from “Lab 1” to “Homework 1”, this will only affect one table (1 cell) instead of every instance (40 cells).
In summary, normalizing data enables optimizations of reduced hardware storage utilization, improved query processing time, and less risk of human errors.
Opening a SQLite Database File
From the terminal, create the database file named grades.db by using the SQLite command line utility and noting the name of the database file we define:
sqlite3 grades.db
This is the same command to use when you want to work with an existing database file.
Exit the CLI utility at any time with CTRL+D.
Examples of queries
Databases tend to separate the structure of data separately from the contents. First we start with working with the database stricture (i.e. create tables) to be able to insert values. The spacing and new lines below is for readability.
CREATING Database Table
Inside the repl:
PRAGMA foreign_keys = ON; --Turn on foreign keys
CREATE TABLE IF NOT EXISTS "ASSESSMENT_TYPE" (
"ID" INTEGER PRIMARY KEY,
"CATEGORY" TEXT,
"POINTS" TEXT
);
-- example with foreign key relationship defined
CREATE TABLE IF NOT EXISTS "COURSE_REQ"(
"RID" INTEGER PRIMARY KEY,
"ITEM" TEXT,
"AID" INTEGER,
"DUE_DATE" DATE,
FOREIGN KEY (AID) REFERENCES ASSESSMENT_TYPE (AID)
);
CREATE TABLE IF NOT EXISTS "STUDENTS"(
"EmplID" INTEGER PRIMARY KEY,
"OS" TEXT
);
CREATE TABLE IF NOT EXISTS "STUDENT_SCORES"(
"EmplID" INTEGER,
"COURSE_REQ" INTEGER,
"STATUS" TEXT,
"SUBMISSION_DATE" DATE,
"SCORE" INTEGER,
FOREIGN KEY (COURSE_REQ) REFERENCES COURSE_REQ (RID),
FOREIGN KEY (EmplID) REFERENCES STUDENTS (EmplID)
);
Changing Table Structure, i.e. rename column
-- update table schema; note use of ALTER keyword
ALTER TABLE ASSESSMENT_TYPE RENAME COLUMN ID to AID;
Deleting a table
DROP TABLE COURSE_REQ;
Inserting values into the database
These are sample values that were created for demonstration.
INSERT INTO COURSE_REQ (RID, ITEM, AID, DUE_DATE) VALUES
(1,"Quiz 1",1,"2022-09-14"),
(2,"Quiz 2",1,"2022-09-28"),
(3,"Quiz 3",1,"2022-10-26"),
(4,"Quiz 4",1,"2022-11-16"),
(11,"Final",3,"2022-12-07"),
(5,"Quiz 5",1,"2022-12-07");
INSERT INTO Students (Emplid, OS)VALUES
(1234,"Mac"),
(1212,"Win"),
(1111,"Mac");
INSERT INTO STUDENT_SCORES (EmplID, COURSE_REQ, STATUS, SUBMISSION_DATE, SCORE) VALUES
(1111,1,"Completed",2022-09-14,3.5),
(1234,1,"Completed",2022-09-14,2),
(1111,2,"Completed",2022-09-28,4);
-- update table record with UPDATE keyword
UPDATE COURSE_REQ SET DUE_DATE="2022-12-19" where ITEM="Final";
QUERIES
From the terminal through SQLite CLI shell utility:
To view data in a certain format you can use the .mode feature. Below we’re using .mode column
sqlite> SELECT * FROM COURSE_REQ;
RID ITEM AID DUE_DATE
--- ------ --- ----------
1 Quiz 1 1 2022-09-14
2 Quiz 2 1 2022-09-28
3 Quiz 3 1 2022-10-26
4 Quiz 4 1 2022-11-16
5 Quiz 5 1 2022-12-07
11 Final 3 2022-12-19
The below examples are for showing the output if you’re using the utility from bash and do not wish to enter the Sqlite3 CLI interface.
From the terminal through bash prompt:
$ sqlite3 -header -column grades.db 'SELECT * from COURSE_REQ;'
RID ITEM AID DUE_DATE
--- ------ --- ----------
1 Quiz 1 1 2022-09-14
2 Quiz 2 1 2022-09-28
3 Quiz 3 1 2022-10-26
4 Quiz 4 1 2022-11-16
5 Quiz 5 1 2022-12-07
11 Final 3 2022-12-19
Join tables
$ sqlite3 -header -column grades.db 'SELECT COURSE_REQ.*,Points FROM COURSE_REQ left join ASSESSMENT_TYPE on COURSE_REQ.AID=ASSESSMENT_TYPE.AID;'
RID ITEM AID DUE_DATE POINTS
--- ------ --- ---------- ------
1 Quiz 1 1 2022-09-14 8
2 Quiz 2 1 2022-09-28 8
3 Quiz 3 1 2022-10-26 8
4 Quiz 4 1 2022-11-16 8
5 Quiz 5 1 2022-12-07 8
11 Final 3 2022-12-19 40
correlated subquery and join
sqlite3 -header -column grades.db 'SELECT STUDENT_SCORES.EmplID, SUM(SCORE),(SELECT Points from COURSE_REQ left join ASSESSMENT_TYPE on COURSE_REQ.AID = ASSESSMENT_TYPE.AID WHERE RID = STUDENT_SCORES.COURSE_REQ) as Points FROM STUDENT_SCORES left join students on STUDENT_SCORES.EmplID = STUDENTS.EmplID GROUP BY STUDENT_SCORES.EmplID'
EmplID SUM(SCORE) Points
------ ---------- ------
1111 7.5 8
1234 2 8
References:
- SQLite3 Documentation https://www.sqlite.org/docs.html
- SQLite Tutorial https://www.sqlitetutorial.net/