Relational Databases and MySQL for Beginners

Intended Audience This is the main introductory course to Relational Databases. It is suitable for anyone who has a basic understanding of computers who wishes to understand relational databases and how they work. It is especially useful for Web designers who will be implementing database driven web applications and who need to understand Relational Database technology from scratch. Rather than using graphical user interface tools to manipulate MySQL the course will use the MySQL command line client and a simple text editor to create SQL scripts. Course Overview This is an entry level course for MySQL application developers. It covers the fundamentals of relational database theory and design and the use of the Data Manipulation Language (DML) features of SQL to retrieve, delete and update information, and the Data Definition Language (DDL) features of SQL to create databases, tables and indexes The course will use MySQL, running on either Windows, Linux. It is generic in the sense that it does not depend on any particular operating system and that it concentrates on the most commonly used standard parts of SQL. The course includes an introductory discussion (with demonstrations) of how MySQL is used in Web applications, and how it can be accessed from programming languages such as PHP, and Python. Key Skills Understanding of the relational model and relational database terminology Ability to run SQL commands Understand entity relationship modeling and be able to draw entity relationship models using UML notation Be able to create databases and populate them with tables and data Implement basic SQL queries Implement complex SQL queries Understand transactions Practical Work About 50% of the course time will be allocated to practical exercises. The exercises will be built around a specific case study, and so will cover the entire process from design, to database implementation, to application development. Attendees will be given a CD containing the compiled and source code for the most recent stable release of MySQL, as well as a collection of applications that enhance the usefulness of MySQL. The course exercises and solutions, plus further exercises (without solutions -- though these can be requested via email .... on presentation of evidence that a serious attempt to tackle them has been made) will also be on the CD. Identifying the entities, attributes and relationships for a given business application Characterising Relationships as to their arity, and as to whether they are mandatory or optional Business Rules and what they mean Referential Integrity Constraints and the Enforcement of Business Rules Understanding the concepts of Candidate Primary Key, Primary Key and Foreign Key How relationships between entities are represented in a relational database Examples and dangers of storing redundant data in a database What Normalisation is all about - a Common Sense Approach - its not nearly as bad as its made out to be and it really is useful Carrying out normalisation to third normal form of a database that is (partly) in second normal form Interact with a pre-built database using SQL - both retrieving and modifying the data Adding new tables to the database and populating them with data Writing pseudo code to enforce referential data integrity constraints Implementing simple reporting applications --------------------------------------------------------------------------------Course Contents Introduction to MySQL The uses of databases The advantages of databases over files The history of MySQL Obtaining and installing MySQL on Linux / Windows machines Connecting to MySQL TCP/IP - Client/Server Command line utilities Graphical clients Web interfaces The Relational Model Relational Algebra Tables, Rows and Columns Keys Relationships Relational Operations Transactions SQL-92 features not supported by MySQL Entity Atrribute Relation (EAR) diagrams UML Class Diagrams corresponding to EAR diagrams Structured Query Language - SQL Data definition Inserting rows Updating rows Deleting rows Queries Joins Ordering Grouping Limiting Altering a table Design and Normalisation Requirements and Design specifications Entity Relation(ER) diagrams (and their UML equivalents) Reasons for Normalising First, Second and Third Normal Forms Boyce-Codd Normal Form Fourth Normal Form (for private study) Denormalisation Overview of Ways of implementing MySQL Clients C programs and the MySQL C API Java programs and JDBC PHP, Perl, Python C++ and the MYSQL++ API ADO.Net Data Analysis and Reporting with MySQL - an overview importing MySQL query results into spreadsheets Querying a MySQL database from Access Creating reports using MySQL and Crystal Reports Using MySQL to store and organise images and documents - an introduction How MySQL handles binary large objects Strategies for classifying images Strategies for classifying documents Example case studies Database Security - an overview Authentication and Authorisation Privileges Database Views as a security mechanism