CPSC 343 Database Theory and Practice Fall 2006

CPSC 343 Course Information

Information is the currency of the Information Age, but having vast quantities of information is useless if you cannot quickly locate the relevant data. Computer databases are used to store, organize, and retrieve information in a useful way. Databases are extremely common, particularly in conjunction with Web sites - if you've ever used amazon.com, eBay, or HWS' library catalog (to name just a few sites), you've interacted with a database.

The goal of this course is to provide an introduction to the theory and practice of relational databases. Four major aspects of relational databases will be addressed: data modeling and database design, SQL, building a Web site which interacts with a database, and understanding how database systems store and process information reliably, securely, and efficiently. Both practical skills and the necessary theoretical underpinnings will be emphasized.


Stina Bridgeman
Lansing 312, x3614

Office Hours

M 12:30-1:30pm, W 3-4:30pm, R 10:30am-noon, F 9:30-10:30am
or by appointment (schedule)

Class Hours and Meeting Place

Lecture/discussion MWF 11:15am-12:10pm, Lansing 301

Some meetings will be in the Lansing 310 computer lab; these will be announced in advance.

Course Web Page


You are expected to regularly consult the course web page for announcements, assignments, and most handouts.


Fundamentals of Database Systems, 5th edition
Elmasri / Navathe
Addison-Wesley, 2007

Additional material will be handed out or posted on the course webpage.


CPSC 225 and CPSC 229


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

Course Content Overview

The course material will cover four major aspects of relational databases: data modeling and database design, SQL, building a Web site which interacts with a database, and understanding how database management systems store and process information reliably, securely, and efficiently. The objectives listed below paint - in broad strokes - what the successful student should be able to do at the end of each section.

Data Modeling and Database Design: The focus of this section of the course will be on the skills needed to effectively design a database using a relational database system such as MySQL. Specific topics include the entity-relationship (ER) model for data modeling, the relational database model and its theoretical underpinnings, the ER-to-relational mapping, the relational algebra and relational calculus, and principles of database design.


  • be conversant in the terminology of ER design and relational databases
  • 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
  • express a query using relational algebra and relational calculus, and explain the result of a query

SQL: This section of the course will focus on the SQL language, a standard for relational databases. Specific topics include data definition, queries, and data-modification commands.


  • use SQL to define a relational data model from an ER diagram, define constraints, populate and modify the database, and perform queries

Database Applications: This section of the course will examine specific technologies for creating applications which make use of databases. The particular focus will be on Web applications. Various technologies will be introduced, including HTML and PHP; other topics such as JSP, servlets, and JDBC will be introduced if time permits.


  • 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 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. If time permits, other topics such as transaction management, reliability, and security will be introduced.


  • 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
  • produce a set of plans for a moderately complex query, and evaluate which is the most efficient
  • use SQL to create and manipulate indexes

Assignments and Evaluation

Exams: There will be two midterm exams and a final exam. The midterms will have an in-class component (closed book, closed notes) and a take-home component (open book, open notes) which will be handed out the day of the in-class part and due several days later. The final will be entirely in the scheduled timeslot.

Homework: Homeworks are designed to reinforce topics covered in class, and to address topics not covered by the course project. To encourage discussion and keeping up with the course material (and to enable rapid feedback), some homeworks will be shorter assignments due one or two class periods after they are assigned; others will be a little longer and due one week after being assigned.

Project: A major component of the course will be a term project in which you will design and implement a database-driven website. There will be deadlines for intermediate phases of the project throughout the semester.

Final Grades: Final grades in this course will be computed as follows:

  • Homework: 30%
  • Project: 25%
  • Midterm Exams: 30% (15% each)
  • Final Exam: 15%

Attendance and Participation: On-time attendance and class participation (see the course policies) are expected, though they are not formally factored into your final grade. Missing class - for any reason - often results in lower grades because important material was missed. Similarly, not participating in class even if you are physically present may mean that you aren't actively following the material and thus may be missing more sophisticated or subtle points. Whether or not your grade is impacted for these reasons is up to you - it is your responsibility to get notes from another student or otherwise catch up on missed material. Also note that class participation and the number of unexecused absences are considered when deciding whether or not to round up a final grade which is just below a grade-level cutoff. See the course policies for the definition of unexcused and excused absences.

Late Policy and Collaboration: See the course policies for the late policy and collaboration policy.

Valid HTML 4.01!