MySQL is an open source relational database management system (RDBMS). It is the world’s most popular open source RDBMS and is currently ranked as the second most popular RDBMS in the world (behind Oracle Database).
MySQL is available as a free download, however, several paid editions are also available which offer additional functionality.
As the name suggests, MySQL is based on SQL. The “My” part is named after co-founder Michael Widenius’ daughter, My.
Microsoft Access is a relational database management system (RDBMS) developed by Microsoft. It’s part of the Microsoft Office suite, included in the Professional and higher editions or sold separately.
Microsoft Access provides a quick and easy way to develop databases and is particularly well suited for individuals and small business. Access is the most popular desktop database on the market.
In Access, most tasks can be performed via the graphical user interface (GUI), but more advanced users can also develop applications using Visual Basic for Applications (VBA) code. Advanced users can also use SQL code to write or modify queries.
SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is Microsoft’s enterprise level RDBMS offering, and is a more sophisticated and robust system than Access, which has traditionally been a desktop system.
SQL Server’s main competitors are Oracle Database, MySQL (now owned by Oracle), PostgreSQL, and IBM’s DB2.
SQL Server is a client-server based system, which means that it operates as a server, typically containing many databases, with multiple clients accessing the databases from across a network. These clients are often other applications (such as a website or CRM system). This is in contrast to desktop systems, where the database will often (but not necessarily) reside on the user’s computer.
SQL is the standard language for querying data inside a relational database management system (RDBMS). It is supported by all of the major database systems, such as Microsoft Access, SQL Server, MySQL, Oracle, PostgreSQL, DB2, etc.
SQL is a relatively easy language to learn when compared to most programming languages. It is based on SQL “statements” that, at times, can resemble natural language.
For example:
SELECT CustomerName FROM Customers WHERE CustomerId = 1
The above SQL statement is asking the database to:
“Select the value of the CustomerName column from the Customers table where the CustomerId column’s value equals 1“.
MySQL Workbench is a graphical tool for working with MySQL.
MySQL Workbench provides an easy to use interface for performing the many tasks involved when working with databases. It integrates SQL development, administration, database design, creation and maintenance into one visual integrated development environment.
MySQL Workbench is similar to SQL Server’s SSMS, which is used for administering SQL Server.
An orphaned record is a record whose foreign key value references a non-existent primary key value.
Orphaned records are a concept within database relationships. If a row in a related table references a non-existent row in the primary table, it is said to be an orphaned row. This is because it has no “parent” with which its data is associated with. The terms orphaned row and orphaned record tend to be used interchangeably, even though there’s a subtle difference between a row and a record.
Referential integrity refers to the accuracy and consistency of data within a relationship.
In relationships, data is linked between two or more tables. This is achieved by having the foreign key (in the associated table) reference a primary key value (in the primary – or parent – table). Because of this, we need to ensure that data on both sides of the relationship remain intact.
So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.
The term data integrity refers to the accuracy and consistency of data.
When creating databases, attention needs to be given to data integrity and how to maintain it. A good database will enforce data integrity whenever possible.
For example, a user could accidentally try to enter a phone number into a date field. If the system enforces data integrity, it will prevent the user from making these mistakes.
Maintaining data integrity means making sure the data remains intact and unchanged throughout its entire life cycle. This includes the capture of the data, storage, updates, transfers, backups, etc. Every time data is processed there’s a risk that it could get corrupted (whether accidentally or maliciously).
In database terminology, field is often used to refer to the individual cells within a row or column. However, it can also refer to the whole column itself.
When referring to an individual cell, we’re usually referring to the value within that cell. So a user might ask “what value is in the FirstName field?” when referring to an individual record.
When referring to the whole column, we’re usually referring to the name of the column, its data type, constraints, and any data contained within that column.
In relational databases, a record is a collection of fields that contain data about a given entity.
A record is typically stored as a row in a table. A record contains the smallest amount of data that can be inserted, updated or deleted from a table.
An example of a record could be a single row in a “Customers” table. This row could contain the customer’s first name and last name for example. Therefore, the record contains the customer’s first name and last name. It could also contain other fields as required – such as an ID field, the date the record was created, etc.