Computer

Relational Database

search

Relational Database, Relational Database Management System, RDBMS

  • See Also
  1. Database
  2. Information Technology
  3. Computer System Infrastructure
  4. Computer Network
  5. Relational Database
  6. Computer Software
  7. Programming Language
  8. Software Development Life Cycle
  • Preparations
  • Relational Database Management System (RDBMS) Examples
  1. IBM DB2
    1. http://en.wikipedia.org/wiki/IBM_DB2
  2. MariaDB
    1. http://en.wikipedia.org/wiki/MariaDB
    2. Community version of MySQL after Oracle purchased MySQL
  3. Microsoft Sql Server (MS-SQL)
    1. http://en.wikipedia.org/wiki/Microsoft_SQL_Server
  4. Oracle Database
    1. http://en.wikipedia.org/wiki/Oracle_Database
  5. Oracle MySQL
    1. http://en.wikipedia.org/wiki/MySQL
  • Background
  • Object Relational Mapping (ORM)
  1. http://en.wikipedia.org/wiki/Object-relational_mapping
  2. Description: Object Relational Mapping (ORM)
    1. Object Oriented Classes in Software map to each table in the database
    2. Each table is mapped to a class
    3. Each table field (attribute or column) has a corresponding property (attribute) in an object class in the software
    4. Each table row may be retrieved as a tuple (typically returned as a dataset collection of multiple rows based on a query)
    5. Classes typically maintain the constraints of the database (e.g. PK and FK relationships)
    6. Classes expose methods to perform CRUD operations on the database
  3. Example: Entity Framework (Microsoft C# or Visual Basic)
    1. http://msdn.microsoft.com/en-us/data/ef.aspx
    2. Entity Framework automatically generates object classes from database tables (with constraints)
    3. Entity Framework can also automatically generate databases from object classes
    4. Queries and database manipulation may be performed through class objects with LINQ expressions (and less often with SQL)
  • Background
  • Structured Query Language (SQL)
  1. Description
    1. Query language with similar but not identical nomenclature across Relational Database Management Systems (RDBMS)
  2. CRUD Operations
    1. INSERT INTO table (col1, col2...) VALUES (val1, val2...)
    2. SELECT * from table WHERE ...
    3. SELECT * from table1 INNER JOIN table2 ON table1.id = table2.id
    4. UPDATE table SET col1=val1, col2=val2 WHERE ...
    5. DELETE from table WHERE ...
  3. Join operations (between table A and table B)
    1. Inner Join
      1. Intersection between tables A and B only if A and B match on the specified criteria (and no nulls)
      2. Equivalent to using WHERE clause
        1. SELECT * from table1, table2 WHERE table1.id = table2.id
      3. Equivalent to using subquery
        1. SELECT * from table1 WHERE table1.id in (SELECT id from table2 WHERE ...)
    2. Left Outer Join
      1. All rows from table A and any matching rows from table B (null or blank if no match)
    3. Right Outer Join
      1. All rows from table B and any matching rows from table A (null or blank if no match)
    4. Full Outer Join
      1. Union between tables A and B, where all rows from both tables are included
      2. If they do not match on the specified criteria, value is null or blank for the missing match
  • References
  1. Desai (2014) Systems, Databases and Networks, AMIA’s CIBRC Online Course
  2. Gennick (2004) SQL Pocket Guide, O'Reilly, Sebastapol, CA