Database Foundations - Relational databases are a group of tables that are tied loosely together all within the same database. Using a key will tie other tables to one another. SQL is known as a data manipulation language that interprets on how to interact with the data.
Tables - To start making tables, you need to plan out what kind of data you need and organize those into tables depending on the relationship of certain types of data. Keep the names of these tables simple. Null is an absence of data. Using a unique key will help manage a cluster of data that may have similar values.
Relationships - Depending on the complexity of your database, you'll have to build relationships within these tables. Once relationships are established its important to make sure to preserve the integrity and how strictly to enforce them to keep it consistent within tables.
Database Optimization - Normalization rules are needed to keep the data from repeating itself, organizing tables, and preserving the integrity of the database. Sometimes you might have to denormalize a database in order to increase speed.
Querying a Database - Once you have a database designed, structured, and optimized you can use a query to be able to retrieve data and be able to specify what kind of data types you want to pull. Make sure to keep in mind about security of a database. Make sure you give any users the right amount of access and update your security to avoid SQL injection attacks. Also be mindful of any regulations from the government on how to manage personalized data.
SQL Essential Training
SQL Overview - SQL language allows us to be able to take the data we have and sort (using the SELECT), edit, insert or delete any data that is in a database.
Fundamental Concepts
Relationships - A joined query allows data from other tables to work together and share it via an ID number.
Transactions - A group of operations that does one unit of work. If any of these operations fail, then it will restore the data before any of the operations took place.
A Simple CRUD Application - This PHP application is a simple program that allows you to quickly change/add/delete data easily within the database.
Learning phpMyAdmin
What phpMyAdmin Does - phpMyAdmin is an open source database management system that has a graphical user interface. Its well supported and can be used to test offline.
Setting Up phpMyAdmin Locally - Learned how to set up a server connection, creating a database, setting up a user account while adjusting the privileges of the user, and enabling any advanced features in the databases.
Creating Tables - When creating a table for a database you need to pick the right data type for the columns. The MySQL default character set is “latin1_swedish_ci”. With phpMyAdmin you can quickly add columns and data types, and alter the structure of the table.
Importing and Exporting Data - You can import database information from an Excel spreadsheet or OpenDocument spreadsheet and be able to put it in as a column or row. Any databases that are exported should be used with the GZIP file format to compress the file and save space. The phpMyAdmin will decompress the file automatically.
Dealing with Foreign Keys - There are Foreign Key Restraints that help preserve the integrity of a database so that the data associated with the Foreign Key don’t become abandoned. You need to change the storage engine to InnoDB to set up the restraints. Using this restraint will prevent the user from deleting any part associated with the Foreign Key.
Running Queries - Performing a simple search in a table will show data that has partial letters in the query running. You can change the criteria of what you’re searching for in phpMyAdmin. When you find and replace text in a column it’ll show you the changes before you commit to them. When using the search feature you’re essentially running a WHERE clause and you can use it to join multiple tables together. You can also save certain queries as a bookmark to access it faster when using a database.
Views and Stored Programs - Creating a view allows you to be able to execute more complex queries and be able to view its results more efficiently. A stored procedure allows a user to alter certain pieces of data within the database. A stored function is a routine that will read the SQL data and show the results. Scheduling events will execute a procedure or function for a period of time.
Checking Performance - When you gather the profiling information you can take time to figure out the efficiencies of specific queries, how quickly it sends and sorts out data. Normalizing tables will help it run more effectively and help clean up any old and redundant data in the table. It will use the 1st - 3rd Form of Normalization depending on how far you would like to go.
