QUIZ 7

NORMALIZATION

PROCESS OF DATABASE PLANING
- Collect needs for user/business and expand E-R model based on user business needs.
- Conversion of E-R model to collection relations.
- Normalization of relations for dissapear anomaly
- implementation the database in create table for user relation to normalize.


Normalization of Data Bases

- Normalization is process structure database form so that more of ambiguity can be disappeared.
- the step of normalization begun from easy step (INF) to hard step (5NF).
- It is usually until 3NF step or BCNF because it's provide enough for result good quality table.

Why normalization must be done?
- Optimalization the table structures
- Increase the speed
- Disappear input data that similar
- More efficient to use in storage media
- Decrease redundancy
- Avoid anomally (insertion anomalies, deletion anomalies, update anomalies).
- Increase the data integrity


Normalization done by because to Optimalisasi Structure of is tables, Improving speed, Eliminating same data inclusion, More efficient in storage media use, Lessening redundans, Avoiding anomali ( insertion anomalies, deletion anomalies, update the anomalies) and improved Data integrity.
A table can be said good ( efficient) or normal if fill in 3 criteria :
- If there is decompotition table, so its decompotition must secure (Lossless-Join Decomposition). It mean after decompotition become a new tables, the new tables result first similar table.
- Preserve the functional dependency when changing data(Dependency Preservation.
- not brake Boyce-Code Normal Form (BCNF)
- If the third criteria can be filled so the table not brake 3rd normal form/ 3NF.



Functional Dependency

Functional Dependency decribe attributes relationship in a relation
A attribute can be said FD if we use atribute value for determine other atribut value.
the simbol for represent FD is -->

Notation A ? B
A & B are arttribute from a table, it mean that functionally. A determine B or B hang up A, if only a 2 row data with A same value,so B same value too.

Notation A /-> B Or A x-> B

Are opposite from previously notation.


FUNCTIONAL DEPENDENCY (FD)



* Functional Dependency:
- NRP -> Name
- Subject_study,NRP -> VALUE

* Non Functional Dependency:
- Subject_study -> NRP
- NRP -> VALUE

Functional Dependency From value table
- NRP -> Name
Because for each same nrp value,so name value same too
- {Subject_study, NRP} -> Value
Because attribute value depend on subject_study and NRP togethe. In other mean for same subject_study and NRP, so the value same too, because subject_study and NRP are the key (unique).
- Subject_study -> NRP
- NRP -> value


First Normal Form - 1NF

A Table can be said in first normal form if it out in unnormalized table form, where doubling same field and enable null field

can be allowed to:
- Attribute that have more value.
- Both Combination attribute.

so:
- Domain value is atomic value



Second Normal Form - 2NF

- Normal form-2NF filled in a table if fill in 1NF form, and all atribute except primary key entirely have Functional Dependency in primary key.
- A table is not fill in 2NF, if there is attribute that Functional Dependency only partial ( only depend on half from primary key) .
- If there is attribute that not have dependency to primary key , so the attribute must be disappeare.
- Functional dependency X-> Y said full if deleting an attribute A from X mean Y not depending functional again.
- Functional Dependency X -> Y said partial if deleting an attribute A from X mean Y still depending functional.
- scheme R relation in 2NF form if each attribute non primary key A -> R depend functionally to primary key R.


Not fill in Second Normal Form - 2NF, because NIM, MKcode as a primary key
{NIM, KodeMk} → NameStudent
{NIM, KodeMk} → Address
{NIM, KodeMk} → Subjectstudy
{NIM, KodeMk} → Sks
{NIM, KodeMk} → LetterValue



Functional dependency :
{NIM, KodeMk} -> LetterValue (fd1)
NIM -> {NamaMhs, Address} (fd2)
KodeMk ? {Subjectstudy, Sks} (fd3)
becoming :

fd1 (NIM, KodeMk, LetterValue) → Tables Value
fd2 (NIM, NameMhs, Address) → Tables of student
fd3 (KodeMk, Matakuliah, Sks) → Tables Subjectstudy



Third Normal Form - 3NF

Normal form-3NF filled if fullfill in 2NF-form, and if there is not non primary key attribute that have dependency in other non primary key attribute (transitive dependency)
Because still have non PK attribute (that is city and regency) that have dependency to other non primary key attribute( postcode)
So that the tables require to discomosition become.:
Student (NIM, NameMhs, Jalan, KodePos)
KodePos (KodePos, Provinsi)


Boyce-Codd Normal Form (BNCF)

Boyce-Codd Normal Form constraint has a stronger form of the Normal third. To be BNCF, relations must be in the form of Normal first and forced each of the attributes depends on the function in the super key attributes. In the example below there is a relationship seminar, is the Primary Key NPM + Seminar.

Students may take one or two seminars. Each seminar requires 2 leader and each of the students be taught by one of the 2 leader in seminar. Each leader can only take one seminar course. In this example NPM and Seminar show a Seminar leader.







Fifth and Fourth normal form

- Relations in fourth normal form (NF 4) if the relation in BCNF and dependency not contain many values. To remove the dependency of many values from a relation, we divide the relationship into two new relations. Each relation contains two attributes that have a lot of relationship value.

- Relations in fifth normal form (5NF) deal with the property called the join without any loss of information (lossless join). Fifth normal form (also called the 5 NF PJNF (projection join normal form). The case is very rare appear and difficult to detect instantly.