Hi guys in this blog we shall see about advanced features in Database.
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 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.
To export a database into a dump we can use the following command.
mysqldump -u mysql_username -p name_of_database > databasefile.sql.
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.
