CSci 4370/6370
Database Management

John Miller
Summer 2011

Textbook

Database Systems: An Application-Oriented Approach, Complete Version, 2/E,
Kifer, Bernstein and Lewis, 2006.
[recommended for 4370, 6370 and 8370]

Database Systems: An Application-Oriented Approach, Introductory Version, 2/E,
Kifer, Bernstein and Lewis, 2005.
[lower cost alternative for first course]
(first 12 chapters almost identical, but has less material on B+Trees)

Class Time (in GSRC 306)

Day Period 5 Period 6
. 1:00 - 2:00 2:15 - 3:15
Monday yes yes (TT)
Tuesday yes no
Wednesday yes yes (HW)
Thursday yes yes (TT)

Course Description

A comprehensive course on the use and implementation of Database Management Systems (DBMSs).

Course Topics

Chapters 1 - 6, 8 - 11.

  1. Overview of Databases and Transactions (Ch. 1)
  2. The Big Picture (Ch. 2)

  3. Relational Model (Ch. 3: 3.1-3.2)
  4. Relational Algebra (Ch. 5: 5.1)

  5. Physical Data Organization (Ch. 9: 9.1-9.3)
  6. Indexing (Ch. 9: 9.4-9.8)

  7. The Basics of Query Processing (Ch. 10: 10.1-10.5)
  8. An Overview of Query Optimization (Ch. 11: 11.1-11.3)

  9. SQL: Data Definition Language (DDL) (Ch. 3: 3.3)
  10. SQL: Query Language (QL) (Ch. 5: 5.2-5.3)

  11. Relational Normalization Theory (Ch. 6: 6.1-6.8)
  12. Conceptual Modeling (ER and UML) (Ch. 4)

Grading

20% Exam I: topics = {1, 2, 3, 4, 5, 6} 7/6
20% Exam II: topics = {6, 7, 8, 9, 10, 11} 7/26
25% Final Exam 8/5
30% Programs (groups of 2) [languages: Java SE 6, Oracle 10g Express, MySQL 5.5, PostgreSQL 9.0]
5% Homework/Tool Talks [presentation]
Exam I: closed notes and book; bring calculator; 1 page info sheet allowed.
Date: Wednesday, July 6, 2011
6 Questions

Exam II: closed notes and book; bring calculator; 2 page info sheet allowed.
Date: Monday, July 25, 2011
6 Questions

Final Exam: closed notes and book; bring calculator; 4 page info sheet allowed.
Date: Friday, August 5, 2011, 3:30 - 6:30 pm
7 Questions

HW: Homework (individual)

Requirement: Present 1 (individual). Subject to rescheduling.
No. Chapters Questions Due
1. Ch. 2 (1) 2.1; (2) 2.11; (3) FMS vs. DBMS 6/15
2. Ch. 3 (4) 3.3; (5) 3.4; (6) 3.5 6/22
3. Ch. 5 (7) 5.1 (8) 5.10 a-b (Relational Algebra only); (9) 5.10 c-e (Relational Algebra only) 6/29
4. Ch. 9 (10) 9.1 (SATA); (11) 9.1 (SAS); (12) RAID 7/6
5. Ch. 9 (13) 9.7 (B+Trees); (14) 9.7 (Linear Hashing); (15) 9.7 (Extendable Hashing) 7/13
6. Ch. 10 (16) 10.8; (17) 10.11; (18) 10.13 7/20
7. Ch. 3, 5 (19) 3.9; (20) 5.10 a-b (SQL only); (21) 5.10 c-e (SQL only) 7/27
8. Ch. 4 (22) 4.3; (23) 4.7; (24) 6.19 8/3

TT: Tool Talks (group)

Requirement: Present 1 (group). Subject to rescheduling.
No. Topic Description Talk Due
1. Java IO vs. NIO Stream vs. Block IO in Java 6/16 slides
2. Oracle 10 XE A leading Object-Relational Database Management System 6/20 .
3. MySQL 5.5 An open source Relational Database Management System 6/23 .
4. PostgreSQL 9.0 Advanced Object-Relational DBMS 6/27 .
5. JDBC A Java API for accessing relational databases 6/30 .
6. security Database Security 7/11 .
7. ORM Object-Relational Mapping 7/14 .
8. UML Unified Modeling Language 7/18 .
9. AJAX Rich Internet Applications 7/21 .
10. Cloud Databases Databases in the Cloud 7/25 .

Projects

TA: Amir Asiaee

Submit projects to the TA by sending a zip file containing all files to the TA's email with the subject line "[4370/6370] Group # Project #". One submission per group will be sufficient. Turn in your fully commented source code files, an ant build.xml file and a readme file. The ant file must have a target for producing documentation using javadoc. The readme file must contain instructions for compiling and running the program as well as a detailed explanation of who coded what parts of the program. Ten percent of the grade is determined by how well documented the project is (all interfaces, classes, fields, constructors, methods and parameters must be documented). Another ten percent of the grade is determined by how well the effort is divided between the two group members.

Must Use New Starter Code.

No. Description Starter Code (must be used) Comment Due
1. Implement RA Operators: Select, Project, Union, Minus Table.java, String2Type.java, KeyType.java, MovieDB.java Finish the implementation 4 of the 5 operators (skip Join) that are partially implemented in Table.java. Store tuples in an ArrayList. Use a TreeMap for an index. 6/22
2. Implement Join Operator and FileList FileList.java, Conversions.java Implement the 5th operator, Join, and finish the implementation of FileList.java. Store tuples in a FileList. Use a TreeMap for an index. Do not use ArrayList for storing tuples. 6/30
3. Implement your own Index Structures. BpTree.java, LinHash.java, Store tuples in a FileList. Use both a BpTree and a LinHash for an index. 7/12
4. Performance Evaluation of RA Operators TupleGenerator.java, TupleGeneratorImpl.java, TestTupleGenerator.java Plot performance for Selects and Joins (response time in ms vs. number of tuples). Compare sequential select vs. indexed select, nested loop join vs. indexed join, ArrayList vs. FileList and TreeMap vs. your index structure. Print your Index Structure. Present performance results. Gold (+6), silver (+4) and bronze (+2) medals for best performers. 7/19
5. Term Project: Database Application with Web Access . Two page proposal giving a detailed description of the application and the Java-based Web framework to be used must be submitted with project 4 (7/19). Project includes database design (UML, Normalization), population and Web-based application development. The term project including a demo will be presented during the last week of class. Worth twice the points of regular programming projects. 8/1,2,3

Plots with error bars of Performance Results for Project 4:

  1. Select - Point Query: σ id = v (Student)
  2. Select - Range Query: σ v1 <= id & id <= v2 (Student)
  3. Join: Student join id = studId Transcript

Policies