San Jose State University : Site Name


Main Content

Working in Mars Mission Control, JPL

Ronald Mak

Department of Computer Engineering
Spring Semester 2017

Office location: ENG 250
Mission Control, Jet Propulsion Laboratory (JPL)
NASA Mars Exploration Rover Mission

CMPE 180-38: Database Systems Workshop

June 5-16 M-F   Lectures   10:30 AM - 12:30 PM   room ENG 232
Lab     2:00 PM -   4:00 PM   room ENG 232

Instructional Student Assistant

Qi Li


Every day M-F 2:00-4:00 PM
ENG 232

Spring 2017 Midterm


Slide rule photos


# Date URL
1 May 4 Exercise1.pdf
Sample solution: Exercise1-solution.pptx
2 May 9 Exercise2.pdf
SQL dump files: ZAGI.sql   HAFH.sql
3 May 11 Exercise3.pdf
Sample solution: Exercise3-solution.pptx
4 May 16 Exercise4.pdf
Solution: Exercise4-solution.pdf


# Assigned Project
1 June 5 Simple end-to-end web application
2 June 7 PHP prepared statements and object-relational mapping
3 June 9 Dimensional modeling and star schema
4 June 12 OLAP operations
5 June 13 XML
6 June 14 NoSQL database
7 June 15 Cisco Information Server (CIS)

Cisco Information Server


Date Content
Review Slides: 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; 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; SQL: create/drop tables, insert into, select, aggregate functions, group by, having, nested queries; table joins;
June 5 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
June 6 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; SQL: alter, update, delete tables; PHP query() vs. exec(); table join with PHP; SQL injection attack; PHP prepared statements; parameter binding; views; more table creation; constraint management

Example PHP features: strings.php   heredoc.php   indexedarray.php   assocarray.php   multiarray.php
Example SQL injection attack: teacher1.html   teacher1.php
Example PHP prepared statement: teacher2.html   teacher2.php
June 7 Slides: MySQL Workbench; reverse and forward engineering; 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; PHP error reporting; Assignment #2

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
June 8 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; recovery log; deferred update; checkpoints; replicated databases; replicated vs. backup; two-phase commit protocol

Database design example: cardealership.sql
June 9 Slides: 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 #3
June 12 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 #4
June 13 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 #5

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
June 14 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 #6
June 15 Slides: 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

CIS data access via JDBC example:   csjdbc.jar
June 16 Slides: Memory hierarchies; speed comparisons; B-trees: insertion, deletion; RAID: redundancy, levels; solid state drives (SSDs)

Team project demos

Recommended 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.