CSci 4370/6370
Database Management

John Miller
Summer 2015

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 Forest Resources 304)

Day Time
Monday 10:30 - 12:45 Lecture + HW
Tuesday 10:30 - 11:30 Lecture
Wednesday 10:30 - 12:45 Lecture + TT
Thursday 10:30 - 12:45 Lecture + HW

Course Description

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

Course Topics

Chapters 1 - 6, 8 - 12.

  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. Conceptual Modeling (ER and UML) (Ch. 4)
  12. Relational Normalization Theory (Ch. 6: 6.1-6.8)

Grading

20% Exam I: topics = {1, 2, 3, 4, 5, 6} 7/1
20% Exam II: topics = {6, 7, 8, 9, 10} 7/16
25% Final Exam .
30% Programs (groups of 4) Java SE 8, MySQL 5.6, PostgreSQL 9.4
5% Homework/Tool Talks presentations
Exam I: closed notes and book; bring calculator; 1 page info sheet allowed.
Review Date: Tuesday, June 30, 2015
Exam Date: Wednesday, July 1, 2015
5 Questions: Introduction (Chs. 1, 2), Relational Model (Ch. 3), Relational Algebra (Ch. 5), Files and Disks (Ch. 9), Indexing (Ch. 9).

Exam II: closed notes and book; bring calculator; 2 page info sheet allowed.
Review Date: Wednesday, July 15, 2015
Exam Date: Thursday, July 16, 2015
5 Questions: Indexing (Ch. 9), SQL (Chs. 3, 5), Complex SQL Query (Chs. 3, 5), Query Processing (Ch. 10), Query Optimization (Chs. 10, 11).

Final Exam: closed notes and book; bring calculator; 4 page info sheet allowed.
Date:
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/11
2. Ch. 3 (4) 3.3; (5) 3.4; (6) 3.5 6/18
3. Ch. 5 (7) 5.1; (8) 5.10 a-b (Relational Algebra only); (9) 5.10 c-d 6/22
4. Ch. 9 (10) 9.1 (SATA); (11) 9.1 (SAS); (12) RAID 6/25
5. Ch. 9 (13) 9.7 (B+Trees); (14) 9.7 (Linear Hashing); (15) 9.7 (Extendable Hashing) 6/29
6. Ch. 10 (16) 10.8; (17) 10.11; (18) 10.13 7/9
7. Ch. 3, 5 (19) 3.9; (20) 5.10 a-b (SQL only); (21) 5.10 c-e 7/15
8. Ch. 5 (22) 5.17; (23) 5.22; (24) 5.24 .
9. Ch. 6 (25) 6.19; (26) 20; (27) 23 .
10. Ch. 4 (28) 4.3; (29) 4.7; (30) 4.9 .
11. Additional HW Per Request

TT: Tool Talks (group)

Requirement: Present 1(group). Subject to rescheduling.
No. Topic Description Talk Due
1. Java 8 Functional Programming in Java 8 . 6/10
2. I/O Streams I/O Streams in Java . 6/17
3. ext4 Extended File System (Linux) . 6/24
4. File I/O File I/O in Java . 7/2
5. MySQL 5.6 Relational Database Management System (R-DBMS) . 7/2
6. JDBC Java Database Connectivity (JDBC) - also see Code Samples . 7/8
7. jQuery/AJAX Rich Internet Applications . 7/15

Projects

Instructions

Must Extend the Code Given Below:

No. Description Starter Code (must be used) Comment Due
1. Implement RA Operators: Select, Project, Union, Minus and Join Table.java, KeyType.java, ArrayUtil.java and MovieDB.java Finish the implementation the 5 RA Operators that are partially implemented in Table.java. Store tuples in an ArrayList. Use a TreeMap for an index. 6/15
2. Implement the following three Index Structures: BpTreeMap.java, LinHashMap.java and ExtHashMap.java Use the indices to speed up the processing of Select and Join. For a bonus of up to (+10) provide the option of storing the tuples in a FileList as well as in an ArrayList. 6/29
3. 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 and TreeMap vs. all index structures. Print your Index Structure. Present performance results. Gold (+6), silver (+4) and bronze (+2) medals for best performers. 7/7
4. Performance Tuning of SQL Queries See queries below Analyze query plans generated by the DBMS. Tune queries by using hints, adding indexes and rewriting queries. Explain how query plans change after tuning. Turn in before and after .sql files and query plans for six queries (given in English, see below). Do this for both the MySQL and PostgreSQL DBMSs: MySQL and PostgreSQL . Present tuning experience and performance results including plots in class. 7/13
5. Term Project: Database Application with Web Access . A two-page proposal giving a detailed description of the application you propose to develop must be submitted on 7/13. Project includes database design (UML, Normalization), population and Web-based application development. The DBMS must be either MySQL or PostgreSQL. The Web development framework must be Struts 2, or jQuery. Access to the database must be via either JDBC or JPA. The term project including a demo will be presented during the last week of class. Worth twice the points of regular programming projects. 4/27,28

Plots with error bars of Performance Results for Project 3:

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

Queries for Project 4:

  1. List the name of the student with id equal to v1 (id).
  2. List the names of students with id in the range of v2 (id) to v3 (inclusive).
  3. List the names of students who have taken course v4 (crsCode).
  4. List the names of students who have taken a course taught by professor v5 (name).
  5. List the names of students who have taken a course from department v6 (deptId), but not v7.
  6. List the names of students who have taken all courses offered by department v8 (deptId).

Policies