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