Please enable JavaScript.

Coggle requires JavaScript to display documents.

sql 20/80 - Coggle Diagram

- - - - create a new view
      - rename a view
      - getting view information
      - listing views
      - create an indexed view
  - - - create sp with outout -> keyword = output
      - declare var to receive the output of sp
      - must specifiy output keyword. execute sp
- - - - collected, exported, stored, organized
      - source of datas
        
        sql dbs, oltp, spreadsheets, online forms, sensor gps, and rfid, network and web server logs
    - - source of datas
        
        e-mails, xml and other markup langs, binary executables, tcp/ip packets, zipped files, integration of data
        
        xml, json
        
        define tags, attributes, and store datas
    - - source of datas
        
        web pages, social media feeds, images, videos and audio, documents and pdf, powerpoint, media logs, surveys
  - - - store high volume operational data
      - relational database management system (rdbms)
      - nosql
        
        key-value store
        
        attribute of data and is a unique identifier
        
        simple integers, strings to complex JSON documents
        
        great for session data, user preference, real time recommend, target ads, in-memory data caching
        
        example: redis, memcached, dynamodb
        
        document based
        
        preferred for ecommerce platforms, medical records storage, crm platforms, analytics platforms
        
        big nono if want to run complex queries, or multi-operation transactions
        
        example: mongodb, documentsdb,couchdb, cloudant
        
        column based
        
        saved as continuous disk entry --> high access, easier, and faster
        
        suitable for heavy write, time series data, weather data, iot data
        
        example: cassandra, hbase
        
        graph based
        
        good for working with connected data: social networks, product recommendations, network diagrams, fraud detection, access management
        
        example: neo4j, cosmosdb
    - - use for conducting complex analytics
      - relational and non-relational dbs, dwhsa, dms, dls, big data stores
        
        example
        
        data warehouse
        
        central repositories from multiple sources
        
        data has been cleansed, conformed, categorized
        
        model structured, analysis ready
        
        contains 3 tiers
        
        tier 1 : database server (extracting data from different sources)
        
        tier 2: OLAP server: process + analyze data from tier 1
        
        tier 3: client front-end layer (querying, layering, reporting + analyzing data
        
        datamart
        
        sub-section for dwh, built specifically for particular business, function, community of users.
        
        three type of datamarts
        
        dependent
        
        independent
        
        hybrid
        
        data lake
        
        large amount structured, semi-structured,
        
        can be loaded without defining the structure and schema of data
        
        repository for raw data from source --> transformed based on used case
  - - - data consistency across app
      - master data mngt
      - data sharing between enterprises
      - data migration and consolidation
    - - pre-built connectors and adapters
      - open-source architecture
      - optimization batch processing continuous data streams
      - integrate with big datas
      - data quality governance, compliance and security
      - portability bw on premise and cloud enviroment
  - - - Transform
      - Load
        
        initial loading: populate data in repository
        
        incremental loading: apply updates and modifications periodically
        
        Full refresh
      - Extract
        
        batch processing - large chunks of data moved source to destination
        
        example: blend, stitch
        
        stream processing: pulled real-time from source, transformed in transit and loaded into repository
        
        example: apache samza, apache storm, apache kafka
      - example