CPSC 343 Course Information


Course 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, the relational algebra and relational calculus, 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.

Course Web Page	http://math.hws.edu/bridgeman/courses/343/f12/ You are expected to regularly consult the course web page for announcements, assignments, and most handouts.

Text	A First Course in Database Systems, 3rd edition Jeffrey D. Ullman and Jennifer Widom Pearson Prentice Hall, 2008 ISBN 0-13-600637-X, 978-0-13-600637-4 The book should be in stock at the bookstore. Both print and e-book versions are available. Additional material will be handed out or posted on the course webpage.

Prerequisites	CPSC 225 is required. Your familiarity with one programming language (Java), with using typical programming constructs to solve problems, and, more generally, with "thinking like a programmer" will help you pick up the new languages (SQL, HTML, PHP) used in this course. CPSC 229 is recommended. Relational databases are all about relations, a topic introduction in CPSC 229, and there is a great deal of theory underlying the design of relational databases and query languages for those databases. The necessary material will be introduced in this course, but previous exposure to the theoretical side of computer science will be useful.

Rationale & Aims	This course, like 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 a Web application to access a database explain how relational database systems store information and process queries, and apply that knowledge to increasing database performance

Course Content Overview	The course material can be divided into four areas: The Relational Model and SQL: The first part of the course will introduce relational databases, some of the underlying theory, and the database query language SQL. Objectives: be conversant in the terminology of relational databases use SQL to create tables and, define constraints, populate and modify the database, and perform queries Database Design: 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) model for data modeling, ER-to-relational mapping, and principles of database design. Objectives: be conversant in the terminology of ER design identify the requirements of real-world situations create ER 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 identify and employ principles of good design Database-Driven Web Applications: This section of the course will examine specific technologies for creating applications which make use of databases. The particular focus will be on building Web applications using HTML and PHP. Objectives: be conversant in the terminologies introduced 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) implement a complete web application which provides a basic, user-friendly interface and interacts with a database Database Management Systems: The last portion 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 express a query using relational algebra, and explain the result of a query produce a set of plans for a moderately complex query, and evaluate which is the most efficient use SQL to create and manipulate indexes