PHP/SQL 2: Relational Theory and Logical Design

In this course, you will learn advanced database theory, design, optimization, and security.

Upon completion of this course, you will be able to:

• Create, administer, back up, and optimize databases and user permissions in MySQL.
• Import and export files based on SQL results.
• Demonstrate understanding of relvars, views, and Relational Algebra.
• Design a database logically using predicates and constraints, keys, and orthogonality.
• Normalize relational databases to prevent design flaws.
• Translate logical design into SQL tables.
• Prevent database security holes, update anomalies, and SQL injection attacks using PHP.
• Design and create a fully-functioning social networking site using PHP and SQL.

From beginning to end, you will learn by doing SQL-based projects and submitting them for instructor feedback.  These projects, as well as the final project, will add to your portfolio and will contribute to certificate completion.  Besides a browser and Internet connection, all software is provided online by the O’Reilly School of Technology.

This course counts toward the PHP/SQL Programming Certificate Series.

Prerequisites: PHP/SQL 1: Introduction to Database Programming, or equivalent skills.  This course is meant for the intermediate or advanced database programmer.

90 Clock-hours  6 CEUs  $398.00 $278.60

Course Details and Syllabus

Course:	PHP/SQL 2: Relational Theory and Logical Design
Tuition:	$398 $278.60
Time Frame:	This course is online and self-paced. You can expect to work approximately 90 hours on this course.
Technical Requirements:	As long as you have a web browser and internet connection, you can take this course from anywhere in the world.
CEUs:	6 Continuing Education Units with official letter from O’Reilly Media.
Software:	The web-based Learning Sandbox® provided for you will contain all your lessons, projects, quizzes, account files, editors, and programming tools necessary to build your skills from beginning to end, even beyond coursework. In addition, you’ll be provided with a mySQL account that can be accessed through the Sandbox or, if you prefer, any normal SSH program. No other software is needed.
Instructor:	You will have one instructor throughout the course who will evaluate your projects and quizzes, hand them back for improvement when necessary, and coach you throughout your skills advancement.
Book:	All required course materials and software are included online within the Learning Sandbox®. However, within a few weeks of enrolling, you’ll receive the e-book SQL and Relational Therapy, Second Edition as a reference resource from O’Reilly.
Certificates:	Completion of this course counts toward the PHP/SQL Programming Certificate Series.
Prerequisites:	Introduction to PHP and PHP/SQL 1: Introduction to Database Programming, or equivalent skills in PHP and SQL. This course is meant for the intermediate or advanced database programmer.
Topics:	The Relational Model, Relational Algebra, Database Theory, Logical Design, Normalization, Predicates and Constraints, Keys and Integrity, Preventing SQL Injection, Form and Database Checking, Transactions and Rollback, Database Users and Permissions, Analyzation and Database Backup, etc.
Syllabus:	PHP/SQL 2 Lab 1 — Introduction and Review How to Learn using OST Courses Understanding the Learning Sandbox Using the PHP DBI, Revisited PHP/SQL 2 Lab 2 — Database Administration for Programmers Your Own DB Server Creating and Altering Databases Creating Database Logins Importing and Exporting Delimited Files PHP/SQL 2 Lab 3 — Relational Database Theory The Relational Model Relations, Attributes, and Tuples Base Relvars and Views PHP/SQL 2 Lab 4 — Relational Algebra Seven Relational Operators Relvar Assignment PHP/SQL 2 Lab 5 — Logical Database Design Logical Design Using Predicates Enforcing Integrity with Constraints Keys and Entity Integrity Foreign Keys and Referential Integrity The Principle of Orthogonality PHP/SQL 2 Lab 6 — Preventing Design Flaws with Normalization First Normal Form, Revisited Functional Dependencies Boyce/Codd Normal Form Nonloss Decomposition and Join Dependencies PHP/SQL 2 Lab 7 — Logical Design in Practice Translating Predicates into SQL Tables Enforcing Constraints with Keys and UNIQUE Choosing the Primary Key PHP/SQL 2 Lab 8 — Using PHP to Protect your Database Enforcing Constraints Through Form Checking Preventing SQL Injection Attacks PHP/SQL 2 Lab 9 — Avoiding Update Anomalies Delete vs. Flag Enforcing Referential Integrity Through Table Checking Transactions and Rollback Functions PHP/SQL 2 Lab 10 — Command-Line Database Safety Granting and Revoking Permissions Backing Up Your Database Analyze, Check, and Optimize PHP/SQL 2 Lab 11 — Planning a Database Project, part 1 Determining the Business Rules Choosing Constraints and Keys Designing and Normalizing the Relvars PHP/SQL 2 Lab 12 — Planning a Database Project, part 2 Creating the SQL Database and Tables Adding Keys Flags, Users, and Permissions Ensuring Safety PHP/SQL 2 Lab 13 — Planning a Database Project, part 3 The Home Page Allowing Members to Join PHP/SQL 2 Lab 14 — Planning a Database Project, part 4 The Login Interface Viewing Member Profiles PHP/SQL 2 Lab 15 — FINAL PROJECT: Your Own Social Networking Site