Database Management System (DBMS)

Database Management System (DBMS)

1. Data Dictionary

   • file that defines the basic organisation of a database and is a repository of all the information about the basic database design and all objects created (queries, views and reports)
   • it contains
     • list of all files (tables)
     • number of records in each file
     • field length
     • field names/data types
     • field validation (validation checks)
     • keys
     • fields default values
     • relationships
     • access rights

2. Data Integrity

   • DBMS software supports validation
   • validation checks are set up at table design stage and are effectively every time a reference is made to that item of data
   • is straightforward to keep data consistent inside the database (if a record is deleted, those related records must be deleted)

3. Data Security (Data Backup)

   • data backup is a centralised task administered by DBA
   • data backup saves the database to a file on a HDD
   • DBA regularly backups the database
   • backup/database maintenance is designed to
     • improve database performance
       • eliminate deleted records
       • reduces database size
     • distribute the database among multiple files/disks
     • be performed when other users are using the DB
     • create a stable snapshot of DB for archiving purposes
   • access rights to particular tables can be created by DBA

4. Data Security (Security Measures)

   • measures to protect data in DBMS
     • physical measures
       • lock doors
       • lock devices
       • implement secure access methods (biometric scans)
     • data backup
     • disk-mirroring
       • duplicate all of the activity of the database at run-time in another storage media (server)
     • access rights
     • encryption
       • encrypt data in order they are not able to be accessed by unauthorised individuals
     • firewall
       • stop unauthorised access
     • use authorisation methods
     • use anti-malware programs
       • detect/remove virus/keyloggers
       • carry out regular scans
     • record locking
       • close a record to a second user until the first update is completed to prevent simultaneous updates of the same record being lost

5. Database Schema

   • Structure of the database described in a formal language supported by DBMS
   • Levels
     • Structure that represents the logical view of a database, how data is organized and how relations are associated
     • contains a descriptive detail of the database designed by DBAs
     • External
       • individual view of the database which hides DB details from the user
     • Conceptual
       • describes data as seen by applications making use of DBMS
       • hides information about physical storage structures
       • describes data types, entities and relationships
     • Internal
       • Physical/External
         • describes how data will be stored on the physical media (HDD)
       • Logical
         • describes how the relationships will be implemented on the logical structure of the DB

6. Data Storage

   • includes those programs required for physically storing data handled by a DBMS
   • creates complex data structures in a process called Data Storage Management

7. Data Manipulation

   • functions to define the structure of data
     • definition/modification of the record structure, fields size/type, conditions to be satisfied by data in each field

8. Data Recovery/Data Concurrency

   • Data recovery means recover data from the DB after a system failure
   • Data Concurrency means a simultaneous access of records by multiple users of the DB

9. Query Optimizer

   • program that evaluates different implementations of a query and chooses the best (uses SQL)

10. Multi-user Access Control

    • DBMS uses sophisticated algorithms to ensure that multiple users can access concurrently without compromising the integrity of the DB

11. Database Access Languages and Application Program Interfaces (APIs)

    • SQL: standarized query language for requesting information from a database
    • API: collection of communication protocols and subroutines used by various programs to communicate between them

12. Database Software Features

    • automatic validation checks
    • add/delete fields/records
    • perform queries
    • import/export data
    • create customised reports/forms/data entry/menu screens
    • link files together using common fields (relationships)

13. Types of users

    • Database Administrator (DBA)
      • manages the complete database system
      • takes care of security, data availability
      • manages license keys, user account and access
    • Application Programmer/Software Developer
      • develops/design parts of the DBMS
    • End-user
      • the person who uses the database (store, retrieve, manipulate data)

14. Software Tools

    • Open Database Connectivity (ODBC)
      • Open standard API to make it possible to access any data from any application
    • Object Linking and Embedding Database (OLE DB)
      • API developed by MS to access any data in an uniform manner
    • Web browsers
      • create/access data in the DB
    • Command Line Interface (CLI)
      • used by DBA
    • Query Processor
      • used by DB designer to access queries

Imagens e arquivos do mapa