Wednesday, 25 July 2012

Document based database

 Hi,
     Document base database. From the name you can get that it has something to do with a database and document. Initially i thought it as database with proper documentation. But it turned out to be database which uses documents to store the data. They can be opened and read like any other file.
      Many people who have been using RDBMS like mysql will find it difficult to switch on to this type. Because this is an entirely new concept where you need to unlearn things. We need to think apart from normalization and relations.So lets get started.

Why Document-based:
       Well each new concept has a reason behind it. And the reason for the document-based is to overcome the rigidity of the RDBMS. By rigid we mean the strict rules in terms of schema.DBD also prevent joining tables as the details which need to be retrieved by joining multiple tables can be provided  within the same document.
       There is no schema in DBD. Each document can contain its own set of attributes which can vary from one document to another(even though it is preferred to have similar attributes in the document ).

Comparing RDBMS and DBD using example:

       For example (in MongoDB):
       db.users.({name:'prem', likes['facebook','twitter'] });
       db.users.({name:'praveen',cars['ducati','ferrari']});
      
        The above statements adds two documents to the collection users with names "prem" and "praveen". These both documents have two different attributes i.e, likes and cars. Thus we can add each document different data types in runtime. Sounds good right....

       If we need the same thing to be done using MySQL the schema would be,
       create table users (name varchar(10),likes1 varchar,likes1 varchar,cars1  
       varchar, cars2 varchar);

      Then for prem there would be two NULL columns in cars1 and cars2. The same would be case for praveen in the likes column. If we need to increase the number of cars owned by praveen we need to alter the entire table in RDBMS. But in DBD only the particular document will be affected.

       Consider a query where you need to "find number of people who like facebook". The query can be done in DBD too. But searching each attribute of document to address the query is not a proper way. So you may need to rethink the structure of the documents.

Storage in DBD:
        Well another advantage of DBD is that the documents are in text files. So the processing of these documents are extremely fast compared to the relational database.Here the Documents are analogous to records and the collections are analogous to the tables.

Searching with JASON:
       Each documents are addressed by a key-value pair. For each document a unique id is generated. Thus search for a document is faster. DBD allows API's such as REST using JSON which is pretty much faster than RDBMS( JDBC , ODBC ). Each document is a object of JASON. In the previous example the two user objects 'prem' and 'praveen' are JSON. For good knowledge on JSON refer http://www.json.org/.
        The use of JASON makes the DBD to support javascript very much. They also form effective means of communication between server and client as the message transfered will be in the form of JASON rather than XML documents.
       Primarily the DBD came into use to manage huge amounts of data. The DBD allows writing very fast and hence they are used in write intensive application. Even in reads it is better to use rather than RDBMS.

Transactions:
        But the problem in this is that we cannot go for transactions in DBD. Even though there are facilities to support transactions these days. DBD are not preferred  for transactions. There is no possibility to go for locks in DBD.
        There can be situations when you need to split the collection into two with a common value to link the two collections(foreign key in RDBMS). Even though we can implement the above scenario using DBD it is not advisable.
       
          

Tuesday, 24 July 2012

Unix commands and working

The audio blog for the blog is at http://yourlisten.com/channel/content/16906239/Linux_commands

Before getting into the commands let us see the Linux directory structure.

/ :
 This is the root directory. Every other folders are under the root directory. This usually contains the sub folders so its best no to keep any direct files in it.

< /boot >
       This folder contains the information it needs when it is getting booted.

< /bin, /usr/bin >
        This folder contains important program like ls,grep etc.,

< /usr >
 It is the largest directory in the linux.
 Inside the usr directory we have these following folders.

doc : Documentation for the user apps, in many file formats.
share : Config files and graphics for many user apps.
src: Source code files for the system's software, including the Linux kernel.
include : Header files for the C compiler. The header files define structures and constants that are needed for building most standard programs. A subdirectory under  contains headers for the C++ compiler.

 </var>
          It has the data which will be constantly changing during the system execution. They contain directories like , log - to to contain the log information,
mail - to have the sent and received mails , spool - to contain the jobs which are queued up for process(eg. printing).

</dev>
        It has the various devices attached to the file system.

Now lets brush up a few linux commands which we normally get to use,

  • ls - to list all the files and directories in the current directory.
  • mv- moving file names.
  • rm- remove the file.
  • rmdir - remove directory.
  • rm -r - removes the directory with all the files ans sub directory inside it.
  • cp filename filename2 - creates the same file with the name as specified in the second parameter.
  • scp filename destination@ip_address - this will upload the given file into the destination address.
  • scp destination_name@ip_address:/path/filename - this will get the file from the destination to our machine.
  • ssh ip_address : This will let us to access the system of the given ip_address through the terminal.
  • chown - to change owner of a file
  • chmod - to change the mode of a given file
  • To start any any network application we can use /etc/init.d/application_name start.
  • ping url - used to check whether the given website is working.
  • ps -elf | grep application name -this will give the details of a particular process.
  • tail -n - prints the last n lines of the file
  • head -n - prints the first n lines of the file
  • top - it is simliar to ps but it shows the real time usage while ps is shows average values over a given snapshot.During top if you press shift + p it will give
  • kill - to kill a particular process given the process id. The process id can be found by using grep command along with the application name.
  • cat , less ,more - these are display commands which is used to display the given file in th terminal.
  • df - disk usage of the file system.
  • du - disk usage of each processes. 
       To know about more commands check this given link
       http://www.oreillynet.com/linux/cmd/



       

Monday, 23 July 2012

Digging deep....DB!!!

Hi guys in this blog we shall see about advanced features in Database.

JOINS:
          Joins are queries which are often executed when you need to fetch data from more than one tables. We can also execute queries without joins, but internally cross join will be getting executed. They are some joins which i might list,

This image give better explanation than any written statements. Thanks to Mr.Hariharan for providing the image.

UNION :
         Union clause is used to get the union of two or more queries. The distinct values will be getting listed. The result of a join and a union may look similar. But the list which is getting returned will be taller in UNION.
         select <columns> from  table1 union select <columns> from table2.

Import and Export :

             To export a database into a dump we can use the following command.
      mysqldump -u mysql_username -p name_of_database > databasefile.sql.
           
           This can be retrieved and used to create a new database.For doing it the following command should be executed.
        mysql -u mysql_username -p name_of_database < databasefile.sql
            These dumps are used as a backup along withe the export and import purposes.

Subqueries :
           The subqueries are queries within queries. It is used when you need to get the results of one query and apply another set of query operation over it.
          The syntax for subquery es,
          select <columns> from <tables> where column(s) {=|>|<|<=|>=|<>}{ANY | ALL | SOME} / { EXISTS | IN } {QUERY};

Normalization :
        Normalization is used in DBMS to overcome the redundancy, anomalies and dependency. This is done by splitting a larger table into smaller tables so that insertions, deletions and modifications can be made in a single table and the changes can be made to known to other tables by defining relationship between the tables. There are various normalized forms. They are,

2NF:
        This is done when there exists partial dependency. XY->Z be a relation. In addition if Y->Z is another relation that is existing. So its better if we could create a separate table having relation Y->Z.

3NF:
         This is done when there is a dependency of the give form. X->Y is one relation and Y->Z is another relation. This implies that X->Z. So we should split the table such that the relations X->Y and Y->Z appear in separate tables.

BCNF:
         This is done when the following scenario appears. When XY->K and another relation exists which states that K->Y. In that case we can remove the column Y appearing in the right side of the second relation. and create a separate table for the relation K->Y and another for X->K. The scenarios are (a)there are multiple candidate keys, (b) The keys are composed of multiple attributes, (c) There are common attributes between the keys.
           In this manner we can apply the normalization over the given tables.

SCENARIO :

We can see about the above mentioned NF's here.
Consider the table with columns,

person_name, company_name, designation, salary.

         here the person_name, company_name --> designation, salary. Logically we can also deduce that company_name, designation --> salary because all the persons in the company will have the same salary. So instead of repeating the group(company_name, designation --> salary) in each employee record we can separately have a table with these columns alone.
               Another problem which is avoided here is, if salary for a particular designation in the company is incremented we need not search for each employee with given designation in the given company and update their salary column. This problem is avoided with a single update in our new model.

              This is the use of normalization.

Concept of views, triggers and stored procedures.

 VIEWS:
          They are stored queries which can be accessed by the name. They are virtual tables and they do not exist in the physical form. Views are created to view certain portion if the tables along with aggregation applied over it. Whenever a view is accessed the query will be getting executed and it helps in abstracting the underlying complexity to the user.

  This can be done by the syntax,
           Create view as select <columns> from ... ; 

           Create view  total_customers as select comp_name as name,count(*)  as 
           no_of_customers from company, customer where 
           comp.cust_id=customer.cust_id group_by(comp_name) ;

           Whenever the above query is run the number of customers in each company will be listed. This can be done by simple running the query,
          select * from total_customers;


           Inside the query for the view is executed and the result is passed onto the query using the view.

           The view can be updatable when the view doesn't contain complex values such as 3.56, col1*col2, col1+3. Any updates in the view / table will be reflected back in the corresponding table / view.

TRIGGERS:
         Triggers are stored procedures which will be getting executed in case of an event. The triggers are used usually to provide integrity in a database for example, when a withdrawal from account causes a negative balance then such transactions should be prevented. This can be accomplished using Triggers. MySQL provides three types of triggers. They are Insert, Update and Delete.The syntax is,

CREATE TRIGGER <trigger_name> 
BEFORE/AFTER UPDATE/INSERT/DELETE ON <table_name> 
REFERENCING NEW ROW AS n, OLD ROW AS o FOR EACH ROW IF n.col_name <comparison_operator> o.col_name  
THEN  
END IF;

STORED PROCEDURES:
           Stored Procedures are piece of code to be encapsulated into a single procedure name and the  code will get invoked from another procedure or from a trigger. These are stored in server.
It has the following syntax,
 DELIMITER //
CREATE PROCEDURE Get_All_cars(IN input varchar(10),OUT output int)
BEGIN
SELECT count(*) INTO output FROM products where type=input;
END //
DELIMITER ; 
          The parameters can be of type IN, OUT or INOUT. In the IN type, the given input will be only for processing. In the OUT type a value will be getting stored in the variable and it will be used in the place from where it was called. INOUT is a combination of both the IN and OUT types.
         The procedure can contain loops and case statements.
        The procedure can be called using by the following syntax,
         CALL Get_All_cars('name',@output) 
          select @output ; // this will give the number of cars of given type.


There are both pros and cons. The pros are user abstraction and sharing logic. The cons are repeating logic in the server and increased in the server.

Saturday, 21 July 2012

RELATING OOPS, DATA STRUCTURE ,DBMS ,WEB


               This blog tries to relate the topics that are being blogged about. So lets try to relate all these things to get the big picture of them all.

                Lets take a journey from the web services and we can move through the rest of the topics.Consider a user accessing a web page to download a picture. The web services starts from a client request to the server.  So lets guess how this works.
             The client sends a message which reaches the server. There can be many paths from client to server and the client must choose the optimal path. This is done by using path finding algorithms. The information will be stored in the networking devices. Proper data structures should be used to give the optimal path to the request from the client.
             The web page from which the request starts will contain many components. Each component will have particular attributes and behavior. This can be easily established using Object Oriented Programming Languages. Here each component will be represented as an object.
           Once the request reaches the server the web server will get the request and will forward it to the application server. This application server will be used to get the picture.
           The picture will be stored in a database. The DBMS will abstract the application programs from the manner in which the data is stored in a database. There will be different data structures which will be used to retrieve the data efficiently. The most preferred data structures used here are indexes and trees(B+).
           Database concentrates more on the data while DS concentrates more about organizing the data.
          Anything which is getting stored will require a data structure. Hence the data structure will be an essential in a programming language like OOPS. The OOPs is related to RDBMS. Because the master tables can be represented by using the objects. There also exists OODBMS which purely depends on the Object orientation.

Friday, 20 July 2012

INSERTION AND RETRIEVE DATA

INSERTION:
        *) The insert command works with the syntax,
        insert into <table_name> values(.....);

        *) There can also be bulk insert where you can insert multiple values into the database in a single query.
         insert into <table_name>  values (field1,field2,....) values
                           (value11,value12,....) , (value21,value22,....) ,...;

         *) There is also an option of inserting data from other tables using the following command.
         insert into table1 select * from table2;

         *) You can also insert particular values into the table1 from table2,
        insert into table1 select id,name,age from table2;

         *) If  you need to insert data from another table you can do it by mentioning the database name of the table before the table name.
          insert into db1.table select * from db2.table2;

RETRIEVAL OF DATA:
        The retrieval of data is done using select command. The where command is used to set the conditions for selecting the columns. Now let us discuss those commands.
        select <columns> from <tables> where <conditions>;
        To select all the columns use the ' * ' symbol. To select the tables list the tables. In the conditions, you can use '=' operator to check for equality, '>' greater than, '<' less than.
        To compare the results of various operators we can have AND and OR conditions attached to them.
        ORDER BY is a clause used to order the rows retrieved based on the columns specified on the ORDER BY clause.
        GROUP BY is a clause used to group the rows containing the columns into a single row.
         The following functions are usually used along with GROUP BY clause and they can be used independently also.
         *) COUNT function is used to return the number of values of a particular column.
         *) SUM function is used to add the values of a particular column.
         *) AVG function is used to find average values of a particular column.
         *) MAX function is used to find the maximum in a particular column.
         *) MIN function is used to find the minimum in a particular column.
         The LIKE operator is  used in the following manner,
         SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern.
           The pattern can have a '%' symbol either in the beginning or in the end or both the ends. The '%' symbol will be substituted by one or many characters.

         

Thursday, 19 July 2012

MODIFYING TABLE

Modifying Table :
                     Modifying table is modifying the schema or contents of the table. The schema can be altered by using ALTER,UPDATE,DELETE command. Modifying involves,

ALTER:      
        ALTER command changes the schema of the tables.
        1) Altering the data type or adding new constraints or changing names of
            column ,
            ALTER TABLE <table_name> CHANGE COLUMN <column_name>
            <new_column_name>  data_type constraints ;

            For_example : If i want to change the car_id from largeint to int with
             AUTO INCREMENT constraint we can do this by,
             ALTER TABLE cars CHANGE COLUMN car_id car_id INT
             PRIMARY_KEY AUTO INCREMENT;

        2) We can also change the column name ,
             ALTER TABLE cars ADD <column_name> AFTER/BEFORE
             <column_name>

             For_example : If we want to add car_color column after the car_id we
             can add it using,
                ALTER TABLE cars ADD car_color after car_id;

 UPDATE:
              Update table is used to change the values inside the table. This is done by selecting particular rows and changing the attributes in those rows.
            UPDATE TABLE <table_name> set <column_name1>=value WHERE
             <column_name1>=value1;
             For_example : If we want to change the color of car having id=102 to
             'Yellow'
             UPDATE TABLE cars set car_color='Yellow' WHERE
             car_id=102;
  DELETE:
             DELETE is used to delete either selected records from the table or the entire records in a table. The DELETE command can be used in either way as follows
             DELETE FROM <table_name> where.....
             The above mentioned is used to delete the selected records depending on the where clause.
              DELETE * FROM <table_name>
              The above mentioned will delete all the columns from the table.

 TRUNCATE:
               TRUNCATE is used to delete the entire contents from the table. The syntax is,
                TRUNCATE TABLE <table_name>

 TRUNCATE VS DELETE:
                Applying DELETE for all records in a table and TRUNCATE may seem similar. They give the same output when they execute. But there is a difference between the both. DELETE logs before deleting each record. DELETE is costlier as it involves resources like locking and logging. But DELETE is more safe to apply than TRUNCATE.

DROP:
             DROP differ from TRUNCATE and DELETE because DROP deletes the schema along with the entries. DROP is applied either to a column or a table. When the schema is deleted all the integrity constraints, access privileges will be deleted.
          Lets quote two examples to differentiate between DROP and TRUNCATE.
           eg.1: TRUNCATE TABLE CAR;
                    SELECT count(*) from car;
                     output :
                      count(*)
                     --------------
                        0
          e.g.2: TRUNCATE TABLE CAR;
                    SELECT count(*) from car;
                     output :
                     ERROR at line 1: ORA-00942: table or view does not exist.

Creating a Table and the commands to work on it..


Here we will be dealing about the various commands to work with in the DBMS. The DBMS which we are using here is MYSQL.

Creating and Deleting a Database :
                 The DBMS can contain any number of databases. Here we can consider a database for cars. So how will you create one...?
                   CREATE DATABASE [ IF NOT EXISTS ] cars ;
                  This command helps in creating a database called cars. the IF NOT EXISTS is used to check the database whether another database by the same name exists. This is optional but helps in preventing errors in-case another database by the same name exists. To use this database we should use the command,
                   USE cars ;
                   This helps in fetching the database cars and then we can create tables in the database and work on it.
                     To delete a database from the DBMS we can use the command,
                     DROP DATABASE [ IF EXISTS ] cars ;
                     The IF EXISTS is again an optional command where you can check in prior whether the database already exists or not.  DROP is a risky command to use as it deletes the database along with the entire tables inside the database.


Creating the tables :
             Once you enter into the database you can create tables inside it. You need to be clear about various things before you create a table. We will discuss various things in detail before we know about the syntax of create tables.


a ) Data Types .
                 Data Types offer a lot of data types to work on. Each column will contain a particular data type. The data types can be NUMERIC, STRING, DATE AND TIME, SPATIAL. Picking a data type for a column determines on 
            1) How do you want to represent the data
            2) Whether it is a fixed length or variable length.
            3) If there is a need to index the data
            4) How MySQL will compare the values in the data types.

b) Storage Engines .
             MySQL offers various types of storage engines. The type of storage engines which are offered innoDB, MyISAM, BDB, HEAP, MERGE. The default engine which will be used is MyISAM. It provides full support to indexing but it is not transaction safe. Only innoDB and BDB are transaction safe.
             MyISAM is used when you need fast access of data without any need to concentrate on foreign key constraints. InnoDB supports foreign key constraints.
            We can change the table type(engine) on your need.
   InnoDB vs MyISAM :
  • InnoDB provides referential integrity(foreign key constraints)
  • InnoDB provides row level locking. Hence it is used in write intensive   applications.
  • MyISAM is simple and hence it is generally preferred for read intensive applications.
  • Genrally MyISAM is faster as it uses full-text indexing.
c) Identifying Primary key and Foreign key.
              Primary key is the key which makes it possible to uniquely identify a record in a table. The primary key can be an attribute(column) or a set of attributes(columns).
        Example: A car database can contain a car table. The car table needs a attribute to differentiate between two records. This can be achieved using having a car_id. This id should be unique and NOT_NULL.
              Usually a table should contain primary key. If you don't have any primary key it wont be an error. Languages like Microsoft Access will maintain a default Primary key with an auto number data type.
  
               Foreign keys are primary keys of another table. Foreign key comes to use to maintain relationships between tables. Foreign keys are used to cross reference between two tables.

               Example: Coming back to the car database. There can be a manufacturer table which contains a manufacturer_id as primary key in it. Each car will have a manufacturer associated with it. This relation can be maintained by having a new column in car table known as manufacturer_id. Now, it will be easy to know the manufacturer of the car and also no car can contain unregistered manufacturer.

d) Identifying Nullable and NOT NULL fields in database :
                     There are certain situations when you don't need to specify certain fields and there are certain attributes which should not be left unfilled. For example a car may have a owner or may not have a owner. So it is not always needed to fill this attribute of the record. But each car should have tires so the tire_company_id should be filled and should not be left unfilled.
                       We can also have a Default field which allows a record to take a default value in-case it wasn't filled during insertion.

e) Identify exact data type for fields :
                   This is an important aspect in creating the table. Now, lets come back to car table. Here car_id can be a largeint as there is a need to store large number of cars and each record should have a unique identifier. The car_model indicating the model of the car should be a string data type. In that it can be a a varchar(10) - 10 letter characters. Because no model of a car can be more than 10 letters. So having some common sense and practical knowledge will let you pick the data types and fields.

f) Naming Conventions for Column Names:
                  The basic things to follow for naming columns are
                1) To check whether the naming makes sense to you,
                2) To check if the naming makes sense to others,
                The first point is because if you see your code code few months later you may get confused. The second is because when many people are working with you the naming should be clear to both and the other people who uses it.
                    The preferred way of naming are using singular words and preventing mentioning the table names as a prefix of the attribute.

e) How to determine which field to index :
                        Creating index will slow down the entire database as there is a need to maintain index and there is overheads involved in creating and deleting the index. Generally primary fields are indexed but non primary fields are sometimes indexed too.
                       Index helps in improving the search if the value searched is same as the value required.
              Example: If the car_id is indexed queries requiring the car_id's can be executed fast. But if there is a query regarding car_id and the model_name then the index wont be useful.
                     Create index by keeping in mind the query performance and the maintenance of the records.