CSE 341  Database Systems, Algorithms, and Applications  (3)

Instructor: Henry Korth

Current Catalog Description
Design of large databases; normalization; query languages (including SQL); transaction-processing protocols; query optimization; performance tuning; distributed systems.  Not available to students who have credit for CSE 241 or IE 224.  Prerequisites: CSE 17 or CSE 18 or consent of instructor.

Textbook

Silberschatz, Korth, Sudarshan, "Database Systems Concepts", 6th Ed., McGraw Hill, 2011. http://www.db-book.com

References
Optional papers in the database research literature pertaining to the history of database systems posted online.

Course Outcomes
CSE 341 is a more advanced version of CSE 241. The first 9 outcomes listed below are identical to those for CSE 241.

Students will have:

1. Fluency in writing code in SQL for data definitions, queries, updates, and online analytic processing (OLAP)
2. Ability to use JDBC to embed SQL database accessess in Java code
3. Ability to use the entity-relationship model for database design.
4. Ability to apply the principles of normalization to relational database design.
5. Abiltiy to use the results of database optimizer plans to analzye and tune database system performance by appropriate selection of indices.
6. Understanding of the concept of a database transaction, including atomicity, concurrency, and recovery.
7. Ability to select appropriate transaction isolation levels.
8. Understanding of SQL authorization and its relationship to broader issues in database security.
9. Understanding of the issues and design choices in distributed databases, including cloud-based databases.
10. Ability to write queries informal query languages: relational algebra and relational calculus; ability to prove equivalences between relational expressions.
11. 11. Understanding of complexity analysus and correctness proofs for normalization algorithms.
12. Understanding of algorithms for B+ trees and extendible hashing.
13. Understanding of query evaluation algorithms, including merge join, hash join, and disk-based sorting.
14. Understanding of database query optimizers.
15. Ability to prove correctnee of major concurrency protocols, including two-phase locking (including multiple granularity), timestamp ordering, and validation.
16. Understanding of potential loss of consistency under snapshop isolation and ways to circumventing this problem.
17. 17. Understanding of ARIES-style recovery algorithms.
18. Algorithms for distributed databases: two-phase commit, distributed and parallel query processing.

Relationship between Course Outcomes and Program Outcomes

CSE 341 substantially supports the following program outcomes:

B. An ability to analyze a problem and identify and define the computing requirements appropriate to its solution.

C. An ability to design, implement, and evaluate a computer-based system.

I. An ability to use current techniques, skills, and tools necessary for computing practices.

J. An ability to applu mathematical foundations, algorithmic principles, and computer science theory in the modeling and design of computer-based systems in a way that demonstrates comprehension of the tradeoffs involved in design choices.

K. An ability to apply design and development principles in the construction of software systems of varying complexity.

CSE 341 provides modest support to the following program outcomes:

A. An ability to apply knowledge of computing and mathematics appropriate to the discipline.

Prerequisites by Topic:

1. Java programming
2. Basic data structures (in-memory)
3. Strong experience in programming and good understanding of concepts of computing systems

Major Topics Covered in the Course
1. Data modeling
2. SQL queries
3. OLAP
4. SQL data definition
5. SQL updates
6. JBDC
7. Intergrity constraints
8. Triggers 9. Entity-Relationship model
10. Relational design and normalization theory
11. Indexing (B+ trees and extendible hashing)
12. Query processing algorithms and query optimization algorithms
13. Transactions: concurrency and recovery
14. Distributed databases
15. Security and authorization
16. Current topics in databases

Assessment Plan for the Course

The students are given several short homework assignments (8 to 10), a major project, one or two mid-term quizzes, and a final examination. Each homework covers a single topic. The project is an end-to-end design and implementation project in which students model part of an enterprises databases (e.g. product and sales data for a retailer), create the database using SQL (on Orable or PostgreSQL), populate it with data, and use JDBC to implement several simple user interfaces to the database (due to the set of prerequisite courses, I accept command-line interfaces, but often get Web-bases ones). The quizzes and exams have a varying number of questions many of which are aimed at a specific topic but others of which test the student's understanding of the relationship among the various components of a database system and its interaction with the rest of a computing environment. Ethical and social issues are discussed in the course and an exam question ties those issues to some aspect of the course I track the performance of the students on each homework assignment, various project checkpoints, and each question on the tests and final examination.

How Data in the Course are Used to Assess Program Outcomes: (unless adequately covered already in the assessment discussion under Criterion 4)

Each semester I include the above data from the assessment plan for the course in my self-assessment of the course. This report is reviewed, in turn, by the Curriculum Committee.