San Jose State University : Site Name


Main Content

Working in Mars Mission Control, JPL

Ronald Mak

Department of Computer Engineering
Spring Semester 2017

Office hours: Th: 2:30-4:30 PM
Office location: ENG 250
Mission Control, Jet Propulsion Laboratory (JPL)
NASA Mars Exploration Rover Mission

CMPE 226: Database Systems

Section 1: Tu 6:00 - 8:45 PM, room DMH 227

Team Projects Website

Note: VPN is required to access this site
from outside the campus network!


# Assigned Due Project
1 Feb 7 Feb 14 Simple end-to-end web application
2 Feb 13 Feb 21 Entity-relationship diagram
3 Feb 21 Feb 28 Relational database model
4 Feb 28 Mar 9 Table joins and PHP prepared statements
5 Mar 7 Mar 14 Object-relational mapping
6 Apr 45 Apr 11 Dimensional modeling and star schema
7 Apr 11 Apr 18 OLAP operations
8 Apr 18 Apr 25 XML
9 Apr 25 May 2 NoSQL database
10 May 2 May 9 Cisco Information Server (CIS)

Cisco Information Server


Date Content
Jan 31 Slides: Course objectives; key concepts; transaction processing; structured vs. unstructured data; data consistency; transaction concept; write-ahead logging; atomicity; database locking; replicated databases; replication vs. backup; two-phase commit; two-phase commit and replication; relational databases; keys; virtual tables; relational algebra; XAMPP
Feb 7 Slides: Client-server web apps; basic "naked" HTML; HTML page template; paragraphs and headings; lists; tables; links; images; form data; get and post methods; text input; checkbox input; radio button input; select list input; three-tier architecture; phpMyAdmin; set the root password; edit; add a new database user; create a table; insert data; MySQL command line; simple end-to-end web application; fetch mode; single-query alternative; Assignment #1

Example basic HTML files: paragraphs.html   lists.html   tables.html   links.html   images.html
Example image files: RonCats1a.jpg   RonCats1a.jpg   RonCats1a.jpg   RonCats1a.jpg
Example form HTML files: text.html   checkbox.html   radio.html   select.html
Example PHP form processing: text.php   checkbox.php   radio.php   select.php

Simple end-to-end web application: index.html   queryDB.php
Feb 14 Slides: PHP syntax; variables; strings; heredocs; string operations; constants; arrays; indexed arrays; associative arrays; looping over array elements; multidimensional arrays; functions; scope; PHP Data Objects (PDO); PDO examples; database system architecture; development steps; requirements; database models: conceptual, logical, physical; front-end applications; operational vs. analytical DBs; entity-relationship (ER) diagrams (ERD); entities and attributes; attributes: composite, multivalued, derived, optional; relationships: cardinality, types, attributes, unary, multiple; weak entities; associative entities; Assignment #2; logical data model; relation conditions and properties; primary key; mapping entities; entity integrity constraint

Example PHP features: strings.php   heredoc.php   indexedarray.php   assocarray.php   multiarray.php
Feb 21 Slides: Foreign keys; mapping 1:M, M:N, 1:1 relationships; referential integrity constraint; mapping example #1; mapping candidate keys, multivalued attributes, derived attributes; mapping example #2; mapping unary relationships, multiple relationships, weak entities; mapping example #3; relational database constraints: implicit and user-defined; database update operations and anomalies; normalization; functional dependencies: trivial, augmented, equivalent, full key, partial, and transitive; normalization: 1NF, 2NF, and 3NF; normalization example; normalization vs. denormalization; Assignment #3
Feb 28 Slides: SQL: create/drop tables, insert into, select, aggregate functions, group by, having, nested queries; table joins; alter, update, delete tables; PHP query() vs. exec(); table join with PHP; SQL injection attack; PHP prepared statements; Assignment #4; views

Example SQL injection attack: teacher1.html   teacher1.php
Example PHP prepared statement: teacher2.html   teacher2.php
Mar 7 Slides: More table creation; constraint management; self join; inner and outer joins; IS NULL; EXISTS; NOT; populate a table with a query; cascading deletes and updates; indexing; MySQL text, numeric, date, time functions; PHP input filtering; object-oriented PHP: classes; inheritance, objects, abstract classes, interfaces, traits; object-relational mapping (ORM); type hinting; namespaces; Assignment #5; PHP error reporting

Example object-oriented code: Pet.php   Shape.php   Triangle.php   Crud.php   Debug.php   Rectangle.php   traittest.php
Example ORM code: people1.html   queryDB1.php   people2.html   queryDB2.php
Example error handling code: display.php   levels.php   suppress.php   custom.php
Mar 14 Slides: Database design example; stored procedures; CALL; parameters; IF; CASE; WHILE; REPEAT; LOOP; triggers; transaction management; simple record update; transaction steps; basic ideas; ACID properties; MySQL transaction example; concurrency problems; lost and uncommitted updates; inconsistent analysis; non-repeatable read; phantom data; transaction conflicts; shared and exclusive locks; lock compatibility; deadlocks; two-phase locking protocol; optimistic techniques; failure and recovery; loss of disk data; failure to write; system failure; recovery log; deferred update; checkpoints; midterm preparation
Mar 21 Midterm examination
Apr 4 Slides: Midterm solutions; data deluge and transformation; operational vs. analytical data; application-oriented vs. subject-oriented; what is a data warehouse; DW components; extract-transform-load (ETL); data marts; creating a DW; dimensional modeling; dimension and fact tables; star schema; surrogate keys; star schema queries; transaction ID and time; multiple fact tables; Assignment #6
Apr 11 Slides: Detailed and aggregated fact tables; line-item and transaction-level fact tables; fact table granularity; slowly changing dimensions; snowflakes; data warehouse architectures; online transaction processing (OLTP); online analytical processing (OLAP); drill up and drill down; slice and dice; pivot; OLAP/BI tools; Assignment #7
Apr 18 Slides: XML: components, namespaces, tools; commercial XML editors; XPath: location paths, axes, expressions, functions; XML schema; well-formed vs. valid documens; schema types: simple, derived, and complex; attributes; XQuery; scripts; FLWOR expressions; user-defined functions; XML and relational databases; XSLT: XML to HTML transformation; Assignment #8

Sample XML documents:   catalog.xml    catalog2.xml    directory.xml
Corresponding XSD schemas:   catalog.xsd    catalog2.xsd    directory.xsd

Example XQuery scripts:
example-1.xql    example-2.xql    example-3.xql    example-4.xql
example-5.xql    example-6.xql    example-7.xql

Example XSLT documents:
example-1.xsl    example-2.xsl    example-3.xsl    example-4.xsl
example-5.xsl    example-6.xsl    example-7.xsl
Apr 25 Slides: Server: individual and team accounts; NoSQL benefits and disadvantages; types of NoSQL databases: key-value stores, graph stores, wide-column stores, document databases; comparisons of types; document model; agile development; scalability; auto-sharding; cloud computing; data replication; data caching; CAP theorem; SQL vs. NoSQL; MongoDB; documents and collections; cursor; eventually consistent; interfaces; command-line demo; aggregation pipeline; map-reduce for aggregation; MongoDB + PHP: create a collection, insert a document, find all documents, update a document, delete a document; Assignment #9
May 2 Slides: Team presentation schedule; data virtualization; Cisco Information Server (CIS); CIS team accounts; logging in; browsing database and XML data sources; XML data ==> database table; create views by joining tables; publish results; data mining: uses, new business opportunities, history; knowledge discovery; types of DB queries; data mining examples; data mining patterns: characterization, discrimination, associations, classification and prediction, clusters, outliers, evolution; patterns; measuring interest; major issues
May 9 Team project demos


The primary goal of this class is to learn the fundamentals of relational database technologies and data management tools and practices for operational and analytical processes. Each project team will design and develop an enterprise-class data management application during the semester.

Team Projects

Small project teams will each design and develop a significant enterprise-class data management application throughout the semester. Weekly assignments will develop the technologies required by the projects.

Project rubrics

Example projects from previous semesters:


Department policy is to enforce
all course prerequisites strictly

CMPE 272 Enterprise Software Platforms Grade C- or better,
or instructor consent

Required book

Database Systems: Introduction to Databases and Data Warehouses
Nenad Jukic, Susan Vrbsky, and Svetlozar Nestorov
Prospect Press, 2017

Paperback ISBN: 978-1-943153-19-0 (available from
eTextbook ISBN: 978-1-943153-18-3 (available from and

These are much less expensive versions of the textbook originally published in 2014 by Pearson.
Pearson ISBN: 978-0-13-257567-6

Software to install

Download and install XAMPP which will install and configure on your Mac, Windows, or Linux platform the following software packages:

You can download and install these packages separately, but then you'll have the hassle of configuring them to work nicely together.

Later in the semester, you will also download and install the Cisco Information Server (CIS) Studio application.