Joins:
What are Joins??Joins are used to combine data and retrieve the data we want from different tables.
Syntax:
EXPLICIT JOIN NOTATIONS:
Select a1,a2,a3 from t1,t2 where c1=c2;
IMPLICIT JOIN NOTATIONS:
select a1,a2 from t1 join t2 on c1=c2;
The simplest kind is the Cross join where The cross join result in cartesian product of all the records from two tables.
Inner Join
if student has rno and rname attributes and
marks has rno and marks attributes then to join them with inner join give it as,
select student.rno,rname,marks from student join marks on marks.rno=student.rno;
You can also use the "using" clause in the above query if the common attributes in both tables have the same name.In our eg "rno" is common ,so u can give it as,
select student.rno,rname,marks from student join marks using(rno);
Equi Join:
If the query performing the join is based on the equality symbol it is called as equi join.
Left Join:This displays all the records in the table specified in the left even if it doesnt have corresponding matching attributes in the right table.
in the above eg if u want all the roll nos in the db even if their marks are nt entered use this kind of joins.Keyword is "left join".
select student.rno,rname,marks from student left join marks on marks.rno=student.rno;
Right Join:In this case all the records in the marks table will be displayed irrespective of the matching records in the left table ie the student.
select student.rno,rname,marks from student left join marks on marks.rno=student.rno;
Natural Join:
A natural join is a type of equi-join where the join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally named columns.
Self Join:
This is used to combine a table to itself.
When you have to find a pairing of all the employess from same country ,u can use this.Remember u have only 1 employee table..
SELECT F.EID, F.Lne, S.ID, S.Lne, F.Country
FROM Employee F
INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EID < S.EID
ORDER BY F.ID, S.ID;
The fourth line is used to reduce the no of comparisons and to find distinct values.
Union:
Unions are used to combine the contents of 2 tables.The number of attributes that you combine should be the same and the type also should be the same.It is used to combine the records,ie the results of 2 queries.
To list all the teachers and the students along with their ID
select tname,tid from teachers union select sname,sid from students;
SUBQUERIES:
This is when you use the select within another query.This can be used along with select,delete or other insert statements.
To find the country with max population we give..
select country,pop
from coun
where pop=(select(max(pop) from coun);
Transactions:
To commit a database :
commits the current transaction, making its changes permanent
commit db_name;
Rollback :
Rolls back to an earlier point undoing the transactions to the last commit operation.
Import Export Database Tables:
I already spoke of creating a dump and storing it in a file in my prev post.To again import the file into the server give
/usr/local/mysql/bin/mysqldump -u uname -p db_name < dumpfile.sql;
Concept of Views Triggers and Procedures:
Create a view:
It is like a dynamic table,made from data extracted from the other tables.
It may be a subset of the other tables.
It occupies only a very little space because only the definition of the view is saved and not the entire data satifying the query.
syntax:
Create view v_name as (select a,b from ttable where c1=c2);
There are also only readable and updatable queries.If it is only readable then update,insert and delete operations in the table will not be effectively reflected in the view.
Procedures:
This is a piece of code that is written and saved in database server.
This is invoked by another program or a trigger or even by another procedure.
Procedures can be recursive.
Advantages and Disadvantages:
Advantages can be reuse,reducing traffic,quick execution etc..
Disadvantages are high load on the database and stuff..So based on the application must decide the use of procedures wisely.
create procedure proc1(Out n1)
begin
select count(*) into n1 from tab1;
end
Triggers:
This is the procedural code automatically executed when a db event or change occurs in a table.
This can be used in a case where inserting a new record in 1 table 'marks' must also update another table 'toppers' based on the marks.
That is the insertion in marks should trigger a check on them to insert into 'toppers'.
Syntax:
CREATE [OR REPLACE] TRIGGER <trigger_name>
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]
[FOR EACH ROW [WHEN (<trigger_condition>)]]
<trigger_body>
eg)
CREATE TABLE T4 (a INTEGER, b CHAR(10));
CREATE TABLE T5 (c CHAR(10), d INTEGER);
CREATE TRIGGER trig1
AFTER INSERT ON T4
REFERENCING NEW AS newRow
FOR EACH ROW
WHEN (newRow.a <= 10)
BEGIN
INSERT INTO T5 VALUES(:newRow.b, :newRow.a);
END trig1;
When val of a in insertion for t4 is less than 10 then do an insert into t5.
NORMALISATIONS:
This is used to improve the efficiency in the way you manage DBMS.You can easily put all the attributes in a single table and access that from that single table.This single table will now be acting as a GOD table with all the attributes.But by doin this there will be so much of redundant data in the database.There will be many copies of the same data in the db and when you make a change in one of the table it may not be reflected in another table having the same attribute.This affects the integrity of the database.
To avoid this you split the data you have to multiple tables and relate the various tables with foriegn keys.
There are many stages in which you avoid the complete redundancy of the data,which is attained with many stages of normalisations.
1)First Normal Form
-->No duplicacy of data
-->No multivalued attributes.
2)Second Normal Form
-->Each tuple must be uniquely identified with the complete set of primary keys,else if individual key is used to identify a tuple,break it into 2 tables;
-->must follow the first normal form.
3)Third Normal Form
-->This removes transitivity.If 'a' depends on 'b' and if 'b' depends on 'c' where 'a','b','c' are attributes you have to split the tables to 2 different tables.
In addition the 2NF should be followed.
4)Fourth Normal Form:
-->Must follow all other normal forms + it should avoid all multivalued dependencies.
5)Boyce Code Normal Form:
-->Must follow all the 3 NF .
-->if a,b,c,d are attributes of a table and a,b together form the primary key ,then there can be a condition where a,d-->b,c but not
a,d-->b..If such a case exists it violates BCNF form.
When all the above are attained to a database system then it is highly atomic,and has high integrity and absence of redundancy.Insert,Update and Delete anomolies are also removed.
