CS634

CS634: Architecture of Database Systems

Department of Computer Science

UMass Boston

Spring 2016: MW 7:00-8:15 PM in M-2-207

Offered by: Elizabeth (Betty) O'Neil
email: eoneil@cs.umb.edu
Office: S-3-169

Description

See the course syllabus.  Prerequisite:  CS430/630 or experience with database and my permission.

Student Information sheet, with list of essential skills needed for this course

Special topics for S16: Data warehousing, big data

Assignments:

Homework 1, due Mon., Feb 8. Relationships, Creating and Loading Databases, Generated PKs (15 points) Solution
Homework 2, due Mon., Feb. 22 Intro to Indexes (15 points) Solution
Homework 3, due Wed., Mar. 9 Being a mysql DBA, Indexing, Intro Query Evaluation Solution 
Homework 4, due Mon., Mar. 28 Query Evaluation and Optimization Solution
Homework 5, due Mon., Apr. 25 Transactions, Locking, More as DBA Solution
Homework 6, due Mon., May 9 Crash Recovery, Data Warehousing, OLAP Solution 

Course Notes/Slides: 6pp means format of six slides per page

Mon, Jan. 25 slides (6pp) Intro
Wed., Jan. 27 slides (6pp) Review of integrity constraints, relationships (handout)
Mon., Feb. 1 slides (6pp) Normalization, Generated Keys, Disks (Chap. 9)
Wed., Feb. 3 slides (6pp) RAID in Practice, Overview of Indexing (Chap. 8)
Mon., Feb. 8 Snow day, University closed
Wed., Feb. 10 slides (6pp)  I/O Cost Model, Tree Indexes (Chap. 10)
Mon., Feb. 15 Holiday
Wed., Feb. 17 slides (6pp) Hash Indexes (Chap. 11)
Mon., Feb. 22 slides (6pp) Managing Disk Resources, mainly Oracle case (not in book)
Wed., Feb. 24 slides (6pp) Managing Disk Resources for MySQL, Query Evaluation Intro (Chap. 12)
Mon., Feb. 29 slides (6pp) Query Evaluation (Chap. 12)
Wed., Mar. 2 slides (6pp) External Sorting (Chap. 13)
Mon., Mar. 7 slides (6pp) Evaluation of Relational Operators (Chap. 14)
Wed., Mar. 9 slides (6pp) Query Optimization (Chap. 15)
Mon., Mar. 21 slides (6pp) Query Plans in Practice
Wed., Mar. 23 slides (6pp) Intro Transactions (Chap. 16)
Mon., Mar. 28 slides (6pp) Concurrency Control, part 1 (Chap. 17)
Wed., Mar. 30 slides (6pp) Midterm Review
Mon, Apr. 4 Midterm Exam: open print book, printouts from ebook, closed electronic devices. Practice Exam (Solution) 
Wed., Apr. 6 slides (6pp) Concurrency Control, part 2 (Chap. 17)
Mon., Apr. 11 slides (6pp) Concurrency Control, part 3 (Chap. 17)
Wed., Apr. 13 slides (6pp) Intro to Recovery (Chap. 18)
Mon., Apr. 18 Holiday
Wed., Apr. 20 slides (6pp) Recovery (Chap. 18)
Mon., Apr. 25 slides (6pp) Data Warehousing and OLAP, part 1 (Chap. 25)
Wed., Apr. 27 slides (6pp) Data Warehousing and OLAP, part 2
Mon., May 2 slides (6pp) Data Warehousing and OLAP, part 3
Wed., May 4 slides (6pp) Big Data
Mon., May 9 slides (6pp) Big Data: Hive, Pig, HDFS
Wed., May 11 slides (6pp) Final Review

Final Exam: Wed., May 18 6:30-9:30 in M/2/207, or alternatively, Mon., May 16 6:30-9:30 in W/1/009 (with cs637)
Practice Final Exam  (Solution) 
Resources:
 midterm exam key
Spring '14 slides, etc.
Access to cs.umb.edu Hosts

SQL2003 data types
Oracle: Oracle setup instructions We are using version 10g on dbs2 or 11g on dbs3 (new server!).  Oracle 10g documentation (SQL)
SQLPlus quick guide: http://www.orafaq.com/wiki/SQL*Plus_FAQ  Basic Commands
Mysql: MySql Setup We are using version 5.6. docs (SQL)
DB2 v10.5 (SQL)

Load scripts for text's sample tables, Oracle and mysql. These are also available in directory /data/htdocs/cs634/tables in the UNIX/Linux filesystem.

Visual Explain for DB2
Example of MS Sql Server visual explain plan

SQL92 Standard