Upon completion of the course, students will be able to:
1. Describe a relational database.
2. Install and configure a web/database server environment.
3. Implement SQL statements using data design, definition, and manipulation techniques.
4. Implement stored procedures.
5. Develop complex data analysis queries in SQL.
6. Apply transaction and database administration concepts.
1. Definition of "relational database"
a. Contrast relational database and file-based databases
b. History of relational database theory
c. Client/server computing
d. History and current options in the database market
e. MySQL, the SQL language, and understanding tables
2. Database set-up
a. Installing and configuring MySQL with Apache, Hypertext Preprocessor (PHP), and PHPMySQL
b. Storage engines Index Sequential Access Method (ISAM) and Inno Database (InnoDB)
3. SQL basics: Syntax, Data Manipulation Language (DML), Data Definition Language (DDL), tables, select, distinct, where, and/or, order by, query and reporting in SQL
4. Data design
a. Data normalization theory
b. Primary key and foreign keys
c. Entity and relationship diagramming
5. Data definition
a. Create database, create table and create index
b. Synonyms, views, stored procedures
6. Data manipulation: insert, update and delete
7. Stored procedures: programming in the database
8. Web database programming
a. Accessing SQL through PHP
b. Building a simple create, read, update, delete data application using Hypertext Markup Language (HTML), Cascading Style Sheets (CSS) and PHP.
9. Data analysis: SQL functions, unions, group by, and complex joins
10. Web data analysis: creating data analysis web applications using Python
11. Transactions: theory and practice for simple and distributed transactions using SQL
12. Database administration: partitioning, replication, enterprise management tools, backup and recovery
Learning MySQL, by Tahaghoghi, Seyed and Williams, Hugh. O'Reilly Media: 2006.