Here’s what is in progress and what has been covered until now:

• A Tiny Intro to Database Systems
• A Tiny Intro to the Android Activity Lifecycle
• A Tiny Intro to GitHub

Thank you for reading. If any suggestions, feel free to send me a tweet @dandancrisan

The following is a tutorial on the Android Activity Lifecycle, a concept that occurs pretty often during mobile dev interviews

There is also an app to demo the topic, simple, a decent way to go through the cycles and process. It is one buck for the support but also open sourced on Github for people who like to learn with examples and more logs (it can be easily tested on Android Studio).

Before going trough the lifecycle process and explaining the main parts, let’s see what is an Android Activity. An activity is a view, a window with related design and interactions. The following is a single activity that has 2 buttons and some text describing in which state of the Activity Lifecycle the app is.

This activity, as any Android Activity, goes through the following states all along its lifecycle:

1) onCreate()

The onCreate() statement is called anytime there is a new instance of...

Here is a short summary of DBMS : database management systems.

Part of the motivation behind those little chapters is described in another blog post here.

• Introduction
• The Entity-Relationship Model
• The Relational Model
• Relational Algebra
• Very Basic SQL
• More Basic SQL
• Integrity Constraints
• Triggers
• On Disk and Buffer Management
• Indexing
• B+ trees
• Concurrency Control - Scheduling problems
• Schema Refinement - Functional Dependencies

If you would like to read those in a nicely formatted PDF or if you have any questions / suggestions / requests, feel free to send me a tweet @dandancrisan.

Schema refinement is just a fancy term for saying polishing tables. It is the last step before considering physical design/tuning with typical workloads:

• 1) Requirement analysis : user needs
• 2) Conceptual design : high-level description, often using E/R diagrams
• 3) Logical design : from graphs to tables (relational schema)
• 4) Schema refinement : checking tables for redundancies and anomalies
  

Let’s see an example of redundancies and anomalies. Consider the following table where the client’s name is the primary key.

The table is presenting information on employees (sales reps) and their clients.

If we want to insert data, we notice that:

• each row requires an entry in the client field
• we can’t insert data for newly hired sales reps until they’ve been assigned to one or more clients
• if sales reps are in a training process, even if they’ve been already hired, they can’t...

In real life, users access a database concurrently.

Database access is done through transactions. What is a transaction?

• a unit of work that has to be treated as “a whole”
• it has to happen in full or not at all

A real life example of a transaction is money transfer:

• first, withdraw an amount X from account A
• second, deposit to account B

The previous operation has to succeed in full. You can not stop halfway. Database transactions work the same way. They ensure that, no matter what happens, manipulated data is treated atomically (you can never see “half a change”).

Atomicity is part of the ACID properties that a DBMS has to maintain:

• Atomicity: either all actions from a transaction happen, or none happen
• Consistency: the database starts from a consistent state and ends in a consistent state
• Isolation: execution of one transaction is isolated from other transactions
• Dura...

In the previous section, Indexing Part 1, we’ve seen that building an index for frequently used attributes considerably increases the efficiency of a query.

In this section we’ll discuss the most widely used index implementation: the B+ Tree.

Each node of a B+ tree is a page, a block of data. A page is the transfer unit to disk.

We are already aware that a table spans on many blocks of data. We can picture this by having the tree analogous to the table, the nodes analogous to the blocks of data and we have to keep in mind that each block of data contains multiple rows.

For now we’ve talked about two things stored on a disk : the index and the data. The index (in blue below) points at the data (in green below). We clearly notice now that creating an index takes extra space on the disk.

Let’s analyze the leaves of a B+ tree. We notice that they are structured as a linked list...

We learned in the last lecture that when data is stored on disks, it is sorted as a set of blocks of data (also called pages). A block is accessed as a whole, in its entirety. On the disk, blocks are structured as link lists:

• they both have a section containing data
• they both have a pointer to the location of the next node (next block/page)

We will demonstrate how useful indexes are through a bunch of examples. An index helps us to find rows faster. They are useful for queries done on attributes that are used frequently. Indexing is just a fancy word to say “sorting a column in order to efficiently query an element”

Let’s start with some examples and define N as the number of blocks that the entire table requires.

We already know that searching on a column that isn’t sorted requires N/2 block accesses using Linear Search. Even worst, if the column doesn’t contain unique entries...

The Database Management System stores information at 3 levels of the memory hierarchy:

• Primary storage - main memory (and cache) : for currently used data, it is fast and usually volatile.
• Secondary storage - magnetic (“hard”) disk : for persistent data, it is relatively slow and nonvolatile. It stores the main database.
• Tertiary storage - tape : nonvolatile older version of the data.

Now why can’t we store everything in the main memory, if it’s the fastest way? Because …

• it costs too much : with 100$ you buy 4GB of RAM, but 2000GB of disk (500 times more)
• it is volatile : we want to save data between runs, not only at run time!

Why can’t we store everything on tape?

• disks use random access vs. sequential

Disk blocks or pages are the main units for measuring retrieved data. They have a fixed usable size, usually being 512 bytes. We can read (from disk to RAM) or write...

A trigger is a procedure that executes automatically as soon as specified changes occur in the DBMS.

A trigger has 3 parts:

• an event : at what type of change the procedure should happen? Usually it happens before/after/insteadOf an insert/update/delete.
• an action : what happens if the trigger runs? (example: add student to scholarshipStudList)
• a condition: under which condition the procedure gets executed once the event triggered? In other words, when does the event gets executed? (example: add student to scholarshipStudList only when studGPA > 3.6).

Let’s start by creating 4 tables and see how a trigger affects them:

• CREATE TABLE test1(a1 INT);
• CREATE TABLE test2(a2 INT);
• CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
• CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 );

Now let’s define a trigger on table test1:

• CREATE...