SQL Tutorial : Database Design

The organization of data in a relational database in the form of tables.
Eg: Student (Name, Address, Subject, Grade)

Problems associated with this scheme are -

Redundancy
The student address is repeated for each subject.

Update anomaly
As a consequence of redundancy updating address in one tuple while leaving it unchanged in another.

Insertion anomaly
It is not possible to record address of a student unless he has registered for at least one subject.

Deletion Anomaly
This is inverse of insertion anomaly if a student drops all the subjects in which he is registered it looses students address.

Integrity Constraints

The integrity constraint is a relational database system it is broadly classified as

Integrity constraint on admissible domain values of a tuple
These constraints restrict the admissible values of the attributes of the relation to some range and are called domain dependencies.

Integrity constraint based on Inter – tuple relationship
The data dependency do not depend on the values of any given component of a tuple, but on whether the projection of two or more tuples on a subset of the attributes of the relation satisfy some relational constraints.

Types of dependencies

• Equality generating dependencies
• Tuple generating dependencies
• Functional dependency
• Multivalued dependency

Normal forms

Normalization is the process for assigning attributes to entities, which reduce data redundancies and eliminate data anomalies that result from those redundancies.

Normalization works through a series of stages called Normal forms.

1^st Normal form
Given a relation R, attribute A of R is functionally dependent on attributes B of R if and only if each A value in R has associated with its precisely one B – value in R.
Parts = (part no, part name, part size, QTY – No- Hand)
Parts. part no -> parts. part name
Parts. part no -> parts. part size
Parts. part No -> parts. QTY –ON – Hand

A relation is said to be in first normal form if every attribute is functionally dependent on key.

2^nd Normal form
To be in second normal form it must be in first normal form and attributes of the table must be dependent only on one part of concatenated primary key.
No key attribute is fully dependent on primary key.
Eg: ORDER (ORD – NO, ITEM – Name, QTY, Price)

Item price is not dependent on ORD – NO, it is dependent only on item – name and QTY is dependent on both parts of key.
Item (Item name, price)

3^rd Normal form

Transitive dependent
If attributes a depend on B and B depends on C due to which A depends on C then A is said to be transitive dependent on C.

-> Transitive dependency causes problems in updation

Normal forms	Test	Remedy
1st N.F.	Relation should have a no non – atomic attributes or nested relations	Form new relation for each non – atomic attribute or nested relation
2nd N.F.	For relations where primary key contains multiple attributes, no non – key attributes should be functionally dependent on a part of the primary keys.	Decompose and set up a new relation for each particle key with its dependent attributes. Make sure to keep a relation with the original primary key and any attributes that are fully functional dependent on it
3rd N.F.	Relation should not have a non key attributes functionally determined by non – key attributes. i.e there should be no  transitive dependency on a non key attribute of primary key.	Decompose and set up a relation that includes the non – key attributes that functionally determine other non key attributes.