Database Intro 2
Functions of a DBMS:
  • Design: ER Modeling
    • It is a part of information system which is an orchestrated dynamic operations of an organization, it includes collection, storing and managing information necessary for business operations.
    • A balance between advancement and obsolescence is crucial, and a database must have the power of transformations to accommodate any changes in during business operations. 
      SQL developer provides some design tool design  and create db from scratch.

      ER Modeling In use: Crow's feet represents many-side relations

       
  • Indexing With SQL you may create and index on a very large table to retrieve rows quickly, using ROWID. As indexes and tables both are synched, DML operations like INSERT, UPDATE, DELETE, slow down processes and may corrupt the table. The primary or Foreign Keys are implicit "indexes", the indexes created with SQL statements are explicit indexes.

    SQL-Views A view is virtual table or a stored query can be created from a single table, multiple tables or from another view.
    SQL Script:
    SQL > CREATE VIEW e_view AS
    SELECT eid, empname, job
    FROM emp1;

     

  • Security: Limiting or regulating users role and privileges, and protecting database from any data corruption or damages.
    • Authorize actions like :Read, Insert, Update, alter or delete
    • Manage code module to enforce security on the procedures and impose constraints to reduce the chances of data corruptions. 
  • Integrity: Check on referential integrity, non-key integrity, matching correct data type, and
  • Concurrency : avoid undesired duplications or redundancies, preventing users from interfering with each other while using the same data sources.
    • Lock: locking table or row to defer other users, when the same is being checked out by another user.
    • Versioning :Keeping track of any revision or upgrade of the DB
    • Resolve conflicting data
  • Backup/Recovery: Backing up the db and keeping transaction log etc.
    • Master Table: Final master table
    • Forward log : Updated record
    • Backward log :Record in the past
    • Transaction : Transactions details like time and the users, affected row etc.