Last time, we walked through the steps in SQLite to create, extend, and save one SQL table, Customer. But there are two more relations to build for our bank's customer database: Phone and Address.
Use SQLite to create and populate the Phone table.
Open your example05-01.db file and add the new table to the same database. Use the schema and instance data from my example last time:
Phone(areaCode, number, owner)
areaCode number owner
-------- -------- -----------
319 273-5919 123-45-6789
517 452-9159 321-45-6789
319 273-2618 123-45-6789
If you are feeling industrious, create and populate the Address table, too!
(Spoiler alert: answer coming soon...).
This was your first chance to type SQL commands on your own, without prompting. Here is what you would need to create and populate Phone:
$ sqlite3 example05-01.db
sqlite> CREATE TABLE Phone(
...> areaCode,
...> number,
...> owner,
...> PRIMARY KEY (areaCode, number)
...> );
sqlite> INSERT INTO Phone VALUES('319','273-5919','123-45-6789');
sqlite> INSERT INTO Phone VALUES('517','452-9159','321-45-6789');
sqlite> INSERT INTO Phone VALUES('319','273-2618','123-45-6789');
You could use these SQLite commands to check the schema or to see all the SQL you'd need to recreate your process:
sqlite> .schema Phone
sqlite> .dump Phone
Now you can say you've really made your first database table on your own.
We are using this week to learn the basics of SQL and SQLite. Last time, you learned how to start SQLite, create a relation, populate it with tuples, ask simple questions of the data, and save the database to a file. You also learned how to open an existing database and add data to it.
We queried the data using SQL's SELECT command. SELECT is a form of declarative programming: we ask the for data we want, and SQLite figures out how to get it for us. You can think of SELECT as looping over all the tuples in the relation. It returns an entry for each tuple that matches the condition specified by the WHERE clause, if any. The entry contains all the attributes in the matching tuple if we SELECT *, or only the tuples we specify.
As you can see, this style of programing avoids most of the data manipulation code that we need to write in general-purpose languages.
We also made a distinction between commands provided by SQL itself and commands provided by our DBMS, SQLite. Last time, we used three SQL commands: CREATE, INSERT, and SELECT. They will be the same when you work with any relational DBMS. We also used several directives provided by SQLite: .schema, .dump, .save, and .quit. They are specific to SQLite and help us to work with the databases we create using SQL.
Notice the syntax: Every SQL command is terminated with a semicolon, ; . Every SQLite command begins with a leading period, . .
This time, let's extend our SQL knowledge with a few targeted features: data types for attributes, deleting tables, and queries that involve two tables. We'll also learn about one of SQLite's very nice features: a database is a file!
Be sure to submit your work at the end of the session! Ask any questions you have, especially about how to access SQLite and the tools we use.
When we define a relational data model, we specify data types for the attributes in a relation. These data types reflect essential information about the values stored in the database, and they enable the DBMS to enforce constraints at run time.
Last time, we learned how to use SQL's CREATE command to define a schema, but the simple form we used did not include type information:
CREATE TABLE Customer (
ssNo,
name,
PRIMARY KEY (ssNo)
);
The CREATE command allows us to specify type information with each attribute by adding a type name after the attribute name. For our Customer table, we might specify types in this fashion:
CREATE TABLE Customer (
ssNo CHAR(11),
name VARCHAR(30),
PRIMARY KEY (ssNo)
);
CHAR(11) says that the ssNo attribute will hold strings that are eleven characters long. VARCHAR(30) indicates strings that will be of variable length, but no more than 30 characters. SQL supports many other data types, including numbers, booleans, dates, and times. We will learn about more of them later.
To define the Customer relation with typed attributes, we could exit SQLite and start over. SQL gives us another option: deleting an existing table using the DROP command:
sqlite> DROP TABLE Customer;
Now we can create Customer from scratch:
sqlite> CREATE TABLE Customer (
...> ssNo CHAR(11),
...> name VARCHAR(30),
...> PRIMARY KEY (ssNo)
...> );
and add tuples the relation:
sqlite> INSERT INTO Customer VALUES ('123-45-6789', 'Eugene Wallingford' );
sqlite> INSERT INTO Customer VALUES ('321-45-6789', 'John Smith');
sqlite> INSERT INTO Customer VALUES ('987-65-4321', 'Boris Spassky');
sqlite> INSERT INTO Customer VALUES ('456-78-9123', 'John Smith');
sqlite> SELECT * FROM Customer;
123-45-6789|Eugene Wallingford
321-45-6789|John Smith
987-65-4321|Boris Spassky
456-78-9123|John Smith
sqlite>
What happens if we try to insert a tuple with an attribute of the wrong kind?
sqlite> INSERT INTO Customer VALUES (456891273, 'Eugene Baddata');
sqlite>
Well, that's disappointing. SQLite doesn't enforce type constraints in the way we expect. However, we will define types for attributes in most of our databases this semester. The data types we define are useful documentation of domain knowledge, important information for us as programmers, and useful when we port our SQL programs to other implementations.
Most implementations of SQL do not implement all the features of any SQL standard. SQLite is no exception. We'll occasionally encounter a missing feature throughout our studies. Don't be disheartened... Using any other SQL DBMS would leave us in the same position. And SQLite has some added features that have helped it become so popular. Let's learn about one now.
One of the advantages of using flat text files to implement our own database systems is the flat files themselves. Text is portable across platforms, and all of the information about a table resides in a single file. This makes a flat file-based DBMS portable and easy to share. When we move to most DBMSes, we surrender those advantages in exchange for other advantages.
In this regard, SQLite is an exception. An SQLite database is a single file. It's not a plain text file, but the file format that SQLite uses is portable across platforms:
A database that is created on one machine can be copied and used on a different machine with a different architecture. SQLite databases are portable across 32-bit and 64-bit machines and between big-endian and little-endian architectures.SQLite databases are also portable through time: the format used for SQLite database files has been stable since the first release of SQLite 3 in 2004.
Storing databases in single files that are portable across platforms is one of the reasons that Android and iOS include SQLite in their frameworks. Technology and software evolve, but the database infrastructure remains the same.
This is also a handy feature for us in the course. We will be able share databases simply by sharing files. You will be able to submit your work as a database file and I will be able to open and read it, regardless of the platform on which you work.
Here is the first database file I will share with you: customer-example.db . It contains type schema for the Customer, Phone, and Address tables, along with all the data that we have been using in this example.
Click the link to download the file. Your web browser will save it wherever it downloads files to. Copy the file to wherever you are using SQLite.
If you are working on student.cs and don't know how to copy your file from your local computer to a remote server to, see these instructions on the course resources page.
Check your local directory to verify that the database file is present. On student.cs, I can type:
$ ls -l
...
-rw-r--r-- 1 wallingf staff 28672 Sep 18 11:31 customer-example.db
...
Launch SQLite and load the file. You can use the SQLite command .tables to list all the tables in the database:
$ sqlite3 customer-example.db
sqlite> .tables
Address Customer Phone
The Customer and Phone tables are the same ones we have been using, with attribute types. Use the .dump command to examine the Address table:
sqlite> .dump Address
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Address(
street VARCHAR(30),
city VARCHAR(20),
state CHAR(2),
owner CHAR(11),
PRIMARY KEY (street, city, state)
);
INSERT INTO Address VALUES('339 WRT','Cedar Falls','IA','321-45-6789');
INSERT INTO Address VALUES('1 Elm St','Plainfield','IN','321-45-6789');
INSERT INTO Address VALUES('305 WRT','Cedar Falls','IA','123-45-6789');
COMMIT;
$
Now you can use the database just like any other:
sqlite> SELECT * FROM Address;
339 WRT|Cedar Falls|IA|321-45-6789
1 Elm St|Plainfield|IN|321-45-6789
305 WRT|Cedar Falls|IA|123-45-6789
sqlite> SELECT owner FROM Address WHERE city='Cedar Falls';
321-45-6789
123-45-6789
Keep SQLite open on this database. We will use it to see one more bit of SQL: queries against multiple tables.
Thus far, we have used SQL's SELECT command to create queries against a single table, such as the one we just tried:
sqlite> SELECT owner FROM Address WHERE city='Cedar Falls';
321-45-6789
123-45-6789
This statement statement says, Select the owner attribute for
every tuple in the Address table where the city attribute is
'Cedar Falls'. The owner attribute is a Social Security
number, so that's what the query returns.
It might be more useful to know the names of the customers who live in Cedar Falls. Such a query will have to find the names associated with the owners of addresses in Cedar Falls. The names are stored in the Customer relation, not the Address relation.
SQL allows us to create queries that reference multiple tables. Try this query:
sqlite> SELECT Customer.name FROM Customer, Address
...> WHERE Address.city='Cedar Falls'
...> AND Address.owner=Customer.ssNo;
John Smith
Eugene Wallingford
This statement statement says something to the effect:
Select the name attribute from the Customer table for every combination of tuples in the Customer and Address tables where the city attribute in Address is 'Cedar Falls' and the Address owner attribute matches the Customer name attribute.
As I said last session, the SELECT command is quite powerful. The semantics of the SELECT command are also quite complex. Next week, we will begin to learn some of the theory underlying SQL and the relational data model -- known as relational algebra -- in order to help us learn and understand all the many wonderful ways we can query and interact with an SQL database. For now, just know that much is possible!
Poor Boris Spassky does not have a phone number or address in the three-relation customer database, customer-example.db . Your task is to:
If you are feeling adventurous, add at least one phone number and at least one address for mysterious John Smith #2.
Recall what we learned last time: When you add information to an SQLite database, it persists after you quit the program. You do not need to save it explicitly.
Submit your updated customer-example.db file using the electronic submission system, under "online05-01". Please submit by 8:00 AM on Wednesday, September 23.
As always, if you have any questions, please email me. I'd like to help quickly so that you have a decent understanding of the basic SQL and SQLite we have learned this week.