MySQL Statements: RDBMS
Page 1
Entity Relationship Diagram
Introduction
A Relational Database Management System (RDBMS)
includes tables which relate to each other
with primary and foreign keys.
The Enhanced Entity Relationship (EER) diagram, below, represents
the relationship between three tables, Orders, Customers
and Products, in an RDBMS.
The parent
table is the Order
table. Foreign keys ProductID and CustomerID
relate the Order table with the Product
and Customer child
tables.
With foreign keys in the Order table,
developers can retrieve customer and product information from
the other two tables.
The next few pages demonstrate how to Create, Read, Update and Delete (CRUD) tables, rows, columns and cells, with MySQL in an RDBMS. We'll also cover how to create and use stored procedures, functions and views.
EER Diagram
Diagram Prepared with MySQL Workbench 8.0.
Primary Key
The primary key symbol looks like a key in the EER diagram.
A primary key is a unique identifier for a record in a table.
The following MySQL statement creates a table
named Products with a primary key
named ProductID.
CREATE TABLE Products ( ProductID int NOT NULL, PRIMARY KEY (ProductID), );
One Or Many
The horizontal line with three connecting lines
represents a one or many
relationship.
In this diagram the Orders
table may have one or many Products.
The orders table may have
one or many Customers.
One & Only One
The double horizontal line with one vertical connecting
line represents one and only one
.
Both the Customers table
and the Products table connect to the
Orders table with the
one and only one
, symbol.
One Order: One Customer
For each order you can have only one customer. That makes perfect sense. In a common order system you wouldn't want to bill more than one customer for one order.
However in medical services order systems you might bill insurance first, then bill the customer with the remainder.
One Order: One Product
For each order, you can have only one product. The order might include multiples of that one product, but it won't include different products.
A more realistic store order system would allow one customer and one order with more than one product per order. That would allow more efficient processing.
Naming Conventions
This series prepends an F before function names, an SP before stored procedure names, a V before views and a T before trigger names. It's usually easier to locate and remember types when they're prepended with a letter or two that indicate their type.
MySQL common standards usually use upper case letters for keywords such as SELECT, WHERE, JOIN, CREATE, and MySQL built in functions such as TRIM(). It's just a common practice, but MySQL statements work with upper or lower case keywords.
Strings
My understanding is that upper and lower case
letters only apply, in a technical sense,
to alphabetic characters, where comparisons can be case-sensitive.
For example SELECT STRCMP("ABCD", "abcd");
returns zero, which means the first and second strings match.
However SELECT BINARY STRCMP("abcd", "ABCD");
returns 0x30. The strings don't match
when the binary value for each letter is compared.
Variables
I used upper and lower case letters to name
tables, functions, triggers, stored procedures, parameters and views,
however, in these cases, MySQL disregards case,
unless the developer specifically applies COLLATE modifiers.
MySQL standards usually use an underscore to separate
such words, yet this series uses uppercase letters to
separate words.
In the future instead of naming
functions such as SPInsertOrder(),
I'll probably use names such as sp_insert_order().
You can call the stored procedure, SPInsertOrder();
with CALL SPInsertOrder(); or call spinsertorder();.
Therefore use of all lower case letters makes more sense, especially
if an upgrade enforces case-sensitivity later. It's a good habit
to name variables the same way you use them. Developers will have a habit
of applying the same case, across the board.
Summary
A Relational Database Management System (RDBMS)
includes tables which relate to each other
with primary and foreign keys.
The Enhanced Entity Relationship (EER) diagram, above, represents
the relationship between three tables, Orders, Customers
and Products, in an RDBMS.
The next few pages demonstrate how to Create, Read, Update and Delete (CRUD) tables, rows, columns and cells, with MySQL in an RDBMS. We'll also cover how to create and use stored procedures, functions and views.
MySQL for the Web
MySQL has many uses in Web development including storing records for e-commerce, product inventory, logging files, blog posts, online portals (logins), client-server models, record and image display for Web front stores.
Client-Server
On the Web, a Web page might act as a client. Users send requests to middleware, through the client, often with the click of a button, input of login text or selection of a menu item. Middleware then retrieves information from a database, often with Web scripting languages.
Web Scripting Middleware
Often Web scripting languages, such as Python and PHP, provide the ability to create, read, update and delete, MySQL records. Scripting languages can determine if login passwords were correct, retrieve, then display lists and images of inventory, add, update or delete orders, and process other complex information.
What is MySQL?
MySQL is a relational database management system (RDBMS).
SQL
,
abbreviates the terms, Structured Query Language.
Relational databases prepare data into sets of one or more tables,
where tables may relate to each other with primary and foreign keys.
The organization and ability to coordinate between tables
allows for structure.
