|
Ronald Mak
Department of Computer Science
|
||||||
|
Mission Control, Jet Propulsion Laboratory (JPL) NASA Mars Exploration Rover Mission |
| # | 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 |
| 8 | Apr 17 | May 7 |
Cisco Information Server (CIS)
How to upload database to CIS: MySQLDataSource.pdf Getting Started Guide 6.2 GettingStartedGuide-6.2.pdf |
| 10 | Apr 17 | May 14 | Final project |
| 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 config.inc.php; 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 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 |
| 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 testbedstart.inc.php testbedend.inc.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: WordPress-skeletons.zip |
| 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 Mac CIS download: Mac-CIS-7.0.5.zip |
| 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 |
| Apr 24 | Slides: Improve query performance; write better queries; query optimization; execution plan; cost; EXPLAIN |
| Apr 26 | Slides: RAID; MTTF; reliability and availability; parallel access; data striping; data redundancy; RAID levels 0, 1, 3, 5, 6, 10; hardware/software RAID; data loss; disk failures; disk recovery; MTBF and MTTR; backups |
| May 1 | Slides: Distributed databases (DDBS); distribution transparency; homogeneous vs. heterogeneous; performance improvements; DDBS complexities; horizontal and vertical fragmentation; data replication; federated databases; parallel databases; cloud computing: advantages, problems, categories; IaaS; PaaS; SaaS; DaaS |
| May 3 | Slides: Object-oriented databases (OODB); OO concepts; object identifier (OID); class hierarchy; user-defined type (UDT); nested type; reference type; methods; Object Query Language (OQL); object-relational database (ORDB); database administration; monitor and maintain; security; public key cryptography; Diffie-Hellman protocol |
| May 8 | Slides: Data mining: techniques, knowledge discovery, uses, new business opportunities, history; types of DB queries; data mining examples; data mining patterns: characterization, discrimination, associations; classification; clusters; measuring interest; major issues; Big Data; Hadoop; MapReduce; MapReduce example |
| May 10 | Data science (presentation only) |
Department policy is to enforce
all course prerequisites strictly
|
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 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 |
