Database

Relational DBMS: Oracle, MySQL (download), SQLite

Key-value Stores: Redis, Memcached

Document stores: MongoDB

Graph: Neo4j

Wide column stores: Cassandra, HBase

1. Design and Modeling (a.k.a Data Definition)

1.1 Schema 1

A database schema of a database system is its structure described in a formal language supported by the database management system (DBMS) and refers to the organization of data as a blueprint of how a database is constructed (divided into database tables in the case of Relational Databases).

The formal definition of database schema is a set of formulas (sentences) called integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema. All constraints are expressible in the same language. A database can be considered a structure in realization of the database language. The states of a created conceptual schema are transformed into an explicit mapping, the database schema. This describes how real world entities are modeled in the database.

1.1.1 Type 2 3

In computer science and computer programming, a data type or simply type is a classification identifying one of various types of data, such as real, integer or Boolean, that determines the possible values for that type; the operations that can be done on values of that type; the meaning of the data; and the way values of that type can be stored.

TEXT, INT, ENUM, TIMESTAMP

1.1.2 Cardinality (a.k.a Relationship) 4

Foreign key, Primary key

1.2 Indexing

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

Why Indexing is important?

Indexing in MySQL 5

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

CREATE INDEX NameIndex
ON Employee (name)

SELECT * FROM Employee WHERE name = 'Ashish'

2. Data Manipulation

Create – Read – Update – Delete 6

  • Create or add new entries
  • Read, retrieve, search, or view existing entries
  • Update or edit existing entries
  • Delete/deactivate existing entries
/* create */
CREATE TABLE Guests (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  email VARCHAR(50),
  reg_date TIMESTAMP
)
/* create (insert) */
INSERT INTO Guests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')
/* read */
SELECT * FROM Guests WHERE id=1
/* update */
UPDATE Guests SET lastname='Doe' WHERE id=1
/* delete */
DELETE FROM Guests WHERE id=1

3. Data Retrieve & Transaction

3.1 Data Retrieve

SELECT, WHERE, FROM, LIMIT, JOIN, GROUP BY, HAVING

Get user id, user name and number of post of this user

SELECT user.id, user.name, COUNT(post.*) AS posts 
FROM user 
  LEFT OUTER JOIN post ON post.owner_id=user.id 
GROUP BY user.id

Select user who only order one time.

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

Calculate the longest period (in days) that the company has gone without a hiring or firing anyone.

SELECT x.date, MIN(y.date) y_date, DATEDIFF(MIN(y.date),x.date) days
FROM (
  SELECT hiredate date FROM employees
  UNION 
  SELECT terminationdate FROM employees
) x
JOIN (
  SELECT hiredate date FROM employees
  UNION 
  SELECT terminationdate FROM employees
  UNION
  SELECT CURDATE()) y
ON y.date > x.date
GROUP BY x.date
ORDER BY days DESC LIMIT 1;

3.2 Transaction

A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in database.

Example: Transfer 900$ from Account Bob to Alice 7

start transaction
select balance from Account where Account_Number='Bob';
select balance from Account where Account_Number='Alice';
update Account set balance=balance-900 here Account_Number='Bob' ;
update Account set balance=balance+900 here Account_Number='Alice' ;
commit; //if all sql queries succed
rollback; //if any of Sql queries failed or error

ACID Properties 8

In computer science, ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.

4. Backup and Restore 9

Sometimes it is desired to bring a database back to a previous state (for many reasons, e.g., cases when the database is found corrupted due to a software error, or if it has been updated with erroneous data). To achieve this a backup operation is done occasionally or continuously, where each desired database state (i.e., the values of its data and their embedding in database’s data structures) is kept within dedicated backup files (many techniques exist to do this effectively). When this state is needed, i.e., when it is decided by a database administrator to bring the database back to this state (e.g., by specifying this state by a desired point in time when the database was in this state), these files are utilized to restore that state.

5. Migration 10

In software engineering, schema migration (also database migration, database change management) refers to the management of incremental, reversible changes to relational database schemas. A schema migration is performed on a database whenever it is necessary to update or revert that database’s schema to some newer or older version.

Example: Android Migration by droid-migrate 11

droid-migrate init -d my_database
droid-migrate generate up
droid-migrate generate down

Example: Database Seeding with Laravel 12

php artisan migrate:make seed_roles_table
php artisan migrate:make seed_users_table  
php artisan migrate:reset
php artisan db:seed  

6. Active record pattern | Object-Relational Mapping (ORM) 13

Object-relational mapping in computer science is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language. There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to create their own ORM tools.

Example

PHP 14

$employee = new Employee();
$employee->setName("Joe");
$employee->save();

Android 15

public class User {

    @DatabaseField(id = true) 
    String username;
    @DatabaseField
    String password;
    @DatabaseField
    String email;
    @DatabaseField
    String alias;

    public User() {}
}

Implementations

Tips For A Successful App

What Makes a Good App Good? 1

  1. Tell Bob’s Story Bob has his own story. Can your app help your make it?

  2. Should Be Easy to Use — Good apps are intuitive and require little or no documentation. The user just fires up the app and they’re off and running.

  3. Provide True Mobility — Apps must have the ability to run in offline mode, then sync with data center systems when connected.

  4. Utilization of Mobile Device Capabilities — Good apps will utilize mobile device capabilities. For example, Meeting Mapper utilizes the iPad camera for capturing pictures relating to meetings.

  5. Fun to Use — One of the best ways to ensure people continue to use your app is to make it fun to work with. Then, even if you’re using it for a business project, the process is an enjoyable one which you’ll share with others.

  6. Design, Design, Design — As much as you can, try to make your app look good. Also, the different visual styles of Windows, Android and Apple need to be considered as much as possible. You can’t copy the design, but it’s much more pleasing if the app fits each device’s style.

  7. Bring People Together — Creating an app that provides for creative collaboration is creating real business value. The ability to easily share, connect, post, recommend, like, follow, get feedback and bookmark is part of today’s social media world.

  8. Provide Real Time Insight — With the increased competition the internet has brought about, giving people the tools to provide up-to-date and relevant information is essential.

  9. Consider the User — Nothing’s worse than a sales app designed by someone who knows nothing about sales. If you don’t fully understand the needs of your users, find someone who does. Four years of intensive research revealed that professionals were looking for an easy to learn, fast and easy to use mobile-based reporting system. The bottom line desire was improved sales and increased earnings. The intensively high failure rates of apps and software were nearly always due to lack of forethought. If you don’t have time to do it right the first time, you might not get a chance to do it right ever.

  10. Intuitive Functionality — Once you understand the needs of your users, you have to build in functions that almost think for the user. Never release an app before you’ve done your due diligence. We took a long time and put in many months into research and testing before we released our app.

MVPC

MVC 1

Model–view–controller (MVC) is a software architectural pattern for implementing user interfaces. It divides a given software application into three interconnected parts, so as to separate internal representations of information from the ways that information is presented to or accepted from the user.

MVP 2

Model–view–presenter (MVP) is a derivation of the model–view–controller (MVC) architectural pattern, and is used mostly for building user interfaces.

In MVP the presenter assumes the functionality of the “middle-man”. In MVP, all presentation logic is pushed to the presenter.

Python

Python is a widely used general-purpose, high-level programming language. Its design philosophy emphasizes code readability, and its syntax allows programmers to express concepts in fewer lines of code than would be possible in languages such as C++ or Java. The language provides constructs intended to enable clear programs on both a small and large scale. 1

Tools


Build Tool

Test

Logging
Unittest logging

Open Source Project

nltk, scikit-learn