CSCI 343 — Trying a little SQL

Goal

Just want to make sure you can create tables with a few values.

One table should list departments and with the abbreviation and name, for example (CSCI, Computer Science). The other table should list courses an their titles, for example (CSCI, 343, Database Management Systems).

Using the cheat sheet

Of course you can use Stephen Scott’s (My)SQL Cheat Sheet.

Using the terminal

This won’t be too much typing. I suggest using the command line.

Using SQLite

Again, take a look at the web page for the command line shell for SQLite and create a database called lab04 while connected to your home directory.

I’m going to give you the CREATE TABLE statements so that you can figure out why it is so complicated.

CREATE TABLE departments(abbrev TEXT PRIMARY KEY, name TEXT UNIQUE) ;
CREATE TABLE courses(abbrev TEXT, number INTEGER, title TEXT,
       PRIMARY KEY(abbrev, number),
       FOREIGN KEY(abbrev) REFERENCES departments(abbrev)) ;
PRAGMA foreign_keys = ON ;

The PRAGMA is required to tell sqlite3 that you are serious about the foreign key constraint. Otherwise, it will not enfoce it.

Now add at least two values into each of the two tables.

Keys

When you created these databases you specified some KEY attribute. The PRIMARY KEY attributes of a row uniquely identify a row. A FOREIGN KEY is like a pointer. It must contain a valid reference to a row of another table.

First, intentionally try to insert values into the department table that violate the constaints placed on that table. You can succeed only if the INSERT fails.

Now, intentionally try to insert values into the courses table that violate the constaints placed on that table. Again, you can succeed only if the INSERT fails.

Using mySQL

If time permits, create the tables in mySQL.