Kancutberbunga’s Blog

Database design process
• Gathering user needs / business
We must have a data user’s needs, which later will be the grip when designing the database. Needs that this will be the basis of the system that we created
• Develop the ER model based on user needs / business
Once we have all the data needs of users, then we can design ER models which later became the basis of data that will be built
• Convert ER Model to set the relation (table)
After have the framework of the ER model, we can enter data from the ER model to the table in the database that we design
• Normalization of relations, to remove anomalies
• to implement the database for each table to create relationships that are normalization

Database normalization
• normalization process is the establishment of the database structure so that most of the ambiguity can lose . stage normalization phase starting from the light (1NF) to most stringent (5NF). The process of normalization should be done only up to 3NF. The process of 4NF and 5NF tend have a separate table that is too increasing the complexity and the query execution time, or often known by the term over normalization.

• Why do normalization?
- Optimization table structures
- Increase the speed
- Eliminate income data the same
- More efficient use of storage media
- Reduce redundancy
- Avoiding anomalies (insertion anomalies, deletion anomalies, update anomalies).
- Improved data integrity
• A table said good (efficient) or if the normal 3 to meet the following criteria:
- If there is decomposition (decomposition) table, it must be guaranteed safe decomposition (Lossless-Join Decomposition). That is, after the table is described / decomposition a new table-table, the table-table can generate a new table with the same exact.
- Maintain the functional dependence on the change data (Dependency preservation).
- No violate Boyce-Code Normal Form (BCNF)
• If the three criteria (BCNF) can not be met, then at least the table does not violate the Normal Form of the third stage (3rd Normal Form / 3NF).

Functional Dependency

notation: A –> B

A and B are attributes of a table. A means of determining the functional B or B depends on A, if and only if there are 2 rows of data with the same value of A, then B is also the same value

• notation: A –> B or A x–> B

• It is the opposite of the previous notation.

Sample table value:

• Nrp –> namaMhs

Because for each value Nrp the same, then the value of the same namaMhs

• {Namakul, NRP} –> NiHuruf

Because the Nihuruf of attributes depending on the NRP and namakul together. In another sense namakul for the NRP and the same, they also the same Nihiruf, because namakul and the NRP is a key (is unique).

• Mata_kuliah not –> NRP

• NRP not –> Nilai

Normal forms:

1. 1st Normal Form / 1NF
2. 2nd Normal Form / 2NF
3. 3rd Normal Form / 3NF
4. Boyce-Code Normal Form (BCNF)
5. 4th Normal Form / 4NF
6. 5th Normal Form / 5NF

First Normal Form – 1NF

· Normal form if it met a 1NF table does not have many valuable attributes (multivalued attribute), attribute composite or a combination of data in the same domain.

· Each attribute in the table must be atomic values (can not be divided-for more)

Ex student data as follows:

or

Table-the table above does not meet the requirements 1NF

decomposition to be:

Mahasiswa table:

Hobi Table:

Second Normal Form – 2NF

• Normal form 2NF met in a table if it meets the form of 1NF, and all the attributes than the primary key, have a full Functional Dependency on primary key
• A table does not meet 2NF, if there are attributes that dependence (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

example:

Functional dependency like this:

{Mhs_nrp, mk_kode} –> nihuruf (fd1)

Mhs_nrp –> {mhs_nama, mhs_alamat} (fd2)

Mk_kode  –> {mk_nama, mk_sks} (fd3)

So:

fd1 (mhs_nrp, mk_kode, nihuruf) –> Nilai Table

fd2 (Mhs_nrp, mhs_nama, mhs_alamat) –> Mahasiswa Table

fd3 (mk_kode, mk_nama, mk_sks) –> MataKuliah Table

Third Normal Form – 3NF

Normal form 3NF fulfilled 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).

Table following students eligible 2NF, 3NF, but does not meet

Because the table above there are still non-primary key attribute (ie, Kota and Provinsi), which has a dependence on non-primary key attributes of the other (ie KodePos), namely:

KodePos –> {Kota, Provinsi}

So that the table in the decomposition needs to be:

• Mahasiswa (NIM, NamaMhs, Jalan, KodePos)

– KodePos (KodePos, Provinsi, Kota)

Boyce-Code Normal Form (BCNF)

BCNF fulfilled in the form of a table, if for every functional dependency of each attribute or attribute combination in the form of: X –> Y and X is a super key

table must be based on functional decomposition of dependency, so that X becomes super-key of the table of decomposition

Each table in BCNF is 3NF. but will each 3NF not necessarily including A. The difference, for the functional dependency X –> A  not allow as part of a primary key.

4th Normal Form /4NF

For example, the table below do not meet 4NF:

Leave a Reply Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

Notify me of follow-up comments via email.