|
Ronald Mak
Department of Computer Engineering
|
||||||
|
Mission Control, Jet Propulsion Laboratory (JPL) NASA Mars Exploration Rover Mission |
| # | 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) |
| 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 |
| 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 |
| May 16 | 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.
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 |
|
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 |
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.
