CS 378 Assignment 1

CS 378: Information Systems

Spring 1999

Assignment 1

Introduction to MySQL

Due Tuesday February 2

The goal for this assignment is to give you a brief introduction to using a database. For this course, we will be using MySQL, which is a freely available database system. Its functionality shares many of the basic SQL commands found in commercially popular database systems including Oracle and Sybase. We will go over commands for creating and querying tables in class. You will also find references to commands in the textbook and the online MySQL manual.

Your task is to create two tables and perform a few queries on them. You may choose the domain of your task (e.g. students and dorm assignments, CD's and musicians, teams and sporting events, etc.).

One table must have an attribute that relates to the other table.
Each table should have at least ten entries.
The tables cannot use the examples discussed in class.
The attribute types should be appropriate for each attribute.
Primary keys should be specified for each table.
At least one other integrity constraint should be used (e.g. additional type restriction, or key constraint).

After you have created your tables, list their contents using a SELECT command.

Now, show examples of the following operations:

Modify one of the column values with the update statement.
Attempt an insert that violates an integrity constraint.
Perform a query that selectively lists only one record in one of your tables.
Perform a query that joins records from both of your tables.
Look up what functions are available for SELECT and perform a query using one of them.

Submission

For submitting your assignment turn in a hard copy of the scripted session of you performing the operations above. Feel free to edit out typos or other mistakes from your script file. If you do, though, notate where the edits occurred (e.g. with a serious of periods where lines were edited out).