In lab, we will get practice with the most widely distribute relational DBMS engine available - SQLite. While SQLite is not the most powerful engine, it is relatively simple to start up (no server set up, very little configuration, no dependencies) and implements most common features of SQL.
We will practice using SQLite by defining a simple relational schema, loading in data, and writing queries.
Begin by running sqlite3 on the command line and the name of the database you are creating. For example:
$ sqlite3 universityDBThis will load the SQLite interface (a command-line prompt). There are many SQLite specific configurations that may be helpful. To get a description of many, type .help.
sqlite> .helpOf interest to us are the following:
.mode column .header ON
.separator '$'
PRAGMA foreign_keys=ON;
Using the given relational schema, create 4 tables using the CREATE TABLE command. Note that SQLite does not care about case but we will stick to using all caps for SQL commands for readability and to give the impression that you are screaming instructions to the DBMS.
We will create four relations to simulate course enrollments using the following schema:Student(id, name, major, level, age)
Class(name, time, room, facultyID)
Enrolled(studentID, className)
Faculty(id, name, dept)
In the above schema, italics form the primary key. Age, all ID values should typed as integers. All other fields should be set as chars. You can feel free to use text as a type for unspecified length, although it is good practice to prescribe length bounds on these fields e.g., 20 for names and department, 10 for majors and time, 4 for level. It should be clear what the foreign key constraints are in the above table (studentID, className, facultyID).
When done, use .table to see all tables created and .schema to review your full schema.
Most SQL engines have some built-in command to bulk-load entries from some file on disk. I have provided 4 files, one each for the tables you just defined. These files are located in my public space:
$ ls ~soni/public/cs44/universityDB/ class_start enrolled_start faculty_start student_startFirst, view the contents of these files (outside of SQLite) to make sure you understand how "raw" data looks. (These files were created by Prof. Newhall).
$ less ~soni/public/cs44/universityDB/student_startThe delimiters could be commas instead of $, but this avoids issues e.g., department names with commas in them. To import, use the following command:
.import 'filename' TableFor example:
.import '/home/soni/public/cs44/universityDB/student_start' StudentLoad all four tables, and then print out the values using a simple query:
sqlite> SELECT * FROM Student; id name major level age ---------- ---------- ---------- ---------- ---------- 1111 Mo CS SO 20 1122 Peter English JR 20 1234 Tyler Math FR 18 2222 Jo Math SR 21 2323 Jo Math JR 22 3333 Tanya Math JR 21 4444 Malik CS JR 20 4545 Sarah English SR 21 5555 Chris Math FR 19 6666 Charles English FR 18 6767 Sarah Math FR 18 7777 Josh CS SO 19 7878 Heather Math SR 22 8888 Elmo CS SO 20 9999 Jo Math FR 19Do your results match the original file?
At this point, you should be able to exit and reload the database. All contents are saved in the file you originally invoked when running sqlite (i.e., universityDB). Note that the file is saved locally.
sqlite> .exit $ sqlite3 universityDB SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .table Class Enrolled Faculty Student