Home › Skill Set Definitions

Skill Set Name: Basic Skills in RDB

Topics Level Sub topics Skill Category No Code Share comment
Basic database theory I

 
 

  • The advantages of databases and the status of Database Management Systems (DBMSs)
    • Concurrent access
    • Integrity
  • Basic concepts of using databases
    • OSS implementation
    • Outline of database structure
 8-1-1-I
Fundamental knowledge of RDBMSs I
  • Relational model as a data structure model.
  • Relational Database Management System (RDBMS) based on a relational model.
  • Outline database language SQL.
  • DDL
  • DML
 8-1-2-I
Fundamental concepts of transactions I
  • The concept and features of transaction indispensable to database design.
    •  ACID
    •  Concurrent Execution
    •  Recovery
  • Concept and implementation of exclusive control.
  • Lock
    •  Shared lock
    •  Exclusive lock
    •  Deadlock
    •  2-phase lock
  • Isolation level
    • Dirty reads
    • Non-repeatable reads
    • Phantom reads
    • READ UNCOMMITTED
    • READ COMMITTED
    • REPEATABLE READ
    • SERIALIZABLE
 8-1-3-I
Database components I
  • Definition of the components of a relational database
    • Table
    • Attribute
    • Tuple
  • Relational schemas
    • Introduction relational schemas
    • Main keys to identify only one record
    • Main keys having plural attributes
    • External keys.
  • Set operations
    • Union
    • Difference
    • Intersection
    • Cartesian product
  • Relational algebraic operations
    • Projection
    • Join
    • Selection (Restriction)
    • Division
  • Ways to maintain consistency among components of a relational database, as well as conditions necessary to maintain consistency.
 8-1-4-I
DOA I
  • Outline of DOA (Data-Oriented Approach)
    • Difference from the conventional process design approaches.
  • Data models
    • Conceptual data model
    • Logical data model
    • Physical data model
  • Three-layer schemas.
    • Conceptual schema
    • External schema
    • Internal schema
 8-1-5-I
Basic database design theory I
  • Methods and procedures for database design
    • Business analysis
    • Conceptual design
    • Physical design
    • Database design
  • Two analytic techniques
    • The top-down approach
    • The bottom-up approach.
 8-1-6-I
ER models I
  • Outline of ER model (Entity-Relational model)
  • Data representation methods
    • Entity
    • Relationship
    • Attribute
    • Cardinality
  • ERD (Entity Relational Diagram)
    • Components
 8-1-7-I
Normalization I
  • Outline of normalization
    • Basic concepts
    • Functional dependency
    • Types of normal forms.
  • Normalization procedures and methods
 8-1-8-I
Database indexes II
  • Concept of index
  • Principle of index
    • Algorisms of retrieval
  • Implementation of index
    • Physical structure
    • Performance
    • Recovery characteristics of index.
  • Features, advantages and disadvantages with various types of index
    • Binary tree (B tree)
    • Index-organized tables
    • Bitmap index
    • Reverse key index
    • Hash cluster (hash index)
 8-1-9-II
The database physical structure II
  • Physical structure of databases required in the implementation of relational databases.
  • Purpose of physical design of databases, and procedures required in the physical design
    • Tradeoff to note in the design
    • How to locate data, index and log
    • Securing database storage area.
 8-1-10-II
Database access in SQL II
  • SQL (Structured Query Language)
    • Structure
    • Principle
    • Operation specifications
    • Types
    • Features.
  • Creation of tables
    • Data type specification
    • Handling of NULL
    • Specifying procedures of primary key and foreign key.
  • Data retrieval and query processing
    • SELECT statements
      • Processing of data
        • Expressions
        • Numerical values
        • Character strings
        • Dates
      • Operations using WHERE clauses
      • Set functions.
  • Joins of tables
    • Outer joins
    • Self joins
    • Sub query
  • Various table handling
    • Complicated retrieval/inquiry procedures
    • Updating/deleting of tables
 8-1-11-II
Typical open source RDBMS products II
  • Following typical open source RDBMSs’s history of developments, main functions,features, and their licenses
    • PostgreSQL
    • Firebird
    • MySQL
    • SQLite
  • Comparison with commercial RDBMS
  • Reason of using open source RDBMS
  • Portability
  • Difference among RDBMSs
    • Backup (online and offline)
    • Specialized features
      • Vacuum
 8-1-12-II
Database design/creation practice II
  • Business analysis
    • Screening out entities
    • Creation of ERD
  • Design procedure of databases
    • Schema
    • Index
    • Distributed databases
  • Physical designs
  • Creation of tables
    • Restriction
    • Data loading
  • Items required in actual DB application developments
    • Procedures for the designs of business forms and screens
  • Performance
    • Data size and query plan
    • Improvement of index design
    • Monitoring of performance
 8-1-13-II