Edgar F. Codd proposed the relational model in 1970 and also Normalization.
https://stackoverflow.com/questions/8337309/superkey-candidate-key-primary-key
Normalization:
Removing inconsistent dependency and redundancy within a database is called normalization.
Denormalization:
Normalization supports for efficient data storage but it increases the time to access the related values, as when querying dbms will look into multiple table for joining tables to return values.
Performance and tracking historical data
Rows = Tuples = Records
Columns = Attributes
Super Key: A super key is a set of columns that uniquely defines a row.
Name: Not Unique
Id: Unique
Address: Not Unique
Phone : Unique
Here Id and Phone are Unique. All the below combinations are super keys. Super keys is a superset of candidate key. Any combination which uniquely defines a row is a superkey.
- Id
- Id, Name
- Id, Address
- Id, Phone
- Phone
- Phone, Name
- Phone, Id
- Phone, Address
Below examples are not Super Keys,
Name, Address
What is Composite Key?
Primary key consists of more than one attributes/Columns is called Composite Key.
Candidate Key:
A candidate key is a "minimal" superkey. So in the above example the minimal is 1. So, we have two canditate key Id and Phone.
Primary Key: There will be
Prime attributes:
The attributes/Columns which are the part of candidate key are called Prime attributes.
Functional Dependency: means relationship among the attributes.
Functional Dependency: X → Y means that the values of Y are determined by the values of X. Two tuples sharing the same values of X will necessarily have the same values of Y.
A
à B
A functionally determines B
Name
|
ID
|
Marks
|
Pradeep
|
1
|
62
|
Vinod
|
2
|
67
|
Ravi
|
3
|
78
|
Pradeep
|
4
|
78
|
In this example
IDà Name (if you know ID, we can uniquely
determine the name of the person, vice versa not holds good as same Name can be
have by multiple persons. In this example there are 2 persons with name Pradeep)
IDà Marks (With ID we can know the marks,
but vice versa again not holds good).
Introduced by Edgar codd
1NF, 2NF, 3NF and so on.
1NF:
- each table has only relevant columns
- each table has primary key
- each table has no repeating groups
- No multiple values in any coloumn of any row
2NF : No partial dependency should exist
To be in 2NF, the table has to be in 1NF and all non-prime attributes/coloumns has to be depend on the whole primary key, and not on a part of the primary key.
Consider a Customer table with CustomerID and StoreID together making up the primary key. If we have an attribute StoreLocation, we can see that it depends only on StoreID, which is not the complete primary key. Hence its not in 2NF.
If the primary key is not composite Key, Primary key is single attribute then the relation will be in 2NF
https://www.tutorialspoint.com/dbms_for_gate_exams/dbms_second_normal_form_2nf.asp
3NF:
https://beginnersbook.com/2015/05/normalization-in-dbms/
