We will discuss breifly the in-class problem from Thursday's class: a soln

Latex

For the next lab assignment you will be writing up solutions to problems using some document producing software. You do not have to use latex, but I encourage you to give it a try.

I'm going to show you a quick demo with an example that you could use as a starting point. You can copy over these files from here:

~newhall/public/latex_examples/report/

My help pages has more information about Latex and other Tools for Creating Documents

SQLite3 tutorial

In lab, we will get practice with a commonly available and used relational DBMS engine - 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.

Getting started

First, create a w04 subdirectory in your cs44/weeklylabs subdirectory:

cd ~/cs44/weeklylabs
mkdir w04
cd w04
pwd

Next, running sqlite3 on the command line and the name of the new database you are creating. For example:

$ sqlite3 universityDB

This 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> .help

Of interest to us are the following:

• By default, query results do not print out column headers, nor do they print in column format (the default uses a delimiter e.g., "ValA|ValB"). We want to fix that by setting the headers ON and the default mode to column format:

  	.mode column
  	.header ON
  	

• We will be using the .import command to quickly insert a number of tuples. We need to define the separator for fields in our text file to the dollar sign $:

  	.separator '$'
  	

• Invoke .show to confirm the changes.
  
  
• SQLite does not enforce foreign key constraints by default. Run the following command to fix this:

  	PRAGMA foreign_keys=ON;
  	

Creating Tables

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. Also, SQLite expects a semicolon at the end of an SQL command:

CREATE TABLE Student ( ... );

We will create the Student relation together, and then you should try creating the Class and Faculty relations on your own:

Student(id, name, major, level, age)
Class(name, time, room, facultyID)
Faculty(id, name, dept)

In the above schema, italics form the primary key.
age, all ID values should be integers.
All other fields should be set as strings (CHAR, VARCHAR, or TEXT). You can feel free to use TEXT as a type for unspecified length, however it is good practice to prescribe length bounds on string fields, so CHAR(N) or VARCHAR(N) are better options. For example, you could specify a length bounds of 20 for names and department, 10 for majors and time, and 4 for level.

SQL types:

• INT or INTEGER
• CHAR(N) fixed length string of size N
• VARCHAR(N) variable-length of up to size N
• REAL or FLOAT
• DATE (‘yyyy-mm-dd’)
• TIME (‘hh:mm:ss’)

We will talk about foreign key constraints this week, but briefly a foreign key is an attribute, or set of attributes in one relation, that refers to a relation intance in another relation. The example relation below has two foreign key constraints: studentID refers to a student in the Student relation (the studentID field refers to the primary key field id of Student); and similarly the ClassName field refers to the primary key name in Class. After we have talked about forgein key constraints in class, I encourage you to try creating this relation in your database, and then try bulk loading my data into it and see if you can get it to work.

Enrolled(studentID, className)

When done, use .table to see all tables created and .schema to review your full schema.

Importing data

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 ~newhall/public/cs44/week04/
class_start  enrolled_start  faculty_start  student_start

First, view the contents of these files (outside of SQLite) to make sure you understand how "raw" data looks.

$ less ~newhall/public/cs44/week04/student_start

The 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' Table

For example:

.import '/home/newhall/public/cs44/week04/student_start' Student

Load all three tables, and then print out the values using a SELECT SQL query:

sqlite> SELECT * FROM Student;
id          name        major       level       age       
----------  ----------  ----------  ----------  ----------
1111        Mo          CS          SO          20        
1122        Peter       English     JR          20        
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        
1234        Tyler       Math        FR          18        
9999        Jo          Math        FR          19       

Do your results match the original file?

cat /home/newhall/public/cs44/week04/student_start

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   Faculty   Student 

# or after adding in the Enrolled Relation:
sqlite> .table
Class   Enrolled   Faculty   Student 

# and you can list their schema:
sqlite> .schema

SQL syntax for CREATE TABLE, INSERT, UPDATE, SELECT, DROP TABLE is identical to that in the book. Just remember that sqlite expects a ; at the end of each SQL command you give it (the ; is for SQLite, but the rest is generic SQL)