Data Types Overview

• Fixed Set of Named Data Elements (Structured Data)

  • Consists of a defined number of elements with specific names and types.

  • Each group has the same number of elements with identical names and types.

• Semi-Structured Data

  • Similar to structured data but allows for variation in the number and types of elements.

  • Types are not explicitly defined; elements can be inferred.

  • Example: Temperatures (e.g., 98.6) is inferred from the context.

• Unstructured Data

  • Data stored as elements within a continuous string without a predefined structure.

  • Often represented in flexible formats like XML or JSON.

XML (eXtensible Markup Language)

• XML Structure

  • Uses tags instead of columns:

    • Start tag: <TagName>

    • End tag: </TagName>

  • Example: Department is an XML element with data Accounting.

• Document Declaration

  • Specifies document processing info (XML version, character encoding).

• Advantages of XML Tags

  • Readable tag names enhance data understanding.

  • Flexible: Tags can be easily added or removed without impacting the document structure.

  • Hierarchical organization through nesting of elements.

• Comparison with Relational Data

  • Hierarchical data in relational databases requires multiple tables and foreign keys, while XML naturally represents hierarchy through nested structure.

JSON (JavaScript Object Notation)

• JSON Structure

  • More compact than XML; consists of key-value pairs.

  • Example format:

    {  
      "Department": "Accounting"  
    }  

  • Jackson formats:

    • Strings: Encased in double quotes

    • Decimal: digits with a decimal point

    • Booleans: true or false

    • Null: null

    • Arrays: Enclosed in brackets []

    • Objects: Enclosed in braces {}

• JSON Example

  • Example of a Customer JSON object:

    {  
      "Customer": {  
        "Name": "Maria Rodriguez",  
        "Vehicle": [  
          {"Make": "Ford", "Model": "F-150", "Year": 2008},  
          {"Make": "Toyota", "Model": "Camry", "Year": 2019}  
        ],  
        "Budget": null,  
        "PreviousCustomer": true,  
        "FamilyMember": ["Jose", "Felicia", "Isabella"],  
        "Notes": "Shopping for a new sports car. Interested in leasing."  
      }  
    }  

Working with JSON and MySQL

• Creating and Inserting JSON in MySQL

  • Create Table

    CREATE TABLE Library (  
      Code VARCHAR(10),  
      Book JSON  
    );  

  • Insert JSON Values:

    INSERT INTO Library  
    VALUES (103, JSON_OBJECT('Title', 'War and Peace')),  
            (192, JSON_OBJECT ('Title', 'Tom Sawyer', 'Author', 'Mark Twain', 'Pages', 145));  

• Querying JSON

  • Example: Retrieving data by extracting JSON elements:

    SELECT JSON_EXTRACT(Book, '$.Title') AS Title  
    FROM Library  
    WHERE JSON_EXTRACT(Book, '$.Pages') > 100;  

• JSON Functions and Type Handling in MySQL

  • Functions for XML and JSON are often similar but vary by database.

  • JSON can be stored as VARCHAR, but it's better stored using JSON type for optimized performance.

  • Syntax validation occurs upon insertion of JSON data into the database.

  • Relational operations can be performed on JSON fields in MySQL, allowing for joins but with performance considerations due to potential document size.