AWS Database services (Intro and how to create a DB Instance)

Quick introduction to AWS Database services

AWS gives option for the following Database services:

Relational Database service:
MySQL, Aurora DB, PostGreSQL, Maria DB, Oracle and Microsoft SQL Server

DynamoDB
Fast, Fully Managed, NOSQL Database Service.
Link to documentation

Elasti-cache
In memory data store and cache service.
Link to documentation

Neptune
High performance graph database engine optimized for storing billions of relationships and querying the graph.
Link to documentation


How to create a DB instance and connect to it?

1. Login to AWS Console and services > RDS
2. Create a database. (I chose MySQL Free Trial)
3. Put in an instance name and username/password.
4. Once the database is created and available, one can connect to it using AWS Workbench. Link to documentation
5. Connecting to MYSQL DB Instance documentation

Reset sa users password in SQL Server Express edition

• Click on Start > Programs > SQL Server Management Studio Express
• If SQL Server Management Studio Express is not installed, install from the following URL: http://www.microsoft.com/en-ca/download/details.aspx?id=8961
• Login as Windows admin
• Click on security
• Click on logins
• Right click on sa
• Change the password and click ok

B Tree v/s Bit Map Indexes

BitMap v/s B-Tree Index?

If your table has low cardinality value (e.g. Gender column, boolean data), opt for a Bit Map Index.
If your table has high cardinality value, opt for a B-tree Index.

B Tree index is very useful in OLTP systems.
Bit Map works best in Data wareehousing systems.

In Bit Map structures, a two-dimensional array is created with one column for every row in the table being indexed.
Each column represents a distinct value within the bitmapped index.
Thus for low cardinality columns, they become very compact and fast to scan.

In B-tree, records are stored in locations called leaves. The starting point is called the root.
Its a tree where max number of children per node is called order.
Depth is the # of operations needed to reach the desired leaf

Consider below an example of a B-Tree with  

Order   = 2
Depth   = 3
Leaves = 4

                           [Root]
                                |
                                | 
                                |  
                                |  
                               / \  {Node]
                              /   \
                             /     \
                            /\     /\
                           /  \   /  \
                          /    \ /    \
                      [Leaves]