CPSC 343 Course Information

Catalogue description:

Computer databases are used to store, organize, and retrieve large collections of information. This course introduces the theory and practice of relational databases and relational database management systems (RDBMS). Topics include data modeling and database design, query languages (SQL), and elements of RDBMS implementation such as file structure and data storage, indexing, and query evaluation. Additional topics may include Web-based access to databases, transaction management, reliability, security, and object-oriented databases.

This course, like many of the other 300- and 400-level computer science courses, explores a particular subdiscipline of computer science. Many aspects of modern society and commerce involve the management of large quantities of data, so databases are a crucial component of computer systems. Furthermore, the study of database systems brings together many areas of computer science (theory, data structures, algorithms, parallel computing, user interfaces, and more); it is particularly nice as an application of theoretical computer science topics.

By the end of the course, the successful student should be able to:

• analyze and model the data needs of a real-world situation
• use a relational database management system to effectively store and access data
• construct an application that accesses a database
• explain how relational database systems store information and process queries, and apply that knowledge to increasing database performance

The course material can be divided into five modules:

Foundations and Alternatives: The course starts with an introduction to databases, database management systems, and information management concepts and wraps up with a consideration of NoSQL, a common alternative to relational databases.

Objectives:

• explain what distinguishes the database approach from programming with data files
• describe the components of a database system and the core function areas that a DBMS must address
• describe the three-schema architecture and the concept of data independence
• identify the stages of the data lifecycle, from creation through eventual destruction, and explain why managing data responsibly is a professional obligation, not just a technical one
• distinguish structured, semi-structured, and unstructured data, and explain the motivation for NoSQL alternatives to the relational model
• describe the key-value and document data models and identify situations where a NoSQL approach may be preferable to a relational one

Data Modeling and Database Design: A database is a structured collection of information; data modeling deals with capturing and expressing that structure. A well-designed database is easier to query, maintain, and extend. This section of the course will address the process of going from specifications to a collection of tables and columns. Specific topics include the entity-relationship (ER/EER) model for data modeling, the relational model, ER-to-relational mapping, and principles of database design, including normalization.

Objectives:

• define the concept of data independence and explain its importance
• explain the difference between different types of data models, such as conceptual data model, physical data model, and representational data model
• be conversant in the terminology of the ER and EER models
• be conversant in the terminology of relational databases
• identify the requirements of real-world situations
• create ER/EER diagrams to model the entities, relationships, and constraints of real-world situations
• explain the tradeoffs in modeling in a certain way (e.g. entity vs. attribute) and when to pick what
• create a relational schema from an ER/EER model
• explain functional dependencies and how they generalize the notion of a key
• identify anomalies that arise from poor design and apply principles of normalization to correct them
• defend a given decomposition as lossless and dependency-preserving
• explain circumstances under which denormalization may be a reasonable tradeoff
• identify and employ principles of good design

Query Languages: SQL is the standard language for working with relational database management systems. This part of the course will introduce SQL for data definition, queries, and data manipulation.

Objectives:

• express queries in SQL and explain what queries expressed in SQL do
• use INSERT, UPDATE, and DELETE statements to manipulate data, including understanding how referential integrity constraints affect modification operations
• create relational database schemas in SQL demonstrating appropriate choices of data types and which incorporate key, entity integrity, referential integrity, and check constraints

Database-Driven Applications: A database doesn't just sit by itself — application programs access the information stored in the database. This section of the course will introduce technologies for building a REST API — the back-end layer that a client application (web, mobile, etc.) talks to in order to interact with a database — along with the transactional, security, privacy, and ethical issues that become relevant once the outside world gets involved.

Objectives:

• explain how a three-tier application works and what technologies address what aspects of the system
• be conversant in the technologies introduced (i.e. be able to read and understand source code, modify an example to suit a similar purpose, and create basic examples from scratch)
• map CRUD operations onto the appropriate SQL statements and REST conventions
• explain what views, triggers, and stored routines are and give examples of when to use each
• implement views, triggers, and stored routines
• implement authentication and authorization
• implement meaningful error handling
• define each of the ACID properties and explain what problems can arise if the properties are not enforced
• describe ways to implement each of the ACID properties
• explain common concurrency anomalies and how isolation levels address them
• explain issues relating to security and how to address them
• explain the role of database auditing in detecting and investigating security incidents
• distinguish data security from data privacy and explain concepts such as personally identifying information, de-identification, and relevant legal/regulatory frameworks (e.g. GDPR, HIPAA)
• identify the stakeholders affected by a given data collection or use and analyze the ethical and social considerations involved
• implement a robust, secure REST API that exposes well-designed endpoints for interacting with a database, suitable for use by a client application

Database Management Systems and Database Tuning: The last part of the course will look at the software systems that make databases happen — the database management systems themselves. The goal is to make the student an informed user of database management systems by understanding some of the factors that affect performance and what control the database administrator/user has over these factors. Specific topics include file structure and data storage, indexing, and query evaluation.

Objectives:

• be conversant in the terminology
• explain how information is stored in a computer system and what issues are relevant to performance
• compare and contrast methods for indexing and explain when they should be used
• explain, at a conceptual level, how relational algebra and query trees relate to how a query is processed and how a query optimizer chooses among candidate execution plans
• apply standard transformation rules to convert a query tree into a more efficient equivalent
• use SQL to create and manipulate indexes
• read and interpret the output of EXPLAIN to diagnose how a query is being executed
• determine whether a given index supports an index-only plan for a given query

This course has three 60-minute class meetings per week. On-time attendance is expected for all scheduled class meetings. In addition, attendance is required at a total of nine 15-minute project-related meetings (midpoint check-ins and handin meetings) and an additional three hours of office hours over the course of the semester. Dates for the check-in and handin meetings will be established early in the semester. Reassessment meetings may substitute for office hours.

Out-of-class work for this course includes homework, a semester-long course project, and studying for exams. New homework problems will be assigned after most classes and will be collected weekly; while there will generally be up to two weeks to submit problems after they are assigned, it is important to put in steady effort and not let things pile up. The project phases are cumulative and steady effort and careful time management is again essential for success.

All together, students should expect to spend approximately 8-10 hours per week on work outside of class. While your experience may vary from topic to topic or from week to week, if you routinely spend substantially more time or you feel like you are spinning your wheels and not making progress, you should visit office hours for help.

CPSC 225 is required.
The course project will involve Java programming and your familiarity with "thinking like a programmer" will help you pick up the new language (SQL) used in this course.

Textbook

There is no textbook; supplementary reading and reference material will be posted on the schedule page.

Laptop

If you have a laptop that you are able to bring to class on lecture days, please do so. (It's OK if you don't; in-class activities will be done in small groups so you can team up with someone who does have a laptop.)

Software

MySQL Workbench (and optionally the MySQL commandline client), Eclipse 2026-06, and Java 21 will be used in this course. Some additional tools may also be used. It is highly recommended that you use the campus Linux environment, accessible in Demarest 002 or remotely through the Linux VDI, though in general the necessary software is freely available for multiple platforms so it is possible to set up your own computer if desired.

If you are off campus, you will need to connect via the VPN in order to access the MySQL server used for the course. VPN access is no longer enabled by default for students, so you will need to contact the Help Desk for access.

last updated: --Mon Sep 14 14:46:16 EDT 2026--
page owned by: bridgeman@hws.edu