Database Design process

Database Design process

• Collecting user / business requirements
• Developing E-R Model refer to user / business requirements
• Converting E-R Model to relationship gatherings (table)
• Normalizing relationship to remove anomalies
• Implementation to database by make table for each relationship has been normalizatio

Database Normalization

• Normalization is process to create database structure until a large of ambiguity can be removed.
• Normalization phase is begin from lightest phase (1NF) until tightest (5NF)
• Usually it’s just until 3NF level or BCNF because was enough is equal to result the good table qualified.

Why do normalization?

• Optimizing table structures
• Increase speed
• Removing the same data input
• More efficient in the use of storage media
• Reduce redundancy
• To avoid anomalies (insertion anomalies, deletion anomalies, update anomalies).
• Increased data integrity

A table is called good (efficient) or normal if has 3 criteria as follows:

1. If there is table decomposition, then its decomposition should be secured safe (Lossless Join Decomposition). It’s mean, after that table is untied / decomposition becomes new tables, which new tables can result original tables equal with previously.
2. It’s petted dependable functional at the moment data change (Dependency Preservation).
3. Don't break Boyce Code Normal Form (BCNF)

If third criteria (BCNF) can't be accomplished, and then at least that table not breach third phase of Normal Form (3rd Normal Form/ 3NF).

Functional Dependency

• Functional Dependency describes attributes relationship in one relationship

• An attribute is called functionally dependant on another one if we use that attribute to determine the value of other attribute.

Symbol that used is -> for representing functional dependency.
-> read: functionally determines

Notation: A -> B
A and b is attribute of one table. In functionally A determines B or B depend to A, if and only if there are 2 data row appropriate with A, then B also have the same value.

Notation: A. -/-> B or A x -> B
Are opposite of the previous notation.

First Normal Form

A table on the form said to be normal 1 if it did not reside in the unnormalized form of a table, where there are multiplication in a same kind of field and field that allows there is null field (empty field)

Prohibited:

• Multivalued attributes
• Attributes composite or a combination of both.

And then:

Cost of the domain attribute must be atomic cost

Second Normal Form

• Normal form 2NF met in a table if it meets the form of 1NF, and all the attributes except the primary key, have a full Functional Dependency on primary key.
• A table does not meet 2NF, if there are attributes that it's dependency (Functional Dependency) are only partial (only depending on the part of the primary key)
• If there are attributes that have no dependence on the primary key, then the attributes must be moved or removed
• Functional dependency X->Y is said full if it is remove an attribute A from X . It is means that Y is no functionally dependent.
• Functional dependency X -> Y is said partial if it is remove an attribute A from X . It is means that Y is functionally dependent.
• Relation scheme R in 2NF if every non-primary key attribute A E R functionally depend on the full primary key of R.

This table following 1NF but not 2NF

Boyce-Codd Normal Form

Boyce-Codd Normal Form constraint has a stronger form of the Normal third. To be BNCF, relations must be in the form of 1NF and forced each of the attributes depends on the function in the super key attributes.

3NF Table

Seminat table is depend on one key attribute is not as super key as condition by BCNF. So
Seminar relations must be parsed into two namely:

Third Normal Form

Normal form 3NF met if the form meets 2NF, and if there are no non-primary key attribute that has a dependence on non-primary key attributes of the other (transitive dependencies).

This table following 2NF but not 3NF

Fourth and Fifth Normal Form

Relations in the 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 (the 5 NF also called PJNF (projection join normal form). The case is very rare and appear difficult to detect in practice.