CS 457 Data Base Processing

Catalog Description

Data independence, relational model, relational algebra and calculus, query languages and SQL, conceptual modeling, database design, data dependencies and normalization, access methods, tables, queries, forms, macros and reports, database administration, introduction to transaction processing, concurrent transactions, and recovery. Case studies of commercial database systems such as Oracle and Microsoft SQL Server. Prerequisite - C or better in CS 202 and CS 303.

Prerequisites

• TODO

Standard Content

Course Outline

• Database Models
• Relational Algebra
• SQL - A reasonably comprehensive introduction to SQL. SQLite is a very good, easy to use, vehicle for this part of the course.
• Database design: logical design, physical design, security design.
• Database design: ER diagrams, normal forms, functional dependencies, transitive closure, etc.
• Transactions: query processing, ACID, logging, indexing, hashing, B+ trees, joins.
• SQL - More advanced features (PL/SQL functions, triggers, etc.)
• NOSQL: discuss a subset of the following (whatever’s hot)
• XML
• Mongodb
• Redis
• levelDB
• DataFrames - Python/pandas
• Cassandra
• many more possibilities

Learning Outcomes

• TODO

Important Assignments and/or Exam Questions

• Relational algebra exercises (pencil and paper) with a variety of joins.
• Single table SQLite assignment for experience using SQLite features and basic queries.
• Multi-table SQLite assignment in the context of normal forms. An example: students at a university with tables for personal data (addresses), financial data (tuition payments), and performance data (credits, grades).
• Assignment to practice C or Python (or whatever) interface to SQLite.
• Postgres/Oracle assignment using multiple databases, tables, triggers, transactions, etc. Examples could include the student/university database with the addition of classes, grades, adding, dropping, room assignments, courses, scheduling, faculty schedules, etc.. A more standard example is a company with employees, customers, suppliers, departments, etc.
• (Mongodb, etc.) Pick smaller database problems and implement them using SQL, mongodb and redis (for example). Compare performance.

Standard resources

Stanford Database Course with Jennifer Widom (available from YouTube). Not a full course, but a nice outline that goes at a slower pace than one might expect from Stanford. PDF files are also available for a more comprehensive version of the course.