San Jose State University : Site Name

Navigation

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
E-mail: ron.mak@sjsu.edu
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

Assignments


# 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


Lectures


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 config.inc.php; 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
cats.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   testbedstart.inc.php   testbedend.inc.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


Goals


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:


Prerequisites


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 Redshelf.com)
eTextbook ISBN: 978-1-943153-18-3 (available from Redshelf.com and VitalSource.com)

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.