IS 4220 Advanced Databases


Spring Semester 2010

General Information

Sample Data

Here is some sample data from the book:

You can find quite a bit of data at the U.S. Census Bureau using the various data tools (http://www.census.gov/main/www/access.html).

This data is specific to the 1990 and 2000 census for comparative purposes (http://factfinder.census.gov/servlet/DatasetMainPageServlet).

You can find data related to K-12 public schools systems and higher education institutions at the National Center for Education Statistics (http://nces.ed.gov/datatools/).

Here is some retention, progression, and graduation (RPG) data and data model from the University System of Georgia:

Here is a site to generate random data, including numbers that could represent values (i.e., salary, average days worked, etc.):

http://www.generatedata.com

Here are some sample E-R data models you might want to use as the basis of a source system and which to build your data warehouse around. You could then use the link above or generate your own data to populate the E-R model and write ETL around it (http://www.databasedev.co.uk/data_models.html).

If you need more ideas or data, please let me know.

Date Lecture
01/20/2010

SQL Review

Chapter 6 - SQL: Data Manipulation (4th edition)
Chapter 7 - SQL: Data Definition (4th edition) Chapter 6 - SQL: Data Manipulation (5th edition)
Chapter 7 - SQL: Data Definition (5th edition)
01/27/2010

SQL for Datawarehousing

Topics
Oracle Technet - MERGE Statement
Oracle - MERGE Statement
PSOUG - MERGE Statement
Oracle Complete Reference - External Tables
Database Journal - CASE Expression
Oracle Technet - CASE Expression
Oracle - CASE Expression
Geek Interview - Hash Joins
Oracle SQL - Subqueries
Oracle SQL - ROLLUP and CUBE Clauses
02/03/2010

Test 1

Test 1 (Word 2003 - .doc) - Due by midnight on 02/09/2010
Test 1 (Word 2007 - .docx)
Test 1 (Adobe Acrobat - .pdf)
02/10/2010

Advanced SQL

Chapter 8 - Advanced SQL

Data Warehouse Concepts - Part 1

Chapter 32 - Data Warehouse Concepts

Assignment

Assignment 1 - Project Statement- Due by 8:00pm on 02/17/2010
02/17/2010

Data Warehouse Concepts - Part 2

Chapter 32 - Data Warehouse Concepts

Data Warehouse Design - Part 1

Chapter 33 - Data Warehouse Design
02/24/2010

Data Warehouse Design - Part 2

Chapter 33 - Data Warehouse Design
03/03/2010

Test 2 - in class


03/10/2010

Spring Break!


03/17/2010

Working Example of Data Warehousing Components in Oracle

We will spend some time this evening, presumably in the lab, working through a concrete example of using Oracle to build a mini-data warehouse.
Sample Data
External Table Sample Script
External Source Data Table
Demographics Dimension Table
Student Dimension Table
Tuition Fact Table
Example ETL
Example Analytic Query
03/24/2010

Object-Relational Databases

Chapter 29 - Object-Relational Databases
03/31/2010

Project Work Day

I will be available online via e-mail between 8:00-10:00pm for answering questions related to your projects.

Project Deliverables - Due as a single report, in electronic form of PDF or Word is fine, NLT May 5, 2010
04/07/2010

Test 3 - Take Home

Open note and book. Two bonus questions will be available for answering for those who did not do well on the data warehouse architecutre and business development lifecycle questions on Test 2. Covers only Chapter 29
Test 3 (Word 2003 - .doc) - Due by midnight on 04/13/2010
Test 3 (Adobe Acrobat - .pdf) - Due by midnight on 04/13/2010
04/14/2010

Databases Security

Chapter 20 - Database Security
04/21/2010

Project Work Day

I will be available online via e-mail between 8:00-10:00pm for answering questions related to your projects.
04/28/2010

Project Presentations

As noted in the Project Deliverables, these prestations are to include all group members and last no more than 10-15 minutes.