Academic Catalog

C S 31A: INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS

Foothill College Course Outline of Record

Foothill College Course Outline of Record
Heading Value
Effective Term: Summer 2023
Units: 4.5
Hours: 4 lecture, 2 laboratory per week (72 total per quarter)
Advisory: One of the following: C S 1A, 2A, 3A, or equivalent.
Degree & Credit Status: Degree-Applicable Credit Course
Foothill GE: Non-GE
Transferable: CSU/UC
Grade Type: Letter Grade (Request for Pass/No Pass)
Repeatability: Not Repeatable

Student Learning Outcomes

  • Create a conceptual database design
  • Use Structured Query Language to perform queries on a database

Description

Introduction to database design and use of database management systems for applications. Topics include database architecture, comparison to file-based systems, historical data models, conceptual model; integrity constraints and triggers; functional dependencies and normal forms; relational model, algebra, database processing and Structured Query Language (SQL), database access from Applications-Embedded SQL, JDBC, Cursors, Dynamic SQL, Stored Procedures. Emerging trends will be studied, such as NoSQL databases, internet and databases, and Online Analytical Processing (OLAP). A team project that builds a database application for a real-world scenario is an important element of the course.

Course Objectives

The student will be able to:

  1. Examine the problems with file-based systems and the advantages of the database approach
  2. Distinguish between the three levels in the architecture of a typical database management system
  3. Practice conceptual database design through entity-relationship (ER), enhanced ER models; describe models of historical interest, such as network and hierarchical model
  4. Design and model a database application using the relational model; design by ER and EER to relational mapping
  5. Define and apply integrity constraints and triggers; tune design using functional dependencies and normal forms
  6. Use Structured Query Language to perform queries and to perform relational operations
  7. Understand emerging database technologies and applications

Course Content

  1. Introduction to database systems
    1. Why databases?
    2. File systems vs. DBMS
    3. Roles of the database management system (DBMS)
    4. Types of database management systems
    5. Description, data requirements, and transaction requirements
  2. Introduction to database design
    1. DBMS architecture and data independence
    2. The process for designing a database in industry
    3. Database languages
    4. Data Definition Language (DDL)
    5. Data Manipulation Language (DML)
  3. Relational model
    1. Historical perspective: hierarchical and network models
    2. Relational data structure
    3. Relational algebra
    4. Logical design of the application
    5. Conceptual design of the application
  4. Modeling relationships in the data
    1. Entity Relationship (ER) and Extended Entity Relationship (EER) modeling concepts
    2. Conceptual design with ER and EER modeling
    3. Logical database design: ER to relational mapping
  5. Database application development
    1. Normalization of databases
    2. Integrity constraints
    3. Tuning the database design
  6. Structured Query Language (SQL)
    1. Data definition commands
    2. Data manipulation commands
    3. SELECT queries
    4. Relational algebra operations
    5. Cursors, Dynamic SQL, stored procedures
    6. Embedded SQL (SQLJ), JDBC
    7. Virtual tables - views
    8. Advanced SQL: set operators, join operators, subqueries and correlated queries, SQL functions, procedural SQL
  7. Emerging trends in database
    1. Introduction to NoSQL databases
    2. Designing NoSQL databases
    3. Comparison of NoSQL databases
    4. Internet and databases
    5. Introduction to Online Analytical Processing (OLAP)

Lab Content

  1. Generate a database application (DA) from an existing database, based on a real-world scenario:
    1. Write detailed specifications for the DA
    2. Write data requirements for the DA
    3. Write transaction requirements for the DA
  2. Design a conceptual model of the database application using ER and EER models
  3. Complete logical design of the database application—ER to relational schema mapping
  4. Tune the model using normalization
  5. Implement the database application that includes:
    1. Fabricating substantial amount of data for the DA
    2. Getting started with the installed DBMS (Oracle/MySQL) - logging into and setting up the DBMS account, changing passwords, etc.
    3. Implementing SQL commands to create, modify, query, and maintain integrity on the installed DBMS for the DA
    4. Build a user interface for the DA
    5. Design an advanced application using cursors, triggers, stored procedures
  6. Represent the application using NoSQL

Special Facilities and/or Equipment

1. Access to a commercial database management system, such as Oracle or MySQL.
2. A website or course management system with an assignment posting component (through which all lab assignments are to be submitted) and a forum component (where students can discuss course material and receive help from the instructor). This applies to all sections, including on-campus (i.e., face-to-face) offerings.
3. When taught via Foothill Global Access on the internet, the college will provide a fully functional and maintained course management system through which the instructor and students can interact.
4. When taught via Foothill Global Access on the internet, students must have currently existing email accounts and ongoing access to computers with internet capabilities.

Method(s) of Evaluation

Methods of Evaluation may include but are not limited to the following:

Exams and quizzes
SQL programming assignments
Team project that designs and implements a database application for a real-world scenario

Method(s) of Instruction

Methods of Instruction may include but are not limited to the following:

Lectures
Online labs (including sections meeting face-to-face/on campus), consisting of:
1. An assignment webpage located on a college-hosted course management system or other department-approved internet environment. Here, the students will review the specification of each assignment and submit their completed lab work
2. A discussion webpage located on a college-hosted course management system or other department-approved internet environment. Here, students can request assistance from the instructor and interact publicly with other class members
Detailed review of assignment that includes model solutions and specific comments on the student submissions
In-person or online discussion, which engages students and instructor in an ongoing dialog, pertaining to all aspects of database management systems
When course is taught fully online:
1. Instructor-authored lecture materials, handouts, syllabus, assignments, tests, and other relevant course material will be delivered through a college-hosted course management system or other department-approved internet environment

Representative Text(s) and Other Materials

Coronel, Carlos, and Steven Morris. Database Systems: Design, Implementation, and Management. 2019.

Elmasri, Ramez, and Sham Navathe. Fundamentals of Database Systems. 2016.

Types and/or Examples of Required Reading, Writing, and Outside of Class Assignments

  1. Reading
    1. Textbook assigned reading averaging 30 pages per week
    2. Reading online resources as directed by instructor though links pertinent to databases
    3. Reading library and reference material directed by instructor through course handouts
  2. Writing
    1. Technical prose documentation that supports and describes the database application assignments that are submitted for grades. The document would include the following aspects of the database application:
      1. A description of the application, data requirements, and transaction requirements
      2. An entity-relationship diagram for the database application
      3. A set of relations with key attributes underlined for the DA obtained by translating E/R diagrams to relations
      4. A description on DA tuning to achieve the best possible performance, using:
        1. Tuning indexes: a description of indexes implemented to improve the speed of your queries
        2. Tuning conceptual schema: a description of the normalization/denormalization process used on the DA schema
        3. Tuning queries: a description of queries rewritten to improve the performance
      5. A user manual for the DA

Discipline(s)

Computer Science