Please enable JavaScript.

Coggle requires JavaScript to display documents.

Database (def ((bad design database (insertion anomaly (can't insert…

- - - - sequence of operations in order to perform a single logical unit of work against data base
    - - atomic
        
        ensure that either all the data in the transaction was modified or not at all
      - consistent
        
        ensure that after any transaction is made on the database, the database will still be in a valid or consistent state
        
        ensure consistency
        
        Add primary keys, and foreign keys
        
        Specify data type an attribute and avoid NULL values
        
        Normalize
        
        Add triggers
        
        Create back ups
        
        Add security
        
        Input validation
      - isolated
        
        transactions that haven't been committed yet to remain isolated from other transactions
        
        4 levels
        
        serilizable
        
        repeatable read
        
        read committed
        
        read uncommitted
      - durable
        
        ensure that once a transaction has been committed, the changes will remain in the database
      - ACID model is a way to increase data integrity which refer to the accuracy and consistency of data stored in a database.
    - - insertion anomaly
        
        can't insert data until we know information for the entire row
        
        happen when there are business logic that allow one info field exist and the other are null
        
        ex: if customer_id and order_ird in the same table, customer need to order to have an order_id so that someone can insert that customer_id with that order_id into a row. However, business logic allow to add customer without ordering something.
      - anomaly
        
        problems that can occur in poorly planned, un-normalised databases where all the data is stored in one table
      - delete anomaly
        
        deletion of a row can cause more than one set of facts to be removed.
      - data duplication
      - update anomaly
        
        since same information is recorded in multiple rows, there are multiple updates that need to be made.
        
        if these update are not successfully completed across all rows, then an inconsistency occurs.
      - search and sort issues
  - - - manipulate the database based on the schema that we give from using DDL
      - Select, Insert, Update, Delete
      - Q in SQL
  - - - define the structure of schema of a database
      - DDL commands: Create, Alter, Drop
      - S in SQL
  - - - Any column that qualifies as being a unique key
    - - A set of 2 or more columns used to uniquely identify a row
    - - A set of two or more columns that can uniquely identify a row, but the columns by themselves cannot uniquely identify a row
  - - - ensure the database stays consistent while multiple operations take place
- - - - insertOne
        
        insertOne({"title":"Rocky", "year":"1976"})
      - insertMany
        
        ordered
        
        default option
        
        when hit err, it will stop inserting
        
        unordered
        
        by adding ,{ ordered: false })
        
        when hit err, it will skip
        
        insertMany( [ { "imdb":"tt0081"},{"imdb":"tt21551"} ] )
      - _id
        
        ObjectId
        
        12 byte Hex String --------------
        
        Date - - - - |
        
        Mac address | - - - |
        
        PD | - - |
        
        Counter | - - - |
      - same _id insertion
        
        duplicate err
      - same content, different _id insertion
        
        passed
    - - exact
        
        one key
        
        one key, value is object, object.items == 10
        
        WRONG: find({tomato.items: 10})
        
        find({"tomato.items": 10})
        
        one key, value is array, array contain "Ryan"
        
        find({actors: "Ryan"})
        
        one key, value is array, matching array
        
        find({actors: ["Ryan","Lee"]})
        
        order of the array have to match
        
        find(tomato: 10)
        
        multiple key
        
        find({student_id: 4, type: "homework"})
      - query selectors
        
        comparation
        
        $eq
        
        " equal "
        
        $gt
        
        " > "
        
        $gte
        
        " >= "
        
        $lt
        
        " < "
        
        $lte
        
        " <= "
        
        $ne
        
        " not equal "
        
        db.test.find({ runtime: { $gte: 90, $lte: 120 } }
        
        $in
        
        " match any value in an array"
        
        find( {qty: { $in : [5, 15] } } )
        
        $nin
        
        " match non of values in array"
        
        element
        
        $exists
        
        find( { qty: {$exists : true } } )
        
        $type
        
        look up this table for type https://docs.mongodb.com/manual/reference/operator/query/type/#op._S_type
        
        find( {"zipCode": { $type : "string" } } )
        
        logical
        
        $or
        
        find( { $or : [ {quantity: { $lt: 20 } }, {price : 10 } ] } )
        
        $and
        
        $nor
        
        $not
        
        find( { price : { $not : {$gt : 1.99 } } } )
        
        regex operator
        
        $regex
        
        array
        
        $size
        
        $all
      - project - limit what gonna get display
        
        _id always show unless { _id : 0 }
        
        find({student_id: 4}, {type: 1})
        
        add , {type :1}) mean only show type (and _id as default)
      - cursor - iterate through database
        
        create cursor
        
        var c = db.test.find();
        
        create a function to traverse database with cursor
        
        var doc = function () { return c.hasNext() ? c.next() : null;}
        
        count obj left
        
        c.objsLeftInBatch();
    - - updateOne
        
        db.collection.updateOne( <filter> , <update> , <options> )
        
        db.test.updateOne( { item: "paper" }, { $set : { "size":"cm", status: "p" } , $currentDate : {lastModified: true } } )
      - updateMany
        
        db.collection.updateMany( <filter> , <update> , <options> )
        
        db.test.updateMany( { "qty" : { $lt : 50 } }, { $set : { "size":"cm", status: "p" } , $currentDate : {lastModified: true } } )
- - - - DROP TABLE IF EXISTS
      - CREATE TABLE
        
        ( filed type null? key? default? extra)
      - DESCRIBE
    - - CREATE DATABASE
      - DROP DATABLE IF EXISTS
      - SHOW DATABASES
    - - INSERT INTO table_name
        
        Assign value in certain order (Optional)
        
        ( firstname, age, title )
        
        VALUES ('Trung', 30, 'CTO')
    - - SELECT
        
        SELECT DISTINCT
        
        Only show unique rows
        
        *
        
        select all fields
        
        field_name
        
        select that field
      - FROM
        
        table_name
        
        from a table
      - WHERE (condition)
        
        compare operator
        
        greater than >
        
        greater than or equal >=
        
        less than <
        
        less than or equal <=
        
        not equal to <>
        
        opposite NOT
        
        equal =
        
        operator
        
        LIKE
        
        name contain 'as' name LIKE '%as%'
        
        name start with 'as' . name LIKE 'as%'
        
        name end with 'as' name LIKE '%as'
        
        AND
        
        OR
        
        BETWEEN
        
        Ex: age BETWEEN 10 AND 20;
        
        CONCAT
        
        concat 2 value of different field
        
        SELECT CONCAT (name,' ',age)
        
        NUMERIC
        
        SELECT name, age + 5
        
        NOT
        
        IN
        
        WHERE first_nam IN ('Jon','Shan','Laura')
        
        IS
        
        = NULL are not a valid statement
        
        IS NULL
        
        IS NOT
        
        IS NOT NULL
    - - UPDATE
        
        table_name
        
        SET
        
        email_id = 5
        
        WHERE
        
        personal_id = 1
        
        email_address e SET e.email_id = 5 WHERE e.personal_id = 1;
    - - OUTER JOIN
      - INNER JOIN
- - - - create indexes for foreign keys and for other columns that are used frequently for queries that apply logical criteria to data
      - If you find that update speed is severely affected, you may choose at a later time to delete some of the indexes you created.
  - - - creating two or more tables with exactly the same structures, and splitting rows between those table.
      - Ex: split order entity to unfilled order table and filled order table. When an open order shipped, the program delete rows from unfilled order and insert the into filled order
      - drawback: when controller need to access all the orders. A query include data from 2 table will be worse than query of either
    - - creating two or more tables with selected solumns and all rows of a table
      - Benefit: the rows in the smaller table will be physically closer together, the smaller table will take up fewer disk pages and this support faster retrieval
      - drawback: query that require data from both tables must join tables and this is a relatively slow operation
- - - - The relation is in second normal form
      - Each column are dependent ONLY on the primary key and no other columns in the table
    - - The relation is in first normal form
      - All non-key attributes are functionally dependent on the entire primary key. (determinant)
    - - There are no repeating groups like array in a cell
      - Each cell to be Singled valued
      - Entries in a column are same type
      - Row uniquely identified by primary key
    - - Free the database of modification anomalies where you could update email address in one row, but not update in another and leave it inconsistent within database
      - Minimize redesign when extending
      - Avoid bias toward any particular access pattern ( only matter in relational database, in mongodb if you not bias, you are bad on all of them )
    - - No multivalued dependencies
  - - - Natural Key
        
        existing data attributes that are unique to the business concept
      - Surrogate Key
        
        a key that has no business meaning
- - - - external entity
        
        outside system that send or receives data, communicating with the system being diagrammed
      - process
        
        any process that change the data, sort or receive data, producing an output
      - data store
        
        database table
      - data flow
        
        the route that data takes between the external entities, processes and data stores
  - - - indicate the source
      - what data go between the system and the source
    - - what data stored into where
      - what operation you need to do in the business
      - how the operation read, retrieve data from data base