Computer Science

ECS 165A Database Systems

ECS 165A DATABASE SYSTEMS (4) II

Format
Lecture: 3 hours
Discussion: 1 hour

Catalog Description:
Database modeling and design (E/R model, relational model), relational algebra, query languages (SQL), file and index structures, query processing, transaction management.

Prerequisite: Course 60

Credit restrictions, cross listings: None

Summary of course contents

  1. Overview of Database Systems
    1. Use of database systems
    2. Data models and database languages
    3. Architectures
  2. Conceptual Modeling and Design
    1. Design issues
    2. Entity-Relationship (E/R) diagrams
    3. Mapping to tables
    4. Comparison with UML
  3. Relational Model
    1. Structure of the model
    2. Relational algebra
  4. Structured Query Language (SQL)
    1. Basic concepts
    2. Simple and complex select statements, subqueries
    3. Aggregate and set functions
    4. Database modifications
    5. Stored procedures (PL/pgSQL)
  5. Rules in Databases
    1. Deductive rules and integrity constraints
    2. Active rules and database triggers
  6. File and Index Structures
    1. Organization of records in a file
    2. Indexing, B+ tree index files
  7. Query Processing
    1. Query cost and cost estimation
    2. Selection, sorting and joins
    3. Query rewriting
  8. Transaction Management
    1. Transaction concepts
    2. Concurrent execution, serializability
    3. Recovery

This course includes a design project, intended to give students the opportunity to work with a full-featured, object-relational database system (PostgreSQL) and develop a simple end-to-end application. The project spans all phases of the development life-cycle, from E/R design, to load scripts, complex SQL queries, application programming, and browser-based visualization.  Students are graded on creativity, design, programming methodology, documentation and satisfactory completion of stated tasks.

Goals:  Students will: (1) learn the design of database applications starting from the conceptual design to the implementation of database schemas and user interfaces; (2) learn about database design concepts, data models (E/R model, relational model), the database query language SQL, and components of a database management system; and (3) learn data access structures, query processing and optimization techniques, and transaction management.

Illustrative reading

  • R. Elmasri and S. Navathe. Fundamentals of Database Systems, 6th edition.Addison-Wesley, 2010
  • A. Silberschatz, H. Korth, and S. Sudarshan. Database System Concepts, 6th edition. McGraw-Hill, 2010
  • H. Garcia-Molina, J. Ullman, and J. Widom. Database Systems: The Complete Book, 2nd edition. Prentice-Hall, 2008

Computer Usage:
Students work individually on projects in a UNIX workstation environment, using standard UNIX tools as well as major database software packages, in particular the Oracle RDBMS.

Programming Projects:
This course includes three design projects, intended to give students the opportunity to work with major commercial database software packages. The projects establish certain database design concepts and techniques to be used, then give open-ended database design assignments that include conceptual design and database application programming. Students are graded on creativity, design, programming methodology, documentation and satisfactory completion of stated tasks

Engineering Design Statement:
The course is heavily design oriented. Three programming projects, counting 40% of the course, are based on student-originated design and implementation of database applications and the use of one major commercial database package at a database designer/programmer level. Students are required to follow guidelines to create their own applications in SQL, PL/SQL, Embedded SQL, test them, document them and submit executable code for grading each project.

In addition, both the midterm and final emphasize design-related questions dealing with selection of appropriate hardware/software configurations and the optimal use of systems and other software to optimize performance of database systems according to the specified requirements.

ABET Category Content:
Engineering Science: 2 units
Engineering Design: 2 units

GE3: Science & Engineering

Overlap: Minor overlap with ECS 166: both courses discuss database querying with SQL.

Instructors: B. Ludäscher

History: 2012.10.20 (B. Ludäscher): Changed catalog description, course contents, and overlap statement. Prior version by M. Gertz (October 2006)

Outcomes

1 X an ability to apply knowledge of mathematics, science, computing, and engineering
2 X an ability to design and conduct experiments, as well as to analyze and interpret data
3 an ability to design, implement, and evaluate a system, process, component, or program to meet desired needs, within realistic constraints such as economic, environmental, social, political, ethical, health and safety, manufacturability, and sustainability
4 an ability to function on multi-disciplinary teams
5 X an ability to identify, formulate, and solve computer science and engineering problems  and define the computing requirements appropriate to their solutions
6 an understanding of professional, ethical, legal, security and social issues and responsibilities
7 an ability to communicate effectively with a range of audiences
8 the broad education necessary to understand the impact of computer science and engineering solutions in a global and societal context
9 X a recognition of the need for, and an ability to engage in life-long learning
10 X knowledge of contemporary issues
11 X an ability to use current techniques, skills, and tools necessary for computing and engineering practice
12 X an ability to apply mathematical foundations, algorithmic principles, and computer science and engineering theory in the modeling and design of computer-based systems in a way that demonstrates comprehension of the tradeoffs involved in design choices
13 X an ability to apply design and development principles in the construction of software systems or computer systems of varying complexity
border