Category: MySQL

SQL and 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

    Model inspired by Jukić et al.

    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.

  • 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.

  • SQL count()

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

    PhpMyAdmin: the most popular caske
    PhpMyAdmin: the most popular caske
    -- 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
    
  • 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.

    Many to many and one to many
    Many to many and one to many
    The ER-Diagram transformed to 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.

  • 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

  • Litteraturlisten

    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.

  • CRUD eksperimenter

    Dagens arbejde består i at få phpscaffold til at generere en kode, der kan bruges af WordPress. Jeg tager udgangspunkt i en relativt enkel tabel.

    1. Generere “stillads”.
    2. Omdanne “stilladset” til en WP plugin i Dashboard.
    ER-golden
    ER-diagram for webkanban.

    Phpscaffold skaber fire CRUD-filer og forudsætter import af en databasekonfigurationsfil.

    Phpscaffold eksempel
    Phpscaffold eksempel
  • Hul igennem fra MySQL til Google Chart Tools

    Hul igennem fra databasen til Googles Graph API
    Hul igennem fra databasen til Googles Graph API

    Så lykkedes det at få hul igennem fra WP MySQL databasen (via klassen $wpdb)  til Googles API.Grafen på billedet herover er lavet med (tilfældige) peakflow-værdier fra min database. Tallene fra databasen er visualiseret i form af en graf.

    Grafen er udviklet via Googles Wizard. Efter at have defineret, hvordan grafen skulle se ud kunne man kopiere en img tag med en meget lang URL.

    Googles URL blev delt op i tre bidder. Første del er de dele af URLen som ligger før tallene fra databasen. Så kommer værdier fra databasen (der er et imploderet array). Til sidst følger den sidste del af Google URLen. Der er selvfølgelig grænser for hvor lang en “GET” streng kan være; men ind til videre er det ikke et problem.

    Brugeren skal være logget på for at kunne se resultaterne; men en demoversion lader sig vel nok fremstille på et tidspunkt. Først ville jeg splitte sagerne op i to funktioner; men det fungerede ikke efter hensigten. Så besluttede jeg at samle hele koden i én funktion i min class – og det virkede så.

    Nu virker den tekniske side af mit plugin, for de øvrige værdier kan findes ved at variere SQL sætningerne og graferne; men den grundlæggende kode er meget ens. Noget helt andet er naturligvis en usabilityanalyse – og designet…

    Der bør nok være en demoside, der viser The Asthma Diary i funktion med en demobruger a la billedet her.

     

Enable Notifications OK No thanks

We use cookies - more information

Multimusen.dk will set a few cookies from Doubleclick, Google and the Social Media plugins they ay set some cookies. Some of my pages use APIs - such as YouTube, LinkedIn, Google Fonts, Google Maps, Mapbox, Spotify, Jetpack, Twitter, Facebook &c.. Such plugins may set the odd cookie.

Close