New Building Website

CSA 385 Database Systems (3 credits)

Typically offered during both the fall and spring semesters.

Catalog description:

Overview of database management, database system architecture, and database modeling principles. Logical database design. The relational database model, relational integrity constraints, and relational algebra. Relational commercial database management systems and languages. Interactive database processing, view processing, and database application programming. Database integrity. Relational database design by normalization. File structures for database systems.

 Prerequisite: CSA 274 or concurrent registration.

Course Objectives:

  • To acquire an appreciation of the data resource and the issues in managing data.
  • To understand the significance and elements of hardware, software, and data structures in database systems.
  • To describe the basic concepts of a variety of data models, and use database languages to define and process large databases.
  • To acquire the skill for designing databases.

 Success Skills:

  • Team programming assignment
  • Written report on team project

Learning Outcomes:

CSA385.1:  Student can explain basic database concepts:
CSA385.1.1.    Student can explain the basic concepts of database systems including mini-worlds, entity sets, and relationship sets.
CSA 385.1.2  Student can explain applications of database systems and understand various architectures for using database systems.
CSA385.1.3      Student can explain the meaning of database transactions and the fundamental concepts of transaction processing.

CSA385.2:   Student can explain and apply basic relational database system concepts:
CSA385.2.1.      Student can explain the relational model including the definition of a relation, properties of relations, the mathematical theory of the relational database model, the notion of keys, and the meaning of NULL in a database.
CSA385.2.2    Student can explain the syntax and semantics of relational algebra operators and can formulate queries in relational algebra

CSA385.3:   Student can design a relational database: 
CSA385.3.1.      Student can design a relational database using the entity-relationship modeling and diagrams. 
CSA385.3.2.      Student can use the theory of normalization to normalize a database design.

CSA385.4: Student can explain and utilize SQL to query a database
CSA385.4.1.      Student can explain the syntax and semantics of statements of the SQL database language.
CSA385.4.2.      Student can express queries in the SQL database language. 
CSA385.4.3.      Student can explain meaning and uses of views in relational systems.
CSA385.4.4.      Student can utilize at least one commercial relational database management system that supports the SQL database language.

CSA385.5: Student can explain and utilize other database features:
CSA385.5.1  Student can explain structural and behavioral integrity constraints and use available database language features to enforce integrity constraints or to detect integrity constraint violations.
CSA385.5.2.      Student can implement software in a higher-level language that accesses a database.
CSA385.5.3.      Student can create and use views in relational databases and use views to develop deductive relational databases.
CSA385.5.4.      Student has completed a medium-sized team project involving design and/or implementation of a relational database.

CSA385.6:  Student can explain internal storage structures used by Relational Database Management Systems.
CSA385.6.1.      Student can explain the file structures that are used by Relational Database Management Systems.

 Required topics (approximate weeks allocated):

  • Overview of database management (.5)
    • database system and its components (hardware, software, data, users)
    • databases, rationale for databases
    • data abstraction and program/data independence in databases
  • Database system architecture (1)
    • views of data (external, conceptual, and internal)
    • external/conceptual and conceptual/internal mappings
    • database management systems, and their components
    • client/server architecture, distributed processing
  • Overview of database modeling (.5)
    • semantic database models
    • record-based logical models (relational, hierarchical, network), object-oriented model, and other models
  • Logical database design and the entity-relationship data model (1)
    • entities, relationships, and related concepts (attributes and domains, keys, types, etc.)
    • degree of relationships, mapping constraints for relationships
    • entity-relationship modeling and diagrams
  • The relational data model (.5)
    • theory of relations
    • the relational representation of databases
    • transforming E-R diagrams to relations
  • Relational database integrity (.5)
    • domain-type integrity constraints
    • null values and non null integrity constraints
    • foreign keys and referential integrity constraints
  • Relational algebra (1)
    • rationale for relational algebra
    • relational algebra operators for query formulations
    • database update operations
  • Relational commercial languages and the SQL language (2)
    • metadata and system catalog files
    • data definition and update operations
    • interactive SQL query language
    • set operators and predicate calculus in SQL
    • query formulations in SQL
    • equivalence of SQL to relational algebra (completeness)
  • View processing in SQL (.5)
    • views in relational systems and view definition in SQL
    • using view for deductive relational databases
    • updatable views
  • Database application programming and embedded SQL (2)
    • steps in processing database application programs
      • precompiler, precompilation, and database request module
      • bind, binding, and application plan
      • execution, language interface module, and runtime supervisor
    • declaring tables and views
    • declaring host variables in application programs
    • the SQL communication area
    • error handling and exception processing
    • cursors and cursor processing
  • Database integrity and integrity constraints (1)
    • prevention and detection methods for database integrity
    • integrity constraints (state vs transition, implicit vs explicit, structural, behavioral, etc.)
    • integrity subsystem and integrity constraint specification languages
    • database languages used as integrity constraint specification languages
  • Relational database design (theory of normalization) (1.5)
    • more on database semantics: Functional dependencies
    • full, partial, transitive, and multivalued dependencies
    • relational normal forms (first, second, third, Boyce/Codd, and fourth normal forms)
    • designing relational databases using the theory of normalization
    • designing relational databases from entity-relationship diagrams
  • File structures for database systems (2)
    • database access, disk manager, file manager, & clusters
    • page sets and files
    • indexing and B-tree indexing
    • static and dynamic hashing
  • Exams/Reviews (1)