|
Syllabus
|
ITS 408 / MSIS 537
(Revised November 28, 2009)
|
| |
|
Assignments
|
Homework 1
|
| Click on the assignment to |
Homework 2
|
| view it in your browser or |
Homework 3
|
| download it to your system. |
Homework 4
|
|
These are
PDF
files.
|
Major Project (coming soon)
|
| |
|
Articles
|
|
|
| |
|
Database Haiku
|
|
|
These are from Hugh Scott at SQL Server Central:
|
Server is lifeless
The customer is distraught
Where is the backup?
Performance is slow
All the disk drives are busy
Where was the where clause?
|
Dynamic SQL
Or SQL stored procedure
Which one should I choose?
The server is built
Everything is now installed
What was the password?
|
|
These are from current and past students:
|
I did the homework
As difficult as it was
Do I get an A?
- Jerry
|
|
Got one of your own? E-mail it to me.
|
| |
|
Resources
|
ACM index of Database Jobs
|
|
|
Postgres - The World's Best (free) Database
|
|
|
SQL Server Developer Center
|
|
|
SQL Server 2005 Express Edition
|
|
|
W3 Schools: SQL
|
|
|
Microsoft Patterns and Practices
-
Data Patterns
|
|
|
SQL Implementation Comparisons
|
|
|
Postgres vs. MySQL (written by a Postgres vendor, but still valid, at least to me)
|
| |
| Examples |
Data vs. Information Essay
|
|
|
CAP Database Query Examples
|
|
|
MySQL 5.0 script for a few PUBS tables
|
|
|
Small NFL database - create
|
|
|
Small NFL database - queries
|
|
|
Entity Subtypes with Aircraft
|
|
|
Final Project: MagiK Snowboards
|
|
|
Final Project: Poughkeepsie Zoo
|
| |
|
The CAP Database
|
|
CAP is an example database used in the book
Database Principles, Programming, and Performance
by Patrick O'Neil and Elizabeth O'Neil.
A listing of the tables and data are available by
clicking here.
|
|
You may wish to use the CAP database in your
own database product. Here are a few SQL scripts that will create and load the CAP
database. These scripts have been tested on SQL Server, MySQL, and PostgreSQL, but they
should work for any RDBMS product with a few modifications, since they are
written in mostly standard SQL.
|
|
|
|
Once you have the CAP database loaded,
try the following commands in your database system of choice to issue a
simple query. (I have displayed line number prompts in bold. Don't type them.)
|
1> select *
2> from Customers
3> go
|
|
|
Note that you may need to type "go"
every time to signal to the query processor that you are done adding
lines and want your query executed. This is the case in Microsoft SQL Server.
If you are using Postgres or Oracle, replace "go" with a semicolon (";").
If everything went well, you should see results that look very much if not
entirely like this:
|
cid cname city discnt
---- ------------- ---------- ---------
c001 Tiptop Duluth 10.000000
c002 Basics Dallas 12.000000
c003 Allied Dallas 8.000000
c004 ACME Duluth 8.000000
c006 ACME Kyoto 0.000000
|
|
|
That was easy, wasn't it? Try some more queries on your
own. Fiddle around, have fun, enjoy databases, and use your powers for good.
|
