Systems Implementation
Last revised: February 22, 2012
| Instructor's Notes | |||
|
Volker's 10-Steps for Database Design |
|
How to design databases (without all the technical jargon). |
|
|
Concepts of Database Design |
|
Details for each of the 10 steps of database design. |
|
|
SQL: Creating and Populating Database Tables |
|
Notes on how to create new tables, delete tables, populate tables, delete records, update records, and view records using SQL. |
|
|
Data Dictionary Template |
|
Word template for database data dictionary |
|
|
SQL: Single Table Queries |
|
Basics of SQL queries using the SELECT command. Covers the WHERE clause, ORDER BY clause, GROUP BY clause and calculated fields. |
|
|
SQL: Multi-Table Queries |
|
Advanced SQL queries to combine data from multiple tables. Includes discussions of Joins, subqueries, self joins, UNION, INTERSECT, MINUS. |
|
|
Updating Database Structure and Data |
|
SQL queries to change existing data in a database and to change the structure of existing tables. |
|
|
Database Administration |
|
SQL queries to create, manage and use views. SQL commands to grant privileges to other users. |
| Recorded Lectures | |||
|
|
SQL 1: Build and Populate Database | ||
Part 1
MP4
|
MySQL Workbench overview; Creating databases using SQL; Deleting databases using SQL; Generating a list of databases using SQL; Creating tables overview; SQL data types | ||
|
Part 2
MP4
|
Creating tables examples; Creating a table with multiple primary keys; Inserting records into a table; Inserting records into a table with an auto-increment key | ||
|
Part 3
MP4
|
Create table review; Insert review; Deleting records;Updating table data | ||
|
|
SQL 2: Single Table Queries | ||
|
Part 1
MP4
|
Logical Keys review; Select all; Selecting specific fields; Filtering records (Where); Filtering a Boolean field; Compound criteria (And, Or) | ||
|
Part 2
MP4
|
Using In( ) in a where clause; Using Like and wildcards in a Where clause; Checking for null field values; Using Distinct; Sorting query results; Creating calculated fields; Using an alias; Using calculated fields in a Where clause; Using calculated fields in an Order By clause; Concatenating strings | ||
|
Part 3
MP4
|
Formatting numbers and dates; Using statistics functions (Count, Sum, Avg, Min, Max), creating groups; filtering groups (Having); Adding a Rollup to a group; Using a subquery in a Where clause | ||
|
|
SQL 3: Multi-table Queries | ||
|
Part 1
MP4
|
Qualifying field names with table names; Creating a table alias; Linking tables using Where; Linking tables using Inner Join; Example joining 5 tables | ||
|
Part 2
MP4
|
More multi-table linking examples; Initial example of (Left/Right) Outer joins | ||
|
Part 3
MP4
|
Inner Join review; Creating statistics criteria (ex. greater than average) using subqueries; Linking tables using subqueries | ||
|
Part 4
MP4
|
Nested sub-queries; Tables joined to themselves; Set functions: Union, Intersect, Minus; Any/All with sub-queries; Left/right outer joins; Specifying a criteria with an outer join | ||
| Labs | Guided Learning Exercises | ||
|
|
SQL 1 - Creating Tables |
|
Lab to experiment with creating and populating tables. |
| Assignments | |||
|
|
Tickets |
|
Daily mini-assignments required to get into the next class period. |
|
|
Job Search |
|
Assignment to locate 3 job openings and document the requirements for those. |
|
|
Assignment 1 |
|
Self evaluation,terminology, fact finding review, group project presentation. |
|
|
Core Abilities Self-Evaluation Form |
|
Form used in class to allow students to evaluate their abilities to work productively, focus on quality, learn effectively, communicate clearly, work cooperatively, act with integrity, think critically, apply technology and demonstrate global awareness. |
|
|
Terminology Worksheet |
|
DBMS terminology worksheet for Assignment 1 |
|
|
Database Design Assignment |
|
Assignment to create ERDs and Data Dictionaries for sample tables. |
|
|
SQL Build and Populate |
|
Assignment to create databases, tables and records. |
|
|
Single Table Queries Master Files Result Set Key |
|
Assignment related to Chapter 3 in the text (Single Table Queries) MySQL Master Files for assignments. Use Workbench to import into server. Compare your query results to mine. |
|
|
Multi-Table Queries
Result Set Key |
|
Assignment related to Chapter 4 in the text (Multiple-Table Queries)
Compare your query results to mine. |
|
|
Updating Structure and Data |
|
Assignment related to Chapter 5 in the text (Updating Data) |
| Evaluation Sheets | Evaluate your own programs before turning them in. | ||
|
|
Premiere Products Database Design |
|
Evaluation sheet for Premiere Products Database Design assignment |
|
|
Henry Books and Alexamara Marina Database Design |
|
Evaluation sheet for Henry Books and Alexamara Marina Database Design assignment |
| Handouts | |||
|
|
Portfolio Instructions |
|
|
|
|
Installing XAMPP and MySQL Query Browser |
|
How to install XAMPP to get a portable version of MySQL. |
|
|
Backing Up and Restoring MySQL Databases Transfer Program |
|
Instructions for how to back up and restore your MySQL databases. Used to transfer databases from school to home and vice versa. Second link is a self-extracting zip file that contains the database transfer programs. |
|
|
Creating Database Access Forms |
|
Comprehensive list of steps for creating data access forms. |
| Additional Resources | |||
|
|
Microsoft TechNet |
|
Comprehensive SQL Resource |
|
|
Database Design Resource |
|
Good database terminology resource. |
