CSCI 466

Database Management Systems

Coordinator: Stephanie Schwartz

Credits: 4.0

Description

Introduction to software design using a relational database management system. Data modeling, data normalization, database and application design, foundations of relational implementation, SQL, embedded SQL, and web publishing of database contents. Offered periodically.

Prerequisites

C- or higher in CSCI 362.

Sample Textbooks

Course Outcomes

  1. Able to design databases using the entity-relationship model, the object-oriented model, and a case tool.
  2. Able to design databases using data normalization techniques.
  3. Able to convert designs from the entity-relationship model and the object-oriented model to the relation model.
  4. Able to implement a design using a relation database available on the departmental computer network.
  5. Able to create graphical user-interface in one of the host languages supported by the available relational database management system.
  6. Able to design batch and screen-oriented reports.
  7. Able to describe concepts of distribute processing.

Major Topics Covered

A. Introduction

  1. Example Databases
  2. The Relationship of Application Programs and DBMS
  3. Definition of a Database
  4. A Short History of Database Processing
  5. Object-oriented DBMS

B. Components of a Database System

  1. The Database
  2. Creating the Database
  3. Components of Applications
  4. Developing Databases

C. Data Modeling

  1. The Entity-Relationship Models
  2. Semantic Objects Model
  3. Type of Objects
  4. Object-oriented Programming and Semantic Objects
  5. Comparing the Semantic Objects and Entity-Relationship Model

D. Data Normalization

  1. The Relation Model
  2. First Through Fifth Normal Forms
  3. Domain/Key Normal Form
  4. The Synthesis of Relations
  5. Design Trade-offs

E. Database Design

  1. Database Design Using Entity-Relationship Model
  2. Transformation from Entity-Relationship Model to Relational Database Design
  3. Trees, Networks, and Bills of Materials
  4. Transformation of Semantic Objects to Relational Database Design

F. Application Design

  1. Case Application: A Personnel Database
  2. Characteristics of Database Applications
  3. Objects and View Materialization
  4. Form Design
  5. Report Design
  6. Application Program Design

G. Foundations of Relation Implementation

  1. Defining Relational Database
  2. Relational Data Manipulation
  3. Relational Algebra

H. Structured Query Language

  1. Creating a relational database
  2. Query a Single Table
  3. Queries Involving Multiple Tables
  4. Updating tables
  5. Creating User Views
  6. Advanced topics

I. Imbedding Structured Query Language in Host Languages

  1. Imbedding SQL code in a Host Language
  2. SQL Module Language
  3. Calling SQL Modules from a Host Language

J. Client Server and Related Applications

  1. Multi-user Database-processing Alternatives
  2. Resource Sharing
  3. Client Server Systems
  4. Oracle and Sybase

K. Distributed Processing

  1. Components of Distributed Database Systems
  2. Four Goals for a Distributed DBMS
  3. Distributed Concurrency Control
  4. Failure and Transparency

Sample Laboratory Projects

There are two kinds of lab projects: database design projects and programming projects. In design projects, the student creates database model for a specified problem using a design tool such as SALSA.

The programming projects are done in SQL and one of the high level languages such as C, Perl, or Java. In a closed lab session of two-hour time-slot, the student is asked to write several SQL queries against an existing database. The student is asked to write programs to access, update, retrieve data from an existing database using a high level language.

The following is a representative set of laboratories:
Lab#1: Database design using a CASE tool such as The Analyst or SALSA.

Lab#2: Practice writing interactive SQL queries using SELECT statement, and statistical functions.

Lab#3: Practice writing interactive SQL queries involving subqueries, and queries involving several tables.

Lab#4: Creating a database using CREATE TABLE command, and loading data into the database using INSERT command.

Lab#5: User-interface design using a host language.

Lab#6: Imbedding SQL queries in a host language.

Lab#7: Loading data into a database from files using programs written in a host language.