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.