CSci 6370
Database Management

Textbook

Database System Concepts, Seventh Edition,
By Avi Silberschatz, Henry F. Korth and S. Sudarshan, 2019.

Alternative: Principles of Database Management: The Practical Guide to Storing, Managing and Analyzing Big and Small Data,
By Lemahieu, Wilfried; Vanden Broucke, Seppe; Baesens, Bart, 2018.

Alternative: Fundamentals of Database Systems, 7th Edition,
By Ramez Elmasri and Shamkant B. Navathe, 2016.

Class Time

Day	Time	Plan	Room
Tuesday	11:10 - 12:25	Lecture + TT	Environmental Health Sciences - room 101
Wednesday	11:30 - 12:20	Lecture	Science Learning Center - room 345
Thursday	11:10 - 12:25	Lecture + HW	Environmental Health Sciences - room 101

Course Description

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

Course Topics

SKS: Chapters 1 - 4, 6 - 7, 9 - 15.

• Chapter 1: Introduction

• Chapter 2: Introduction to Relational Model

• Chapter 3: Introduction to SQL

• Chapter 4: Introduction to Immediate SQL

• Chapter 6: Database Design Using the E-R Model

• Chapter 7: Relational Database Design

• Chapter 9: Application Development

• Chapter 10: Big Data

• Chapter 11: Data Analytics

• Chapter 12: Physical Storage Systems

• Chapter 13: Data Storage Structures

• Chapter 14: Indexing

• Chapter 15: Query Processing

Skip Chapters 5, 8

LBB: Chapters 1 - 4, 6 - 7, 9, 11 - 15.

• Chapter 1: Fundamental Concepts of Database Management

• Chapter 2: Architecture and Categorization of DBMSs

• Chapter 3: Conceptual Data Modeling using (E)ER Model and UML Class Diagrams

• Chapter 4: Organizational Aspects of Database Management [Overview]

• Chapter 6: Relational Databases: The Relational Model

• Chapter 7: Relational Databases: Structure Query Language (SQL)

• Chapter 9: Extended Relational Databases

• Chapter 11: NoSQL Databases

• Chapter 12: Physical File and Indexing

• Chapter 13: Physical Database Organization

• Chapter 14: Basics of Transaction Management

• Chapter 15: Accessing Databases and Database APIs

Skip Chapters 5, 8, 10

EN: Chapters 1 - 9, 14 - 20, 24.

• Part 1: Introduction to Databases
  Chapter 1: Databases and Database Users
  Chapter 2: Database Systems Concepts and Architecture

• Part 3: The Relational Data Model and SQL
  Chapter 5: The Relational Data Model and Relational Database Constraints
  Chapter 6: Basic SQL
  Chapter 7: More SQL: Complex Queries, Triggers, Views, and Schema Modification
  Chapter 8: The Relational Algebra and Relational Calculus

• Part 7: File Structures, Hashing, Indexing, and Physical Database Design
  Chapter 16: Disk Storage, Basic File Structures, Hashing, and Modern Storage Architectures
  Chapter 17: Indexing Structures for Files and Physical Database Design

• Part 8: Query Processing and Optimization
  Chapter 18: Strategies for Query Processing
  Chapter 19: Query Optimization

• Part 2: Conceptual Data Modeling and Database Design
  Chapter 3: Data Modeling Using the Entity Relationship (ER) Model
  Chapter 4: The Enhanced Entity Relationship (EER) Model
  Chapter 9: Relational Database Design by ER- and EER-to-Relational Mapping

• Part 6: Database Design Theory and Normalization
  Chapter 14: Basics of Functional Dependencies and Normalization for Relational Databases
  Chapter 15: Relational Database Design Algorithms and Further Dependencies

• Additional Topics (time permitting)
  Chapter 20: Introduction to Transaction Processing Concepts and Theory
  Chapter 24: NoSQL Databases and Big Data Storage Systems

Sample Performance Analysis Table

Table Scan	Successful	N_R = #block reads
Case	Exact	Asymptotic
Best	1	O(1)
Expected	(N_r + 1) / 2	O(N_r)
Worst	N_r	O(N_r)

Grading

READ Instructions

Must Extend the Code Given Below: (TA will ensure this by looking for changes from prior versions)

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 MapType.NO_MAP for the index type (no indexing).	.
2.	Implement the following Index Structure:	LinHashMap.java	Use indices (TreeMap, HashMap (in Java) and LinHashMap) to speed up the processing of Select and Join. Indices must be integrated and used in the Table class. For a bonus of up to (+10) provide the option of storing the tuples in a FileList as well as in an ArrayList.	.
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. Compare nested loop join vs. indexed join. Compare the three types of maps/indices (TreeMap, HashMap, and LinHashMap). Print your Index Structure. TA to overview performance results. Gold (+6), silver (+4) and bronze (+2) medals for best performers.	.
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.	.
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 with project 3. Proposal must indicate Web Framework (Spring Boot or Play 3 unless permission), JDBC or Ebeans, Database (MySQL or PostgreSQL, unless permission). Project includes database design (UML Class Diagram, 3NF/BCNF Normalization), population and Web-based application development. The Web development framework is Spring Boot or Play 3. The term project including a demo will be presented during the last week of class. Worth twice the points of regular programming projects.	.

Plots with error bars of Performance Results for Project 3:

1. Select - Point Query: e.g., σ _{id = v} (Student)
   • Table Scan - ArrayList, NO_MAP
   • Indexed Select - ArrayList, TreeMap
   • Indexed Select - ArrayList, HashMap
   • Indexed Select - ArrayList, LinHash
2. Select - Range Query: e.g., σ _{v1 <= id & id <= v2} (Student)
   • Table Scan - ArrayList, NO_MAP
   • Indexed Select - ArrayList, HashMap
   • Indexed Select - ArrayList, LinHash
3. Join: e.g., Student join _{id = studId} Transcript
   • Nested Loop Join - ArrayList, NO_MAP
   • Indexed Join - ArrayList, TreeMap
   • Indexed Join - ArrayList, HashMap
   • Indexed Join - ArrayList, LinHash
   • Hash Join - ArrayList, NO_MAP

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).

Rubric for Final Presentations:

1. Design Schema 1 using a UML Class Diagram
2. Translate UML to Relational Model
3. Design Schema 2 using the Boyce-Codd Normal Form (BCNF) Decomposition Algorithm
4. Design Schema 3 using the Third Normal Form (3NF) Synthesis Algorithm
5. Compare the three designs
6. Show final schema in SQL and explain constraints
7. Discuss Software Architecture and Components
8. Demo Secure Logins (password handling and SQL injection prevention)
9. Demo Database Queries
10. Demo Database Updates and Transactions

Policies

• Late Points - 10 points off per day late.
• Make-Up Tests - written pre-approval or medical documentation required (no retakes).
• Departmental Honesty Policy.
• A Culture of Honesty.
• Regents Guide to Understanding Copyright.