Information Systems Analysis and Design [Part-7]

AN EXERCISE

I have task, that is to normalize the ER-Diagram below and provide the representation of the physical table.

that diagram is have anomaly entity, then i try to solve that as follows:
1. There’s no cardinality between entity BAGIAN and PROYEK. It should have been 1-N from BAGIAN to PROYEK. it is because of that one BAGIAN may have many PROYEK, and one PROYEK may only have one BAGIAN.
2. The ternary relationship between entity PEGAWAI, PROYEK, & TANGGUNGAN seems strange. It’s legitimate if entity PEGAWAI has a relationship with TANGGUNGAN (TANGGUNGAN will be the weak entity because it’s Dependant with the existence of the PEGAWAI). But it’s also has a relation with PROYEK, and supported with the existence of relation between PEGAWAI an PROYEK. Then I will delete the ternary relationship between entity PEGAWAI and TANGGUNGAN and change them only with binary relationship.
3. To determine the mapping later, we need to fix the relation PIMPIN between entity PEGAWAI and BAGIAN to gain the foreign key. Because of one BAGIAN must be managed at least by one PEGAWAI, and one PEGAWAI must not managing one BAGIAN.
Here is the fixed ER diagram:
Then I try to make the Mapping of the ER-Diagram above:

After that, I make the normalization for 1NF as follows :

For table TANGGUNGAN and KERJA, it don't need to be normalization because they don't have a primary key.
The normalization above has also been the 2NF normalization.

Then for the 3NF normalization, we should determine which of the attribute that has a transitive dependency. There is: