SQL_study_notes4：the_connection_of_tables

1 Introduction
1.1 We need a unique column to connect several tables.In general, the primary key in a table can be used.
1.2 The FOREIGN KEY is a column in a table that references the primary key of anoher table.
1.3 I can use a foreign key to reference a unique value in the parent table, which would give me definite adv. Which means I will only insert values into my foreign key that exist in the table the key came from the parent table. This is called referential integrity. And it does not have to be the primary
key of the parent table, but it must be unique.
1.4 one-one, one-many, many-many. We need a junction table which is that one-many and one -many get many-many.

2 composite keys use multiple columns
2.1 A composite key is a primary key composed of multiple columns, creating a unique key.
2.2 When a column’s data must change when another column’s data is modfied, the first column is FUNCTIONALLY DEPENDENT on the second.
2.3 A partial functional dependency means that a non_key column is dependent on some, but not all, of the columns in a composite primary key.
2.4 If change any of the non_key columns, it might cause any of the other columns to change, this is a so_called transitive dependency, which happens between two or several non_key columns
IN SUMMARY
We need to focus on the relationship between different columns, which is that some columns would undergo alteration because of the change of other columns. So to forbid this situation happening, using an unique id field like MY_CONTACT before allows we to completely avoid the issue. since it is a new key that exists only to index that table, all other columns is independent on it.

3 1NF & 2NF & 3NF
3.1 FIRST NORMAL FORM or 1NF: 1 columns contain only atomic values 2 no repeating groups of data.
3.2 SECOND NORMAL FORM or 2NF: 1 be in 1NF 2 all the columns in table are a part of the primary key, which means that all the information would be contained by the primary key. or it has a single column primary key, this is a great reason to assign an auto_increment id column.(have no partial functional dependencies).
3.3 THIRD NORMAL FORM or 3NF: 1 be in 2NF 2 have no transitive dependencies, which means non_key columns are inter-independent.

4 multiple table management (simple)

### How to use code transfer a non_1NF table to 1NF table?
alter table my_contacts
add column interest_deal,
add column interest1 varchar(50),
add column interest2 varchar(50),
add column interest3 varchar(50),
add column interest4 varchar(50)；
update my_contacts
set interest1 = substring_index(interests, ',', 1);
update my_contacts
set 
interest_deal = substr(interests, length(interest1)+2),
interest2 = substring_index(insterest_deal, ',', 1),
interest_deal = substr(interest_deal, length(interest2)+2),
interest3 = substring_index(insterest_deal, ',', 1),
interest_deal = substr(interest_deal, length(interest3)+2),
interest4 = substring_index(insterest_deal, ',', 1);
alter table my_contacts
drop column interest_deal;
create table interests
as select interests
from my_contacts;
### How to use the data of the existed table to create a totally new table?
create table profession
(
id int(10) not null auto_increment primary key,
profession varchar(20)
);
insert into profession
(profession)
select profession from my_contacts
group by profession
order by profession;
### Optimizing...
create table profession as 
select
profession
from my_contacts
group by profession
order by profession;
alter table profession
add column id int not null auto_increment first,
add primary key(id);
### Continuing Optimizing...
create table profession 
( 
id int(10) not null auto_increment primary key,
profession varchar(20)
)
as 
select
profession as prof
from my_contacts as mc
group by prof
order by prof;
### "as" could be left out to save the space

5 the cross join & inner join statements

### the cross join takes each value in from the first table and pairs it up with each value from the second table.
select t.toy, b.boy
from toys t cross join boys b;
### inner join
select boys.boy, toys.toy
from boys inner join toys on boys.toy_id = toys.toy_id;
### natural joins only work if the column you are joining by has the same name in both tables, which means that this kind of join identify matching the same column names.
select 
me.last_name,
me.first_name,
me.phone
from
my_contacts me natural join job_desired jd
where 
jb.title = 'web developer'
and
jd.salary_low < 105000;

6 the outer query & the inner query
Still a little confused about this section.

### a subquery is a query that is wrapped within another query. It is also called an inner query.
### And that another query also can be called outer query.
### outer query + inner query = query with a subquery
select 
mc.first_name, 
mc.last_name, 
jc.title
from
job_current jc
natural join
my_contacts mc
where jc.title in (select title from job_listings);

Or other general expression:
select some_column, another_column
from table
where column = (select column from table);

### Or
select 
mc.first_name,
mc.last_name,
( select state from zip_code where mc.zip_code = zip_code ) as state
from my_contacts mc;

ATTENTION:
If a subquery is used as a column expression in a select statement, it can only return one value from one column.
If the subquery stands alone and does not reference anything from the outer query, it is a noncorrelated subquery.
So we can use IN or NOT IN function to test if the values returnes in the subquery are members of a set.
In a noncorrelated subquery, the inner query, or subquery, gets interpreted by the RDBMS, followed by the outer query.

### Correlated subqueries
select mc.first_name, mc.last_name 
from my_contacts mc
where 3 = (select count(*) from contact_interest where contact_id = mc.contact_id);
### a useful correlated subquery with NOT EXISTS
select mc.first_name, mc.last_name, mc.email
from my_contact mc
where not exists
(select * from job_current jc where mc.contact_id = jc.contact_id);

7 the left and right outer join statement

### the left outer join matches every row in the left table with a row from the right table
### a null value in the results of a left outer join means that the right table has no values that correspond to the left table.
select
g.girl, t.toy
from
toys t
left outer join
girls g
on g.toy_id = t.toy_id;
### the right outer join evaluates the right table against the left table.
select 
g.girl, t.toy
from 
toys t
right outer join 
girls g
on g.toy_id = t.toy_id;

8 a self_join idea

### a self_referencing foreign key is the primary key of a table used in the same table for another purpose.
### the self_join allows you to query a single tables as though there were two tables with exacty the same information in them.
select c1.name, c2.name boss
from
clown_info c1
inner join 
clown_info c2
on c1.boss_id = c2.id;
### or
### the following statement is a little difficult to understand.
select 
c1.name,
(select name
from clown_info
where id = c1.boss_id) boss
from 
clown_info c1;
### the above 2 statements just show the same meanings.