Current Catalog Description

Design of large databases: Integration of databases and applications using SQL and JDBC; transaction processing; performance tuning; data mining and data warehouses. Not available to students who have credit for CSE 341 or IE 224. Prerequisite: CSE 17 or CSE 18 or consent of the instructor.

Instructor: Sihong Xie (Fall 2022)

Textbook

Silberschatz, Korth  "Database System Concepts", 7th edition, McGraw Hill 2020. , ISBN 978-0078022159

Course Outcomes

Students will have:

  1. Skill in SQL at an advanced level, including complex queries, data definition, constraint specification, coding with PL/SQL (or similar if a non-Oracle system is used), and Java programming with JDBC
  2. Database design using the ER Model and using relational normalization
  3. Understanding of the overall internal structure of a database system including indexing, query processing, transaction management, and fault tolerance
  4. Experience in designing a database for a new enterprise, implementing a design for that database in a real database system, and constructing user interfaces to the database via JDBC and/or PL/SQL (or similar)

Relationship between Course Outcomes and Student Enabled Characteristics

CSE 241 substantially supports the following student enabled characteristics:

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

J. An ability to apply 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.

Prerequisites by Topic

  • Basic data structures: trees, lists, graphs,etc.
  • Basic sorting algorithms
  • Java programming up to and including classes/objects

Major Topics Covered in the Course

  • SQL: schema definition, queries, nested queries, updates, complex join expressions, integrity constraints, authorization, index creation, transactions
  • Entity-relationship design
  • JBDC, SQL functions, SQL stored procedures
  • Triggers
  • Recursive queries
  • Ranking and OLAP queries
  • Relational database design: normalization and decomposition, losslessness dependency preservation
  • Enterprise database implementation
  • Storage management: buffer management, index structures
  • Transactions management
    • ACID properties
    • Recovery: write-ahead log, fault tolerance
    • Concurrency: serializability, two-phase locking, snapshot isolation
  • Parallel and  distributed databases (includes cloud)
  • Data mining and data warehouses

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 database (e.g. product and sales data for a retailer), create the database using SQL (on Oracle 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-based 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.