Category: MySQL

SQL and MySQL

Multimedia Design MySQL

ER-Diagram Symbols

Above you see the atomic building blocks of the ER-Diagram:

Entity
Attribute
Relation
Carninalities (Crow’s feet notation) one, zero-or-one, zero-or-many, one-or-many, many.

From Requrements to Deployment

ER-Diagram

This ER-Diagram is inspired by some of the tables in his sample database

Database Implementation

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.

Normalizing

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.

Multimedia Design MySQL

Video: What is (a) Database & SQL?

Post author By Per Thykjaer Jensen
Post date 15. July 2017
No Comments on Video: What is (a) Database & SQL?

Multimedia Design MySQL

Adminer – a lightweight alternative to PhpMyAdmin

Post author By Per Thykjaer Jensen
Post date 30. June 2016
No Comments on Adminer – a lightweight alternative to PhpMyAdmin

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.

Tags GUI, MySQL, PHP

MySQL sql

SQL count(): The Most Popular Cake

Post author By Per Thykjaer Jensen
Post date 20. May 2016
No Comments on SQL count(): The Most Popular Cake

So how do yo find the most popular cake in the Shire Bakery? Here’s an idea.

-- Show the best cookie
-- See: Ben Forta Chap. 16
SELECT COUNT( cakes.cake ) AS 'popular', cakes.cake, costumers.who
FROM cakes, costumers, likes
-- sort out the cardinalities (relations)
WHERE cakes.cakes_id = likes.cakes_id
AND costumers.costumers_id = likes.costumers_id

-- group by the cake 
group by cakes.cake

-- and sort (most popular and downwards)
order by popular desc

Tags MySQL, SQL

Multimedia Design MySQL

SQL: Many-to-Many and One-to-Many

Post author By Per Thykjaer Jensen
Post date 19. May 2016
No Comments on SQL: Many-to-Many and One-to-Many

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.

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.

One-to-Many

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.

Tags MySQL, SQL

Multimedia Design MySQL

ER Diagram Solutions

Cardinalities

How do you solve cardinalities such as:

One to many.
Many to many.
Multivalue attributes.

Here are some suggestions.

A student drawing en ER-Diagram

Two students explaining ER-Ds

Kanban MySQL

Litteraturlisten

Post author By Per Thykjaer Jensen
Post date 10. April 2013

Masterprojektets litteraturliste skal naturligvis skabes via en MySQL database. Derfor var det relativt enkelt at udvikle en klasse i WordPress, der automatisk formatterer litteraturlisten. Sådan ser klassen ud:

class littList {

public function BooksAnnotated() {

	global $wpdb; // wp db class

	$result = $wpdb->get_results("SELECT * FROM `Litteraturliste` ORDER BY `Author`") or trigger_error(mysql_error()); 

	foreach($result as $row){
		echo "<p><strong>" 
		. $row->Author 
		. ":</strong> &quot;" 
		. $row->Title
		. "&quot;, "
		. $row->Where
		. " ("
		. $row->Year
		. ") <br>    "
		. $row->Note																																				
		.  "</p> ";
	}

} // end littList

Databasens grundlæggende struktur er nem at rekonstruere 😉

Resultatet ser sådan ud:

Aristoteles: “Poetik”, Hans Reitzels Forlag (1958)
Aristoteles (384 – 322 fvt.) – værket er udgangspunkt for dramatisk fortælleteknik. Spændingskurven i berettermodellen er inspireret af Aristoteles. Kanban etablerer en fortælling.

Baym, Nancy: “Personal Connections In The Digital Age”, Polity (2010)
p. 6: Seven key concepts – måder at knytte sociale kontakter online.

Benson, Jim; Barry, Tonianne DeMaria: “Personal Kanban – Mapping Work | Navigating Life”, Modus Cooperandi Press (2011)
En praksisnær manual baseret på personlige erfaringer.

Bouvin, Niels Oluf & Hansen, Allan: “Kompendium: Hypermedier og Web”, Computer Science, Aarhus Universitet (2011)
p. 95 Berners-Lee, Tim: “The Semantic Web” (2001) p. 214 Nardi et al.: “Why we Blog” (2004) p. 229 O’Reilly, Tim: “What is Web 2.0” (2005) p. 54 Halaz, Frank G.: “Reflections on Notecards: Seven Issues for the Next Generation of Hypermedia Systems” (1988)

Burgess, Jean; Green, Joshua: “Youtube”, polity (2009)
p. 58 Youtube’s social network

(… etc…)

Uberto Eco råder forskere, som skal i gang med et større projekt, til at lave en samling af kartotekskort, der igen ordnes i skuffer eller kasser. Databasen er et mere nutidigt svar på forskerens udfordring.