Above you see the atomic building blocks of the ER-Diagram:
Carninalities (Crow’s feet notation) one, zero-or-one, zero-or-many, one-or-many, many.
From Requrements to Deployment
This ER-Diagram is inspired by some of the tables in his sample database
Here are Ben Forta’s tables in PhpMyAdmin “design viewW.
During implementation you will create the tables and structure.
Note that you have to design the data as integers, numbers, etc.:
cust_id = int(11)
cust_name = char(50)
The customers table in ER-diagram form.
In the diagram the cardinalities have to be normalized. Here are a few solutions to typical problems.
TIP: From ER-diagram to HTML form
Look at the attributes. Create an input for each field. Then use the INSERT keyword.
PhpMyAdmin is powerfull but the GUI is messy. Adminer is a lightweight alternative. You have to download the files and place them in your folder.
# sudo apt-get isntall adminer
# sudo ln -s /usr/share/adminer/ adminer
The first line will install the files. They are saved in /usr/share/adminer. Therefore we need a symbolic link to the folder, which is made in the second line of code.
Here’s a short tutorial on how to solve some basic SQL cardinalities. First have a look at the ER-diagram for the database and the resulting tables in PhpMyAdmin.
Many to many and one to many
The ER-Diagram transformed to tables in PhpMyAdmin Many-to-Many
Many to many cardinalities may be solved like this:
SELECT cake, who
FROM cakes, costumers, likes
WHERE costumers.costumers_id = likes.costumers_id
AND cakes.cakes_id = likes.cakes_id
Note that there are two where clauses combined via AND.
This cardinality is more easy:
SELECT inventory .* , costumers.who
FROM inventory, costumers
WHERE inventory.costumers_id = costumers.costumers_id
In the “many” table you simply connect via the “one” table’s id.
You can download and try out the samples from my Github Repository
oneToMany_ManyToMany. Read On
These samples are inspired by Ben Forta’s “MySQL Crash Course” Ch. 15 – 16.