Levels of Relation Schema & Functional Dependency
There are two levels of relation schema. They are:
· Conceptual level schema: In this level schema it understands the database structures meaning, interrelationships and constraints. The basic components of the schema are the entity types, relationship types and attributes.
· Physical level schema: in physical level schema it deals with specifying the internal storage, structures, indexes, access paths and file organizations for the database files. Along with this they design application programs which are implemented as transactions. This can be represented with the help of ER diagrams.
Normal Forms Based on Primary Keys
A relation schema R is in first normal form if every attribute of R takes only single atomic values. We can also define it as intersection of each row and column containing one and only one value. To transform the un-normalized table (a table that contains one or more repeating groups) to first normal form, we identify and remove the repeating groups within the table.
E.g.
Dept.
|
D.Name |
D.No |
D. location |
|
R&D |
5 |
[England, London, Delhi) |
|
HRD |
4 |
Bangalore |
Figure A
Consider the figure that each dept can have number of locations. This is not in first normal form because D.location is not an atomic attribute. The dormain of D location contains multivalues.
There is a technique to achieve the first normal form. Remove the attribute D.location that violates the first normal form and place into separate relation Dept_location
Functional dependency: The concept of functional dependency was introduced by Prof. Codd in 1970 during the emergence of definitions for the three normal forms. A functional dependency is the constraint between the two sets of attributes in a relation from a database.
Given a relation R, a set of attributes X in R is said to functionally determine another attribute Y, in R, (X->Y) if and only if each value of X is associated with one value of Y. X is called the determinant set and Y is the dependant attribute.
For eg.: Consider the example of STUDENT_COURSE database.
STUDENT_COURSE
|
Sid |
Sname |
Address |
Cid |
Course |
Maxmarks |
Marks Obtained (%) |
|
001 |
Nupur |
Lucknow |
MB010 |
Database concepts |
100 |
83 |
|
001 |
Nupur |
Lucknow |
MB011 |
C++ |
100 |
90 |
|
002 |
Priya |
Chennai |
MB010 |
Database Concepts |
100 |
85 |
|
002 |
Priya |
Chennai |
MB011 |
C++ |
100 |
75 |
|
002 |
Priya |
Chennai |
MQ040 |
Computer Networks |
75 |
65 |
|
003 |
Pal |
Bengal |
MB009 |
Unix |
100 |
70 |
|
004 |
Prasad |
Bangalore |
MC011 |
System Software |
100 |
85 |
In the STUDENT_COURSE database (Sid) student id does not uniquely identifies a tuple and therefore it cannot be a primary key. Similarly (Cid) course id cannot be primary key. But the combination of (Sid, Cid) uniquely identifies a row in STUDENT_COURSE. Therefore (Sid, Cid) is the primary key which uniquely retrieves Sname, address, course, marks, which are dependent on the primary key.
Second Normal Form (2 NF)
A second normal form is based on the concept of full functional dependency. A relation is in second normal form if every non-prime attribute A in R is fully functionally dependent on the Primary Key of R.
Emp_Project:Emp_ProjectFigure 9.2: 2NF and 3 NF, (a) Normalizing
EMP_PROJ into 2NF relations
Normalizing EMP_DEPT into 3NF relations
A Partial functional dependency is a functional dependency in which one or more non-key attributes are functionally dependent on part of the primary key. It creates a redundancy in that relation, which results in anomalies when the table is updated.
9.4.2 Third Normal Form (3NF)
This is based on the concept of transitive dependency. We should design relational schema in such a way that there should not be any transitive dependencies, because they lead to update anomalies. A functional dependence [FD] x->y in a relation schema ‘R’ is a transitive dependency. If there is a set of attributes ‘Z’ Le x->, z->y is transitive. The dependency SSN->Dmgr is transitive through Dnum in Emp_dept relation because SSN-
>Dnum and Dnum->Dmgr, Dnum is neither a key nor a subset [part] of the
key.
![clip_image002[4]](https://sqlmentalist.com/wp-content/uploads/2013/07/clip_image00243.jpg "clip_image002[4]")
According to codd’s definition, a relational schema ‘R’ is in 3NF if it satisfies
2NF and no no_prime attribute is transitively dependent on the primary key. Emp_dept relation is not in 3NF, we can normalize the above table by decomposing into E1 and E2.
Note: Transitive is a mathematical relation that states that if a relation is true between the first value and the second value, and between the second value and the 3rd value, then it is true between the 1st and the 3rd value.
Example 2:
Consider a relation schema ‘Lots’ which describes the parts of land for sale in various countries of a state. Suppose there are two candidate keys: property_ID and {Country_name.lot#}; that is, lot numbers are unique only within each country, but property_ID numbers are unique across countries for entire state.
Based on the two candidate keys property_ID and {country name,Lot} we know that functional dependencies FD1 and FD2 hold. Suppose the following two additional functional dependencies hold in LOTS.
FD3: Country_name -> tax_rate
FD4: Area -> price
Here, FD3 says that the tax rate is fixed for a given country coutryname taxrate, FD4 says that price of a Lot is determined by its area, area
price. The Lots relation schema violates 2NF, because tax_rate is partially
dependent upon candidate key { Country_namelot#} Due to this, it decomposes lots relation into two relations – lots1 and lots 2.
Lots1 violates 3NF, because price is transitively dependent on candidate key of Lots1 via attribute area. Hence we could decompose LOTS1 into LOTS1A and LOTS1B.
A relation schema R is in 3NF when it satisfies the conditions below.
1. It is fully functionally dependent on every key of ‘R’
2. It is non_transitively dependent on every key of ‘R’