CS 486/586 Introduction to Database Management Systems

Credit Hours:

4/3

Course Coordinator:

Jesse Chaney and Bruce Irvin

Course Description:

Introduction to fundamental concepts of database management systems using primarily the relational model. Schema design and refinement. Query languages. Database application development environments. Overview of physical data organization, query optimization and processing, physical design, security, and transactions used in recovery and concurrency control.

Prerequisites:

CS 161 and CS 250. Recommended prerequisites: CS 251.

Goals:

To learn how to design effective database schemas that accurately model real-world information semantics. To learn how to pose complex database queries in SQL as well as theoretical query languages. To understand what is meant by a transaction. To gain an introductory background in concurrency control and recovery. To learn about indexing in database systems. To learn about advanced database models, such as object-relational models and data warehouses.

Upon the successful completion of this class, students will be able to:

1. Transform requirements into an ER diagram
2. Transform an ER Diagram into a relational Schema
3. Normalize a relational schema into BCNF
4. Analyze an application that includes embedded SQL
5. Translate between equivalent English, SQL, relational algebra queries
6. Explain how queries are optimized and processed
7. Given a query, evaluate which indexes are likely to be most effective
8. Explain how basic backup, recovery and concurrency control services are implemented.
9. Given a collection of SQL authorization statements, determine user permissions

Textbooks:

1. Database Systems: The Complete Book; Hector Garcia-Molina, Jeffrey Ullman, and Jennifer Widom; 2008
2. SQL: 1999: Understanding Relational Language Components, Jim Melton and Alan Simon, 2001 (recommended)

References:

Since you will be using SQL in some assignments, you may wish to have additional information concerning the SQL query language. SQL:1999--Understanding Relational Language Components, by Melton, Simon, and Gray (2001 ISBN 1-55860-456-1) is a good reference.

Major Topics:

• Relational Model of Data
• Entity-Relationship Modeling
• Relational Algebra
• SQL
• Application Programming Interfaces to SQL
• Query Processing
• Normalization Theory
• Transaction Management
• Indexing
• Database Security

Laboratory Exercises:

Implementation of a database application -- entering the schema, transforming and loading data, and posing queries. (4 weeks)

Oral and Written Communications:

Social and Ethical Issues:

Ethical considerations of ownership of information and protection of private information is discussed during the database security lectures. The class time devoted to social and ethical considerations is about 30 minutes. Students are graded on their understanding of database security, but not on their understanding of ethical issues.

Theoretical Content:

Relational algebra & calculus, and normalization theory are the primary theoretical components of the course, but also some theory of transaction concurrency control is covered. Theoretical topics are covered about 4 weeks out of 10 weeks.

Problem Analysis:

Students must analyze the information content of applications using entity-relationship modeling during the course.

Solution Design:

Students must design database schemas as a result of their ER modeling analysis.