San Jose State University : Site Name


Main Content

Working in Mars Mission Control, JPL

Ronald Mak

Department of Computer Science
Spring Semester 2018

Office hours: TuTh: 3:00-4:00 PM
Office location: ENG 250
Mission Control, Jet Propulsion Laboratory (JPL)
NASA Mars Exploration Rover Mission

CS/SE 157B: Database Management Systems II

Section 3: TuTh 9:00 - 10:15 AM, room MH 233


# Assigned Due Assignment
1 Feb 6 Feb 19 Design a database
2 Feb 20 Feb 28 PHP functions and object-relational mapping
3 Mar 1 Mar 9 Dimensional model and star schema
4 Mar 6 Mar 23 OLAP operations
5 Mar 22 Apr 9 XML and XQuery
6 Apr 5 Apr 13 MongoDB database and API
7 Apr 12 Apr 20 MongoDB database and REST


Date Content
Jan 25 Slides: Topics and course learning outcomes; project teams; postmortem report; grading; database requirements; data models; front-end apps; operational vs. analytical databases
Jan 30 Slides: ERDPlus; entities and attributes; ER diagrams; relationships; cardinalities: 1:1, 1:M, M:N, exact; weak entities; logical database model; relational tables; primary key; mapping entities; entity integrity constraint
Feb 1 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
Feb 6 Slides: phpMyAdmin; set the root password; edit; add a new database user; create a table; insert data; MySQL command line; MySQL Workbench; Assignment #1; database update operations and anomalies; normalization; functional dependencies: trivial, augmented, equivalent, full key, partial, and transitive
Feb 8 Slides: Normalization: 1NF, 2NF, and 3NF; normalization example; normalization vs. denormalization; create table examples; drop table sequences; joins; Cartesian products
Feb 13 Slides: Table creation with constraint management; insert into select; 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;

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
Feb 15 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 example; PHP query() vs. exec(); table join with PHP; SQL injection attack; PHP prepared statements;

Example PHP features: strings.php   heredoc.php   indexedarray.php   assocarray.php   multiarray.php
Simple end-to-end web application: index.html   queryDB.php
Example SQL injection attack: teacher1.html   teacher1.php
Example PHP prepared statement: teacher2.html   teacher2.php
Feb 20 Slides: 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; cascading deletes and updates; indexing; MySQL text, numeric, date, time functions

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
Feb 22 Slides: GROUP BY; GROUP BY HAVING; views; database design example
Feb 27 Slides: Data deluge and transformation; operational vs. analytical data; application-oriented vs. subject-oriented; what is a data warehouse; data warehouse components; extract-transform-load (ETL); data marts; business intelligence (BI) applications
Mar 1 Slides: Dimensional modeling; dimension and fact tables; star schema; surrogate keys; star schema queries; transaction ID and time; multiple fact tables; detailed and aggregated fact tables; fact table granularity; line-item and transaction-level fact tables; Assignment #3
Mar 6 Slides: Final project details; 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; conformed dimensions; OLAP/BI tools; Assignment #4
Mar 8 Slides

Guest lecturer: Robert Nicholson
Topic: Content management and WordPress
Example code:
Mar 13 Slides: Extract-transform-load (ETL); ETL example; ETL infrastructure; review for the midterm
Mar 20 Slides: XML: components, namespaces, tools; commercial XML editors; XPath: location paths, axes, expressions, functions;

Midterm solution
Sample XML document:   catalog.xml   
Mar 22 Slides: XML: components, namespaces, tools; commercial XML editors; XPath: location paths, axes, expressions, functions; XML schema; well-formed vs. valid documents; schema types: simple, derived, and complex; attributes; Assignment #5; XQuery; scripts; FLWOR expressions; user-defined functions

Sample XML documents:   catalog.xml    directory.xml
Corresponding XML schemas:   catalog.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
Apr 3 Slides: 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
Apr 5 Slides: Model-view-controller (MVC); routes; HTTP methods; serving web pages; node.js; Express; form data; GET and POST; three-tier web app; Express hello world app; Express form examples: text fields, checkboxes, radio buttons, menu;

Example Express program:
Hello, World: package.json   app.js   index.js   main.js  
Form Examples: package.json   app.js   index.js   main.js   index.html   text.html   checkbox.html   radio.html   select.html
Apr 10 Slides: Eclipse node.js plug-in; Express/MongoDB example; initialize the MongoDB database; connect to the database; display users; new user form; add a new user; delete a user

Example Express-MongoDB code (original version): package.json   app.js   index.js   cntrlMain.js   modelMain.js   error.jade   index.jade   layout.jade   newuser.jade   showuser.jade   userlist.jade
Apr 12 Slides: Dangers of URL auto-completion; new version of Express-MongoDB example; model-view-controller (MVC); routes; REST: resources, routes, guiding principles; database actions and HTTP verbs; default RESTful actions; RESTful app example; Assignment #7;

Example Express-MongoDB code (new version): package.json   app.js   index.js   cntrlMain.js   modelMain.js   error.jade   index.jade   layout.jade   newuser.jade   showuser.jade   userlist.jade   deleteuser.jade
Apr 17 Slides: Server accounts; CIS team accounts; logging in; browsing database and XML data sources; XML data ==> database table; create views by joining tables; publish results

CIS Studio: InstallationGuide.pdf   GettingStartedGuide.pdf   Studio-7.0.5-windows-x64-installer.exe
Apr 19 Slides: 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


This course will concentrate on practical aspects of database management systems as currently practiced by industry: The emphasis will be on acquiring job skills that employers want for new hires in data management. Gain confidence and proficiency in developing enterprise-class transactional and analytical applications that use back-end data management. The course will also introduce some key data science topics, including Big Data, cloud computing, and data mining.


Department policy is to enforce
all course prerequisites strictly

CS 157A (with a grade of "C-" or better); Computer Science, Applied and Computational Math, or Software Engineering Majors only; or instructor consent.

Recommended books

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

Publisher-provided slides
The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence, 2nd edition
Ralph Kimball and Margy Ross
Wiley, 2015
ISBN: 978-1119216315
Database Systems: The Complete Book, 2nd edition
Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Windom
Pearson Prentice Hall, 2009
ISBN: 978-0-13-187325-4